merge flex table

Naveen_Kumar

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

[Deleted User]

Greetings.
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.

Naveen_Kumar

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 

  WHEN NOT MATCHED THEN INSERT(keys) VALUES(keys);"
but i have thousand no of keys so i dont want to mention all the keys manually. so can you help me now? 

[Deleted User]

Greetings.
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.

Naveen_Kumar

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;"
done
exit 0;
 after executing .sh i am getting an empty table as merg2;
can you help me ?
thanks in advcance.

[Deleted User]

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.