Running parameterized queries

This document describes how to run parameterized queries in BigQuery.

Running parameterized queries

BigQuery supports query parameters to help prevent SQL injection when queries are constructed using user input. This feature is only available with standard SQL syntax. Query parameters can be used as substitutes for arbitrary expressions. Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

To specify a named parameter, use the @ character followed by an identifier, such as @param_name. For example, this query finds all the words in a specific Shakespeare corpus with counts that are at least the specified value.

#standardSQL
SELECT
  word,
  word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE
  corpus = @corpus
  AND word_count >= @min_word_count
ORDER BY
  word_count DESC

Alternatively, use the placeholder value ? to specify a positional parameter. Note that a query can use positional or named parameters but not both.

Console

Parameterized queries are not supported by the Cloud Console.

Classic UI

Parameterized queries are not supported by the BigQuery web UI.

CLI

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

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

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

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 standard SQL syntax by setting useLegacySql to false.

{
  "query": "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;",
  "queryParameters": [
    {
      "parameterType": {
        "type": "STRING"
      },
      "parameterValue": {
        "value": "romeoandjuliet"
      },
      "name": "corpus"
    },
    {
      "parameterType": {
        "type": "INT64"
      },
      "parameterValue": {
        "value": "250"
      },
      "name": "min_word_count"
    }
  ],
  "useLegacySql": false,
  "parameterMode": "NAMED"
}

Try it in the Google APIs Explorer.

To use positional parameters, set the parameterMode to POSITIONAL in the query job configuration.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

To use named parameters: 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryWithNamedParams demonstrate issuing a query using named query parameters.
func queryWithNamedParams(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	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)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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.
func queryWithPositionalParams(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	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)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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 . 

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String corpus = "romeoandjuliet";
long minWordCount = 250;
String query =
    "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.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter("corpus", QueryParameterValue.string(corpus))
        .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .

To use named parameters: 
from google.cloud import bigquery

# 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),
    ]
)
query_job = client.query(query, job_config=job_config)  # Make an API request.

for row in query_job:
    print("{}: \t{}".format(row.word, row.word_count))

To use positional parameters:

from google.cloud import bigquery

# 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),
    ]
)
query_job = client.query(query, job_config=job_config)  # Make an API request.

for row in query_job:
    print("{}: \t{}".format(row.word, row.word_count))

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 use named parameters: 
// Run a query using named query parameters

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryParamsNamed() {
  // The SQL query to run
  const sqlQuery = `SELECT word, word_count
        FROM \`bigquery-public-data.samples.shakespeare\`
        WHERE corpus = @corpus
        AND word_count >= @min_word_count
        ORDER BY word_count DESC`;

  const options = {
    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 query
  const [rows] = await bigquery.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 library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryParamsPositional() {
  // The SQL query to run
  const sqlQuery = `SELECT word, word_count
        FROM \`bigquery-public-data.samples.shakespeare\`
        WHERE corpus = ?
        AND word_count >= ?
        ORDER BY word_count DESC`;

  const options = {
    query: sqlQuery,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    params: ['romeoandjuliet', 250],
  };

  // Run the query
  const [rows] = await bigquery.query(options);

  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

Using arrays in parameterized queries

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

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

Console

Parameterized queries are not supported by the Cloud Console.

Classic UI

Parameterized queries are not supported by the BigQuery web UI.

CLI

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"
}

[Try it in the Google APIs Explorer][bigquery-query-params-arrays-api].

[bigquery-query-params-arrays-api]: https://developers.google.com/apis-explorer/#p/bigquery/v2/bigquery.jobs.query?projectId=my-project-id&_h=1&resource=%257B%250A++%2522query%2522%253A+%2522SELECT+name%252C+sum(number)+as+count%255CnFROM+%2560bigquery-public-data.usa_names.usa_1910_2013%2560%255CnWHERE+gender+%253D+%2540gender%255CnAND+state+IN+UNNEST(%2540states)%255CnGROUP+BY+name%255CnORDER+BY+count+DESC%255CnLIMIT+10%253B%2522%252C%250A++%2522queryParameters%2522%253A+%250A++%255B%250A++++%257B%250A++++++%2522parameterType%2522%253A+%250A++++++%257B%250A++++++++%2522type%2522%253A+%2522STRING%2522%250A++++++%257D%252C%250A++++++%2522parameterValue%2522%253A+%250A++++++%257B%250A++++++++%2522value%2522%253A+%2522M%2522%250A++++++%257D%252C%250A++++++%2522name%2522%253A+%2522gender%2522%250A++++%257D%252C%250A++++%257B%250A++++++%2522parameterType%2522%253A+%250A++++++%257B%250A++++++++%2522type%2522%253A+%2522ARRAY%2522%252C%250A++++++++%2522arrayType%2522%253A+%250A++++++++%257B%250A++++++++++%2522type%2522%253A+%2522STRING%2522%250A++++++++%257D%250A++++++%257D%252C%250A++++++%2522parameterValue%2522%253A+%250A++++++%257B%250A++++++++%2522arrayValues%2522%253A+%250A++++++++%255B%250A++++++++++%257B%250A++++++++++++%2522value%2522%253A+%2522WA%2522%250A++++++++++%257D%252C%250A++++++++++%257B%250A++++++++++++%2522value%2522%253A+%2522WI%2522%250A++++++++++%257D%252C%250A++++++++++%257B%250A++++++++++++%2522value%2522%253A+%2522WV%2522%250A++++++++++%257D%252C%250A++++++++++%257B%250A++++++++++++%2522value%2522%253A+%2522WY%2522%250A++++++++++%257D%250A++++++++%255D%250A++++++%257D%252C%250A++++++%2522name%2522%253A+%2522states%2522%250A++++%257D%250A++%255D%252C%250A++%2522useLegacySql%2522%253A+false%252C%250A++%2522parameterMode%2522%253A+%2522NAMED%2522%250A%257D&

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 . 

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.
func queryWithArrayParams(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	q := 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;`)
	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)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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 . 

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String gender = "M";
String[] states = {"WA", "WI", "WV", "WY"};
String query =
    "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;";
// Note: Standard SQL is required to use query parameters.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter("gender", QueryParameterValue.string(gender))
        .addNamedParameter("states", QueryParameterValue.array(states, String.class))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

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 . 

from google.cloud import bigquery

# 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"]),
    ]
)
query_job = client.query(query, job_config=job_config)  # Make an API request.

for row in query_job:
    print("{}: \t{}".format(row.name, row.count))

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 . 

// Run a query using array query parameters

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryParamsArrays() {
  // The SQL query to run
  const sqlQuery = `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;`;

  const options = {
    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 query
  const [rows] = await bigquery.query(options);

  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

Using timestamps in parameterized queries

To use a timestamp in a query parameter set the type to TIMESTAMP. The value should be in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone.

See the data types reference for more information about the timestamp type.

Console

Parameterized queries are not supported by the Cloud Console.

Classic UI

Parameterized queries are not supported by the BigQuery web UI.

CLI

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"
}

[Try it in the Google APIs Explorer][bigquery-query-params-timestamps-api].

[bigquery-query-params-timestamps-api]: https://developers.google.com/apis-explorer/#p/bigquery/v2/bigquery.jobs.query?projectId=my-project-id&_h=1&resource=%257B%250A++%2522query%2522%253A+%2522SELECT+TIMESTAMP_ADD(%2540ts_value%252C+INTERVAL+1+HOUR)%253B%2522%252C%250A++%2522queryParameters%2522%253A+%250A++%255B%250A++++%257B%250A++++++%2522name%2522%253A+%2522ts_value%2522%252C%250A++++++%2522parameterType%2522%253A+%250A++++++%257B%250A++++++++%2522type%2522%253A+%2522TIMESTAMP%2522%250A++++++%257D%252C%250A++++++%2522parameterValue%2522%253A+%250A++++++%257B%250A++++++++%2522value%2522%253A+%25222016-12-07+08%253A00%253A00%2522%250A++++++%257D%250A++++%257D%250A++%255D%252C%250A++%2522useLegacySql%2522%253A+false%252C%250A++%2522parameterMode%2522%253A+%2522NAMED%2522%250A%257D&

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 . 

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.
func queryWithTimestampParam(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	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)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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 . 

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);
String query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
// Note: Standard SQL is required to use query parameters.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter(
            "ts_value",
            QueryParameterValue.timestamp(
                // Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
                timestamp.getMillis() * 1000))
        .build();

// Print the results.
DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  System.out.printf(
      "%s\n",
      formatter.print(
          new DateTime(
              // Timestamp values are returned in microseconds since 1970-01-01T00:00:00 UTC,
              // but org.joda.time.DateTime constructor accepts times in milliseconds.
              row.get(0).getTimestampValue() / 1000, DateTimeZone.UTC)));
  System.out.printf("\n");
}

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 . 

import datetime

import pytz
from google.cloud import bigquery

# 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=pytz.UTC),
        )
    ]
)
query_job = client.query(query, job_config=job_config)  # Make an API request.

for row in query_job:
    print(row)

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 . 

// Run a query using timestamp parameters

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryParamsTimestamps() {
  // The SQL query to run
  const sqlQuery = `SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);`;

  const options = {
    query: sqlQuery,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    params: {ts_value: new Date()},
  };

  // Run the query
  const [rows] = await bigquery.query(options);

  console.log('Rows:');
  rows.forEach(row => console.log(row.f0_));
}

Using structs in parameterized queries

To use a struct in a query parameter set the type to STRUCT<T> where T defines the fields and types within the struct. Field definitions are separated by commas and are of the form field_name TF where TF is the type of the field. For example, STRUCT<x INT64, y STRING> defines a struct with a field named x of type INT64 and a second field named y of type STRING.

See the data types reference for more information about the struct type.

Console

Parameterized queries are not supported by the Cloud Console.

Classic UI

Parameterized queries are not supported by the BigQuery web UI.

CLI

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"
}

[Try it in the Google APIs Explorer][bigquery-query-params-structs-api].

[bigquery-query-params-structs-api]: https://developers.google.com/apis-explorer/#p/bigquery/v2/bigquery.jobs.query?projectId=my-project-id&_h=1&resource=%257B%250A++%2522query%2522%253A+%2522SELECT+%2540struct_value+AS+s%253B%2522%252C%250A++%2522queryParameters%2522%253A+%250A++%255B%250A++++%257B%250A++++++%2522name%2522%253A+%2522struct_value%2522%252C%250A++++++%2522parameterType%2522%253A+%250A++++++%257B%250A++++++++%2522type%2522%253A+%2522STRUCT%2522%252C%250A++++++++%2522structTypes%2522%253A+%250A++++++++%255B%250A++++++++++%257B%250A++++++++++++%2522name%2522%253A+%2522x%2522%252C%250A++++++++++++%2522type%2522%253A+%250A++++++++++++%257B%250A++++++++++++++%2522type%2522%253A+%2522INT64%2522%250A++++++++++++%257D%250A++++++++++%257D%252C%250A++++++++++%257B%250A++++++++++++%2522name%2522%253A+%2522y%2522%252C%250A++++++++++++%2522type%2522%253A+%250A++++++++++++%257B%250A++++++++++++++%2522type%2522%253A+%2522STRING%2522%250A++++++++++++%257D%250A++++++++++%257D%250A++++++++%255D%250A++++++%257D%252C%250A++++++%2522parameterValue%2522%253A+%250A++++++%257B%250A++++++++%2522structValues%2522%253A+%250A++++++++%257B%250A++++++++++%2522x%2522%253A+%250A++++++++++%257B%250A++++++++++++%2522value%2522%253A+%25221%2522%250A++++++++++%257D%252C%250A++++++++++%2522y%2522%253A+%250A++++++++++%257B%250A++++++++++++%2522value%2522%253A+%2522foo%2522%250A++++++++++%257D%250A++++++++%257D%250A++++++%257D%250A++++%257D%250A++%255D%252C%250A++%2522useLegacySql%2522%253A+false%252C%250A++%2522parameterMode%2522%253A+%2522NAMED%2522%250A%257D&

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 . 

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.
func queryWithStructParam(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	type MyStruct struct {
		X int64
		Y string
	}
	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)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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 . 

from google.cloud import bigquery

# 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"),
        )
    ]
)
query_job = client.query(query, job_config=job_config)  # Make an API request.

for row in query_job:
    print(row.s)

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 . 

// Run a query using struct query parameters

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryParamsStructs() {
  // The SQL query to run
  const sqlQuery = `SELECT @struct_value AS struct_obj;`;

  const options = {
    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 query
  const [rows] = await bigquery.query(options);

  console.log('Rows:');
  rows.forEach(row => console.log(row.struct_obj.y));
}
Var denne side nyttig? Giv os en anmeldelse af den:

Send feedback om...

Denne side
Feedback om de tilhørende oplysninger
BigQuery
Feedback om produktet
Har du brug for hjælp? Besøg vores supportside.