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)
• Azure Blob Storage
• 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 form PROJECT_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:

  • Cloud Storage, Amazon Simple Storage Service (Amazon S3), and Blob Storage export option list
  • Bigtable export option list
  • Pub/Sub export option list
  • Spanner export option list

• query_statement: A SQL query. The query result is exported to the external destination. The query can't reference metatables, including INFORMATION_SCHEMA views, system tables, or wildcard tables.

Export to Cloud Storage, Amazon S3, or Blob Storage

You can export BigQuery data to Cloud Storage, Amazon S3, or Blob Storage 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, Amazon S3, or Blob Storage. For more information, see Cloud Storage pricing, Amazon S3 pricing, or Blob Storage pricing.

Cloud Storage, Amazon S3, and Blob Storage export option list

The option list specifies options for exporting to Cloud Storage, Amazon S3, or Blob Storage. Specify the option list in the following format: NAME=VALUE, ...

Examples

The following examples show common use cases for exporting to Cloud Storage, Amazon S3, or Blob Storage.

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

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

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

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.