Creating Live Aggregate Projections

kfruchtman

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

marcothesane

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.

kfruchtman

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?

marcothesane

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
;

kfruchtman

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

Many thanks!!!
Keren