使用自动分页运行查询并获取行。
包含此代码示例的文档页面
如需查看上下文中使用的代码示例,请参阅以下文档:
代码示例
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.TableId;
import com.google.cloud.bigquery.TableResult;
// Sample to run query with pagination.
public class QueryPagination {
public static void main(String[] args) {
String datasetName = "MY_DATASET_NAME";
String tableName = "MY_TABLE_NAME";
String query =
"SELECT name, SUM(number) as total_people"
+ " FROM `bigquery-public-data.usa_names.usa_1910_2013`"
+ " GROUP BY name"
+ " ORDER BY total_people DESC"
+ " LIMIT 100";
queryPagination(datasetName, tableName, query);
}
public static void queryPagination(String datasetName, String tableName, String query) {
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();
TableId tableId = TableId.of(datasetName, tableName);
QueryJobConfiguration queryConfig =
QueryJobConfiguration.newBuilder(query)
// save results into a table.
.setDestinationTable(tableId)
.build();
bigquery.query(queryConfig);
TableResult results =
bigquery.listTableData(tableId, BigQuery.TableDataListOption.pageSize(20));
// First Page
results
.getValues()
.forEach(row -> row.forEach(val -> System.out.printf("%s,\n", val.toString())));
if (results.hasNextPage()) {
// Next Page
results
.getNextPage()
.getValues()
.forEach(row -> row.forEach(val -> System.out.printf("%s,\n", val.toString())));
}
if (results.hasNextPage()) {
// Remaining Pages
results
.getNextPage()
.iterateAll()
.forEach(row -> row.forEach(val -> System.out.printf("%s,\n", val.toString())));
}
System.out.println("Query pagination performed successfully.");
} catch (BigQueryException | InterruptedException e) {
System.out.println("Query not performed \n" + e.toString());
}
}
}
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function queryPagination() {
// Run a query and get rows using automatic pagination.
const query = `SELECT name, SUM(number) as total_people
FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
GROUP BY name
ORDER BY total_people DESC
LIMIT 100`;
// Run the query as a job.
const [job] = await bigquery.createQueryJob(query);
// Wait for job to complete and get rows.
const [rows] = await job.getQueryResults();
console.log('Query results:');
rows.forEach(row => {
console.log(`name: ${row.name}, ${row.total_people} total people`);
});
}
queryPagination();
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 total_people
FROM `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY name
ORDER BY total_people DESC
"""
query_job = client.query(query) # Make an API request.
query_job.result() # Wait for the query to complete.
# Get the destination table for the query results.
#
# All queries write to a destination table. If a destination table is not
# specified, the BigQuery populates it with a reference to a temporary
# anonymous table after the query completes.
destination = query_job.destination
# Get the schema (and other properties) for the destination table.
#
# A schema is useful for converting from BigQuery types to Python types.
destination = client.get_table(destination)
# Download rows.
#
# The client library automatically handles pagination.
print("The query data:")
rows = client.list_rows(destination, max_results=20)
for row in rows:
print("name={}, count={}".format(row["name"], row["total_people"]))
后续步骤
如需搜索和过滤其他 Google Cloud 产品的代码示例,请参阅 Google Cloud 示例浏览器