Data export to a file from table

harish_babu_yar

Hello i had an scenario where i need to copy data from a table to a file. So can some one suggest me a way to do this . (Can u please suggest a way with out writing a shell script if that is only the way please enclose an example ) Thanks

Daniel_Leybovic

Hi! I believe any client that can connect to Vertica can export to csv, depends on client you use 'vsql' is also and it default Vertica client, so I will provide a vsq script:

  --  -- DEFENITIONS  --  \set output_file      '/tmp/customer_dim.csv'  \set table            'public.customer'  \set export_table     'SELECT * FROM :table;'    --  -- CSV OPTIONS  -- (remove or comment unnecessary)  --  \a  \pset tuples_only      -- export with no header  \pset footer          off  \pset null            'N\\A'  \pset recordsep       '\t'  \pset fieldsep        '\n'    --  -- EXPORT  --  \o :output_file  :export_table  \o  

harish_babu_yar

i want to do it in a single command line so can u suggest me one thanks

Daniel_Leybovic

See External Procedures.

Navin_C

Hello harish, Maybe this can help you. Use this one liner to get data into the flat file

 vsql -F'","' -Ac "select * from test" | grep -v '([0-9]* rows)' | sed 's/^\|$/"/g' >> '/tmp/test.csv'

This will load all data from table test into your csv file. hope this helps.

Daniel_Leybovic

Hi! @Navin I already improved this version: CMD No compression $ vsql -F'","' -P footer=off -Aqnc "select * from tbl" | awk '{print "$ vsql -F'","' -P footer=off -Aqnc "select * from tbl" | awk '{print "\042"$0"\042"}' > /path/to/file.csv 42"$0"$ vsql -F'","' -P footer=off -Aqnc "select * from tbl" | awk '{print "\042"$0"\042"}' > /path/to/file.csv 42"}' > /path/to/file.csv With compression $ vsql -F'","' -P footer=off -Aqnc "select * from tbl" | awk '{print "$ vsql -F'","' -P footer=off -Aqnc "select * from tbl" | awk '{print "\042"$0"\042"}' | gzip -f > /path/to/file.csv.gz42"$0"$ vsql -F'","' -P footer=off -Aqnc "select * from tbl" | awk '{print "\042"$0"\042"}' | gzip -f > /path/to/file.csv.gz42"}' | gzip -f > /path/to/file.csv.gz

harish_babu_yar

COPY tablename (a1,a2,a3,a4) from LOCAL '\temp\test.txt' DIRECT DELIMITER ',' ENCLOSED BY '"' SKIP 1 ABORT ON ERROR; this is how i am running a copy command for file to table where the file path changes dynamically . similarly i need to load now from table to file . suggest me a one line command which i can run on vsql .

harish_babu_yar

actually i am running these queries against vertica d/b through a jdbc connection. so what can i do in this case.

Daniel_Leybovic

Hi! >> actually i am running these queries against vertica d/b through a jdbc connection Could not explain it from the beginning?And you are asking how to save it in file? O_o OMG! What kind of Java programmer you are? Good Luck!