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 スコープが必要です。BigQuery に対する認証を行いドライブへのアクセスを有効にするには、次の操作を行います。
コンソール
Google Cloud コンソールで外部テーブルを作成する場合は、ウェブベースの認証手順に沿って操作します。プロンプトが表示されたら、[許可] をクリックして、BigQuery クライアント ツールにドライブへのアクセスを許可します。
gcloud
このページの gcloud CLI のサンプルは、次のいずれかの開発環境から使用できます。
次のコマンドを入力して、Google Cloud CLI が最新バージョンであることを確認します。
gcloud components update
次のコマンドを入力して、ドライブの認証を行います。
gcloud auth login --enable-gdrive-access
API
BigQuery のスコープに加えて、適切な ドライブの OAuth スコープをリクエストします。
gcloud auth login --enable-gdrive-access
コマンドを実行してログインします。gcloud auth print-access-token
コマンドを実行して、API に使用されるドライブ スコープで OAuth アクセス トークンを取得します。
Python
ローカル環境で、必要なスコープを使用して次の手順によりアプリケーションのデフォルト認証情報(ADC)を設定します。
Google Cloud CLI をインストールし、次のコマンドを実行して初期化します。
gcloud init
Google アカウントのローカル認証情報を作成します。
gcloud auth application-default login \ --client-id-file=CLIENT_ID_FILE \ --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
CLIENT_ID_FILE
は、OAuth クライアント ID を含むファイルに置き換えます。詳細については、gcloud CLI で提供されるユーザー認証情報をご覧ください。
Java
ローカル環境で、必要なスコープを使用して次の手順によりアプリケーションのデフォルト認証情報(ADC)を設定します。
Google Cloud CLI をインストールし、次のコマンドを実行して初期化します。
gcloud init
Google アカウントのローカル認証情報を作成します。
gcloud auth application-default login \ --client-id-file=CLIENT_ID_FILE \ --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
CLIENT_ID_FILE
は、OAuth クライアント ID を含むファイルに置き換えます。詳細については、gcloud CLI で提供されるユーザー認証情報をご覧ください。
必要なロール
外部テーブルを作成するには、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 形式で表示するには、bq コマンドライン ツールに
[テーブルを作成] をクリックします。
必要に応じて、自分のアカウントを選択して [許可] をクリックし、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
from google.cloud import bigquery import google.auth credentials, project = google.auth.default() # 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] options = external_config.google_sheets_options assert options is not None options.skip_leading_rows = 1 # Optionally skip header row. 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) results = client.query_and_wait(sql) # Make an API request. # Wait for the query to complete. w_states = list(results) print( "There are {} states with names starting with W in the selected range.".format( len(w_states) ) )
Java
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 { GoogleCredentials credentials = ServiceAccountCredentials.getApplicationDefault(); // 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()); } } }
外部テーブルにクエリを実行する
詳細については、ドライブデータのクエリをご覧ください。
_FILE_NAME
疑似列
外部データソースに基づくテーブルは、_FILE_NAME
という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Cloud Storage と Google ドライブに保存されている外部データを参照するテーブルでのみ使用できます。
_FILE_NAME
という列名は予約されています。つまり、この名前を持つ列はどのテーブルにも作成できません。