Google ドライブデータのクエリ

このページでは、BigQuery を使用して Google ドライブのデータにクエリを実行する方法について説明します。

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

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

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

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

Google ドライブの URI を取得する

Google ドライブ データソース用に外部テーブルを作成するには、Google ドライブの URI を使用します。Google ドライブの URI を取得するには、次の操作を行います。

  1. Google ドライブを開きます。

  2. ファイルを右クリックして、[共有可能なリンクを取得] を選択します。URI は、https://drive.google.com/open?id=file_id のような形式になります。

    ここで

    • file_id は、Google ドライブ ファイルの英数字の ID です。

ファイルを開いて URI を取得することもできます。たとえば、Google スプレッドシート ファイルの URI を取得するには、次の手順に従います。

  1. Google スプレッドシートを開きます。

  2. スプレッドシートを開き、ブラウザのアドレスバーの URI をコピーします。URI は、次の https://docs.google.com/spreadsheets/d/file_id のようになります。

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

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

Console

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

従来の UI

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

CLI

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

  1. 次のコマンドを入力して、コマンドライン ツールを最新バージョンに更新します。

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

    gcloud auth login --enable-gdrive-access
    

API

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

Python

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

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",
    ]
)

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

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

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

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

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

必要な権限とスコープ

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

BigQuery の権限

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

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

bigquery.tables.create 権限および bigquery.tables.getData 権限はいずれも、事前定義された以下の Cloud IAM の役割に含まれています。

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

bigquery.jobs.create 権限は、事前定義された以下の Cloud IAM の役割に含まれています。

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

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

BigQuery での Cloud IAM の役割と権限については、事前定義された役割と権限をご覧ください。

Google ドライブの権限

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

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

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

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

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

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

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

  • GCP Console または従来の BigQuery ウェブ UI
  • コマンドライン ツールの mk コマンドを使用する
  • tables.insert API メソッドを使用する際に ExternalDataConfiguration を作成
  • クライアント ライブラリの使用

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

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

  • tables.insert API メソッドを使用して永続外部テーブルを作成するには、スキーマ定義と ExternalDataConfiguration を含むテーブル リソースを作成します。autodetect パラメータを true に設定して、サポートされているデータソースのスキーマ自動検出を有効にします。
  • CLI で永続外部テーブルを作成する場合には、テーブル定義ファイルを使用できます。固有のスキーマ ファイルを作成して使用することも、コマンドラインからインラインでスキーマを入力することもできます。テーブル定義ファイルを作成する際に、サポートされているデータソースに対するスキーマの自動検出を有効にできます。
  • コンソールまたは従来の BigQuery ウェブ UI を使用して永続外部テーブルを作成する場合は、テーブル スキーマを手動で入力するか、サポートされているデータソースに対するスキーマの自動検出を利用できます。

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

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。
    GCP Console に移動する

  2. ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。ウィンドウの右側にある [テーブルを作成] をクリックします。

    テーブルの作成

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

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

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

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

      • カンマ区切り値(CSV)
      • JSON(改行区切り)
      • Avro
      • Google スプレッドシート
  4. (省略可)[Google スプレッドシート] を選択した場合、[シートの範囲(省略可)] ボックスで、クエリを実行するシートとセル範囲を指定します。シート名を指定するか、セル範囲として sheet_name!top_left_cell_id:bottom_right_cell_id を指定できます。たとえば、「Sheet1!A1:B20」と指定します。[シートの範囲] を指定しない場合、ファイルの最初のシートが使用されます。

  5. [テーブルの作成] ページの [送信先] セクション: * [データセット名] で該当するデータセットを選択し、[テーブル名] フィールドに BigQuery で作成するテーブルの名前を入力します。

    データセットを選択

    • [テーブルタイプ] が [外部テーブル] に設定されていることを確認します。
  6. [スキーマ] セクションにスキーマ定義を入力します。

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

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

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

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

従来の UI

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

    BigQuery ウェブ UI に移動

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

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

    • [Location] で [Google Drive] を選択し、ソース フィールドに Google ドライブの URI を入力します。Google ドライブの URI ではワイルドカードがサポートされないことに注意します。
    • [ファイル形式] でデータの形式を選択します。Google ドライブのデータで有効な形式は次のとおりです。

      • カンマ区切り値(CSV)
      • JSON(改行区切り)
      • Avro
      • Google スプレッドシート
  4. [テーブルの作成] ページの [宛先テーブル] セクションで、次の操作を行います。

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

    • JSON または CSV ファイルの場合、[自動検出] オプションをオンにしてスキーマの自動検出を有効にできます。現在、Google スプレッドシートの場合、ウェブ UI でスキーマの自動検出を使用できません(CLI と API では使用できます)。また、Avro データソースにも自動検出を使用できません。スキーマ情報は、Avro ファイルから自動的に取得されます。

    • CSV、JSON、Google スプレッドシート ファイルの場合、次の方法でスキーマ情報を手動で入力できます。

      • [テキストとして編集] をクリックして、テーブル スキーマを JSON 形式で入力する。
      • [Add Field] を使用して、スキーマを手動で入力する。
  6. [Options] セクションで該当する項目を選択し、[テーブルの作成] をクリックします。

  7. アカウントを選択して [Allow] をクリックし、Google ドライブへのアクセスを BigQuery クライアント ツールに許可します。

    クライアント ツールのアクセス ダイアログ

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

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

CLI

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

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

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

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 は、CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS のいずれかです。
  • drive_uri は、使用する Google ドライブの URI です。
  • dataset はテーブルを含むデータセットの名前です。
  • table は、作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER を使用して、Google ドライブに保存された Google スプレッドシート ファイルにリンクする永続テーブルが 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 は、CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS のいずれかです。
  • drive_uri は、使用する Google ドライブの URI です。
  • dataset はテーブルを含むデータセットの名前です。
  • table は、作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、/tmp/sales_schema.json スキーマ ファイルを使用して、Google ドライブに保存された 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 としてダウンロード、テーブルとして保存、Google スプレッドシートに保存できます。詳しくは、データのダウンロード、保存、エクスポートをご覧ください。

API

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

Python

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

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",
    ]
)

# TODO(developer): 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)
    )
)

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

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

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

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

必要な権限

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

BigQuery の権限

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

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

bigquery.tables.getData 権限は、事前定義された以下の Cloud IAM の役割に含まれています。

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

bigquery.jobs.create 権限は、事前定義された以下の Cloud IAM の役割に含まれています。

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

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

BigQuery での Cloud IAM の役割と権限については、事前定義された役割と権限をご覧ください。

Google ドライブの権限

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

一時テーブルを作成してクエリを行う

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

CLI

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

  • テーブル定義ファイル(ローカルマシンに保存)
  • インライン スキーマの定義
  • 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 は、CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS のいずれかです。
  • drive_uri は、使用する Google ドライブの URI です。
  • query は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER を使用して、Google ドライブに保存された 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_format=drive_uri \
'query'

ここで

  • location は、使用するロケーションです。--location フラグは省略可能です。
  • schema_file は、ローカルマシン上にある JSON スキーマ ファイルのパスです。
  • source_format は、CSVNEWLINE_DELIMITED_JSONAVROGOOGLE_SHEETS のいずれかです。
  • drive_uri は、使用する Google ドライブの URI です。
  • query は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、/tmp/sales_schema.json スキーマ ファイルを使用して、Google ドライブに保存された 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 のリファレンス ドキュメントをご覧ください。

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",
    ]
)

# TODO(developer): 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()
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)  # 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)
    )
)

_FILE_NAME 疑似列

外部データソースに基づくテーブルは、_FILE_NAME という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Cloud StorageGoogle ドライブに保存されている外部データを参照するテーブルでのみ使用できます。

列名 _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 のサポートページをご覧ください。