Connect
Also in Cloud

gcp_bigquery_select

Executes a SELECT query against BigQuery and creates a message for each row received.

Introduced in version 3.63.0.

Once the rows from the query are exhausted, this input shuts down, allowing the pipeline to gracefully terminate (or the next input in a sequence to execute).

input:
  label: ""
  gcp_bigquery_select:
    project: "" # No default (required)
    credentials_json: ""
    table: "" # No default (required)
    columns: [] # No default (required)
    where: "" # No default (optional)
    auto_replay_nacks: true
    job_labels: {}
    priority: ""
    args_mapping: "" # No default (optional)
    prefix: "" # No default (optional)
    suffix: "" # No default (optional)

Examples

Word counts

Here we query the public corpus of Shakespeare’s works to generate a stream of the top 10 words that are 3 or more characters long:

input:
  gcp_bigquery_select:
    project: sample-project
    table: bigquery-public-data.samples.shakespeare
    columns:
      - word
      - sum(word_count) as total_count
    where: length(word) >= ?
    suffix: |
      GROUP BY word
      ORDER BY total_count DESC
      LIMIT 10
    args_mapping: |
      root = [ 3 ]

Fields

args_mapping

An optional Bloblang mapping that evaluates to an array of values for the ? placeholders in the where field, in order. Each value is sent to BigQuery as a positional query parameter.

Type: string

# Examples:
args_mapping: root = [ "article", now().ts_format("2006-01-02") ]

auto_replay_nacks

Whether to automatically replay rejected messages (negative acknowledgements, or nacks) at the output level. If the cause of rejections persists, leaving this option enabled can result in back pressure.

Set auto_replay_nacks to false to delete rejected messages. Disabling auto replays can greatly improve memory efficiency of high throughput streams, as the original shape of the data is discarded immediately upon consumption and mutation.

Requires version 4.27.0 or later.

Type: bool

Default: true

columns[]

A list of columns to query.

Type: array<string>

credentials_json

The Google Service Account credentials in JSON format (optional). Provide the contents of the credentials file as plain JSON, not Base64-encoded. Use this field to authenticate with Google Cloud services. If this field is empty, the component uses Application Default Credentials. For more information about creating service account credentials, see Google’s service account documentation.

This field contains sensitive information that usually shouldn’t be added to a configuration directly. For more information, see Secrets.

Requires version 4.33.0 or later.

Type: string

Default: ""

job_labels

A map of labels to add to the query job.

Type: object<string>

Default: {}

prefix

Optional GoogleSQL text to add before the SELECT keyword of the generated query, for example a WITH clause.

Type: string

priority

The priority with which to schedule the query.

Requires version 4.7.0 or later.

Type: string

Default: ""

project

The GCP project where the query job runs.

Type: string

suffix

Optional GoogleSQL text to append after the generated query, for example GROUP BY, ORDER BY, or LIMIT clauses.

Type: string

table

The fully-qualified name of the BigQuery table to query.

Type: string

# Examples:
table: bigquery-public-data.samples.shakespeare

where

An optional WHERE clause to add to the query. The args_mapping field populates the placeholder arguments. Placeholders must always be question marks (?).

Type: string

# Examples:
where: type = ? and created_at > ?

# ---

where: user_id = ?