通过创建临时表查询 Google 表格文件中的数据。
深入探索
如需查看包含此代码示例的详细文档,请参阅以下内容:
代码示例
Java
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Java 设置说明进行操作。如需了解详情,请参阅 BigQuery Java API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;
// Sample to queries an external data source using a temporary table
public class QueryExternalSheetsTemp {
public static void main(String[] args) {
// TODO(developer): Replace these variables before running the sample.
String tableName = "MY_TABLE_NAME";
String sourceUri =
"https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
Schema schema =
Schema.of(
Field.of("name", StandardSQLTypeName.STRING),
Field.of("post_abbr", StandardSQLTypeName.STRING));
String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
queryExternalSheetsTemp(tableName, sourceUri, schema, query);
}
public static void queryExternalSheetsTemp(
String tableName, String sourceUri, Schema schema, String query) {
try {
// Create credentials with Drive & BigQuery API scopes.
// Both APIs must be enabled for your project before running this code.
GoogleCredentials credentials =
ServiceAccountCredentials.getApplicationDefault()
.createScoped(
ImmutableSet.of(
"https://www.googleapis.com/auth/bigquery",
"https://www.googleapis.com/auth/drive"));
// 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.newBuilder().setCredentials(credentials).build().getService();
// Skip header row in the file.
GoogleSheetsOptions sheetsOptions =
GoogleSheetsOptions.newBuilder()
.setSkipLeadingRows(1) // Optionally skip header row.
.setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
.build();
// Configure the external data source and query job.
ExternalTableDefinition externalTable =
ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
QueryJobConfiguration queryConfig =
QueryJobConfiguration.newBuilder(query)
.addTableDefinition(tableName, externalTable)
.build();
// Example query to find states starting with 'W'
TableResult results = bigquery.query(queryConfig);
results
.iterateAll()
.forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));
System.out.println("Query on external temporary table performed successfully.");
} catch (BigQueryException | InterruptedException | IOException e) {
System.out.println("Query not performed \n" + e.toString());
}
}
}
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
from google.cloud import bigquery
import google.auth
# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
# --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
scopes=[
"https://www.googleapis.com/auth/drive",
"https://www.googleapis.com/auth/cloud-platform",
]
)
# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)
# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
"https://docs.google.com/spreadsheets"
"/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.schema = [
bigquery.SchemaField("name", "STRING"),
bigquery.SchemaField("post_abbr", "STRING"),
]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1 # Optionally skip header row.
options.range = (
"us-states!A20:B49" # Optionally set range of the sheet to query from.
)
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})
# Example query to find states starting with "W".
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)
query_job = client.query(sql, job_config=job_config) # Make an API request.
# Wait for the query to complete.
w_states = list(query_job)
print(
"There are {} states with names starting with W in the selected range.".format(
len(w_states)
)
)
后续步骤
如需搜索和过滤其他 Google Cloud 产品的代码示例,请参阅 Google Cloud 示例浏览器。