merge flex table

can we merge two view table?
if yes,what would be the best way if we are having thousands of keys.


  • Options
    One can definitely merge Flex views, but the method may differ based on the specifics of what you're trying to do (if you post some specifics, I can expand on the best method.) I should note, however, that Vertica has a limit of 1,600 on the number of columns in tables or views.

    1. If you've got two views pointing at the same Flexible Table, you can merge the contents of the {MyFlexTable}_keys tables used to generate them, then call build_flextable_view() to generate a new VIEW containing the keys from both previous views.

    2. If you've got two views pointing at different Flexible Tables, retrieve the definitions for each view via export_objects() then merge their SELECT lists, and JOIN or UNION the two source tables, depending on your desired merge behavior to create a new view definition.

    As always, we're interested in hearing how you'd like an automated version of this to look/act like.
  • Options
    Thanks for reply.
    i think i should post what i am doing.:-
    i have two flex tables say flex_tab1 ,  and flex_tab2  both are having the same no. of keys, now i want to insert all the data of flex_tab2 into flex_tab1. 
    can i use like - "MERGE INTO flex_tab1 t USING flex_tab2 s ON t.a = s.a      
      WHEN MATCHED THEN UPDATE SET a=s.a, b=s.b, c=s.c 
    but i have thousand no of keys so i dont want to mention all the keys manually. so can you help me now? 
  • Options
    In this initial Crane release, we do not support INSERT, UPDATE, or MERGE into Flexible Tables. There are ways to manually get to your use case, although none ideal at this time.
    Easy to do is getting this behavior with a standard table target, and this will get you everything save for the automatic translation between "SELECT MyVirtualCol FROM MyFlexTable" into "SELECT MapLookup(MyFlexTable.__raw__, 'MyVirtualCol') FROM MyFlexTable".
    So, if you wanted to merge Flex Table foo and Flex Table bar into a standard table baz with values from bar taking precedence JOINing on "id", and assuming no materialized columns for this example, you could:

    CREATE TABLE baz(__raw__ long varbinary(130000), __identity__ IDENTITY(1,1));
    INSERT INTO baz(__raw__) SELECT (__raw__) FROM bar;
    INSERT INTO baz(__raw__) SELECT (__raw__) FROM foo WHERE NOT EXISTS (SELECT 1 FROM baz WHERE foo.id = MapLookup(baz.__raw__, 'id'));

    This gives you a non-Flex Table "baz" containing the data you want, and while "SELECT id FROM baz;" will not work, "SELECT MapLookup(__raw__, 'id') FROM baz;" will work, and is the same pattern used for working with data loaded with multiple levels of nesting.
  • Options
    Lot of thanks for this wonderful idea. It really helped.  and sorry to bother again.
    ether this query or merge query(for  structured table) is working well from vsql platfrom i mean:
    vertica=> insert into merg2(__raw__) select(__raw__) from newmap2;
    but same query is not working from bash script :
    #! /bin/bash
    for i in /tmp/var/tellme/idmlogs/temp/MergedLog_searshs-pxoe_PxOE.*; do
    echo hello
    st=$(date +%s)'
    t=$(date +%S%N)
    echo $t
    /opt/vertica/bin/vsql -c "create flex table new$t();"
    echo new"$t"_keys
    /opt/vertica/bin/vsql -c "copy new$t from '$i' parser fjsonparser();"
    /opt/vertica/bin/vsql -c "insert into merg2(__raw__) select(__raw__) from new$t;"
    exit 0;
     after executing .sh i am getting an empty table as merg2;
    can you help me ?
    thanks in advcance.

  • Options
    It looks like you're missing a "COMMIT" statement within the same batch as your INSERT--by not committing your INSERTs, they are rolled back when that session/vsql call exits.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file