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

このページでは、外部データソースに対するテーブル定義ファイルを作成する方法を説明します。外部データソースは、データが BigQuery に保存されていない場合でも直接クエリできるデータソースです。

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

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

テーブル定義ファイルを作成して、次の外部データソースの永続テーブルまたは一時外部テーブルを記述できます。

• Cloud Storage

  • カンマ区切り値（CSV）
  • JSON（改行区切り）
  • Avro ファイル
  • Datastore エクスポート ファイル
  • ORC ファイル
  • Parquet ファイル
  • Firestore エクスポート ファイル

• Google ドライブ

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

• Bigtable

始める前に

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

• データソースがドライブの場合は、ドライブの URI が必要です。
• データソースが Cloud Storage の場合には、Cloud Storage URI が必要です。
• Bigtable データソースには、Bigtable URI が必要です。

CSV ファイル、JSON ファイル、Google スプレッドシート ファイルの定義ファイルを作成する

次のいずれかの方法で、Cloud Storage またはドライブに CSV ファイル、JSON ファイル、Google スプレッドシート ファイルのテーブル定義ファイルを作成します。

• autodetect フラグを使用する
• インライン スキーマを使用する
• JSON スキーマ ファイルを使用する

autodetect フラグを使用する

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

Cloud Storage データソースで自動検出を使用する

Cloud Storage データソースのテーブル定義ファイルを作成します。

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

   bq mkdef \
     --autodetect \
     --source_format=SOURCE_FORMAT \
     "URI" > /tmp/FILE_NAME
   

   次のように置き換えます。

   • SOURCE_FORMAT: 使用するファイル形式
   • FILE_NAME: 使用するテーブル定義ファイルの名前

   • URI: Cloud Storage URI

     例: gs://mybucket/myfile

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": [
     "URI"
   ]
   }
   

3. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

ドライブのデータソースで自動検出を使用する

ドライブのデータソース用のテーブル定義ファイルを作成します。

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

   bq mkdef \
      --autodetect \
      --source_format=SOURCE_FORMAT \
      "URI" > /tmp/FILE_NAME
   

   次のように置き換えます。

   • SOURCE_FORMAT: 使用するファイル形式
   • FILE_NAME: 使用するテーブル定義ファイルの名前

   • URI: ドライブの URI

     例: https://drive.google.com/open?id=123ABCD123AbcD123Abcd

2. テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド nano /tmp/file_name を実行して、ファイルを nano で開きます。Google スプレッドシートの外部データソースの場合、ファイルは次のようになります。"autodetect" は true に設定されています。

   {
   "autodetect": true,
   "sourceFormat": "GOOGLE_SHEETS",
   "sourceUris": [
     "URI"
   ]
   }
   

3. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの全般設定を、変更、追加、または削除します。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 またはドライブのデータソースでインライン スキーマを使用する

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

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

   bq mkdef \
     --noautodetect \
     --source_format=SOURCE_FORMAT \
     "URI" \
     FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME
   

   次のように置き換えます

   • SOURCE_FORMAT: ソースファイルの形式

   • URI: Cloud Storage URI またはドライブの URI

     たとえば、Cloud Storage の場合は gs://mybucket/myfile、ドライブの場合は https://drive.google.com/open?id=123ABCD123AbcD123Abcd です。

   • FIELD:DATA_TYPE,FIELD:DATA_TYPE: スキーマ定義

     例: Name:STRING,Address:STRING, ...

   • 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. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

JSON スキーマ ファイルを使用する

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

Cloud Storage データソースでスキーマ ファイルを使用する

JSON スキーマ ファイルを使用して、Cloud Storage データソースのテーブル定義を作成します。

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

   bq mkdef \
      --noautodetect \
      --source_format=SOURCE_FORMAT \
      "URI" \
     PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME
   

   次のように置き換えます。

   • SOURCE_FORMAT: 使用するファイル形式
   • FILE_NAME: 使用するテーブル定義ファイルの名前

   • URI: Cloud Storage URI

     例: gs://mybucket/myfile

   • PATH_TO_SCHEMA_FILE: ローカルマシン上の JSON スキーマ ファイルの場所

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. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

ドライブのデータソースでスキーマ ファイルを使用する

JSON スキーマ ファイルを使ってドライブ データソースのテーブル定義を作成するには、次の手順を行います。

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

   bq mkdef \
      --noautodetect \
      --source_format=source_format \
      "URI" \
      PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME
   

   次のように置き換えます。

   • SOURCE_FORMAT: ソースファイルの形式

   • URI: ドライブの URI

     例: https://drive.google.com/open?id=123ABCD123AbcD123Abcd

   • PATH_TO_SCHEMA_FILE: ローカルマシン上の JSON スキーマ ファイルの場所

   • 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": [
     "URI"
   ]
   }
   

3. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

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

自己記述型形式の定義ファイルを作成する

Avro、Parquet、ORC は自己記述型形式です。これらの形式のデータファイルには独自のスキーマ情報が含まれます。これらの形式のいずれかを外部データソースとして使用する場合、BigQuery はソースデータを使用してスキーマを自動的に取得します。テーブル定義を作成する際に、スキーマの自動検出を使用する必要はなく、インライン スキーマ定義やスキーマ ファイルを指定する必要もありません。

Avro、Parquet、ORC のテーブル定義ファイルは Cloud Storage またはドライブに保存できます。

1. bq mkdef コマンドを使用してテーブル定義を作成します。

   bq mkdef \
       --source_format=FORMAT \
       "URI" > FILE_NAME
   

   次のように置き換えます。

   • FORMAT: ソース形式

   • URI: Cloud Storage URI またはドライブの URI

     たとえば、Cloud Storage の場合は gs://mybucket/myfile、ドライブの場合は https://drive.google.com/open?id=123ABCD123AbcD123Abcd です。

   • FILE_NAME: 使用するテーブル定義ファイルの名前

2. （省略可）テキスト エディタでテーブル定義ファイルを開きます。ファイルは次のようになります。

   {
      "sourceFormat": "AVRO",
      "sourceUris": [
      "URI"
       ]
   }
   

3. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの全般設定を変更、追加、または削除します。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

Hive パーティション分割データの定義ファイルを作成する

この bq mkdef コマンドに hive_partitioning_mode フラグおよび hive_partitioning_source_uri_prefix フラグを指定して実行し、Cloud Storage、Amazon Simple Storage Service（Amazon S3）、Azure Blob Storage のいずれかに保存される Hive パーティション分割データの定義ファイルを作成します。

Datastore と Firestore の定義ファイルを作成する

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

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

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

   bq mkdef \
   --source_format=DATASTORE_BACKUP \
   "URI" > /tmp/FILE_NAME
   

   次のように置き換えます。

   • URI: Cloud Storage URI
   • FILE_NAME: 使用するテーブル定義ファイルの名前

   DATASTORE_BACKUP ソース形式は、Datastore と Firestore の両方で使用されます。

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

   {
   "sourceFormat": "DATASTORE_BACKUP",
   "sourceUris": [
     "gs://URI"
   ]
   }
   

3. （省略可）テーブル定義ファイルを手動で編集して、maxBadRecords や ignoreUnknownValues などの設定を、変更、追加、または削除します。Datastore と Firestore のエクスポート ファイルに固有の構成設定はありません。詳しくは、API リファレンスの ExternalDataConfiguration をご覧ください。

Bigtable の定義ファイルを作成する

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

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

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

次のように置き換えます。

• PROJECT_ID: Bigtable クラスタを含むプロジェクト
• INSTANCE_ID: Bigtable インスタンス ID
• TABLE_NAME: クエリ対象のテーブルの名前
• FAMILY_ID: 列ファミリー ID

詳しくは、Cloud Bigtable URI の取得をご覧ください。

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

データが複数のファイルに分かれている場合は、アスタリスク（*）ワイルドカードを使用して複数のファイルを選択できます。ワイルドカードとしてアスタリスクを使用する場合は、次のルールに従う必要があります。

• アスタリスクは、オブジェクト名の中または末尾に使用できます。
• 複数のアスタリスクは使用できません。たとえば、パス gs://mybucket/fed-*/temp/*.csv は無効です。
• バケット名にはアスタリスクを使用できません。

例:

• 次の例では、gs://mybucket/fed-samples/fed-sample で始まるすべてのフォルダ内のすべてのファイルを選択する方法を示します。

  gs://mybucket/fed-samples/fed-sample*
  

• 次の例では、fed-samples というフォルダと fed-samples のサブフォルダにある .csv という拡張子のファイルのみを選択する方法を示します。

  gs://mybucket/fed-samples/*.csv
  

• 次の例では、fed-samples という名前のフォルダで fed-sample*.csv という命名パターンのファイルを選択する方法を示します。この例では、fed-samples のサブフォルダ内のファイルは選択されません。

  gs://mybucket/fed-samples/fed-sample*.csv
  

一部のプラットフォームでは、bp コマンドライン ツールの使用時に、アスタリスクをエスケープしなければならない場合があります。

アスタリスク ワイルドカードを使用する場合は、バケットとファイル名を引用符で囲みます。たとえば、fed-sample000001.csv と fed-sample000002.csv という 2 つのファイルがあり、アスタリスクを使用して両方を選択する場合、バケット URI は "gs://mybucket/fed-sample*" になります。

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

• Bigtable。Bigtable データの場合、指定できるデータソースは 1 つのみです。URI 値は、Bigtable テーブルに有効な HTTPS URL である必要があります。
• Datastore または Firestore。Cloud Storage に保存されている Datastore または Firestore のエクスポート。Datastore のバックアップの場合、指定できるデータソースは 1 つのみです。URI 値は、末尾が .backup_info か .export_metadata である必要があります。
• ドライブ。データはドライブに保存されています。

次のステップ

• Cloud Storage データにクエリを実行する方法を確認する。
• ドライブデータにクエリを実行する方法を確認する。
• Bigtable データにクエリを実行する方法を確認する。