How to Export Greenplum Query Results into CSV?

  • Post author:
  • Post last modified:May 9, 2019
  • Post category:Greenplum
  • Reading time:7 mins read

In a data warehouse environment, most of the tables are secured. The database administrator will allow only authorized used to access records from the tables. The decision makers analyze the flat files created from the database tables to come up with business-critical decision. Saving results of a query or a database table in any relational database is one of the basic and important work. The created flat files or CSV files then be transported using any mode of electronic transport such as email, FTP, SFTP, etc. in this article, we will check how to export Greenplum query results into CSV flat file.

How to Export Greenplum Query Results into CSV?

As we know the importance of creating CSV file in previous section, now let us check how to export a Greenplum query results to CSV flat file?

There are many methods that you can use to export PostgreSQL Greenplum table or results into a flat file. I will discuss commonly used methods in this article. i.e.

  • Use COPY commands to export results and tables.
  • Use psql Commands to export query results.

Related Readings:

Now let us discuss above methods in details with an example.

COPY Command to Export Greenplum Table into CSV

The COPY command in Greenplum is one of the widely used command to import and export tables. The COPOY command in Greenplum database is one of the easiest way to export Greenplum table to CSV flat file format.

Syntax

Below is the COPY command syntax that you can use to export Greenplum table:

COPY table_name TO '/paht/export.csv'  CSV HEADER;

Example

Below example demonstrates the COPY command to export Greenplum table in CSV format:

template1=# COPY export_test TO '/home/gpadmin/export_test.csv'  CSV HEADER;
COPY 6
template1=#

Check output:

[gpadmin@server-d861 ~]$ cat export_test.csv
id,name,city
101,black2,LON
100,black,LON
103,white2,BEN
102,white,BEN
105,Rum2,BEN
104,Rum,BEN
[gpadmin@server-d861 ~]$

COPY Command to Export Query Output into CSV

Not only table, you can also export result of SQL query using COPY command.

Syntax

Below is the COPY command syntax that you can use to export query results set:

COPY SQL_statement TO '/paht/export.csv'  CSV HEADER;

Example

Below example demonstrates the COPY command to export SQL query results set into CSV format:

template1=# COPY (Select * from export_test) TO '/home/gpadmin/export_test1.csv'  CSV HEADER;
COPY 6
template1=#

Check file content:

[gpadmin@server-d861 ~]$ cat export_test1.csv
id,name,city
100,black,LON
102,white,BEN
104,Rum,BEN
101,black2,LON
103,white2,BEN
105,Rum2,BEN
[gpadmin@server-d861 ~]$

Greenplum psql Commands to Export Query Results

The PostgreSQL command line interface psql provides lot of options to export SQL query result sets into CSV flat file.

Exporting data using psql 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.

psql Options to Export Greenplum Data to CSV File

Below psql options can be used to export Greenplum result sets to csv format:

Command-line Option Description
-A or –no-align unaligned table output mode
-t or –tuples-only print rows only
-F or –field-separator=STRING set field separator
-o or –output=FILENAME send query results to file
-c or –command=COMMAND run only single command

These options can be set either from within an interactive psql session, or through command-line arguments to the psql command.

Greenplum psql Commands to Export Query Results Example

Below is the psql command line options example to export query results set data into a flat file:

[gpadmin@server-d861 ~]$ psql -d template1 -h 192.168.200.185 -F',' -A -t -c 'Select * from export_test' -o '/home/gpadmin/export_test2.csv'

Check file content:

[gpadmin@server-d861 ~]$ cat export_test2.csv
101,black2,LON
103,white2,BEN
105,Rum2,BEN
100,black,LON
102,white,BEN
104,Rum,BEN
[gpadmin@server-d861 ~]$

You can also use JDBC connector to connect Greenplum and export using any programming language. You can read more about this on my other post: How to Connect Greenplum using JDBC Driver from Python?

Hope this helps ?

Tags: