创建对象表

本文档介绍了如何通过创建对象表访问 BigQuery 中的非结构化数据。

如需创建对象表，您必须完成以下任务：

1. 创建连接以从 Cloud Storage 读取对象信息。
2. 向与连接关联的服务账号授予读取 Cloud Storage 信息的权限。
3. 使用 CREATE EXTERNAL TABLE 语句创建对象表并将其与连接相关联。

准备工作

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. Enable the BigQuery and BigQuery Connection API APIs.

   Enable the APIs

5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the BigQuery and BigQuery Connection API APIs.

   Enable the APIs

8. 确保 BigQuery 管理员已创建连接并设置对 Cloud Storage 的访问权限。

所需的角色

如需使用对象表，用户需要根据他们在组织中的角色拥有以下 IAM 权限。如需详细了解用户角色，请参阅安全模型。如需详细了解如何授予权限，请参阅查看可针对资源授予的角色。

• 数据湖管理员

  如需获得连接到 Cloud Storage 所需的权限，请让您的管理员向您授予项目的 BigQuery Connection Admin (roles/bigquery.connectionAdmin) 角色。

  如需获取创建和管理 Cloud Storage 存储桶所需的权限，请让您的管理员授予您项目的 Storage Admin (roles/storage.admin) 角色。

  此预定义角色包含连接到 Cloud Storage 以及创建和管理 Cloud Storage 存储桶所需的权限。如需查看所需的确切权限，请展开所需权限部分：

  所需权限

  • bigquery.connections.create
  • bigquery.connections.get
  • bigquery.connections.list
  • bigquery.connections.update
  • bigquery.connections.use
  • bigquery.connections.delete
  • storage.bucket.*
  • storage.object.*

• 数据仓库管理员

  如需获得创建对象表所需的权限，请让您的管理员向您授予项目的以下角色：

  • BigQuery Data Editor (roles/bigquery.dataEditor) 角色。
  • BigQuery Connection Admin (roles/bigquery.connectionAdmin) 角色。

  此预定义角色包含创建对象表所需的权限。如需查看所需的确切权限，请展开所需权限部分：

  所需权限

  • bigquery.tables.create
  • bigquery.tables.update
  • bigquery.connections.delegate

• 数据分析师

  如需获取查询对象表所需的权限，请让管理员授予您项目的以下角色：

  • BigQuery Data Viewer (roles/bigquery.dataViewer) 角色
  • BigQuery Connection User (roles/bigquery.connectionUser) 角色

  此预定义角色包含查询对象表所需的权限。如需查看所需的确切权限，请展开所需权限部分：

  所需权限

  • bigquery.jobs.create
  • bigquery.tables.get
  • bigquery.tables.getData
  • bigquery.readsessions.create

  您也可以使用自定义角色或其他预定义角色来获取这些权限。

注意：数据分析师不应具有以下权限：

• 直接从 Cloud Storage 读取对象的权限（请参阅 Storage Object Viewer IAM 角色），使数据分析师可以规避数据仓库管理员设置的访问权限控制。

• 能够将表绑定到连接（例如 BigQuery Connection Administrator）。

  否则，数据分析师可以创建没有任何访问权限控制的新对象表，从而规避数据仓库管理员设置的控制。

创建对象表

在创建对象表之前，您必须已有包含对象表的数据集。如需了解详情，请参阅创建数据集。

如需创建对象表，请执行以下操作：

SQL

使用 CREATE EXTERNAL TABLE 语句。

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，输入以下语句：

   CREATE EXTERNAL TABLE `PROJECT_ID.DATASET_ID.TABLE_NAME`
   WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
   OPTIONS(
     object_metadata = 'SIMPLE',
     uris = ['BUCKET_PATH'[,...]],
     max_staleness = STALENESS_INTERVAL,
     metadata_cache_mode = 'CACHE_MODE');

   请替换以下内容：

   • PROJECT_ID：您的项目 ID。
   • DATASET_ID：包含对象表的数据集的 ID。
   • TABLE_NAME：对象表的名称。
   • REGION：包含连接的单区域或多区域。
   • CONNECTION_ID：要与此对象表一起使用的云资源连接的 ID。连接决定了使用哪个服务账号从 Cloud Storage 读取数据。

     当您在 Google Cloud 控制台中查看连接详情时，连接 ID 是连接 ID 中显示的完全限定连接 ID 的最后一部分中的值，例如 projects/myproject/locations/connection_location/connections/myconnection。

   • BUCKET_PATH：包含对象表所表示的对象的 Cloud Storage 存储桶的路径，格式为 ['gs://bucket_name/[folder_name/]*']。

     您可以在每个路径中使用一个星号 (*) 通配符来限制对象表中包含的文件。例如，如果存储桶包含多种类型的非结构化数据，您可以通过指定 ['gs://bucket_name/*.pdf'] 仅对 PDF 对象创建对象表。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

     您可以通过提供多个路径为 uris 选项指定多个存储桶，例如 ['gs://mybucket1/*', 'gs://mybucket2/folder5/*']。

     如需详细了解如何在 BigQuery 中使用 Cloud Storage URI，请参阅 Cloud Storage 资源路径。

   • STALENESS_INTERVAL：指定对对象表执行的操作是否使用了缓存的元数据，以及操作使用的缓存元数据的新鲜度。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

     如需停用元数据缓存，请指定 0。这是默认设置。

     如需启用元数据缓存，请指定 30 分钟到 7 天之间的间隔时间字面量值。例如，指定 INTERVAL 4 HOUR 表示 4 小时过时间隔时间。使用此值时，如果缓存的元数据在过去 4 小时内刷新，则对表执行的操作会使用缓存的元数据。如果缓存的元数据早于该值，则操作会从 Cloud Storage 检索元数据。

   • CACHE_MODE：指定元数据缓存是自动刷新还是手动刷新。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

     如果设置为 AUTOMATIC，元数据缓存会按系统定义的间隔时间刷新，通常在 30 到 60 分钟之间。

     如果要根据您确定的时间表刷新元数据缓存，请设置为 MANUAL。在这种情况下，您可以调用 BQ.REFRESH_EXTERNAL_METADATA_CACHE 系统过程来刷新缓存。

     如果 STALENESS_INTERVAL 设置为大于 0 的值，您必须设置 CACHE_MODE。

3. 点击 运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

示例

以下示例会创建一个元数据缓存过时间隔时间为 1 天的对象表：

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://mybucket/*'],
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

以下示例会对三个 Cloud Storage 存储桶中的对象创建一个对象表：

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket1/*','gs://bucket2/folder1/*','gs://bucket3/*']
);

以下示例仅会对 Cloud Storage 存储桶中的 PDF 对象创建对象表：

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket1/*.pdf']
);

bq

使用 bq mk 命令。

bq mk --table \
--external_table_definition=[e-mail]_ID \
--object_metadata=SIMPLE \
--max_staleness=STALENESS_INTERVAL \
--metadata_cache_mode=CACHE_MODE \
PROJECT_ID:DATASET_ID.TABLE_NAME

请替换以下内容：

• PROJECT_ID：您的项目 ID。
• DATASET_ID：包含对象表的数据集的 ID。
• TABLE_NAME：对象表的名称。
• REGION：包含连接的单区域或多区域。
• CONNECTION_ID：要与此外部表一起使用的云资源连接的 ID。连接决定了使用哪个服务账号从 Cloud Storage 读取数据。

  当您在 Google Cloud 控制台中查看连接详情时，连接 ID 是连接 ID 中显示的完全限定连接 ID 的最后一部分中的值，例如 projects/myproject/locations/connection_location/connections/myconnection。

• BUCKET_PATH：包含对象表所表示的对象的 Cloud Storage 存储桶的路径，格式为 gs://bucket_name/[folder_name/]*。

  您可以在每个路径中使用一个星号 (*) 通配符来限制对象表中包含的文件。例如，如果存储桶包含多种类型的非结构化数据，您可以通过指定 gs://bucket_name/*.pdf 仅对 PDF 对象创建对象表。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

  您可以通过提供多个路径为 uris 选项指定多个存储桶，例如 gs://mybucket1/*,gs://mybucket2/folder5/*。

  如需详细了解如何在 BigQuery 中使用 Cloud Storage URI，请参阅 Cloud Storage 资源路径。

• STALENESS_INTERVAL：指定对对象表执行的操作是否使用了缓存的元数据，以及操作使用的缓存元数据的新鲜度。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

  如需停用元数据缓存，请指定 0。这是默认设置。

  如需启用元数据缓存，请使用 INTERVAL 数据类型文档中所述的 Y-M D H:M:S 格式指定 30 分钟到 7 天之间的间隔时间值。例如，指定 0-0 0 4:0:0 表示 4 小时过时间隔时间。使用此值时，如果缓存的元数据在过去 4 小时内刷新，则对表执行的操作会使用缓存的元数据。如果缓存的元数据早于该值，则操作会从 Cloud Storage 检索元数据。

• CACHE_MODE：指定元数据缓存是自动刷新还是手动刷新。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

  如果设置为 AUTOMATIC，元数据缓存会按系统定义的间隔时间刷新，通常在 30 到 60 分钟之间。

  如果要根据您确定的时间表刷新元数据缓存，请设置为 MANUAL。在这种情况下，您可以调用 BQ.REFRESH_EXTERNAL_METADATA_CACHE 系统过程来刷新缓存。

  如果 STALENESS_INTERVAL 设置为大于 0 的值，您必须设置 CACHE_MODE。

示例

以下示例会创建一个元数据缓存过时间隔时间为 1 天的对象表：

bq mk --table \
--external_table_definition=gs://mybucket/*@us.my-connection \
--object_metadata=SIMPLE \
--max_staleness=0-0 1 0:0:0 \
--metadata_cache_mode=AUTOMATIC \
my_dataset.object_table

以下示例会对三个 Cloud Storage 存储桶中的对象创建一个对象表：

bq mk --table \
--external_table_definition=gs://bucket1/*,gs://bucket2/folder1/*,gs://bucket3/*@us.my-connection \
--object_metadata=SIMPLE \
my_dataset.object_table

以下示例仅会对 Cloud Storage 存储桶中的 PDF 对象创建对象表：

bq mk --table \
--external_table_definition=gs://bucket1/*[e-mail]-connection \
--object_metadata=SIMPLE \
my_dataset.object_table

API

调用 tables.insert 方法。 添加 ExternalDataConfiguration 对象，并在传入的 Table 资源中将 objectMetadata 字段设置为 SIMPLE。

以下示例展示了如何使用 curl 调用此方法：

ACCESS_TOKEN=$(gcloud auth print-access-token) curl \
-H "Authorization: Bearer ${ACCESS_TOKEN}" \
-H "Content-Type: application/json" \
-X POST \
-d '{"tableReference": {"projectId": "my_project", "datasetId": "my_dataset", "tableId": "object_table_name"}, "externalDataConfiguration": {"objectMetadata": "SIMPLE", "sourceUris": ["gs://mybucket/*"]}}' \
https://www.googleapis.com/bigquery/v2/projects/my_project/datasets/my_dataset/tables

Terraform

本示例创建一个启用了手动刷新元数据缓存的对象表。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

为对象表指定的关键字段是 google_bigquery_table.external_data_configuration.object_metadata、google_bigquery_table.external_data_configuration.metadata_cache_mode 和 google_bigquery_table.max_staleness。如需详细了解每种资源，请参阅 Terraform BigQuery 文档。

# This queries the provider for project information.
data "google_project" "default" {}

# This creates a connection in the US region named "my-connection-id".
# This connection is used to access the bucket.
resource "google_bigquery_connection" "default" {
  connection_id = "my-connection-id"
  location      = "US"
  cloud_resource {}
}

# This grants the previous connection IAM role access to the bucket.
resource "google_project_iam_member" "default" {
  role    = "roles/storage.objectViewer"
  project = data.google_project.default.project_id
  member  = "serviceAccount:${google_bigquery_connection.default.cloud_resource[0].service_account_id}"
}

# This defines a Google BigQuery dataset.
resource "google_bigquery_dataset" "default" {
  dataset_id = "my_dataset_id"
}

# This creates a bucket in the US region named "my-bucket" with a pseudorandom suffix.
resource "random_id" "bucket_name_suffix" {
  byte_length = 8
}
resource "google_storage_bucket" "default" {
  name                        = "my-bucket-${random_id.bucket_name_suffix.hex}"
  location                    = "US"
  force_destroy               = true
  uniform_bucket_level_access = true
}

# This defines a BigQuery object table with manual metadata caching.
resource "google_bigquery_table" "default" {
  deletion_protection = false
  table_id            = "my-table-id"
  dataset_id          = google_bigquery_dataset.default.dataset_id
  external_data_configuration {
    connection_id = google_bigquery_connection.default.name
    autodetect    = false
    # `object_metadata is` required for object tables. For more information, see
    # https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/bigquery_table#object_metadata
    object_metadata = "SIMPLE"
    # This defines the source for the prior object table.
    source_uris = [
      "gs://${google_storage_bucket.default.name}/*",
    ]

    metadata_cache_mode = "MANUAL"
  }

  # This ensures that the connection can access the bucket
  # before Terraform creates a table.
  depends_on = [
    google_project_iam_member.default
  ]
}

如需在 Google Cloud 项目中应用 Terraform 配置，请完成以下部分中的步骤。

准备 Cloud Shell

1. 启动 Cloud Shell。

2. 设置要在其中应用 Terraform 配置的默认 Google Cloud 项目。

   您只需为每个项目运行一次以下命令，即可在任何目录中运行它。

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   如果您在 Terraform 配置文件中设置显式值，则环境变量会被替换。

准备目录

每个 Terraform 配置文件都必须有自己的目录（也称为“根模块”）。

1. 在 Cloud Shell 中，创建一个目录，并在该目录中创建一个新文件。文件名必须具有 .tf 扩展名，例如 main.tf。在本教程中，该文件称为 main.tf。

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. 如果您按照教程进行操作，可以在每个部分或步骤中复制示例代码。

   将示例代码复制到新创建的 main.tf 中。

   （可选）从 GitHub 中复制代码。如果端到端解决方案包含 Terraform 代码段，则建议这样做。

3. 查看和修改要应用到您的环境的示例参数。
4. 保存更改。
5. 初始化 Terraform。您只需为每个目录执行一次此操作。

   terraform init

   （可选）如需使用最新的 Google 提供程序版本，请添加 -upgrade 选项：

   terraform init -upgrade

应用更改

1. 查看配置并验证 Terraform 将创建或更新的资源是否符合您的预期：

   terraform plan

   根据需要更正配置。

2. 通过运行以下命令并在提示符处输入 yes 来应用 Terraform 配置：

   terraform apply

   等待 Terraform 显示“应用完成！”消息。

3. 打开您的 Google Cloud 项目以查看结果。在 Google Cloud 控制台的界面中找到资源，以确保 Terraform 已创建或更新它们。

注意：Terraform 示例通常假定您的 Google Cloud 项目中启用了所需的 API。

查询对象表

您可以像查询任何其他 BigQuery 一样查询对象表，例如：

SELECT *
FROM mydataset.myobjecttable;

查询对象表会返回底层对象的元数据。如需了解详情，请参阅对象表架构。

后续步骤

• 了解如何对图片对象表运行推理。
• 了解如何使用远程函数分析对象表。