ドライブデータをクエリする

このドキュメントでは、Google ドライブの外部テーブルに保存されているデータをクエリする方法について説明します。

BigQuery は、個人用ドライブ ファイルと共有ファイルの両方に対するクエリをサポートしています。ドライブの詳細については、Google ドライブのトレーニングとヘルプをご覧ください。

永続外部テーブルまたはクエリの実行時に作成した一時外部テーブルに含まれているドライブデータをクエリできます。

必要なロール

ドライブの外部テーブルに対してクエリを行うには、次のロールが必要です。

  • BigQuery データ閲覧者（roles/bigquery.dataViewer）
  • BigQuery ユーザー（roles/bigquery.user）

権限に応じて、これらのロールを自身に付与するか、これらのロールを付与するよう管理者に依頼します。ロールの付与の詳細については、リソースに対して付与可能なロールの表示をご覧ください。

外部テーブルのクエリに必要な BigQuery 権限を正確に確認するには、[必要な権限] セクションを開きます。

必要な権限

カスタムロールや他の事前定義ロールを使用して、これらの権限を取得することもできます。

ドライブの権限

ドライブの外部データにクエリを実行するには、少なくとも外部テーブルにリンクされたドライブ ファイルへの View アクセス権が必要です。

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

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

Compute Engine インスタンスをサービス アカウントとして実行するように設定し、このサービス アカウントでドライブ データソースにリンクされた外部テーブルにアクセスする場合、ドライブの OAuth スコープ（https://www.googleapis.com/auth/drive.readonly）をそのインスタンスに追加する必要があります。

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

外部の永続テーブルを使用してドライブのデータをクエリする

ドライブの外部テーブルを作成すると、標準の BigQuery テーブルの場合と同じように GoogleSQL 構文を使用してクエリを行うことができます。例: SELECT field1, field2 FROM mydataset.my_drive_table;

一時テーブルを使用してドライブのデータをクエリする

外部データに対する 1 回限りのアドホック クエリを行う場合、または抽出、変換、読み込み（ETL）プロセスを行う場合は、一時テーブルを使用して外部データソースのクエリを行うと便利です。

永続テーブルを作成せずに外部データソースに対してクエリを実行するには、一時テーブルに対してテーブル定義を指定し、コマンドまたは呼び出しでそのテーブル定義を使用して一時テーブルに対してクエリを実行します。テーブル定義は次のいずれかの方法で指定できます。

テーブル定義ファイルまたは指定したスキーマを使用して一時外部テーブルが作成され、そのテーブルに対してクエリが実行されます。

外部の一時テーブルを使用する場合は、BigQuery データセット内にテーブルが作成されません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。

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

bq コマンドライン ツール、API、クライアント ライブラリを使用して、外部データソースにリンクされた一時テーブルを作成してクエリを実行できます。

bq

外部データソースにリンクされている一時テーブルに対するクエリを行うには、--external_table_definition フラグを指定して bq query コマンドを実行します。bq コマンドライン ツールを使用して、外部データソースにリンクする一時テーブルに対してクエリを実行するには、以下を使用してテーブルのスキーマを指定します。

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

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

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

ここで

  • LOCATION は、ロケーションです。--location フラグは省略可能です。
  • 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=DRIVE_URI \
'QUERY'

ここで

  • LOCATION は、ロケーションです。--location フラグは省略可能です。
  • TABLE は、作成する一時テーブルの名前です。
  • SCHEMA は、FIELD:DATA_TYPE,FIELD:DATA_TYPE という形式のインライン スキーマの定義です。
  • SOURCE_FORMAT は CSV、NEWLINE_DELIMITED_JSON、AVRO、または GOOGLE_SHEETS です。
  • DRIVE_URI は、使用するドライブの URI です。
  • QUERY は、一時テーブルに送信するクエリです。

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

bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Region,Total_sales
 FROM
   sales'

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

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \
'QUERY'

ここで

  • LOCATION は、ロケーションです。--location フラグは省略可能です。
  • SCHEMA_FILE は、ローカルマシン上の JSON スキーマ ファイルのパスです。
  • SOURCE_FILE は CSV、NEWLINE_DELIMITED_JSON、AVRO、または GOOGLE_SHEETS です。
  • DRIVE_URI は、使用するドライブの URI です。
  • QUERY は、一時テーブルに送信するクエリです。

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

bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Total_sales
 FROM
   sales'

API

Python

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

BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証を設定するをご覧ください。

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")

# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table_id = "us_states"
job_config = bigquery.QueryJobConfig(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)  # Make an API request.

# Wait for the query to complete.
w_states = list(query_job)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

Java

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

BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証を設定するをご覧ください。

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

// Sample to queries an external data source using a temporary table
public class QueryExternalSheetsTemp {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalSheetsTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

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

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

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

制限事項

BigQuery クエリを Google スプレッドシートにオーバーロードすると、Resources exceeded during query execution: Google Sheets service overloaded. のようなエラーが発生する可能性があります。スプレッドシートを単純にすることを検討してください。たとえば、数式の使用を最小限に抑えます。詳細については、外部テーブルの制限事項をご覧ください。

次のステップ