How do I retrieve Vertica Data in Parquet? locally

Question

I'm trying to export data from my vertica DB to my laptop's local storage. Is this possible?

When I tried the directory = 'C:/Downloads' it showed dir not found error.

What's the alternative to directly retrieve data to local in parquet?

Doc for reference : https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/EXPORTTOPARQUET.htm

EXPORT TO PARQUET(directory = 'hdfs:///user3/data') 
 OVER(ORDER BY col1) AS SELECT col1 + col1 AS A, col2
 FROM public.T3;

If you have an AWS account with access to an S3 bucket, you can use your credentials to export the data to that bucket and get the file from there. — Zaeem Hussain, Apr 06 '22 at 20:11

score 0 · Answer 1 · answered Mar 10 '22 at 12:48

The only way that worked for me was to export to a directory on a Vertica node, and then scp the generated directory to my platform (a Mac M1 in my case):

marco ~/1/aaa $ vsql -c "export to parquet (
                   directory='/tmp/d_custym_scd',int96AsTimestamp=false 
                 ) as select * from scd.d_custym_scd"
 Rows Exported 
---------------
            22
(1 row)

marco ~/1/aaa $ scp -r dbadmin@zbook:/tmp/d_custym_scd/ /tmp/
dbadmin@zbook's password: 
aae46ff7-v_sbx_node0001-140499690358528-0.parquet    100% 4605   332.4KB/s   00:00

If you don't have access as dbadmin to a Vertica node's Linux shell, make sure that there exists a directory on the Vertica node exists where you - as an ordinary mortal Linux user - can read from, and scp the created directory logged in as that user instead of dbadmin.

unfortunately, no access to anything but an empty table to fill values into (via hostname, db name). Thank you. — Shivam Anand, Mar 10 '22 at 12:56

How do I retrieve Vertica Data in Parquet? locally

1 Answers1