Google ドライブの外部テーブルを作成する
このドキュメントでは、Google ドライブに保存されているデータを使用して外部テーブルを作成する方法について説明します。
BigQuery は、個人用ドライブ ファイルと共有ファイルの両方で外部テーブルをサポートしています。ドライブの詳細については、ドライブのトレーニングとヘルプをご覧ください。
ドライブ内にある次の形式のファイルを使用して外部テーブルを作成できます。
- カンマ区切り値(CSV)
- JSON(改行区切り)
- Avro
- Google スプレッドシート
始める前に
外部テーブルを作成する前に、情報を収集して、テーブルの作成権限があることを確認します。
ドライブの URI を取得する
Google ドライブのデータソース用に外部テーブルを作成するには、ドライブの URI を指定する必要があります。ドライブの URI は、ドライブデータの URL から直接取得できます。
URI の形式
https://docs.google.com/spreadsheets/d/FILE_ID
または
https://drive.google.com/open?id=FILE_ID
ここで、FILE_ID
はドライブ ファイルの英数字の ID です。
ドライブのアクセスを有効にする
ドライブ内でホストされているデータにアクセスするには、追加の OAuth スコープが必要です。これはデフォルトでは有効になっていませんが、Google Cloud コンソール、bq
コマンドライン ツール、または API を使用して、以下の手順で有効にできます。
コンソール
Google Cloud コンソールで永続テーブルを作成する場合は、ウェブベースの認証手順を行います。プロンプトが表示されたら、[許可] をクリックして、BigQuery クライアント ツールにドライブへのアクセスを許可します。
gcloud
ドライブ アクセスを有効にするには:
次のコマンドを入力して、Google Cloud CLI が最新バージョンであることを確認します。
gcloud components update
次のコマンドを入力して、ドライブの認証を行います。
gcloud auth login --enable-gdrive-access
API
BigQuery API を使用する場合には、BigQuery のスコープとドライブの OAuth スコープをリクエストします。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
BigQuery への認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証の設定をご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery への認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証の設定をご覧ください。
必要なロール
外部テーブルを作成するには、bigquery.tables.create
BigQuery Identity and Access Management(IAM)権限が必要です。
この権限は、次のそれぞれの事前定義 Identity and Access Management ロールに含まれています。
- BigQuery データ編集者(
roles/bigquery.dataEditor
) - BigQuery データオーナー(
roles/bigquery.dataOwner
) - BigQuery 管理者(
roles/bigquery.admin
)
これらのロールのプリンシパルでない場合は、アクセス権の付与または外部テーブルの作成を管理者に依頼してください。
BigQuery での Identity and Access Management のロールと権限の詳細については、事前定義ロールと権限をご覧ください。
外部テーブルを作成する
外部のデータソースにリンクされた永続テーブルは、次の方法で作成します。
- Google Cloud コンソールの使用
bq
コマンドライン ツールのmk
コマンドを使用するtables.insert
API メソッドを使用する際にExternalDataConfiguration
を作成する- クライアント ライブラリの使用
外部テーブルを作成するには:
コンソール
- Google Cloud コンソールで、BigQuery ページを開きます。
[エクスプローラ] パネルでプロジェクトを開いて、データセットを選択します。
アクション オプションを開いて、[開く] をクリックします。
詳細パネルで [テーブルを作成]
をクリックします。[テーブルの作成] ページの [ソース] セクションで、次の操作を行います。
[テーブルの作成元] で [ドライブ] を選択します。
[ドライブの URI を選択] フィールドに ドライブの URI を入力します。ドライブの URI にはワイルドカードを使用できません。
[ファイル形式] でデータの形式を選択します。ドライブのデータで有効な形式は次のとおりです。
- カンマ区切り値(CSV)
- JSON(改行区切り)
- Avro
- スプレッドシート
(省略可)[スプレッドシート] を選択した場合、[シートの範囲(省略可)] ボックスで、クエリを実行するシートとセル範囲を指定します。シート名を指定するか、セル範囲として
sheet_name!top_left_cell_id:bottom_right_cell_id
を指定できます。たとえば、「Sheet1!A1:B20」と指定します。[シートの範囲] を指定しない場合、ファイルの最初のシートが使用されます。[テーブルの作成] ページの [送信先] セクションで、次の操作を行います。
[データセット名] で該当するデータセットを選択し、[テーブル名] フィールドに BigQuery で作成するテーブルの名前を入力します。
[テーブルタイプ] が [外部テーブル] に設定されていることを確認します。
[スキーマ] セクションにスキーマ定義を入力します。
- JSON または CSV ファイルの場合、[自動検出] オプションをオンにしてスキーマの自動検出を有効にできます。Datastore エクスポート、Firestore エクスポート、Avro ファイルには、[自動検出] を使用できません。これらのファイル形式のスキーマ情報は、自己記述型のソースデータから自動的に取得されます。
- スキーマ情報を手動で入力します。
- [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。注: 既存のテーブルのスキーマを JSON 形式で表示するには、
bq
コマンドライン ツールにbq show --format=prettyjson DATASET.TABLE
コマンドを入力します。 - [フィールドを追加] を使用して、スキーマを手動で入力します。
- [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。注: 既存のテーブルのスキーマを JSON 形式で表示するには、
[テーブルを作成] をクリックします。
必要に応じて、自分のアカウントを選択して [許可] をクリックし、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 のリファレンス ドキュメントをご覧ください。
BigQuery への認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証の設定をご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery への認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証の設定をご覧ください。
外部テーブルに対してクエリを実施する
詳細については、ドライブデータをクエリするをご覧ください。
_FILE_NAME 疑似列
外部データソースに基づくテーブルは、_FILE_NAME
という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Cloud Storage と Google ドライブに保存されている外部データを参照するテーブルでのみ使用できます。
_FILE_NAME
という列名は予約されています。つまり、この名前を持つ列はどのテーブルにも作成できません。