查询 Cloud Storage 数据

BigQuery 支持查询以下格式的 Cloud Storage 数据:

  • 英文逗号分隔值 (CSV)
  • JSON(以换行符分隔)
  • Avro 文件
  • Cloud Datastore 导出文件
  • Cloud Firestore 导出文件

BigQuery 支持查询以下存储类别的 Cloud Storage 数据:

  • 多区域
  • 单区域
  • Nearline
  • Coldline

如需查询 Cloud Storage 外部数据源,请提供数据的 Cloud Storage URI 路径,并创建一个引用该数据源的表。用于引用 Cloud Storage 数据源的表可以是永久表或临时表

查询存储在 Cloud Storage 中的数据时,请务必考虑您的数据集和 Cloud Storage 存储分区的位置

检索 Cloud Storage URI

要使用 Cloud Storage 数据源创建外部表,则必须提供 Cloud Storage URI。

Cloud Storage URI 包含存储分区名称和对象(文件名)。例如,如果 Cloud Storage 存储分区名为 mybucket,并且数据文件名为 myfile.csv,则存储分区 URI 为 gs://mybucket/myfile.csv。如果数据被分成多个文件,您可以在 URI 中使用通配符。如需了解详情,请参阅 Cloud Storage 请求 URI

BigQuery 不支持在初始双斜杠之后添加多个连续斜杠的源 URI。Cloud Storage 对象名称可包含多个连续斜杠(“/”)字符。但是,BigQuery 会将多个连续斜杠转换为单个斜杠。例如,虽然以下源 URI 在 Cloud Storage 中有效,但在 BigQuery 中无效:gs://bucket/my//object//name

如需检索 Cloud Storage URI,请执行以下操作:

  1. 打开 Cloud Storage 控制台

    Cloud Storage 控制台

  2. 浏览到包含源数据的对象(文件)的位置。

  3. 在 Cloud Storage 控制台顶部,找到并记下对象的路径。如需构成 URI,请将 gs://bucket/file 替换为适当的路径,例如 gs://mybucket/myfile.json, 其中“bucket”是 Cloud Storage 存储分区名称,“file”是包含数据的对象(文件)的名称。

永久外部表与临时外部表

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

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

使用永久外部表查询 Cloud Storage 数据

所需的权限和范围

当您使用永久表在 Cloud Storage 中查询外部数据时,您需要拥有相关权限,才能在项目级别或更高级别运行查询作业,创建指向外部数据的表以及访问表。如果外部数据存储在 Cloud Storage 中,您还需要有权访问 Cloud Storage 存储分区中的数据。

BigQuery 权限

在 BigQuery 中创建和查询外部表至少需要以下权限。

  • bigquery.tables.create
  • bigquery.tables.getData
  • bigquery.jobs.create

以下预定义的 Cloud IAM 角色包含 bigquery.tables.createbigquery.tables.getData 权限:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下预定义的 Cloud IAM 角色包含 bigquery.jobs.create 权限:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。 借助 bigquery.dataOwner 访问权限,用户可以在数据集中创建外部表,但仍需拥有 bigquery.jobs.create 权限才能查询数据。

如需详细了解 BigQuery 中的 Cloud IAM 角色和权限,请参阅预定义的角色和权限

Cloud Storage 权限

如需查询 Cloud Storage 存储分区中的外部数据,您必须拥有 storage.objects.get 权限。如果要使用 URI 通配符,您还必须拥有 storage.objects.list 权限。

可以通过授予预定义的 Cloud IAM 角色 storage.objectViewer 来提供 storage.objects.getstorage.objects.list 权限。

Compute Engine 实例的范围

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

如果您设置某个 Compute Engine 实例作为默认 Compute Engine 服务帐号运行,并且该服务帐号要访问链接到 Cloud Storage 数据源的外部表,则该实例需要对 Cloud Storage 的只读访问权限。系统会自动将 https://www.googleapis.com/auth/devstorage.read_only 范围授予给默认 Compute Engine 服务帐号。如果您创建自己的服务帐号,请将 Cloud Storage 只读范围应用于实例。

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

创建和查询永久外部表

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

如需使用永久表查询外部数据源,需要在 BigQuery 数据集中创建一个链接到外部数据源的表。数据不会存储在 BigQuery 表中。由于该表是永久性的,因此您可以使用数据集级别的访问控制与其他同样有权访问底层外部数据源的人员共享该表。

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

  • 如果使用 tables.insert API 方法创建永久外部表,将会创建包含架构定义和 ExternalDataConfiguration 的表资源。将 autodetect 参数设置为 true 可为受支持的数据源启用架构自动检测
  • 如果使用 CLI 创建永久外部表,可以使用表定义文件,您可以创建并使用自己的架构文件,或者(在命令行上)输入内嵌架构。创建表定义文件时,可以为受支持的数据源启用架构自动检测
  • 如果使用控制台或经典版 BigQuery 网页界面创建永久外部表,则可以手动输入表架构,也可以对受支持的数据源使用架构自动检测

如需创建外部表,请执行以下操作:

控制台

  1. 在 GCP Console 中打开 BigQuery 网页界面。
    转到 GCP Console
  2. 在导航面板的资源部分中,展开您的项目并选择数据集。
  3. 点击窗口右侧的创建表创建表
  4. 创建表页面的来源部分,执行以下操作:

    • 基于以下数据创建表部分,选择 Cloud Storage。

    • 从 Cloud Storage 存储分区中选择文件 (Select file from Cloud Storage bucket) 字段中,浏览并找到相应的文件/Cloud Storage 存储分区,或输入 Cloud Storage URI。请注意,GCP Console 不支持添加多个 URI,但支持使用通配符。Cloud Storage 存储分区必须与您要创建的表所属的数据集位于同一位置。

      选择文件

    • 对于文件格式,请选择数据格式。外部 Cloud Storage 数据的有效格式包括:

      • 英文逗号分隔值 (CSV)
      • JSON(以换行符分隔)
      • Avro
      • Cloud Datastore 备份(也用于 Cloud Firestore)
  5. 创建表页面的目标部分,执行以下操作:

    • 数据集名称部分,选择相应的数据集

      选择数据集

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

    • 表名称字段中,输入您要在 BigQuery 中创建的表的名称。

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

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

创建永久表后,您可以对该表运行查询,就像对原生 BigQuery 表运行查询一样。查询完成后,可以将结果导出为 CSV 或 JSON 文件,将结果保存为表,或将结果保存到 Google 表格。

经典版界面

  1. 转到 BigQuery 网页界面。
    转到 BigQuery 网页界面

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

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

    • Location 部分选择 Cloud Storage,然后在来源字段中输入 Cloud Storage URI。请注意,Cloud Storage URI 支持通配符
    • 对于 File format,请选择数据格式。Cloud Storage 数据的有效格式包括:
      • 英文逗号分隔值 (CSV)
      • JSON(以换行符分隔)
      • Avro
      • Cloud Datastore 备份(也用于 Cloud Firestore)
  4. Create Table 页面的 Destination Table 部分,执行以下操作:

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

    • 对于 JSON 或 CSV 文件,您可以选中 Auto-detect 选项,以启用架构自动检测功能。 自动检测不适用于 Cloud Datastore 导出文件、Cloud Firestore 导出文件和 Avro 文件。系统将自动从自描述源数据中检索这些文件类型的架构信息。

    • 您还可以通过以下方式手动输入 CSV 或 JSON 架构信息:

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

创建永久表后,您可以对该表运行查询,就像对原生 BigQuery 表运行查询一样。查询完成后,可以将结果导出为 CSV 或 JSON 文件,将结果保存为表,或将结果保存到 Google 表格。

CLI

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

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

要使用表定义文件来创建链接到 Cloud Storage 数据源的永久表,请输入以下命令。

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=Cloud Storage URI \
dataset.table

其中:

  • schema 是架构定义(格式为 field:data_type,field:data_type)。
  • source_formatCSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUPDATASTORE_BACKUP 也用于 Cloud Filestore)。
  • Cloud Storage URI 是您的 Cloud Storage URI
  • dataset 是包含该表的数据集的名称。
  • table 是您要创建的表的名称。

例如,以下命令使用以下架构定义来创建名为 sales 的永久表,该表链接到存储在 Cloud Storage 中的 CSV 文件:Region:STRING,Quarter:STRING,Total_sales:INTEGER

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
mydataset.sales

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

bq mk \
--external_table_definition=schema@source_format=Cloud Storage URI \
dataset.table

其中:

  • schema 是本地机器上的 JSON 架构文件的路径
  • source_formatCSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUPDATASTORE_BACKUP 也用于 Cloud Firestore)。
  • Cloud Storage URI 是您的 Cloud Storage URI
  • dataset 是包含该表的数据集的名称。
  • table 是您要创建的表的名称。

例如,以下命令使用 /tmp/sales_schema.json 架构文件创建名为 sales 的表,该表链接到存储在 Cloud Storage 中的 CSV 文件。

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
mydataset.sales

创建永久表后,您可以对该表运行查询,就像对原生 BigQuery 表运行查询一样。查询完成后,可以将结果导出为 CSV 或 JSON 文件,将结果保存为表,或将结果保存到 Google 表格。

API

使用 tables.insert API 方法时创建 ExternalDataConfiguration。指定 schema 属性或将 autodetect 属性设置为 true 以为受支持的数据源启用架构自动检测。

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

# 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('CSV')
external_config.source_uris = [
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv',
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS 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)))

使用临时表查询 Cloud Storage 数据

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

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

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

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

所需权限

当您使用临时表在Cloud Storage 中查询外部数据时,您需要拥有相关权限才能在项目级别或更高级别运行查询作业,并且需要对指向该外部数据的表所属数据集的访问权限。在 Cloud Storage 中查询数据时,您还需要拥有对您的数据所在的存储分区的访问权限。

BigQuery 权限

使用临时表在 BigQuery 中查询外部表至少需要以下权限。

  • bigquery.tables.getData
  • bigquery.jobs.create

以下预定义的 Cloud IAM 角色包含 bigquery.tables.getData 权限:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下预定义的 Cloud IAM 角色包含 bigquery.jobs.create 权限:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。 借助 bigquery.dataOwner 访问权限,用户可以在数据集中创建并访问外部表,但仍需拥有 bigquery.jobs.create 权限才能查询数据。

如需详细了解 BigQuery 中的 Cloud IAM 角色和权限,请参阅预定义的角色和权限

Cloud Storage 权限

如需查询 Cloud Storage 存储分区中的外部数据,您必须拥有 storage.objects.get 权限。如果要使用 URI 通配符,您还必须拥有 storage.objects.list 权限。

可以通过授予预定义的 Cloud IAM 角色 storage.objectViewer 来提供 storage.objects.getstorage.objects.list 权限。

创建和查询临时表

您可以通过使用 CLI、API 或客户端库来创建和查询链接到外部数据源的临时表。

CLI

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

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

(可选)添加 --location 标志并将其值设置为您的位置

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

bq --location=location query \
--external_table_definition=table::definition_file \
'query'

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • table 是您要创建的临时表的名称。
  • definition_file 是本地机器上表定义文件的路径。
  • query 是您要提交到临时表的查询。

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

bq query \
--external_table_definition=sales::sales_def \
'SELECT
  Region,
  Total_sales
FROM
  sales'

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

bq --location=location query \
--external_table_definition=table::schema@source_format=Cloud Storage URI \
'query'

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • table 是您要创建的临时表的名称。
  • schema 是内嵌架构定义(格式为 field:data_type,field:data_type)。
  • source_formatCSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUPDATASTORE_BACKUP 也用于 Cloud Firestore)。
  • Cloud Storage URI 是您的 Cloud Storage URI
  • query 是您要提交到临时表的查询。

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

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

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

bq --location=location query \
--external_table_definition=schema_file@source_format=Cloud Storage URI \
'query'

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • schema_file 是本地机器上 JSON 架构文件的路径。
  • source_formatCSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUPDATASTORE_BACKUP 也用于 Cloud Firestore)。
  • Cloud Storage URI 是您的 Cloud Storage URI
  • query 是您要提交到临时表的查询。

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

bq query
--external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv
'SELECT Region, Total_sales FROM sales'

API

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()

# Configure the external data source and query job
external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv',
]
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)))

Cloud Storage URI 的通配符支持

如果您的 Cloud Storage 数据分散在多个共用一个通用基本名称的文件中,则可以在表定义文件的 URI 中使用通配符。在不使用表定义文件的情况下创建外部表时,也可以使用通配符。

要在 Cloud Storage URI 中添加通配符,可在基本名称后附加星号 (*)。例如,如果您有两个文件,名为 fed-sample000001.csvfed-sample000002.csv,则存储分区 URI 为 gs://mybucket/fed-sample*。此通配符 URI 随后可用于控制台、经典版界面、CLI、API 或客户端库。

存储分区中的对象(文件名)仅可使用一个通配符。通配符可以出现在对象名称内或对象名称末尾。不支持在存储分区名称中附加通配符。

对于 Google Cloud Datastore 导出文件,您只能指定一个 URI,且该 URI 必须以 .backup_info.export_metadata 结尾。

在以下情况下,允许使用 * 通配符:

  • 创建关联到 Cloud Datastore 或 Cloud Firestore 导出文件的外部表
  • 从 Cloud Storage 加载 Cloud Datastore 或 Cloud Firestore 导出数据

_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 是永久外部表的名称
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

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