创建 Cloud Storage 外部表

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

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

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

  • 标准
  • Nearline
  • Coldline
  • 归档

如需查询 Cloud Storage 外部表，您必须拥有外部表和 Cloud Storage 文件的权限。我们建议尽可能使用 BigLake 表。BigLake 表提供访问权限委托功能，因此您只需拥有 BigLake 表的权限即可查询 Cloud Storage 数据。

查询存储在 Cloud Storage 中的数据时，请务必考虑您的数据集和 Cloud Storage 存储桶的位置。

准备工作

授予为用户提供执行本文档中的每个任务所需权限的 Identity and Access Management (IAM) 角色。 执行任务所需的权限（如果有）列出在任务的“所需权限”部分中。

所需的角色

如需创建外部表，您需要拥有 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)

您还需要以下权限才能访问包含您的数据的 Cloud Storage 存储桶：

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list（如果您使用的是 URI 通配符，则为必需）

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

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

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

Compute Engine 实例的访问权限范围

如果对于 Compute Engine 实例，您需要查询链接到 Cloud Storage 源的外部表，则该实例必须至少具有 Cloud Storage 只读访问权限范围 (https://www.googleapis.com/auth/devstorage.read_only)。

这些范围用于控制 Compute Engine 实例对 Google Cloud 产品（包括 Cloud Storage）的访问权限。在实例上运行的应用使用关联到实例的服务账号来调用 Google Cloud API。

如果您设置某个 Compute Engine 实例作为默认 Compute Engine 服务账号运行，则该实例默认会获得一些默认范围，包括 https://www.googleapis.com/auth/devstorage.read_only 范围。

如果您使用自定义服务账号设置实例，请务必向该实例明确授予 https://www.googleapis.com/auth/devstorage.read_only 范围。

如需了解将范围应用于 Compute Engine 实例的信息，请参阅更改实例的服务账号和访问范围。如需详细了解 Compute Engine 服务账号，请参阅服务账号。

基于未分区数据创建外部表

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

从下列选项中选择一项：

控制台

  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. 在表类型部分，选择外部表。

  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`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]]
    );

    请替换以下内容：

    • PROJECT_ID：要在其中创建表的项目的名称，例如 myproject
    • DATASET：要在其中创建表的 BigQuery 数据集的名称，例如 mydataset
    • EXTERNAL_TABLE_NAME：要创建的表的名称，例如 mytable
    • 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 资源路径。

  3. 点击 运行。

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

示例

以下示例使用架构自动检测功能创建名为 sales 的外部表，该表链接到存储在 Cloud Storage 中的 CSV 文件：

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

下一个示例明确指定架构，并跳过 CSV 文件中的第一行：

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

如需创建外部表，请使用带有 --external_table_definition 标志的 bq mk 命令。此标志包含表定义文件的路径或内嵌表定义。

选项 1：表定义文件

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

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  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 资源路径。

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

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

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

  • SCHEMA：指定 JSON 架构文件的路径，或者以 field:data_type,field:data_type,... 格式指定架构。

示例：

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

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

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

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

选项 2：内嵌表定义

您可以将表定义直接传递给 bq mk 命令，而不用创建表定义文件：

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH \
  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 资源路径。

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

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

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

例如：

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

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

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

Java

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

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

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

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

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

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

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

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

基于分区数据创建外部表

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

如需为 Hive 分区数据创建外部表，请选择以下选项之一：

控制台

  1. 在 Google Cloud 控制台中，找到 BigQuery。

    转到 BigQuery

  2. 在浏览器窗格中，展开您的项目，然后选择数据集。
  3. 点击 查看操作，然后点击创建表。此时将打开创建表窗格。
  4. 在来源部分，指定以下详细信息：
    1. 在基于以下数据创建表部分，选择 Google Cloud Storage。
    2. 在从 Cloud Storage 存储桶中选择文件部分，使用通配符输入 Cloud Storage 文件夹的路径。例如 my_bucket/my_files*。Cloud Storage 存储桶必须与您要创建、附加或覆盖的表所属的数据集位于同一位置。
    3. 从文件格式列表中，选择文件类型。
    4. 选中源数据分区复选框，然后在选择源 URI 前缀部分，输入 Cloud Storage URI 前缀。例如，gs://my_bucket/my_files。
    5. 在分区推理模式部分中，选择以下选项之一：
      • 自动推断类型：用于将分区架构检测模式设置为 AUTO。
      • 所有列都是字符串：用于将分区架构检测模式设置为 STRINGS。
      • 自主提供：用于将分区架构检测模式设置为 CUSTOM，并手动输入分区键的架构信息。如需了解详情，请参阅提供自定义分区键架构。
    6. 可选：如需要求对此表的所有查询使用分区过滤条件，请选中要求使用分区过滤条件复选框。要求使用分区过滤条件可以减少费用并提高性能。如需了解详情，请参阅要求对查询中的分区键使用谓词过滤条件。
  5. 在目标部分，指定以下详细信息：
    1. 对于项目，选择要在其中创建表的项目。
    2. 对于数据集，选择您要在其中创建表的数据集。
    3. 在表部分中，输入您要创建的表的名称。
    4. 对于表类型，选择外部表。
  6. 在架构部分，输入架构定义。
  7. 如需启用对架构的自动检测，请选择自动检测。
  8. 如需忽略额外列值与架构不匹配的行，请展开高级选项部分，然后选择未知值。
  9. 点击创建表。

SQL

使用 CREATE EXTERNAL TABLE DDL 语句.

以下示例使用 Hive 分区键的自动检测功能：

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

请替换以下内容：

  • SOURCE_FORMAT：外部数据源的格式，例如 PARQUET
  • GCS_URIS：Cloud Storage 文件夹的路径（使用通配符格式）
  • GCS_URI_SHARED_PREFIX：不包含通配符的来源 URI 前缀
  • BOOLEAN：查询时是否要求使用谓词过滤条件。此标志是可选标志。默认值为 false。

以下示例使用自定义 Hive 分区键和类型，方法是在 WITH PARTITION COLUMNS 子句中列出它们：

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

请替换以下内容：

  • PARTITION_COLUMN_LIST：以 Cloud Storage 文件夹路径中的相同顺序排列的列的列表，格式为：
KEY1 TYPE1, KEY2 TYPE2

下面的示例将创建一个外部分区表。该示例使用架构自动检测功能来检测文件架构和 hive 分区布局。如果外部路径是 gs://bucket/path/field_1=first/field_2=1/data.parquet，则分区列会被检测为 field_1 (STRING) 和 field_2 (INT64)。

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

下面的示例将通过明确指定分区列来创建外部分区表。此示例假定外部文件路径的格式为 gs://bucket/path/field_1=first/field_2=1/data.parquet。

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

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

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

请替换以下内容：

  • SOURCE_FORMAT：外部数据源的格式。例如 CSV。
  • PARTITIONING_MODE：Hive 分区模式。请使用下列其中一个值：
    • AUTO：自动检测键名称和类型。
    • STRINGS：自动将键名称转换为字符串。
    • CUSTOM：对来源 URI 前缀中的键架构进行编码。
  • GCS_URI_SHARED_PREFIX：源 URI 前缀。
  • BOOLEAN：指定查询时是否需要谓词过滤条件。此标志是可选标志。默认值为 false。
  • 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 命令来创建外部表：

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

请替换以下内容：

  • DEFINITION_FILE：表定义文件的路径。
  • DATASET_NAME：包含该表的数据集的名称。
  • TABLE_NAME：您要创建的表的名称。
  • SCHEMA：指定 JSON 架构文件的路径，或者以 field:data_type,field:data_type,... 格式指定架构。如需使用架构自动检测功能，请省略此参数。

示例

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

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --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

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

bq mkdef --source_format=CSV \
  --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 \
  --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 对象。

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

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

Java

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

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

查询外部表

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

将外部表升级到 BigLake

您可以将外部表关联到连接，根据 Cloud Storage 将表升级为 BigLake 表。如果您要将元数据缓存与 BigLake 表搭配使用，可以同时对此指定设置。如需获取表的详细信息，例如源格式和源 URI，请参阅获取表信息。

如需将外部表更新为 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：表格的名称

Cloud Storage 资源路径

根据 Cloud Storage 数据源创建外部表时，您必须提供数据的路径。

Cloud Storage 资源路径包含存储桶名称和对象（文件名）。例如，如果 Cloud Storage 存储桶的名称为 mybucket，且数据文件的名称为 myfile.csv，则资源路径为 gs://mybucket/myfile.csv。

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

如需检索 Cloud Storage 资源路径，请执行以下操作：

  1. 打开 Cloud Storage 控制台

    Cloud Storage 控制台

  2. 浏览到包含源数据的对象（文件）所在的位置。

  3. 点击对象的名称。

    对象详情页面随即会打开。

  4. 复制 gsutil URI 字段中提供的值，该值以 gs:// 开头。

Cloud Storage URI 的通配符支持

如果您的数据分为多个文件，则可以使用星号 (*) 通配符选择多个文件。使用星号通配符必须遵循以下规则：

  • 星号可以出现在对象名称内或对象名称末尾。
  • 不支持使用多个星号。例如，路径 gs://mybucket/fed-*/temp/*.csv 无效。
  • 不支持在存储桶名称中使用星号。

示例：

  • 以下示例展示了如何选择以前缀 gs://mybucket/fed-samples/fed-sample 开头的所有文件夹中的所有文件：

    gs://mybucket/fed-samples/fed-sample*

  • 以下示例展示了如何仅选择名为 fed-samples 的文件夹中和 fed-samples 的任何子文件夹中扩展名为 .csv 的文件：

    gs://mybucket/fed-samples/*.csv

  • 以下示例展示了如何选择文件夹 fed-samples 中命名格式为 fed-sample*.csv 的文件。此示例不会选择 fed-samples 子文件夹中的文件。

    gs://mybucket/fed-samples/fed-sample*.csv

使用 bq 命令行工具时，您可能需要在某些平台上对星号进行转义。

创建与 Datastore 或 Firestore 导出关联的外部表时，不能使用星号通配符。

限制

如需了解适用于外部表的限制，请参阅外部表限制。

后续步骤