Google Cloud Query

Google Cloud Query

Certified

Execute a BigQuery SQL job

Runs a SQL statement with standard SQL by default, optionally writing into a destination table. Supports cache usage, priority selection, schema updates, and deprecated fetch/store outputs (superseded by fetchType). Uses task-level project, service account, and scopes.

yaml
type: io.kestra.plugin.gcp.bigquery.Query

Create a table with a custom query

yaml
id: gcp_bq_query
namespace: company.team

tasks:
  - id: query
    type: io.kestra.plugin.gcp.bigquery.Query
    destinationTable: "my-project.my_dataset.my_table"
    writeDisposition: WRITE_APPEND
    sql: |
      SELECT
        "hello" as string,
        NULL AS `nullable`,
        1 as int,
        1.25 AS float,
        DATE("2008-12-25") AS date,
        DATETIME "2008-12-25 15:30:00.123456" AS datetime,
        TIME(DATETIME "2008-12-25 15:30:00.123456") AS time,
        TIMESTAMP("2008-12-25 15:30:00.123456") AS timestamp,
        ST_GEOGPOINT(50.6833, 2.9) AS geopoint,
        ARRAY(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS `array`,
        STRUCT(4 AS x, 0 AS y, ARRAY(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS z) AS `struct`

Execute a query and fetch results sets on another task

yaml
id: gcp_bq_query
namespace: company.team

tasks:
  - id: fetch
    type: io.kestra.plugin.gcp.bigquery.Query
    fetch: true
    sql: |
      SELECT 1 as id, "John" as name
      UNION ALL
      SELECT 2 as id, "Doe" as name
  - id: use_fetched_data
    type: io.kestra.plugin.core.debug.Return
    format: |
      {% for row in outputs.fetch.rows %}
      id : {{ row.id }}, name: {{ row.name }}
      {% endfor %}
Properties

Sets whether the job is enabled to create arbitrarily large results

If true the query is allowed to create large results at a slight cost in performance. destinationTable must be provided.

SubTypestring

The clustering specification for the destination table

Possible Values
CREATE_IF_NEEDEDCREATE_NEVER

Create disposition

Whether the job may create the destination table

Sets the default dataset

This dataset is used for all unqualified table names used in the query.

Destination table

Table to receive job output; creation depends on createDisposition

Defaultfalse

Dry run

If true, validates the job and returns statistics without running it

DefaultNONE
Possible Values
STOREFETCHFETCH_ONENONE

Result handling mode

NONE by default. Use FETCH or STORE to make results available to downstream tasks; prefer over deprecated fetch/store flags.

Defaulttrue

Sets whether nested and repeated fields should be flattened

If set to false, allowLargeResults must be true.

The GCP service account to impersonate

Job timeout

Optional max duration; BigQuery may terminate the job when exceeded

Job labels

Defaultfalse

Use legacy SQL

Default false; when true, runs the query with the legacy dialect

Dataset location

Optional BigQuery location for created or targeted resources. Experimental and may change; see BigQuery dataset location documentation.

This is only supported in the fast query path

The maximum number of rows of data to return per page of results. Setting this flag to a small value such as 1000 and then paging through results might improve reliability when the query result set is large. In addition to this limit, responses are also limited to 10 MB. By default, there is no maximum row count, and only the byte limit applies.

Limits the billing tier for this job

Queries that have resource usage beyond this tier will fail (without incurring a charge). If unspecified, this will be set to your project default.

Limits the bytes billed for this job

Queries that will have bytes billed beyond this limit will fail (without incurring a charge). If unspecified, this will be set to your project default.

Reference (ref) of the pluginDefaults to apply to this task.

DefaultINTERACTIVE
Possible Values
INTERACTIVEBATCH

Query priority

INTERACTIVE by default; choose BATCH to queue if slots are unavailable

The GCP project ID

The end range partitioning, inclusive

Range partitioning field for the destination table

The width of each interval

The start of range partitioning, inclusive

Automatic BigQuery retry policy

Optional custom retry policy for retryable BigQuery errors. If unset, uses an exponential backoff starting at 5s, up to 15m, max 10 attempts.

Definitions

Retry with a fixed delay between attempts.

interval*Requiredstring
Formatduration
type*Requiredobject
behaviorstring
DefaultRETRY_FAILED_TASK
Possible Values
RETRY_FAILED_TASKCREATE_NEW_EXECUTION
maxAttemptsinteger
Minimum>= 1
maxDurationstring
Formatduration
warningOnRetryboolean
Defaultfalse

Retry with exponentially increasing delays between attempts.

interval*Requiredstring
Formatduration
maxInterval*Requiredstring
Formatduration
type*Requiredobject
behaviorstring
DefaultRETRY_FAILED_TASK
Possible Values
RETRY_FAILED_TASKCREATE_NEW_EXECUTION
delayFactornumber
maxAttemptsinteger
Minimum>= 1
maxDurationstring
Formatduration
warningOnRetryboolean
Defaultfalse

Retry with a random delay within a configurable range between attempts.

maxInterval*Requiredstring
Formatduration
minInterval*Requiredstring
Formatduration
type*Requiredobject
behaviorstring
DefaultRETRY_FAILED_TASK
Possible Values
RETRY_FAILED_TASKCREATE_NEW_EXECUTION
maxAttemptsinteger
Minimum>= 1
maxDurationstring
Formatduration
warningOnRetryboolean
Defaultfalse
SubTypestring
Default["due to concurrent update","Retrying the job may solve the problem","Retrying may solve the problem"]

Retry message substrings

Case-insensitive substrings that, if found in the error message, trigger an automatic retry

SubTypestring
Default["rateLimitExceeded","jobBackendError","backendError","internalError","jobInternalError"]

Retry reasons

BigQuery error reasons that trigger an automatic retry; evaluated against error reason strings

SubTypestring
Possible Values
ALLOW_FIELD_ADDITIONALLOW_FIELD_RELAXATION

[Experimental] Options allowing the schema of the destination table to be updated as a side effect of the query job

Schema update options are supported in two cases: * when writeDisposition is WRITE_APPEND;

  • when writeDisposition is WRITE_TRUNCATE and the destination table is a partition of a table, specified by partition decorators. For normal tables, WRITE_TRUNCATE will always overwrite the schema.
SubTypestring
Default["https://www.googleapis.com/auth/cloud-platform"]

The GCP scopes to be used

The GCP service account

SQL query

Rendered SQL string to execute; uses standard SQL unless legacySql is true

The time partitioning field for the destination table

DefaultDAY
Possible Values
DAYHOURMONTHYEAR

The time partitioning type specification

Defaultfalse

Sets whether to use BigQuery's legacy SQL dialect for this query

A valid query will return a mostly empty response with some processing statistics, while an invalid query will return the same error it would if it wasn't a dry run.

Sets whether to look for the result in the query cache

The query cache is a best-effort cache that will be flushed whenever tables in the query are modified. Moreover, the query cache is only available when destinationTable is not set

Possible Values
WRITE_TRUNCATEWRITE_TRUNCATE_DATAWRITE_APPENDWRITE_EMPTY

Write disposition

Action when destination exists

The destination table (if one) or the temporary table created automatically

Definitions
datasetstring

The dataset of the table

projectstring

The project of the table

tablestring

The table name

The job id

Map containing the first row of fetched data

Only populated if 'fetchOne' parameter is set to true.

SubTypeobject

List containing the fetched data

Only populated if 'fetch' parameter is set to true.

The size of the rows fetch

Formaturi

The uri of store result

Only populated if 'store' is set to true.

Whether the query result was fetched from the query cache.

The time it took for the query to run.

Unitbytes

The original estimate of bytes processed for the query.

The number of child jobs executed by the query.

Unitrecords

The number of rows affected by a DML statement. Present only for DML statements INSERT, UPDATE or DELETE.

The number of tables referenced by the query.

Unitbytes

The total number of bytes billed for the query.

Unitbytes

The total number of bytes processed by the query.

Unitpartitions

The totla number of partitions processed from all partitioned tables referenced in the job.

The slot-milliseconds consumed by the query.

More Plugins in this Category

NetBox (EE)
NetBox
Enterprise

Enterprise NetBox tasks to create, update, and query devices, IP addresses, and sites over the NetBox API.

Infrastructure

7 tasks

4 blueprints

Clickhouse
Clickhouse

Tasks that connect to ClickHouse via JDBC to run queries and loads.

Data

5 tasks

2 blueprints