外部データソース用のテーブル定義ファイルを作成する
このページでは、外部データソースに対するテーブル定義ファイルを作成する方法を説明します。外部データソースは、データが 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
フラグを使用する
インラインのスキーマ説明またはスキーマ ファイルを使用せずに CSV、JSON、または Google スプレッドシート ファイルを指定する場合は、テーブル定義ファイル内で --autodetect
フラグを使用して "autodetect"
オプションを true
に設定します。自動検出を有効にすると、BigQuery はスキーマを可能な限り自動的に推測します。詳細については、外部データソースのスキーマ自動検出をご覧ください。
Cloud Storage データソースで自動検出を使用する
Cloud Storage データソースのテーブル定義ファイルを作成します。
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
(省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド
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" ] }
(省略可)テーブル定義ファイルを手動で編集して、
maxBadRecords
やignoreUnknownValues
などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスのExternalDataConfiguration
をご覧ください。
ドライブのデータソースで自動検出を使用する
ドライブのデータソース用のテーブル定義ファイルを作成します。
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
テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド
nano /tmp/file_name
を実行して、ファイルを nano で開きます。Google スプレッドシートの外部データソースの場合、ファイルは次のようになります。"autodetect"
はtrue
に設定されています。{ "autodetect": true, "sourceFormat": "GOOGLE_SHEETS", "sourceUris": [ "URI" ] }
(省略可)テーブル定義ファイルを手動で編集して、
maxBadRecords
やignoreUnknownValues
などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスのExternalDataConfiguration
をご覧ください。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 またはドライブのデータソースのテーブル定義を作成するには、次の手順を行います。
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
: 使用するテーブル定義ファイルの名前
(省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド
nano /tmp/file_name
を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。ここで、"autodetect"
は有効になっていません。スキーマ情報はテーブル定義ファイルに書き込まれます。{ "schema": { "fields": [ { "name": "FIELD", "type": "DATA_TYPE" }, { "name": "FIELD", "type": "DATA_TYPE" } ... ] }, "sourceFormat": "NEWLINE_DELIMITED_JSON", "sourceUris": [ "URI" ] }
(省略可)テーブル定義ファイルを手動で編集して、
maxBadRecords
やignoreUnknownValues
などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスのExternalDataConfiguration
をご覧ください。
JSON スキーマ ファイルを使用する
自動検出やインライン スキーマ定義を使用しない場合は、JSON スキーマ ファイルを作成し、テーブル定義ファイルの作成時にこのファイルを参照することもできます。ローカルマシンに JSON スキーマ ファイルを手動で作成します。Cloud Storage またはドライブに保存されている JSON スキーマ ファイルへの参照はサポートされていません。
Cloud Storage データソースでスキーマ ファイルを使用する
JSON スキーマ ファイルを使用して、Cloud Storage データソースのテーブル定義を作成します。
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 スキーマ ファイルの場所
(省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド
nano /tmp/file_name
を実行して、ファイルを nano で開きます。
ファイルを開くと、下のように表示されます。ここで、"autodetect"
は有効になっていません。スキーマ情報はテーブル定義ファイルに書き込まれます。{ "schema": { "fields": [ { "name": "FIELD", "type": "DATA_TYPE" }, { "name": "FIELD", "type": "DATA_TYPE" } ... ] }, "sourceFormat": "NEWLINE_DELIMITED_JSON", "sourceUris": [ "URI" ] }
(省略可)テーブル定義ファイルを手動で編集して、
maxBadRecords
やignoreUnknownValues
などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスのExternalDataConfiguration
をご覧ください。
ドライブのデータソースでスキーマ ファイルを使用する
JSON スキーマ ファイルを使ってドライブ データソースのテーブル定義を作成するには、次の手順を行います。
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
: 使用するテーブル定義ファイルの名前
テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド
nano /tmp/file_name
を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。ここで、"autodetect"
は有効になっていません。スキーマ情報はテーブル定義ファイルに書き込まれます。{ "schema": { "fields": [ { "name": "FIELD", "type": "DATA_TYPE" }, { "name": "FIELD", "type": "DATA_TYPE" } ... ] }, "sourceFormat": "GOOGLE_SHEETS", "sourceUris": [ "URI" ] }
(省略可)テーブル定義ファイルを手動で編集して、
maxBadRecords
やignoreUnknownValues
などの全般設定を、変更、追加、または削除します。JSON ソースファイルに固有の構成設定はありませんが、CSV ファイルと Google スプレッドシート ファイルに適用される設定はあります。詳しくは、API リファレンスのExternalDataConfiguration
をご覧ください。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 またはドライブに保存できます。
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
: 使用するテーブル定義ファイルの名前
(省略可)テキスト エディタでテーブル定義ファイルを開きます。ファイルは次のようになります。
{ "sourceFormat": "AVRO", "sourceUris": [ "URI" ] }
(省略可)テーブル定義ファイルを手動で編集して、
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 のエクスポート データについて、テーブル定義ファイルを作成できます。
bq mkdef
コマンドを使用してテーブル定義を作成します。Datastore または Firestore のバックアップ ファイルに、--noautodetect
フラグを使用する必要はありません。これらのファイルタイプでは、スキーマの自動検出が無効になります。mkdef
コマンドでは、テーブル定義ファイルが JSON 形式で生成されます。次の例では、テーブル定義を作成し、出力をファイル/tmp/file_name
に書き込んでいます。bq mkdef \ --source_format=DATASTORE_BACKUP \ "URI" > /tmp/FILE_NAME
次のように置き換えます。
URI
: Cloud Storage URIFILE_NAME
: 使用するテーブル定義ファイルの名前
DATASTORE_BACKUP
ソース形式は、Datastore と Firestore の両方で使用されます。(省略可)テキスト エディタでテーブル定義ファイルを開きます。たとえば、コマンド
nano /tmp/file_name
を実行して、ファイルを nano で開きます。ファイルを開くと、下のように表示されます。"autodetect"
の設定は不要です。{ "sourceFormat": "DATASTORE_BACKUP", "sourceUris": [ "gs://URI" ] }
(省略可)テーブル定義ファイルを手動で編集して、
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 インスタンス IDTABLE_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 データにクエリを実行する方法を確認する。