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

&#32;

!

Exclamation Mark

33

41

21

100001

&#33;

&excl;

#

Hash or Number

35

43

23

100011

&#35;

&num;

$

Dollar Sign

36

44

24

100100

&#36;

&dollar;

%

Percentage

37

45

25

100101

&#37;

&percnt;

&

Ampersand

38

46

26

100110

&#38;

&amp;

(

Left Parenthesis

40

50

28

101000

&#40;

&lpar;

)

Right Parenthesis

41

51

29

101001

&#41;

&rpar;

*

Asterisk

42

52

2A

101010

&#42;

&ast;

+

Plus Sign

43

53

2B

101011

&#43;

&plus;

,

Comma

44

54

2C

101100

&#44;

&comma;

/

Slash

47

57

2F

101111

&#47;

&sol;

;

Semicolon

59

73

3B

111011

&#59;

&semi;

<

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

&#0;

SOH

Start of Heading

1

1

1

1

&#1;

STX

Start of Text

2

2

2

10

&#2;

ETX

End of Text

3

3

3

11

&#3;

EOT

End of Transmission

4

4

4

100

&#4;

ENQ

Enquiry

5

5

5

101

&#5;

ACK

Acknowledge

6

6

6

110

&#6;

BEL

Bell

7

7

7

111

&#7;

BS

Backspace

10

8

8

1000

&#8;

HT

Horizontal Tab

11

9

9

1001

&#9;

VT

Vertical Tab

13

11

0B

1011

&#11;

FF

NP Form Feed, New Page

14

12

0C

1100

&#12;

SO

Shift Out

16

14

0E

1110

&#14;

SI

Shift In

17

15

0F

1111

&#15;

DLE

Data Link Escape

20

16

10

10000

&#16;

DC1

Device Control 1

21

17

11

10001

&#17;

DC2

Device Control 2

22

18

12

10010

&#18;

DC3

Device Control 3

23

19

13

10011

&#19;

DC4

Device Control 4

24

20

14

10100

&#20;

NAK

Negative Acknowledge

25

21

15

10101

&#21;

SYN

Synchronous Idle

26

22

16

10110

&#22;

ETB

End of Transmission Block

27

23

17

10111

&#23;

CAN

Cancel

30

24

18

11000

&#24;

EM

End of Medium

31

25

19

11001

&#25;

SUB

Substitute

32

26

1A

11010

&#26;

ESC

Escape

33

27

1B

11011

&#27;

FS

File Separator

34

28

1C

11100

&#28;

GS

Group Separator

35

29

1D

11101

&#29;

RS

Record Separator

36

30

1E

11110

&#30;

US

Unit Separator

37

31

1F

11111

&#31;

DEL

Delete

177

127

7F

1111111

&#127;

Unsupported ASCII Field Delimiters

ASCII

Character

Description

Octal

Hex

Binary

HTML Code

HTML Name

10

LF

NL Line Feed, New Line

12

0A

1010

&#10;

13

CR

Carriage Return

15

0D

1101

&#13;

34

Double Quote

42

22

100010

"

&quot;

45

-

Minus Sign

55

2D

101101

-

&minus;

46

.

Period

56

2E

101110

.

&period;

48

0

Zero

60

30

110000

&#48;

49

1

Number One

61

31

110001

&#49;

50

2

Number Two

62

32

110010

&#50;

51

3

Number Three

63

33

110011

&#51;

52

4

Number Four

64

34

110100

&#52;

53

5

Number Five

65

35

110101

&#53;

54

6

Number Six

66

36

110110

&#54;

55

7

Number Seven

67

37

110111

&#55;

56

8

Number Eight

70

38

111000

&#56;

57

9

Number Nine

71

39

111001

&#57;

58

:

Colon

72

3A

111010

&#58;

&colon;

92

\

Backslash

134

5C

01011100

&#92;

78

N

N

116

4E

1001110

&#78;

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.