Data export to a file from table

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

Comments

  • 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  
  • i want to do it in a single command line so can u suggest me one thanks
  • See External Procedures.
  • Navin_CNavin_C Vertica Customer
    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.
  • 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
  • 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 .
  • actually i am running these queries against vertica d/b through a jdbc connection. so what can i do in this case.
  • 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!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file