Cross-cloud Lakehouse で Databricks Unity Catalog の Iceberg テーブルを BigQuery から参照してみた

-- スキーマを作成
CREATE SCHEMA IF NOT EXISTS main.iceberg_external
MANAGED LOCATION 's3://<バケット名>/iceberg_tables/';

-- Managed Iceberg テーブルを作成
CREATE TABLE main.iceberg_external.sales_data
(
  sale_id BIGINT,
  sale_ts TIMESTAMP,
  amount DECIMAL(10,2),
  region STRING
)
USING ICEBERG;

-- サンプルレコードを追加
INSERT INTO main.iceberg_external.sales_data
(sale_id, sale_ts, amount, region)
VALUES
  (1001, TIMESTAMP '2025-12-01 10:30:00', 15000.50, 'Tokyo'),
  (1002, TIMESTAMP '2025-12-01 11:45:00', 8500.00, 'Osaka'),
  (1003, TIMESTAMP '2025-12-01 14:20:00', 12300.75, 'Tokyo'),
  (1004, TIMESTAMP '2025-12-02 09:15:00', 22000.00, 'Nagoya'),
  (1005, TIMESTAMP '2025-12-02 13:50:00', 9800.25, 'Fukuoka');

SELECT * FROM main.iceberg_external.sales_data ORDER BY sale_id;

GRANT USE CATALOG ON CATALOG main TO `<アプリケーションID>`;
GRANT USE SCHEMA  ON SCHEMA main.iceberg_external TO `<アプリケーションID>`;
GRANT SELECT ON TABLE main.iceberg_external.sales_data TO `<アプリケーションID>`;

-- サービスプリンシパルに EXTERNAL USE SCHEMA 権限を付与
GRANT EXTERNAL USE SCHEMA ON SCHEMA main.iceberg_external
TO `<アプリケーションID>`;

gcloud services enable biglake.googleapis.com secretmanager.googleapis.com

# 変数の設定
PROJECT_ID="<プロジェクトID>"
REGION="us-east5"
SECRET_NAME="databricks-secret"
CATALOG_NAME="federated-catalog"
UNITY_INSTANCE="<Databricks ワークスペースのホスト名>"
UNITY_CATALOG="main" # ここではmain

# credentials.json を作成
cat > credentials.json << EOF
{
  "client_id": "<Client ID>",
  "client_secret": "<Client Secret>"
}
EOF

# リージョンエンドポイントを設定（カタログと同じリージョンにする）
gcloud config set api_endpoint_overrides/secretmanager \
    https://secretmanager.${REGION}.rep.googleapis.com/

# シークレットを作成
gcloud secrets create ${SECRET_NAME} \
    --location="${REGION}" \
    --project="${PROJECT_ID}" \
    --data-file=credentials.json

gcloud alpha biglake iceberg catalogs create ${CATALOG_NAME} \
    --project="${PROJECT_ID}" \
    --primary-location="${REGION}" \
    --catalog-type="federated" \
    --federated-catalog-type="unity" \
    --secret-name="projects/${PROJECT_ID}/locations/${REGION}/secrets/${SECRET_NAME}" \
    --unity-instance-name="${UNITY_INSTANCE}" \
    --unity-catalog-name="${UNITY_CATALOG}" \
    --refresh-interval="5m"

Created catalog [projects/<プロジェクトID>/catalogs/federated-catalog].
BigLake service account: blirc-xxxxxxxxxxxxx-xxxx@gcp-sa-biglakerestcatalog.iam.gserviceaccount.com
BigLake service account ID: xxxxxxxxxxxxxxxxxxxx

# サービスアカウントを変数に追加
SA="blirc-xxxxxxxxxxxxx-xxxx@gcp-sa-biglakerestcatalog.iam.gserviceaccount.com"

# リージョンエンドポイントを設定
gcloud config set api_endpoint_overrides/secretmanager \
    https://secretmanager.${REGION}.rep.googleapis.com/

# アクセス権を付与
gcloud secrets add-iam-policy-binding ${SECRET_NAME} \
    --project="${PROJECT_ID}" \
    --location="${REGION}" \
    --member="serviceAccount:${SA}" \
    --role="roles/secretmanager.secretAccessor"

gcloud alpha biglake iceberg catalogs list \
    --project="${PROJECT_ID}" \
    --format="json"

[
  {
    "biglake-service-account": "blirc-xxxxxxxxxxxxx-xxxx@gcp-sa-biglakerestcatalog.iam.gserviceaccount.com",
    "catalog-type": "CATALOG_TYPE_FEDERATED",
    "federated-catalog-options": {
      "refresh-status": {
        "start-time": "2026-05-19T13:53:40Z"
      },
      "secret-name": "projects/<プロジェクトID>/locations/us-east5/secrets/databricks-secret",
      "unity-catalog-info": {
        "catalog-name": "main",
        "instance-name": "<Databricks ワークスペースのホスト名>"
      }
    },
    "name": "projects/<プロジェクトID>/catalogs/federated-catalog",
    ...
  }
]

-- Iceberg 読み取りを有効化した Delta テーブル（UniForm）
CREATE OR REPLACE TABLE main.iceberg_external.products_delta (
  product_id INT,
  product_name STRING,
  category STRING,
  price DECIMAL(10,2),
  created_at TIMESTAMP
)
USING DELTA
TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name',
  'delta.enableIcebergCompatV2' = 'true',
  'delta.universalFormat.enabledFormats' = 'iceberg'
);

DESCRIBE EXTENDED main.iceberg_external.products_delta;

-- サンプルデータを追加
INSERT INTO main.iceberg_external.products_delta
(product_id, product_name, category, price, created_at)
VALUES
  (101, 'Laptop Pro', 'Electronics', 129800.00, TIMESTAMP '2025-12-01 09:00:00'),
  (102, 'Wireless Mouse', 'Electronics', 2980.00, TIMESTAMP '2025-12-01 10:00:00'),
  (103, 'Desk Chair', 'Furniture', 45000.00, TIMESTAMP '2025-12-02 11:00:00'),
  (104, 'Standing Desk', 'Furniture', 78000.00, TIMESTAMP '2025-12-02 12:00:00'),
  (105, 'Monitor 27"', 'Electronics', 35800.00, TIMESTAMP '2025-12-03 13:00:00');

-- サービスプリンシパルに権限付与
GRANT SELECT ON TABLE main.iceberg_external.products_delta TO `<アプリケーションID>`;