Export statements in GoogleSQL
EXPORT DATA
statement
The EXPORT DATA
statement exports the results of a query to an external
storage location. You can export to the following services:
- Cloud Storage
- Amazon Simple Storage Service (Amazon S3)
- Spanner
- Bigtable
- Pub/Sub
Syntax
EXPORT DATA [WITH CONNECTION connection_name] OPTIONS (export_option_list) AS query_statement
Arguments
connection_name
: Specifies a connection that has credentials for accessing the Amazon S3 data. Specify the connection name in the formPROJECT_ID.LOCATION.CONNECTION_ID
. If the project ID or location contains a dash, enclose the connection name in backticks (`
). Connections aren't required to export to Google Cloud services.export_option_list
: Specifies a list of options for the export operation, including the URI of the destination. For more information, see the following sections:query_statement
: A SQL query. The query result is exported to the external destination. The query can't reference metatables, includingINFORMATION_SCHEMA
views, system tables, or wildcard tables.
Export to Cloud Storage or Amazon S3
You can export BigQuery data to Cloud Storage or Amazon S3 in Avro, CSV, JSON, and Parquet formats. For more information about exporting to Cloud Storage, see Export table data to Cloud Storage.
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.
You are not billed for the export operation, but you are billed for running the query and for storing data in Cloud Storage or or Amazon S3. For more information, see Cloud Storage pricing or Amazon S3 pricing.
Cloud Storage and Amazon S3 export option list
The option list specifies options for exporting to Cloud Storage or
Amazon S3. Specify the option list in the following
format: NAME=VALUE, ...
Options | |
---|---|
compression |
Specifies a compression format. If not specified, the exported files
are uncompressed. Supported values include: |
field_delimiter |
The delimiter used to separate fields. Default: Applies to: CSV. |
format |
Required. The format of the exported data. Supported values include:
|
header |
If Applies to: CSV. |
overwrite |
If Note: When |
uri |
Required. The destination URI for the export. The Examples: |
use_avro_logical_types |
Whether to use appropriate AVRO logical types when exporting
Applies to: AVRO. For more information, see Avro export details. |
Examples
The following examples show common use cases for exporting to Cloud Storage or Amazon S3.
Export data to Cloud Storage in 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
Export data to Cloud Storage in 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
Export data to Cloud Storage in Parquet format
The following example exports data to Parquet format. It includes the option to overwrite the destination location.
EXPORT DATA OPTIONS( uri='gs://bucket/folder/*', format='PARQUET', overwrite=true) AS SELECT field1, field2 FROM mydataset.table1 ORDER BY field1 LIMIT 10
Export data to Amazon S3 in JSON format
The following example exports query results that run against a BigLake table based on Amazon S3 to your Amazon S3 bucket:
EXPORT DATA WITH CONNECTION myproject.us.myconnection OPTIONS( uri='s3://bucket/folder/*', format='JSON', overwrite=true) AS SELECT field1, field2 FROM mydataset.table1 ORDER BY field1 LIMIT 10
Export to Bigtable
You can export BigQuery data to a Bigtable table
by using the EXPORT DATA
statement. For Bigtable export
examples and configuration options, see
Export data to Bigtable.
You are not billed for the export operation, but you are billed for running the query and for storing data in Bigtable. For more information, see Bigtable pricing.
Bigtable export option list
The option list specifies options for exporting to Bigtable.
Specify the option list in the following format: NAME=VALUE
, ...
Options | |
---|---|
format |
Required. When exporting to Bigtable, the value must
always be |
bigtable_options |
JSON string containing configurations related to mapping exported
fields to Bigtable columns families and columns. For more
information, see Configure exports with |
overwrite |
If |
truncate |
If |
uri |
Required. The destination URI for the
export. We recommend specifying an app profile for traffic routing and
visibility at monitoring dashboards provided by
Bigtable. The |
auto_create_column_families |
If |
Example
The following example exports data to a Bigtable table. Data in
field1
becomes a row key in Bigtable destination table. The
fields field2
, field3
and field4
are written as columns cbtFeld2
,
cbtField3
and cbtField4
into column family column_family
.
EXPORT DATA OPTIONS ( uri="https://bigtable.googleapis.com/projects/my-project/instances/my-instance/tables/my-table", format="CLOUD_BIGTABLE", bigtable_options="""{ "columnFamilies" : [ { "familyId": "column_family", "columns": [ {"qualifierString": "cbtField2", "fieldName": "field2"}, {"qualifierString": "cbtField3", "fieldName": "field3"}, {"qualifierString": "cbtField4", "fieldName": "field4"}, ] } ] }""" ) AS SELECT CAST(field1 as STRING) as rowkey, STRUCT(field2, field3, field4) as column_family FROM `bigquery_table`
Export to Pub/Sub
You can export BigQuery data to a Pub/Sub topic
by using the EXPORT DATA
statement in a
continuous query. For
more information about Pub/Sub configuration options, see
Export data to Pub/Sub.
For information about the costs involved with exporting to Pub/Sub by using a continuous query, see Costs.
Pub/Sub export option list
The option list specifies options for exporting to Pub/Sub.
Specify the option list in the following format: NAME=VALUE
, ...
Options | |
---|---|
format |
Required. When exporting to Pub/Sub, the value must
always be |
uri |
Required. The destination URI for the
export. The |
Example
The following example shows a continuous query that filters data from a BigQuery table that is receiving streaming taxi ride information, and publishes the data to a Pub/Sub topic in real time:
EXPORT DATA OPTIONS ( format = 'CLOUD_PUBSUB', uri = 'https://pubsub.googleapis.com/projects/myproject/topics/taxi-real-time-rides') AS ( SELECT TO_JSON_STRING( STRUCT( ride_id, timestamp, latitude, longitude)) AS message FROM `myproject.real_time_taxi_streaming.taxi_rides` WHERE ride_status = 'enroute' );
Export to Spanner
To provide feedback or request support for this feature, send email to bq-cloud-spanner-federation-preview@google.com.
You can export data from a BigQuery table to a
Spanner table by using the EXPORT DATA
statement.
Spanner export option list
The option list specifies options for the export operation. Specify the option
list in the following format: NAME=VALUE, ...
Options | |
---|---|
format |
Required. To export data from BigQuery to
Spanner, the value must always be
|
uri |
Required. The destination URI for the export. For Spanner,
the URI must be provided in the following format:
|
spanner_options |
Required. A JSON string containing configurations related to mapping exported
fields to Spanner column families and columns. For more
information, see
Configure exports with |
Examples
Export data to Spanner
The following example exports data to a Spanner table:
EXPORT DATA OPTIONS ( uri="https://spanner.googleapis.com/projects/my-project/instances/my-instance/databases/my-database", format="CLOUD_SPANNER", spanner_options="""{ "table": "my_table" }""" ) AS SELECT * FROM `bigquery_table`
For more Spanner export examples and configuration options, see Export data to Spanner.