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

スキーマの自動検出

スキーマの自動検出は、BigQuery にデータを読み込む場合や、外部データソースに対してクエリを行う場合に使用できます。

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

テーブルで検出されたスキーマを確認するには:

  • コマンドライン ツールの bq show コマンドを使用します。
  • Cloud Console または従来のウェブ UI を使用して、テーブルのスキーマを表示します。

BigQuery がスキーマを検出するときに、BigQuery SQL 構文に対応させるためにフィールド名が変更されることがまれにあります。

データ型変換について詳しくは、以下をご覧ください。

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

データの読み込み時にスキーマの自動検出を有効にするには:

  • Cloud Console: [スキーマ] セクションの [自動検出] で、[スキーマと入力パラメータ] オプションをオンにします。
  • 従来の BigQuery ウェブ UI: [Schema] セクションで、[Automatically detect] オプションをオンにします。
  • CLI: --autodetect パラメータを指定して、bq load コマンドを使用します。

有効にすると、BigQuery は CSV と JSON ファイルのスキーマを可能な限り自動的に推測します。

Avro、Parquet、ORC の各ファイル、Firestore と Datastore のエクスポート ファイルでは、スキーマの自動検出は使用されません。この種のファイルを BigQuery に読み込むと、自己記述型ソースデータから自動的にテーブル スキーマが取得されます。

JSON や CSV データを読み込むときにスキーマの自動検出を使用するには:

Console

  1. Cloud Console で BigQuery ウェブ UI を開きます。
    Cloud Console に移動

  2. ナビゲーション パネルの [リソース] セクションで、データセットを選択します。

  3. ウィンドウの右側にある [テーブルを作成] をクリックします。

    テーブルの作成

  4. [テーブルの作成] ページの [ソース] セクションで、次の操作を行います。

    • [テーブルの作成元] で、目的のソースタイプを選択します。
    • ソース フィールドで、ファイルまたは Cloud Storage バケットを選択するか、Cloud Storage URI を入力します。BigQuery ウェブ UI では URI の複数指定はできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。

      ファイルを選択

    • ファイル形式として、CSV または JSON を選択します。

  5. [テーブルの作成] ページの [送信先] セクションで、次の操作を行います。

    • [データセット名] で、該当するデータセットを選択します。

      データセットを選択

    • [テーブル名] フィールドに、作成するテーブルの名前を入力します。

    • [テーブルタイプ] が [ネイティブ テーブル] に設定されていることを確認します。

  6. [テーブルを作成] をクリックします。

従来の UI

  1. BigQuery ウェブ UI に移動します。
    BigQuery ウェブ UI に移動

  2. ナビゲーション内のデータセット名の横にある下矢印アイコン 下矢印アイコン をクリックし、[Create new table] をクリックします。

    注: この UI では、データを読み込むプロセスは、テーブルを作成するプロセスと同じです。
  3. [Create table] ページで、次の操作を行います。

    • [Source Data] で、[Create from source] をクリックします。
    • [Destination Table] で、データセットを選択し、[Destination table name] フィールドにテーブル名を入力します。
    • [Schema] で、[Automatically detect] をクリックしてスキーマを特定します。

      自動検出リンク

    • [Create Table] をクリックします。

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 を、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
    }
    

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
    # 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 のリファレンス ドキュメントをご覧ください。

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 ウェブ UI で、[Automatically detect] オプションをオンにします。

有効にすると、BigQuery は CSV と JSON の外部データソースのスキーマを可能な限り自動的に推測します。

現在のところ、Cloud Console または従来のウェブ UI を使用して、Google スプレッドシートの外部データソースのスキーマ自動検出を有効にすることはできません。また、外部 Avro ファイル、Firestore エクスポート ファイル、Datastore エクスポート ファイルでは、スキーマの自動検出は使用されません。この種のファイル形式のいずれかにリンクするテーブルを作成すると、BigQuery は自己記述型ソースデータからスキーマを自動的に取得します。

CLI の場合、CSV、JSON、Google スプレッドシート データにテーブル定義ファイルを作成するときに、スキーマの自動検出を有効にできます。CLI を使用してテーブル定義ファイルを作成するときに、スキーマの自動検出を有効にするには --autodetect フラグを、自動検出を無効にするには --noautodetect フラグを mkdef コマンドに渡します。

--autodetect フラグを使用する場合は、テーブル定義ファイルで autodetecttrue に設定します。--noautodetect フラグを使用する場合は、"autodetect"false に設定します。テーブル定義の作成時に外部データソースのスキーマを定義せず、--noautodetect または --autodetect フラグを使用しない場合は、"autodetect" をデフォルトの true に設定します。

API を使用してテーブル定義ファイルを作成する場合は、autodetect プロパティの値を true または false に設定します。autodetecttrue に設定すると、自動検出が有効になります。autodetectfalse に設定すると、無効になります。

自動検出の詳細

自動検出では、スキーマの詳細が検出されるだけでなく、次のものも認識されます。

圧縮

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
  • 2017-07-05 12:54:00 UTC
  • 2017-08-19 07:11:35.220 -05:00
  • 2017-07-05 12:54:00 UTC
  • 2017-08-19T12:11:35.220Z
  • 2.53402300799e11
  • 2018-08-19 12:11:35.220000
  • 2018-08-19 12:11:35.220