为外部数据源创建表定义文件

本页介绍了如何针对外部数据源创建表定义文件。外部数据源是可以直接查询的数据源，即使数据未存储在 BigQuery 中也是如此。

表定义文件包含外部表的架构定义和元数据，例如表的数据格式及相关属性。创建表定义文件时，您可以使用架构自动检测功能针对外部数据源定义架构。您可以内嵌方式提供架构，也可以提供包含架构定义的 JSON 文件。

表定义文件与 bq 命令行工具搭配使用。使用 REST API 时，您也可以使用表定义文件中的属性来创建 ExternalDataConfiguration。使用 Google Cloud 控制台创建外部表时，不可使用表定义文件。

您可以创建表定义文件来描述以下外部数据源的永久或临时外部表：

  • Cloud Storage

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

  • Google 云端硬盘

    • 英文逗号分隔值 (CSV)
    • 以换行符分隔的 JSON
    • Avro 文件
    • Google 表格

  • Bigtable

准备工作

如需创建表定义文件，您需要知道数据源的 URI：

为 CSV、JSON 或 Google 表格文件创建定义文件

使用以下方法之一在 Cloud Storage 或云端硬盘中为 CSV、JSON 或 Google 表格文件创建表定义文件：

使用 autodetect 标志

如果您指定了 CSV、JSON 或 Google 表格文件，但未加入内嵌架构说明或架构文件，可以在表定义文件中使用 --autodetect 标志将 "autodetect" 选项设置为 true。如果启用了自动检测功能，BigQuery 将尽力尝试自动推断出架构。如需了解详情，请参阅对外部数据源使用架构自动检测功能。

将自动检测功能与 Cloud Storage 数据源搭配使用

为 Cloud Storage 数据源创建表定义文件：

  1. 使用带有 --autodetect 标志的 bq mkdef 命令创建表定义文件。mkdef 命令会生成 JSON 格式的表定义文件。以下示例会创建表定义并将输出写入 /tmp/file_name 文件。

    bq mkdef \
  --autodetect \
  --source_format=SOURCE_FORMAT \
  "URI" > /tmp/FILE_NAME

    请替换以下内容：

    • SOURCE_FORMAT：您的文件格式
    • FILE_NAME：表定义文件的名称

    • URI：Cloud Storage URI

      例如 gs://mybucket/myfile。

  2. （可选）在文本编辑器中打开表定义文件。例如，nano /tmp/file_name 命令会使用 nano 打开此文件。对于 CSV 外部数据源，文件内容应如下所示。请注意，"autodetect" 设为 true。

    {
"autodetect": true,
"csvOptions": {
  "allowJaggedRows": false,
  "allowQuotedNewlines": false,
  "encoding": "UTF-8",
  "fieldDelimiter": ",",
  "quote": "\"",
  "skipLeadingRows": 0
},
"sourceFormat": "CSV",
"sourceUris": [
  "URI"
]
}

  3. （可选）手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等常规设置。我们没有提供特定于 JSON 源文件的配置设置，但提供了适用于 CSV 和 Google 表格文件的设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

将自动检测功能与云端硬盘数据源搭配使用

为云端硬盘数据源创建表定义文件：

  1. 使用带有 --autodetect 标志的 bq mkdef 命令创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。以下示例会创建表定义并将输出写入 /tmp/file_name 文件。

    bq mkdef \
   --autodetect \
   --source_format=SOURCE_FORMAT \
   "URI" > /tmp/FILE_NAME

    请替换以下内容：

    • SOURCE_FORMAT：您的文件格式
    • FILE_NAME：表定义文件的名称

    • URI：云端硬盘 URI

      例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

  2. 在文本编辑器中打开表定义文件。例如，nano /tmp/file_name 命令会使用 nano 打开此文件。外部数据源为 Google 表格时，文件内容应如下所示。请注意，"autodetect" 设为 true。

    {
"autodetect": true,
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. （可选）手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等常规设置。我们没有提供特定于 JSON 源文件的配置设置，但提供了适用于 CSV 和 Google 表格文件的设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

  4. 如需指定 Google 表格文件中的特定工作表或单元格范围，请将 range 属性添加到表定义文件中。如需查询特定工作表，请指定工作表名称。如需查询某一单元格范围，请按 sheet_name!top_left_cell_id:bottom_right_cell_id 格式指定此范围，例如 "Sheet1!A1:B20"。如果您未指定 range 参数，则系统会使用该文件中的第一个工作表。

使用内嵌架构

如果您不想使用架构自动检测功能，可以通过提供内嵌架构定义来创建表定义文件。如需提供内嵌架构定义，请在命令行中按以下格式列出字段和数据类型：FIELD:DATA_TYPE,FIELD:DATA_TYPE。

将内嵌架构与 Cloud Storage 或云端硬盘数据源搭配使用

使用内嵌架构定义创建 Cloud Storage 或云端硬盘数据源的表定义：

  1. 使用带有 --noautodetect 标志的 bq mkdef 命令创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。以下示例会创建表定义并将输出写入 /tmp/file_name 文件。

    bq mkdef \
  --noautodetect \
  --source_format=SOURCE_FORMAT \
  "URI" \
  FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME

    替换以下内容

    • SOURCE_FORMAT：源文件格式

    • URI：Cloud Storage URI 或云端硬盘 URI

      例如，gs://mybucket/myfile 表示 Cloud Storage，https://drive.google.com/open?id=123ABCD123AbcD123Abcd 表示云端硬盘。

    • FIELD:DATA_TYPE,FIELD:DATA_TYPE：架构定义

      例如 Name:STRING,Address:STRING, ...。

    • FILE_NAME：表定义文件的名称

  2. （可选）在文本编辑器中打开表定义文件。例如，nano /tmp/file_name 命令会使用 nano 打开此文件。文件内容应如下所示。请注意，"autodetect" 未启用，并且架构信息已写入表定义文件。

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. （可选）手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等常规设置。我们没有提供特定于 JSON 源文件的配置设置，但提供了适用于 CSV 和 Google 表格文件的设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

使用 JSON 架构文件

如果您不想使用自动检测功能，也不想提供内嵌架构定义，则可以创建 JSON 架构文件，然后在创建表定义文件时引用该文件。在本地机器上手动创建 JSON 架构文件。系统不支持引用 Cloud Storage 或云端硬盘中存储的 JSON 架构文件。

将架构文件与 Cloud Storage 数据源搭配使用

使用 JSON 架构文件为 Cloud Storage 数据源创建表定义：

  1. 使用带有 --noautodetect 标志的 bq mkdef 命令创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。以下示例会创建表定义并将输出写入 /tmp/file_name 文件。

    bq mkdef \
   --noautodetect \
   --source_format=SOURCE_FORMAT \
   "URI" \
  PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    请替换以下内容：

    • SOURCE_FORMAT：您的文件格式
    • FILE_NAME：表定义文件的名称

    • URI：Cloud Storage URI

      例如 gs://mybucket/myfile。

    • PATH_TO_SCHEMA_FILE：本地机器上 JSON 架构文件的位置

  2. （可选）在文本编辑器中打开表定义文件。例如，nano /tmp/file_name 命令会使用 nano 打开此
    文件。文件内容应如下所示。请注意，"autodetect" 未启用，并且架构信息已写入表定义文件。

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. （可选）手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等常规设置。我们没有提供特定于 JSON 源文件的配置设置，但提供了适用于 CSV 和 Google 表格文件的设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

将架构文件与云端硬盘数据源搭配使用

使用 JSON 架构文件为云端硬盘数据源创建表定义：

  1. 使用带有 --noautodetect 标志的 bq mkdef 命令创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。以下示例会创建表定义并将输出写入 /tmp/file_name 文件。

    bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "URI" \
   PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    请替换以下内容：

    • SOURCE_FORMAT：源文件格式

    • URI：云端硬盘 URI

      例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

    • PATH_TO_SCHEMA_FILE：本地机器上 JSON 架构文件的位置

    • FILE_NAME：表定义文件的名称

  2. 在文本编辑器中打开表定义文件。例如，nano /tmp/file_name 命令会使用 nano 打开此文件。文件内容应如下所示。请注意，"autodetect" 未启用，并且架构信息已写入表定义文件。

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. （可选）手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等常规设置。我们没有提供特定于 JSON 源文件的配置设置，但提供了适用于 CSV 和 Google 表格文件的设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

  4. 如需指定 Google 表格文件中的特定工作表或单元格范围，请将 range 属性添加到表定义文件中。如需查询特定工作表，请指定工作表名称。如需查询某一单元格范围，请按 sheet_name!top_left_cell_id:bottom_right_cell_id 格式指定此范围，例如 "Sheet1!A1:B20"。如果您未指定 range 参数，则系统会使用该文件中的第一个工作表。

为自描述格式创建定义文件

Avro、Parquet 和 ORC 为自描述格式。这些格式的数据文件包含自己的架构信息。如果您使用这些格式之一作为外部数据源，BigQuery 将使用源数据自动检索架构。创建表定义时，您无需使用架构自动检测功能，也无需提供内嵌架构定义或架构文件。

您可以为存储在 Cloud Storage 或云端硬盘中的 Avro、Parquet 或 ORC 数据创建表定义文件：

  1. 使用 bq mkdef 命令创建表定义。

    bq mkdef \
    --source_format=FORMAT \
    "URI" > FILE_NAME

    请替换以下内容：

    • FORMAT：来源格式

    • URI：Cloud Storage URI 或云端硬盘 URI

      例如，gs://mybucket/myfile（对于 Cloud Storage）或 https://drive.google.com/open?id=123ABCD123AbcD123Abcd（对于云端硬盘）。

    • FILE_NAME：表定义文件的名称

  2. 可选：在文本编辑器中打开表定义文件。该文件类似于以下内容：

    {
   "sourceFormat": "AVRO",
   "sourceUris": [
   "URI"
    ]
}

  3. 可选：手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等常规设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

为 Hive 分区数据创建定义文件

使用带有 hive_partitioning_mode 和 hive_partitioning_source_uri_prefix 标志的 bq mkdef 命令，为存储在 Cloud Storage、Amazon Simple Storage Service (Amazon S3) 或 Azure Blob Storage 中的 Hive 分区数据创建定义文件。

为 Datastore 和 Firestore 创建定义文件

如果您使用 Datastore 或 Firestore 导出文件作为外部数据源，BigQuery 会自动使用自描述源数据检索架构。创建表定义时，无需提供内嵌架构定义或架构文件。

您可以针对 Cloud Storage 中存储的 Datastore 和 Firestore 导出数据创建表定义文件：

  1. 使用 bq mkdef 命令创建表定义。对于 Datastore 或 Firestore 备份文件，您无需使用 --noautodetect 标志，系统对此类文件类型停用了架构自动检测功能。mkdef 命令会生成 JSON 格式的表定义文件。以下示例会创建表定义并将输出写入 /tmp/file_name 文件。

    bq mkdef \
--source_format=DATASTORE_BACKUP \
"URI" > /tmp/FILE_NAME

    请替换以下内容：

    Datastore 和 Firestore 均使用 DATASTORE_BACKUP 源格式。

  2. （可选）在文本编辑器中打开表定义文件。例如，nano /tmp/file_name 命令会使用 nano 打开此文件。 文件内容应如下所示。请注意，您无需使用 "autodetect" 设置。

    {
"sourceFormat": "DATASTORE_BACKUP",
"sourceUris": [
  "gs://URI"
]
}

  3. （可选）手动修改表定义文件，以修改、添加或删除 maxBadRecords 和 ignoreUnknownValues 等设置。没有特定于 Datastore 和 Firestore 导出文件的配置设置。如需了解详情，请参阅 API 参考文档中的 ExternalDataConfiguration。

为 Bigtable 创建定义文件

针对 Bigtable 创建表定义文件时，需要手动生成 JSON 格式的文件。目前，系统不支持使用 mkdef 命令创建 Bigtable 数据源的表定义，也不支持对 Bigtable 使用架构自动检测功能。如需查看 Bigtable 表定义选项列表，请参阅 REST API 参考文档中的 BigtableOptions。

Bigtable 的 JSON 表定义文件如下所示。使用此表定义文件时，BigQuery 会从单个列族读取数据，并将值解释为二进制编码整数。

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

请替换以下内容：

  • PROJECT_ID：您的 Bigtable 集群所在的项目
  • INSTANCE_ID：Bigtable 实例 ID
  • TABLE_NAME：您要查询的表的名称
  • FAMILY_ID：列族标识符

如需了解详情，请参阅检索 Bigtable 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 命令行工具时，您可能需要在某些平台上对星号进行转义。

如果要使用星号通配符，请用英文引号将存储桶和文件名括起来。例如，如果您有两个名为 fed-sample000001.csv 和 fed-sample000002.csv 的文件，并且想使用星号选择这两个文件，则存储桶 URI 为 "gs://mybucket/fed-sample*"。

为以下数据源创建表定义文件时，不得使用 * 通配符：

  • Bigtable。对于 Bigtable 数据，您只能指定一个数据源。URI 值必须是 Bigtable 表的有效 HTTPS 网址。
  • Datastore 或 Firestore。存储在 Cloud Storage 中的 Datastore 或 Firestore 导出文件 对于 Datastore 备份，您只能指定一个数据源。URI 值必须以 .backup_info 或 .export_metadata 结尾。
  • 云端硬盘。存储在云端硬盘中的数据。

后续步骤