Run parameterized queries

When querying BigQuery data using GoogleSQL syntax, you canuse parameters to protect queries made from user input againstSQL injection. The parameterssubstitute 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 inGoogleSQL syntax. Parameterscannot be used as substitutes for identifiers, column names, table names, orother parts of the query.

To specify a named parameter, use the@ character followed by anidentifier, such as@param_name. Alternatively, use the placeholder value? to specify apositional parameter. A query can use positional or named parameters,but not both.

Note: To protect potentially sensitive information, the parameter value isn't logged in the BigQuerylogs when you run a query with a parameter.

You can run a parameterized query in BigQuery in the followingways:

  • The BigQuery Studio query editor in the Google Cloud console
  • The bq command-line tool'sbq query command
  • The API
  • The client libraries

The following examples show how to pass parameter values to a parameterizedquery:

Console

To run a parameterized query in the Google Cloud console, configureparameters inQuery settings, and then reference them in your SQLquery by prefixing each parameter name with the@ character.

Supported data types: the Google Cloud console only supportsparameterized queries of primitive data types, such asBIGNUMERIC,BOOL,BYTES,DATE,DATETIME,FLOAT64,GEOGRAPHY,INT64,INTERVAL,NUMERIC,STRING,TIME, orTIMESTAMP. Complex data types, such asARRAY andSTRUCT, aren't supported in the Google Cloud console.

Add the parameters in the Google Cloud console

  1. Go to theBigQuery page.

    Go to BigQuery

  2. In the query editor toolbar, clickMore and selectQuery settings.

  3. In theQuery settings pane, locate theQuery parameters sectionand clickAdd parameter.

  4. 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.

  5. ClickSave.

Pass parameter values to a query in the Google Cloud console

  1. In the query editor, enter a SQL query using the parameters youconfigured in the previous step. Reference them by prefixing theirnames with the@ character, as shown in the example.

    Example:

    SELECTword,word_countFROM`bigquery-public-data.samples.shakespeare`WHEREcorpus=@corpusANDword_count>=@min_word_countORDERBYword_countDESC;

    For this example, you would add thecorpus parameter as aSTRINGwith valueromeoandjuliet, and themin_word_count parameter as anINT64 with value250.

    If the query contains a missing or invalid parameter, an error messageis displayed. ClickSet parameter in the error message to adjust theparameter settings.

  2. To run the parameterized query in the query editor, clickRun.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, aCloud 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.

  2. Use--parameter to provide values for parameters in the formname:type:value. An empty name produces a positional parameter.The type may be omitted to assumeSTRING.

    The--parameter flag must be used in conjunction with the flag--use_legacy_sql=false to specify GoogleSQL syntax.

    (Optional) Specify yourlocation using the--location flag.

    bqquery\--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 theparameterMode toNAMED in thequery jobconfiguration.

PopulatequeryParameters with the list of parameters in thequery jobconfiguration. Set thename of each parameter with the@param_name used inthe query.

Enable GoogleSQL syntaxby settinguseLegacySql tofalse.

{"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 theparameterMode toPOSITIONAL in thequery job configuration.

C#

Before trying this sample, follow theC# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryC# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

To use named parameters:
usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryQueryWithNamedParameters{publicvoidQueryWithNamedParameters(stringprojectId="your-project-id"){varcorpus="romeoandjuliet";varminWordCount=250;// Note: Standard SQL is required to use query parameters.varquery=@"            SELECT word, word_count            FROM `bigquery-public-data.samples.shakespeare`            WHERE corpus = @corpus            AND word_count >= @min_word_count            ORDER BY word_count DESC";// Initialize client that will be used to send requests.varclient=BigQueryClient.Create(projectId);varparameters=newBigQueryParameter[]{newBigQueryParameter("corpus",BigQueryDbType.String,corpus),newBigQueryParameter("min_word_count",BigQueryDbType.Int64,minWordCount)};varjob=client.CreateQueryJob(sql:query,parameters:parameters,options:newQueryOptions{UseQueryCache=false});// Wait for the job to complete.job=job.PollUntilCompleted().ThrowOnAnyError();// Display the resultsforeach(BigQueryRowrowinclient.GetQueryResults(job.Reference)){Console.WriteLine($"{row["word"]}: {row["word_count"]}");}}}

Before trying this sample, follow theC# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryC# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

To use positional parameters:
usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryQueryWithPositionalParameters{publicvoidQueryWithPositionalParameters(stringprojectId="project-id"){varcorpus="romeoandjuliet";varminWordCount=250;// Note: Standard SQL is required to use query parameters.varquery=@"                SELECT word, word_count                FROM `bigquery-public-data.samples.shakespeare`                WHERE corpus = ?                AND word_count >= ?                ORDER BY word_count DESC;";// Initialize client that will be used to send requests.varclient=BigQueryClient.Create(projectId);// Set the name to None to use positional parameters.// Note that you cannot mix named and positional parameters.varparameters=newBigQueryParameter[]{newBigQueryParameter(null,BigQueryDbType.String,corpus),newBigQueryParameter(null,BigQueryDbType.Int64,minWordCount)};varjob=client.CreateQueryJob(sql:query,parameters:parameters,options:newQueryOptions{UseQueryCache=false,ParameterMode=BigQueryParameterMode.Positional});// Wait for the job to complete.job=job.PollUntilCompleted().ThrowOnAnyError();// Display the resultsforeach(BigQueryRowrowinclient.GetQueryResults(job.Reference)){Console.WriteLine($"{row["word"]}: {row["word_count"]}");}}}

Go

Before trying this sample, follow theGo setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryGo API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

To use named parameters:
import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryWithNamedParams demonstrate issuing a query using named query parameters.funcqueryWithNamedParams(wio.Writer,projectIDstring)error{// projectID := "my-project-id"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()q:=client.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;`)q.Parameters=[]bigquery.QueryParameter{{Name:"corpus",Value:"romeoandjuliet",},{Name:"min_word_count",Value:250,},}// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

To use positional parameters:
import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryWithPostionalParams demonstrate issuing a query using positional query parameters.funcqueryWithPositionalParams(wio.Writer,projectIDstring)error{// projectID := "my-project-id"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()q:=client.Query(`SELECT word, word_count        FROM `+"`bigquery-public-data.samples.shakespeare`"+`        WHERE corpus = ?        AND word_count >= ?        ORDER BY word_count DESC;`)q.Parameters=[]bigquery.QueryParameter{{Value:"romeoandjuliet",},{Value:250,},}// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

To use named parameters:
importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.QueryParameterValue;importcom.google.cloud.bigquery.TableResult;publicclassQueryWithNamedParameters{publicstaticvoidqueryWithNamedParameters(){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();Stringcorpus="romeoandjuliet";longminWordCount=250;Stringquery="SELECT word, word_count\n"+"FROM `bigquery-public-data.samples.shakespeare`\n"+"WHERE corpus = @corpus\n"+"AND word_count >= @min_word_count\n"+"ORDER BY word_count DESC";// Note: Standard SQL is required to use query parameters.QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).addNamedParameter("corpus",QueryParameterValue.string(corpus)).addNamedParameter("min_word_count",QueryParameterValue.int64(minWordCount)).build();TableResultresults=bigquery.query(queryConfig);results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query with named parameters performed successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

To use positional parameters:
importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.QueryParameterValue;importcom.google.cloud.bigquery.TableResult;publicclassQueryWithPositionalParameters{publicstaticvoidqueryWithPositionalParameters(){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();Stringcorpus="romeoandjuliet";longminWordCount=250;Stringquery="SELECT word, word_count\n"+"FROM `bigquery-public-data.samples.shakespeare`\n"+"WHERE corpus = ?\n"+"AND word_count >= ?\n"+"ORDER BY word_count DESC";// Note: Standard SQL is required to use query parameters.QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).addPositionalParameter(QueryParameterValue.string(corpus)).addPositionalParameter(QueryParameterValue.int64(minWordCount)).build();TableResultresults=bigquery.query(queryConfig);results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query with positional parameters performed successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

Node.js

Before trying this sample, follow theNode.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryNode.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

To use named parameters:
// Run a query using named query parameters// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryParamsNamed(){// The SQL query to runconstsqlQuery=`SELECT word, word_count        FROM \`bigquery-public-data.samples.shakespeare\`        WHERE corpus = @corpus        AND word_count >= @min_word_count        ORDER BY word_count DESC`;constoptions={query:sqlQuery,// Location must match that of the dataset(s) referenced in the query.location:'US',params:{corpus:'romeoandjuliet',min_word_count:250},};// Run the queryconst[rows]=awaitbigquery.query(options);console.log('Rows:');rows.forEach(row=>console.log(row));}

To use positional parameters:
// Run a query using positional query parameters// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryParamsPositional(){// The SQL query to runconstsqlQuery=`SELECT word, word_count        FROM \`bigquery-public-data.samples.shakespeare\`        WHERE corpus = ?        AND word_count >= ?        ORDER BY word_count DESC`;constoptions={query:sqlQuery,// Location must match that of the dataset(s) referenced in the query.location:'US',params:['romeoandjuliet',250],};// Run the queryconst[rows]=awaitbigquery.query(options);console.log('Rows:');rows.forEach(row=>console.log(row));}

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

To use named parameters:
fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()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;"""job_config=bigquery.QueryJobConfig(query_parameters=[bigquery.ScalarQueryParameter("corpus","STRING","romeoandjuliet"),bigquery.ScalarQueryParameter("min_word_count","INT64",250),])results=client.query_and_wait(query,job_config=job_config)# Make an API request.forrowinresults:print("{}:\t{}".format(row.word,row.word_count))

To use positional parameters:
fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()query="""    SELECT word, word_count    FROM `bigquery-public-data.samples.shakespeare`    WHERE corpus = ?    AND word_count >= ?    ORDER BY word_count DESC;"""# Set the name to None to use positional parameters.# Note that you cannot mix named and positional parameters.job_config=bigquery.QueryJobConfig(query_parameters=[bigquery.ScalarQueryParameter(None,"STRING","romeoandjuliet"),bigquery.ScalarQueryParameter(None,"INT64",250),])results=client.query_and_wait(query,job_config=job_config)# Make an API request.forrowinresults:print("{}:\t{}".format(row.word,row.word_count))

Use arrays in parameterized queries

To use an array type in a query parameter, set the type toARRAY<T> whereTis the type of the elements in the array. Construct the value as acomma-separated list of elements enclosed in square brackets, such as[1, 2,3].

See thedata types reference for more information about the array type.

Console

Arrays in parameterized queries aren't supported by theGoogle Cloud console.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, aCloud 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.

  2. This query selects the most popular names for baby boys born in US statesstarting with the letter W:

    Note: This example queries a US-based public dataset. Because the publicdataset is stored in the US multi-region location, the dataset that containsyour destination table must also be in the US. You cannot query a datasetin one location and write the results to a destination table in anotherlocation.
    bqquery\--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 thecommand output is not accidentally redirected to a file by the> character.

API

To use an array-valued parameter, set theparameterType toARRAY inthequery job configuration.

If the array values are scalars set theparameterTypeto the type of the values, such asSTRING. If the array values arestructures set this toSTRUCT and add the needed field definitions tostructTypes.

For example, this query selects the most popular names for baby boys born in USstates 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"}

Try it in the Google APIs Explorer.

C#

Before trying this sample, follow theC# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryC# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryQueryWithArrayParameters{publicvoidQueryWithArrayParameters(stringprojectId="your-project-id"){vargender="M";string[]states={"WA","WI","WV","WY"};// Note: Standard SQL is required to use query parameters.varquery=@"            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;";// Initialize client that will be used to send requests.varclient=BigQueryClient.Create(projectId);varparameters=newBigQueryParameter[]{newBigQueryParameter("gender",BigQueryDbType.String,gender),newBigQueryParameter("states",BigQueryDbType.Array,states)};varjob=client.CreateQueryJob(sql:query,parameters:parameters,options:newQueryOptions{UseQueryCache=false});// Wait for the job to complete.job=job.PollUntilCompleted().ThrowOnAnyError();// Display the resultsforeach(BigQueryRowrowinclient.GetQueryResults(job.Reference)){Console.WriteLine($"{row["name"]}: {row["count"]}");}}}

Go

Before trying this sample, follow theGo setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryGo API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryWithArrayParams demonstrates issuing a query and specifying query parameters that include an// array of strings.funcqueryWithArrayParams(wio.Writer,projectIDstring)error{// projectID := "my-project-id"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()q:=client.Query(`SELECTname,sum(number) as count        FROM `+"`bigquery-public-data.usa_names.usa_1910_2013`"+`WHEREgender = @gender        AND state IN UNNEST(@states)GROUP BYnameORDER BYcount DESCLIMIT 10;`)q.Parameters=[]bigquery.QueryParameter{{Name:"gender",Value:"M",},{Name:"states",Value:[]string{"WA","WI","WV","WY"},},}// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.QueryParameterValue;importcom.google.cloud.bigquery.TableResult;// Sample to running a query with array query parameters.publicclassQueryWithArrayParameters{publicstaticvoidrunQueryWithArrayParameters(){Stringgender="M";String[]states={"WA","WI","WV","WY"};Stringquery="SELECT name, sum(number) as count\n"+"FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"+"WHERE gender = @gender\n"+"AND state IN UNNEST(@states)\n"+"GROUP BY name\n"+"ORDER BY count DESC\n"+"LIMIT 10;";queryWithArrayParameters(query,gender,states);}publicstaticvoidqueryWithArrayParameters(Stringquery,Stringgender,String[]states){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();// Note: Standard SQL is required to use query parameters.QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).addNamedParameter("gender",QueryParameterValue.string(gender)).addNamedParameter("states",QueryParameterValue.array(states,String.class)).build();TableResultresults=bigquery.query(queryConfig);// Print the results.results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query with arrays parameters performed successfully");}catch(BigQueryException|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

Node.js

Before trying this sample, follow theNode.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryNode.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

// Run a query using array query parameters// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryParamsArrays(){// The SQL query to runconstsqlQuery=`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;`;constoptions={query:sqlQuery,// Location must match that of the dataset(s) referenced in the query.location:'US',params:{gender:'M',states:['WA','WI','WV','WY']},};// Run the queryconst[rows]=awaitbigquery.query(options);console.log('Rows:');rows.forEach(row=>console.log(row));}

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()query="""    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;"""job_config=bigquery.QueryJobConfig(query_parameters=[bigquery.ScalarQueryParameter("gender","STRING","M"),bigquery.ArrayQueryParameter("states","STRING",["WA","WI","WV","WY"]),])rows=client.query_and_wait(query,job_config=job_config)# Make an API request.forrowinrows:print("{}:\t{}".format(row.name,row.count))

Use timestamps in parameterized queries

To use a timestamp in a query parameter, the underlying REST API takes a valueof typeTIMESTAMP in the formatYYYY-MM-DD HH:MM:SS.DDDDDD time_zone. If youare using the client libraries, you create a built-in date object in thatlanguage, and the library converts it to the right format. For more information,see the following language-specific examples.

For more information about theTIMESTAMP type, see thedata types reference.

Console

Follow the steps foradding parameters in the Google Cloud consoledescribed earlier in this document. SelectTIMESTAMP for the parametertype and enter the timestamp value in the formatYYYY-MM-DD HH:MM:SS.DDDDDD time_zone.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, aCloud 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.

  2. This query adds an hour to the timestamp parameter value:

    bqquery\--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 theparameterTypetoTIMESTAMP 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"}

Try it in the Google APIs Explorer.

C#

Before trying this sample, follow theC# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryC# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryQueryWithTimestampParameters{publicvoidQueryWithTimestampParameters(stringprojectId="project-id"){vartimestamp=newDateTime(2016,12,7,8,0,0,DateTimeKind.Utc);// Note: Standard SQL is required to use query parameters.varquery="SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";// Initialize client that will be used to send requests.varclient=BigQueryClient.Create(projectId);varparameters=newBigQueryParameter[]{newBigQueryParameter("ts_value",BigQueryDbType.Timestamp,timestamp),};varjob=client.CreateQueryJob(sql:query,parameters:parameters,options:newQueryOptions{UseQueryCache=false});// Wait for the job to complete.job=job.PollUntilCompleted().ThrowOnAnyError();// Display the resultsforeach(BigQueryRowrowinclient.GetQueryResults(job.Reference)){Console.WriteLine(row[0]);}}}

Go

Before trying this sample, follow theGo setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryGo API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

import("context""fmt""io""time""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryWithTimestampParam demonstrates issuing a query and supplying a timestamp query parameter.funcqueryWithTimestampParam(wio.Writer,projectIDstring)error{// projectID := "my-project-id"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()q:=client.Query(`SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);`)q.Parameters=[]bigquery.QueryParameter{{Name:"ts_value",Value:time.Date(2016,12,7,8,0,0,0,time.UTC),},}// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.QueryParameterValue;importcom.google.cloud.bigquery.TableResult;importorg.threeten.bp.LocalDateTime;importorg.threeten.bp.ZoneOffset;importorg.threeten.bp.ZonedDateTime;// Sample to running a query with timestamp query parameters.publicclassQueryWithTimestampParameters{publicstaticvoidrunQueryWithTimestampParameters(){queryWithTimestampParameters();}publicstaticvoidqueryWithTimestampParameters(){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();ZonedDateTimetimestamp=LocalDateTime.of(2016,12,7,8,0,0).atZone(ZoneOffset.UTC);Stringquery="SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";// Note: Standard SQL is required to use query parameters.QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).addNamedParameter("ts_value",QueryParameterValue.timestamp(// Timestamp takes microseconds since 1970-01-01T00:00:00 UTCtimestamp.toInstant().toEpochMilli()*1000)).build();TableResultresults=bigquery.query(queryConfig);results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s",val.toString())));System.out.println("Query with timestamp parameter performed successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

Node.js

Before trying this sample, follow theNode.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryNode.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

// Run a query using timestamp parameters// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryParamsTimestamps(){// The SQL query to runconstsqlQuery=`SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);`;constoptions={query:sqlQuery,// Location must match that of the dataset(s) referenced in the query.location:'US',params:{ts_value:newDate()},};// Run the queryconst[rows]=awaitbigquery.query(options);console.log('Rows:');rows.forEach(row=>console.log(row.f0_));}

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importdatetimefromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()query="SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);"job_config=bigquery.QueryJobConfig(query_parameters=[bigquery.ScalarQueryParameter("ts_value","TIMESTAMP",datetime.datetime(2016,12,7,8,0,tzinfo=datetime.timezone.utc),)])results=client.query_and_wait(query,job_config=job_config)# Make an API request.forrowinresults:print(row)

Use structs in parameterized queries

To use a struct in a query parameter, set the type toSTRUCT<T> whereTdefines the fields and types within the struct. Field definitions areseparated by commas and are of the formfield_name TF whereTF is the typeof the field. For example,STRUCT<x INT64, y STRING> defines a struct with afield namedx of typeINT64 and a second field namedy of typeSTRING.

For more information about theSTRUCT type, see thedata types reference.

Console

Structs in parameterized queries aren't supported by theGoogle Cloud console.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, aCloud 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.

  2. This trivial query demonstrates the use of structured types by returning theparameter value:

    bqquery\--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 theparameterTypetoSTRUCT in the query job configuration.

Add an object for each field of the struct tostructTypesin the job'squeryParameters.If the struct values are scalars set thetypeto the type of the values, such asSTRING. If the struct values are arraysset this toARRAY, and set the nestedarrayType field to the appropriatetype. If the struct values are structures settype toSTRUCT and add theneededstructTypes.

This trivial query demonstrates the use of structured types by returning theparameter 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"}

Try it in the Google APIs Explorer.

C#

TheBigQuery client library for .NETdoes not supportstruct parameters.

Go

Before trying this sample, follow theGo setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryGo API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryWithStructParam demonstrates running a query and providing query parameters that include struct// types.funcqueryWithStructParam(wio.Writer,projectIDstring)error{// projectID := "my-project-id"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()typeMyStructstruct{Xint64Ystring}q:=client.Query(`SELECT @struct_value as s;`)q.Parameters=[]bigquery.QueryParameter{{Name:"struct_value",Value:MyStruct{X:1,Y:"foo"},},}// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.QueryParameterValue;importcom.google.cloud.bigquery.TableResult;importjava.util.HashMap;importjava.util.Map;publicclassQueryWithStructsParameters{publicstaticvoidqueryWithStructsParameters(){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();// Create structMap<String,QueryParameterValue>struct=newHashMap<>();struct.put("booleanField",QueryParameterValue.bool(true));struct.put("integerField",QueryParameterValue.string("test-stringField"));struct.put("stringField",QueryParameterValue.int64(10));QueryParameterValuerecordValue=QueryParameterValue.struct(struct);Stringquery="SELECT STRUCT(@recordField) AS record";QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).setUseLegacySql(false).addNamedParameter("recordField",recordValue).build();TableResultresults=bigquery.query(queryConfig);results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s",val.toString())));System.out.println("Query with struct parameter performed successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

Node.js

Before trying this sample, follow theNode.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryNode.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

// Run a query using struct query parameters// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryParamsStructs(){// The SQL query to runconstsqlQuery=`SELECT @struct_value AS struct_obj;`;constoptions={query:sqlQuery,// Location must match that of the dataset(s) referenced in the query.location:'US',params:{struct_value:{x:1,y:'foo'}},};// Run the queryconst[rows]=awaitbigquery.query(options);console.log('Rows:');rows.forEach(row=>console.log(row.struct_obj.y));}

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()query="SELECT @struct_value AS s;"job_config=bigquery.QueryJobConfig(query_parameters=[bigquery.StructQueryParameter("struct_value",bigquery.ScalarQueryParameter("x","INT64",1),bigquery.ScalarQueryParameter("y","STRING","foo"),)])results=client.query_and_wait(query,job_config=job_config)# Make an API request and waits for results.forrowinresults:print(row.s)

Use ranges in parameterized queries

To use a range in a query parameter, set thetype field toRANGE.

For more information about theRANGE type, see thedata types reference.

Console

Ranges in parameterized queries aren't supported by theGoogle Cloud console.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, aCloud 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.

  2. This query demonstrates the use of range types by returning theparameter value:

    bqquery\--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 theparameterTypeset thetype field toRANGE and set therangeElementType field to the type of range you want to use.

This query shows how to use theRANGE parameter type by returning theparameter 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"}

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-19 UTC.