查询外部分区数据

BigQuery 支持使用默认 Hive 分区布局查询存储在 Cloud Storage 中的 Avro、Parquet、ORC、JSON 和 CSV 格式的外部分区数据。您可以通过设置表定义文件中的相应选项来启用 Hive 分区支持。 您可以使用 Cloud Console、bq 命令行工具和 BigQuery API 创建和修改表。

如需了解如何查询代管式分区表,请参阅分区表简介

限制

  • 如需构建 Hive 分区支持,所有 URI 都应使用共同的源 URI 前缀,且该前缀的后面应紧跟分区编码,如下所示:gs://BUCKET/PATH_TO_TABLE/
  • Hive 分区表的目录结构应以相同顺序显示相同的分区键,且每个表的分区键数不得超过 10 个。
  • 数据必须遵循默认 Hive 分区布局
  • 底层文件中的 Hive 分区键和列不能重叠。
  • 必须遵循与查询存储在 Cloud Storage 中的外部数据源相关的所有限制
  • 仅支持使用标准 SQL

支持的数据布局

数据必须遵循默认 Hive 分区布局。例如,以下文件采用默认布局,其中键值对以目录的方式配置,使用 = 符号作为分隔符,且分区键的顺序始终相同:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar

此示例中使用的共同源 URI 前缀为 gs://myBucket/myTable

不支持的数据布局

如果分区键名未在目录路径中进行编码,则分区架构检测将失败。例如,请考虑以下路径,该路径未对分区键的名称进行编码:

gs://myBucket/myTable/2019-10-31/en/foo

架构顺序不一致的文件也会导致检测失败。例如,请考虑以下两个文件,它们采用的分区键编码是反向的:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/lang=fr/dt=2018-10-31/bar

对于这些文件,分区架构检测会失败。

分区架构检测模式

从 Cloud Storage 查询数据时,Hive 分区键会以普通列的形式显示。BigQuery 支持三种 Hive 分区架构检测模式:

  • AUTO:系统会自动检测键的名称和类型。可检测到以下类型的键:STRING、INTEGER、DATE、TIMESTAMP。
  • STRINGS:键名会自动转换为 STRING 类型。
  • CUSTOM:分区键架构按照源 URI 前缀中指定的方式进行编码。

提供自定义分区键架构

如需使用 CUSTOM 架构,您必须在源 URI 前缀字段中指定架构。使用 CUSTOM 架构,您可以指定每个分区键的类型。值必须有效地解析为指定类型,否则查询将会失败。

例如,如果您将 source_uri_prefix 标志设置为 gs://myBucket/myTable/{dt:DATE}/{val:STRING},则 BigQuery 会将 val 视为 STRING,将 dt 视为 DATE,并使用 gs://myBucket/myTable 作为匹配文件的源 URI 前缀。

删减分区

BigQuery 会尽可能使用分区键中的查询谓词来剪除分区。这可让 BigQuery 避免读取不必要的文件,从而有助于提高性能。

要求对查询中的分区键使用谓词过滤条件

创建外部分区表时,您可以通过启用 HivePartitioningOptions 下的 requirePartitionFilter 选项,要求对分区键使用谓词过滤条件。

启用此选项时,如果试图在不指定 WHERE 子句的情况下查询外部分区表,则会产生以下错误:Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination

为 Hive 分区数据创建外部表

您可以通过以下方式为 Hive 分区数据创建外部表:

  • 使用 Cloud Console。
  • 使用 bq 命令行工具。
  • 使用客户端库。

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 探索器面板中,展开您的项目并选择数据集。

  3. 展开 操作选项,然后点击打开

  4. 在详情面板中,点击创建表

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

    1. 基于以下数据源创建表下拉列表中,选择 Cloud Storage
    2. 从 Cloud Storage 存储分区中选择文件 (Select file from Cloud Storage bucket) 字段中,使用通配符格式输入 Cloud Storage 文件夹的路径。例如 my_bucket/my_files*
    3. 文件格式下拉列表中,选择文件类型。
    4. 选中源数据分区复选框。
    5. 选择源 URI 前缀字段中,输入 Cloud Storage URI 前缀。例如 gs://my_bucket/my_files
    6. 选择分区推断模式。如果您选择自主提供,则输入分区键的架构信息。
    7. 可选:如需要求针对相应表的所有查询都必须使用谓词过滤条件,请勾选需要分区过滤条件。如需了解详情,请参阅要求对查询中的分区键使用谓词过滤条件
  6. 创建表页面的目标部分,执行以下操作:

    1. 选择项目名称和数据集名称。
    2. 表类型下拉列表中,选择外部表
    3. 表名称字段中,输入外部表的名称。
  7. 创建表页面的架构部分中,输入架构信息。BigQuery 支持某些格式的架构自动检测。如需了解详情,请参阅使用架构自动检测功能

  8. 点击创建表

bq

  • 如需设置自动分区键检测,请将 --hive_partitioning_mode 标志设置为 AUTO。例如:
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • 如需设置字符串类型的分区键检测,请将 --hive_partitioning_mode 标志设置为 STRINGS。例如:
bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • 如需使用 --source_uri_prefix 标志指定自定义分区键架构,请将 --hive_partitioning_mode 标志设置为 CUSTOM。例如:
bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/CUSTOM_SCHEMA_ENCODING \
GCS_URIS FILE_SCHEMA > TABLE_DEF_FILE

对于 --hive_partitioning_source_uri_prefix 标志,请在 <var>GCS_URI_SHARED_PREFIX</var> 部分之后立即指定参数的 <var>CUSOM_SCHEMA_ENCODING</var> 部分,如下所示:

GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

例如:

--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING}

  • 如需在查询时要求使用谓词过滤条件,请在 bq mkdef 命令中添加 --require_hive_partition_filter=True;例如:
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=True \
GCS_URIS > TABLE_DEF_FILE

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 参考文档

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());
    }
  }
}