The critical systems do not allow any users to access to decision critical tables. Instead, flat files are created and shared with the client for review. Saving results of a query or database table in any relational database is one of important work. You can then transport create flat file or CSV file using any mode of electronic transport such as email, FTP, SFTP, etc. In this article, we will check one of such methods to export Vertica query results into CSV flat file.
How to Export Vertica Query Results into CSV?
As we know the importance of creating CSV file, now let us check how to export a Vertica query results to CSV flat file?
There are many methods that you can use to export Vertica table or results into a flat file. I will discuss one of the commonly used methods in this article. i.e. use vsql commands to export results and tables into a CSV file format.
Related Readings:
Exporting data using Vertica vsql
Vsql is one of the widely used Vertica command line interface. Just like Netezza nzsql, Vertica vsql provides lots of options to work with Vertica database and tables from command line.
Exporting data using Vertica vsql is easy, you can use it for simple data export task, such as creating a flat file by changing the output format of a query result set so that the output format is suitable for importing into another system or analyzing data using simple data visualization tools like Tableau.
Vsql Options to Export Vertica Data to CSV File
Below vsql options can be used to export Vertica result sets to csv format:
| Command-line Option | Description |
| -A or –no-align | Disable padding used to align output. |
| -t or –tuples-only | Show only tuples, disabling column headings and row counts. |
| -F or –field-separator | Set the field separator character. |
| -o or –output | Send output to a file. |
| -c or –command | Specify a SQL statement to execute. |
These options can be set either from within an interactive vsql session, or through command-line arguments to the vsql command.
Export Vertica Query Results into CSV Example
Below is the vsql command line options example to export Vertica table data into a flat file:
$vsql -U dbadmin -w password -F $',' -At -o sample_table_output.csv -c " select * from store.store_sales_fact limit 10;"
$ cat sample_table_output.csv
1,AAA
2,BBB
3,CCC
4,DDD
5,EEEYou can also use JDBC connector to connect Vertica and export using any programming language. You can read more about this on my other post How to Connect Vertica Database using JDBC Driver?
Hope this helps 🙂