SELECT all data such that a single-node query plan is maintained

gjorgjevski

Assume I have a table defined as

CREATE TABLE checkout (
    id INT ENCODING RLE,
    transaction_timestamp TIMESTAMP ENCODING DELTAVAL,
    transaction_amount DOUBLE PRECISION
)
ORDER BY transaction_timestamp
SEGMENTED BY hash(id) ALL NODES;

My Vertica cluster has 3 nodes. I want to go to one of those nodes and SELECT * FROM checkout; such that the data is present locally and the query is executed only on that node. Is there a way to do this?

Jim_Knicely

Hi,

Is this what you need?

dbadmin=> CREATE TABLE checkout (
dbadmin(>     id INT ENCODING RLE,
dbadmin(>     transaction_timestamp TIMESTAMP ENCODING DELTAVAL,
dbadmin(>     transaction_amount DOUBLE PRECISION
dbadmin(> )
dbadmin-> ORDER BY transaction_timestamp
dbadmin-> SEGMENTED BY hash(id) ALL NODES;
CREATE TABLE

dbadmin=> INSERT INTO checkout SELECT 1, sysdate, 100;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO checkout SELECT 2, sysdate+2, 200;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO checkout SELECT 3, sysdate+3, 300;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO checkout SELECT 4, sysdate+4, 400;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO checkout SELECT 5, sysdate+5, 500;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT node_name FROM nodes;
     node_name
--------------------
 v_test_db_node0001
 v_test_db_node0002
 v_test_db_node0003
(3 rows)

dbadmin=> SELECT * FROM checkout WHERE local_node_name() = 'v_test_db_node0001' ORDER BY id;
 id |   transaction_timestamp   | transaction_amount
----+---------------------------+--------------------
  5 | 2018-10-10 09:46:53.97863 |                500
(1 row)

dbadmin=> SELECT * FROM checkout WHERE local_node_name() = 'v_test_db_node0002' ORDER BY id;
 id |   transaction_timestamp    | transaction_amount
----+----------------------------+--------------------
  1 | 2018-10-05 09:46:53.756176 |                100
  2 | 2018-10-07 09:46:53.820046 |                200
  3 | 2018-10-08 09:46:53.870484 |                300
(3 rows)

dbadmin=> SELECT * FROM checkout WHERE local_node_name() = 'v_test_db_node0003' ORDER BY id;
 id |   transaction_timestamp    | transaction_amount
----+----------------------------+--------------------
  4 | 2018-10-09 09:46:53.922143 |                400
(1 row)

gjorgjevski

Thank you, Jim! That seems to be working nicely and elegantly.