Snowflake on Google Cloud 上の Snowflake-managed Apche Iceberg テーブルを BigQuery から参照する方法

## 概要

Snowflake on Google Cloud で管理している Snowflake-managed Apache Iceberg テーブルを、BigQuery の Apache Iceberg 用 BigLake 外部テーブルとして扱う方法を紹介します。Snowflake のデータを BigQuery に連携することなく、BigQuery から Snowflake と同等のデータを参照できるようになります。

BigQuery の Apache Iceberg 用 BigLake 外部テーブルは REST CATALOG をサポートしていないため、Snowflake-managed Apache Iceberg テーブルを Snowflake Open Catalog に同期しても、カタログ経由で最新のメタデータファイルを取得できません。そこで、Snowflake が提供する `GET_ICEBERG_TABLE_INFORMATION` システム関数でテーブル情報を取得し、その取得結果に基づき BigQuery の Apache Iceberg 用 BigLake 外部テーブルを更新する手順を解説します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/be141473-81d2-45db-a78a-3e8c8a603d24.png)

出所：[Apache Iceberg 用の BigLake 外部テーブルを作成する  | BigQuery  | Google Cloud](https://cloud.google.com/bigquery/docs/iceberg-external-tables?hl=ja)

Apache Iceberg 用の BigLake 外部テーブルを、Google Colab で作成したデータから作成する手順を、以下の記事で紹介しています。Apache Iceberg 用の BigLake 外部テーブルを初めて作成する方は参考にしてみてください。

- [Google Colab で操作した Apache Iceberg のデータから Apache Iceberg 用の BigLake 外部テーブルを作成する方法 #BigQuery - Qiita](https://qiita.com/manabian/items/1f252dc06ef7101ffbf2)

Snowflake における Apache Iceberg 機能の概要については、以下の記事で紹介しています。

- [Snowflake における Apache Iceberg の機能の完全ガイド： Apache Iceberg on Snowflake 活用の第一歩 #Spark - Qiita](https://qiita.com/manabian/items/7ba563b614e8ae016cca)

Spark での Apache Iceberg テーブルの基本操作については、以下の記事で紹介しています。

- [Getting started with PyIceberg と Iceberg で作成されるファイル郡の確認を Google Colab でやってみた #Python - Qiita](https://qiita.com/manabian/items/c7d8204a565e63a99764)
- [Google Colab の Spark にて Hadoop Catalog の Apache Iceberg の動作検証 #iceberg - Qiita](https://qiita.com/manabian/items/99aa3396bc15a28f9e10)

## 事前準備

### Snowflake on Google Cloud を作成

> GCP_US_CENTRAL1

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8807ed32-ca8e-4980-8a4c-647afe7fe90c.png)

### Snowflake と同一リージョンに GCS のバケットを作成

Snowflake を `GCP_US_CENTRAL1` に作成したため、`us-central1` リージョンにバケットを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e430a2e9-231b-4fc9-81f9-294d3a45db01.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/633dc43a-3d70-463a-97b3-acede2e452d6.png)

バケット作成後、`snowflake` というフォルダを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e03d8e89-0606-4d50-a7a0-002f5ef0ae3e.png)

## Snowflake における外部 Volume の構築

### Snowflake にて外部 Volume を作成

```sql
CREATE OR REPLACE EXTERNAL VOLUME my_gcs_01_vol
  STORAGE_LOCATIONS = (
    (
      NAME = 'my_gcs_bucket',
      STORAGE_PROVIDER = 'GCS',
      STORAGE_BASE_URL = 'gcs://manabianqiita/snowflake'
    )
  )
  ALLOW_WRITES = TRUE;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9a1da5d5-ab50-4615-83a6-d2f7603e40fd.png)

### 外部 Volume に紐づくアカウントに対して GCS の権限を付与

`DESC EXTERNAL VOLUME` の出力に含まれる `STORAGE_GCP_SERVICE_ACCOUNT` の値を取得します。

```sql
DESC EXTERNAL VOLUME my_gcs_01_vol;
SELECT 
    PARSE_JSON($4):STORAGE_GCP_SERVICE_ACCOUNT::string AS STORAGE_GCP_SERVICE_ACCOUNT
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE $2 = 'STORAGE_LOCATION_1';
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/cb2de46d-1241-4f6d-8ae4-0c43cdd9381a.png)

Google Cloud コンソールにて作成したバケットを選択し、`PERMISSIONS` -> `+ GRANT ACCESS` を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2cb8faf7-8c5d-4da2-ac4f-014e2f4f7c4c.png)

`STORAGE_GCP_SERVICE_ACCOUNT` の値を `New principals` に貼り付け、適切な権限（例：`Storage Admin`）を付与し、`SAVE` を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c9c8f4f8-95dd-4fbe-a369-8d413db84bb1.png)

Snowflake に戻り、外部 Volume の検証が成功することを確認します。

```sql
SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('my_gcs_01_vol');
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/391dce60-0aeb-4373-bef8-7615c401c37c.png)

## Snowflake-managed Apache Iceberg テーブルを作成し BigQuery に登録

### Snowflake-managed Apache Iceberg テーブルの作成

```sql
CREATE OR REPLACE ICEBERG TABLE customer_iceberg (
    c_custkey INTEGER,
    c_name STRING,
    c_address STRING,
    c_nationkey INTEGER,
    c_phone STRING,
    c_acctbal INTEGER,
    c_mktsegment STRING,
    c_comment STRING
)
    CATALOG = 'SNOWFLAKE'
    EXTERNAL_VOLUME = 'my_gcs_01_vol'
    BASE_LOCATION = 'customer_iceberg'
    AS
    SELECT 
      60001 AS C_CUSTKEY,
      'Customer#000060001' AS C_NAME,
      '9Ii4zQn9cX' AS C_ADDRESS,
      14 AS C_NATIONKEY,
      '24-678-784-9652' AS C_PHONE,
      9957.56 AS C_ACCTBAL,
      'HOUSEHOLD' AS C_MKTSEGMENT,
      'l theodolites boost slyly at the platelets: permanently ironic packages wake slyly pend' AS C_COMMENT;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/18dffb05-366d-4698-b5ab-82eac59a59d5.png)

```sql
SELECT * FROM customer_iceberg LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5ac4f155-abeb-4931-995b-2f39207d7538.png)

GCS 上に Apache Iceberg テーブルのディレクトリが作成されていることを確認します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/34200b9b-fe2f-4dc0-b563-101c38969263.png)

### BigQuery にて Apache Iceberg 用の BigLake 外部テーブルを作成

Snowflake でテーブルの最新のメタデータファイルのパスを取得します。Snowflake ではプロトコルが `gcs` となっているため、`gs` に修正する必要があります。

```sql
SELECT 
    JSON_EXTRACT_PATH_TEXT(
        SYSTEM$GET_ICEBERG_TABLE_INFORMATION('CUSTOMER_ICEBERG'),
        'metadataLocation'
    ) AS metadataLocation;
```

> gcs://manabianqiita/snowflake/customer_iceberg/metadata/00001-7cdb0a89-9264-4347-8e7a-6804e039a6c8.metadata.json

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4c7e9ba4-405f-47bf-9afc-441af883f92f.png)

Apache Iceberg 用の BigLake 外部テーブルを作成します。`uris` には取得したパスを指定しつつ、`gcs` を `gs` に修正してください。

```sql
CREATE OR REPLACE EXTERNAL TABLE test.customer_iceberg
OPTIONS(
  format = 'ICEBERG',
  uris = ['gs://manabianqiita/snowflake/customer_iceberg/metadata/00001-7cdb0a89-9264-4347-8e7a-6804e039a6c8.metadata.json']
);
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7651a6f9-d426-46d3-97b7-5ea19e1a5ca8.png)

データが参照できることを確認します。

```sql
SELECT * FROM test.customer_iceberg;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/aff95ba3-3d12-4828-9e25-835223cddee4.png)

## Snowflake-managed Apache Iceberg テーブルの変更内容を BigQuery に反映

### Snowflake-managed Apache Iceberg テーブルにデータを追加

```sql
INSERT INTO customer_iceberg
SELECT 
  60002,
  'Customer#000060002',
  'ThGBMjDwKzkoOxhz',
  15,
  '25-782-500-8435',
  742.46,
  'BUILDING',
  'beans. fluffily regular packages'
UNION ALL
SELECT 
  60003,
  'Customer#000060003',
  'Ed hbPtTXMTAsgGhCr4HuTzK,Md2',
  16,
  '26-859-847-7640',
  2526.92,
  'BUILDING',
  'fully pending deposits sleep quickly. blithely unusual accounts across the blithely bold requests are quickly';
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/dd63aaa2-ddbe-4716-a8b0-c1508648a1b1.png)

### Apache Iceberg 用の BigLake 外部テーブルのメタデータ更新

Snowflake でテーブルの最新のメタデータファイルのパスを再取得します。

```sql
SELECT 
    JSON_EXTRACT_PATH_TEXT(
        SYSTEM$GET_ICEBERG_TABLE_INFORMATION('CUSTOMER_ICEBERG'),
        'metadataLocation'
    ) AS metadataLocation;
```

> gcs://manabianqiita/snowflake/customer_iceberg/metadata/00002-d56a73ad-6788-4b8e-bbd1-494f3db9913d.metadata.json

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/0a57a80e-c968-49ac-87b5-65f42291da9f.png)

メタデータ更新用の JSON ファイルを作成します。`uris` の値は上記で確認したものを使用し、`gcs` を `gs` に修正してください。

```bash
bq mkdef --source_format=ICEBERG \
"gs://manabianqiita/snowflake/customer_iceberg/metadata/00002-d56a73ad-6788-4b8e-bbd1-494f3db9913d.metadata.json" > biglake_version
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c9e9e89b-902c-43f5-984a-df13ac5c3ae9.png)

作成した JSON 定義ファイルを元に、以下のコマンドでテーブルのメタデータを更新します。`{project_id}` の部分はご自分のプロジェクト ID に置き換えてください。

```bash
bq update --autodetect_schema --external_table_definition=biglake_version \
{project_id}:test.customer_iceberg
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8f458022-61b4-4cdd-876c-978b6dfb1262.png)

更新後、BigQuery から最新のデータを取得できることを確認します。

```sql
SELECT * FROM test.customer_iceberg;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/23a77cdc-b4d7-400e-acee-ac532a73b736.png)

:::note info
本記事では bq コマンドを使用していますが、運用時には Snowflake から取得したメタデータファイルのパスを、REST API で更新するプログラムを実装することをおすすめします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/b14d0e69-37b1-40cb-8f36-eb4814f9db76.png)

出所：https://cloud.google.com/bigquery/docs/iceberg-external-tables?hl=ja#api
:::