Export data to Spanner (reverse ETL)

This document describes how you can set up a reverse extract, transform, andload (reverse ETL) workflow from BigQuery to Spanner. You cando this by using theEXPORT DATA statementto export data from BigQuery data sources, includingIceberg tables, to aSpanner table.

This reverse ETL workflow combines analytic capabilities inBigQuery with low latency and high throughput inSpanner. This workflow lets you serve data to application userswithout exhausting quotas and limits on BigQuery.

Before you begin

• Create aSpanner databaseincluding a table to receive the exported data.

• GrantIdentity and Access Management (IAM) roles that give users thenecessary permissions to perform each task in this document.

• Create anEnterprise or a higher tier reservation.You might reduce BigQuery compute costs when you run one-timeexports to Spanner by setting a baseline slot capacity of zeroand enablingautoscaling.

Required roles

To get the permissions that you need to export BigQuery data to Spanner, ask your administrator to grant you the following IAM roles on your project:

• Export data from a BigQuery table:BigQuery Data Viewer (roles/bigquery.dataViewer)
• Run an extract job:BigQuery User (roles/bigquery.user)
• Check parameters of the Spanner instance:Cloud Spanner Viewer (roles/spanner.viewer)
• Write data to a Spanner table:Cloud Spanner Database User (roles/spanner.databaseUser)

For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Limitations

• This feature is not supported in Assured Workloads.

• The following BigQuery data types don't have equivalents inSpanner and are not supported:

• The maximum size of an exported row cannot exceed 1 MiB.

• Spanner enforces referential integrity during the export.If the target table is a child of another table (INTERLEAVE IN PARENT), or ifthe target table has foreign key constraints, the foreign keys and parent keywill be validated during the export. If an exported row is is written to atable with INTERLEAVE IN PARENT and the parent row doesn't exist, the exportwill fail with "Parent row is missing. Row cannot be written" error. If theexported row is written to a table with foreign key constraints and isreferencing a key that doesn't exist, the export will fail with"Foreign key constraint is violated" error. When exporting to multiple tables,we recommend sequencing the export to ensure that referential integrity willbe maintained through the export. This usually means exporting parent tablesand tables that are referenced by foreign keys before tables that referencethem.

  If the table that is the target of the export has foreign key constraints,or is a child of another table (INTERLEAVE IN PARENT), the parent table mustbe populated before a child table export, and should contain all thecorresponding keys. An attempt to export a child table while a parent tabledoes not have the complete set of relevant keys will fail.

• A BigQuery job, such as an extract job toSpanner, has a maximum duration of 6 hours. For informationabout optimizing large extract jobs, seeExport optimization. Alternatively, consider splittingthe input into individual blocks of data, which may be exported as individualextract jobs.

• Exports to Spanner are only supported for theBigQuery Enterprise or Enterprise Pluseditions. The BigQuery Standard edition and on-demandcompute are not supported.

• You cannot use continuous queries to export to Spanner tableswithauto-generated primary keys.

• You cannot use continuous queries to export to Spanner tablesin a PostgreSQL-dialect database.

• When using continuous queries to export to a Spanner table,ensure that you choose a primary key that doesn't correspond to a monotonicallyincreasing integer in your BigQuery table. Doing so might cause performanceissues in your export. For information about primary keys in Spanner,and ways to mitigate these performance issues, seeChoose a primary key.

Configure exports withspanner_options option

You can use thespanner_options option to specify a destinationSpanner database and table. The configuration is expressed inthe form of a JSON string, as the following example shows:

EXPORTDATAOPTIONS(uri="https://spanner.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID",format='CLOUD_SPANNER',spanner_options="""{      "table": "TABLE_NAME",      "change_timestamp_column": "CHANGE_TIMESTAMP",      "priority": "PRIORITY",      "tag": "TAG",   }""")

Replace the following:

• PROJECT_ID: the name of your Google Cloud project.
• INSTANCE_ID: the name of your database instance.
• DATABASE_ID: the name of your database.
• TABLE_NAME: the name of an existing destination table.
• CHANGE_TIMESTAMP: the name of theTIMESTAMP type columnin the destination Spanner table. This option is usedduring export to track the timestamp of the most recent row update. Whenthis option is specified, the export first performs a read of the row in theSpanner table, to ensure that only the latest row update iswritten. We recommend specifying aTIMESTAMP type column when you run acontinuous export, where the ordering of changes torows with the same primary key is important.
• PRIORITY (optional):priorityof the write requests. Allowed values:LOW,MEDIUM,HIGH. Defaultvalue:MEDIUM.
• TAG (optional):request tagto help identify exporter traffic in Spanner monitoring.Default value:bq_export.

Export query requirements

To export query results to Spanner, the results must meet thefollowing requirements:

• All columns in the result set must exist in the destination table, and theirtypes must match or beconvertible.
• The result set must contain allNOT NULL columns for the destination table.
• Column values must not exceed Spannerdata size limits within tables.
• Any unsupported column types must be converted to one of the supported typesbefore exporting to Spanner.

Type conversions

For ease of use, Spanner exporter automatically applies thefollowing type conversions:

Export data

You can use theEXPORT DATA statementto export data from a BigQuery table into aSpanner table.

The following example exports selected fields from a table that's namedmydataset.table1:

EXPORTDATAOPTIONS(uri="https://spanner.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID",format='CLOUD_SPANNER',spanner_options="""{ "table": "TABLE_NAME" }""")ASSELECT*FROMmydataset.table1;

Replace the following:

• PROJECT_ID: the name of your Google Cloud project
• INSTANCE_ID: the name of your database instance
• DATABASE_ID: the name of your database
• TABLE_NAME: the name of an existing destination table

Export multiple results with the samerowkey value

When you export a result containing multiple rows with the samerowkey value,values written to Spanner end up in the sameSpanner row. Only single matching BigQuery row(there is no guarantee which one) will be present in the Spannerrow set produced by export.

Export continuously

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

To continuously process an export query, seeCreate continuous queries for instructionsandexample code.

Export optimization

To optimize the export of records from BigQuery toSpanner, you can try the following:

• Increase the number of nodes in the Spanner destinationinstance. During the early stages of theexport, increasing the number of nodes in the instance might not immediatelyincrease export throughput. A slight delay can occur whileSpanner performsload-based splitting.With load-based splitting, the export throughput grows and stabilizes. UsingtheEXPORT DATA statement batches data to optimize writes toSpanner. For more information, seePerformance overview.

• SpecifyHIGH priority withinspanner_options. Ifyour Spanner instance hasautoscaling enabled, settingHIGH priority helps ensure that CPU utilization reaches the necessarythreshold to trigger scaling. This allows the autoscaler to add computeresources in response to the export load, which can improve overall exportthroughput.

  Caution: usingHIGH priority can cause significant performance degradation for other workloads served by the same Spanner instance. Consider usingHIGH priority only if the Spanner instance is dedicated to this export, or if other workloads are not sensitive to performance impacts.

  The following example shows a Spanner export command set toHIGH priority:

  EXPORTDATAOPTIONS(uri="https://spanner.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID",format='CLOUD_SPANNER',spanner_options="""{ "table": "TABLE_NAME", "priority": "HIGH" }""")

• Avoid ordering the query results. If the result set contains all primary keycolumns, the exporter automatically sorts the primary keys of thedestination table to streamline writes and minimize contention.

  If the destination table's primary key includes generated columns, add thegenerated columns' expressions to the query to ensure that the exported datais sorted and batched properly.

  For example, in the following Spanner schema,SaleYearandSaleMonth are generated columns that make up the beginning ofthe Spanner primary key:

  CREATETABLESales(SaleIdSTRING(36)NOTNULL,ProductIdINT64NOTNULL,SaleTimestampTIMESTAMPNOTNULL,AmountFLOAT64,-- Generated columnsSaleYearINT64AS(EXTRACT(YEARFROMSaleTimestamp))STORED,SaleMonthINT64AS(EXTRACT(MONTHFROMSaleTimestamp))STORED,)PRIMARYKEY(SaleYear,SaleMonth,SaleId);

  When you export data from BigQuery to aSpanner table with generated columns used in the primarykey, it is recommended, but not required, to include the expressionsfor these generated columns in yourEXPORT DATA query. This letsBigQuery pre-sort the data correctly, which is criticalfor efficient batching and writing to Spanner. The valuesfor the generated columns in theEXPORT DATA statement aren'tcommitted in Spanner, because they areauto-generated by Spanner, but they are used tooptimize the export.

  The following example exports data to a SpannerSales tablewhose primary key uses generated columns. To optimize write performance, thequery includesEXTRACT expressions that match the generatedSaleYear andSaleMonth columns, letting BigQuery pre-sort the databefore export:

  EXPORTDATAOPTIONS(uri="https://spanner.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID",format='CLOUD_SPANNER',spanner_options="""{ "table": "Sales" }""")ASSELECTs.SaleId,s.ProductId,s.SaleTimestamp,s.Amount,-- Add expressions that match the generated columns in the Spanner PKEXTRACT(YEARFROMs.SaleTimestamp)ASSaleYear,EXTRACT(MONTHFROMs.SaleTimestamp)ASSaleMonthFROMmy_dataset.sales_exportASs;

• To prevent long running jobs, export data by partition. Shard yourBigQuery data using a partition key, such as a timestamp inyour query:

  EXPORTDATAOPTIONS(uri="https://spanner.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID",format='CLOUD_SPANNER',spanner_options="""{ "table": "TABLE_NAME", "priority": "MEDIUM" }""")ASSELECT*FROM'mydataset.table1'dWHEREd.timestamp>=TIMESTAMP'2025-08-28T00:00:00Z'ANDd.timestamp<TIMESTAMP'2025-08-29T00:00:00Z';

  This lets the query complete within the 6-hour job runtime. For moreinformation about these limits, see thequery job limits.

• To improve data loading performance, drop the index in theSpanner table where data is imported. Then, recreate it afterthe import completes.

• We recommend starting with one Spanner node (1000 processorunits) and a minimal BigQuery slot reservation. For example,100 slots, or 0 baseline slots with autoscaling. For exports under100 GB, this configuration typically completes within the 6-hour joblimit. For exports larger than 100 GB, increase throughput by scalingup Spanner nodes and BigQuery slotreservations, as needed. Throughput scales at approximately 5 MiB/s pernode.

Pricing

When you export data to Spanner using theEXPORT DATA statement,you are billed usingBigQuery capacity compute pricing.

To export continuously to Spanner using a continuous query, youmust have aBigQuery Enterprise or Enterprise Plus editionslot reservation and areservation assignmentthat uses theCONTINUOUS job type.

BigQuery exports to Spanner that cross regionalboundaries are charged using data extraction rates. For more information, seeBigQuery pricing.To avoid data transfer charges, make sure that your BigQueryexport runs in the same region as the Spannerdefault leader. Continuousquery exports don't support exports that cross regional boundaries.

After the data is exported, you're charged for storing the data inSpanner. For more information,seeSpanner pricing.