Running parameterized queries

BigQuery supports query parameters to help preventSQL injection when queries areconstructed using user input. This feature is only available withGoogleSQL syntax. Query parameterscan be used as substitutes for arbitrary expressions. Parameters cannot be usedas substitutes for identifiers, column names, table names, or other parts of thequery.

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

When using a parameter, the provided value itself is not logged in the BigQuerylogs to protect potentially sensitive information.

You can run a parameterized query in BigQuery in the following ways:

  • the bq command-line tool'sbq query command
  • the API
  • the client libraries

The following example shows how to pass parameter values to a parameterizedquery:

Console

Parameterized queries are not supported by the Google 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. 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))

Using 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

Parameterized queries are not supported by the Google 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))

Using 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

Parameterized queries are not supported by the Google 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 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)

Using 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

Parameterized queries are not supported by the Google 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)

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 2025-12-15 UTC.