Snowflake-managed Apache Iceberg™ table におけるクローニング機能を試してみた

## 概要

Snowflake-managed Apache Iceberg™ table にてサポートされているクローニング機能を検証しました。 Apache Iceberg の Branching や Tagging などが利用されているのかと予測していたのですが、 同一のテーブルのディレクトリ内で異なる metadata file により管理されるような仕様でした。検証コードとその結果を共有します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7a6c95f3-b4f9-4b29-9754-a8998aacfafb.png)

出所：[Apache Iceberg™ テーブル | Snowflake Documentation](https://docs.snowflake.com/ja/user-guide/tables-iceberg)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/fee88250-6db6-4c6a-b248-d357d5700b83.png)



## 事前準備

下記記事における環境構築の手順を実施してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5a727271-1090-4694-bf8c-b2a493fd0e56.png)

出所：[Snowflake Catalog における Iceberg テーブルの基本的な操作手順 #Spark - Qiita](https://qiita.com/manabian/items/69761bde791d0f014246)

## 検証コードと結果

### データベースを作成

```sql
CREATE DATABASE IF NOT EXISTS OPEN_CATALOG_TUTORIAL_DB;
USE DATABASE OPEN_CATALOG_TUTORIAL_DB;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/58e577d2-1ea4-4b32-8edf-0b595fafe775.png)

### クローン元のテーブルを作成

```sql
CREATE OR REPLACE ICEBERG TABLE nation__base 
    CATALOG = 'SNOWFLAKE'
    EXTERNAL_VOLUME = 'my_azure_sf_volume'
    BASE_LOCATION = 'nation__base'
    AS SELECT
        N_NATIONKEY,
        N_NAME
        FROM
            snowflake_sample_data.tpch_sf1.nation;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2a9ea68b-2483-483a-b94d-eda9527bbfc3.png)


### クローンを実施

```sql
CREATE OR REPLACE ICEBERG TABLE nation__clone
    CLONE nation__base;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c87efe96-c708-4b3b-ae3e-371a67e21cb5.png)

### 2 つのテーブルの metadata file のパスを確認

`nation__base`テーブルと`nation__clone`テーブルの metatadata file のパスが異なることを確認できます。

```sql
SET TABLE_NAME_1 = 'nation__base';
SET TABLE_NAME_2 = 'nation__clone';

WITH table_1 AS (
    SELECT PARSE_JSON(SYSTEM$GET_ICEBERG_TABLE_INFORMATION($TABLE_NAME_1)) AS JSON_DATA
),
table_2 AS (
    SELECT PARSE_JSON(SYSTEM$GET_ICEBERG_TABLE_INFORMATION($TABLE_NAME_2)) AS JSON_DATA
)
SELECT
    $TABLE_NAME_1 AS table_name,    
    JSON_DATA:metadataLocation::STRING AS metadata_location,
    JSON_DATA:status::STRING AS status
FROM table_1
UNION ALL
SELECT
    $TABLE_NAME_2 AS table_name,    
    JSON_DATA:metadataLocation::STRING AS metadata_location,
    JSON_DATA:status::STRING AS status
FROM table_2;
```

| TABLE_NAME    | METADATA_LOCATION                                            | STATUS  |
| ------------- | ------------------------------------------------------------ | ------- |
| nation__base  | azure://snowflakeicebergqiita.blob.core.windows.net/snowflake/snowflakecatalog/nation__base/metadata/00001-a97cfa17-4b8e-496b-aaa7-6ba0dc6a664f.metadata.json |  success       |
| nation__clone | azure://snowflakeicebergqiita.blob.core.windows.net/snowflake/snowflakecatalog/nation__base/metadata/00001-b685bd45-4174-4f0b-a6b7-7e97546ed3f1.metadata.json | success |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d5f6cd33-59c7-4242-8a1e-768a99f5b05f.png)

Azure Storage 上でも 2 つのファイルがあることを確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9ae6078e-9c1b-41a9-86fb-8e7696037fbd.png)

### 片方のテーブルを DELETE した際の動作を確認

```sql
DELETE FROM nation__clone
    WHERE N_NATIONKEY > 19;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/93d7cc4e-28d3-4e60-9ce8-9b077b9b75d0.png)

`nation__clone`テーブルの件数が減っていることを確認します。

```sql
SELECT COUNT(*) FROM nation__base;
SELECT COUNT(*) FROM nation__clone;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8a0debbe-fb89-4666-be5b-2762e0a3be18.png)

DELETE したテーブルの metadata file が`0002`となっていることを確認できます。

```sql
SET TABLE_NAME_1 = 'nation__base';
SET TABLE_NAME_2 = 'nation__clone';

WITH table_1 AS (
    SELECT PARSE_JSON(SYSTEM$GET_ICEBERG_TABLE_INFORMATION($TABLE_NAME_1)) AS JSON_DATA
),
table_2 AS (
    SELECT PARSE_JSON(SYSTEM$GET_ICEBERG_TABLE_INFORMATION($TABLE_NAME_2)) AS JSON_DATA
)
SELECT
    $TABLE_NAME_1 AS table_name,    
    JSON_DATA:metadataLocation::STRING AS metadata_location,
    JSON_DATA:status::STRING AS status
FROM table_1
UNION ALL
SELECT
    $TABLE_NAME_2 AS table_name,    
    JSON_DATA:metadataLocation::STRING AS metadata_location,
    JSON_DATA:status::STRING AS status
FROM table_2;
```

| TABLE_NAME    | METADATA_LOCATION                                            | STATUS  |
| ------------- | ------------------------------------------------------------ | ------- |
| nation__base  | azure://snowflakeicebergqiita.blob.core.windows.net/snowflake/snowflakecatalog/nation__base/metadata/00001-a97cfa17-4b8e-496b-aaa7-6ba0dc6a664f.metadata.json | success        |
| nation__clone | azure://snowflakeicebergqiita.blob.core.windows.net/snowflake/snowflakecatalog/nation__base/metadata/00002-00e930be-3996-45da-81c2-bd572a352f3b.metadata.json | success |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/466d1f0b-dff6-43ab-8863-29fb744c4545.png)