COPY TO

The COPY TO statement can be used to export data from your BLUE cluster by exporting tables or query results to your cloud storage.

Note

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

Syntax

 COPY { [<schema_name>].<table_name> [ ( <column_name> [, ... ] ) ] | <query> }
   TO [FOREIGN DATA] WRAPPER <fdw_name>

     OPTIONS
     (
        [ <copy_to_option> [, ...] ]
     )

 fdw_name ::= csv_fdw | parquet_fdw | orc_fdw

 schema_name ::= identifier

 table_name ::= identifier

 copy_to_option ::=

    LOCATION = { S3 URI | HDFS URI | GCP URI }

    | DELIMITER = '<delimiter>'

    | RECORD_DELIMITER = '<record_delimiter>'

    | HEADER = { true | false }

    | AWS_ID = 'AWS ID'

    | AWS_SECRET = 'AWS Secret'

    | MAX_FILE_SIZE = '<size_in_bytes>'

    | ENFORCE_SINGLE_FILE = { true | false }


delimiter ::= string

record delimiter ::= string

AWS ID ::= string

AWS Secret ::= string

Elements

Parameter	Description
[schema_name].table_name	Name of the table to be exported
query	An SQL query that returns a table result, or a table name
fdw_name	The name of the Foreign Data Wrapper to use Supported FDWs are: csv_fdw orc_fdw avro_fdw parquet_fdw
LOCATION	A GCP, S3, HDFS, or local file system path. For example: GCP: gs://blue_docs/nba.csv S3: s3://sqream-docs/nba.csv HDFS: hdfs://my-namenode:8020/foo.csv
HEADER	Supported only for CSV. The CSV file will contain the file column header rows
DELIMITER	Supported only for CSV. Specifies a character or string with which to separate fields (columns) for each row of the file. The default is a comma character (,)
RECORD_DELIMITER	Supported only for CSV. Specifies s character or string with which to separate records in a data set
AWS_ID, AWS_SECRET	Specifies the authentication details for secured S3 buckets
MAX_FILE_SIZE	Sets the maximum file size (bytes). Default value: 16*2^20 (16MB)
ENFORCE_SINGLE_FILE	Enforces the maximum file size (bytes). Default value: true Permitted values: true - creates one file of unlimited size. When set to true, the single file size is not limited by the MAX_FILE_SIZE setting false - permits creating several files together limited by the MAX_FILE_SIZE. When set to false, the combined file sizes cannot exceed the MAX_FILE_SIZE

Field Delimiters

Field delimiters are characters used to separate fields within a data file. In the context of the COPY TO command, they specify how data should be separated when exporting data from a database table to a file.

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'.

Note

Delimiters are only applicable to the CSV file format.

Printable ASCII Characters

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

Character	Description	ASCII	Octal	Hex	Binary	HTML Code	HTML Name
(Space)	Space	32	40	20	100000	 
!	Exclamation Mark	33	41	21	100001	!	!
#	Hash or Number	35	43	23	100011	#	#
$	Dollar Sign	36	44	24	100100	$	$
%	Percentage	37	45	25	100101	%	%
&	Ampersand	38	46	26	100110	&	&
(	Left Parenthesis	40	50	28	101000	(	(
)	Right Parenthesis	41	51	29	101001	)	)
*	Asterisk	42	52	2A	101010	*	*
+	Plus Sign	43	53	2B	101011	+	+
,	Comma	44	54	2C	101100	,	,
/	Slash	47	57	2F	101111	/	/
;	Semicolon	59	73	3B	111011	&#59;	;
<	Less Than	60	74	3C	111100	&#60;	&lt;
=	Equals Sign	61	75	3D	111101	&#61;	&equals;
>	Greater Than	62	76	3E	111110	&#62;	&gt;
?	Question Mark	63	77	3F	111111	&#63;	&quest;
@	At Sign	64	100	40	1000000	&#64;	&commat;
[	Left Square Bracket	91	133	5B	1011011	&#91;	&lsqb;
\	Backslash	92	134	5C	1011100	&#92;	&bsol;
]	Right Square Bracket	93	135	5D	1011101	&#93;	&rsqb;
^	Caret or Circumflex	94	136	5E	1011110	&#94;	&hat;
_	Underscore	95	137	5F	1011111	&#95;	&lowbar;
`	Grave Accent	96	140	60	1100000	&#96;	&grave;
{	Left Curly Bracket	123	173	7B	1111011	&#123;	&lcub;
|	Vertical Bar	124	174	7C	1111100	&#124;	&verbar;
}	Right Curly Bracket	125	175	7D	1111101	&#125;	&rcub;
~	Tilde	126	176	7E	1111110	&#126;	&tilde;
65	A	A	101	41	1000001	&#65;
66	B	B	102	42	1000010	&#66;
67	C	C	103	43	1000011	&#67;
68	D	D	104	44	1000100	&#68;
69	E	E	105	45	1000101	&#69;
70	F	F	106	46	1000110	&#70;
71	G	G	107	47	1000111	&#71;
72	H	H	110	48	1001000	&#72;
73	I	I	111	49	1001001	&#73;
74	J	J	112	4A	1001010	&#74;
75	K	K	113	4B	1001011	&#75;
76	L	L	114	4C	1001100	&#76;
77	M	M	115	4D	1001101	&#77;
79	O	O	117	4F	1001111	&#79;
80	P	P	120	50	1010000	&#80;
81	Q	Q	121	51	1010001	&#81;
82	R	R	122	52	1010010	&#82;
83	S	S	123	53	1010011	&#83;
84	T	T	124	54	1010100	&#84;
85	U	U	125	55	1010101	&#85;
86	V	V	126	56	1010110	&#86;
87	W	W	127	57	1010111	&#87;
88	X	X	130	58	1011000	&#88;
89	Y	Y	131	59	1011001	&#89;
90	Z	Z	132	5A	1011010	&#90;
92	\	Backslash	134	5C	01011100	&#92;
97	a	a	141	61	1100001	&#97;
98	b	b	142	62	1100010	&#98;
99	c	c	143	63	1100011	&#99;
100	d	d	144	64	1100100	&#100;
101	e	e	145	65	1100101	&#101;
102	f	f	146	66	1100110	&#102;
103	g	g	147	67	1100111	&#103;
104	h	h	150	68	1101000	&#104;
105	i	i	151	69	1101001	&#105;
106	j	j	152	6A	1101010	&#106;
107	k	k	153	6B	1101011	&#107;
108	l	l	154	6C	1101100	&#108;
109	m	m	155	6D	1101101	&#109;
110	n	n	156	6E	1101110	&#110;
111	o	o	157	6F	1101111	&#111;
112	p	p	160	70	1110000	&#112;
113	q	q	161	71	1110001	&#113;
114	r	r	162	72	1110010	&#114;
115	s	s	163	73	1110011	&#115;
116	t	t	164	74	1110100	&#116;
117	u	u	165	75	1110101	&#117;
118	v	v	166	76	1110110	&#118;
119	w	w	167	77	1110111	&#119;
120	x	x	170	78	1111000	&#120;
121	y	y	171	79	1111001	&#121;
122	z	z	172	7A	1111010	&#122;

Non-Printable ASCII Characters

Character	Description	Octal	ASCII	Hex	Binary	HTML Code
NUL	Null	0	0	0	0	�
SOH	Start of Heading	1	1	1	1	
STX	Start of Text	2	2	2	10	
ETX	End of Text	3	3	3	11	
EOT	End of Transmission	4	4	4	100	
ENQ	Enquiry	5	5	5	101	
ACK	Acknowledge	6	6	6	110	
BEL	Bell	7	7	7	111	
BS	Backspace	10	8	8	1000	
HT	Horizontal Tab	11	9	9	1001		
VT	Vertical Tab	13	11	0B	1011	
FF	NP Form Feed, New Page	14	12	0C	1100	
SO	Shift Out	16	14	0E	1110	
SI	Shift In	17	15	0F	1111	
DLE	Data Link Escape	20	16	10	10000	
DC1	Device Control 1	21	17	11	10001	
DC2	Device Control 2	22	18	12	10010	
DC3	Device Control 3	23	19	13	10011	
DC4	Device Control 4	24	20	14	10100	
NAK	Negative Acknowledge	25	21	15	10101	
SYN	Synchronous Idle	26	22	16	10110	
ETB	End of Transmission Block	27	23	17	10111	
CAN	Cancel	30	24	18	11000	
EM	End of Medium	31	25	19	11001	
SUB	Substitute	32	26	1A	11010	
ESC	Escape	33	27	1B	11011	
FS	File Separator	34	28	1C	11100	
GS	Group Separator	35	29	1D	11101	
RS	Record Separator	36	30	1E	11110	
US	Unit Separator	37	31	1F	11111	
DEL	Delete	177	127	7F	1111111	

Unsupported ASCII Field Delimiters

ASCII	Character	Description	Octal	Hex	Binary	HTML Code	HTML Name
10	LF	NL Line Feed, New Line	12	0A	1010	

13	CR	Carriage Return	15	0D	1101	
34	“	Double Quote	42	22	100010	"	"
45	-	Minus Sign	55	2D	101101	-	−
46	.	Period	56	2E	101110	.	.
48	0	Zero	60	30	110000	0
49	1	Number One	61	31	110001	1
50	2	Number Two	62	32	110010	2
51	3	Number Three	63	33	110011	3
52	4	Number Four	64	34	110100	4
53	5	Number Five	65	35	110101	5
54	6	Number Six	66	36	110110	6
55	7	Number Seven	67	37	110111	7
56	8	Number Eight	70	38	111000	8
57	9	Number Nine	71	39	111001	9
58	:	Colon	72	3A	111010	:	:
92	\	Backslash	134	5C	01011100	\
78	N	N	116	4E	1001110	N

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 parser.

For more information on the datetime format, see Date.

Examples

Exporting Data to Cloud Storage

Saving files to an authenticated S3 bucket without the header row:

COPY
  nba TO
WRAPPER
  csv_fdw
OPTIONS
  (
    LOCATION = 's3://sqream-docs/nba.csv',
    DELIMITER = '\t',
    HEADER = false,
    AWS_ID = 'my_aws_id',
    AWS_SECRET = 'my_aws_secret'
  );

Exporting a table to a CSV file with the HEADER row:

COPY
  nba TO
WRAPPER
  csv_fdw
OPTIONS
  (
    LOCATION = 'gs://blue_docs/nba.csv',
    DELIMITER = '\t',
    HEADER = true
  );

Exporting a table to a TSV file with a HEADER row:

COPY
  nba TO
WRAPPER
  csv_fdw
OPTIONS
  (
    LOCATION = 's3://sqream-docs/nba.csv',
    DELIMITER = '\t',
    HEADER = true
  );

Using Non-Printable ASCII Characters as Delimiters

The following is an example of using non-printable ASCII characters as delimiters:

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'.

COPY
  nba TO
WRAPPER
  csv_fdw
OPTIONS
  (
    LOCATION = 's3://sqream-docs/nba.csv',
    DELIMITER = '\t',
    DELIMITER = E'\017'
  );

COPY
  nba TO
WRAPPER
  csv_fdw
OPTIONS
  (
    LOCATION = 's3://sqream-docs/nba.csv',
    DELIMITER = '\t',
    DELIMITER = E'\011' -- 011 is a tab character
  );

Using the MAX_FILE_SIZE and ENFORCE_SINGLE_FILE parameters

COPY
  nba TO
WRAPPER
  csv_fdw
OPTIONS
  (
    MAX_FILE_SIZE = '250000000',
    ENFORCE_SINGLE_FILE = 'true',
    LOCATION = 's3://sqream-docs/nba.csv'
  );

Permissions

The COPY TO command requires a SELECT permission on every table or schema that is referenced by the statement.