Other statements in Standard SQL

EXPORT DATA statement

The EXPORT DATA statement exports the results of a query to an external storage location. The storage location must be Cloud Storage.

EXPORT DATA
OPTIONS (export_option_list) AS
query_statement

Where:

• export_option_list specifies a list of options for the export operation, including the URI of the destination.

• query_statement is a SQL query. The query result is exported to the external destination.

Note: You are not billed for the export operation, but you are billed for running the query and for storing data in Cloud Storage. For more information, see Pricing.

Use the format option to specify the format of the exported data. The following limitations apply:

• You cannot export nested and repeated data in CSV format.
• If you export data in JSON format, INT64 data types are encoded as JSON strings to preserve 64-bit precision.

export_option_list

The option list specifies options for the export operation. Specify the option list in the following format: NAME=VALUE, ...

The uri and format options are required. The uri option must be a single-wildcard URI as described in Exporting data into one or more files.

Options
compression	STRING Specifies a compression format. If not specified, the exported files are uncompressed. Supported values include: GZIP, DEFLATE, SNAPPY.
field_delimiter	STRING The delimiter used to separate fields. Default: ',' (comma). Applies to: CSV.
format	STRING The format of the exported data. Supported values include: AVRO, CSV, JSON, PARQUET (Preview).
header	BOOL If true, generates column headers for the first row of each data file. Default: false. Applies to: CSV.
overwrite	BOOL If true, overwrite the destination location. Otherwise, if the destination location is not empty, the statement returns an error. Default: false.
uri	STRING The URI of destination for the export. Example: "gs://bucket/path/file_*.csv"

Examples

Exporting data to CSV format

The following example exports data to a CSV file. It includes options to overwrite the destination location, write header rows, and use ';' as a delimiter.

EXPORT DATA OPTIONS(
  uri='gs://bucket/folder/*.csv',
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=';') AS
SELECT field1, field2 FROM mydataset.table1 ORDER BY field1 LIMIT 10

Exporting data to Avro format

The following example exports data to Avro format using Snappy compression.

EXPORT DATA OPTIONS(
  uri='gs://bucket/folder/*',
  format='AVRO',
  compression='SNAPPY') AS
SELECT field1, field2 FROM mydataset.table1 ORDER BY field1 LIMIT 10