Run parameterized queries
When querying BigQuery data using GoogleSQL syntax, you can use parameters to protect queries made from user input against SQL injection . The parameters substitute arbitrary expressions in your GoogleSQL queries.
You pass query parameters for various data types, including the following:
- Arrays
- Timestamps
- Structs
- Ranges
Pass a parameter in a query
Query parameters are only supported in GoogleSQL syntax . 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. A query can use positional or named parameters,
but not both.
You can run a parameterized query in BigQuery in the following ways:
- The BigQuery Studio query editor in the Google Cloud console
- The bq command-line tool's
bq querycommand - The API
- The client libraries
The following examples show how to pass parameter values to a parameterized query:
Console
To run a parameterized query in the Google Cloud console, configure
parameters in Query settings, and then reference them in your SQL
query by prefixing each parameter name with the @
character.
Supported data types:the Google Cloud console only supports
parameterized queries of primitive data types, such as BIGNUMERIC
, BOOL
, BYTES
, DATE
, DATETIME
, FLOAT64
, GEOGRAPHY
, INT64
, INTERVAL
, NUMERIC
, STRING
, TIME
, or TIMESTAMP
. Complex data types, such as ARRAY
and STRUCT
, aren't supported in the Google Cloud console.
Add the parameters in the Google Cloud console
-
Go to the BigQuerypage.
-
In the query editor toolbar, click Moreand select Query settings.
-
In the Query settingspane, locate the Query parameterssection and click Add parameter.
-
For each parameter in your query, provide the following:
- Name: Enter the parameter name (don't include the
@character). - Type: Select the data type for the parameter.
- Value: Enter the value you want to use for this execution.
- Name: Enter the parameter name (don't include the
-
Click Save.
Pass parameter values to a query in the Google Cloud console
-
In the query editor, enter a SQL query using the parameters you configured in the previous step. Reference them by prefixing their names with the
@character, as shown in the example.Example:
SELECT word , word_count FROM ` bigquery - public - data . samples . shakespeare ` WHERE corpus = @ corpus AND word_count > = @ min_word_count ORDER BY word_count DESC ;For this example, you would add the
corpusparameter as aSTRINGwith valueromeoandjuliet, and themin_word_countparameter as anINT64with value250.If the query contains a missing or invalid parameter, an error message is displayed. Click Set parameterin the error message to adjust the parameter settings.
-
To run the parameterized query in the query editor, click Run.
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:Use 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
Arrays in parameterized queries aren't 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 .
Use 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
Follow the steps for adding parameters in the Google Cloud console
described earlier in this document. Select TIMESTAMP
for the parameter
type and enter the timestamp value in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone
.
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 .
Use 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
Structs in parameterized queries aren't 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 .
Use ranges in parameterized queries
To use a range in a query parameter, set the type
field to RANGE
.
For more information about the RANGE
type, see the data types reference
.
Console
Ranges in parameterized queries aren't 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 demonstrates the use of range types by returning the parameter value:
bq query \ --use_legacy_sql = false \ --parameter = 'my_param:RANGE<DATE>:[2020-01-01, 2020-12-31)' \ 'SELECT @my_param AS foo;'
API
To use a range parameter, in the parameterType
set the type
field to RANGE
and set the rangeElementType
field to the type of range you want to use.
This query shows how to use the RANGE
parameter type by returning the
parameter value.
{
"query"
:
"SELECT @my_param AS value_of_range_parameter;"
,
"queryParameters"
:
[
{
"name"
:
"range_param"
,
"parameterType"
:
{
"type"
:
"RANGE"
,
"rangeElementTYpe"
:
{
"type"
:
"DATE"
}
},
"parameterValue"
:
{
"rangeValue"
:
{
"start"
:
{
"value"
:
"2020-01-01"
},
"end"
:
{
"value"
:
"2020-12-31"
}
}
}
}
],
"useLegacySql"
:
false
,
"parameterMode"
:
"NAMED"
}
