Creating Live Aggregate Projections

Answers

• marcothesane - Select Field - Administrator

  April 2021

  The projection will always be sorted and segmented by its GROUP BY columns - which is also the most efficient way for its purpose.

  And, yes, you can create an UNSEGMENTED ALL NODES projection on a single-node.

  0
• kfruchtman Vertica Customer ✭

  April 2021

  Sorry , I am still not clear.
  I need the projection to be segmented not unsegemtned to create a groupby clause.So I can create it segemented despite the fact that it is a 1 node environment?
  To be clearer - can I create a "Live Aggregate Projections" on a one node environment?

  0
• marcothesane - Select Field - Administrator

  April 2021

  Yes. If you can create a "normal" projection SEGMENTED BY HASH() ALL NODES. on a one-node, you can do that with a Live Aggregate projection, too ...

  My single noder:

  marco ~/1/Vertica/sql/demos $ vsql -x -c "select * from nodes"
  -[ RECORD 1 ]---------+-------------------------------------------------
  node_name             | v_sbx_node0001
  node_id               | 45035996273704978
  node_state            | UP
  is_primary            | t
  node_address          | 127.0.0.1
  node_address_family   | ipv4
  export_address        | 127.0.0.1
  export_address_family | ipv4
  catalog_path          | /home/dbadmin/sbx/v_sbx_node0001_catalog/Catalog
  node_type             | PERMANENT
  is_ephemeral          | f
  standing_in_for       | 
  subcluster_name       | 
  last_msg_from_node_at | 2021-04-09 11:27:21.007699+02
  node_down_since       | 
  

  And here's an export_objects() of a Live Aggregated projection from that database:
  marco ~/1/Vertica/sql/demos $ vsql -Atc "select export_objects('','published.item_Test_DailyItemAgg',false)"

  CREATE PROJECTION published.item_Test_DailyItemAgg
  (
   installation_ek,
   DailyItemAggLocalDate,
   supergroup,
   type,
   disposalType,
   reject,
   rejectReason,
   itemGroup,
   refundGroup,
   sumOfItemValue,
   numberOfItems
  )
  AS
   SELECT i.installation_ek,
          date(i.consumerSession_endLocalTime) AS DailyItemAggLocalDate,
          i.supergroup,
          i.type,
          i.disposalType,
          i.reject,
          i.rejectReason,
          i.itemGroup,
          i.refundgroup AS refundGroup,
          sum((i.value * i.itemCount)) AS sumOfItemValue,
          sum(i.itemCount) AS numberOfItems
   FROM published.item_Test i
   GROUP BY i.installation_ek,
            date(i.consumerSession_endLocalTime),
            i.supergroup,
            i.type,
            i.disposalType,
            i.reject,
            i.rejectReason,
            i.itemGroup,
            i.refundgroup
  ;
  

  0
• kfruchtman Vertica Customer ✭

  April 2021

  I will try that..didn't get to it yet.
  I hope it will improve my bad query performance.

  Many thanks!!!
  Keren

  0