通过创建永久表查询 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.TableId;
import com.google.cloud.bigquery.TableInfo;
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 permanent table
public class QueryExternalSheetsPerm {
public static void main(String[] args) {
// TODO(developer): Replace these variables before running the sample.
String datasetName = "MY_DATASET_NAME";
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.%s WHERE name LIKE 'W%%'", datasetName, tableName);
queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
}
public static void queryExternalSheetsPerm(
String datasetName, 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();
TableId tableId = TableId.of(datasetName, tableName);
// Create a permanent table linked to the Sheets file.
ExternalTableDefinition externalTable =
ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
bigquery.create(TableInfo.of(tableId, externalTable));
// Example query to find states starting with 'W'
TableResult results = bigquery.query(QueryJobConfiguration.of(query));
results
.iterateAll()
.forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));
System.out.println("Query on external permanent 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/bigquery",
]
)
# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)
# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"
# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
bigquery.SchemaField("name", "STRING"),
bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
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]
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.external_data_configuration = external_config
# Create a permanent table linked to the Sheets file.
table = client.create_table(table) # Make an API request.
# Example query to find states starting with "W".
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)
results = client.query_and_wait(sql) # Make an API request.
# Wait for the query to complete.
w_states = list(results)
print(
"There are {} states with names starting with W in the selected range.".format(
len(w_states)
)
)后续步骤
如需搜索和过滤其他 Google Cloud 产品的代码示例，请参阅 Google Cloud 示例浏览器。