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

1. Cloud Storage Console を開きます。

   Cloud Storage Console

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

3. Cloud Storage Console の上部に、オブジェクトのパスが表示されます。URI を作成するには、gs://bucket/file を適切なパス（例: gs://mybucket/myfile.json）に置き換えます。bucket は Cloud Storage バケット名で、file はデータを含むオブジェクト（ファイル）の名前です。

注: gsutil ls コマンドを使用してバケットやオブジェクトを一覧表示することもできます。

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

永続テーブルまたは一時テーブルを使用すると、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

1. Cloud コンソールで、[BigQuery] ページを開きます。

BigQuery に移動

1. [エクスプローラ] パネルでプロジェクトを開いて、データセットを選択します。

2. アクション オプションを開いて、[テーブルを作成] をクリックします。

3. [テーブルの作成] ページの [ソース] セクションで、次の操作を行います。

   • [テーブルの作成元] で [Google Cloud Storage] を選択します。

   • [GCS バケットからファイルを選択] フィールドで、ファイルまたは Cloud Storage バケットを参照するか、「Cloud Storage URI」を入力します。Cloud コンソールで複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。

   • [ファイル形式] でデータの形式を選択します。

4. [テーブルの作成] ページの [送信先] セクションで、次の操作を行います。

   • [データセット] で、該当するデータセットを選択します。
   • [テーブルタイプ] が [外部テーブル] に設定されていることを確認します。
   • [テーブル名] フィールドに、BigQuery で作成するテーブルの名前を入力します。

5. [スキーマ] セクションで、スキーマの自動検出を有効にするか、スキーマを手動で指定できます。

   • スキーマの自動検出を有効にするには、[自動検出] オプションを選択します。

   • 手動でスキーマを指定するには、[自動検出] オプションを選択せずに、次のいずれかを行います。

     • [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。

6. [テーブルを作成] をクリックします。

永続テーブルが作成されると、ネイティブの BigQuery テーブルの場合と同じようにクエリを実行できます。クエリの完了後は、結果を CSV または JSON としてエクスポート、テーブルとして保存、または Google スプレッドシートに保存できます。

SQL

永続外部テーブルを作成するには、CREATE EXTERNAL TABLE DDL ステートメントを実行します。スキーマは明示的に指定できます。スキーマを指定しない場合、BigQuery はスキーマの自動検出を使用して、外部データからスキーマを推測します。

次の例では、スキーマの自動検出を使用して、Cloud Storage に格納された CSV ファイルにリンクする sales という名前の外部テーブルを作成しています。

1. Cloud Console で、[BigQuery] ページに移動します。

   BigQuery に移動

2. クエリエディタで次のステートメントを入力します。

   CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
     OPTIONS (
     format = 'CSV',
     uris = ['gs://mybucket/sales.csv']);

3. [ 実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリの実行をご覧ください。

次の例では、スキーマを明示的に指定し、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 のリファレンス ドキュメントをご覧ください。

GitHub で表示 フィードバック

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。

GitHub で表示 フィードバック

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

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

GitHub で表示 フィードバック

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

# Configure the external data source
dataset_ref = bigquery.DatasetReference(project, 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 データにクエリを実行する

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

• テーブル定義ファイルとクエリ
• クエリとインライン スキーマ定義
• クエリと 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 を東京リージョンで使用している場合は、このフラグの値を