Contents
1. 外部テーブルと Connection の基本概念
| 項目 | 説明 |
|---|---|
| 外部テーブル | データが GCS、Google Sheets、Cloud Spanner、Amazon S3（BigLake 経由）等のストレージに格納されたままクエリできる仮想テーブル。データはクエリ実行時にオンデマンドでスキャンされます。 |
| Connection | 認証情報・ネットワーク設定・暗号化ポリシーなどを一元管理するリソース。外部テーブル作成時に connection_id を指定して利用します。複数の外部テーブルで同じ Connection を再利用でき、権限管理が簡素化されます。 |
| メリット |
|
ポイント：Connection は「認証」「ネットワーク」「暗号化」の３要素をまとめた「接続プロファイル」だと考えると、外部テーブル作成時に毎回同じ設定を書き込む必要がなくなります。
2. 前提条件と必須 IAM ロール
| ロール | 主な権限 | 推奨使用シーン |
|---|---|---|
roles/bigquery.admin |
テーブル作成、クエリ実行、ジョブ管理 | 開発者が外部テーブルを自ら作成・実行する場合 |
roles/bigquery.connectionAdmin (※) |
Connection の作成・更新・削除、接続情報取得 | 接続設定担当者（最小権限での運用推奨） |
roles/bigquery.dataViewer |
外部テーブルのメタデータ閲覧 | 読み取り専用ユーザー向け |
※
bigquery.connectionAdminは 2023 年 10 月に GA された比較的新しいロールです。詳細は IAM ロールリファレンス を参照してください。
VPC Service Controls（VSC）の考慮
- 境界設定：外部テーブルが参照するデータソース（例：GCS バケット）が配置されているプロジェクトと、Connection が属するプロジェクトは同一 VSC 境界に入れることを推奨します。
- リージョン整合性：Connection のロケーションと外部テーブルのデータセットロケーションが一致しないと「Invalid connection location」エラーが発生します。
IAM ロール付与例（gcloud）
|
1 2 3 4 5 6 7 8 9 10 11 |
# ユーザー alice@example.com に最小権限を付与 PROJECT=my-project gcloud projects add-iam-policy-binding $PROJECT \ --member=user:alice@example.com \ --role=roles/bigquery.connectionAdmin gcloud projects add-iam-policy-binding $PROJECT \ --member=user:alice@example.com \ --role=roles/bigquery.dataViewer |
3. コンソールで Connection を作成する手順
3.1 Google Cloud Storage (GCS) 接続
| 手順 | 操作内容 |
|---|---|
| 1 | Cloud Console → BigQuery → 左メニュー External connections → Create connection |
| 2 | Connection type = Google Cloud Storage を選択 |
| 3 | 名前（例：gcs_conn_sales）とロケーション（データセットと同じリージョン）を入力 |
| 4 | Authentication はプロジェクトのサービスアカウントが自動的に使用されます。CMEK を利用する場合は、バケット側で KMS 暗号化キーへの roles/cloudkms.cryptoKeyEncrypterDecrypter 権限を付与してください |
| 5 | Create をクリック |
補足：GCS バケットが Uniform bucket‑level access（UBLA）になっているか確認し、必要に応じて IAM で
roles/storage.objectViewerをサービスアカウントに付与します。
3.2 Google Sheets 接続
| 手順 | 操作内容 |
|---|---|
| 1 | 同様に External connections → Create connection |
| 2 | Connection type = Google Sheets を選択 |
| 3 | 名前（例：sheets_conn_marketing）とロケーションを入力 |
| 4 | OAuth scopes が自動的に https://www.googleapis.com/auth/drive.readonly で設定されます。接続作成時に Google Drive API の使用許可が求められます |
| 5 | シート ID（URL の /d/ 以降）を入力し Create |
ポイント：シートはユーザー単位のアクセス制御です。組織全体で利用する場合は、対象シートを「閲覧者」ロールで共有しておくと権限エラーが減ります。
3.3 Cloud Spanner 接続
| 手順 | 操作内容 |
|---|---|
| 1 | Connection type = Cloud Spanner を選択 |
| 2 | 名前（例：spanner_conn_orders）とロケーションを入力 |
| 3 | インスタンス名、データベース名を指定 |
| 4 | 接続に使用するサービスアカウントが roles/spanner.databaseUser を持っていることを確認 |
| 5 | Create |
注意：Spanner はリージョン単位でインスタンスが作成されるため、Connection のロケーションは必ずそのインスタンスと同一にしてください。
3.4 Amazon S3（BigLake 経由）接続
| 手順 | 操作内容 |
|---|---|
| 1 | Connection type = BigLake (AWS S3) を選択 |
| 2 | 名前（例：biglake_conn_s3）とロケーションを入力 |
| 3 | 認証方式を選択 ・IAM Role ARN（GCP から AWS IAM ロールへ委任） ・Access key / secret key（従来のキー認証） |
| 4 | 必要な S3 バケット ARN と、オプションで Dataplex/Hive メタストアのリファレンスを設定 |
| 5 | Create |
デリゲーション：2023 年 11 月にリリースされた BigLake delegation 機能は、AWS 側で
sts:AssumeRoleを許可した IAM ロールへ GCP のサービスアカウントが委任できる仕組みです。キー管理の負荷を大幅に削減します（詳細は BigLake デリゲーション ガイド を参照）。
4. CLI（bq / gcloud）で Connection を作成する例
ベストプラクティス：IaC（Terraform, Deployment Manager 等）で管理したい場合は
gcloud beta bigquery connections系コマンドを活用し、スクリプト化・バージョン管理します。
4.1 GCS 接続（bq CLI）
|
1 2 3 4 5 6 7 |
# bq コマンドは Cloud Storage と Google Sheets のみ対応 bq mk --connection \ --project_id=my-project \ --location=us-central1 \ --display_name="gcs_conn_sales" \ --connection_type=CLOUD_STORAGE |
4.2 Sheets 接続（gcloud beta）
|
1 2 3 4 5 6 7 8 |
gcloud beta bigquery connections create sheets \ --connection-id=sheets_conn_marketing \ --project=my-project \ --region=asia-northeast1 \ --properties='{ "googleSheetsSpreadsheetId":"1AbCdefGhIjKlMnOpQrStUvWxYz" }' |
4.3 Cloud Spanner 接続（gcloud beta）
|
1 2 3 4 5 6 7 8 9 |
gcloud beta bigquery connections create spanner \ --connection-id=spanner_conn_orders \ --project=my-project \ --region=europe-west1 \ --properties='{ "spannerInstance":"orders-instance", "spannerDatabase":"order-db" }' |
4.4 BigLake (S3) 接続（gcloud beta）
|
1 2 3 4 5 6 7 8 9 10 |
gcloud beta bigquery connections create biglake \ --connection-id=biglake_conn_s3 \ --project=my-project \ --region=us-east4 \ --properties='{ "awsIamRoleArn":"arn:aws:iam::123456789012:role/BigQueryS3Access", "awsS3Bucket":"arn:aws:s3:::my-data-lake", "dataplexMetastore":"projects/my-project/locations/us-east4/lakes/my-metastore" }' |
5. 外部テーブル定義とクエリ実行
5.1 CREATE EXTERNAL TABLE の構文例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
-- GCS (CSV) CREATE EXTERNAL TABLE `my_dataset.sales_csv` OPTIONS ( format = 'CSV', uris = ['gs://my-sales-data/2024/*.csv'], skip_leading_rows = 1, connection_id = 'projects/my-project/locations/us-central1/connections/gcs_conn_sales' ); -- Google Sheets CREATE EXTERNAL TABLE `my_dataset.marketing_sheet` OPTIONS ( format = 'GOOGLE_SHEETS', sheet_range = 'Sheet1!A1:E1000', connection_id = 'projects/my-project/locations/asia-northeast1/connections/sheets_conn_marketing' ); -- Cloud Spanner CREATE EXTERNAL TABLE `my_dataset.spanner_orders` OPTIONS ( format = 'SPANNER', spanner_instance = 'orders-instance', spanner_database = 'order-db', connection_id = 'projects/my-project/locations/europe-west1/connections/spanner_conn_orders' ); -- BigLake (S3) – Iceberg テーブル CREATE EXTERNAL TABLE `my_dataset.s3_iceberg` OPTIONS ( format = 'ICEBERG', location_uri = 's3://my-data-lake/iceberg/', connection_id = 'projects/my-project/locations/us-east4/connections/biglake_conn_s3' ); |
ポイント：
OPTIONS.connection_idに必ず Connection のフルパス（projects/.../connections/<id>）を指定します。省略すると「接続が見つからない」エラーになります。
5.2 クエリ実行とコスト概算
| シナリオ | 想定スキャンサイズ | 現行料金* |
|---|---|---|
| GCS CSV（5 GB） | 5 GB | $0.05/GB → 約 $0.25 |
| Sheets（200 MB） | 0.2 GB | $0.05/GB → $0.01 |
| Spanner（10 GB） | 10 GB | $0.05/GB → $0.50 |
| S3 Iceberg（30 GB） | 30 GB | $0.05/GB → $1.50 |
*2024 年 3 月現在、BigQuery のオンデマンドクエリは「スキャンしたバイト数 × $5/TB」（＝$0.005/GB）。料金は地域や割引（定額プラン・予約容量）により変動します。最新の価格は公式料金ページをご確認ください。
コスト削減テクニック
- プレビュー機能 –
bq show --format=prettyjson <external_table>で最初の 10 MB は無料です。スキーマやデータサンプルだけを確認したいときに活用してください。 - 列指向 SELECT – 必要な列のみを選択することで、実際にスキャンされるバイト数が削減されます。例：
SELECT date, amount FROM my_dataset.sales_csv. - パーティション・プルーニング – 外部テーブルの
urisに日付やディレクトリ単位でプレフィックスを付与し、クエリ側でWHERE _FILE_NAME LIKE '2024/03/%'のように絞り込むとスキャン対象が減ります。 - CMEK を併用したデータ暗号化 – 暗号化自体は料金に影響しませんが、キー管理の一元化で運用コストを低減できます。
6. エラー例と対処法
| エラーメッセージ | 主因 | 推奨対策 |
|---|---|---|
Access Denied: Permission bigquery.connections.get |
接続取得権限不足 | roles/bigquery.connectionViewer または bigquery.connectionAdmin を付与 |
Invalid connection location |
Connection のロケーションとテーブルのデータセットが不一致 | 同一リージョンで作成し直す（例：us-central1） |
Schema detection failed |
CSV ヘッダー欠損・区切り文字不整合 | field_delimiter, skip_leading_rows を明示指定 |
External table scan aborted due to insufficient resources |
大規模スキャンが同時実行制限に引っかかった | クエリを分割、または maximumBytesBilled オプションで上限設定 |
BigLake delegation not enabled |
S3 接続作成時に Dataplex メタストア未登録 | 前述の Dataplex メタストア登録手順 を実行 |
7. セキュリティと監査ベストプラクティス
7.1 最小権限ロールの設計
|
1 2 3 4 5 6 7 |
# カスタムロール例：外部テーブル接続の閲覧・ジョブ作成のみ許可 gcloud iam roles create BigQueryExternalConnectionUser \ --project=my-project \ --title="BigQuery External Connection User" \ --description="Read external connections and run queries" \ --permissions=bigquery.connections.get,bigquery.jobs.create,bigquery.tables.get |
- 開発者：
roles/bigquery.dataEditor+ カスタムロール（接続作成権限） - 分析担当：上記カスタムロールだけでクエリ実行が可能
7.2 CMEK（顧客管理キー）による暗号化
- KMS キー作成
bash
gcloud kms keyrings create my-keyring --location=us-central1 --project=my-project
gcloud kms keys create bq-cmek-key \
--keyring=my-keyring \
--location=us-central1 \
--purpose=encryption
- バケットに CMEK を適用
bash
gsutil mb -p my-project -c STANDARD -l us-central1 gs://secure-sales-data/
gsutil kms encryption -k projects/my-project/locations/us-central1/keyRings/my-keyring/cryptoKeys/bq-cmek-key gs://secure-sales-data/
- 外部テーブル作成時に
encryption_configuration.kms_key_nameを指定（SQL 例）
sql
CREATE EXTERNAL TABLE my_dataset.sales_secure
OPTIONS (
format='CSV',
uris=['gs://secure-sales-data/2024/*.csv'],
connection_id='projects/my-project/locations/us-central1/connections/gcs_conn_sales',
encryption_configuration=STRUCT('kmsKeyName'='projects/my-project/locations/us-central1/keyRings/my-keyring/cryptoKeys/bq-cmek-key')
);
7.3 アクセス監査ログの有効化
BigQuery の Data Access ログを Cloud Logging にエクスポートし、外部テーブルへのアクセスや Connection 設定変更を追跡します。
|
1 2 3 4 5 |
gcloud logging sinks create bq-audit-logs \ "bigquery.googleapis.com/activity" \ --log-filter='resource.type="bigquery_resource"' \ --destination=storage.googleapis.com/my-audit-bucket/ |
- 推奨設定：
bigquery.connections.*,bigquery.jobs.create,bigquery.tables.getを含むフィルタで細かく監査。 - 可視化：Cloud Monitoring のダッシュボードや Looker Studio で定期的にレポートを作成すると、権限逸脱の早期検知につながります。
8. パフォーマンス最適化の追加テクニック
| 手法 | 説明 |
|---|---|
| 列プルーニング | 外部テーブルはスキャン対象列だけを読み込む。SELECT col1, col2 FROM … で不要列を除外すると、データサイズが数十％削減されることがあります。 |
ファイルレベルフィルタ (_FILE_NAME) |
WHERE _FILE_NAME LIKE '2024/01/%' のようにファイルパスで絞り込むと、対象オブジェクトのリスト取得コストが削減されます。 |
| Parquet / ORC への変換 | CSV よりも列指向フォーマット（Parquet, ORC）は圧縮率・スキップ機能が高く、同等データでスキャン量が 30 % 以下に抑えられます。 |
| 外部テーブルのキャッシュ | CREATE TABLE ... CLONE を利用して一時的にローカル BigQuery テーブルへコピーし、頻繁に参照する場合はクエリコストを回避できます（ただしデータ更新が必要なときは再同期）。 |
| スロットの割り当て | 大規模スキャンの場合はオンデマンドスロットではなく、予約スロット（固定スロット数）で実行するとクエリ完了までの時間が安定します。 |
9. まとめ
- Connection リソースは認証・暗号化・監査を一元管理し、外部テーブル作成時に安全かつ簡潔に参照できる基盤です。
- 必要な IAM ロールは
bigquery.connectionAdmin（接続作成）と最小権限ロールの組み合わせで十分です。VPC Service Controls との併用でネットワークレベルの保護も実装できます。 - コンソール・CLI の両方から Connection を作成でき、IaC ツールと相性が良いので運用自動化を推奨します。
- 外部テーブルはクエリ時にスキャンされるため コストはスキャンバイト数 に比例します。列プルーニングや Parquet 変換で大幅な削減が可能です。
- セキュリティは最小権限、CMEK、監査ログの３層防御を基本とし、定期的にロール・キーの見直しを行いましょう。
次のステップ：まずは GCS バケット（例：
gs://my-demo-data/）にサンプル CSV を置き、上記手順で Connection と外部テーブルを作成してください。クエリ実行後は Cloud Logging のbigquery.googleapis.com/activityへ出力された監査ログを確認し、権限が正しく適用されていることを検証しましょう。
参考リンク
| 内容 | URL |
|---|---|
| 外部データソース（公式） | https://cloud.google.com/bigquery/docs/external-data-sources |
| Connection の概要と設定方法 | https://cloud.google.com/bigquery/docs/connections-intro |
| BigLake デリゲーションガイド | https://cloud.google.com/bigquery/docs/biglake-delegation |
| IAM ロールリファレンス | https://cloud.google.com/iam/docs/understanding-roles |
| VPC Service Controls のベストプラクティス | https://cloud.google.com/vpc-service-controls/docs/best-practices |
| BigQuery 料金ページ（最新） | https://cloud.google.com/bigquery/pricing |
本稿は執筆時点の情報に基づいています。Google Cloud のサービスは頻繁にアップデートされるため、実装前に公式ドキュメントで最新情報をご確認ください。