Query

Execute BigQuery SQL query in a specific BigQuery database.

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 %}

Dynamic YES

SubType string

Dynamic YES

The clustering specification for the destination table.

Dynamic YES

Possible Values

CREATE_IF_NEEDEDCREATE_NEVER

Whether the job is allowed to create tables.

Dynamic YES

Sets the default dataset.

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

Dynamic YES

The table where to put query results.

If not provided, a new table is created.

Dynamic YES

Default false

Dynamic NO

Default false

Whether to Fetch the data from the query result to the task output. This is deprecated, use fetchType: FETCH instead

Dynamic NO

Default false

Whether to Fetch only one data row from the query result to the task output. This is deprecated, use fetchType: FETCH_ONE instead

Dynamic YES

Default NONE

Possible Values

STOREFETCHFETCH_ONENONE

Fetch type

The way you want to store data :

FETCH_ONE - output the first row
FETCH - output all rows as output variable
STORE - store all rows to a file
NONE - do nothing

Dynamic YES

Default true

Dynamic YES

The GCP service account to impersonate.

Dynamic YES

Format duration

Job timeout.

If this time limit is exceeded, BigQuery may attempt to terminate the job.

SubType string

Dynamic YES

The labels associated with this job.

You can use these to organize and group your jobs. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key.

Dynamic YES

Default false

Dynamic YES

The geographic location where the dataset should reside.

This property is experimental and might be subject to change or removed.

See Dataset Location

Dynamic YES

Default INTERACTIVE

Possible Values

INTERACTIVEBATCH

Sets a priority for the query.

Dynamic YES

The GCP project ID.

Dynamic YES

Range partitioning field for the destination table.

Dynamic YES

Dynamic NO

SubType string

Dynamic YES

Default ["due to concurrent update","Retrying the job may solve the problem"]

The messages which would trigger an automatic retry.

Message is tested as a substring of the full message, and is case insensitive.

SubType string

Dynamic YES

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

The reasons which would trigger an automatic retry.

SubType string

Dynamic YES

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.

SubType string

Dynamic YES

Default ["https://www.googleapis.com/auth/cloud-platform"]

The GCP scopes to be used.

Dynamic YES

The GCP service account.

Dynamic YES

The sql query to run

Dynamic NO

Default false

Whether to store the data from the query result into an ion serialized data file. This is deprecated, use fetchType: STORE instead

Dynamic YES

The time partitioning field for the destination table.

Dynamic YES

Default DAY

Possible Values

DAYHOURMONTHYEAR

The time partitioning type specification.

Dynamic YES

Default false

Dynamic YES

Possible Values

WRITE_TRUNCATEWRITE_APPENDWRITE_EMPTY

The action that should occur if the destination table already exists.

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

The job id

Map containing the first row of fetched data

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

SubType object

List containing the fetched data

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

The size of the rows fetch

Format uri

The uri of store result

Only populated if 'store' is set to true.

Dynamic NO

Format duration

Dynamic NO

Default RETRY_FAILED_TASK

Possible Values

RETRY_FAILED_TASKCREATE_NEW_EXECUTION

Dynamic NO

Minimum >= 1

Dynamic NO

Format duration

Dynamic NO

Default constant

Dynamic NO

Default false

Dynamic NO

Format duration

Dynamic NO

Format duration

Dynamic NO

Default RETRY_FAILED_TASK

Possible Values

RETRY_FAILED_TASKCREATE_NEW_EXECUTION

Dynamic NO

Minimum >= 1

Dynamic NO

Format duration

Dynamic NO

Default random

Dynamic NO

Default false

Dynamic NO

Format duration

Dynamic NO

Format duration

Dynamic NO

Default RETRY_FAILED_TASK

Possible Values

RETRY_FAILED_TASKCREATE_NEW_EXECUTION

Dynamic NO

Minimum >= 1

Dynamic NO

Format duration

Dynamic NO

Default exponential

Dynamic NO

Default false

Dynamic NO

The dataset of the table

Dynamic NO

The project of the table

Dynamic NO

The table name

​Query

Query