Configure a BigQuery dataset

Application Design Center lets you create and configure BigQuery datasets for enterprise data analytics. The dataset components and parameters are based on the terraform-google-bigquery Terraform module on GitHub.

Component connections

The following table includes the components that you can connect to a BigQuery dataset, and the resulting updates to your application and its generated Terraform code.

Connected component Application updates Background information

Compute Engine instance template

  • The Compute Engine instances can read from and write data to the BigQuery dataset.
  • The BigQuery resource metadata is added to the Compute Engine instance template.
  • The roles/bigquery.dataEditor role is granted to the Compute Engine instance template service account.
Instance templates
Service account
  • The service account can be used by services, such as Cloud Run, to read and modify data in the BigQuery dataset.
  • The roles/bigquery.dataEditor role is granted to the service account.
BigQuery Identity and Access Management (IAM) roles and permissions
Cloud Run
  • The Cloud Run service can interact with the BigQuery dataset.
  • The BigQuery resource metadata is added to the Cloud Run environment variables.
  • The roles/bigquery.dataEditor role is granted to the Cloud Run service account.
What is Cloud Run

Pub/Sub

  • The Pub/Sub subscription can write messages to a table in the BigQuery dataset.
  • The BigQuery dataset and table information is added to the Pub/Sub subscription fields.
BigQuery subscriptions

Required configuration parameters

If your template includes a BigQuery component, you must configure the following parameters before you deploy.

Parameter name Description and constraints Background information

Dataset ID

id Introduction to datasets

Project ID

The project where you want to deploy the BigQuery dataset and table.

Configure components

Optional configuration parameters

The following parameters are optional. To display advanced parameters, in the Configuration area, select Show advanced fields.

Feature Parameter name Description and constraint information Background information
Tables

Table ID

id Introduction to tables
Tables

Description

description Introduction to tables
Tables

Table Name

friendlyName Table naming
Tables

Schema

schema Specifying a schema
Tables

Clustering

clustering Introduction to clustered tables
Tables

Require Partition Filter

requirePartitionFilter Set partition filter requirements
Tables: Time Partitioning

Expiration MS

expirationMs Set the partition expiration
Tables: Time Partitioning

Field

field Create a time-unit column-partitioned table
Tables: Time Partitioning

Type

type Create a time-unit column-partitioned table
Tables: Range Partitioning

Field

field Set partition filter requirements
Tables: Range Partitioning

Range Start

range.start Integer range partitioning
Tables: Range Partitioning

Range End

range.end Integer range partitioning
Tables: Range Partitioning

Range Interval

range.interval Integer range partitioning
Tables

Expiration Time

expirationTime Update a table's expiration time
Tables

Deletion Protection

Select the checkbox to prevent this table from being deleted by Terraform. If not selected, the component-level Deletion Protection setting is used.

deletion_protection
Tables: Labels

Key

labels Add labels to tables and views
Tables: Labels

Value

labels Add labels to tables and views

Dataset Name

friendlyName Name datasets

Description

description Update dataset description

Region

location BigQuery locations

Delete Contents on Destroy

Select to delete all tables in the dataset when destroying the resource. If not selected, destroying the resource fails if tables are present.

delete_contents_on_destroy

Deletion Protection

When selected, a terraform apply or terraform destroy that would delete tables fails. When not selected, tables can be deleted. You can override this setting using the deletion protection setting for each table.

deletion_protection

Default Table Expiration MS

defaultTableExpirationMs Update default table expiration times

Default Partition Expiration MS

defaultPartitionExpirationMs Update default partition expiration times

Max Time Travel Hours

maxTimeTravelHours Update time travel windows

Storage Billing Model

StorageBillingModel Storage billing models

Encryption Key

kmsKeyName Encryption at rest
Dataset Labels

Key

labels Add labels to datasets
Dataset Labels

Value

value Add labels to datasets
Resource Tags

Key

resourceTags Tag definitions and identifiers
Resource Tags

Value

resourceTags Tag definitions and identifiers
Views

View ID

id View naming
Views

Description

description Introduction to views
Views

Query

query Updating a view's SQL query
Views

Use Legacy SQL

useLegacySql Migrating to GoogleSQL
Views: Labels

Key

labels Add labels to tables and views
Views: Labels

Value

labels Add labels to tables and views
Materialized Views

View ID

id View naming
Materialized Views

Description

description Introduction to materialized views
Materialized Views

Query

query Materialized view query support
Materialized Views

Enable Refresh

enableRefresh Refresh materialized views
Materialized Views

Refresh Interval MS

refreshIntervalMs Set the refresh frequency cap
Materialized Views

Clustering

fields[] Cluster materialized views
Materialized Views: Time Partitioning

Expiration MS

expirationMs Set the partition expiration
Materialized Views: Time Partitioning

Field

field Create a time-unit column-partitioned table
Materialized Views: Time Partitioning

Type

type Create a time-unit column-partitioned table
Materialized Views: Time Partitioning

Require Partition Filter

requirePartitionFilter Set partition filter requirements
Materialized Views: Range Partitioning

Field

field Partitioned materialized views
Materialized Views: Range Partitioning

Range Start

range.start Integer range partitioning
Materialized Views: Range Partitioning

Range End

range.end Integer range partitioning
Materialized Views: Range Partitioning

Range Interval

range.interval Integer range partitioning
Materialized Views

Expiration Time

expirationTime Updating a view's expiration time
Materialized Views

Max Staleness

maxStaleness Use materialized views with max_staleness option
Materialized Views: Labels

Key

labels Add labels to tables and views
Materialized Views: Labels

Value

labels Add labels to tables and views
External Tables

Table ID

id Introduction to external tables
External Tables

Description

A description of the external table. description
External Tables

Autodetect

autodetect Use the autodetect flag
External Tables

Compression

compression Loading compressed and uncompressed data
External Tables

Ignore Unknown Values

ignoreUnknownValues Create a table definition file for an external data source
External Tables

Max Bad Records

maxBadRecords Create a table definition file for an external data source
External Tables

Schema

schema Create a table definition file for an external data source
External Tables

Source Format

sourceFormat Create a table definition file for an external data source
External Tables

Source URIs

sourceUris Create a table definition file for an external data source
External Tables: CSV Options

Quote

quote Loading CSV data from Cloud Storage
External Tables: CSV Options

Allow Jagged Rows

allowJaggedRows Loading CSV data from Cloud Storage
External Tables: CSV Options

Allow Quoted Newlines

allowQuotedNewlines Loading CSV data from Cloud Storage
External Tables: CSV Options

Encoding

encoding Encoding
External Tables: CSV Options

Field Delimiter

FieldDelimiter Field delimiters
External Tables: CSV Options

Skip Leading Rows

skipLeadingRows CSV options
External Tables: Google Sheets Options

Range

range Create Google Drive external tables
External Tables: Google Sheets Options

Skip Leading Rows

skipLeadingRows Create Google Drive external tables
External Tables: Hive Partitioning Options

Mode

mode Detection modes
External Tables: Hive Partitioning Options

Source URI Prefix

sourceUriPrefix Use externally partitioned data
External Tables

Expiration Time

expirationTime Update a table's expiration time
External Tables

Max Staleness

maxStaleness Manage table staleness
External Tables

Deletion Protection

Select the checkbox to prevent this table from being deleted by Terraform. If not selected, the component-level Deletion Protection setting is used.

deletion_protection
External Tables: Labels

Key

labels Add labels to tables and views
External Tables: Labels

Value

labels Add labels to tables and views
Routines

Routine ID

routineId Manage routines
Routines

Routine Type

routineType RoutineType
Routines

Language

language Language
Routines

Definition Body

definitionBody View the body of a routine
Routines

Return Type

returnType StandardSqlDataType
Routines

Description

A description of the routine. description
Routines: Arguments

Name

The name of the argument. name
Routines: Arguments

Data Type

dataType StandardSqlDataType
Routines: Arguments

Argument Kind

argumentKind ArgumentKind
Routines: Arguments

Mode

mode Mode