Console

To run a parameterized query in the Google Cloud console, configure parameters in Query settings, and then reference them in your SQL query by prefixing each parameter name with the @ character.

Supported data types: the Google Cloud console only supports parameterized queries of primitive data types, such as BIGNUMERIC, BOOL, BYTES, DATE, DATETIME, FLOAT64, GEOGRAPHY, INT64, INTERVAL, NUMERIC, STRING, TIME, or TIMESTAMP. Complex data types, such as ARRAY and STRUCT, aren't supported in the Google Cloud console.

Add the parameters in the Google Cloud console

1. Go to the BigQuery page.

   Go to BigQuery

2. In the query editor toolbar, click More and select Query settings.

3. In the Query settings pane, locate the Query parameters section and click Add 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. Click Save.

Pass parameter values to a query in the Google Cloud console

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

   Example:

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

   For this example, you would add the corpus parameter as a STRING with value romeoandjuliet, and the min_word_count parameter as an INT64 with value 250.

   If the query contains a missing or invalid parameter, an error message is displayed. Click Set parameter in the error message to adjust the parameter settings.

2. To run the parameterized query in the query editor, click Run.

bq

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

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. 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 GoogleSQL 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 GoogleSQL syntax by setting useLegacySql to false.

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

Try it in the Google APIs Explorer.

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

C#

Go

Java

Node.js

Python

Console

Arrays in parameterized queries aren't 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, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

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

C#

Go

Java

Node.js

Python

Console

Follow the steps for adding parameters in the Google Cloud console described earlier in this document. Select TIMESTAMP for the parameter type and enter the timestamp value in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone.

bq

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

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

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

C#

Go

Java

Node.js

Python

Console

Structs in parameterized queries aren't 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, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

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

C#

The BigQuery client library for .NET does not support struct parameters.

Go

Java

Node.js

Python

Console

Ranges in parameterized queries aren't 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, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

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

   bq query \
      --use_legacy_sql=false \
      --parameter='my_param:RANGE<DATE>:[2020-01-01, 2020-12-31)' \
      'SELECT @my_param AS foo;'

API

To use a range parameter, in the parameterType set the type field to RANGE and set the rangeElementType field to the type of range you want to use.

This query shows how to use the RANGE parameter type by returning the parameter value.

{
  "query": "SELECT @my_param AS value_of_range_parameter;",
  "queryParameters": [
    {
      "name": "range_param",
      "parameterType": {
        "type": "RANGE",
        "rangeElementTYpe": {
          "type": "DATE"
        }
      },
      "parameterValue": {
        "rangeValue": {
          "start": {
            "value": "2020-01-01"
          },
          "end": {
            "value": "2020-12-31"
          }
        }
      }
    }
  ],
  "useLegacySql": false,
  "parameterMode": "NAMED"
}