Populating Flex table Materialized columns from current data in __raw__

robann

The documentation for Flex tables includes information to "promote virtual columns to materialized (real) columns":

https://docs.vertica.com/12.0.x/en/flex-tables/materializing-flex-tables/

The example given adds the promoted column and then loads data to populate it. However, we have a large amount of data already loaded into a Flex/hybrid table and wish to add the column and populate the materialized column from the data already loaded.

This sounds like it should be possible, but I have not found a way to do this. i.e. add the column and then populate it from the data already contained in the raw column. Any tips?

Thanks,
Robert

Ariel_Cary

You can use the DEFAULT option to populate values in the new, promoted column.

Add a column with a DEFAULT expression to an existing table. Vertica populates the new column with its default values when it is added to the table.

https://docs.vertica.com/12.0.x/en/admin/working-with-native-tables/managing-table-columns/defining-column-values/

See this example:

-- Load some data into a flex table
create flex table f1();
insert into f1(a_key) values(123);

-- Check query plan to see where data for "a" is coming from.
-- Line 3 below confirms "a" data is looked up from the flex table's VMap (__raw__ column)
--   3[label = "ExprEval: \n  public.MapLookup(f1.__raw__, \'a_key\')\nUnc: Long Varchar(130000)", color = "brown", shape = "box"];
 EXPLAIN select a_key from f1;

 Access Path:
 +-STORAGE ACCESS for f1 (PATH ID: 1)
 |  Projection: public.f1_super
 |  Materialize: f1.__raw__

  ------------------------------
 ----------------------------------------------- 
 PLAN: BASE QUERY PLAN (GraphViz Format)
 ----------------------------------------------- 
 digraph G {
 graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: EXPLAIN select a_key from f1;\n\nAll Nodes Vector: \n\n  node[0]=initiator (initiator) Up\n", labelloc=t, labeljust=l ordering=out]
 0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"];
 1[label = "NewEENode \nOutBlk=[UncTuple]", color = "green", shape = "box"];
 2[label = "StorageUnionStep: f1_super\nUnc: Long Varchar(130000)", color = "purple", shape = "box"];
 3[label = "ExprEval: \n  public.MapLookup(f1.__raw__, \'a_key\')\nUnc: Long Varchar(130000)", color = "brown", shape = "box"];
 4[label = "ScanStep: f1_super\n__identity__ (not emitted)\n__raw__\nUnc: Long Varbinary(130000)", color = "brown", shape = "box"];
 1->0 [label = "V[0]",color = "black"];
 2->1 [label = "0",color = "blue"];
 3->2 [label = "0",color = "blue"];
 4->3 [label = "0",color = "blue"];
 }


-- Promote column "a_key" to a real column and set DEFAULT option to load data to existing rows.
-- Assume "a_key" stores integer data.
alter table f1 add column a_key INTEGER DEFAULT (public.MapLookup(f1.__raw__, 'a_key'))::!int;

-- Re-check query plan to verify "a_key" data comes from the promoted column.
 EXPLAIN select a_key from f1;

 Access Path:
 +-STORAGE ACCESS for f1 (PATH ID: 1)
 |  Projection: public.f1_super
 |  Materialize: f1.a_key

 ------------------------------
 ----------------------------------------------- 
 PLAN: BASE QUERY PLAN (GraphViz Format)
 ----------------------------------------------- 
 digraph G {
 graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: EXPLAIN select a_key from f1;\n\nAll Nodes Vector: \n\n  node[0]=initiator (initiator) Up\n", labelloc=t, labeljust=l ordering=out]
 0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"];
 1[label = "NewEENode \nOutBlk=[UncTuple]", color = "green", shape = "box"];
 2[label = "StorageUnionStep: f1_super\nUnc: Integer(8)", color = "purple", shape = "box"];
 3[label = "ScanStep: f1_super\n__identity__ (not emitted)\na_key\nUnc: Integer(8)", color = "brown", shape = "box"];
 1->0 [label = "V[0]",color = "black"];
 2->1 [label = "0",color = "blue"];
 3->2 [label = "0",color = "blue"];
 }

-- verify existing rows do get a value assigned.
select a_key from f1;
 a_key 
-------
   123
(1 row)