使用架构自动检测功能

架构自动检测功能

架构自动检测功能可让 BigQuery 推断出 CSV、JSON 或 Google 表格数据的架构。将数据加载到 BigQuery 以及查询外部数据源时，您可以使用架构自动检测功能。

启用自动检测功能后，BigQuery 会推断每一列的数据类型。BigQuery 会在数据源中随机选择一个文件，然后扫描多达前 500 行数据以用作代表性样本。然后，BigQuery 会检查每个字段，并尝试根据样本中的值向该字段分配数据类型。 如果列中的所有行都为空，则自动检测功能会将列默认为 STRING 数据类型。

如果不为 CSV、JSON 或 Google 表格数据启用架构自动检测功能，则必须在创建表时手动提供架构。

您无需为 Avro、Parquet、ORC、Firestore 导出文件或 Datastore 导出文件启用架构自动检测功能。这些文件格式是自描述格式，因此 BigQuery 会自动根据源数据推断表架构。对于 Parquet、Avro 和 Orc 文件，您可以选择性地提供显式架构以替换推断的架构。

您可以通过下列方式查看检测到的表架构：

• 使用 Google Cloud 控制台。
• 使用 bq 命令行工具的 bq show 命令。

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

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

• 数据类型转换（从 Datastore 加载数据时）
• 数据类型转换（从 Firestore 加载数据时）
• Avro 转换
• Parquet 转换
• ORC 转换

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

如需在加载数据时启用架构自动检测功能，请使用以下方法之一：

• 在 Google Cloud 控制台中，对于架构部分的自动检测，勾选架构和输入参数选项。
• 在 bq 命令行工具中，将 bq load 命令与 --autodetect 参数结合使用。

如果启用了架构自动检测功能，BigQuery 将尽力尝试自动推断出 CSV 和 JSON 文件的架构。 自动检测逻辑通过读取最多前 500 行数据来推断架构字段类型。如果存在 --skip_leading_rows 标志，则会跳过前导行。字段类型以字段最多的行为基础。因此，只要至少一行数据在每一列/字段中都有值，自动检测功能就会按预期运行。

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

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

控制台

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在浏览器面板中，展开您的项目并选择数据集。

3. 展开 操作选项，然后点击打开。

4. 在详情面板中，点击创建表 。

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

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

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

     

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

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

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

     

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

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

7. 点击创建表。

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

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为客户端库设置身份验证。

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());
    }
  }
}

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
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 CSV data with autodetect schema from Cloud Storage into a new BigQuery table
public class LoadCsvFromGcsAutodetect {

  public static void main(String[] args) {
    // 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.csv";
    loadCsvFromGcsAutodetect(datasetName, tableName, sourceUri);
  }

  public static void loadCsvFromGcsAutodetect(
      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);

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(csvOptions)
              .setAutodetect(true)
              .build();

      // Load data from a GCS CSV 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() && job.getStatus().getError() == null) {
        System.out.println("CSV 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 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为客户端库设置身份验证。

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

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为客户端库设置身份验证。

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Imports data to the given table from json file present in GCS by auto
 * detecting options and schema.
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId The BigQuery table ID.
 */
function import_from_storage_json_autodetect(
    string $projectId,
    string $datasetId,
    string $tableId = 'us_states'
): void {
    // instantiate the bigquery table service
    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);

    // 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);

    // check if the job is complete
    $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 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为客户端库设置身份验证。

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

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为客户端库设置身份验证。

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

针对外部数据源的架构自动检测

架构自动检测功能可以与 CSV、JSON 和 Google 表格外部数据源搭配使用。启用架构自动检测功能后，BigQuery 将尽力尝试自动从源数据推断出架构。如果您不为这些来源启用架构自动检测功能，则必须提供显式架构。

如果查询外部 Avro、Parquet、ORC、Firestore 导出文件或 Datastore 导出文件，就无需启用架构自动检测功能。这些文件格式是自描述格式，因此 BigQuery 会自动根据源数据推断表架构。对于 Parquet、Avro 和 Orc 文件，您可以选择性地提供显式架构以替换推断的架构。

使用 Google Cloud 控制台，您可以勾选自动检测的架构和输入参数选项来启用架构自动检测功能。

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

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

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

自动检测详述

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

压缩

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

日期和时间值

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 后转换数据。

针对 CSV 数据的架构自动检测

CSV 分隔符

BigQuery 会检测以下分隔符：

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

CSV 标题

BigQuery 通过将文件的第一行与文件中的其他行进行比较来推断出标题。如果第一行只包含字符串，而其他行包含其他数据类型，则 BigQuery 会假设第一行是标题行。BigQuery 会根据标题行中的字段名称分配列名称。您可以根据 BigQuery 中列的命名规则修改名称。例如，空格将替换为下划线。

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

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

CSV 中括起的新行

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

针对 JSON 数据的架构自动检测

JSON 嵌套和重复字段

BigQuery 会推断 JSON 文件中的嵌套和重复字段。如果字段值是 JSON 对象，BigQuery 会以 RECORD 类型加载该列。如果字段值是数组，BigQuery 会将该列作为重复列加载。如需查看包含嵌套和重复数据的 JSON 数据示例，请参阅加载嵌套和重复的 JSON 数据。

字符串转换

如果启用架构自动检测功能，则 BigQuery 会尽可能将字符串转换为布尔值、数字或日期/时间类型。例如，使用以下 JSON 数据，架构自动检测会将 id 字段转换为 INTEGER 列：

{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}

如需了解详情，请参阅从 Cloud Storage 加载 JSON 数据。

针对 Google 表格的架构自动检测

对于 Google 表格，BigQuery 会自动检测第一行是否为标题行，类似于针对 CSV 文件的自动检测。如果第一行被标识为标题，则 BigQuery 会根据标题行中的字段名称分配列名称并跳过该行。您可以根据 BigQuery 中列的命名规则修改名称。例如，空格将替换为下划线。

表安全性

如需控制对 BigQuery 中表的访问权限，请参阅表访问权限控制简介。