Query

Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout

28 variables
28 variables

Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout

Authorization

To use this building block you will have to grant access to at least one of the following scopes:

  • View and manage your data in Google BigQuery
  • View and manage your data across Google Cloud Platform services
  • View your data across Google Cloud Platform services

Input

This building block consumes 28 input parameters

  = Parameter name
  = Format

projectId STRING Required

Project ID of the project billed for the query

parameterMode STRING

Standard SQL only. Set to POSITIONAL to use positional (?) query parameters or to NAMED to use named (@myparam) query parameters in this query

useQueryCache BOOLEAN

[Optional] 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. The default value is true

defaultDataset OBJECT

defaultDataset.projectId STRING

[Optional] The ID of the project containing this dataset

defaultDataset.datasetId STRING

[Required] A unique ID for this dataset, without the project name. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters

location STRING

The geographic location where the job should run. See details at https://cloud.google.com/bigquery/docs/locations#specifying_your_location

preserveNulls BOOLEAN

[Deprecated] This property is deprecated

query STRING

[Required] A query string, following the BigQuery query syntax, of the query to execute. Example: "SELECT count(f1) FROM [myProjectId:myDatasetId.myTableId]"

maxResults INTEGER

[Optional] 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

dryRun BOOLEAN

[Optional] If set to true, BigQuery doesn't run the job. Instead, if the query is valid, BigQuery returns statistics about the job such as how many bytes would be processed. If the query is invalid, an error returns. The default value is false

queryParameters[] OBJECT

queryParameters[].name STRING

[Optional] If unset, this is a positional parameter. Otherwise, should be unique within a query

queryParameters[].parameterType OBJECT

queryParameters[].parameterType.type STRING

[Required] The top level type of this field

queryParameters[].parameterType.structTypes[] OBJECT

queryParameters[].parameterType.structTypes[].name STRING

[Optional] The name of this field

queryParameters[].parameterType.structTypes[].description STRING

[Optional] Human-oriented description of the field

queryParameters[].parameterType.arrayType OBJECT

queryParameters[].parameterType.arrayType.type STRING

[Required] The top level type of this field

queryParameters[].parameterValue OBJECT

queryParameters[].parameterValue.value STRING

[Optional] The value of this value, if a simple scalar type

queryParameters[].parameterValue.structValues OBJECT

[Optional] The struct field values, in order of the struct type's declaration

queryParameters[].parameterValue.structValues.customKey OBJECT

Add additional named properties

queryParameters[].parameterValue.arrayValues[] OBJECT

useLegacySql BOOLEAN

Specifies whether to use BigQuery's legacy SQL dialect for this query. The default value is true. If set to false, the query will use BigQuery's standard SQL: https://cloud.google.com/bigquery/sql-reference/ When useLegacySql is set to false, the value of flattenResults is ignored; query will be run as if flattenResults is false

timeoutMs INTEGER

[Optional] How long to wait for the query to complete, in milliseconds, before the request times out and returns. Note that this is only a timeout for the request, not the query. If the query takes longer to run than the timeout value, the call returns without any results and with the 'jobComplete' flag set to false. You can call GetQueryResults() to wait for the query to complete and read the results. The default value is 10000 milliseconds (10 seconds)

kind STRING

The resource type of the request

Output

This building block provides 28 output parameters

  = Parameter name
  = Format

schema OBJECT

schema.fields[] OBJECT

schema.fields[].name STRING

[Required] The field name. The name must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_), and must start with a letter or underscore. The maximum length is 128 characters

schema.fields[].type STRING

[Required] The field data type. Possible values include STRING, BYTES, INTEGER, INT64 (same as INTEGER), FLOAT, FLOAT64 (same as FLOAT), BOOLEAN, BOOL (same as BOOLEAN), TIMESTAMP, DATE, TIME, DATETIME, RECORD (where RECORD indicates that the field contains a nested schema) or STRUCT (same as RECORD)

schema.fields[].categories OBJECT

[Optional] The categories attached to this field, used for field-level access control

schema.fields[].categories.names[] STRING

schema.fields[].mode STRING

[Optional] The field mode. Possible values include NULLABLE, REQUIRED and REPEATED. The default value is NULLABLE

schema.fields[].description STRING

[Optional] The field description. The maximum length is 1,024 characters

schema.fields[].fields[] OBJECT

rows[] OBJECT

rows[].f[] OBJECT

rows[].f[].v ANY

errors[] OBJECT

errors[].reason STRING

A short error code that summarizes the error

errors[].message STRING

A human-readable description of the error

errors[].location STRING

Specifies where the error occurred, if present

errors[].debugInfo STRING

Debugging information. This property is internal to Google and should not be used

pageToken STRING

A token used for paging results

kind STRING

The resource type

jobComplete BOOLEAN

Whether the query has completed or not. If rows or totalRows are present, this will always be true. If this is false, totalRows will not be available

numDmlAffectedRows INTEGER

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

totalBytesProcessed INTEGER

The total number of bytes processed for this query. If this query was a dry run, this is the number of bytes that would be processed if the query were run

totalRows INTEGER

The total number of rows in the complete query result set, which can be more than the number of rows in this single page of results

jobReference OBJECT

jobReference.location STRING

The geographic location of the job. See details at https://cloud.google.com/bigquery/docs/locations#specifying_your_location

jobReference.jobId STRING

[Required] The ID of the job. The ID must contain only letters (a-z, A-Z), numbers (0-9), underscores (_), or dashes (-). The maximum length is 1,024 characters

jobReference.projectId STRING

[Required] The ID of the project containing this job

cacheHit BOOLEAN

Whether the query result was fetched from the query cache