外部テーブルの Cloud Storage データにクエリを実行する
このドキュメントでは、Cloud Storage 外部テーブルに保存されているデータに対してクエリを行う方法について説明します。
始める前に
Cloud Storage 外部テーブルがあることを確認します。
必要なロール
Cloud Storage 外部テーブルに対してクエリを行うには、次のロールを持っていることを確認してください。
- BigQuery データ閲覧者(
roles/bigquery.dataViewer
) - BigQuery ユーザー(
roles/bigquery.user
) - Storage オブジェクト閲覧者(
roles/storage.objectViewer
)
権限に応じて、これらのロールを自身に付与するか、これらのロールを付与するよう管理者に依頼します。ロールの付与の詳細については、リソースに対して付与可能なロールの表示をご覧ください。
外部テーブルのクエリに必要な BigQuery 権限を正確に確認するには、[必要な権限] セクションを開きます。
必要な権限
bigquery.jobs.create
bigquery.readsessions.create
(BigQuery Storage Read API を使用してデータを読み取る場合にのみ必要)bigquery.tables.get
bigquery.tables.getData
カスタムロールや他の事前定義ロールを使用して、これらの権限を取得することもできます。
外部の永続テーブルに対してクエリを行う
Cloud Storage 外部テーブルを作成すると、標準の BigQuery テーブルの場合と同じように GoogleSQL 構文を使用してクエリを行うことができます。例: SELECT field1, field2
FROM mydataset.my_cloud_storage_table;
外部の一時テーブルに対してクエリを行う
外部データに対する 1 回限りのアドホック クエリを行う場合、または抽出、変換、読み込み(ETL)プロセスを行う場合は、一時テーブルを使用して外部データソースのクエリを行うと便利です。
永続テーブルを作成せずに外部データソースに対してクエリを実行するには、一時テーブルに対してテーブル定義を指定し、コマンドまたは呼び出しでそのテーブル定義を使用して一時テーブルに対してクエリを実行します。テーブル定義は次のいずれかの方法で指定できます。
- テーブル定義ファイル
- インライン スキーマの定義
- JSON スキーマ ファイル
テーブル定義ファイルまたは指定したスキーマを使用して一時外部テーブルが作成され、そのテーブルに対してクエリが実行されます。
外部の一時テーブルを使用する場合は、BigQuery データセット内にテーブルが作成されません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。
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=BUCKET_PATH \ 'QUERY'
次のように置き換えます。
LOCATION
: ロケーション の名前。--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。TABLE
: 作成する一時テーブルの名前。SCHEMA
:field:data_type,field:data_type
という形式のインライン スキーマの定義。SOURCE_FORMAT
は、外部データソースの形式です(例:CSV
)。BUCKET_PATH
: テーブルのデータを含む Cloud Storage バケットへのパス(gs://bucket_name/[folder_name/]file_pattern
形式)。file_pattern
にワイルドカードとしてアスタリスク(*
)を 1 つ指定して、バケットから複数のファイルを選択することもできます。たとえば、gs://mybucket/file00*.parquet
のようにします。詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。複数のパスを指定して、
uris
オプションに複数のバケットを指定できます。次の例に、有効な
uris
値を示します。gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
、gs://bucket1/path1/*
複数のファイルをターゲットとする
uris
値を指定する場合、それらのファイルはすべて互換性のあるスキーマを共有する必要があります。BigQuery での Cloud Storage URI の使用方法については、Cloud Storage リソースパスをご覧ください。
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=BUCKET_PATH \ 'QUERY'
次のように置き換えます。
LOCATION
: ロケーション の名前。--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。SCHEMA_FILE
: ローカルマシン上の JSON スキーマ ファイルへのパスSOURCE_FORMAT
は、外部データソースの形式です(例:CSV
)。BUCKET_PATH
: テーブルのデータを含む Cloud Storage バケットへのパス(gs://bucket_name/[folder_name/]file_pattern
形式)。file_pattern
にワイルドカードとしてアスタリスク(*
)を 1 つ指定して、バケットから複数のファイルを選択することもできます。たとえば、gs://mybucket/file00*.parquet
のようにします。詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。複数のパスを指定して、
uris
オプションに複数のバケットを指定できます。次の例に、有効な
uris
値を示します。gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
、gs://bucket1/path1/*
複数のファイルをターゲットとする
uris
値を指定する場合、それらのファイルはすべて互換性のあるスキーマを共有する必要があります。BigQuery での Cloud Storage URI の使用方法については、Cloud Storage リソースパスをご覧ください。
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
API を使用してクエリを実行する手順は次のとおりです。
Job
オブジェクトを作成します。Job
オブジェクトのconfiguration
セクションにJobConfiguration
オブジェクトを入力します。JobConfiguration
オブジェクトのquery
セクションにJobConfigurationQuery
オブジェクトを入力します。JobConfigurationQuery
オブジェクトのtableDefinitions
セクションにExternalDataConfiguration
オブジェクトを入力します。- クエリを非同期で実行するには
jobs.insert
メソッドを呼び出し、同期的にクエリを実行するにはjobs.query
メソッドを呼び出して、Job
オブジェクトで渡します。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Java の設定手順を完了してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Node.js の設定手順を完了してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Python の設定手順を完了してください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
_FILE_NAME 疑似列に対してクエリを行う
外部データソースに基づくテーブルは、_FILE_NAME
という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Cloud Storage、Google ドライブ、Amazon S3、Azure Blob Storage に保存されている外部データを参照するテーブルでのみ使用できます。
_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
疑似列を使用してクエリ述語を構築する場合、疑似列を使用して取り込み時間パーティション分割テーブルに対するクエリを実行する場合と同様の推奨事項が適用されます。
次のステップ
- BigQuery での SQL の使用について確認する。
- 外部テーブルについて確認する。
- BigQuery の割り当てについて確認する。