This document describes how to run parameterized queries in BigQuery.
Running parameterized queries
BigQuery supports query parameters to help prevent SQL injection when queries are constructed using user input. This feature is only available with standard SQL syntax. Query parameters can be used as substitutes for arbitrary expressions. Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.
To specify a named parameter, use the @
character followed by an
identifier,
such as @param_name
. For example, this query finds all the words in a
specific Shakespeare corpus with counts that are at least the specified value.
#standardSQL SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;
Alternatively, use the placeholder value ?
to specify a positional parameter.
Note that a query can use positional or named parameters but not both.
Console
Parameterized queries are not supported by the GCP Console.Web UI
Parameterized queries are not supported by the BigQuery web UI.Command-line
Use--parameter
to provide values for parameters in the form
"name:type:value". An empty name produces a positional parameter. The type may
be omitted to assume STRING
.
The --parameter
flag must be used in conjunction with the flag
--use_legacy_sql=False
to specify standard SQL syntax. Specify your location
using the --location
flag. The --location
flag is optional.
bq --location=US query --use_legacy_sql=False \
--parameter=corpus::romeoandjuliet \
--parameter=min_word_count:INT64:250 \
'SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus
AND word_count >= @min_word_count
ORDER BY word_count DESC;'
API
To use named parameters, set the jobs#configuration.query.parameterMode toNAMED
.
Populate
jobs#configuration.query.queryParameters[]
with the list of parameters. Set the
name
of each parameter with the @param_name
used in the query.
Enable standard SQL
syntax by setting
useLegacySql
to false
.
{
"query": "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;",
"queryParameters": [
{
"parameterType": {
"type": "STRING"
},
"parameterValue": {
"value": "romeoandjuliet"
},
"name": "corpus"
},
{
"parameterType": {
"type": "INT64"
},
"parameterValue": {
"value": "250"
},
"name": "min_word_count"
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
Try it in the Google APIs Explorer.
To use positional parameters, set the
jobs#configuration.query.parameterMode
to POSITIONAL
.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .
To use named parameters: To use positional parameters:Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .
To use named parameters: To use positional parameters:Using arrays in parameterized queries
To use an array type in a query parameter set the type to ARRAY<T>
where T
is the type of the elements in the array. Construct the value as a
comma-separated list of elements enclosed in square brackets, such as [1, 2,
3]
.
See the data types reference for more information about the array type.
Console
Parameterized queries are not supported by the GCP Console.Classic UI
Parameterized queries are not supported by the classic BigQuery web UI.Command-line
This query selects the most popular names for baby boys born in US states starting with the letter W. This example uses the--location=US
flag because you
are querying a public dataset. The BigQuery public datasets are
stored in the US multi-region location. Because the public datasets are stored
in the US, you cannot write public data query results to a table in another
region, and you cannot join tables in public datasets with tables in another
region.
bq --location=US query --use_legacy_sql=False \
--parameter='gender::M' \
--parameter='states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]' \
'SELECT name, sum(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_2013`
WHERE gender = @gender
AND state IN UNNEST(@states)
GROUP BY name
ORDER BY count DESC
LIMIT 10;'
Be careful to enclose the array type declaration in single quotes so that the
command output is not accidentally redirected to a file by the >
character.
API
To use an array-valued parameter set the jobs#configuration.query.queryParameters[].parameterType.type toARRAY
.
If the array values are scalars set the
jobs#configuration.query.queryParameters[].parameterType.arrayType.type
to the type of the values, such as STRING
. If the array values are
structures set this to STRUCT
and add the needed field definitions to
structTypes
.
For example, this query selects the most popular names for baby boys born in US states starting with the letter W.
{
"query": "SELECT name, sum(number) as count\nFROM `bigquery-public-data.usa_names.usa_1910_2013`\nWHERE gender = @gender\nAND state IN UNNEST(@states)\nGROUP BY name\nORDER BY count DESC\nLIMIT 10;",
"queryParameters": [
{
"parameterType": {
"type": "STRING"
},
"parameterValue": {
"value": "M"
},
"name": "gender"
},
{
"parameterType": {
"type": "ARRAY",
"arrayType": {
"type": "STRING"
}
},
"parameterValue": {
"arrayValues": [
{
"value": "WA"
},
{
"value": "WI"
},
{
"value": "WV"
},
{
"value": "WY"
}
]
},
"name": "states"
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .
Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .
Using timestamps in parameterized queries
To use a timestamp in a query parameter set the type to TIMESTAMP
. The value
should be in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone
.
See the data types reference for more information about the timestamp type.
Console
Parameterized queries are not supported by the GCP Console.Classic UI
Parameterized queries are not supported by the classic BigQuery web UI.Command-line
This query adds an hour to the timestamp parameter value.bq --location=US query --use_legacy_sql=False \
--parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00' \
'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);'
API
To use a timestamp parameter set the jobs#configuration.query.queryParameters[].parameterType.type toTIMESTAMP
.
This query adds an hour to the timestamp parameter value.
{
"query": "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);",
"queryParameters": [
{
"name": "ts_value",
"parameterType": {
"type": "TIMESTAMP"
},
"parameterValue": {
"value": "2016-12-07 08:00:00"
}
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .
Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .
Using structs in parameterized queries
To use a struct in a query parameter set the type to STRUCT<T>
where T
defines the fields and types within the struct. Field definitions are
separated by commas and are of the form field_name TF
where TF
is the type
of the field. For example, STRUCT<x INT64, y STRING>
defines a struct with a
field named x
of type INT64
and a second field named y
of type STRING
.
See the data types reference for more information about the struct type.
Console
Parameterized queries are not supported by the GCP Console.Classic UI
Parameterized queries are not supported by the BigQuery web UI.Command-line
This trivial query demonstrates the use of structured types by returning the parameter value.bq --location=US query --use_legacy_sql=False \
--parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \
'SELECT @struct_value AS s;'
API
To use a struct parameter set the jobs#configuration.query.queryParameters.parameterType.type toSTRUCT
.
Add an object for each field of the struct to
jobs#configuration.query.queryParameters.parameterType.structTypes.
If the struct values are scalars set the
type
to the type of the values, such as STRING
. If the struct values are arrays
set this to ARRAY
and set the nested arrayType
field to the appropriate
type. If the struct values are structures set type
to STRUCT
and add the
needed structTypes
.
This trivial query demonstrates the use of structured types by returning the parameter value.
{
"query": "SELECT @struct_value AS s;",
"queryParameters": [
{
"name": "struct_value",
"parameterType": {
"type": "STRUCT",
"structTypes": [
{
"name": "x",
"type": {
"type": "INT64"
}
},
{
"name": "y",
"type": {
"type": "STRING"
}
}
]
},
"parameterValue": {
"structValues": {
"x": {
"value": "1"
},
"y": {
"value": "foo"
}
}
}
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .