Export data to Pub/Sub (reverse ETL)

Exporting data to Pub/Sub requires using BigQuerycontinuous queries.

This document describes how you can set up reverse extract-transform-load(RETL) fromBigQuery toPub/Sub.You can do this by using theEXPORT DATA statementin acontinuous query toexport data from BigQuery to aPub/Sub topic.

You can use a RETL workflow to Pub/Sub to combineBigQuery's analytics capabilities with Pub/Sub'sasynchronous and scalable global messaging service. This workflow lets youserve data to downstream applications and services in an event-driven manner.

Prerequisites

You mustcreate a service account. Aservice accountis required to run a continuous query that exports results to aPub/Sub topic.

You must create aPub/Sub topicto receive the continuous query results as messages, and aPub/Sub subscriptionthat the target application can use to receive those messages.

Required roles

This section provides information about the roles and permissions required bythe user account that creates the continuous query, and the service account thatruns the continuous query.

User account permissions

To create a job in BigQuery, the user account must have thebigquery.jobs.create IAM permission. Each of the followingIAM roles grants thebigquery.jobs.create permission:

• BigQuery User (roles/bigquery.user)
• BigQuery Job User (roles/bigquery.jobUser)
• BigQuery Admin (roles/bigquery.admin)

To submit a job that runs using a service account, the user account must have theService Account User (roles/iam.serviceAccountUser)role. If you are using the same user account to create the service account,then the user account must have theService Account Admin (roles/iam.serviceAccountAdmin)role. For information on how to limit a user's access to single service account,rather than to all service accounts within a project, seeGrant a single role.

If the user account must enable the APIs required for yourcontinuous query use case, the user account must have theService Usage Admin (roles/serviceusage.serviceUsageAdmin)role.

Service account permissions

To export data from a BigQuery table, the service account musthave thebigquery.tables.export IAM permission. Each of thefollowing IAM roles grants thebigquery.tables.exportpermission:

• BigQuery Data Viewer (roles/bigquery.dataViewer)
• BigQuery Data Editor (roles/bigquery.dataEditor)
• BigQuery Data Owner (roles/bigquery.dataOwner)
• BigQuery Admin (roles/bigquery.admin)

For the service account to access Pub/Sub, you must grant theservice account both of the following IAM roles:

• Pub/Sub Viewer (roles/pubsub.viewer)
• Pub/Sub Publisher (roles/pubsub.publisher)

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

Before you begin

Enable the BigQuery and Pub/Sub APIs.

Enable the APIs

Export to Pub/Sub

Use theEXPORT DATA statementto export data to a Pub/Sub topic:

Export multiple columns to Pub/Sub

If you want to include multiple columns in your output, you can create a structcolumn to contain the column values, and then convert the struct valueto a JSON string by using theTO_JSON_STRING function.The following example exports data from four columns, formatted as a JSONstring:

EXPORTDATAOPTIONS(format='CLOUD_PUBSUB',uri='https://pubsub.googleapis.com/projects/myproject/topics/taxi-real-time-rides')AS(SELECTTO_JSON_STRING(STRUCT(ride_id,timestamp,latitude,longitude))ASmessageFROMAPPENDS(TABLE`myproject.real_time_taxi_streaming.taxi_rides`,-- Configure the APPENDS TVF start_timestamp to specify when you want to-- start processing data using your continuous query.-- This example starts processing at 10 minutes before the current time.CURRENT_TIMESTAMP()-INTERVAL10MINUTE)WHEREride_status='enroute');

Export optimization

If your continuous query job performance appears to be limited byavailable compute resources,try increasing the size of your BigQueryCONTINUOUS slot reservation assignment.

Limitations

• The exported data must consist of a singleSTRING orBYTES column.The column name can be whatever you choose.
• You must use acontinuous query toexport to Pub/Sub.
• You can't pass a schema to a Pub/Sub topic in thecontinuous query.
• You can't export data to a Pub/Sub topic that uses aschema.
• When exporting to Pub/Sub, you can export JSON formatted recordswhere some values areNULL, but you can't export records that consist ofonlyNULL values. You can excludeNULL records from the query results byincluding aWHERE message IS NOT NULL filter in the continuous query.
• When exporting data to a Pub/Sub topic configured with alocational endpoint,the endpoint must be configured within the same Google Cloud regionalboundary as the BigQuery dataset that contains the table youare querying.
• Exported data must not exceedPub/Sub quotas.

Pricing

When you export data in a continuous query, you are billed usingBigQuery capacity compute pricing.To run continuous queries, you must have areservation that uses theEnterprise or Enterprise Plus edition,and areservation assignmentthat uses theCONTINUOUS job type.

After the data is exported, you're charged for using Pub/Sub.For more information, seePub/Sub pricing.