Cloud Storage データのクエリ
BigQuery では、次の形式の Cloud Storage データのクエリがサポートされています。
- カンマ区切り値(CSV)
- JSON(改行区切り)
- Avro
- ORC
- Parquet
- Datastore エクスポート
- Firestore エクスポート
BigQuery では、次のストレージ クラスの Cloud Storage データにクエリを実行できます。
- Standard
- Nearline
- Coldline
- アーカイブ
Cloud Storage 外部データソースに対してクエリを実行するには、データの Cloud Storage URI パスを指定し、データソースを参照するテーブルを作成します。Cloud Storage のデータソースの参照に使用するテーブルは、永続テーブルまたは一時テーブルです。
Cloud Storage に保存されているデータに対してクエリを実行する場合は、必ずデータセットと Cloud Storage バケットの場所に関する考慮事項を参照してください。
始める前に
このドキュメントの各タスクを実行するために必要な権限をユーザーに与える Identity and Access Management(IAM)のロールを付与します。タスクの実行に必要な権限(存在する場合)は、タスクの「必要な権限」セクションに記載されています。
Cloud Storage URI の取得
Cloud Storage データソースを使用して外部テーブルを作成するには、Cloud Storage URI を指定する必要があります。
Cloud Storage URI は、バケット名とオブジェクト(ファイル名)で構成されます。たとえば、Cloud Storage バケットの名前が mybucket
でデータファイルの名前が myfile.csv
の場合、バケットの URI は gs://mybucket/myfile.csv
になります。データが複数のファイルに分かれている場合は、URI にワイルドカードを使用できます。詳細については、Cloud Storage のリクエスト URI をご覧ください。
BigQuery は、最初のダブル スラッシュ以降に複数の連続スラッシュが含まれるソース URI をサポートしていません。Cloud Storage では、オブジェクト名に複数の連続スラッシュ("/")文字を含めることができます。一方、BigQuery では、複数の連続スラッシュは単一のスラッシュに変換されます。たとえば、gs://bucket/my//object//name
というソース URI は Cloud Storage では有効ですが、BigQuery では機能しません。
Cloud Storage URI を取得するには:
Cloud Storage Console を開きます。
ソースデータを含むオブジェクト(ファイル)の場所に移動します。
Cloud Storage Console の上部に、オブジェクトのパスが表示されます。URI を作成するには、
gs://bucket/file
を適切なパス(例:gs://mybucket/myfile.json
)に置き換えます。bucket は Cloud Storage バケット名で、file はデータを含むオブジェクト(ファイル)の名前です。
外部の永続テーブルと一時テーブル
永続テーブルまたは一時テーブルを使用すると、BigQuery で外部のデータソースに対してクエリを行うことができます。永続テーブルは、データセット内に作成され、外部データソースにリンクされるテーブルです。テーブルは永続的であるため、アクセス制御を行い、基礎となる外部データソースにアクセスできる他のユーザーとテーブルを共有できます。テーブルに対するクエリはいつでも実行できます。
一時テーブルを使用して外部データソースに対してクエリを実行する場合には、クエリを含むコマンドを送信し、外部データソースにリンクする一時テーブルを作成します。一時テーブルを使用する場合、BigQuery データセット内にはテーブルを作成しません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。一時テーブルを使用して外部データソースに対するクエリ行なう方法は、外部データに 1 回限りのアドホック クエリを実行する場合、あるいは抽出、変換、読み込み(ETL)処理を行う場合に便利です。
外部の永続テーブルを使用して Cloud Storage データをクエリする
必要な権限
永続テーブルを使用して Cloud Storage の外部データに対してクエリを実行するには、次のことを行うための権限が必要です。
- プロジェクト レベル以上でクエリジョブを実行する。
- 外部データを指すテーブルを作成する。
- そのテーブルにアクセスする。
外部データが Cloud Storage に保存されている場合は、データが入っているバケットにアクセスする権限も必要です。
BigQuery で外部テーブルを作成してクエリを実行する権限
BigQuery で外部テーブルを作成してクエリを実行するには、次の IAM 権限が必要です。
bigquery.tables.create
bigquery.tables.getData
bigquery.jobs.create
以下の各 IAM 事前定義ロールには、BigQuery で外部テーブルを作成してクエリを実行するために必要な権限が含まれています。
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(bigquery.jobs.create
権限を含む)roles/bigquery.user
(bigquery.jobs.create
権限を含む)roles/bigquery.jobUser
(bigquery.jobs.create
権限を含む)
また、bigquery.datasets.create
権限がある場合は、作成したデータセットに外部テーブルを作成してアクセスできます。それでも、データを照会するには bigquery.jobs.create
権限が必要です。
BigQuery での IAM のロールと権限については、事前定義ロールと権限をご覧ください。
Cloud Storage バケット内の外部データに対してクエリを実行する権限
Cloud Storage バケット内の外部データに対してクエリを実行するには、次の IAM 権限が必要です。
storage.objects.get
storage.objects.list
(URI ワイルドカードを使用する場合に必要)
IAM 事前定義ロール roles/storage.objectViewer
には、Cloud Storage バケット内の外部データに対してクエリを実行するために必要なすべての権限が含まれています。
Compute Engine インスタンスのアクセス スコープ
Compute Engine インスタンスから、Cloud Storage ソースにリンクされている外部テーブルにクエリを実行する必要がある場合は、インスタンスに少なくとも Cloud Storage の読み取り専用アクセス スコープが必要です(https://www.googleapis.com/auth/devstorage.read_only
)。
このスコープにより、Cloud Storage などの Google Cloud プロダクトに対する Compute Engine インスタンスのアクセスが制御されます。インスタンス上で実行されるアプリケーションは、インスタンスにアタッチしたサービス アカウントを使用して Google Cloud APIs を呼び出します。
デフォルトの Compute Engine サービス アカウントとして実行するように Compute Engine インスタンスを設定すると、インスタンスにはいくつかのデフォルトのスコープがデフォルトで付与され、https://www.googleapis.com/auth/devstorage.read_only
スコープを含みます。
代わりにカスタム サービス アカウントでインスタンスを設定する場合は、https://www.googleapis.com/auth/devstorage.read_only
スコープをインスタンスに明示的に付与してください。
Compute Engine インスタンスへのスコープの適用方法については、インスタンスのサービス アカウントとアクセス スコープを変更するをご覧ください。Compute Engine サービス アカウントの詳細については、サービス アカウントをご覧ください。
外部の永続テーブルを作成してクエリを実行する
外部のデータソースにリンクされた永続テーブルは、次の方法で作成します。
- Cloud コンソールの使用
bq mk
コマンドの使用tables.insert
API メソッドを使用する際にExternalDataConfiguration
を作成するCREATE EXTERNAL TABLE
データ定義言語(DDL)ステートメントを実行する- クライアント ライブラリを使用する
永続テーブルを使用して外部データソースに対してクエリを実行するには、BigQuery データセットに外部データソースにリンクするテーブルを作成します。データは BigQuery テーブルに保存されません。テーブルは永続的であるため、アクセス制御を行い、基礎となる外部データソースにアクセスできる他のユーザーとテーブルを共有できます。
永続外部テーブルを作成する場合は、次の方法でスキーマを指定できます。
- 明示的なテーブル スキーマを指定します。
- スキーマの自動検出を使用します。
外部テーブルを作成するには:
Console
- Cloud コンソールで、[BigQuery] ページを開きます。
[エクスプローラ] パネルでプロジェクトを開いて、データセットを選択します。
アクション オプションを開いて、[テーブルを作成] をクリックします。
[テーブルの作成] ページの [ソース] セクションで、次の操作を行います。
[テーブルの作成元] で [Google Cloud Storage] を選択します。
[GCS バケットからファイルを選択] フィールドで、ファイルまたは Cloud Storage バケットを参照するか、「Cloud Storage URI」を入力します。Cloud コンソールで複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。
[ファイル形式] でデータの形式を選択します。
[テーブルの作成] ページの [送信先] セクションで、次の操作を行います。
- [データセット] で、該当するデータセットを選択します。
- [テーブルタイプ] が [外部テーブル] に設定されていることを確認します。
- [テーブル名] フィールドに、BigQuery で作成するテーブルの名前を入力します。
[スキーマ] セクションで、スキーマの自動検出を有効にするか、スキーマを手動で指定できます。
スキーマの自動検出を有効にするには、[自動検出] オプションを選択します。
手動でスキーマを指定するには、[自動検出] オプションを選択せずに、次のいずれかを行います。
- [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。
[テーブルを作成] をクリックします。
永続テーブルが作成されると、ネイティブの BigQuery テーブルの場合と同じようにクエリを実行できます。クエリの完了後は、結果を CSV または JSON としてエクスポート、テーブルとして保存、または Google スプレッドシートに保存できます。
SQL
永続外部テーブルを作成するには、CREATE EXTERNAL TABLE
DDL ステートメントを実行します。スキーマは明示的に指定できます。スキーマを指定しない場合、BigQuery はスキーマの自動検出を使用して、外部データからスキーマを推測します。
次の例では、スキーマの自動検出を使用して、Cloud Storage に格納された CSV ファイルにリンクする sales
という名前の外部テーブルを作成しています。
Cloud Console で、[BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
CREATE OR REPLACE EXTERNAL TABLE mydataset.sales OPTIONS ( format = 'CSV', uris = ['gs://mybucket/sales.csv']);
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリの実行をご覧ください。
次の例では、スキーマを明示的に指定し、CSV ファイルの最初の行をスキップしています。
CREATE OR REPLACE EXTERNAL TABLE mydataset.sales ( Region STRING, Quarter STRING, Total_Sales INT64 ) OPTIONS ( format = 'CSV', uris = ['gs://mybucket/sales.csv'], skip_leading_rows = 1);
bq
外部テーブルを作成するには、--external_table_definition
フラグを指定して bq mk
コマンドを使用します。このフラグには、テーブル定義ファイルへのパスまたはインライン テーブル定義が含まれます。
オプション 1: テーブル定義ファイル
bq mkdef
コマンドを使用してテーブル定義ファイルを作成し、次のようにファイルパスを bq mk
コマンドに渡します。
bq mkdef --source_format=SOURCE_FORMAT \ BUCKET_URL > DEFINITION_FILE bq mk --table \ --external_table_definition=DEFINITION_FILE \ DATASET_NAME.TABLE_NAME \ SCHEMA
ここで
- SOURCE_FORMAT は、外部データソースの形式です(例:
CSV
)。 - BUCKET_URI は、使用する Cloud Storage URI です。
- DEFINITION_FILE は、ローカルマシン上のテーブル定義ファイルのパスです。
- DATASET_NAME は、テーブルを含むデータセットの名前です。
- TABLE_NAME は、作成するテーブルの名前です。
- SCHEMA は、JSON スキーマ ファイルのパスを指定するか、
field:data_type,field:data_type,...
の形式でスキーマを指定します。
例:
bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
スキーマの自動検出を使用するには、mkdef
コマンドで --autodetect=true
フラグを設定し、スキーマを省略します。
bq mkdef --source_format=CSV --autodetect=true \
gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
mydataset.mytable
オプション 2: インライン テーブルの定義
テーブル定義ファイルを作成する代わりに、テーブル定義を bq mk
コマンドに直接渡します。
bq mk --table \ --external_table_definition=@SOURCE_FORMAT=BUCKET_URI \ DATASET_NAME.TABLE_NAME \ SCHEMA
ここで
- SOURCE_FORMAT は、外部データソースの形式です(例:
CSV
)。 - BUCKET_URI は、使用する Cloud Storage URI です。
- DATASET_NAME は、テーブルを含むデータセットの名前です。
- TABLE_NAME は、作成するテーブルの名前です。
- SCHEMA は、JSON スキーマ ファイルのパスを指定するか、
field:data_type,field:data_type,...
の形式でスキーマを指定します。スキーマの自動検出を使用するには、この引数を省略します。
例:
bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
API
tables.insert
API メソッドを使用する際に ExternalDataConfiguration
を作成します。schema
プロパティを指定するか autodetect
プロパティを true
に設定して、サポートされているデータソースのスキーマの自動検出を有効にします。
Java
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用にある Java の設定手順を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
Node.js
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用にある Python の設定手順を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
一時テーブルを使用して Cloud Storage データにクエリを実行する
永続テーブルを作成せずに外部データソースに対してクエリを実行するには、次のものを結合するコマンドを実行します。
- テーブル定義ファイルとクエリ
- クエリとインライン スキーマ定義
- クエリと JSON スキーマ定義ファイル
テーブル定義ファイルまたは指定したスキーマを使用して一時外部テーブルが作成され、そのテーブルに対してクエリが実行されます。一時テーブルを使用した外部データソースに対するクエリの実行は、bq
コマンドライン ツールと API でサポートされています。
外部の一時テーブルを使用する場合は、BigQuery データセット内にテーブルが作成されません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。外部データに対する 1 回限りのアドホック クエリを行う場合、または抽出、変換、読み込み(ETL)プロセスを行う場合は、一時テーブルを使用して外部データソースのクエリを行うと便利です。
必要な権限
一時テーブルを使用して Cloud Storage の外部データに対してクエリを実行するには、プロジェクト レベル以上でクエリジョブを実行する権限と、外部データを指すテーブルを含むデータセットにアクセスする権限が必要です。Cloud Storage のデータに対してクエリを実行するには、データを含むバケットにアクセスする権限も必要です。
BigQuery で外部テーブルに対してクエリを実行する権限
一時テーブルを使用して BigQuery で外部テーブルに対してクエリを実行するには、次の IAM 権限が必要です。
bigquery.tables.getData
bigquery.jobs.create
以下の各 IAM 事前定義ロールには、一時テーブルを使用して BigQuery の外部テーブルに対してクエリを実行するために必要な権限が含まれています。
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(bigquery.jobs.create
権限を含む)roles/bigquery.user
(bigquery.jobs.create
権限を含む)roles/bigquery.jobUser
(bigquery.jobs.create
権限を含む)
また、bigquery.datasets.create
権限がある場合は、作成したデータセットに外部テーブルを作成してアクセスできます。それでも、データを照会するには bigquery.jobs.create
権限が必要です。
BigQuery での IAM のロールと権限については、事前定義ロールと権限をご覧ください。
Cloud Storage バケット内の外部データに対してクエリを実行する権限
Cloud Storage バケット内の外部データに対してクエリを実行するには、次の IAM 権限が必要です。
storage.objects.get
storage.objects.list
(URI ワイルドカードを使用する場合に必要)
IAM 事前定義ロール roles/storage.objectViewer
には、Cloud Storage バケット内の外部データに対してクエリを実行するために必要なすべての権限が含まれています。
一時テーブルを作成してクエリを行う
bq
コマンドライン ツール、API、クライアント ライブラリを使用して、外部データソースにリンクされた一時テーブルを作成してクエリを実行できます。
bq
外部データソースにリンクされている一時テーブルに対するクエリを行うには、--external_table_definition
フラグを指定して bq query
コマンドを実行します。bq
コマンドライン ツールを使用して外部データソースにリンクする一時テーブルに対してクエリを実行するには、以下を使用してテーブルのスキーマを識別します。
- テーブル定義ファイル(ローカルマシンに保存)
- インライン スキーマの定義
- JSON スキーマ ファイル(ローカルマシンに保存)
(省略可)--location
フラグを指定して、その値をロケーションに設定します。
テーブル定義ファイルを使用して、外部データソースにリンクする一時テーブルをクエリするには、次のコマンドを入力します。
bq --location=location query \ --external_table_definition=table::definition_file \ 'query'
ここで
- location は、使用するロケーションの名前です。
--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。 - table は、作成する一時テーブルの名前です。
- definition_file は、ローカルマシン上のテーブル定義ファイルのパスです。
- query は、一時テーブルに送信するクエリです。
たとえば、次のコマンドを実行すると、sales_def
というテーブル定義ファイルを使用して sales
という一時テーブルが作成され、クエリが実行されます。
bq query \
--external_table_definition=sales::sales_def \
'SELECT
Region,
Total_sales
FROM
sales'
インライン スキーマ定義を使用して、外部データソースにリンクする一時テーブルに対してクエリを実行するには、次のコマンドを入力します。
bq --location=location query \ --external_table_definition=table::schema@source_format=Cloud Storage URI \ 'query'
ここで
- location は、使用するロケーションの名前です。
--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。 - table は、作成する一時テーブルの名前です。
- schema は、
field:data_type,field:data_type
という形式のインライン スキーマの定義です。 - source_format は、外部データソースの形式です(例:
CSV
)。 - Cloud Storage URI は、使用する Cloud Storage URI です。
- query は、一時テーブルに送信するクエリです。
たとえば、次のコマンドを実行すると、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER
を使用して、Cloud Storage に保存された CSV ファイルにリンクする一時テーブルが sales
という名前で作成され、クエリが実行されます。
bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
Region,
Total_sales
FROM
sales'
JSON スキーマ ファイルを使用して、外部のデータソースにリンクする一時テーブルにクエリを実行するには、次のコマンドを入力します。
bq --location=location query \ --external_table_definition=schema_file@source_format=Cloud Storage URI \ 'query'
ここで
- location は、使用するロケーションの名前です。
--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。 - schema_file は、ローカルマシン上の JSON スキーマ ファイルのパスです。
- source_format は、外部データソースの形式です(例:
CSV
)。 - Cloud Storage URI は、使用する Cloud Storage URI です。
- query は、一時テーブルに送信するクエリです。
たとえば、次のコマンドを実行すると、/tmp/sales_schema.json
というスキーマ ファイルを使用して、Cloud Storage に保存された CSV ファイルにリンクするテーブルが sales
という名前で作成されます。
bq query \ --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \ 'SELECT Region, Total_sales FROM sales'
API
クエリジョブ構成を作成します。
jobs.query
とjobs.insert
の呼び出しについては、データのクエリをご覧ください。ExternalDataConfiguration
を作成して、外部データソースを指定します。
Java
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
Node.js
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用にある Python の設定手順を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
外部でパーティションに分割されたデータのクエリ
外部でパーティション分割された Cloud Storage データのクエリの手順を参照してください。
Cloud Storage の URI でのワイルドカードの使用
Cloud Storage のデータが、共通のベース名を共有する複数のファイルに分割されている場合は、テーブル定義ファイルの URI でワイルドカードを使用できます。テーブル定義ファイルを使用せずに外部テーブルを作成するときにもワイルドカードを使用できます。
Cloud Storage の URI にワイルドカードを追加するには、ベース名にアスタリスク(*
)を追加します。
例:
次のワイルドカード URI は、接頭辞
gs://mybucket/fed-samples/fed-sample
で始まるすべてのフォルダ内のすべてのファイルを選択します。gs://mybucket/fed-samples/fed-sample*
次のワイルドカード URI は、
fed-samples
というフォルダとfed-samples
のサブフォルダにある.csv
拡張子を持つファイルのみを選択します。gs://mybucket/fed-samples/fed-sample/*.csv
次のワイルドカード URI は、
fed-samples
という名前のフォルダで、fed-sample*.csv
という命名パターンのファイルを選択します。この例では、fed-samples
のサブフォルダ内のファイルは選択されません。gs://mybucket/fed-samples/fed-sample*.csv
一部のプラットフォームでは、bq
コマンドライン ツールの使用時に、アスタリスクをエスケープしなければならない場合があります。
バケット内のオブジェクト(ファイル名)について使用できるワイルドカードは 1 つのみです。ワイルドカードは、オブジェクト名の中や末尾に使用できます。バケット名にワイルドカードを付けることはできません。ソース URI では複数のワイルドカードを使用できません。たとえば、パス gs://mybucket/fed-*/temp/*.csv
は無効です。
Google Datastore のエクスポートで指定できる URI は 1 つのみです。また、URI の末尾は .backup_info
または .export_metadata
である必要があります。
以下の場合、ワイルドカード文字(アスタリスク)は使用できません。
- Datastore または Firestore のエクスポートにリンクされる外部テーブルを作成する。
- Cloud Storage から Datastore または Firestore のエクスポート データを読み込む。
_FILE_NAME 疑似列
外部データソースに基づくテーブルは、_FILE_NAME
という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Cloud Storage と Google ドライブに保存されている外部データを参照するテーブルでのみ使用できます。
列名 _FILE_NAME
は予約されています。つまり、この名前を持つ列はどのテーブルにも作成できません。_FILE_NAME
の値を選択するには、エイリアスを使用する必要があります。次の例のクエリでは、エイリアス fn
を疑似列に割り当て、_FILE_NAME
を選択しています。
bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
name,
_FILE_NAME AS fn
FROM
`dataset.table_name`
WHERE
name contains "Alex"'
ここで
- project_id は、有効なプロジェクト ID です。Cloud Shell を使用する場合や Google Cloud CLI でデフォルトのプロジェクトを設定する場合、このフラグは不要です。
- dataset は、外部の永続テーブルが保存されているデータセットの名前です。
- table_name は、外部の永続テーブルの名前です。
クエリに _FILE_NAME
疑似列に対するフィルタ述語がある場合、BigQuery は、フィルタに一致しないファイルの読み取りをスキップしようとします。_FILE_NAME
疑似列を使用してクエリ述語を構築する場合、疑似列を使用して取り込み時間パーティション分割テーブルに対するクエリを実行する場合と同様の推奨事項が適用されます。