使用指定参数运行查询。
包含此代码示例的文档页面
如需查看上下文中使用的代码示例，请参阅以下文档：
代码示例
C#
在尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 C# 设置说明进行操作。如需了解详情，请参阅 BigQuery C# API 参考文档。
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"]}");
}
}
}Go
在尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Go 设置说明进行操作。如需了解详情，请参阅 BigQuery Go API 参考文档。
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
}
Java
试用此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Java 设置说明进行操作。 如需了解详情，请参阅 BigQuery Java API 参考文档。
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 main(String[] args) {
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());
}
}
}Node.js
在尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情，请参阅 BigQuery Node.js API 参考文档。
// 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));
}Python
在尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Python 设置说明进行操作。如需了解详情，请参阅 BigQuery Python API 参考文档。
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))