8

I`m working with Vertica. I try to export data from SELECT query into csv. I tried making it with sql query:

SELECT * FROM table_name INTO OUTFILE '/tmp/fileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

I got an error:

[Vertica][VJDBC](4856) ERROR: Syntax error at or near "INTO"

Is there a way to export a query result to a csv file? I prefer not to use vsql, but if there no other way, I will use it. I tried the following:

vsql -c "select * from table_name;" > /tmp/export_data.txt
Asclepius
  • 57,944
  • 17
  • 167
  • 143
Opperix
  • 123
  • 2
  • 2
  • 5

3 Answers3

12

Here is how you do it:

vsql -U dbadmin -F ',' -A -P footer=off -o dumpfile.txt -c "select ... from ... where ...;"

Reference: Exporting Data Using vsql

Asclepius
  • 57,944
  • 17
  • 167
  • 143
Marcus Couto
  • 129
  • 1
  • 3
  • 1
    ```vsql -U dbadmin dwh -A -P footer=off -F ',' -c "SELECT 'Definitely not a valid csv, but who cares, right?' AS mycolumn;" | RESULT: | mycolumn Definitely not a valid csv, but who cares, right? ``` – Alex Dec 28 '18 at 12:22
  • 2
    How do I provide quote charater here. For example my output should be -> a,"hi,there",1 – Carbonrock Apr 25 '19 at 14:54
6

Accordingly to https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/ConnectingToHPVertica/vsql/ExportingDataUsingVsql.htm

=> SELECT * FROM my_table;
 a |   b   | c
---+-------+---
 a | one   | 1
 b | two   | 2
 c | three | 3
 d | four  | 4
 e | five  | 5
(5 rows)
=> \a
Output format is unaligned.
=> \t
Showing only tuples.
=> \pset fieldsep ','
Field separator is ",".
=> \o dumpfile.txt
=> select * from my_table;
=> \o
=> \! cat dumpfile.txt
a,one,1
b,two,2
c,three,3
d,four,4
e,five,5
fl00r
  • 82,987
  • 33
  • 217
  • 237
3

By following way you can write to CSV file as comma separated and no footer.

vsql -h $HOST -U $USER -d $DATABASE -w $PASSWORD -f $SQL_PATH/SQL_FILE -A -o $FILE -F ',' -P footer=off  -q
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Avinash
  • 31
  • 1