배열 매개변수를 사용하여 쿼리를 실행합니다.
이 코드 샘플이 포함된 문서 페이지
코드 샘플
C#
이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 C# 설정 안내를 따르세요. 자세한 내용은 BigQuery C# API 참조 문서를 확인하세요.
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 참조 문서를 확인하세요.
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
}
자바
이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 자바 설정 안내를 따르세요. 자세한 내용은 BigQuery 자바 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;
// Sample to running a query with array query parameters.
public class QueryWithArrayParameters {
public static void main(String[] args) {
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 참조 문서를 확인하세요.
// 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 참조 문서를 확인하세요.
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))