使用架构自动检测功能

架构自动检测功能

在将数据加载到 BigQuery 以及查询外部数据源时，您可以使用架构自动检测功能。

启用自动检测功能后，BigQuery 会按以下方式开始推断架构：在数据源中随机选择一个文件，然后扫描多达 100 行数据以用作代表性样本。然后，BigQuery 会检查每个字段，并尝试根据样本中的值向该字段分配数据类型。

如需查看检测到的表架构，您可以采用以下方式：

使用命令行工具的 bq show 命令
使用 Cloud Console 或经典版网页界面查看表架构

如果 BigQuery 检测到架构，则在极少数的情况下可能会更改字段名称，使其与 BigQuery SQL 语法兼容。

如需了解数据类型转换，请参阅以下内容：

从 Datastore 加载数据时的数据类型转换
从 Firestore 加载数据时的数据类型转换
Avro 转换
Parquet 转换
ORC 转换

在加载数据时使用架构自动检测功能

如需在加载数据时启用架构自动检测功能，请按如下所述操作：

Cloud Console：在架构部分的自动检测下，勾选架构和输入参数选项。
经典版 BigQuery 网页界面：在 Schema 部分，勾选 Automatically detect 选项。
CLI：使用带 --autodetect 参数的 bq load 命令。

如果启用了自动检测功能，BigQuery 将尽力尝试自动推断出 CSV 和 JSON 文件的架构。

架构自动检测功能不适用于 Avro 文件、Parquet 文件、ORC 文件、Firestore 导出文件或 Datastore 导出文件。在将这些文件加载到 BigQuery 中时，系统会自动从自描述源数据中检索表架构。

如需在加载 JSON 或 CSV 数据时使用架构自动检测功能，请执行如下操作：

控制台

在 Cloud Console 中打开 BigQuery 网页界面。
转到 Cloud Console
从导航面板的资源部分选择数据集。
点击窗口右侧的创建表。
在创建表页面的来源部分，执行以下操作：
- 在基于以下数据创建表部分，选择所需的来源类型。
- 在来源字段中，浏览并找到相应的文件/Cloud Storage 存储分区，或输入 Cloud Storage URI。请注意，BigQuery 网页界面不支持添加多个 URI，但支持使用通配符。Cloud Storage 存储分区必须与您要创建的表所属的数据集位于同一位置。
- 在文件格式部分，选择 CSV 或 JSON。
在创建表页面的目标位置部分，执行以下操作：
- 在数据集名称部分，选择相应数据集。
- 在表名称字段中，输入您要创建的表的名称。
- 确认表类型设置为原生表。
点击创建表。

经典版界面

转到 BigQuery 网页界面。
转到 BigQuery 网页界面
在导航窗格中，点击数据集名称旁边的向下箭头图标，然后点击 Create new table。

注意：在此界面中，数据加载过程与表的创建过程相同。
在 Create table 页面中执行以下操作：
- 在 Source Data 部分，点击 Create from source。
- 在 Destination Table 部分，选择您的数据集，并在 Destination table name 字段中输入表名。
- 在 Schema 部分，点击 Automatically detect 以确定架构。
- 点击创建表。

CLI

发出带 --autodetect 参数的 bq load 命令。

（可选）提供 --location 标志并将其值设置为您所在的位置。

以下命令通过架构自动检测功能加载文件：

    bq --location=location load \
    --autodetect \
    --source_format=format \
    dataset.table \
    path_to_source

其中：

location 是您所在位置的名称。--location 是可选标志。例如，如果您在东京区域使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置默认位置值。
format 是 NEWLINE_DELIMITED_JSON 或 CSV。
dataset 是您要向其中加载数据的表所属的数据集。
table 是要向其中加载数据的表的名称。
path_to_source 是 CSV 或 JSON 文件的位置。

示例：

输入以下命令可以将本地机器中的 myfile.csv 加载到名为 mytable 的表中，该表存储在名为 mydataset 的数据集中。

bq load --autodetect --source_format=CSV mydataset.mytable ./myfile.csv

输入以下命令可以将本地机器中的 myfile.json 加载到名为 mytable 的表中，该表存储在名为 mydataset 的数据集中。

bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable ./myfile.json

API

创建指向源数据的 load 作业。如需了解如何创建作业，请参阅以编程方式运行 BigQuery 作业。在 jobReference 部分的 location 属性中指定您所在的位置。
设置 sourceFormat 属性以指定数据格式。如需使用架构自动检测功能，必须将此值设置为 NEWLINE_DELIMITED_JSON 或 CSV。
使用 autodetect 属性将架构自动检测功能设置为 true。

Go

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

在 GitHub 上查看反馈

import (
    	"context"
    	"fmt"

    	"cloud.google.com/go/bigquery"
    )

    // importJSONAutodetectSchema demonstrates loading data from newline-delimited JSON data in Cloud Storage
    // and using schema autodetection to identify the available columns.
    func importJSONAutodetectSchema(projectID, datasetID, tableID string) error {
    	// projectID := "my-project-id"
    	// datasetID := "mydataset"
    	// tableID := "mytable"
    	ctx := context.Background()
    	client, err := bigquery.NewClient(ctx, projectID)
    	if err != nil {
    		return fmt.Errorf("bigquery.NewClient: %v", err)
    	}
    	defer client.Close()

    	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
    	gcsRef.SourceFormat = bigquery.JSON
    	gcsRef.AutoDetect = true
    	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
    	loader.WriteDisposition = bigquery.WriteEmpty

    	job, err := loader.Run(ctx)
    	if err != nil {
    		return err
    	}
    	status, err := job.Wait(ctx)
    	if err != nil {
    		return err
    	}

    	if status.Err() != nil {
    		return fmt.Errorf("job completed with error: %v", status.Err())
    	}
    	return nil
    }

Node.js

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

在 GitHub 上查看反馈

// Import the Google Cloud client libraries
    const {BigQuery} = require('@google-cloud/bigquery');
    const {Storage} = require('@google-cloud/storage');

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

    /**
     * This sample loads the JSON file at
     * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json
     *
     * TODO(developer): Replace the following lines with the path to your file.
     */
    const bucketName = 'cloud-samples-data';
    const filename = 'bigquery/us-states/us-states.json';

    async function loadJSONFromGCSAutodetect() {
      // Imports a GCS file into a table with autodetected schema.

      // Instantiate clients
      const bigquery = new BigQuery();
      const storage = new Storage();

      // Configure the load job. For full list of options, see:
      // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad
      const metadata = {
        sourceFormat: 'NEWLINE_DELIMITED_JSON',
        autodetect: true,
        location: 'US',
      };

      // Load data from a Google Cloud Storage file into the table
      const [job] = await bigquery
        .dataset(datasetId)
        .table(tableId)
        .load(storage.bucket(bucketName).file(filename), metadata);
      // load() waits for the job to finish
      console.log(`Job ${job.id} completed.`);

      // Check the job's status for errors
      const errors = job.status.errors;
      if (errors && errors.length > 0) {
        throw errors;
      }
    }
    loadJSONFromGCSAutodetect();

PHP

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

在 GitHub 上查看反馈

use Google\Cloud\BigQuery\BigQueryClient;
    use Google\Cloud\Core\ExponentialBackoff;

    /** Uncomment and populate these variables in your code */
    // $projectId  = 'The Google project ID';
    // $datasetId  = 'The BigQuery dataset ID';

    // instantiate the bigquery table service
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table('us_states');

    // create the import job
    $gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';
    $loadConfig = $table->loadFromStorage($gcsUri)->autodetect(true)->sourceFormat('NEWLINE_DELIMITED_JSON');
    $job = $table->runJob($loadConfig);
    // poll the job until it is complete
    $backoff = new ExponentialBackoff(10);
    $backoff->execute(function () use ($job) {
        print('Waiting for job to complete' . PHP_EOL);
        $job->reload();
        if (!$job->isComplete()) {
            throw new Exception('Job has not yet completed', 500);
        }
    });
    // check if the job has errors
    if (isset($job->info()['status']['errorResult'])) {
        $error = $job->info()['status']['errorResult']['message'];
        printf('Error running job: %s' . PHP_EOL, $error);
    } else {
        print('Data imported successfully' . PHP_EOL);
    }

Python

如需启用架构自动检测功能，请将 LoadJobConfig.autodetect 属性设置为 True。

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

在 GitHub 上查看反馈

# from google.cloud import bigquery
    # client = bigquery.Client()
    # dataset_id = 'my_dataset'

    dataset_ref = client.dataset(dataset_id)
    job_config = bigquery.LoadJobConfig()
    job_config.autodetect = True
    job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
    uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
    load_job = client.load_table_from_uri(
        uri, dataset_ref.table("us_states"), job_config=job_config
    )  # API request
    print("Starting job {}".format(load_job.job_id))

    load_job.result()  # Waits for table load to complete.
    print("Job finished.")

    destination_table = client.get_table(dataset_ref.table("us_states"))
    print("Loaded {} rows.".format(destination_table.num_rows))

Ruby

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

在 GitHub 上查看反馈

require "google/cloud/bigquery"

    def load_table_gcs_json_autodetect dataset_id = "your_dataset_id"
      bigquery = Google::Cloud::Bigquery.new
      dataset  = bigquery.dataset dataset_id
      gcs_uri  = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
      table_id = "us_states"

      load_job = dataset.load_job table_id,
                                  gcs_uri,
                                  format:     "json",
                                  autodetect: true
      puts "Starting job #{load_job.job_id}"

      load_job.wait_until_done! # Waits for table load to complete.
      puts "Job finished."

      table = dataset.table table_id
      puts "Loaded #{table.rows_count} rows to table #{table.id}"
    end

对外部数据源使用架构自动检测功能

如需在创建链接到外部数据源的表时启用架构自动检测功能，请执行以下操作：

在 Cloud Console 中，在自动检测部分勾选架构和输入参数选项。
在经典版 BigQuery 网页界面中，勾选 Automatically detect 选项。

启用架构自动检测功能后，BigQuery 会尽力尝试自动推断出 CSV 和 JSON 外部数据源的架构。

目前，您无法使用 Cloud Console 或经典版网页界面为 Google 表格外部数据源启用架构自动检测功能。此外，架构自动检测功能不适用于外部 Avro 文件、Firestore 导出文件或 Datastore 导出文件。在创建链接到这些文件类型的表时，BigQuery 会自动从自描述源数据中检索架构。

使用 CLI 时，您可以在为 CSV、JSON 或 Google 表格数据创建表定义文件时启用架构自动检测功能。使用 CLI 创建表定义文件时，您可以将 --autodetect 标志传递给 mkdef 命令来启用架构自动检测功能，或者传递 --noautodetect 标志来停用自动检测功能。

使用 --autodetect 标志时，应在表定义文件中将 autodetect 设置设为 true。使用 --noautodetect 标志时，应将 "autodetect" 设置设为 false。如果您在创建表定义时未提供外部数据源的架构定义，且未使用 --noautodetect 或 --autodetect 标志，则 "autodetect" 设置将默认为 true。

使用 API 创建表定义文件时，将 autodetect 属性的值设置为 true 或 false。将 autodetect 设置为 true 可启用自动检测功能。将 autodetect 设置为 false 可停用自动检测功能。

自动检测功能详述

除了检测架构详细信息外，自动检测功能还可识别以下内容：

压缩

打开文件时，BigQuery 会识别与 gzip 兼容的文件压缩。

CSV 分隔符

BigQuery 会检测以下分隔符：

逗号 (,)
竖线 (|)
制表符 (\t)

CSV 标题

BigQuery 会通过将文件的第一行与数据集中的其他行进行比较来推断出标题。如果第一行只包含字符串，而其他行则不包含，则 BigQuery 会假定第一行是标题行。

CSV 中带英文引号的新行

BigQuery 可检测 CSV 字段中带英文引号的新行字符，但不会将其解释为行边界。

日期

在对 JSON 或 CSV 数据使用架构检测功能时，DATE 列中的值必须使用短划线 (-) 分隔符，且必须采用 YYYY-MM-DD（年-月-日）格式。

时间戳

BigQuery 可检测各种时间戳格式，包括但不限于：

yyyy-mm-dd
yyyy-mm-dd hh:mm:ss
yyyy-mm-dd hh:mm:ss.mmm

时间戳还可包含世界协调时间 (UTC) 偏移量和世界协调时间 (UTC) 地区指示符 Z，同时还支持整数形式的时间戳值。

在对 JSON 或 CSV 数据使用架构检测功能时，TIMESTAMP 列中的值必须在时间戳的日期部分使用短划线 (-) 分隔符，且日期必须采用 YYYY-MM-DD（年-月-日）格式。时间戳的 hh:mm:ss（时-分-秒）部分必须使用英文冒号 (:) 分隔符。

时间戳示例

以下示例显示了 BigQuery 可自动检测到的时间戳格式：

253402300799
2018-07-05 12:54:00 UTC
2018-08-19 07:11:35.220 -05:00
2018-08-19 12:11:35.220 UTC
2018-08-19T12:11:35.220Z
2.53402300799e11
2018-08-19 12:11:35.220000
2018-08-19 12:11:35.220