スキーマの自動検出の使用

スキーマの自動検出

スキーマの自動検出により、BigQuery は CSV、JSON、Google スプレッドシートのデータのスキーマを推測できます。スキーマの自動検出は、BigQuery にデータを読み込む場合や、外部データソースに対してクエリを行う場合に使用できます。

自動検出を有効にすると、BigQuery は各列のデータ型を推測します。BigQuery は、データソース内でランダムにファイルを選択し、データの最初の最大 500 行をスキャンして、代表的なサンプルとして使用します。BigQuery は、各フィールドを検証し、そのサンプル内の値に基づいてそのフィールドにデータ型を割り当てようとします。

CSV、JSON、Google スプレッドシートのデータに対してスキーマの自動検出を有効にしない場合は、テーブルの作成時に手動でスキーマを指定する必要があります。

Avro、Parquet、ORC、Firestore エクスポート、Datastore エクスポートのファイルに対しては、スキーマの自動検出を有効にする必要はありません。これらのファイル形式は自己記述型であるため、BigQuery はソースデータからテーブル スキーマを自動的に推測します。Parquet、Avro、Orc ファイルの場合は、必要に応じて明示的なスキーマを指定して、推測されるスキーマをオーバーライドできます。

テーブルに検出されたスキーマは、次の方法で表示できます。

  • Google Cloud コンソールを使用する
  • bq コマンドライン ツールの bq show コマンドを使用する

BigQuery がスキーマを検出する際、GoogleSQL 構文との互換性を確保するためにフィールド名が変更されることがまれにあります。

データ型の変換の詳細については、次をご覧ください。

スキーマ自動検出を使用したデータの読み込み

データの読み込み時にスキーマの自動検出を有効にするには、次のいずれかの方法を使用します。

  • Google Cloud コンソールで、[スキーマ] セクションにおいて、[自動検出] について、[スキーマと入力パラメータ] オプションをオンにします。
  • bq コマンドライン ツールで、--autodetect パラメータを指定して bq load コマンドを実行します。

スキーマの自動検出を有効にすると、BigQuery は CSV と JSON ファイルのスキーマを可能な限り自動的に推測します。自動検出のロジックでは、最初の 500 行までのデータを読み取り、スキーマ フィールドの型を推測します。--skip_leading_rows フラグを指定すると、先頭行はスキップされます。フィールドの型は、フィールドが最も大きい行に基づいて推測されます。すべての列またはフィールドに値が存在するデータ行が 1 つ以上存在していれば、自動検出は想定どおりに機能します。

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 などの一般的な列名を割り当てます。テーブルを作成した後は、スキーマでは列名を更新できませんが、手動で名前を変更することはできます。もう 1 つの方法は、autodetect を使用する代わりに明示的なスキーマを指定することです。

すべてのデータ フィールドが文字列のヘッダー行を含む CSV ファイルがあるとします。この場合、最初の行がヘッダーであることを BigQuery は自動的に検出しません。--skip_leading_rows オプションを使用することで、ヘッダー行をスキップできます。そうしない場合、ヘッダーはデータとしてインポートされます。こうしたケースでは、明示的なスキーマを指定して列名を割り当てることも検討してください。

CSV 引用符付き改行

BigQuery は、CSV フィールド内の引用符付き改行文字を検出し、引用符付き改行文字を行境界として解釈しません。

JSON データのスキーマ自動検出

JSON のネストされたフィールドと繰り返しフィールド

BigQuery は、JSON ファイルのネストされたフィールドと繰り返しフィールドを推測します。フィールド値が JSON オブジェクトの場合、BigQuery は RECORD 型としてその列を読み込みます。フィールド値が配列の場合、BigQuery はこの列を繰り返し列として読み込みます。ネストされたデータと繰り返しデータを含む JSON データの例については、ネストされた JSON データと繰り返し JSON データの読み込みをご覧ください。

文字列変換

スキーマの自動検出を有効にすると、BigQuery は可能であれば文字列をブール値、数値、日時型に変換します。たとえば、次の JSON データを使用すると、スキーマの自動検出によって id フィールドが INTEGER 列に変換されます。

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

詳細については、Cloud Storage からの JSON データの読み込みをご覧ください。

Google スプレッドシートのスキーマの自動検出

スプレッドシートの場合、CSV ファイルの自動検出と同様に、BigQuery は最初の行がヘッダー行かどうかを自動検出します。最初の行がヘッダーとして識別されると、BigQuery はヘッダー行のフィールド名に基づいて列名を割り当て、その行をスキップします。この名前は、BigQuery の列の命名規則を満たすように変更される場合があります。たとえば、スペースはアンダースコアに置き換えられます。

テーブルのセキュリティ

BigQuery でテーブルへのアクセスを制御するには、テーブルのアクセス制御の概要をご覧ください。