COPY TO¶

COPY ... TO is a statement that allows writing data from a table or query to a file.

In general, COPY moves data between SQream DB tables and file-system files.

Note

To copy data from a file to a table, see COPY FROM.

Permissions¶

The role must have the SELECT permission on every table or schema that is referenced by the statement.

Syntax¶

copy_to_stmt ::= COPY ( table_ref ) TO 'filepath_spec'
     [ [ WITH ] copy_opt [ ...] ]
;

table_ref ::=
   table_reference
   | query

table_reference ::= identifier

copy_opt ::=
   DELIMITER '{ delimiter }'
   | HEADER
   | AWS_ID '{ AWS ID }'
   | AWS_SECRET '{ AWS secret }'

filepath_spec ::=
   filename
   | S3 URI
   | HDFS URI

Elements¶

Parameter	Description
`query`	An SQL query that returns a table or a table name
`filepath_spec`	A path on the local filesystem, S3, or HDFS URI. For example, `/tmp/foo.csv`, `s3://my-bucket/foo.csv`, or `hdfs://my-namenode:8020/foo.csv`. The local path must be an absolute path that SQream DB can access.
`HEADER`	The CSV file will contain a header line with the names of each column in the file. This option is allowed only when using CSV format.
`DELIMITER`	Specifies the character that separates fields (columns) within each row of the file. The default is a comma character (`,`).
`AWS_ID`, `AWS_SECRET`	Specifies the authentication details for secured S3 buckets

Usage notes¶

Supported field delimiters¶

Printable characters¶

Any printable ASCII character can be used as a delimiter without special syntax. The default CSV field delimiter is a comma (,).

A printable character is any ASCII character in the range 32 - 126.

Non-printable characters¶

A non-printable character (1 - 31, 127) can be used in its octal form.

A tab can be specified by escaping it, for example \t. Other non-printable characters can be specified using their octal representations, by using the E'\000' format, where 000 is the octal value of the character.

For example, ASCII character 15, known as “shift in”, can be specified using E'\017'.

Date format¶

The date format in the output CSV is formatted as ISO 8601 (2019-12-31 20:30:55.123), regardless of how it was parsed initially with COPY FROM date parsers.

Examples¶

Export table to a CSV¶

nba=> COPY nba TO '/home/rhendricks/nba.csv';  -- Standard CSV
executed

$ head -n6 nba.csv
Avery Bradley,Boston Celtics,0,PG,25,6-2,180,Texas,7730337
Jae Crowder,Boston Celtics,99,SF,25,6-6,235,Marquette,6796117
John Holland,Boston Celtics,30,SG,27,6-5,205,Boston University,\N
R.J. Hunter,Boston Celtics,28,SG,22,6-5,185,Georgia State,1148640
Jonas Jerebko,Boston Celtics,8,PF,29,6-10,231,\N,5000000
Amir Johnson,Boston Celtics,90,PF,29,6-9,240,\N,12000000

Export table to a CSV with a header row¶

Use WITH ... to control output options

nba=> COPY nba TO '/home/rhendricks/nba_h.csv' WITH HEADER;  -- CSV with header
executed

$ head -n6 nba_h.csv
Name,Team,Number,Position,Age,Height,Weight,College,Salary
Avery Bradley,Boston Celtics,0,PG,25,6-2,180,Texas,7730337
Jae Crowder,Boston Celtics,99,SF,25,6-6,235,Marquette,6796117
John Holland,Boston Celtics,30,SG,27,6-5,205,Boston University,\N
R.J. Hunter,Boston Celtics,28,SG,22,6-5,185,Georgia State,1148640
Jonas Jerebko,Boston Celtics,8,PF,29,6-10,231,\N,5000000

Export table to a TSV with a header row¶

When combining multiple options, use WITH followed by all options, separated by a space.

nba=> COPY nba TO '/home/rhendricks/nba_h.tsv' WITH HEADER DELIMITER '|';  -- TSV with header
executed

$ head -n6 nba_h.tsv
Name    Team    Number  Position        Age     Height  Weight  College Salary
Avery Bradley   Boston Celtics  0       PG      25      6-2     180     Texas  7730337
Jae Crowder     Boston Celtics  99      SF      25      6-6     235     Marquette       6796117
John Holland    Boston Celtics  30      SG      27      6-5     205     Boston University       \N
R.J. Hunter     Boston Celtics  28      SG      22      6-5     185     Georgia State   1148640
Jonas Jerebko   Boston Celtics  8       PF      29      6-10    231     \N     5000000

Use non-printable ASCII characters as delimiter¶

Non-printable characters can be specified using their octal representations, by using the E'\000' format, where 000 is the octal value of the character.

For example, ASCII character 15, known as “shift in”, can be specified using E'\017'.

nba=> COPY nba TO '/home/rhendricks/nba_shiftin.txt' WITH HEADER DELIMITER E'\017';
executed

nba=> COPY nba TO '/home/rhendricks/nba.tsv' WITH HEADER DELIMITER E'\011'; -- 011 is a tab character
executed

Exporting the result of a query to a CSV¶

nba=> COPY (SELECT "Team", AVG("Salary") FROM nba GROUP BY 1) TO '/home/rhendricks/nba_salaries.csv';
executed

$ head -n5 nba_salaries.csv
Atlanta Hawks,4860196
Boston Celtics,4181504
Brooklyn Nets,3501898
Charlotte Hornets,5222728
Chicago Bulls,5785558

Saving files to an authenticated S3 bucket¶

nba=> COPY (SELECT "Team", AVG("Salary") FROM nba GROUP BY 1)
.     TO 's3://my_bucket/salaries/nba_salaries.csv'
.     WITH AWS_ID 'my_aws_id' AWS_SECRET 'my_aws_secret';
executed

Saving files to an HDFS path¶

nba=> COPY (SELECT "Team", AVG("Salary") FROM nba GROUP BY 1)
.     TO 'hdfs://pp_namenode:8020/salaries/nba_salaries.csv';
executed