批量加载数据

您可以将 Cloud Storage 中的数据或本地文件作为批量操作加载到 BigQuery 中。源数据可以采用以下任一格式:

  • Avro
  • 英文逗号分隔值 (CSV)
  • JSON(以换行符分隔)
  • ORC
  • Parquet
  • 存储在 Cloud Storage 中的 Firestore 导出文件

您还可以使用 BigQuery Data Transfer Service 设置从 Cloud Storage 到 BigQuery 的周期性加载作业。

亲自尝试

如果您是 Google Cloud 新手,请创建一个帐号来评估 BigQuery 在实际场景中的表现。新客户还可获享 $300 赠金,用于运行、测试和部署工作负载。

免费试用 BigQuery

所需权限

在将数据加载到 BigQuery 时,您需要拥有相关权限,才能运行加载作业并将数据加载到新的或现有的 BigQuery 表和分区中。如果要从 Cloud Storage 加载数据,您还需要拥有对您的数据所在的存储分区的访问权限。

BigQuery 权限

至少需具备以下权限,才能将数据加载到 BigQuery。无论您是要将数据加载到新的表或分区,还是要附加到或覆盖现有的表或分区,都必须具备这些权限。

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.jobs.create

以下预定义 IAM 角色同时具有 bigquery.tables.createbigquery.tables.updateData 权限:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下预定义的 IAM 角色包含 bigquery.jobs.create 权限:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限,用户可以通过使用加载作业在数据集中创建和更新表。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制

Cloud Storage 权限

如需从 Cloud Storage 存储分区中加载数据,您必须获得 storage.objects.get 权限。如果要使用 URI 通配符,您还必须具有 storage.objects.list 权限。

授予预定义的 IAM 角色 storage.objectViewer,即可同时提供 storage.objects.getstorage.objects.list 权限。

从 Cloud Storage 加载数据

BigQuery 支持从以下任意一种 Cloud Storage 存储类别加载数据:

  • Standard
  • Nearline
  • Coldline
  • Archive

如需了解如何将数据加载到 BigQuery 中,请参阅数据格式页面:

如需了解如何配置从 Cloud Storage 到 BigQuery 的周期性负载,请参阅 Cloud Storage 转移作业

位置注意事项

在选择数据的位置时,请考虑以下事项:

  • 共置 Cloud Storage 存储分区,以便加载数据。
    • 如果您的 BigQuery 数据集位于多区域位置,则包含您要加载的数据的 Cloud Storage 存储分区必须位于同一位置的区域或多区域存储分区中。例如,如果您的 BigQuery 数据集位于 EU,则 Cloud Storage 存储分区必须位于 EU 的单区域或多区域存储分区中。
    • 如果您的数据集位于单区域位置,则您的 Cloud Storage 存储分区必须是同一位置的单区域存储分区。例如,如果您的数据集位于东京区域,则您的 Cloud Storage 存储分区必须是位于东京的单区域存储分区。
    • 例外情况:如果您的数据集位于 US 多区域位置,则可以从任何区域或多区域位置的 Cloud Storage 存储分区加载数据。
  • 制定数据管理计划。
    • 如果您选择区域存储资源(如 BigQuery 数据集或 Cloud Storage 存储分区),请制定按地理位置管理数据的计划。

如需详细了解 Cloud Storage 位置,请参阅 Cloud Storage 文档中的存储分区位置

您无法在创建数据集后更改其位置,但可以复制数据集或手动移动数据集。如需了解详情,请参阅:

检索 Cloud Storage URI

要从 Cloud Storage 数据源加载数据,您必须提供 Cloud Storage URI。

Cloud Storage URI 包含存储分区名称和对象(文件名)。例如,如果 Cloud Storage 存储分区的名称为 mybucket,且数据文件的名称为 myfile.csv,则存储分区 URI 为 gs://mybucket/myfile.csv。如果数据被分成多个文件,您可以在 URI 中使用通配符。如需了解详情,请参阅 Cloud Storage 请求 URI

BigQuery 不支持数据源 URI 在初始双斜杠之后添加多个连续斜杠。Cloud Storage 对象名称可包含多个连续斜杠(“/”)字符。但是,BigQuery 会将多个连续斜杠转换为单个斜杠。例如,以下数据源 URI 在 Cloud Storage 中有效,但在 BigQuery 中则无效:gs://bucket/my//object//name

如需检索 Cloud Storage URI,请执行以下操作:

  1. 打开 Cloud Storage 控制台

    Cloud Storage 控制台

  2. 浏览到包含源数据的对象(文件)所在的位置。

  3. 在 Cloud Storage 控制台顶部,找到并记下该对象的路径。如需编写 URI,请将 gs://bucket/file 替换为适当的路径,例如 gs://mybucket/myfile.jsonbucket 是 Cloud Storage 存储分区名称,file 是包含数据的对象(文件)的名称。

Cloud Storage URI 的通配符支持

如果您的 Cloud Storage 数据分为共用一个通用基本名称的多个文件,则在加载数据时,您可以在 URI 中使用通配符。

如需在 Cloud Storage URI 中添加通配符,可在基本名称后附加星号 (*)。例如,如果您在名为 fed-samples 的文件夹中有两个名为 fed-sample000001.csvfed-sample000002.csv 的文件,则通配符 URI 为 gs://mybucket/fed-samples/fed-sample*。您可以在 Cloud Console、bq 命令行工具、API 或客户端库中使用此通配符 URI。

再举一例,如果您只想加载 CSV 文件,则可以使用 gs://mybucket/fed-samples/*.csv。此通配符 URI 还包含此路径下与基本名称模式匹配的任何文件夹中的文件;例如 gs://mybucket/fed-samples/temp/temp-file.csv。如需仅匹配特定文件夹中的文件,请使用文件匹配模式,而不是文件夹匹配模式;例如 gs://mybucket/fed-samples/fed-sample*.csv

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

Google Datastore 导出文件只能指定一个 URI,且必须以 .backup_info.export_metadata 结尾。

在执行以下操作时不可使用星号通配符:

  • 创建与 Datastore 或 Firestore 导出文件关联的外部表
  • 从 Cloud Storage 加载 Datastore 或 Firestore 导出数据

限制

将数据从 Cloud Storage 存储分区加载到 BigQuery 时,需要遵循以下限制:

  • 如果数据集的位置设置为 US 以外的值,则单区域或多区域 Cloud Storage 存储分区必须与数据集位于相同的区域。
  • BigQuery 不保证外部数据源的数据一致性。在查询运行的过程中,底层数据的更改可能会导致意外行为。

Cloud Storage 源数据可能还存在其他限制,具体取决于源数据的格式。如需了解详情,请参阅以下内容:

从本地文件加载数据

您可以使用以下方式之一从可读数据源(如本地机器)加载数据:

  • Google Cloud Console
  • bq 命令行工具的 bq load 命令
  • API
  • 客户端库

使用 Cloud Console 或 bq 命令行工具加载数据时,系统会自动创建加载作业。

如需从本地数据源加载数据,请按如下所述操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 在导航面板的资源部分中,展开您的 Google Cloud 项目并选择数据集。

  3. 在窗口右侧的详细信息面板中,点击创建表。加载数据的过程与创建空表的过程相同。

  4. 创建表页面的来源部分中,执行以下操作:

    • 对于基于以下数据创建表,选择上传

      上传表。

    • 选择文件下点击浏览

      浏览文件。

    • 浏览到相应文件,然后点击打开。请注意,本地文件不支持使用通配符和逗号分隔列表。

    • File format 部分,选择 CSVJSON(以换行符分隔)AvroParquetORC

  5. 创建表页面的目标部分中,执行以下操作:

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

      查看数据集。

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

    • 确认表类型设置为原生表

  6. 架构部分中,输入架构定义。

    • 对于 CSV 和 JSON 文件,您可以勾选自动检测选项来启用架构自动检测功能。对于其他支持的文件类型,架构信息在源数据中为自描述形式。

    • 您也可按照以下方式手动输入架构信息:

      • 点击以文本形式修改,并以 JSON 数组格式输入表架构:

      • 使用添加字段手动输入架构。

  7. 高级选项部分中选择适用项。如需了解可用选项,请参阅 CSV 选项JSON 选项

  8. 可选:在高级选项中,选择写入处置方式:

    • 只写入空白表:仅当表为空时才写入数据。
    • 附加到表:将数据附加到表的末尾。这是默认设置。
    • 覆盖表:在写入新数据之前清空表中的所有现有数据。
  9. 点击创建表

bq

使用 bq load 命令,指定 source_format,并添加本地文件的路径。

(可选)提供 --location 标志并将其值设置为您的位置

如果要在非默认项目中加载数据,请按照以下格式将项目 ID 添加到数据集:PROJECT_ID:DATASET

bq --location=LOCATION load \
--source_format=FORMAT \
PROJECT_ID:DATASET.TABLE \
PATH_TO_SOURCE \
SCHEMA

请替换以下内容:

  • LOCATION:您所在的位置。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置位置的默认值。
  • FORMATCSVAVROPARQUETORCNEWLINE_DELIMITED_JSON
  • project_id:您的项目 ID。
  • dataset:现有数据集。
  • table:要向其中加载数据的表的名称。
  • path_to_source:本地文件的路径。
  • schema:有效架构。该架构可以是本地 JSON 文件,也可以在命令中以内嵌形式输入架构。您还可以改用 --autodetect 标志,而无需提供架构定义。

此外,您可以为选项添加标志,以便控制 BigQuery 解析数据的方式。例如,您可以使用 --skip_leading_rows 标志来忽略 CSV 文件中的标题行。如需了解详情,请参阅 CSV 选项JSON 选项

示例:

以下命令将本地以换行符分隔的 JSON 文件 (mydata.json) 加载到默认项目的 mydataset 内名为 mytable 的表中。架构是在名为 myschema.json 的本地架构文件中定义的。

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    ./mydata.json \
    ./myschema.json

以下命令将本地 CSV 文件 (mydata.csv) 加载到 myotherproject 项目的 mydataset 内名为 mytable 的表中。架构采用以下格式以内嵌方式定义:FIELD:DATA_TYPE, FIELD:DATA_TYPE

    bq load \
    --source_format=CSV \
    myotherproject:mydataset.mytable \
    ./mydata.csv \
    qtr:STRING,sales:FLOAT,year:STRING

以下命令将本地 CSV 文件 (mydata.csv) 加载到默认项目的 mydataset 内名为 mytable 的表中。架构是使用架构自动检测功能定义的。

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

C#

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请使用 JobCreationOptions 基类中处理相应格式的更新选项类,而不是使用 UploadCsvOptions


using Google.Cloud.BigQuery.V2;
using System;
using System.IO;

public class BigQueryLoadFromFile
{
    public void LoadFromFile(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id",
        string tableId = "your_table_id",
        string filePath = "path/to/file.csv"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Create job configuration
        var uploadCsvOptions = new UploadCsvOptions()
        {
            SkipLeadingRows = 1,  // Skips the file headers
            Autodetect = true
        };
        using (FileStream stream = File.Open(filePath, FileMode.Open))
        {
            // Create and run job
            // Note that there are methods available for formats other than CSV
            BigQueryJob job = client.UploadCsv(
                datasetId, tableId, null, stream, uploadCsvOptions);
            job.PollUntilCompleted();  // Waits for the job to complete.
            // Display the number of rows uploaded
            BigQueryTable table = client.GetTable(datasetId, tableId);
            Console.WriteLine(
                $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
        }
    }
}

Go

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请将 NewReaderSourceDataFormat 属性设置为相应的格式。

import (
	"context"
	"fmt"
	"os"

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

// importCSVFromFile demonstrates loading data into a BigQuery table using a file on the local filesystem.
func importCSVFromFile(projectID, datasetID, tableID, filename 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()

	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)

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

Java

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请将 FormatOptions 设置为相应的格式。

TableId tableId = TableId.of(datasetName, tableName);
WriteChannelConfiguration writeChannelConfiguration =
    WriteChannelConfiguration.newBuilder(tableId).setFormatOptions(FormatOptions.csv()).build();
// The location must be specified; other fields can be auto-detected.
JobId jobId = JobId.newBuilder().setLocation(location).build();
TableDataWriteChannel writer = bigquery.writer(jobId, writeChannelConfiguration);
// Write data to writer
try (OutputStream stream = Channels.newOutputStream(writer)) {
  Files.copy(csvPath, stream);
}
// Get load job
Job job = writer.getJob();
job = job.waitFor();
LoadStatistics stats = job.getStatistics();
return stats.getOutputRows();

Node.js

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请将 load 函数的 metadata 参数设置为相应的格式。

// Imports the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function loadLocalFile() {
  // Imports a local file into a table.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const filename = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(filename);

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

PHP

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请将 sourceFormat 设置为相应的格式。

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';
// $tableId    = 'The BigQuery table ID';
// $source     = 'The path to the CSV source file to import';

// instantiate the bigquery table service
$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
// create the import job
$loadConfig = $table->load(fopen($source, 'r'))->sourceFormat('CSV');

$job = $table->runJob($loadConfig);
// poll the job until it is complete
$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    printf('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

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请将 LoadJobConfig.source_format 属性设置为相应的格式。

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"

job_config = bigquery.LoadJobConfig(
    source_format=bigquery.SourceFormat.CSV, skip_leading_rows=1, autodetect=True,
)

with open(file_path, "rb") as source_file:
    job = client.load_table_from_file(source_file, table_id, job_config=job_config)

job.result()  # Waits for the job to complete.

table = client.get_table(table_id)  # Make an API request.
print(
    "Loaded {} rows and {} columns to {}".format(
        table.num_rows, len(table.schema), table_id
    )
)

Ruby

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

以下代码演示了如何将本地 CSV 文件加载到新的 BigQuery 表中。如需加载其他格式的本地文件,请将 Table#load_job 方法的 format 参数设置为相应的格式。

require "google/cloud/bigquery"

def load_from_file dataset_id = "your_dataset_id",
                   file_path  = "path/to/file.csv"

  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table_id = "new_table_id"

  # Infer the config.location based on the location of the referenced dataset.
  load_job = dataset.load_job table_id, file_path do |config|
    config.skip_leading = 1
    config.autodetect   = true
  end
  load_job.wait_until_done! # Waits for table load to complete.

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

限制

从本地数据源加载数据会受到以下限制:

  • 从本地数据源加载文件时,不支持使用通配符和英文逗号分隔列表。各文件必须分别加载。
  • 使用 Cloud Console 时,从本地数据源加载的文件不得超过 10 MB。对于超过此大小的文件,请从 Cloud Storage 加载文件。

加载经过压缩和未经压缩的数据

加载压缩数据和未压缩数据时,首选格式都是 Avro 二进制格式。Avro 数据加载速度更快,因为可以并行读取数据,即使数据块已经过压缩也是如此。系统不支持压缩的 Avro 文件,但支持压缩的数据块。请参阅 Avro 压缩

Parquet 二进制格式也是一个不错的选择,因为 Parquet 可高效地逐列进行编码,这通常会改善压缩比并减小文件大小。Parquet 文件还可利用支持并行加载文件的压缩方法。 系统不支持压缩的 Parquet 文件,但支持压缩的数据块。请参阅 Parquet 压缩

ORC 二进制格式的优势与 Parquet 格式相类似。ORC 文件中的数据加载速度很快,因为可以并行读取数据条带。每个数据条带中的行将按顺序加载。为了优化加载时间,建议使用大小约为 256 MB 或更小的数据条带。系统不支持压缩的 ORC 文件,但支持压缩的文件页脚和条带。 请参阅 ORC 压缩

对于其他数据格式(如 CSV 和 JSON),BigQuery 加载未压缩文件的速度要比加载压缩文件快得多,因为可以并行读取未压缩文件。由于未经压缩的文件较大,因此使用这些文件可能会超出带宽限制,并且数据在加载到 BigQuery 之前会产生较高的 Cloud Storage 暂存费用。请注意,无论文件是否已经过压缩,都无法保证行的排序。您需要根据具体用例来权衡这些利弊,这一点很重要。

一般来说,如果带宽有限,建议先使用 gzip 将 CSV 和 JSON 文件压缩,然后再上传到 Cloud Storage。目前,当您将数据加载到 BigQuery 中时,gzip 是 CSV 和 JSON 文件唯一支持的文件压缩类型。如果加载速度对您的应用很重要,并且您有足够的带宽来加载数据,请将文件保留为未压缩状态。

对表执行附加或覆盖操作

您可以通过源文件或附加查询结果,将其他数据加载到表中。如果数据架构与目标表或分区的架构不匹配,您可以在执行附加或覆盖操作时更新架构。

如果您要在附加数据时更新架构,BigQuery 将允许执行以下操作:

  • 添加新字段
  • REQUIRED 字段放宽为 NULLABLE

如果您要覆盖表,则架构始终会被覆盖。在覆盖表时,架构更新不受限制。

在 Cloud Console 中,使用写入偏好设置选项指定从源文件或查询结果加载数据时要执行的操作。bq 命令行工具和 API 包括以下选项:

控制台选项 bq 工具标志 BigQuery API 属性 说明
只写入空白表 WRITE_EMPTY 仅当表为空时才写入数据。
附加到表 --noreplace--replace=false;如果未指定 --replace,则默认为附加 WRITE_APPEND (默认)在表末尾附加数据。
覆盖表 --replace--replace=true WRITE_TRUNCATE 清空表中所有现有数据然后再写入新数据。

配额政策

如需了解批量加载数据的配额政策,请参阅“配额和限制”页面中的加载作业

价格

将数据批量加载到 BigQuery 无需付费。如需了解详情,请参阅 BigQuery 数据提取价格