使用架构自动检测功能

架构自动检测功能

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

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

如需查看检测到的表架构，请执行如下操作：

  • 在命令行中，使用 bq show 命令
  • 使用 Cloud Console 或经典版网页界面查看表架构

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

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

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

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

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

启用后，BigQuery 将尽可能尝试自动推断出 CSV 和 JSON 文件的架构。

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

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

控制台

  1. 在 Cloud Console 中，转到 BigQuery 网页界面。
    转到 Cloud Console

  2. 在导航面板的资源部分中，选择一个数据集。

  3. 点击创建表。

    创建表。

  4. 在创建表页面的来源部分，执行以下操作：

    • 在基于以下数据创建表部分，选择所需的来源类型。

    • 在来源字段中，浏览文件/Cloud Storage 存储分区，或输入 Cloud Storage URI。请注意，BigQuery 网页界面不支持添加多个 URI，但支持使用通配符。Cloud Storage 存储分区必须与您要创建的表所属的数据集位于同一位置。

      选择文件。

    • 在文件格式部分，选择 CSV 或 JSON。

  5. 在创建表页面的目标位置部分，执行以下操作：

    • 在数据集名称部分，选择相应数据集。

      选择数据集。

    • 在表名称字段中，输入您要创建的表的名称。

    • 确认表类型是否设置为原生表。

  6. 点击创建表。

经典版界面

  1. 转到 BigQuery 网页界面。
    转到 BigQuery 网页界面

  2. 在导航面板中，点击数据集名称旁边的向下箭头图标 向下箭头图标。。

  3. 点击 Create new table。

    注意：在界面中，数据加载过程与表的创建过程相同。

  4. 在 Create table 页面上，执行以下操作：

    • 对于 Source Data，点击 Create from source。
    • 对于 Destination Table，选择您的数据集并在 Destination table name 字段中输入表名。

    • 对于 Schema，点击 Automatically detect 来确定架构。

      自动检测链接。

    • 点击 Create Table。

bq

发出带 --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 从本地机器加载到存储在 mydataset 数据集的 mytable 表中。

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

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

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

API

  1. 创建指向源数据的 load 作业。如需了解如何创建作业，请参阅以编程方式运行 BigQuery 作业。在 jobReference 部分的 location 属性中指定您的位置。

  2. 设置 sourceFormat 属性以指定数据格式。如需使用架构自动检测功能，必须将此值设置为 NEWLINE_DELIMITED_JSON 或 CSV。

  3. 使用 autodetect 属性将架构自动检测设置为 true。

Go

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

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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.TableId;

// Sample to load JSON data with autodetect schema from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCSAutodetect {

  public static void runLoadJsonFromGCSAutodetect() {
    // 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/us-states/us-states.json";
    loadJsonFromGCSAutodetect(datasetName, tableName, sourceUri);
  }

  public static void loadJsonFromGCSAutodetect(
      String datasetName, String tableName, 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();

      TableId tableId = TableId.of(datasetName, tableName);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setAutodetect(true)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Json Autodetect from GCS successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

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

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

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

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name

# Set the encryption key to use for the destination.
# TODO: Replace this key with a key you have created in KMS.
# kms_key_name = "projects/{}/locations/{}/keyRings/{}/cryptoKeys/{}".format(
#     "cloud-samples-tests", "us", "test", "test"
# )
job_config = bigquery.LoadJobConfig(
    autodetect=True, 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, table_id, job_config=job_config
)  # Make an API request.
load_job.result()  # Waits for the job to complete.
destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

Ruby

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

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 会自动从自描述源数据中检索架构。

借助命令行界面，您可以在为 CSV、JSON 或 Google 表格数据创建表定义文件时启用架构自动检测功能。使用命令行界面创建表定义文件时，将 --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 会假设第一行是标题行。在这种情况下，BigQuery 会根据标题行中的字段名称分配列名称。您可以根据 BigQuery 中列的命名规则修改名称。例如，空格将替换为下划线。

否则，BigQuery 会假设第一行是数据行，并分配常规列名（例如 string_field_1）。请注意，创建表后，无法在架构中更新列名，但您可以在创建表后手动更改名称。另一种方法是提供显式架构，而不是使用自动检测。

您可能拥有一个包含标题行的 CSV 文件，其所有数据字段都是字符串。在这种情况下，BigQuery 不会自动检测第一行是否为标题。使用 -skip_leading_rows 选项跳过标题行。否则，标题将作为数据导入。在这种情况下，请考虑提供显式架构，以便您可以分配列名。

CSV 中括起的新行

BigQuery 可检测 CSV 字段中括起的新行字符，不会将其解释为行边界。

日期和时间值

BigQuery 会根据源数据的格式检测日期和时间值。

DATE 列中的值必须采用以下格式：YYYY-MM-DD。

TIME 列中的值必须采用以下格式：HH:MM:SS[.SSSSSS]（小数秒部分为可选）。

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

  • YYYY-MM-DD HH:MM
  • YYYY-MM-DD HH:MM:SS
  • YYYY-MM-DD HH:MM:SS.SSSSSS
  • YYYY/MM/DD HH:MM

时间戳还可包含世界协调时间 (UTC) 偏移量或世界协调时间 (UTC) 区域指示符 ('Z')。

以下是 BigQuery 将自动检测为时间戳值的一些值示例：

  • 2018-08-19 12:11
  • 2018-08-19 12:11:35.22
  • 2018/08/19 12:11
  • 2018-08-19 07:11:35.220 -05:00

如果 BigQuery 无法识别格式，则会以字符串数据类型加载该列。在这种情况下，您可能需要在加载源数据之前进行预处理。例如，如果您要从电子表格导出 CSV 数据，请将日期设置为与以下某个示例相匹配的格式。或者，您可以在将数据加载到 BigQuery 后转换数据。