EXPORT TO PARQUET - Specify the Node Data Will be Written To

avi120

I have 3 node Vertica cluster. I would like to export data to local folder on specific node from the three node cluster.
How can I do it without creating a mapping on the other nodes?

Currently my command is:

EXPORT TO PARQUET (directory = '/data/export_vertica_tables/a/') AS SELECT b from imp.a;

Jim_Knicely

Try using an empty OVER() clause.

EXPORT TO PARQUET (directory = '/data/export_vertica_tables/a/') OVER() AS SELECT b from imp.a;

See: Partitioning and Sorting Data

It says:

If you are exporting data to a local file system, you might want to force a single node to write all of the files. To do so, use an empty OVER clause.

avi120

Thanks for the reply. To better understand, I would appreciate getting input on the following:

1) When I used the empty OVER() I saw that the directory was also created on the other nodes but was empty. Why this empty directory is created?

2) I ran the command on node1, but the data was exported to node2. Can I control to which node the data will be written?

3) Is OVER() also the default if I omit the OVER all together.

Jim_Knicely

Hi,

Which version of Vertica are you running? Note that Vertica 11.0 is the first version to officially support exporting to the Linux File System.

See: Export to Linux File System

It says:

When exporting data, you can now write to the node-local Linux file system. Previously, exports to Linux were only supported for an NFS mount.

Anyway, there doesn't appear to be a way to just write to one node...

Below are some quick tests using the DB view as described here Monitoring Exports to show the rows written on each node...

dbadmin=> SELECT sysdate;
          sysdate
----------------------------
 2021-10-25 11:15:54.592678
(1 row)

dbadmin=> EXPORT TO PARQUET(directory='/home/dbadmin/big_empty_over100') OVER () AS SELECT * FROM big;
 Rows Exported
---------------
         65536
(1 row)

dbadmin=> SELECT node_name, SUM(rows) rows FROM parquet_export_events WHERE report_time > '10/25/2021 11:15:54' GROUP BY node_name ORDER BY node_name;
     node_name      | rows
--------------------+-------
 v_test_db_node0001 | 21722
 v_test_db_node0002 | 21929
 v_test_db_node0003 | 21885
(3 rows)

dbadmin=> SELECT sysdate;
          sysdate
----------------------------
 2021-10-25 11:17:03.680264
(1 row)

dbadmin=> EXPORT TO PARQUET(directory='/home/dbadmin/big_no_over100') AS SELECT * FROM big;
 Rows Exported
---------------
         65536
(1 row)

dbadmin=> SELECT node_name, SUM(rows) rows FROM parquet_export_events WHERE report_time > '10/25/2021 11:17:03' GROUP BY node_name ORDER BY node_name;
     node_name      | rows
--------------------+-------
 v_test_db_node0001 | 21722
 v_test_db_node0002 | 21929
 v_test_db_node0003 | 21885
(3 rows)

dbadmin=> SELECT sysdate;
          sysdate
----------------------------
 2021-10-25 11:17:50.409652
(1 row)

dbadmin=> EXPORT TO PARQUET(directory='/home/dbadmin/big_over100') OVER(PARTITION BY c1) AS SELECT * FROM big;
 Rows Exported
---------------
         65536
(1 row)

dbadmin=> SELECT node_name, SUM(rows) rows FROM parquet_export_events WHERE report_time > '10/25/2021 11:17:50' GROUP BY node_name ORDER BY node_name;
     node_name      | rows
--------------------+-------
 v_test_db_node0001 | 19865
 v_test_db_node0002 | 15592
 v_test_db_node0003 | 18346
(3 rows)