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

本页面介绍了如何为外部数据源创建表定义文件。外部数据源（也称为“联合数据源”）是可供直接查询的数据源，即使数据未存储在 BigQuery 中也是如此。

表定义文件

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

表定义文件与 bq 命令行工具搭配使用。使用 REST API 时，您也可以使用表定义文件中的属性来创建 ExternalDataConfiguration。使用 Cloud Console 或经典版 BigQuery 网页界面创建外部表时，不可使用表定义文件。

您可以为以下外部数据源创建表定义文件：

• Cloud Storage

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

• Google 云端硬盘

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

• Cloud Bigtable

准备工作

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

• 对于 Google 云端硬盘数据源，您需要知道 Google 云端硬盘 URI
• 对于 Cloud Storage 数据源，您需要知道 Cloud Storage URI
• 对于 Cloud Bigtable 数据源，您需要知道 Cloud Bigtable URI

永久外部表与临时外部表

您可以使用永久表或临时表在 BigQuery 中查询外部数据源。永久表是在数据集中创建的表，该表链接到外部数据源。由于该表是永久性的，因此您可以使用访问权限控制与其他同样有权访问底层外部数据源的人员共享该表，还可以随时查询该表。

使用临时表查询外部数据源时，您需要提交一个命令，该命令必须包含查询并创建一个链接到外部数据源的非永久表。使用临时表时，不会在任何 BigQuery 数据集内创建表。由于该表并非永久存储在数据集内，因此无法与他人共享。如果您要对外部数据进行一次性临时查询，或者要执行提取、转换和加载 (ETL) 过程，那么使用临时表查询外部数据源会非常有用。

您可以使用表定义文件来描述永久外部表或临时外部表。

使用架构自动检测功能创建表定义

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

针对以下文件创建表定义时可以使用架构自动检测功能：

• 存储在 Cloud Storage 或 Google 云端硬盘中的 JSON 文件
• 存储在 Cloud Storage 或 Google 云端硬盘中的 CSV 文件
• 存储在 Google 云端硬盘中的 Google 表格文件

Cloud Storage

要使用 bq 命令行工具为 Cloud Storage 数据源创建表定义，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 source_format 替换为您的文件格式：NEWLINE_DELIMITED_JSON、CSV 或 GOOGLE_SHEETS。
   • 将 file_name 替换为您的表定义文件的名称。
   • 将 bucket_uri 替换为您的 Cloud Storage URI，例如 gs://mybucket/myfile。

   bq mkdef \
   --autodetect \
   --source_format=source_format \
   "bucket_uri" > /tmp/file_name
   

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": [
     "bucket_uri"
   ]
   }
   

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

外部分区数据

如需针对 Cloud Storage 上的外部分区数据配置表定义文件，请参阅查询外部分区数据。

Google 云端硬盘

要使用 bq 命令行工具为 Google 云端硬盘数据源创建表定义，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 source_format 替换为您的文件格式：NEWLINE_DELIMITED_JSON、CSV 或 GOOGLE_SHEETS。
   • 将 file_name 替换为您的表定义文件的名称。
   • 将 drive_uri 替换为您的 Google 云端硬盘 URI，例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

   bq mkdef \
   --autodetect \
   --source_format=source_format \
   "drive_uri" > /tmp/file_name
   

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

   {
   "autodetect": true,
   "sourceFormat": "GOOGLE_SHEETS",
   "sourceUris": [
     "drive_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 或 Google 云端硬盘中的 JSON 文件
• 存储在 Cloud Storage 或 Google 云端硬盘中的 CSV 文件
• 存储在 Google 云端硬盘中的 Google 表格文件

如需通过 bq 命令行工具使用内嵌架构定义为 Cloud Storage 数据源创建表定义，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 source_format 替换为您的文件格式：NEWLINE_DELIMITED_JSON、CSV 或 GOOGLE_SHEETS。
   • 将 uri 替换为您的 Cloud Storage URI 或 Google 云端硬盘 URI。例如，https://drive.google.com/open?id=123ABCD123AbcD123Abcd（对于 Google 云端硬盘）或 gs://mybucket/myfile（对于 Cloud Storage）。
   • 将 field:data_type,field:data_type 替换为您的架构定义，例如 Name:STRING,Address:STRING, ...。
   • 将 file_name 替换为您的表定义文件的名称。

   bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "uri" \
   field:data_type,field:data_type > /tmp/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 或 Google 云端硬盘中引用 JSON 架构文件。

针对以下文件创建表定义时可以使用 JSON 架构文件：

• 存储在 Cloud Storage 或 Google 云端硬盘中的 JSON 文件
• 存储在 Cloud Storage 或 Google 云端硬盘中的 CSV 文件
• 存储在 Google 云端硬盘中的 Google 表格文件

Cloud Storage

要通过 bq 命令行工具使用 JSON 架构文件为 Cloud Storage 数据源创建表定义，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 source_format 替换为您的文件格式：NEWLINE_DELIMITED_JSON、CSV 或 GOOGLE_SHEETS。
   • 将 file_name 替换为您的表定义文件的名称。
   • 将 bucket_uri 替换为您的 Cloud Storage URI，例如 gs://mybucket/myfile。
   • 将 path_to_schema_file 替换为您本地机器上存放 JSON 架构文件的位置。

   bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "bucket_uri" \
   path_to_schema_file > /tmp/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": [
     "bucket_uri"
   ]
   }
   

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

Google 云端硬盘

要通过 bq 命令行工具使用 JSON 架构文件为 Google 云端硬盘数据源创建表定义，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 source_format 替换为您的文件格式：NEWLINE_DELIMITED_JSON、CSV 或 GOOGLE_SHEETS。
   • 将 drive_uri 替换为您的 Google 云端硬盘 URI，例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。
   • 将 path_to_schema_file 替换为您本地机器上存放 JSON 架构文件的位置。
   • 将 file_name 替换为您的表定义文件的名称。

   bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "drive_uri" \
   path_to_schema_file > /tmp/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": [
     "drive_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 表定义

如果您使用 Avro 文件作为外部数据源，BigQuery 将使用该源数据自动检索架构。为 Avro 文件创建表定义时，您无需使用架构自动检测功能，也无需提供内嵌架构定义或架构文件。

您可以针对存储在 Cloud Storage 或 Google 云端硬盘中的 Avro 数据创建表定义文件。

要使用 bq 命令行工具为 Avro 数据创建表定义文件，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 uri 替换为您的 Cloud Storage URI 或 Google 云端硬盘 URI。例如，https://drive.google.com/open?id=123ABCD123AbcD123Abcd（对于 Google 云端硬盘）或 gs://mybucket/myfile（对于 Cloud Storage）。
   • 将 file_name 替换为您的表定义文件的名称。

   bq mkdef \
   --source_format=AVRO \
   "uri" > /tmp/file_name
   

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

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

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

创建 Datastore 和 Firestore 导出表定义

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

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

要使用 bq 命令行工具为 Datastore 或 Firestore 导出数据创建表定义文件，请执行以下操作：

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

   在以下命令中，进行如下替换：

   • 将 bucket_uri 替换为您的 Cloud Storage URI。
   • 将 file_name 替换为您的表定义文件的名称。

   请注意，Datastore 和 Firestore 均使用 DATASTORE_BACKUP 源格式。

   bq mkdef \
   --source_format=DATASTORE_BACKUP \
   "uri" > /tmp/file_name
   

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

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

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

创建 Cloud Bigtable 表定义

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

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

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/project_id/instances/instance_id/tables/table_name"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "family_int",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

注意：在 sourceUris 条目中，project_id 是您的 Cloud Bigtable 集群所属的项目，instance_id 是 Cloud Bigtable 实例 ID，table 是您要查询的表的名称。如需了解详情，请参阅检索 Cloud Bigtable URI。

表定义文件的通配符支持

如果您的 Cloud Storage 数据分散在多个共用一个通用基本名称的文件中，则可以在表定义文件的 URI 中使用通配符。您可以向基本名称添加一个星号 (*)，并给存储分区和文件名加上英文引号。例如，如果您有两个名为 fed-sample000001.csv 和 fed-sample000002.csv 的文件，则存储分区 URI 为 "gs://mybucket/fed-sample*"。

存储分区中的对象（文件名）仅可使用一个通配符。通配符可以出现在对象名称内或对象名称末尾。 系统不支持在存储分区名称中附加通配符。

对于 Cloud Bigtable 数据，您只能指定一个 URI，并且此 URI 必须是 Cloud Bigtable 表的完整有效 HTPPS 网址。对于 Datastore 备份，您只能指定一个 URI，而且该 URI 必须以 .backup_info 结尾。

针对以下内容创建表定义文件时，不得使用 * 通配符：

• Cloud Bigtable 数据源
• 存储在 Cloud Storage 中的 Datastore 导出文件
• 存储在 Cloud Storage 中的 Firestore 导出文件
• Google 云端硬盘中存储的数据