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

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

表定义文件

表定义文件包含外部表的架构定义和元数据,例如表的数据格式及相关属性。创建表定义文件时,您可以使用架构自动检测功能为外部数据源定义架构,可以通过内嵌方式提供架构(使用命令行),还可以提供包含架构定义的 JSON 文件。

表定义文件可与 BigQuery CLI 结合使用。使用 REST API 时,您也可以使用表定义文件中的属性来创建 ExternalDataConfiguration。使用 GCP Console 或经典版 BigQuery 网页界面创建外部表时,不可使用表定义文件。

您可以针对以下外部数据源创建表定义文件:

  • Cloud Storage

    • 逗号分隔值 (CSV)
    • 以换行符分隔的 JSON
    • Avro 文件
    • Cloud Datastore 导出文件
    • Cloud Firestore 导出文件
  • Google 云端硬盘

    • 逗号分隔值 (CSV)
    • 以换行符分隔的 JSON
    • Avro 文件
    • Google 表格
  • 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

如需使用 CLI 为 Cloud Storage 数据源创建表定义,请执行以下操作:

  1. 在命令行工具中,使用带 --autodetect 标志的 mkdef 命令来创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。下面的示例将创建表定义并将输出内容写入 /tmp/file_name 文件中。

    在以下命令中,进行如下替换:

    • source_format 替换为您的文件格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 JSON 源文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 API 参考中的 ExternalDataConfiguration

Google 云端硬盘

如需使用 CLI 为 Google 云端硬盘数据源创建表定义,请执行以下操作:

  1. 在命令行工具中,使用带 --autodetect 标志的 mkdef 命令来创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。下面的示例将创建表定义并将输出内容写入 /tmp/file_name 文件中。

    在以下命令中,进行如下替换:

    • source_format 替换为您的文件格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 JSON 源文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 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 表格文件

如需通过 CLI 使用内嵌架构定义为 Cloud Storage 数据源创建表定义,请执行以下操作:

  1. 在命令行工具中,使用带 --noautodetect 标志的 mkdef 命令来创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。下面的示例将创建表定义并将输出内容写入 /tmp/file_name 文件中。

    在以下命令中,进行如下替换:

    • source_format 替换为您的文件格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_SHEETS
    • uri 替换为您的 Cloud Storage URIGoogle 云端硬盘 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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 JSON 源文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 API 参考中的 ExternalDataConfiguration

使用 JSON 架构文件创建表定义

如果您不想使用自动检测功能,也不想提供内嵌架构定义,则可以创建 JSON 架构文件,然后在创建表定义文件时引用该文件。您必须手动创建 JSON 架构文件并将其置于本地机器中。系统不支持在 Cloud Storage 或 Google 云端硬盘中引用 JSON 架构文件。

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

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

Cloud Storage

如需通过 CLI 使用 JSON 架构文件为 Cloud Storage 数据源创建表定义,请执行以下操作:

  1. 在命令行工具中,使用带 --noautodetect 标志的 mkdef 命令来创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。下面的示例将创建表定义并将输出内容写入 /tmp/file_name 文件中。

    在以下命令中,进行如下替换:

    • source_format 替换为您的文件格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 JSON 源文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 API 参考中的 ExternalDataConfiguration

Google 云端硬盘

如需通过 CLI 使用 JSON 架构文件为 Google 云端硬盘数据源创建表定义,请执行以下操作:

  1. 在命令行工具中,使用带 --noautodetect 标志的 mkdef 命令来创建表定义。mkdef 命令会生成 JSON 格式的表定义文件。下面的示例将创建表定义并将输出内容写入 /tmp/file_name 文件中。

    在以下命令中,进行如下替换:

    • source_format 替换为您的文件格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 JSON 源文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 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 数据创建表定义文件。

如需使用 CLI 为 Avro 数据创建表定义文件,请执行以下操作:

  1. 在命令行工具中,使用 mkdef 命令来创建表定义。对于 Avro 文件,无需使用 --noautodetect 标志,而且系统已停用架构自动检测功能。mkdef 命令会生成 JSON 格式的表定义文件。下面的示例将创建表定义并将输出内容写入 /tmp/file_name 文件中。

    在以下命令中,进行如下替换:

    • uri 替换为您的 Cloud Storage URIGoogle 云端硬盘 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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 Avro 源文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 API 参考中的 ExternalDataConfiguration

创建 Cloud Datastore 和 Cloud Firestore 导出数据表定义

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

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

如需使用 CLI 为 Cloud Datastore 或 Cloud Firestore 导出数据创建表定义文件,请执行以下操作:

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

    在以下命令中,进行如下替换:

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

    请注意,Cloud Datastore 和 Cloud 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. (可选)手动修改表定义文件,以修改、添加或删除 maxBadRecordsignoreUnknownValues 等常规设置。虽然没有提供特定于 Cloud Datastore 和 Cloud Firestore 导出文件的配置设置,但很多设置都适用于 CSVGoogle 表格文件。如需了解详情,请参阅 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"
            }
        ],
    }
}

表定义文件的通配符支持

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

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

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

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

  • Cloud Bigtable 数据源
  • Cloud Storage 中存储的 Cloud Datastore 导出数据
  • Cloud Storage 中存储的 Cloud Firestore 导出数据
  • Google 云端硬盘中存储的数据
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面