创建 Google 云端硬盘外部表
本文档介绍如何基于 Google 云端硬盘中存储的数据创建外部表。
BigQuery 支持基于个人云端硬盘文件和共享文件的外部表。如需详细了解云端硬盘,请参阅云端硬盘培训和帮助。
您可以基于 Google 云端硬盘中具有以下格式的文件创建外部表:
- 逗号分隔值 (CSV)
- 以换行符分隔的 JSON
- Avro
- Google 表格
准备工作
在创建外部表之前,请收集一些信息并确保您有权创建该表。
检索云端硬盘 URI
如需为 Google 云端硬盘数据源创建外部表,您必须提供云端硬盘 URI。您可以直接从云端硬盘数据的网址中检索云端硬盘 URI:
URI 格式
https://docs.google.com/spreadsheets/d/FILE_ID
或
https://drive.google.com/open?id=FILE_ID
其中 FILE_ID
是 Google 云端硬盘文件的字母数字 ID。
进行身份验证并启用云端硬盘访问权限
访问云端硬盘中托管的数据需要额外的 OAuth 范围。如需向 BigQuery 进行身份验证并启用云端硬盘访问权限,请执行以下操作:
控制台
在 Google Cloud 控制台中创建外部表时,请按照基于网络的身份验证步骤进行操作。出现提示时,请点击允许,以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。
gcloud
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
输入以下命令,确保您拥有最新版本的 Google Cloud CLI。
gcloud components update
输入以下命令,进行 Google 云端硬盘身份验证。
gcloud auth login --enable-gdrive-access
API
除了 BigQuery 的范围之外,还需请求适当的云端硬盘 OAuth 范围:
- 运行
gcloud auth login --enable-gdrive-access
命令进行登录。 - 运行
gcloud auth print-access-token
命令,获取具有云端硬盘范围的 OAuth 访问令牌以供 API 使用。
Python
执行以下操作,在本地环境中设置具有所需范围的应用默认凭据 (ADC):
安装 Google Cloud CLI,然后通过运行以下命令对其进行初始化:
gcloud init
为您的 Google 账号创建本地身份验证凭据:
gcloud auth application-default login \ --client-id-file=CLIENT_ID_FILE \ --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
将
CLIENT_ID_FILE
替换为包含您的 OAuth 客户端 ID 的文件。如需了解详情,请参阅使用 gcloud CLI 提供的用户凭据。
Java
执行以下操作,在本地环境中设置具有所需范围的应用默认凭据 (ADC):
安装 Google Cloud CLI,然后通过运行以下命令对其进行初始化:
gcloud init
为您的 Google 账号创建本地身份验证凭据:
gcloud auth application-default login \ --client-id-file=CLIENT_ID_FILE \ --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
将
CLIENT_ID_FILE
替换为包含您的 OAuth 客户端 ID 的文件。如需了解详情,请参阅使用 gcloud CLI 提供的用户凭据。
所需的角色
如需创建外部表,您需要拥有 bigquery.tables.create
BigQuery Identity and Access Management (IAM) 权限。
以下每个预定义的 Identity and Access Management 角色都具有此权限:
- BigQuery Data Editor (
roles/bigquery.dataEditor
) - BigQuery Data Owner (
roles/bigquery.dataOwner
) - BigQuery Admin (
roles/bigquery.admin
)
如果您不是这些角色中的主账号,请让您的管理员授予您访问权限或为您创建外部表。
如需详细了解 BigQuery 中的 Identity and Access Management 角色和权限,请参阅预定义的角色和权限。
创建外部表
您可以通过以下方式创建链接到外部数据源的永久表:
- 使用 Google Cloud 控制台
- 使用 bq 命令行工具的
mk
命令 - 使用
tables.insert
API 方法时创建ExternalDataConfiguration
- 使用客户端库
如需创建外部表,请执行以下操作:
控制台
- 在 Google Cloud 控制台中,打开 BigQuery 页面。
在浏览器面板中,展开您的项目并选择数据集。
展开
操作选项,然后点击打开。在详情面板中,点击创建表
。在创建表页面的来源部分,执行以下操作:
在基于以下数据创建表部分,选择云端硬盘。
在选择云端硬盘 URI 字段中,输入 Google 云端硬盘 URI。请注意,Google 云端硬盘 URI 不支持使用通配符。
对于文件格式,请选择数据格式。Google 云端硬盘数据的有效格式包括:
- 逗号分隔值 (CSV)
- 以换行符分隔的 JSON
- Avro
- 表格
(可选)如果选择“Google 表格”,请在工作表范围(可选)框中指定要查询的工作表和单元格范围。您可以指定工作表名称,也可以指定
sheet_name!top_left_cell_id:bottom_right_cell_id
作为单元格范围,例如“Sheet1!A1:B20”。如果未指定工作表范围,则系统会使用文件中的第一个工作表。在创建表页面的目标部分,执行以下操作:
对于数据集名称,请选择相应的数据集,然后在表名称字段中输入您要在 BigQuery 中创建的表的名称。
验证表类型设置为外部表。
在架构部分中,输入架构定义。
- 对于 JSON 或 CSV 文件,您可以勾选自动检测选项,以启用架构自动检测功能。自动检测功能不适用于 Datastore 导出文件、Firestore 导出文件和 Avro 文件。对于这些文件类型,系统将自动从自描述源数据中检索其架构信息。
- 通过以下方式,手动输入架构信息:
- 启用以文本形式修改,并以 JSON 数组格式输入表架构。注意:您可以在 bq 命令行工具中输入以下命令,以 JSON 格式查看现有表架构:
bq show --format=prettyjson DATASET.TABLE
。 - 使用添加字段手动输入架构。
- 启用以文本形式修改,并以 JSON 数组格式输入表架构。注意:您可以在 bq 命令行工具中输入以下命令,以 JSON 格式查看现有表架构:
点击创建表。
如有必要,选择您的账号,然后点击允许,以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。
接着,您可以对该表运行查询,就像对标准 BigQuery 表运行查询一样,但需遵守外部数据源的限制。
查询完成后,您可以将结果下载为 CSV 或 JSON 格式、将结果保存为表,或将结果保存到 Google 表格。详情请参阅下载、保存和导出数据。
bq
您可以在 bq 命令行工具中使用 bq mk
命令创建表。使用 bq 命令行工具创建链接到外部数据源的表时,您可以使用以下命令标识表的架构:
- 表定义文件(存储在本地机器上)
- 内嵌架构定义
- JSON 架构文件(存储在本地机器上)
如需使用表定义文件创建链接到 Google 云端硬盘数据源的永久表,请输入以下命令。
bq mk \ --external_table_definition=DEFINITION_FILE \ DATASET.TABLE
其中:
DEFINITION_FILE
是本地机器上表定义文件的路径。DATASET
是包含该表的数据集的名称。TABLE
是您要创建的表的名称。
例如,以下命令会使用名为 mytable_def
的表定义文件创建名为 mytable
的永久表。
bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable
如需使用内嵌架构定义创建链接到外部数据源的永久表,请输入以下命令。
bq mk \ --external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE
其中:
SCHEMA
是架构定义,格式为FIELD:DATA_TYPE,FIELD:DATA_TYPE
。SOURCE_FORMAT
是CSV
、NEWLINE_DELIMITED_JSON
、AVRO
或GOOGLE_SHEETS
。DRIVE_URI
是您的 Google 云端硬盘 URI。DATASET
是包含表的数据集的名称。TABLE
是您要创建的表的名称。
例如,以下命令会使用架构定义 Region:STRING,Quarter:STRING,Total_sales:INTEGER
创建名为 sales
的永久表,该表链接到存储在 Google 云端硬盘中的 Google 表格文件。
bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales
如需使用 JSON 架构文件创建链接到外部数据源的永久表,请输入以下命令。
bq mk \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE
其中:
SCHEMA_FILE
是本地机器上 JSON 架构文件的路径。SOURCE_FORMAT
是CSV
、NEWLINE_DELIMITED_JSON
、AVRO
或GOOGLE_SHEETS
。DRIVE_URI
是您的 Google 云端硬盘 URI。DATASET
是包含表的数据集的名称。TABLE
是您要创建的表的名称。
如果您的表定义文件包含特定于 Google 表格的配置,则可以跳过前导行并指定已定义的工作表范围。
以下示例使用 /tmp/sales_schema.json
架构文件创建名为 sales
的表,该表链接到存储在 Google 云端硬盘中的 CSV 文件。
bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales
创建永久表后,可以对该表运行查询,就像对标准 BigQuery 表运行查询一样,但要受到外部数据源的限制。
查询完成后,您可以将结果下载为 CSV 或 JSON 格式、将结果保存为表,或将结果保存到 Google 表格。详情请参阅下载、保存和导出数据。
API
使用 tables.insert
API 方法时创建 ExternalDataConfiguration
。指定 schema
属性或将 autodetect
属性设置为 true
,为受支持的数据源启用架构自动检测功能。
Python
from google.cloud import bigquery import google.auth credentials, project = google.auth.default() # 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) ) )
Java
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 { GoogleCredentials credentials = ServiceAccountCredentials.getApplicationDefault(); // 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()); } } }
查询外部表
如需了解详情,请参阅查询云端硬盘数据。
_FILE_NAME
伪列
基于外部数据源的表可提供名为 _FILE_NAME
的伪列。此列包含相应行所属文件的完全限定路径,且仅可用于引用存储在 Cloud Storage 和 Google 云端硬盘中的外部数据的表。
_FILE_NAME
列名为预留名称,也就是说,您不能在任何表中使用该名称创建列。