Hello,
Is there a way to get the data segmentation accross nodes based on a column and a cluster size.
I have simulated a case where I break initial segmentation rule and need to change segmentation expression.
table I m using is named 'mytable' and have the column ID as a primary key and HASH(ID) in segmentation column clause.
My initial table (mytable) is well segmented.
dbadmin=> select node_name, projection_schema, projection_name, anchor_table_name, row_count dbadmin-> from projection_storage dbadmin-> where projection_schema ='test' dbadmin-> and anchor_table_name = 'mytable' dbadmin-> and projection_name ='mytable_b0' dbadmin-> order by 1; node_name | projection_schema | projection_name | anchor_table_name | row_count ---------------+-------------------+-----------------+-------------------+----------- v_kb_node0001 | test | mytable_b0 | mytable | 939667 v_kb_node0002 | test | mytable_b0 | mytable | 940459 v_kb_node0003 | test | mytable_b0 | mytable | 940889 v_kb_node0004 | test | mytable_b0 | mytable | 939362 v_kb_node0005 | test | mytable_b0 | mytable | 939706 (5 rows)
After deleting data from one node (just simulating a deletion/update that break a correct segmentation)
dbadmin=> delete from test.mytable where local_node_name() = 'v_kb_node0001';
OUTPUT
--------
939667
(1 row)
dbadmin=> commit;
COMMIT
select DO_TM_TASK('moveout','test.mytable');
select DO_TM_TASK('mergeout','test.mytable');
now data is not present in node1:
dbadmin=> select node_name, projection_schema, projection_name, anchor_table_name, row_count dbadmin-> from projection_storage dbadmin-> where projection_schema ='test' dbadmin-> and anchor_table_name = 'mytable' dbadmin-> and projection_name ='mytable_b0' dbadmin-> order by 1; node_name | projection_schema | projection_name | anchor_table_name | row_count ---------------+-------------------+-----------------+-------------------+----------- v_kb_node0001 | test | mytable_b0 | mytable | 0 v_kb_node0002 | test | mytable_b0 | mytable | 940459 v_kb_node0003 | test | mytable_b0 | mytable | 940889 v_kb_node0004 | test | mytable_b0 | mytable | 939362 v_kb_node0005 | test | mytable_b0 | mytable | 939706 (5 rows)
My goal is to find a query that might give the same distribution as above.
I found the below query in the forum, but it seem not to be giving the correct segmentation distribution :
dbadmin=> SELECT FLOOR(HASH(ID)/((9223372036854775807/5) + 1)) AS Node,
dbadmin-> COUNT(*) AS row_count
dbadmin-> FROM test.mytable
dbadmin-> GROUP BY 1
dbadmin-> ORDER BY 1;
Node | row_count
------+-----------
0 | 752296
1 | 752671
2 | 752863
3 | 750851
4 | 751735
(5 rows)
Note: I also tried exporting data (after deletion of node1 data) to a file and loading it again using COPY command, it also show that node1 data is empty, which is expected as segmentation rely on the same values.
Can you please help with a query that can allow to evaluate segmentation expressions.
Many thanks.
Mkheir.
