執行參數化查詢

BigQuery 支援查詢參數，以協助使用者在利用輸入內容建構查詢時，避免發生 SQL 植入的情況。但您只能利用 GoogleSQL 語法來使用這個功能。查詢參數可取代任意運算式。參數不可取代 ID、資料欄名稱、資料表名稱，或是查詢的其他部分。

如要指定具名參數，請使用 @ 字元，後面加上 ID，例如 @param_name。或者，您可以使用預留位置值 ? 來指定位置參數。請注意，查詢可以使用位置或已命名參數，但不得同時使用這兩者。

使用參數時，為保護可能含有私密資訊的資料，BigQuery 記錄不會記錄您提供的值。

您可以在 BigQuery 中透過下列方式執行參數化查詢：

• bq 指令列工具的 bq query 指令
• API
• 用戶端程式庫

以下範例說明如何將參數值傳遞至參數化查詢：

控制台

Google Cloud 控制台不支援參數化查詢。

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. 請使用 --parameter，以 name:type:value 的格式來提供參數的值。如果名稱留白，會產生位置參數。如果您省略類型，系統會假設類型是 STRING。

   --parameter 標記必須與標記 --use_legacy_sql=false 一起使用，以便指定 GoogleSQL 語法。

   (選用) 使用 --location 標記指定您的位置。

   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

如要使用已命名參數，請將 query 工作設定中的 parameterMode 設定為 NAMED。

請利用 query 工作設定中的參數清單填入 queryParameters。請利用查詢中所用的 @param_name 來設定每個參數的 name。

請將 useLegacySql 設定為 false 來啟用 GoogleSQL 語法。

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

在 Google APIs Explorer 中嘗試這個範例。

如要使用位置參數，請將 query 工作設定中的 parameterMode 設定為 POSITIONAL。

C#

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 C# 設定操作說明進行操作。詳情請參閱 BigQuery C# API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要使用已命名參數：

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryWithNamedParameters
{
    public void QueryWithNamedParameters(string projectId = "your-project-id")
    {
        var corpus = "romeoandjuliet";
        var minWordCount = 250;

        // Note: Standard SQL is required to use query parameters.
        var 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";

        // Initialize client that will be used to send requests.
        var client = BigQueryClient.Create(projectId);

        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter("corpus", BigQueryDbType.String, corpus),
            new BigQueryParameter("min_word_count", BigQueryDbType.Int64, minWordCount)
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["word"]}: {row["word_count"]}");
        }
    }
}

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 C# 設定操作說明進行操作。詳情請參閱 BigQuery C# API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要使用位置參數：

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryWithPositionalParameters
{
    public void QueryWithPositionalParameters(string projectId = "project-id")
    {
        var corpus = "romeoandjuliet";
        var minWordCount = 250;

        // Note: Standard SQL is required to use query parameters.
        var query = @"
                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.
        var client = BigQueryClient.Create(projectId);

        // Set the name to None to use positional parameters.
        // Note that you cannot mix named and positional parameters.
        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter(null, BigQueryDbType.String, corpus),
            new BigQueryParameter(null, BigQueryDbType.Int64, minWordCount)
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions
            {
                UseQueryCache = false,
                ParameterMode = BigQueryParameterMode.Positional
            });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["word"]}: {row["word_count"]}");
        }
    }
}

Go

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Go 設定操作說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要使用已命名參數：

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)
	}
	defer client.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)
	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
}

如要使用位置參數：

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)
	}
	defer client.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)
	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

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要使用已命名參數：

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryParameterValue;
import com.google.cloud.bigquery.TableResult;

public class QueryWithNamedParameters {

  public static void queryWithNamedParameters() {
    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.
      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();

      TableResult results = 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 | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

如要使用位置參數：

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryParameterValue;
import com.google.cloud.bigquery.TableResult;

public class QueryWithPositionalParameters {
  public static void queryWithPositionalParameters() {
    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.
      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 = ?\n"
              + "AND word_count >= ?\n"
              + "ORDER BY word_count DESC";

      // Note: Standard SQL is required to use query parameters.
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addPositionalParameter(QueryParameterValue.string(corpus))
              .addPositionalParameter(QueryParameterValue.int64(minWordCount))
              .build();

      TableResult results = 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 | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要使用已命名參數：

// 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));
}

如要使用位置參數：

// 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));
}

Python

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Python 設定操作說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要使用已命名參數：

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

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

如要使用位置參數：

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

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

在參數化查詢中使用陣列

如要在查詢參數中使用陣列類型，請將類型設定為 ARRAY<T>，其中 T 是陣列中元素的類型。請將值建構為以方括號括住的元素清單，其中的元素以逗號分隔，例如 [1, 2, 3]。

如要進一步瞭解陣列類型，請參閱資料類型參考資料。

控制台

Google Cloud 控制台不支援參數化查詢。

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. 這項查詢會針對在美國出生的男嬰，挑出開頭為英文字母 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;'

   請小心地用單引號括住陣列類型宣告，以免 > 字元意外地將指令輸出重新導向至某個檔案。

API

如要使用陣列值參數，請將 query 工作設定中的 parameterType 設定為 ARRAY。

如果陣列值是純量，請將 parameterType 設定為值的類型，例如 STRING。如果陣列值是結構，請將參數類型設定為 STRUCT，並將所需的欄位定義新增至 structTypes。

例如，這個查詢會針對在美國出生的男嬰選取以字母 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"
}

在 Google APIs Explorer 中嘗試這個範例。

C#

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 C# 設定操作說明進行操作。詳情請參閱 BigQuery C# API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryWithArrayParameters
{
    public void QueryWithArrayParameters(string projectId = "your-project-id")
    {
        var gender = "M";
        string[] states = { "WA", "WI", "WV", "WY" };

        // Note: Standard SQL is required to use query parameters.
        var 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;";

        // Initialize client that will be used to send requests.
        var client = BigQueryClient.Create(projectId);

        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter("gender", BigQueryDbType.String, gender),
            new BigQueryParameter("states", BigQueryDbType.Array, states)
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}: {row["count"]}");
        }
    }
}

Go

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Go 設定操作說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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)
	}
	defer client.Close()

	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

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryParameterValue;
import com.google.cloud.bigquery.TableResult;

// Sample to running a query with array query parameters.
public class QueryWithArrayParameters {

  public static void runQueryWithArrayParameters() {
    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;";
    queryWithArrayParameters(query, gender, states);
  }

  public static void queryWithArrayParameters(String query, String gender, 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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // 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();

      TableResult results = 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 | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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));
}

Python

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Python 設定操作說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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

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

在參數化查詢中使用時間戳記

如要在查詢參數中使用時間戳記，基礎 REST API 會採用 TIMESTAMP 類型的值，格式為 YYYY-MM-DD HH:MM:SS.DDDDDD time_zone。如果您使用用戶端程式庫，請以該語言建立內建日期物件，程式庫會將其轉換為正確格式。詳情請參閱下列特定語言的範例。

如要進一步瞭解 TIMESTAMP 型別，請參閱資料型別參考資料。

控制台

Google Cloud 主控台不支援參數化查詢。

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. 下列查詢會把一小時加到時間戳記參數值中：

   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

如要使用時間戳記參數，請將查詢工作設定中的 parameterType 設定為 TIMESTAMP。

下列查詢會把一小時加到時間戳記參數值中。

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

在 Google APIs Explorer 中嘗試這個範例。

C#

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 C# 設定操作說明進行操作。詳情請參閱 BigQuery C# API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryWithTimestampParameters
{
    public void QueryWithTimestampParameters(string projectId = "project-id")
    {
        var timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeKind.Utc);

        // Note: Standard SQL is required to use query parameters.
        var query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";

        // Initialize client that will be used to send requests.
        var client = BigQueryClient.Create(projectId);

        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter("ts_value", BigQueryDbType.Timestamp, timestamp),
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine(row[0]);
        }
    }
}

Go

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Go 設定操作說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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)
	}
	defer client.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)
	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

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryParameterValue;
import com.google.cloud.bigquery.TableResult;
import org.threeten.bp.LocalDateTime;
import org.threeten.bp.ZoneOffset;
import org.threeten.bp.ZonedDateTime;

// Sample to running a query with timestamp query parameters.
public class QueryWithTimestampParameters {

  public static void runQueryWithTimestampParameters() {
    queryWithTimestampParameters();
  }

  public static void queryWithTimestampParameters() {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      ZonedDateTime timestamp = LocalDateTime.of(2016, 12, 7, 8, 0, 0).atZone(ZoneOffset.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.toInstant().toEpochMilli() * 1000))
              .build();

      TableResult results = 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 | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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_));
}

Python

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Python 設定操作說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import datetime

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=datetime.timezone.utc),
        )
    ]
)
results = client.query_and_wait(
    query, job_config=job_config
)  # Make an API request.

for row in results:
    print(row)

在參數化查詢中使用結構

如要在查詢參數中使用結構，請將類型設定為 STRUCT<T>，其中 T 會定義結構中的欄位和類型。欄位定義是以逗號分隔，且格式為 field_name TF，其中 TF 是欄位的類型。舉例來說，STRUCT<x INT64, y STRING> 會利用名為 x 且類型為 INT64 的欄位，以及第二個名為 y 且類型為 STRING 的欄位來定義結構。

如要進一步瞭解 STRUCT 型別，請參閱資料型別參考資料 。

控制台

Google Cloud 主控台不支援參數化查詢。

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. 下列的一般查詢會示範，如何藉由傳回參數值來使用結構類型：

   bq query \
      --use_legacy_sql=false \
      --parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \
      'SELECT
        @struct_value AS s;'

API

如要使用結構參數，請將查詢工作設定中的 parameterType 設定為 STRUCT。

請將每個結構欄位的物件，新增至工作的 queryParameters 中的 structTypes。如果結構值是純量，請將 type 設定為值的類型，例如 STRING。如果結構值是陣列，請將參數類型設定為 ARRAY，並將巢狀的 arrayType 欄位設定為適當的類型。如果結構值是結構，請將 type 設定為 STRUCT，並新增所需的 structTypes。

下列的一般查詢會示範，如何藉由傳回參數值來使用結構類型。

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

在 Google APIs Explorer 中嘗試這個範例。

C#

.NET 適用的 BigQuery 用戶端程式庫不支援結構體參數。

Go

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Go 設定操作說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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)
	}
	defer client.Close()

	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
}

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryParameterValue;
import com.google.cloud.bigquery.TableResult;
import java.util.HashMap;
import java.util.Map;

public class QueryWithStructsParameters {

  public static void queryWithStructsParameters() {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Create struct
      Map<String, QueryParameterValue> struct = new HashMap<>();
      struct.put("booleanField", QueryParameterValue.bool(true));
      struct.put("integerField", QueryParameterValue.string("test-stringField"));
      struct.put("stringField", QueryParameterValue.int64(10));
      QueryParameterValue recordValue = QueryParameterValue.struct(struct);

      String query = "SELECT STRUCT(@recordField) AS record";
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .setUseLegacySql(false)
              .addNamedParameter("recordField", recordValue)
              .build();

      TableResult results = 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 | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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));
}

Python

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Python 設定操作說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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"),
        )
    ]
)
results = client.query_and_wait(
    query, job_config=job_config
)  # Make an API request and waits for results.

for row in results:
    print(row.s)