外部データソースに対するテーブル定義ファイルの作成

このページでは、外部データソースに対するテーブル定義ファイルを作成する方法を説明します。外部データソース(フェデレーション データソースとも呼ばれます)は、データが BigQuery に格納されていない場合でも直接クエリできるデータソースです。

テーブル定義ファイル

テーブル定義ファイルには、外部テーブルのスキーマ定義とメタデータ(テーブルのデータ フォーマットや関連プロパティなど)が記述されています。テーブル定義ファイルを作成するときにスキーマの自動検出を有効にして、外部データソースのスキーマを定義できます。コマンドラインを使用する場合は、スキーマをインラインで指定できます。また、スキーマ定義を含む JSON ファイルを使用することもできます。

テーブル定義ファイルは、bq コマンドライン ツールで使用されます。テーブル定義ファイルのプロパティは、REST API を使用する場合の ExternalDataConfiguration の作成にも適用されます。Cloud Console または従来の BigQuery ウェブ UI を使用して外部テーブルを作成する場合は、テーブル定義ファイルを使用しません。

次の外部データソースにテーブル定義を作成できます。

  • Cloud Storage

    • カンマ区切り値(CSV)
    • JSON(改行区切り)
    • Avro ファイル
    • Datastore エクスポート ファイル
    • ORC ファイル
    • Parquet ファイル
    • Firestore エクスポート ファイル
  • Google ドライブ

    • カンマ区切り値(CSV)
    • JSON(改行区切り)
    • Avro ファイル
    • Google スプレッドシート
  • Cloud Bigtable

始める前に

テーブル定義ファイルを作成するには、データソースの URI が必要です。

外部の永続テーブルと一時テーブル

永続テーブルまたは一時テーブルを使用すると、BigQuery で外部のデータソースに対してクエリを行うことができます。永続テーブルは、データセット内に作成され、外部データソースにリンクされるテーブルです。テーブルは永続的であるため、アクセス制御を行い、基礎となる外部データソースにアクセスできる他のユーザーとテーブルを共有できます。テーブルに対するクエリはいつでも実行できます。

一時テーブルを使用して外部データソースに対してクエリを実行する場合には、クエリを含むコマンドを送信し、外部データソースにリンクする一時テーブルを作成します。一時テーブルを使用する場合、BigQuery データセット内にはテーブルを作成しません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。一時テーブルを使用して外部データソースにクエリを実行する方法は、外部データに 1 回限りのアドホック クエリを実行する場合、あるいは抽出、変換、読み込み(ETL)プロセスを行う場合に便利です。

テーブル定義ファイルには、外部の永続テーブルまたは一時テーブルを記述できます。

スキーマの自動検出を使用してテーブル定義を作成する

インラインのスキーマ説明またはスキーマ ファイルを使用せずに CSV、JSON、または Google スプレッドシート ファイルを指定する場合は、テーブル定義ファイル内で --autodetect フラグを使用して "autodetect" オプションを true に設定します。自動検出を有効にすると、BigQuery はスキーマを可能な限り自動的に推測します。詳細については、BigQuery でのスキーマの自動検出をご覧ください。

スキーマの自動検出は、次のテーブル定義を作成する場合に使用できます。

  • Cloud Storage または Google ドライブに保存された JSON ファイル
  • Cloud Storage または Google ドライブに保存された CSV ファイル
  • Google ドライブに保存された Google スプレッドシート ファイル

Cloud Storage

bq コマンドライン ツールを使用して Cloud Storage データソースのテーブル定義を作成するには、次の手順を行います。

  1. bq ツールの mkdef コマンドを --autodetect フラグを付けて使用し、テーブル定義を作成します。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • source_format をファイル形式 NEWLINE_DELIMITED_JSONCSV、または GOOGLE_SHEETS に置き換えます。
    • file_name を、テーブル定義ファイルの名前に置き換えます。
    • bucket_uriCloud Storage の URI(たとえば、gs://mybucket/myfile)に置き換えます。
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "bucket_uri" > /tmp/file_name
    
  2. (省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。CSV 外部データソースの場合、ファイルは次のようになります。"autodetect"true に設定されています。

    {
    "autodetect": true,
    "csvOptions": {
      "allowJaggedRows": false,
      "allowQuotedNewlines": false,
      "encoding": "UTF-8",
      "fieldDelimiter": ",",
      "quote": "\"",
      "skipLeadingRows": 0
    },
    "sourceFormat": "CSV",
    "sourceUris": [
      "bucket_uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

外部パーティション分割データ

外部パーティション分割データのテーブル定義ファイルを Cloud Storage 上に構成する方法については、外部パーティション分割データのクエリをご覧ください。

Google ドライブ

bq コマンドライン ツールを使用して Google ドライブのデータソースのテーブル定義を作成するには:

  1. bq ツールの mkdef コマンドを --autodetect フラグを付けて使用し、テーブル定義を作成します。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • source_format をファイル形式 NEWLINE_DELIMITED_JSONCSV、または GOOGLE_SHEETS に置き換えます。
    • file_name を、テーブル定義ファイルの名前に置き換えます。
    • drive_uri を該当する Google ドライブの URI(たとえば、https://drive.google.com/open?id=123ABCD123AbcD123Abcd)に置き換えます。
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "drive_uri" > /tmp/file_name
    
  2. テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。Google スプレッドシートの外部データソースの場合、ファイルは次のようになります。"autodetect"true に設定されています。

    {
    "autodetect": true,
    "sourceFormat": "GOOGLE_SHEETS",
    "sourceUris": [
      "drive_uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

  4. Google スプレッドシートのファイルで特定のシートまたはセル範囲を指定するには、テーブル定義ファイルに range プロパティを追加します。特定のシートをクエリするには、シート名を指定します。セル範囲をクエリするには、sheet_name!top_left_cell_id:bottom_right_cell_id という形式で範囲を指定します("Sheet1!A1:B20" など)。range パラメータが指定されていない場合、ファイルの最初のシートが使用されます。

インライン スキーマを使用してテーブル定義を作成する

自動検出を使用しない場合は、インライン スキーマ定義を使用してテーブル定義ファイルを作成できます。インライン スキーマ定義を提供するには、コマンドラインでフィールドとデータ型を field:data_type,field:data_type の形式でリストします。

以下のテーブル定義ファイルを作成するときに、インライン スキーマ定義を使用できます。

  • Cloud Storage または Google ドライブに保存された JSON ファイル
  • Cloud Storage または Google ドライブに保存された CSV ファイル
  • Google ドライブに保存された Google スプレッドシート ファイル

インライン スキーマ定義を使用して bq コマンドライン ツールを使用して、Cloud Storage データソースのテーブル定義を作成するには:

  1. bq ツールの mkdef コマンドを --noautodetect フラグを付けて使用し、テーブル定義を作成します。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • source_format をファイル形式 NEWLINE_DELIMITED_JSONCSV、または GOOGLE_SHEETS に置き換えます。
    • uriCloud Storage の URI または Google ドライブの URI に置き換えます。たとえば、Google ドライブの場合は https://drive.google.com/open?id=123ABCD123AbcD123Abcd、Cloud Storage の場合は gs://mybucket/myfile のようになります。
    • field:data_type,field:data_type をスキーマ定義に置き換えます。たとえば、Name:STRING,Address:STRING, ... のようになります。
    • file_name を、テーブル定義ファイルの名前に置き換えます。
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "uri" \
    field:data_type,field:data_type > /tmp/file_name
    
  2. (省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。ここで、"autodetect" は有効になっていません。スキーマ情報はテーブル定義ファイルに書き込まれます。

    {
    "schema": {
      "fields": [
        {
          "name": "field",
          "type": "data_type"
        },
        {
          "name": "field",
          "type": "data_type"
        }
        ...
      ]
    },
    "sourceFormat": "NEWLINE_DELIMITED_JSON",
    "sourceUris": [
      "uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

JSON スキーマ ファイルを使用してテーブル定義を作成する

自動検出やインライン スキーマ定義を使用しない場合は、JSON スキーマ ファイルを作成し、テーブル定義ファイルの作成時にこのファイルを参照することもできます。JSON スキーマ ファイルは手動で作成し、ローカルマシンに保存する必要があります。Cloud Storage または Google ドライブに保存されている JSON スキーマ ファイルへの参照はサポートされていません。

JSON スキーマ ファイルは、次のテーブル定義を作成するときに使用できます。

  • Cloud Storage または Google ドライブに保存された JSON ファイル
  • Cloud Storage または Google ドライブに保存された CSV ファイル
  • Google ドライブに保存された Google スプレッドシート ファイル

Cloud Storage

JSON スキーマ ファイルを使用して Cloud Storage データソースのテーブル定義を bq コマンドライン ツールを使用して作成するには:

  1. bq ツールの mkdef コマンドを --noautodetect フラグを付けて使用し、テーブル定義を作成します。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • source_format をファイル形式 NEWLINE_DELIMITED_JSONCSV、または GOOGLE_SHEETS に置き換えます。
    • file_name を、テーブル定義ファイルの名前に置き換えます。
    • bucket_uriCloud Storage の URI(たとえば、gs://mybucket/myfile)に置き換えます。
    • path_to_schema_file をローカルマシン上の JSON スキーマ ファイルの場所に置き換えます。
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "bucket_uri" \
    path_to_schema_file > /tmp/file_name
    
  2. (省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。
    ファイルを開くと、下のように表示されます。ここで、"autodetect" は有効になっていません。スキーマ情報はテーブル定義ファイルに書き込まれます。

    {
    "schema": {
      "fields": [
        {
          "name": "field",
          "type": "data_type"
        },
        {
          "name": "field",
          "type": "data_type"
        }
        ...
      ]
    },
    "sourceFormat": "NEWLINE_DELIMITED_JSON",
    "sourceUris": [
      "bucket_uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

Google ドライブ

JSON スキーマ ファイルを使用して Google ドライブ データソースのテーブル定義を bq コマンドライン ツールを使用して作成するには:

  1. bq ツールの mkdef コマンドを --noautodetect フラグを付けて使用し、テーブル定義を作成します。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • source_format をファイル形式 NEWLINE_DELIMITED_JSONCSV、または GOOGLE_SHEETS に置き換えます。
    • drive_uri を該当する Google ドライブの URI(たとえば、https://drive.google.com/open?id=123ABCD123AbcD123Abcd)に置き換えます。
    • path_to_schema_file をローカルマシン上の JSON スキーマ ファイルの場所に置き換えます。
    • file_name を、テーブル定義ファイルの名前に置き換えます。
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "drive_uri" \
    path_to_schema_file > /tmp/file_name
    
  2. テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。ここで、"autodetect" は有効になっていません。スキーマ情報はテーブル定義ファイルに書き込まれます。

    {
    "schema": {
      "fields": [
        {
          "name": "field",
          "type": "data_type"
        },
        {
          "name": "field",
          "type": "data_type"
        }
        ...
      ]
    },
    "sourceFormat": "GOOGLE_SHEETS",
    "sourceUris": [
      "drive_uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

  4. Google スプレッドシートのファイルで特定のシートまたはセル範囲を指定するには、テーブル定義ファイルに range プロパティを追加します。特定のシートをクエリするには、シート名を指定します。セル範囲をクエリするには、sheet_name!top_left_cell_id:bottom_right_cell_id という形式で範囲を指定します("Sheet1!A1:B20" など)。range パラメータが指定されていない場合、ファイルの最初のシートが使用されます。

Avro テーブル定義を作成する

Avro ファイルを外部データソースとして使用する場合、BigQuery はソースデータを使用してスキーマを自動的に取得します。Avro ファイルのテーブル定義を作成する際に、スキーマの自動検出を使用する必要はなく、インライン スキーマ定義やスキーマ ファイルを指定する必要もありません。

Cloud Storage または Google ドライブに保存された Avro データ用のテーブル定義ファイルを作成できます。

bq コマンドライン ツールを使用して Avro データのテーブル定義ファイルを作成するには:

  1. bq ツールの mkdef コマンドを使用してテーブル定義を作成します。Avro ファイルに --noautodetect フラグを使用する必要はありません。Avro ファイルの場合、スキーマの自動検出は無効になります。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • uriCloud Storage の URI または Google ドライブの URI に置き換えます。たとえば、Google ドライブの場合は https://drive.google.com/open?id=123ABCD123AbcD123Abcd、Cloud Storage の場合は gs://mybucket/myfile のようになります。
    • file_name を、テーブル定義ファイルの名前に置き換えます。
    bq mkdef \
    --source_format=AVRO \
    "uri" > /tmp/file_name
    
  2. (省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。"autodetect" の設定は不要です。

    {
    "sourceFormat": "AVRO",
    "sourceUris": [
      "uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの全般設定を、変更、追加、または削除します。Avro ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

Datastore と Firestore のエクスポート テーブル定義を作成する

Datastore または Firestore のエクスポートを外部データソースとして使用する場合、BigQuery は自己記述型のソースデータを使用して、スキーマを自動的に取得します。Datastore および Firestore のバックアップ ファイルのテーブル定義を作成する際に、スキーマの自動検出を使用する必要はなく、インライン スキーマ定義やスキーマ ファイルを指定する必要もありません。

Cloud Storage に格納された Datastore および Firestore のエクスポート データについて、テーブル定義ファイルを作成できます。

bq コマンドライン ツールを使用して Datastore または Firestore のエクスポート データのテーブル定義ファイルを作成するには:

  1. bq ツールの mkdef コマンドを使用してテーブル定義を作成します。Datastore または Firestore のバックアップ ファイルに、--noautodetect フラグを使用する必要はありません。これらのファイルタイプでは、スキーマの自動検出が無効になります。mkdef コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル /tmp/file_name に書き込んでいます。

    コマンドの要素をそれぞれ次のように置き換えます。

    • bucket_uriCloud Storage の URI に置き換えます。
    • file_name を、テーブル定義ファイルの名前に置き換えます。

    DATASTORE_BACKUP ソース形式は、Datastore と Firestore の両方で使用されることに注意してください。

    bq mkdef \
    --source_format=DATASTORE_BACKUP \
    "uri" > /tmp/file_name
    
  2. (省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。"autodetect" の設定は不要です。

    {
    "sourceFormat": "DATASTORE_BACKUP",
    "sourceUris": [
      "gs://bucket_uri"
    ]
    }
    
  3. (省略可)テーブル定義ファイルを手動で編集して、maxBadRecordsignoreUnknownValues などの設定を、変更、追加、または削除します。Datastore および Firestore のエクスポート ファイルに固有の構成設定はありませんが、CSV ファイルおよび Google スプレッドシート ファイルに適用される設定があります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

Cloud Bigtable テーブル定義を作成する

Cloud Bigtable 用のテーブル定義は、JSON 形式のファイルとして手動で生成する必要があります。Cloud Bigtable データソースの場合、現在、mkdef コマンドでテーブル定義を作成できません。また、スキーマの自動検出もサポートされていません。Cloud Bigtable テーブル定義オプションの一覧については、REST API リファレンスの BigtableOptions をご覧ください。

Cloud Bigtable の JSON テーブル定義ファイルは次のようになります。BigQuery は、このテーブル定義ファイルを使用して単一の列ファミリーからデータを読み取り、値をバイナリでエンコードされた整数として解釈します。

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/project_id/instances/instance_id/tables/table_name"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "family_int",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

テーブル定義ファイルでワイルドカードを使用する

Cloud Storage のデータが、共通のベース名を共有する複数のファイルに分割されている場合は、テーブル定義ファイルの URI でワイルドカードを使用できます。ベース名の後にアスタリスク(*)を付けて、バケットとファイル名を引用符で囲みます。たとえば、fed-sample000001.csvfed-sample000002.csv という 2 つのファイルがある場合、バケット URI は "gs://mybucket/fed-sample*" になります。

バケット内のオブジェクト(ファイル名)について使用できるワイルドカードは 1 つのみです。ワイルドカードは、オブジェクト名の中や末尾に使用できます。バケット名にワイルドカードを付けることはできません。

Cloud Bigtable データの場合、指定できる URI は 1 つだけです。これは Cloud Bigtable テーブルに対する完全かつ有効な HTTPS URL でなければなりません。Datastore のバックアップで指定できる URI は 1 つのみです。また、URI の末尾は .backup_info である必要があります。

次のものにテーブル定義ファイルを作成する場合、* ワイルドカード文字は使用できません。

  • Cloud Bigtable データソース
  • Cloud Storage に保存されている Datastore のエクスポート
  • Cloud Storage に保存されている Firestore のエクスポート
  • Google ドライブに保存されているデータ