Cloud
Also in Self-Managed

gcp_bigquery_select

Executes a SELECT query against BigQuery and replaces messages with the rows returned.

  • Common

  • Advanced

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

Examples

Word count

Given a stream of English terms, enrich the messages with the word count from Shakespeare’s public works:

pipeline:
  processors:
    - branch:
        processors:
          - gcp_bigquery_select:
              project: test-project
              table: bigquery-public-data.samples.shakespeare
              columns:
                - word
                - sum(word_count) as total_count
              where: word = ?
              suffix: |
                GROUP BY word
                ORDER BY total_count DESC
                LIMIT 10
              args_mapping: root = [ this.term ]
        result_map: |
          root.count = this.get("0.total_count")

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") ]

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 Manage Secrets before adding it to your configuration.

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

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 = ?