Populating Flex table Materialized columns from current data in __raw__

Comments

• Ariel_Cary Vertica Employee Employee

  May 2023

  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)
  

  0