已命名參數和指定類型

使用具名參數和指定參數類型執行查詢。

程式碼範例

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.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to run query with named types parameters.
public class QueryWithNamedTypesParameters {

  public static void main(String[] args) {
    String[] words = {"and", "is", "the", "moon"};
    String corpus = "romeoandjuliet";
    Integer wordsCount = 250;
    String query =
        "SELECT word, word_count"
            + " FROM `bigquery-public-data.samples.shakespeare`"
            + " WHERE word IN UNNEST(@wordList)"
            + " AND corpus = @corpus"
            + " AND word_count >= @minWordCount"
            + " ORDER BY word_count DESC";
    queryWithNamedTypesParameters(query, words, corpus, wordsCount);
  }

  public static void queryWithNamedTypesParameters(
      String query, String[] words, String corpus, Integer wordsCount) {
    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();

      QueryParameterValue wordList = QueryParameterValue.array(words, StandardSQLTypeName.STRING);
      QueryParameterValue corpusParam = QueryParameterValue.of(corpus, StandardSQLTypeName.STRING);
      QueryParameterValue minWordCount =
          QueryParameterValue.of(wordsCount, StandardSQLTypeName.INT64);

      // Note: Standard SQL is required to use query parameters.
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addNamedParameter("wordList", wordList)
              .addNamedParameter("corpus", corpusParam)
              .addNamedParameter("minWordCount", 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 types 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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「設定用戶端程式庫的驗證機制」。

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

async function queryParamsNamedTypes() {
  // Run a query using named query parameters and provided parameter types.

  // The SQL query to run
  const sqlQuery = `SELECT word, word_count
        FROM \`bigquery-public-data.samples.shakespeare\`
        WHERE word IN UNNEST(@wordList)
        AND corpus = @corpus
        AND word_count >= @minWordCount
        ORDER BY word_count DESC`;

  const queryOptions = {
    query: sqlQuery,
    params: {
      wordList: ['and', 'is', 'the', 'moon'],
      corpus: 'romeoandjuliet',
      minWordCount: 250,
    },
    types: {wordList: ['STRING'], corpus: 'STRING', minWordCount: 'INT64'},
  };

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

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

後續步驟

如要搜尋及篩選其他 Google Cloud 產品的程式碼範例，請參閱Google Cloud 範例瀏覽器。