为 Cloud Storage 创建 BigLake 外部表

本文档介绍了如何创建 Cloud Storage BigLake 表。 借助 BigLake 表，您可以使用委托访问权限功能来查询 Cloud Storage 中的结构化数据。委托访问权限功能将对 BigLake 表的访问权限与对底层数据存储区的访问权限分离。

准备工作

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

    Go to project selector

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

  3. Enable the BigQuery Connection API.

    Enable the API

    如果要从 Apache Spark 等开源引擎读取 BigLake 表，则需要启用 BigQuery Storage Read API。

  4. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

  5. 确保您有一个 BigQuery 数据集。

  6. 确保您的 Google Cloud SDK 为 366.0.0 版或更高版本：

    gcloud version

    如有需要，请更新 Google Cloud SDK。

    1. 可选：对于 Terraform，需要 terraform-provider-google 4.25.0 或更高版本。terraform-provider-google 版本已在 GitHub 上列出。您可以从 HashiCorp Terraform 下载页面下载最新版本的 Terraform。

  7. 根据外部数据源创建 Cloud 资源连接，并向该连接授予对 Cloud Storage 的访问权限。如果您没有创建连接的适当权限，请让您的 BigQuery 管理员创建连接并与您共享。

所需的角色

如需创建 BigLake 表，您需要拥有以下 BigQuery Identity and Access Management (IAM) 权限：

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

BigQuery Admin (roles/bigquery.admin) 预定义的 Identity and Access Management 角色包含这些权限。

如果您不是此角色的主账号，请让您的管理员授予您访问权限或为您创建 BigLake 表。

如需详细了解 BigQuery 中的 Identity and Access Management 角色和权限，请参阅预定义的角色和权限。

位置注意事项

使用 Cloud Storage 存储数据文件时，您可以借助 Cloud Storage 单区域或双区域存储桶（而不是多区域存储桶）来提高性能。

对未分区数据创建 BigLake 表

如果您熟悉在 BigQuery 中创建表的过程，则创建 BigLake 表的过程类似。 表可以使用 BigLake 支持的任何文件格式。如需了解详情，请参阅限制。

创建 BigLake 表之前，您需要拥有可访问 Cloud Storage 的数据集和 Cloud 资源连接。

如需创建 BigLake 表，请选择以下选项之一：

控制台

  1. 转到 BigQuery 页面。

    转到 BigQuery

  2. 在浏览器窗格中，展开您的项目，然后选择数据集。

  3. 展开 操作选项，然后点击创建表。

  4. 在来源部分，指定以下详细信息：

    1. 在基于以下数据创建表部分，选择 Google Cloud Storage

    2. 在从 GCS 存储桶中选择文件或使用 URI 模式部分，浏览以选择要使用的存储桶和文件，或输入 gs://bucket_name/[folder_name/]file_name 格式的路径。

      您无法在 Google Cloud 控制台中指定多个 URI，但可以通过指定一个星号 (*) 通配符来选择多个文件。例如 gs://mybucket/file_name*。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

      Cloud Storage 存储桶必须与您要创建的表所属的数据集位于同一位置。

    3. 在文件格式部分，选择与您的文件匹配的格式。

  5. 在目标部分，指定以下详细信息：

    1. 在项目部分，选择要在其中创建表的项目。

    2. 在数据集部分，选择要在其中创建表的数据集。

    3. 在表部分，输入您要创建的表的名称。

    4. 在表类型部分，选择外部表。

    5. 选择使用 Cloud 资源连接来创建 BigLake 表。

    6. 在连接 ID 部分，选择您之前创建的连接。

  6. 在架构部分中，您可以启用架构自动检测功能，也可以手动指定架构（如果您拥有源文件）。如果您没有源文件，则必须手动指定架构。

    • 如需启用架构自动检测功能，请选择自动检测选项。

    • 如需手动指定架构，请让自动检测选项处于未选中状态。启用以文本形式修改，然后以 JSON 数组形式输入表架构。

  7. 如需忽略额外列值与架构不匹配的行，请展开高级选项部分，然后选择未知值。

  8. 点击创建表。

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

SQL

使用 CREATE EXTERNAL TABLE DDL 语句. 您可以明确指定架构，也可以使用架构自动检测功能，根据外部数据推断架构。

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

    转到 BigQuery

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

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

    请替换以下内容：

    • PROJECT_ID：要在其中创建表的项目的名称，例如 myproject
    • DATASET：要在其中创建表的 BigQuery 数据集的名称，例如 mydataset
    • EXTERNAL_TABLE_NAME：要创建的表的名称，例如 mytable
    • REGION：包含连接的区域，例如 us
    • CONNECTION_ID：连接 ID，例如 myconnection

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

    • TABLE_FORMAT：要创建的表的格式，例如 PARQUET

      如需详细了解支持的格式，请参阅限制。

    • BUCKET_PATH：包含外部表数据的 Cloud Storage 存储桶的路径，格式为 ['gs://bucket_name/[folder_name/]file_name']。

      您可以通过在路径中指定一个星号 (*) 通配符从存储桶中选择多个文件。例如 ['gs://mybucket/file_name*']。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

      您可以通过提供多个路径来为 uris 选项指定多个存储桶。

      以下示例展示了有效的 uris 值：

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

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

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

      如需停用元数据缓存，请指定 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. 点击 运行。

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

bq

选项 1：表定义文件

使用 bq mkdef 命令创建表定义文件，然后将文件路径传递给 bq mk 命令，如下所示：

bq mkdef \
    --connection_id=CONNECTION_ID \
    --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

bq mk --table \
    --external_table_definition=DEFINITION_FILE \
    --max_staleness=STALENESS_INTERVAL \
    PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME \
    SCHEMA

替换以下内容：

  • CONNECTION_ID：连接 ID，例如 myconnection

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

  • SOURCE_FORMAT：外部数据源的格式。例如 PARQUET。

  • BUCKET_PATH：包含表数据的 Cloud Storage 存储桶的路径，格式为 gs://bucket_name/[folder_name/]file_pattern。

    您可以通过在 file_pattern 中指定一个星号 (*) 通配符从存储桶中选择多个文件。例如 gs://mybucket/file00*.parquet。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

    您可以通过提供多个路径来为 uris 选项指定多个存储桶。

    以下示例展示了有效的 uris 值：

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*，gs://bucket1/path1/*

    如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

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

  • DEFINITION_FILE：本地机器上表定义文件的路径。

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

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

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

  • DATASET：要在其中创建表的 BigQuery 数据集的名称，例如 mydataset

  • EXTERNAL_TABLE_NAME：要创建的表的名称，例如 mytable

  • SCHEMA：BigLake 表的架构

示例：

bq mkdef
    --connection_id=myconnection
    --metadata_cache_mode=CACHE_MODE
    --source_format=CSV 'gs://mybucket/*.csv' > mytable_def

bq mk
    --table
    --external_table_definition=mytable_def='gs://mybucket/*.csv'
    --max_staleness=0-0 0 4:0:0
    myproject:mydataset.mybiglaketable
    Region:STRING,Quarter:STRING,Total_sales:INTEGER

如需使用架构自动检测功能，请在 mkdef 命令中设置 --autodetect=true 标志并省略架构：

bq mkdef \
    --connection_id=myconnection \
    --metadata_cache_mode=CACHE_MODE \
    --source_format=CSV --autodetect=true \
    gs://mybucket/*.csv > mytable_def

bq mk \
    --table \
    --external_table_definition=mytable_def=gs://mybucket/*.csv \
    --max_staleness=0-0 0 4:0:0 \
    myproject:mydataset.myexternaltable

选项 2：内嵌表定义

您可以将表定义直接传递给 bq mk 命令，而不用创建表定义文件。使用 @connection 修饰器指定要在 --external_table_definition 参数末尾使用的连接。

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH@projects/PROJECT_ID/locations/REGION/connections/CONNECTION_ID \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

请替换以下内容：

  • SOURCE_FORMAT：外部数据源的格式

    例如 CSV。

  • BUCKET_PATH：包含表数据的 Cloud Storage 存储桶的路径，格式为 gs://bucket_name/[folder_name/]file_pattern。

    您可以通过在 file_pattern 中指定一个星号 (*) 通配符从存储桶中选择多个文件。例如 gs://mybucket/file00*.parquet。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

    您可以通过提供多个路径来为 uris 选项指定多个存储桶。

    以下示例展示了有效的 uris 值：

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*，gs://bucket1/path1/*

    如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

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

  • PROJECT_ID：要在其中创建表的项目的名称，例如 myproject

  • REGION：包含连接的区域，例如 us

  • CONNECTION_ID：连接 ID，例如 myconnection

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

  • DATASET_NAME：您要在其中创建 BigLake 表的数据集的名称

  • TABLE_NAME：BigLake 表的名称

  • SCHEMA：BigLake 表的架构

示例：

bq mk --table \
    --external_table_definition=@CSV=gs://mybucket/*.parquet@projects/myproject/locations/us/connections/myconnection \
    --max_staleness=0-0 0 4:0:0 \
    myproject:mydataset.myexternaltable \
    Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

调用 tables.insert 方法 API 方法，并在传入的 Table 资源中创建 ExternalDataConfiguration。

指定 schema 属性或将 autodetect 属性设置为 true，为受支持的数据源启用架构自动检测功能。

指定 connectionId 属性，以标识用于到 Cloud Storage 的连接。

Terraform

以下示例基于未分区数据创建 BigLake 表。

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

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

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

# This creates a connection in the US region named "my-connection".
# This connection is used to access the bucket.
resource "google_bigquery_connection" "default" {
  connection_id = "my-connection"
  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.project.id
  member  = "serviceAccount:${google_bigquery_connection.default.cloud_resource[0].service_account_id}"
}

# This makes the script wait for seven minutes before proceeding.
# This lets IAM permissions propagate.
resource "time_sleep" "default" {
  create_duration = "7m"

  depends_on = [google_project_iam_member.default]
}

# This defines a Google BigQuery dataset with
# default expiration times for partitions and tables, a
# description, a location, and a maximum time travel.
resource "google_bigquery_dataset" "default" {
  dataset_id                      = "my_dataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "My dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  # This defines a map of labels for the bucket resource,
  # including the labels "billing_group" and "pii".
  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}


# This creates a BigQuery Table with automatic metadata caching.
resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "my_table"
  schema = jsonencode([
    { "name" : "country", "type" : "STRING" },
    { "name" : "product", "type" : "STRING" },
    { "name" : "price", "type" : "INT64" }
  ])
  external_data_configuration {
    # This defines an external data configuration for the BigQuery table
    # that reads Parquet data from the publish directory of the default
    # Google Cloud Storage bucket.
    autodetect    = false
    source_format = "PARQUET"
    connection_id = google_bigquery_connection.default.name
    source_uris   = ["gs://${google_storage_bucket.default.name}/data/*"]
    # This enables automatic metadata refresh.
    metadata_cache_mode = "AUTOMATIC"
  }

  # This sets the maximum staleness of the metadata cache to 10 hours.
  max_staleness = "0-0 0 10:0:0"

  deletion_protection = false

  depends_on = [time_sleep.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 已创建或更新它们。

BigLake 支持架构自动检测功能。但是，如果您没有提供架构，并且服务账号在前面的步骤中未被授予访问权限，则如果您尝试自动检测架构，这些步骤将失败并显示访问遭拒消息。

对 Hive 分区数据创建 BigLake 表

您可以在 Cloud Storage 中为 Hive 分区数据创建 BigLake 表。创建外部分区表后，您便无法更改分区键。您需要重新创建表才能更改分区键。

如需根据 Cloud Storage 中的 Hive 分区数据创建 BigLake 表，请选择以下选项之一：

控制台

  1. 转到 BigQuery 页面。

    转到 BigQuery

  2. 在浏览器窗格中，展开您的项目，然后选择数据集。

  3. 点击 查看操作，然后点击创建表。此时将打开创建表窗格。

  4. 在来源部分，指定以下详细信息：

    1. 在基于以下数据创建表部分，选择 Google Cloud Storage。

    2. 使用通配符提供文件夹的路径。例如 my_bucket/my_files*。该文件夹必须与您要创建、附加或覆盖的表所属的数据集位于同一位置。

    3. 从文件格式列表中，选择文件类型。

    4. 选中源数据分区复选框，然后指定以下详细信息：

      1. 在选择来源 URI 前缀部分，输入 URI 前缀。例如 gs://my_bucket/my_files。
      2. 可选：如需对此表的所有查询使用分区过滤条件，请选中需要分区过滤条件复选框。要求使用分区过滤条件可以减少费用并提高性能。如需了解详情，请参阅要求对查询中的分区键使用谓词过滤条件。

      3. 在分区推理模式部分中，选择以下选项之一：

        • 自动推断类型：将分区架构检测模式设置为 AUTO。
        • 所有列都是字符串：用于将分区架构检测模式设置为 STRINGS。
        • 自主提供：用于将分区架构检测模式设置为 CUSTOM，并手动输入分区键的架构信息。如需了解详情，请参阅提供自定义分区键架构。

  5. 在目标部分，指定以下详细信息：

    1. 对于项目，选择要在其中创建表的项目。
    2. 在数据集部分，选择您要在其中创建表的数据集。
    3. 对于表，输入您要创建的表的名称。
    4. 在表类型部分，选择外部表。
    5. 选中使用 Cloud 资源连接来创建 BigLake 表复选框。
    6. 在连接 ID 部分，选择您之前创建的连接。

  6. 在 Schema 部分中，选择 Auto detect 选项，以启用架构自动检测功能。

  7. 如需忽略额外列值与架构不匹配的行，请展开高级选项部分，然后选择未知值。

  8. 点击创建表。

SQL

使用 CREATE EXTERNAL TABLE DDL 语句：

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

    转到 BigQuery

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

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
(
  PARTITION_COLUMN PARTITION_COLUMN_TYPE,
)
WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
OPTIONS (
  hive_partition_uri_prefix = "HIVE_PARTITION_URI_PREFIX",
  uris=['FILE_PATH'],
  max_staleness = STALENESS_INTERVAL,
  metadata_cache_mode = 'CACHE_MODE',
  format ="TABLE_FORMAT"
);

    替换以下内容：

    • PROJECT_ID：要在其中创建表的项目的名称，例如 myproject
    • DATASET：要在其中创建表的 BigQuery 数据集的名称，例如 mydataset
    • EXTERNAL_TABLE_NAME：要创建的表的名称，例如 mytable
    • PARTITION_COLUMN：分区列的名称
    • PARTITION_COLUMN_TYPE：分区列的类型
    • REGION：包含连接的区域，例如 us
    • CONNECTION_ID：连接 ID，例如 myconnection

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

    • HIVE_PARTITION_URI_PREFIX：hive 分区 URI 前缀，例如 gs://mybucket/
    • FILE_PATH：要创建的外部表的数据源路径，例如 gs://mybucket/*.parquet
    • STALENESS_INTERVAL：指定对 BigLake 表执行的操作是否使用了缓存的元数据，以及操作使用的缓存元数据的新鲜度。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

      如需停用元数据缓存，请指定 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。

    • TABLE_FORMAT：要创建的表的格式，例如 PARQUET

  3. 点击 运行。

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

示例

以下示例将基于分区数据创建 BigLake 表，其中：

  • 架构是自动检测的。
  • 表的元数据缓存过时间隔为 1 天。
  • 元数据缓存会自动刷新。
CREATE EXTERNAL TABLE `my_dataset.my_table`
WITH PARTITION COLUMNS
(
  sku STRING,
)
WITH CONNECTION `us.my-connection`
OPTIONS(
  hive_partition_uri_prefix = "gs://mybucket/products",
  uris = ['gs://mybucket/products/*'],
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

以下示例将基于分区数据创建 BigLake 表，其中：

  • 架构已指定。
  • 表的元数据缓存过时间隔为 8 小时。
  • 您必须手动刷新元数据缓存。
CREATE EXTERNAL TABLE `my_dataset.my_table`
(
  ProductId INTEGER,
  ProductName STRING,
  ProductType STRING
)
WITH PARTITION COLUMNS
(
  sku STRING,
)
WITH CONNECTION `us.my-connection`
OPTIONS(
  hive_partition_uri_prefix = "gs://mybucket/products",
  uris = ['gs://mybucket/products/*'],
  max_staleness = INTERVAL 8 HOUR,
  metadata_cache_mode = 'MANUAL'
);

bq

首先，使用 bq mkdef 命令创建表定义文件：

bq mkdef \
--source_format=SOURCE_FORMAT \
--connection_id=REGION.CONNECTION_ID \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
--metadata_cache_mode=CACHE_MODE \
 GCS_URIS > DEFINITION_FILE

替换以下内容：

  • SOURCE_FORMAT：外部数据源的格式。例如 CSV。
  • REGION：包含连接的区域，例如 us。

  • CONNECTION_ID：连接 ID，例如 myconnection。

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

  • PARTITIONING_MODE：Hive 分区模式。请使用以下某个值：

    • AUTO：自动检测键名称和类型。
    • STRINGS：自动将键名称转换为字符串。
    • CUSTOM：对来源 URI 前缀中的键架构进行编码。

  • GCS_URI_SHARED_PREFIX：来源 URI 前缀。

  • BOOLEAN：指定查询时是否需要谓词过滤条件。此标志是可选标志。默认值为 false。

  • CACHE_MODE：指定元数据缓存是自动刷新还是手动刷新。仅当您还计划在后续 bq mk 命令中使用 --max_staleness 标志来启用元数据缓存时，才需要添加此标志。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

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

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

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

  • GCS_URIS：Cloud Storage 文件夹的路径（使用通配符格式）。

  • DEFINITION_FILE：本地机器上表定义文件的路径。

如果 PARTITIONING_MODE 为 CUSTOM，请使用以下格式将分区键架构包括在来源 URI 前缀中：

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

创建表定义文件后，请使用 bq mk 命令来创建 BigLake 表：

bq mk --external_table_definition=DEFINITION_FILE \
--max_staleness=STALENESS_INTERVAL \
DATASET_NAME.TABLE_NAME \
SCHEMA

替换以下内容：

  • DEFINITION_FILE：表定义文件的路径。

  • STALENESS_INTERVAL：指定对 BigLake 表执行的操作是否使用了缓存的元数据，以及操作使用的缓存元数据的新鲜度。如需添加此标志，您还必须在上述 bq mkdef 命令中指定 --metadata_cache_mode 标志的值。如需详细了解元数据缓存注意事项，请参阅启用元数据缓存以提高性能。

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

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

  • DATASET_NAME：包含该表的数据集的名称。

  • TABLE_NAME：您要创建的表的名称。

  • SCHEMA：指定 JSON 架构文件的路径，或者以 field:data_type,field:data_type,... 格式指定架构。如需使用架构自动检测功能，请省略此参数。

示例

以下示例使用 AUTO Hive 分区模式，并将元数据缓存设置为 12 小时过时间隔并自动刷新：

bq mkdef --source_format=CSV \
  --connection_id=us.my-connection \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  --metadata_cache_mode=AUTOMATIC \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  --max_staleness=0-0 0 12:0:0 \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

以下示例使用 STRING Hive 分区模式：

bq mkdef --source_format=CSV \
  --connection_id=us.my-connection \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

以下示例使用 CUSTOM Hive 分区模式：

bq mkdef --source_format=CSV \
  --connection_id=us.my-connection \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

如需使用 BigQuery API 设置 Hive 分区，请在创建表定义文件时在 ExternalDataConfiguration 对象中添加 hivePartitioningOptions 对象。如需创建 BigLake 表，您还必须指定 connectionId 字段的值。

如果将 hivePartitioningOptions.mode 字段设置为 CUSTOM，则必须在 hivePartitioningOptions.sourceUriPrefix 字段中对分区键架构进行如下所示的编码：gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

如需在查询时强制使用谓词过滤条件，请将 hivePartitioningOptions.requirePartitionFilter 字段设置为 true。

Terraform

以下示例基于分区数据创建 BigLake 表。

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


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

resource "google_storage_bucket_object" "default" {
  # This creates a fake message to create partition locations on the table.
  # Otherwise, the table deployment fails.
  name    = "publish/dt=2000-01-01/hr=00/min=00/fake_message.json"
  content = "{\"column1\": \"XXX\"}"
  bucket  = google_storage_bucket.default.name
}

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

# This creates a connection in the US region named "my-connection".
# This connection is used to access the bucket.
resource "google_bigquery_connection" "default" {
  connection_id = "my-connection"
  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.id
  member  = "serviceAccount:${google_bigquery_connection.default.cloud_resource[0].service_account_id}"
}

# This makes the script wait for seven minutes before proceeding. This lets IAM
# permissions propagate.
resource "time_sleep" "default" {
  create_duration = "7m"

  depends_on = [google_project_iam_member.default]
}

# This defines a Google BigQuery dataset with default expiration times for
# partitions and tables, a description, a location, and a maximum time travel.
resource "google_bigquery_dataset" "default" {
  dataset_id                      = "my_dataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "My dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  # This defines a map of labels for the bucket resource,
  # including the labels "billing_group" and "pii".
  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

# This creates a BigQuery table with partitioning and automatic metadata
# caching.
resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "my_table"
  schema     = jsonencode([{ "name" : "column1", "type" : "STRING", "mode" : "NULLABLE" }])
  external_data_configuration {
    # This defines an external data configuration for the BigQuery table
    # that reads Parquet data from the publish directory of the default
    # Google Cloud Storage bucket.
    autodetect    = false
    source_format = "PARQUET"
    connection_id = google_bigquery_connection.default.name
    source_uris   = ["gs://${google_storage_bucket.default.name}/publish/*"]
    # This configures Hive partitioning for the BigQuery table,
    # partitioning the data by date and time.
    hive_partitioning_options {
      mode                     = "CUSTOM"
      source_uri_prefix        = "gs://${google_storage_bucket.default.name}/publish/{dt:STRING}/{hr:STRING}/{min:STRING}"
      require_partition_filter = false
    }
    # This enables automatic metadata refresh.
    metadata_cache_mode = "AUTOMATIC"
  }


  # This sets the maximum staleness of the metadata cache to 10 hours.
  max_staleness = "0-0 0 10:0:0"

  deletion_protection = false

  depends_on = [
    time_sleep.default,
    google_storage_bucket_object.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 已创建或更新它们。

设置访问权限控制政策

您可以使用多种方法来控制对 BigLake 表的访问权限：

例如，假设您要限制数据集 mydataset 中表 mytable 的行访问权限：

+---------+---------+-------+
| country | product | price |
+---------+---------+-------+
| US      | phone   |   100 |
| JP      | tablet  |   300 |
| UK      | laptop  |   200 |
+---------+---------+-------+

您可以为 Kim (kim@example.com) 创建行级过滤条件，限制其仅可访问 country 为 US 的行。

CREATE ROW ACCESS POLICY only_us_filter
ON mydataset.mytable
GRANT TO ('user:kim@example.com')
FILTER USING (country = 'US');

之后，Kim 运行以下查询：

SELECT * FROM projectid.mydataset.mytable;

输出将仅显示 country 为 US 的行：

+---------+---------+-------+
| country | product | price |
+---------+---------+-------+
| US      | phone   |   100 |
+---------+---------+-------+

查询 BigLake 表

如需了解详情，请参阅查询 BigLake 表中的 Cloud Storage 数据。

更新 BigLake 表

如有必要，您可以更新 BigLake 表，例如更改其元数据缓存。如需获取表的详细信息，例如源格式和源 URI，请参阅获取表信息。

您还可以按照相同的过程，通过将外部表关联到连接将基于 Cloud Storage 的外部表升级到 BigLake 表。如需了解详情，请参阅将外部表升级为 BigLake 表。

如需更新 BigLake 表，请选择以下选项之一：

SQL

使用 CREATE OR REPLACE EXTERNAL TABLE DDL 语句更新表：

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

    转到 BigQuery

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

    CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION `REGION.CONNECTION_ID`
  OPTIONS(
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

    请替换以下内容：

    • PROJECT_ID：包含表的项目的名称
    • DATASET：包含表的数据集的名称
    • EXTERNAL_TABLE_NAME：表的名称
    • REGION：包含连接的区域
    • CONNECTION_ID：要使用的连接的名称
    • TABLE_FORMAT：表使用的格式

      更新表时，您无法对此进行更改。

    • BUCKET_PATH：包含外部表数据的 Cloud Storage 存储桶的路径，格式为 ['gs://bucket_name/[folder_name/]file_name']。

      您可以通过在路径中指定一个星号 (*) 通配符从存储桶中选择多个文件。例如 ['gs://mybucket/file_name*']。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

      您可以通过提供多个路径来为 uris 选项指定多个存储桶。

      以下示例展示了有效的 uris 值：

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

      如需详细了解如何在 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. 点击 运行。

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

bq

使用 bq mkdef 和 bq update 命令更新表：

  1. 生成外部表定义，用于描述要更改的表方面：

    bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \
--source_format=TABLE_FORMAT \
--metadata_cache_mode=CACHE_MODE \
"BUCKET_PATH" > /tmp/DEFINITION_FILE

    请替换以下内容：

    • PROJECT_ID：包含连接的项目的名称
    • REGION：包含连接的区域
    • CONNECTION_ID：要使用的连接的名称
    • TABLE_FORMAT：表使用的格式更新表时，您无法对此进行更改。

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

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

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

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

    • BUCKET_PATH：包含外部表数据的 Cloud Storage 存储桶的路径，格式为 gs://bucket_name/[folder_name/]file_name。

      您可以通过在路径中指定一个星号 (*) 通配符来限制从存储桶中选择的文件。例如 gs://mybucket/file_name*。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

      您可以通过提供多个路径来为 uris 选项指定多个存储桶。

      以下示例展示了有效的 uris 值：

      • gs://bucket/path1/myfile.csv
      • gs://bucket/path1/*.csv
      • gs://bucket/path1/*,gs://bucket/path2/file00*

      如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

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

    • DEFINITION_FILE：您要创建的表定义文件的名称。

  2. 使用新的外部表定义更新表：

    bq update --max_staleness=STALENESS_INTERVAL \
--external_table_definition=/tmp/DEFINITION_FILE \
PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME

    替换以下内容：

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

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

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

    • DEFINITION_FILE：您创建或更新的表定义文件的名称。

    • PROJECT_ID：包含表的项目的名称

    • DATASET：包含表的数据集的名称

    • EXTERNAL_TABLE_NAME：表格的名称

示例

以下示例更新 mytable 以使用缓存的元数据（只要它在过去 4.5 小时内已刷新），并且自动刷新缓存的元数据：

bq update --project_id=myproject --max_staleness='0-0 0 4:30:0' \
--external_table_definition=enable_metadata.json mydataset.mytable

其中 enable_metadata.json 包含以下内容：{ "metadataCacheMode": "AUTOMATIC" }

审核日志记录

如需了解 BigQuery 中的日志记录，请参阅 BigQuery 监控简介。如需详细了解 Google Cloud 中的日志记录，请参阅 Cloud Logging。

后续步骤