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 GoogleSQL 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
. Alternatively, use the placeholder value ?
to specify a
positional parameter. Note that a query can use positional or named parameters
but not both.
You can run a parameterized query in BigQuery in the following ways:
- the bq command-line tool's
bq querycommand - the API
- the client libraries
The following example shows how to pass parameter values to a parameterized query:
Console
Parameterized queries are not supported by the Google Cloud console.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
-
Use
--parameterto provide values for parameters in the formname:type:value. An empty name produces a positional parameter. The type may be omitted to assumeSTRING.The
--parameterflag must be used in conjunction with the flag--use_legacy_sql=falseto specify GoogleSQL syntax.(Optional) Specify your location using the
--locationflag.bq 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 parameterMode
to NAMED
in the query
job
configuration.
Populate queryParameters
with the list of parameters in the query
job
configuration. Set the name
of each parameter with the @param_name
used in
the query.
Enable GoogleSQL 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 parameterMode
to POSITIONAL
in the query
job configuration.
C#
Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery C# API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
To use named parameters:Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery C# API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
To use positional parameters: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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
To use named 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 .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
To use named parameters:Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Node.js API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
To use named parameters: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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
To use named 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 Google Cloud console.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
-
This query selects the most popular names for baby boys born in US states starting with the letter W:
bq 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 parameterType
to ARRAY
in
the query
job configuration.
If the array values are scalars set the parameterType
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"
}
C#
Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery C# API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Node.js API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
Using timestamps in parameterized queries
To use a timestamp in a query parameter, the underlying REST API takes a value
of type TIMESTAMP
in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone
. If you
are using the client libraries, you create a built-in date object in that
language, and the library converts it to the right format. For more information,
see the following language-specific examples.
For more information about the TIMESTAMP
type, see the data types reference
.
Console
Parameterized queries are not supported by the Google Cloud console.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
-
This query adds an hour to the timestamp parameter value:
bq 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 parameterType
to TIMESTAMP
in the query job configuration.
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"
}
C#
Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery C# API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Node.js API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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
.
For more information about the STRUCT
type, see the data types reference
.
Console
Parameterized queries are not supported by the Google Cloud console.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
-
This trivial query demonstrates the use of structured types by returning the parameter value:
bq 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 parameterType
to STRUCT
in the query job configuration.
Add an object for each field of the struct to structTypes
in the job's queryParameters
.
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"
}
C#
The BigQuery client library for .NET does not support struct parameters .
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Node.js API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
