0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

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

Last updated at Posted at 2025-03-07

概要

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

image.png

出所:Apache Iceberg™ テーブル | Snowflake Documentation

image.png

事前準備

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

image.png

出所:Snowflake Catalog における Iceberg テーブルの基本的な操作手順 #Spark - Qiita

検証コードと結果

データベースを作成

CREATE DATABASE IF NOT EXISTS OPEN_CATALOG_TUTORIAL_DB;
USE DATABASE OPEN_CATALOG_TUTORIAL_DB;

image.png

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

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

クローンを実施

CREATE OR REPLACE ICEBERG TABLE nation__clone
    CLONE nation__base;

image.png

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

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

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

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

image.png

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

DELETE FROM nation__clone
    WHERE N_NATIONKEY > 19;

image.png

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

SELECT COUNT(*) FROM nation__base;
SELECT COUNT(*) FROM nation__clone;

image.png

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

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?