Create BigQuery subscriptions

This document describes how to create a BigQuery subscription. You can use the Google Cloud console, the Google Cloud CLI, the client library, or the Pub/Sub API to create a BigQuery subscription.

Before you begin

Before reading this document, ensure that you're familiar with the following:

• How subscriptions work.

• The workflow for BigQuery subscriptions.

• How to configure a dead letter topic to handle message failures.

In addition to your familiarity with Pub/Sub and BigQuery, ensure that you meet the following prerequisites before you create a BigQuery subscription:

• A BigQuery table exists. Alternatively, you can create one when you create the BigQuery subscription as described in the later sections of this document.

• Compatibility between the schema of the Pub/Sub topic and the BigQuery table. If you add a non-compatible BigQuery table, you get a compatibility-related error message. For more information, see Schema compatibility.

Required roles and permissions

To get the permissions that you need to create a BigQuery subscription, ask your administrator to grant you the Pub/Sub Editor (roles/pubsub.editor) IAM role on the project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery subscription. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to create a BigQuery subscription:

• pubsub.subscriptions.create on the project
• pubsub.topics.attachSubscription on the topic

You might also be able to get these permissions with custom roles or other predefined roles.

Cross-project subscriptions

If you create a subscription in one project for a topic in another project, you must have pubsub.subscriptions.create permission on the project in which you are creating the subscription, and pubsub.topics.attachSubscription permission on the topic.

Grant IAM roles to the service account

Pub/Sub uses an Identity and Access Management (IAM) service account to access Google Cloud resources. By default, it uses the Pub/Sub service agent (service-PROJECT_NUMBER@gcp-sa-pubsub.iam.gserviceaccount.com).

To enable Pub/Sub to write to a BigQuery table, the service account requires the BigQuery Data Editor (roles/bigquery.dataEditor) role. You can give the service account permissions either for the project or the table, as follows:

Use a custom service account

By granting the BigQuery Data Editor role to the Cloud Pub/Sub service account, any user who has permission to create a subscription in your project can write to the BigQuery table. If you want to provide more granular permissions, configure a user-managed service account instead.

The following permissions are required to configure a user-managed service account to write to BigQuery:

• The user-managed service account must have the BigQuery Data Editor role.

• The Cloud Pub/Sub service account must have the iam.serviceAccounts.getAccessToken permission on the user-managed service account.

• The user creating the subscription must have the iam.serviceAccounts.actAs permission on the user-managed service account.

When you create the subscription, specify the user-managed service account as the subscription service account.

BigQuery subscription properties

BigQuery subscriptions support all of the common subscription properties. The following sections describe properties that are specific to BigQuery subscriptions.

Use topic schema

This option lets Pub/Sub use the schema of the Pub/Sub topic to which the subscription is attached. In addition, Pub/Sub writes the fields in messages to the corresponding columns in the BigQuery table.

When you use this option, remember to check the following additional requirements:

• The fields in the topic schema and the BigQuery schema must have the same names and their types must be compatible with each other.

• Any optional field in the topic schema must also be optional in the BigQuery schema.

• Required fields in the topic schema don't need to be required in the BigQuery schema.

• If there are BigQuery fields that are not present in the topic schema, these BigQuery fields must be in mode NULLABLE.

• If the topic schema has additional fields that are not present in the BigQuery schema and these fields can be dropped, select the option Drop unknown fields.

• You can select only one of the subscription properties, Use topic schema or Use table schema.

If you don't select the Use topic schema or Use table schema option, ensure that the BigQuery table has a column called data of type BYTES, STRING, or JSON. Pub/Sub writes the message to this BigQuery column.

You might not see changes to the Pub/Sub topics schema or BigQuery table schema take effect immediately with messages written to the BigQuery table. For example, if the Drop unknown fields option is enabled and a field is present in the Pub/Sub schema, but not the BigQuery schema, messages written to the BigQuery table might still not contain the field after adding it to the BigQuery schema. Eventually, the schemas synchronize and subsequent messages include the field.

When you use the Use topic schema option for your BigQuery subscription, you can also take advantage of BigQuery change data capture (CDC). CDC updates your BigQuery tables by processing and applying changes to existing rows.

To learn more about this feature, see Stream table updates with change data capture.

To learn how to use this feature with BigQuery subscriptions, see BigQuery change data capture.

Use table schema

This option lets Pub/Sub use the schema of the BigQuery table to write the fields of a JSON message to the corresponding columns. When you use this option, remember to check the following additional requirements:

• The names of each column in the BigQuery table must only contain letters (a-z, A-Z), numbers (0-9), or underscores (_).

• Published messages must be in JSON format.

  If a BigQuery table column has the JSON data type, the corresponding field in your Pub/Sub message must be valid, JSON in an escaped string. For example, for a column named myData, the message field must be "myData": "{\"key\":\"value\"}". BigQuery rejects messages that don't contain valid JSON.

• The following JSON conversions are supported:

  JSON Type	BigQuery Data Type
  string	NUMERIC, BIGNUMERIC, DATE, TIME, DATETIME, or TIMESTAMP
  number	NUMERIC, BIGNUMERIC, DATE, TIME, DATETIME, or TIMESTAMP

  • When using number to DATE, DATETIME, TIME, or TIMESTAMP conversions, the number must adhere to the supported representations.
  • When using