Google ドライブデータにクエリを実行する

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

BigQuery は、個人用 Google ドライブとチーム用 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 です。

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

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

ウェブ UI


ウェブ 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 Quickstart の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

import google.auth
# from google.cloud import bigquery

# 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',
])
client = bigquery.Client(credentials=credentials, project=project)

アクセス制御とスコープ

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

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

  • 外部テーブルを含むデータセットに対する READER または bigquery.dataViewer アクセス権
  • データセットを含むプロジェクトに対する bigquery.user アクセス権(クエリジョブを実行するため)
  • 外部テーブルにリンクされた Google ドライブ ファイルに対する Can view アクセス権

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ウェブ UI

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

    BigQuery ウェブ UI に移動

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

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

    • [Location] で [Google Drive] を選択し、ソース フィールドに Google ドライブの URI を入力します。Google ドライブの URI ではワイルドカードを使用できません。
    • [File format] で、データのフォーマットを選択します。Google ドライブのデータに有効なフォーマットは次のとおりです。

      • カンマ区切り値(CSV)
      • JSON(改行区切り)
      • Avro
      • Google スプレッドシート(最初のタブのみ)
  4. [Create Table] ページの [Destination Table] セクションで、次の操作を行います。

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

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

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

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

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

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

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

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

CLI

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

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

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

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]=[DRIVE_URI] [DATASET_ID].[TABLE_NAME]

ここで

  • [SCHEMA] は、スキーマ定義([FIELD]:[DATA_TYPE], [FIELD]:[DATA_TYPE] の形式)です。
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または GOOGLE_SHEETS です。
  • [DRIVE_URI] は、使用する Google ドライブの URI です。
  • [DATASET_ID] は、テーブルを含むデータセットの名前です。
  • [TABLE_NAME] は作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、スキーマ定義 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_ID].[TABLE_NAME]

ここで

  • [SCHEMA_FILE] は、ローカルマシン上にある JSON スキーマ ファイルへのパスです。
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または GOOGLE_SHEETS です。
  • [DRIVE_URI] は、使用する Google ドライブの URI です。
  • [DATASET_ID] は、テーブルを含むデータセットの名前です。
  • [TABLE_NAME] は作成するテーブルの名前です。

たとえば、次のコマンドを実行すると、/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

外部データソースを指定するには、externalDataConfiguration プロパティを使用します。

Python

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

import google.auth
# from google.cloud import bigquery
# dataset_id = 'my_dataset'

# 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',
])
client = bigquery.Client(credentials=credentials, project=project)

# 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('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
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets 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)))

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

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

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

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

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

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

CLI

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

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

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

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

ここで

  • [LOCATION] は、使用するロケーションです。--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]=[DRIVE_URI] '[QUERY]'

ここで

  • [LOCATION] は、使用するロケーションです。--location フラグはオプションです。
  • [TABLE_NAME] は、作成する一時テーブルの名前です。
  • [SCHEMA] は、インライン スキーマ定義です。形式: [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]
  • [SOURCE_FORMAT] は、CSVNEWLINE_DELIMITED_JSONAVRO または GOOGLE_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_JSONAVRO または GOOGLE_SHEETS です。
  • [DRIVE_URI] は、使用する Google ドライブの URI です。
  • [QUERY] は、一時テーブルに送信するクエリです。

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

bq --location=US 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 Quickstart の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

import google.auth
# from google.cloud import bigquery

# 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',
])
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
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)))

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