Google Cloud Storage データに対するクエリ

このページでは、BigQuery を使用して Google Cloud Storage のデータにクエリを実行する方法を説明します。

BigQuery では、次の形式の Cloud Storage データのクエリがサポートされています。

  • カンマ区切り値(CSV)
  • JSON(改行区切り)
  • Avro ファイル
  • Cloud Datastore のエクスポート
  • Cloud Firestore のエクスポート

BigQuery では、次のストレージ クラスの Google Cloud Storage データにクエリを実行できます。

  • Multi-Regional
  • Regional
  • Nearline
  • Coldline

Cloud Storage 外部データソースに直接クエリを実行するには、データの Cloud Storage URI パスを指定し、データソースを参照するテーブルを作成する必要があります。Cloud Storage データソースの参照には、永続テーブルまたは一時テーブルを使用できます。

データセットのロケーションとは異なるリージョンに格納されているデータを参照する外部テーブルを作成することはできません。たとえば、米国ベースのデータセットで外部テーブルを作成する場合、データを含む Cloud Storage バケットは、米国内のリージョン バケットまたはマルチリージョン バケットにする必要があります。

Google Cloud Storage の URI を取得する

Google 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 を取得するには:

  1. Cloud Storage ウェブ UI を開きます。

    Cloud Storage ウェブ UI

  2. ソースデータを含むオブジェクト(ファイル)の場所に移動します。

  3. Cloud Storage ウェブ UI の上部に、オブジェクトのパスが表示されます。URI を作成するには、gs://[BUCKET]/[FILE] を適切なパスで置き換えます。たとえば、gs://mybucket/myfile.json[BUCKET] は Cloud Storage バケット名で、[FILE] はデータを含むオブジェクト(ファイル)の名前です。

アクセス制御とスコープ

外部の永続テーブルに対するアクセス制御

Google Cloud Storage データソースにリンクする外部の永続テーブルへのアクセスを共有できます。アクセス権は、ユーザー(サービス アカウントを含む)またはグループと共有できます。外部テーブルをクエリするには、ユーザーまたはグループに少なくとも次の権限が必要です。

  • 外部テーブルを含むデータセットに対する READER または bigquery.dataViewer アクセス権
  • データセットを含むプロジェクトに対する bigquery.user アクセス権(クエリジョブを実行するため)
  • Cloud Storage データの読み取りを許可する事前定義の IAM 役割 storage.objectViewer または次の権限:

Compute Engine インスタンスのスコープ

Compute Engine インスタンスを作成するときに、インスタンスに対するスコープのリストを指定できます。スコープにより、Google Cloud Storage ドライブを含む Google Cloud Platform プロダクトに対するインスタンスのアクセスを制御します。VM 上で実行されるアプリケーションは、サービス アカウントを使用して Google Cloud API を呼び出します。

Compute Engine のデフォルトのサービス アカウントとして実行するように Compute Engine インスタンスを設定し、このサービス アカウントが Cloud Storage データソースにリンクする外部テーブルにアクセスする場合、インスタンスに Google Cloud Storage に対する読み取り専用アクセスが必要です。Compute Engine のデフォルトのサービス アカウントには、https://www.googleapis.com/auth/devstorage.read_only スコープが自動的に付与されます。固有のサービス アカウントを作成する場合には、インスタンスに Cloud Storage 読み取りスコープを適用してください。

Compute Engine インスタンスへのスコープの適用方法については、インスタンスのサービス アカウントとアクセス スコープの変更をご覧ください。Compute Engine サービス アカウントの詳細については、サービス アカウントをご覧ください。

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

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

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

外部の永続テーブルを使用して Cloud Storage データにクエリを実行する

永続テーブルを使用して外部データソースにクエリを実行するには、BigQuery データセットに外部データソースにリンクするテーブルを作成します。データは BigQuery テーブルに保存されません。テーブルが永続のため、データセット レベルでアクセス制御を行い、基本となる外部データソースにアクセスできる他のユーザーとテーブルを共有できます。

BigQuery で外部の永続テーブルを作成するときに、次の 3 つの方法でスキーマ情報を指定できます。

  • API で外部の永続テーブルを作成する場合には、テーブル定義ファイルを作成して、外部データソースのスキーマとメタデータを定義します。データソースがスキーマの自動検出に対応している場合、テーブル定義ファイルの作成時にこの機能を有効にできます。
  • CLI で外部の永続テーブルを作成する場合には、テーブル定義ファイルを使用できます。固有のスキーマ ファイルを作成して使用することも、コマンドラインからインラインでスキーマを入力することもできます。
  • ウェブ UI で外部の永続テーブルを作成する場合には、テーブル スキーマを手動で入力できます。データソースがスキーマの自動検出に対応している場合には、この機能を使用できます。

外部の永続テーブルを使用して Cloud Storage データにクエリを実行するには、次の操作を行います。

  • テーブル定義ファイルを作成する(API の場合。CLI の場合は任意)
  • BigQuery に外部データソースにリンクするテーブルを作成する
  • 外部データソースにリンクするテーブルに対してクエリを実行する

外部の永続テーブルを作成する

外部データソースにリンクする永続テーブルを作成するには、ウェブ UI、CLI または API を使用します。

ウェブ UI

  1. BigQuery ウェブ UI に移動します。
    BigQuery ウェブ UI に移動

  2. ナビゲーション パネルで、データセットにカーソルを合わせて下矢印アイコン 下矢印アイコン画像 をクリックし、[Create new table] をクリックします。

  3. [Create Table] ページの [Source Data] セクションで、次の操作を行います。

    • [Location] で [Google Cloud Storage] を選択し、ソース フィールドに Cloud Storage の URI を入力します。Google Cloud Storage の URI にはワイルドカードがサポートされていることに注意してください。
    • [File format] で、データのフォーマットを選択します。Cloud Storage に有効なフォーマットは次のとおりです。

      • カンマ区切り値(CSV)
      • JSON(改行区切り)
      • Avro
      • Cloud Datastore のバックアップ(Cloud Firestore でも使用)
  4. [Create Table] ページの [Destination Table] セクションで、次の操作を行います。

    • [Table name] で、該当するデータセットを選択します。テーブル名のフィールドで、BigQuery で作成する永続テーブルの名前を入力します。
    • [Table type] が [External table] に設定されていることを確認します。
  5. [Schema] セクションで、スキーマ情報を入力します。

    • JSON または CSV ファイルの場合、[Auto-detect] オプションをオンにしてスキーマの自動検出を有効にできます。Cloud Datastore エクスポート、Cloud Firestore エクスポート、Avro ファイルの場合、自動検出を使用できません。これらのファイル形式のスキーマ情報は、自己記述型のソースデータから自動的に取得されます。

    • CSV または JSON スキーマ情報を手動で入力するには、次の操作を行います。

      • [Edit as text] をクリックして、テーブル スキーマを JSON 形式で入力する。
      • [Add Field] を使用して、スキーマを手動で入力する。
  6. [Options] セクションで該当する項目を選択し、[Create Table] をクリックします。

永続テーブルが作成されると、ネイティブの BigQuery テーブルと同じようにクエリを実行できます。ただし、外部データソースの制限の影響を受けます。

クエリの完了後に、結果を CSV または JSON としてエクスポートしたり、テーブルとして保存したり、Google スプレッドシートに保存したりできます。詳しくは、データのダウンロード、保存、エクスポートご覧ください。

CLI

BigQuery コマンドライン ツールでテーブルを作成するには、bq mk コマンドを使用します。CLI を使用して、外部データソースにリンクするテーブルを作成する場合、次のものを使用してテーブルのスキーマを確認できます。

  • テーブル定義ファイル(ローカルマシンに保存)
  • インライン スキーマの定義
  • JSON スキーマ ファイル(ローカルマシンに保存)

テーブル定義ファイルを使用して、Cloud Storage データソースにリンクする永続テーブルを作成するには、次のコマンドを入力します。

bq mk --external_table_definition=[DEFINITION_FILE] [DATASET_ID].[TABLE_NAME]

ここで

  • [DEFINITION_FILE] は、ローカルマシン上のテーブル定義ファイルのパスです。
  • [DATASET_ID] は、テーブルを含むデータセットの名前です。
  • [TABLE_NAME] は作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、mytable_def というテーブル定義ファイルを使用して mytable という永続テーブルが作成されます。

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

インライン スキーマ定義を使用して、外部データソースにリンクする永続テーブルを作成するには、次のコマンドを入力します。

bq mk --external_table_definition=[SCHEMA]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] [DATASET_ID].[TABLE_NAME]

ここで

  • [SCHEMA] は、スキーマ定義([FIELD]:[DATA_TYPE], [FIELD]:[DATA_TYPE] の形式)です。
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または DATASTORE_BACKUP です(DATASTORE_BACKUP は Cloud Filestore でも使用されます)。
  • [CLOUD_STORAGE_URI] は、使用する Cloud Storage URI です。
  • [DATASET_ID] は、テーブルを含むデータセットの名前です。
  • [TABLE_NAME] は作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER を使用して、Cloud Storage に保存された CSV にリンクする sales という永続テーブルが作成されます。

bq mk --external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv mydataset.sales

JSON スキーマ ファイルを使用して、外部データソースにリンクする永続テーブルを作成するには、次のコマンドを入力します。

bq mk --external_table_definition=[SCHEMA_FILE]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] [DATASET_ID].[TABLE_NAME]

ここで

  • [SCHEMA_FILE] は、ローカルマシン上にある JSON スキーマ ファイルへのパスです。
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または DATASTORE_BACKUP です(DATASTORE_BACKUP は Cloud Firestore でも使用されます)。
  • [CLOUD_STORAGE_URI] は、使用する Cloud Storage URI です。
  • [DATASET_ID] は、テーブルを含むデータセットの名前です。
  • [TABLE_NAME] は作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、/tmp/sales_schema.json というスキーマ ファイルを使用して、Cloud Storage に保存された CSV にリンクするテーブルが sales という名前で作成されます。

bq mk --external_table_definition=/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv mydataset.sales

永続テーブルが作成されると、ネイティブの BigQuery テーブルと同じようにクエリを実行できます。ただし、外部データソースの制限の影響を受けます。

クエリの完了後に、結果を CSV または JSON としてダウンロード、テーブルとして保存、Google スプレッドシートに保存できます。詳しくは、データのダウンロード、保存、エクスポートをご覧ください。

API

  • sourceUris は、完全修飾された gs://[BUCKET]/[OBJECT] 形式にする必要があります。

  • configuration.query.tableDefinitions プロパティを設定して、外部データソースのプロパティを指定します。

Python

このサンプルを試す前に、クライアント ライブラリを使用する BigQuery Quickstart の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

# Configure the external data source
dataset_ref = client.dataset(dataset_id)
table_id = 'us_states'
schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv',
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM {}.{} WHERE name like "W%"'.format(
    dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print('There are {} states with names starting with W.'.format(
    len(w_states)))

一時テーブルを使用して Cloud Storage データにクエリを実行する

永続テーブルを作成せずに外部データソースにクエリを実行するには、次のものを結合するコマンドを実行します。

テーブル定義ファイルまたは指定したスキーマが使用され、外部の一時テーブルが作成され、このテーブルに対してクエリが実行されます。一時テーブルを使用して外部データソースに対してクエリを実行する場合、BigQuery CLI または API を使用します。

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

一時テーブルを作成してクエリを実行する

外部データソースにリンクする一時テーブルを作成してクエリを実行するには、CLI または API を使用します。

CLI

外部データソースにリンクする一時テーブルに対してクエリを実行するには、bq query コマンドで --external_table_definition フラグを使用します。CLI を使用して、外部データソースにリンクする一時テーブルに対してクエリを実行する場合、次のものを使用してテーブルのスキーマを確認できます。

  • テーブル定義ファイル(ローカルマシンに保存)
  • インライン スキーマの定義
  • JSON スキーマ ファイル(ローカルマシンに保存)

--location フラグを指定してロケーションを設定します。

テーブル定義ファイルを使用して、外部データソースにリンクする一時テーブルをクエリするには、次のコマンドを入力します。

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[DEFINITION_FILE] '[QUERY]'

ここで

  • [LOCATION] は、該当するロケーションの名前です。データが US または EU のマルチリージョン ロケーションにある場合は、--location フラグを省略できます。
  • [TABLE_NAME] は、作成する一時テーブルの名前です。
  • [DEFINITION_FILE] は、ローカルマシン上のテーブル定義ファイルのパスです。
  • [QUERY] は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、sales_def というテーブル定義ファイルを使用して sales という一時テーブルが作成され、クエリが実行されます。

bq --location=US query --external_table_definition=sales::sales_def 'SELECT Region,Total_sales FROM sales;'

インライン スキーマ定義を使用して、外部データソースにリンクする一時テーブルに対してクエリを実行するには、次のコマンドを入力します。

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[SCHEMA]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] '[QUERY]'

ここで

  • [LOCATION] は、該当するロケーションの名前です。データが US または EU のマルチリージョン ロケーションにある場合は、--location フラグを省略できます。
  • [TABLE_NAME] は、作成する一時テーブルの名前です。
  • [SCHEMA] は、インライン スキーマ定義です。形式: [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または DATASTORE_BACKUP です(DATASTORE_BACKUP は Cloud Firestore でも使用されます)。
  • [CLOUD_STORAGE_URI] は、使用する Cloud Storage URI です。
  • [QUERY] は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER を使用して、Cloud Storage に保存された CSV ファイルにリンクする一時テーブルが sales という名前で生成されます。

bq --location=US 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] は、該当するロケーションの名前です。データが US または EU のマルチリージョン ロケーションにある場合は、--location フラグを省略できます。
  • [SCHEMA_FILE] は、ローカルマシン上にある JSON スキーマ ファイルへのパスです。
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または DATASTORE_BACKUP です(DATASTORE_BACKUP は Cloud Firestore でも使用されます)。
  • [CLOUD_STORAGE_URI] は、使用する Cloud Storage URI です。
  • [QUERY] は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、/tmp/sales_schema.json スキーマ情報を使用して、Cloud Storage に保存された CSV ファイルにリンクする一時テーブルが sales という名前で作成されます。

bq --location=US query --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv 'SELECT Region,Total_sales FROM sales;'

API

Python

このサンプルを試す前に、クライアント ライブラリを使用する BigQuery Quickstart の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

# from google.cloud import bigquery
# client = bigquery.Client()

# Configure the external data source and query job
external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv',
]
external_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table_id = 'us_states'
job_config = bigquery.QueryJobConfig()
job_config.table_definitions = {table_id: external_config}

# Example query to find states starting with 'W'
sql = 'SELECT * FROM {} WHERE name like "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # API request

w_states = list(query_job)  # Waits for query to finish
print('There are {} states with names starting with W.'.format(
    len(w_states)))

Cloud Storage の URI でのワイルドカードの使用

Google Cloud Storage データを複数のファイルに分割し、共通のベース名を共有する場合、テーブル定義ファイルの URI でワイルドカードを使用できます。テーブル定義ファイルを使用せずに外部テーブルを作成するときにもワイルドカードを使用できます。

Cloud Storage の URI にワイルドカードを追加するには、ベース名にアスタリスク(*)を追加します。たとえば、fed-sample000001.csvfed-sample000002.csv という名前のファイルがある場合、バケットの URI は gs://mybucket/fed-sample* のようになります。このワイルドカードを含む URI をウェブ UI、CLI または API で使用できます。

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

Google Cloud Datastore のエクスポートでは、指定できる URI は 1 つのみで、末尾に .backup_info または .export_metadata を指定する必要があります。

以下の場合、* ワイルドカード文字は使用できません

  • Cloud Datastore または Cloud Firestore のエクスポートにリンクされる外部テーブルを作成する
  • Cloud Storage から Cloud Datastore または Cloud Firestore のエクスポート データを読み込む

_FILE_NAME 疑似列

外部データソースに基づくテーブルは、_FILE_NAME という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Google 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 を使用する場合あるいは Cloud SDK でデフォルトのプロジェクトを設定する場合、このフラグは不要です。
  • [DATASET] は、外部の永続テーブルが保存されているデータセットの名前です。
  • [TABLE_NAME] は、外部の永続テーブルの名前です。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。