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.

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: CSV, JSON, AVRO.

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 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 table1 ORDER BY field1 LIMIT 10