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

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

このドキュメントでは、BigQuery を使用してドライブのデータに対してクエリを実行する方法について説明します。

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

ドライブ内にある次の形式のファイルをクエリできます。

• カンマ区切り値（CSV）
• JSON（改行区切り）
• Avro
• スプレッドシート

ドライブの外部データソースに対してクエリを実行するには、データのドライブ URI パスを指定し、そのデータソースを参照する外部テーブルを作成します。ドライブのデータソースの参照に使用するテーブルは、永続テーブルまたは一時テーブルのどちらでもかまいません。

制限

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

ドライブの URI を取得する

ドライブのデータソース用に外部テーブルを作成するには、ドライブの URI を指定する必要があります。ドライブの URI を取得するには、ファイルへのリンクで共有するをご覧ください。

URI の形式

• https://docs.google.com/spreadsheets/d/FILE_ID

  または

• https://drive.google.com/open?id=FILE_ID

ここで、FILE_ID はドライブ ファイルの英数字の ID です。

ドライブのアクセスを有効にする

ドライブ内でホストされているデータにアクセスするには、フェデレーション ソースを定義するときとクエリの実行中のいずれにおいても、追加の OAuth スコープが必要になります。これはデフォルトでは有効になっていませんが、Cloud Console、bq コマンドライン ツール、または API を使用して、以下の手順で有効にできます。

Console

Cloud Console で永続テーブルを作成する場合は、ウェブベースの認証手順に沿って操作します。プロンプトが表示されたら、[許可] をクリックして、BigQuery クライアント ツールにドライブへのアクセスを許可します。

gcloud

ドライブ アクセスを有効にするには:

1. 次のコマンドを入力して、Google Cloud CLI が最新バージョンであることを確認します。

   gcloud components update
   

2. 次のコマンドを入力して、ドライブの認証を行います。

   gcloud auth login --enable-gdrive-access
   

注: Cloud Shell を使用してドライブのデータにアクセスする場合、Google Cloud CLI の更新やドライブでの認証を行う必要はありません。

API

BigQuery API を使用する場合には、BigQuery のスコープとドライブの OAuth スコープをリクエストします。

Python

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

GitHub で表示 フィードバック

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)

Java

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

GitHub で表示 フィードバック

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();

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

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

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

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

必要な権限とスコープ

永続テーブルを使用してドライブの外部データに対してクエリを行う場合、プロジェクト レベル以上でクエリジョブを実行する権限、外部データを指すテーブルを作成する権限、およびテーブルデータにアクセスできる権限が必要です。外部データがドライブに保存されている場合、外部テーブルにリンクされたドライブ ファイルにアクセスする権限も必要です。

BigQuery の権限

BigQuery で外部テーブルを作成およびクエリするには、少なくとも以下の権限が必要です。

• bigquery.tables.create
• bigquery.tables.getData
• bigquery.jobs.create

次の事前定義済みの IAM ロールには bigquery.tables.create 権限と bigquery.tables.getData 権限の両方が含まれています。

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

• bigquery.user
• bigquery.jobUser
• bigquery.admin

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセスを使用すると、ユーザーはデータセット内に外部テーブルを作成できますが bigquery.jobs.create データを照会するには権限が必要です。

BigQuery での IAM のロールと権限について詳しくは、事前定義ロールと権限をご覧ください。

ドライブの権限

ドライブの外部データにクエリを実行するには、少なくとも外部テーブルにリンクされたドライブ ファイルへの 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 サービス アカウントの詳細については、サービス アカウントをご覧ください。

外部の永続テーブルを作成してクエリを実行する

外部のデータソースにリンクされた永続テーブルは、次の方法で作成します。

• Cloud Console の使用
• bq コマンドライン ツールの mk コマンドを使用する
• tables.insert API メソッドを使用する際に ExternalDataConfiguration を作成する
• クライアント ライブラリを使用する

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

永続外部テーブルを作成する場合は、次の方法でスキーマを指定できます。

• 明示的なテーブル スキーマを指定します。
• スキーマの自動検出を使用します。

外部テーブルを作成するには:

Console

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

BigQuery に移動

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

2. アクション オプションを開いて、[開く] をクリックします。

3. 詳細パネルで [テーブルを作成] をクリックします。

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

   • [テーブルの作成元] で [ドライブ] を選択します。

   • [ドライブの URI を選択] フィールドに ドライブの URI を入力します。ドライブの URI にはワイルドカードを使用できません。

   • [ファイル形式] でデータの形式を選択します。ドライブのデータで有効な形式は次のとおりです。

     • カンマ区切り値（CSV）
     • JSON（改行区切り）
     • Avro
     • スプレッドシート
     注: Cloud Console で Avro または Datastore のバックアップを選択すると、[自動検出] オプションは非表示になります。これらのファイルタイプは自己記述型であるためです。

5. （省略可）[スプレッドシート] を選択した場合、[シートの範囲（省略可）] ボックスで、クエリを実行するシートとセル範囲を指定します。シート名を指定するか、セル範囲として sheet_name!top_left_cell_id:bottom_right_cell_id を指定できます。たとえば、「Sheet1!A1:B20」と指定します。[シートの範囲] を指定しない場合、ファイルの最初のシートが使用されます。

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

   • [データセット名] で該当するデータセットを選択し、[テーブル名] フィールドに BigQuery で作成するテーブルの名前を入力します。

     

   • [テーブルタイプ] が [外部テーブル] に設定されていることを確認します。

7. [スキーマ] セクションにスキーマ定義を入力します。

   • JSON または CSV ファイルの場合、[自動検出] オプションをオンにしてスキーマの自動検出を有効にできます。Datastore エクスポート、Firestore エクスポート、Avro ファイルには、[自動検出] を使用できません。これらのファイル形式のスキーマ情報は、自己記述型のソースデータから自動的に取得されます。
   • スキーマ情報を手動で入力します。
     • [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。注: 既存のテーブルのスキーマを JSON 形式で表示するには、bq コマンドライン ツールに bq show --format=prettyjson DATASET.TABLE コマンドを入力します。
     • [フィールドを追加] を使用して、スキーマを手動で入力します。

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

9. 必要に応じて、自分のアカウントを選択して [許可] をクリックし、BigQuery クライアント ツールにドライブへのアクセスを許可します。

これで、ネイティブ BigQuery テーブルの場合と同じようにテーブルに対してクエリを実行できます。ただし、外部データソースの制限は適用されます。

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

bq

bq mk コマンドを使用して bq コマンドライン ツールでテーブルを作成します。bq コマンドライン ツールを使用して外部データソースにリンクするテーブルを作成するには、以下を使用してテーブルのスキーマを識別します。

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

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

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

ここで

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

たとえば、mytable_def という名前のテーブル定義ファイルを使用して、mytable という名前の永続テーブルを作成するコマンドは次のとおりです。

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

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

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

ここで

• SCHEMA は、FIELD:DATA_TYPE,FIELD:DATA_TYPE という形式のスキーマ定義です。
• SOURCE_FORMAT は CSV、NEWLINE_DELIMITED_JSON、AVRO、または GOOGLE_SHEETS です。
• DRIVE_URI は、使用するドライブの URI です。
• DATASET は、テーブルを含むデータセットの名前です。
• TABLE は、作成するテーブルの名前です。

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

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

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

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

ここで

• SCHEMA_FILE は、ローカルマシン上の JSON スキーマ ファイルのパスです。
• SOURCE_FORMAT は CSV、NEWLINE_DELIMITED_JSON、AVRO、または GOOGLE_SHEETS です。
• DRIVE_URI は、使用するドライブの URI です。
• DATASET は、テーブルを含むデータセットの名前です。
• TABLE は、作成するテーブルの名前です。

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

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

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

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

API

tables.insert API メソッドを使用する際に ExternalDataConfiguration を作成します。schema プロパティを指定するか autodetect プロパティを true に設定して、サポートされているデータソースのスキーマの自動検出を有効にします。

Python

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

GitHub で表示 フィードバック

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)

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
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.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.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an 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)  # 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 のリファレンス ドキュメントをご覧ください。

GitHub で表示 フィードバック

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.TableId;
import com.google.cloud.bigquery.TableInfo;
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 permanent table
public class QueryExternalSheetsPerm {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    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.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, 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();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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

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

• テーブル定義ファイルとクエリ
• クエリとインライン スキーマ定義
• クエリと JSON スキーマ定義ファイル

テーブル定義ファイルまたは指定したスキーマを使用して一時外部テーブルが作成され、そのテーブルに対してクエリが実行されます。一時テーブルを使用した外部データソースに対するクエリの実行は、bq コマンドライン ツールと API でサポートされています。

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

必要な権限

一時テーブルを使用してドライブの外部データに対してクエリを行う場合、プロジェクト レベル以上でクエリジョブを実行する権限と、外部データを指すテーブルを含むデータセットへのアクセス権が必要です。ドライブのデータに対してクエリを行う場合は、データを含むドライブ ファイルにアクセスするための権限も必要です。

BigQuery の権限

一時テーブルを使用して BigQuery の外部テーブルに対してクエリを行うには、少なくとも以下の権限が必要です。

• bigquery.tables.getData
• bigquery.jobs.create

次の事前定義済みの IAM ロールには bigquery.tables.getData 権限が含まれています。

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

• bigquery.user
• bigquery.jobUser
• bigquery.admin

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセス権により、ユーザーはデータセット内の外部テーブルを作成してアクセスできますが、データに対してクエリを行うにはさらに bigquery.jobs.create 権限が必要です。

BigQuery での IAM のロールと権限について詳しくは、事前定義ロールと権限をご覧ください。

ドライブの権限

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

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

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

• クエリジョブ構成を作成します。jobs.query と jobs.insert の呼び出しについては、データのクエリをご覧ください。

• ExternalDataConfiguration を作成して、外部データソースを指定します。

Python

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

GitHub で表示 フィードバック

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 のリファレンス ドキュメントをご覧ください。

GitHub で表示 フィードバック

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());
    }
  }
}

_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 疑似列を使用してクエリ述語を構築する場合、疑似列を使用して取り込み時間パーティション分割テーブルに対するクエリを実行する場合と同様の推奨事項が適用されます。