查询 Google 云端硬盘数据

本页介绍如何使用 BigQuery 查询存储在 Google 云端硬盘中的数据。

BigQuery 支持查询个人 Google 云端硬盘文件和团队云端硬盘文件。如需详细了解 Google 云端硬盘,请参阅 G Suite 学习中心

您可以使用如下格式,查询 Google 云端硬盘中的文件:

  • 英文逗号分隔值 (CSV)
  • JSON(以换行符分隔)
  • Avro 文件
  • Google 表格(仅限第一个标签页)

要直接查询 Google 云端硬盘外部数据源,请提供数据的 Google 云端硬盘 URI 路径,并创建一个引用数据源的外部表。您可以使用永久表临时表引用 Google 云端硬盘数据源。

检索 Google 云端硬盘 URI

要为 Google 云端硬盘数据源创建外部表格,您必须提供 Google 云端硬盘 URI。检索 Google 云端硬盘 URI 的方法如下:

  1. 转至 Google 云端硬盘

  2. 右键点击您的文件并选择获取共享链接。URI 应该如下所示:https://drive.google.com/open?id=[FILE_ID]

    其中:

    • [FILE_ID] 是 Google 云端硬盘文件的字母数字 ID

启用 Google 云端硬盘访问权限

若要访问 Google 云端硬盘中托管的数据,则需要额外的 OAuth 范围,无论是在定义联合源时还是在查询执行期间都是如此。尽管系统在默认情况下并未启用此访问权限,但您可以通过以下方式使用界面、CLI 或 API 启用它:

Console

在 BigQuery 网页界面中创建永久表时,请按照基于网页的身份验证步骤进行操作。出现提示时,请点击允许,以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

经典版界面

在 BigQuery 网页界面中创建永久表时,请按照基于网页的身份验证步骤进行操作。出现提示时,请点击 Allow,以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

CLI


启用 Google 云端硬盘访问权限的方法如下:

  1. 输入以下命令,确保您拥有最新版本的命令行工具。

    gcloud components update
    
  2. 输入以下命令,进行 Google 云端硬盘身份验证。

    gcloud auth login --enable-gdrive-access
    

API


如果您使用的是 BigQuery API,则除了 BigQuery 的范围之外,还应请求 Google 云端硬盘的 OAuth 范围

Python

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

import google.auth
# from google.cloud import bigquery

# Create credentials with Drive & BigQuery API scopes
# Both APIs must be enabled for your project before running this code
credentials, project = google.auth.default(scopes=[
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/bigquery',
])
client = bigquery.Client(credentials=credentials, project=project)

访问控制和范围

永久外部表的访问控制

您可以共享对链接到 Google 云端硬盘数据源的永久外部表的访问权限。您可以与用户(包括服务帐号)或组共享访问权限。要查询外部表,您的用户或群组(至少)需要具备以下权限:

  • 对外部表所属数据集的 READERbigquery.dataViewer 访问权限
  • 对数据集所属项目的 bigquery.user 访问权限(用于运行查询作业)
  • 对链接到外部表的 Google 云端硬盘文件的 Can view 访问权限

Compute Engine 实例的范围

创建 Compute Engine 实例时,您可以为该实例指定一个范围列表。这些范围用于控制实例对 Google Cloud Platform 产品(包括 Google 云端硬盘)的访问权限。在虚拟机上运行的应用使用服务帐号来调用 Google Cloud API。

如果您将某个 Compute Engine 实例设置为以服务帐号身份运行,并且该服务帐号可以访问一个链接到 Google 云端硬盘数据源的外部表,则您必须为该实例添加 Google 云端硬盘 OAuth 范围 (https://www.googleapis.com/auth/drive)。

要了解如何将范围应用于 Compute Engine 实例,请参阅更改实例的服务帐号和访问范围。要详细了解 Compute Engine 服务帐号,请参阅服务帐号

永久外部表与临时外部表

您可以使用永久表或临时表在 BigQuery 中查询外部数据源。如果使用永久表,您会在 BigQuery 数据集内创建一个链接到外部数据源的表。该表是永久性的,因此您可以使用数据集级层的访问权限控制来与其他同样有权访问底层外部数据源的人员共享该表,还可以随时查询该表。

使用临时表查询外部数据源时,您要提交一条命令,该命令包含一项查询且会创建链接到外部数据源的非永久表。如果使用临时表,将不会在任何 BigQuery 数据集内创建表。该表不会永久存储在数据集内,因此无法与他人共享。使用临时表查询外部数据源适用于对外部数据进行一次性临时查询,或执行提取、转换和加载 (ETL) 过程。

使用永久外部表查询 Google 云端硬盘数据

要使用永久表查询外部数据源,需要在 BigQuery 数据集中创建一个链接到外部数据源的表。数据不会存储在 BigQuery 表格中。由于该表是永久表,因此可使用数据集级别的访问控制与其他也可访问基础外部数据源的人员进行共享。

在 BigQuery 中创建永久外部表时,可通过三种方式指定架构信息:

  • 如果使用 API 创建永久外部表,首先要创建表定义文件,该文件可定义外部数据源的架构和元数据。创建表定义文件时,可以为受支持的数据源启用架构自动检测
  • 如果使用 CLI 创建永久外部表,可以使用表定义文件,您可以创建并使用自己的架构文件,或者(在命令行上)输入架构内嵌。
  • 如果您使用控制台或经典版 BigQuery 网页界面创建永久外部表,则可以手动输入表架构,也可以对受支持的数据源使用架构自动检测

要使用永久外部表查询 Google 云端硬盘数据,请执行以下操作:

  • 创建表定义文件(用于 API,并有选择地用于 CLI)
  • 在 BigQuery 中创建一个链接到外部数据源的表
  • 查询链接到外部数据源的表

创建永久外部表

您可以通过以下方式创建链接到外部数据源的永久表:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。
    转到 BigQuery 网页界面

  2. 在导航面板的资源部分中,展开您的项目并选择数据集。点击窗口右侧的创建表

    创建表

  3. 创建表页面的来源部分,执行以下操作:

    • 基于以下数据创建表部分,选择云端硬盘作为所需的来源类型。

      创建表来源

    • 选择云端硬盘 URI 字段中,输入 Google 云端硬盘 URI。请注意,Google 云端硬盘 URI 不支持通配符。

    • 对于文件格式,请选择数据格式。Google 云端硬盘数据的有效格式包括:

      • 英文逗号分隔值 (CSV)
      • JSON(以换行符分隔)
      • Avro
      • Google 表格(仅限第一个标签页)
  4. 创建表页面的目标位置部分,执行以下操作:

    • 对于数据集名称,请选择相应的数据集,然后在表名称字段中输入您要在 BigQuery 中创建的表的名称。

      选择数据集

    • 验证表类型是否设置为外部表

  5. 架构部分中,输入架构定义。

    • 对于 JSON 或 CSV 文件,您可以选中自动检测选项,以启用架构自动检测功能。Auto-detect 不适用于 Cloud Datastore 导出文件、Cloud Firestore 导出文件和 Avro 文件。系统将自动从自描述源数据中检索这些文件类型的架构信息。
    • 通过以下方式,手动输入架构信息:
      • 启用以文本形式修改,并以 JSON 数组格式输入表架构。注意:您可以输入以下命令来查看 JSON 格式的现有表架构:bq show --format=prettyjson [DATASET].[TABLE]
      • 使用添加字段手动输入架构。
  6. 点击创建表

  7. 如有必要,选择您的帐号,然后点击允许,以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

然后,您可以对该表运行查询,就像对原生 BigQuery 表运行查询一样,但要受到外部数据源的限制

查询完成后,可以将结果下载为 CSV 或 JSON,将结果保存为表,或将结果保存到 Google 表格。如需了解详情,请参阅下载、保存和导出数据

经典版界面

  1. 转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 在导航面板中,将鼠标悬停在数据集上,点击向下箭头图标 向下箭头图标图片,然后点击 Create new table

  3. Create Table 页面的 Source Data 部分,执行以下操作:

    • Location 部分选择 Google Drive,然后在源字段中输入 Google Drive URI。请注意,Google 云端硬盘 URI 不支持通配符。
    • 对于 File format,请选择数据格式。Google 云端硬盘数据的有效格式包括:

      • 英文逗号分隔值 (CSV)
      • JSON(以换行符分隔)
      • Avro
      • Google 表格(仅限第一个标签页)
  4. Create Table 页面的 Destination Table 部分,执行以下操作:

    • 对于 Table name,请选择适当的数据集,然后在表名称字段中输入要在 BigQuery 中创建的永久表的名称。
    • 验证 Table type 是否设置为 External table
  5. Schema 部分中,输入架构信息。

    • 对于 JSON 或 CSV 文件,您可以选中 Auto-detect 选项,以启用架构自动检测功能。Google 表格目前不支持在网页界面中使用架构自动检测功能(但可以通过 CLI 和 API 使用此功能)。此外,Avro 数据源也不支持自动检测功能。系统会从 Avro 文件自动检索架构信息。

    • 对于 CSV、JSON 或 Google 表格文件,您可以手动输入架构信息,方法如下:

      • 点击 Edit as text,并以 JSON 格式输入表架构
      • 使用 Add Field,手动输入架构
  6. Options 部分中选择适用项,然后点击 Create Table

  7. 选择您的帐号,然后点击 Allow,授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

    “客户端工具访问”(Client tools access) 对话框

然后,您可以对该表运行查询,就像对原生 BigQuery 表运行查询一样,但要受到外部数据源的限制

查询完成后,可以将结果下载为 CSV 或 JSON,将结果保存为表,或将结果保存到 Google 表格。详情请参阅下载、保存和导出数据

CLI

您可以使用 bq mk 命令在 BigQuery 命令行工具中创建表。使用 CLI 创建链接到外部数据源的表时,可以使用以下方法标识表的架构:

  • 表定义文件(存储在本地机器上)
  • 内嵌架构定义
  • JSON 架构文件(存储在本地计算机上)

要使用表定义文件创建链接到 Google 云端硬盘数据源的永久表,请输入以下命令。

bq mk --external_table_definition=[DEFINITION_FILE] [DATASET_ID].[TABLE_NAME]

其中:

  • [DEFINITION_FILE] 是本地机器上表定义文件的路径
  • [DATASET_ID] 是包含表的数据集名称
  • [TABLE_NAME] 是您要创建的表的名称

例如,以下命令使用名为 mytable 的表定义文件创建名为 mytable_def 的永久表。

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

要使用内嵌架构定义创建链接到外部数据源的永久表,请输入以下命令。

bq mk --external_table_definition=[SCHEMA]@[SOURCE_FORMAT]=[DRIVE_URI] [DATASET_ID].[TABLE_NAME]

其中:

  • [SCHEMA] 是架构定义(格式为 [FIELD]:[DATA_TYPE], [FIELD]:[DATA_TYPE]
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS
  • [DRIVE_URI] 是您的 Google 云端硬盘 URI
  • [DATASET_ID] 是包含表的数据集名称
  • [TABLE_NAME] 是您要创建的表的名称

例如,以下命令使用以下架构定义创建名为 sales 的永久表,该表链接到存储在 Google 云端硬盘中的 Google 表格文件:Region:STRING,Quarter:STRING,Total_sales:INTEGER

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_ID].[TABLE_NAME]

其中:

  • [SCHEMA_FILE] 是本地机器上 JSON 架构文件的路径
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS
  • [DRIVE_URI] 是您的 Google 云端硬盘 URI
  • [DATASET_ID] 是包含表的数据集名称
  • [TABLE_NAME] 是您要创建的表的名称

例如,以下命令使用 /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

使用 externalDataConfiguration 属性指定外部数据源。

Python

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

import google.auth
# from google.cloud import bigquery
# dataset_id = 'my_dataset'

# Create credentials with Drive & BigQuery API scopes
# Both APIs must be enabled for your project before running this code
credentials, project = google.auth.default(scopes=[
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/bigquery',
])
client = bigquery.Client(credentials=credentials, project=project)

# Configure the external data source
dataset_ref = client.dataset(dataset_id)
table_id = 'us_states'
schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
table = bigquery.Table(dataset_ref.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]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(
    dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print('There are {} states with names starting with W.'.format(
    len(w_states)))

使用临时表查询 Google 云端硬盘数据

要在不创建永久表的情况下查询外部数据源,请运行命令以便进行以下组合:

  • 表定义文件与查询组合
  • 将内嵌架构定义与查询组合
  • 将 JSON 架构定义文件与查询组合

表定义文件或提供的架构用于创建临时外部表,查询针对临时外部表运行。BigQuery CLI 和 API 支持使用临时表查询外部数据源。

如果使用临时外部表,则不会在您的某个 BigQuery 数据集中创建表。该表不会永久存储在数据集内,因此无法与他人共享。使用临时表查询外部数据源适用于对外部数据进行一次性临时查询,或执行提取、转换和加载 (ETL) 过程。

创建和查询临时表

您可以使用 CLI 或 API 创建和查询链接到外部数据源的临时表。

CLI

您可以使用带有 --external_table_definition 标志的 bq query 命令查询链接到外部数据源的临时表。使用 CLI 查询链接到外部数据源的临时表时,可以使用以下方法标识表的架构:

  • 表定义文件(存储在本地机器上)
  • 内嵌架构定义
  • JSON 架构文件(存储在本地计算机上)

要使用表定义文件查询链接到外部数据源的临时表,请输入以下命令。

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[DEFINITION_FILE] '[QUERY]'

其中:

  • [LOCATION] 是您的位置--location 是可选标志。
  • [TABLE_NAME] 是您要创建的临时表的名称
  • [DEFINITION_FILE] 是本地机器上表定义文件的路径
  • [QUERY] 是您要提交到临时表的查询

例如,以下命令使用名为 sales_def 的表定义文件创建并查询名为 sales 的临时表。

bq --location=US query --external_table_definition=sales::sales_def 'SELECT Region,Total_sales FROM sales;'

要使用内嵌架构定义查询链接到外部数据源的临时表,请输入以下命令。

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[SCHEMA]@[SOURCE_FORMAT]=[DRIVE_URI] '[QUERY]'

其中:

  • [LOCATION] 是您的位置--location 是可选标志。
  • [TABLE_NAME] 是您要创建的临时表的名称
  • [SCHEMA] 是内嵌架构定义(格式为 [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS
  • [DRIVE_URI] 是您的 Google 云端硬盘 URI
  • [QUERY] 是您要提交到临时表的查询

例如,以下命令使用以下架构定义创建并查询名为 sales 的临时表,该表链接到存储在 Google 云端硬盘中的 CSV 文件:Region:STRING,Quarter:STRING,Total_sales:INTEGER

bq --location=US query --external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd 'SELECT Region,Total_sales FROM sales;'

要使用 JSON 架构文件查询链接到外部数据源的临时表,请输入以下命令。

bq --location=[LOCATION] query --external_table_definition=[SCHEMA_FILE]@[SOURCE_FORMAT]=[DRIVE_URI] '[QUERY]'

其中:

  • [LOCATION] 是您的位置--location 是可选标志。
  • [SCHEMA_FILE] 是本地机器上 JSON 架构文件的路径
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS
  • [DRIVE_URI] 是您的 Google 云端硬盘 URI
  • [QUERY] 是您要提交到临时表的查询

例如,以下命令使用架构文件 /tmp/sales_schema.json 创建并查询名为 sales 的临时表,该表链接到存储在 Google 云端硬盘中的 CSV 文件。

bq --location=US query --external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd 'SELECT Total_sales FROM sales;'

API

Python

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

import google.auth
# from google.cloud import bigquery

# Create credentials with Drive & BigQuery API scopes
# Both APIs must be enabled for your project before running this code
credentials, project = google.auth.default(scopes=[
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/bigquery',
])
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')
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table_id = 'us_states'
job_config = bigquery.QueryJobConfig()
job_config.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)  # API request

w_states = list(query_job)  # Waits for query to finish
print('There are {} states with names starting with W.'.format(
    len(w_states)))

_FILE_NAME 伪列

基于外部数据源的表提供名为 _FILE_NAME 的伪列。此列包含该行所属文件的完全限定路径。此列仅可用于引用存储在 Cloud StorageGoogle 云端硬盘中的外部数据的表。

_FILE_NAME 列名为预留名称,也就是说不能在任何表中使用该名称创建列。要选择 _FILE_NAME 的值,必须使用别名。以下示例查询演示了通过将别名 fn 分配给伪列来选择 _FILE_NAME

bq query --project_id=[PROJECT_ID] --use_legacy_sql=false 'SELECT name, _FILE_NAME AS fn from [DATASET].[TABLE_NAME] where name contains "Alex";' 

其中:

  • [PROJECT_ID] 是有效的项目 ID(如果使用 Cloud Shell 或在 Cloud SDK 中设置默认项目,则不需要此标志)
  • [DATASET] 是存储永久外部表的数据集名称
  • [TABLE_NAME] 是永久外部表的名称

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面