使用结构体参数运行查询。
包含此代码示例的文档页面
如需查看上下文中使用的代码示例,请参阅以下文档:
代码示例
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
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 参考文档。
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 main(String[] args) {
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 参考文档。
// 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 参考文档。
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"),
)
]
)
query_job = client.query(query, job_config=job_config) # Make an API request.
for row in query_job:
print(row.s)