Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Creating Live Aggregate Projections

Hi Experts!
Is there a way to workaround that that it is mandatory that a projection should be segmenteed to make "group by" in the projection?
"The projection cannot be unsegmented." from the documentation.
I have only 1 node.Can I create a segmented projection in a 1 node environment? I need the groupby projection functionality.
Is it better to "group by" a projection than create this column in the sort by the first one?
I am trying to avoid "hash group by " for a long running query.

Many thanks for helping!
Keren

Answers

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

  • 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?

  • 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
    ;
    
  • I will try that..didn't get to it yet.
    I hope it will improve my bad query performance.

    Many thanks!!!
    Keren

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.