创建 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 范围。 尽管系统在默认情况下并未启用此访问权限,但您可以通过以下机制使用 Google Cloud 控制台、bq 命令行工具或 API 启用它:
控制台
在 Google Cloud 控制台中创建永久表时,请按照基于 Web 的身份验证步骤进行操作。出现提示时,请点击允许,以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。
gcloud
启用 Google 云端硬盘访问权限的方法如下:
输入以下命令,确保您拥有最新版本的 Google Cloud CLI。
gcloud components update
输入以下命令,进行 Google 云端硬盘身份验证。
gcloud auth login --enable-gdrive-access
API
如果您使用的是 BigQuery API,则除了 BigQuery 的范围之外,还应请求 Google 云端硬盘的 OAuth 范围。
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
Java
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Java 设置说明进行操作。如需了解详情,请参阅 BigQuery Java API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
所需的角色
如需创建外部表,您需要拥有 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
是您要创建的表的名称。
例如,以下命令使用 /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
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
Java
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Java 设置说明进行操作。如需了解详情,请参阅 BigQuery Java API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
查询外部表
如需了解详情,请参阅查询云端硬盘数据。
_FILE_NAME 伪列
基于外部数据源的表可提供名为 _FILE_NAME
的伪列。此列包含相应行所属文件的完全限定路径,且仅可用于引用存储在 Cloud Storage 和 Google 云端硬盘中的外部数据的表。
_FILE_NAME
列名为预留名称,也就是说,您不能在任何表中使用该名称创建列。