Community Articles

Find and share helpful community-sourced technical articles.
Announcements
Celebrating as our community reaches 100,000 members! Thank you!
avatar
Expert Contributor

Purpose: Access CDW Iceberg table with Snowflake

Architecture

Arch.png

Image source: Configure an external volume for Iceberg tables

Initiate

Configure an External Volume in Snowflake

Step 1: Configure access permissions for the S3 bucket

  1. Create a policy in AWS IAM. I created a policy called zzeng-Snowflake-ext:
    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                    "s3:PutObject",
                    "s3:GetObject",
                    "s3:GetObjectVersion",
                    "s3:DeleteObject",
                    "s3:DeleteObjectVersion"
                ],
                "Resource": "arn:aws:s3:::<my-bucket>/data/zzeng/*"
            },
            {
                "Effect": "Allow",
                "Action": [
                    "s3:ListBucket",
                    "s3:GetBucketLocation"
                ],
                "Resource": "arn:aws:s3:::<my-bucket>",
                "Condition": {
                    "StringLike": {
                        "s3:prefix": [
                            "data/zzeng/*"
                        ]
                    }
                }
            }
        ]
    }
  2. Set it in the AWS console
    image.png

Step 2: Create an IAM role in AWS

  • zzeng-Snowflake-ext-role:
    03-Trustimage.pngzzeng_2-1710469862448.png

Step 3: Grant privileges required for SSE-KMS encryption to the IAM role (optional)

(Ref: Grant privileges required for SSE-KMS encryption to the IAM role (optional))

Skip.

Step 4: Create an external volume in Snowflake

SQL in Snowflake:

 

CREATE OR REPLACE EXTERNAL VOLUME extIcebergVolC
   STORAGE_LOCATIONS =
      (
         (
            NAME = 'zzeng-iceberg-se-s3-ap-northeast-1'
            STORAGE_PROVIDER = 'S3'
            STORAGE_BASE_URL = 's3://<my-bucket>/data/zzeng/'
            STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<my-AWS-id*****>:role/zzeng-Snowflake-ext-role'
         )
      );

 

Step 5: Retrieve the AWS IAM user for your Snowflake account

 

DESC EXTERNAL VOLUME extIcebergVolC;

 

Result:

 

zzeng#COMPUTE_WH@ZZENG.PUBLIC>DESC EXTERNAL VOLUME extIcebergVolC;
+-------------------+--------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| parent_property   | property           | property_type | property_value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | property_default |
|-------------------+--------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------|
|                   | ALLOW_WRITES       | Boolean       | true                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | true             |
| STORAGE_LOCATIONS | STORAGE_LOCATION_1 | String        | {"NAME":"zzeng-iceberg-se-s3-ap-northeast-1","STORAGE_PROVIDER":"S3","STORAGE_BASE_URL":"s3://<my-bucket-id>/data/zzeng/","STORAGE_ALLOWED_LOCATIONS":["s3://<my-bucket-id>/data/zzeng/*"],"STORAGE_REGION":"us-east-2","PRIVILEGES_VERIFIED":true,"STORAGE_AWS_ROLE_ARN":"<STORAGE_AWS_ROLE_ARN>","STORAGE_AWS_IAM_USER_ARN":"<STORAGE_AWS_ROLE_ARN>","STORAGE_AWS_EXTERNAL_ID":"<a long string for STORAGE_AWS_EXTERNAL_ID>","ENCRYPTION_TYPE":"NONE","ENCRYPTION_KMS_KEY_ID":""} |                  |
| STORAGE_LOCATIONS | ACTIVE             | String        | zzeng-iceberg-se-s3-ap-northeast-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |                  |
+-------------------+--------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
3 Row(s) produced. Time Elapsed: 0.949s
zzeng#COMPUTE_WH@ZZENG.PUBLIC>

 

Take a memo for STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID. 

Step 6: Grant the IAM user permissions to access bucket objects

  • Modify the trustship in IAM, use the value in Step 5:
    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Sid": "",
          "Effect": "Allow",
          "Principal": {
            "AWS": "<snowflake_user_arn>"
          },
          "Action": "sts:AssumeRole",
          "Condition": {
            "StringEquals": {
              "sts:ExternalId": "<snowflake_external_id>"
            }
          }
        }
      ]
    }


    zzeng_1-1710469836741.png

 

 

 

Create an Iceberg Table in CDW

  • Create folder:
    s3a://${my-test-bucket}/data/${user_id}/airlines/airlines​
  • use my own value as the ${my-test-bucket} and ${user_id}:
    CREATE DATABASE ${user_id}_airlines_ice;
    drop table if exists ${user_id}_airlines_ice.airlines;
    
    CREATE EXTERNAL TABLE ${user_id}_airlines_ice.airlines (code string, description string) 
    STORED BY ICEBERG
    STORED AS PARQUET
    LOCATION 's3a://${cdp_env_bucket}/data/${user_id}/airlines/airlines'
    tblproperties("format-version"="2",'external.table.purge'='true');
    
    INSERT INTO ${user_id}_airlines_ice.airlines
      SELECT * FROM ${user_id}_airlines_csv.airlines_csv;
      
      
    select * from ${user_id}_airlines_ice.airlines;
      
    select count(*) from ${user_id}_airlines_ice.airlines;

    zzeng_1-1710469946720.png
    zzeng_2-1710469960669.png

  • Check my DDL:

     

 

 

Create an Iceberg Table in Snowflake, pointing to the exist table in CDW

SQL:

 

CREATE OR REPLACE ICEBERG TABLE airlines
  CATALOG='zzengIcebergCatalogInt'
  EXTERNAL_VOLUME='extIcebergVolC'
  BASE_LOCATION='airlines/airlines'
  METADATA_FILE_PATH='metadata/00001-7e28a998-42f9-4466-8884-32d450af5c85.metadata.json'
;

 

 

Check the value

  • In Snowflake::snowflake:
    zzeng#COMPUTE_WH@ZZENG.PUBLIC>select count(*) from AIRLINES;
    +----------+
    | COUNT(*) |
    |----------|
    |     1491 |
    +----------+
    1 Row(s) produced. Time Elapsed: 0.393s
    zzeng#COMPUTE_WH@ZZENG.PUBLIC>select * from AIRLINES limit 3;
    +------+--------------------+
    | CODE | DESCRIPTION        |
    |------+--------------------|
    | 02Q  | Titan Airways      |
    | 04Q  | Tradewind Aviation |
    | 05Q  | "Comlux Aviation   |
    +------+--------------------+
    3 Row(s) produced. Time Elapsed: 4.705s
    zzeng#COMPUTE_WH@ZZENG.PUBLIC>
  • In CDW Hive HUE:
    select count(*) from ${user_id}_airlines_ice.airlines;

    zzeng_2-1710470163298.png

 

 

Modify Data

INSERT DATA

It is mentioned that You can use INSERT and UPDATE statements to modify an Iceberg table that uses Snowflake as the catalog, I got an Error in Snowflake when inserting it into the Iceberg table:

zzeng#COMPUTE_WH@ZZENG.PUBLIC>INSERT INTO airlines (code, description) VALUES
                                   ('A1', 'Airline 1 Description'),
                                   ('A2', 'Airline 2 Description'),
                                   ('A3', 'Airline 3 Description'),
                                   ('A4', 'Airline 4 Description'),
                                   ('A5', 'Airline 5 Description'),
                                   ('A6', 'Airline 6 Description'),
                                   ('A7', 'Airline 7 Description'),
                                   ('A8', 'Airline 8 Description'),
                                   ('A9', 'Airline 9 Description'),
                                   ('A10', 'Airline 10 Description'),
                                   ('A11', 'Airline 11 Description'),
                                   ('A12', 'Airline 12 Description'),
                                   ('A13', 'Airline 13 Description'),
                                   ('A14', 'Airline 14 Description'),
                                   ('A15', 'Airline 15 Description'),
                                   ('A16', 'Airline 16 Description'),
                                   ('A17', 'Airline 17 Description'),
                                   ('A18', 'Airline 18 Description'),
                                   ('A19', 'Airline 19 Description'),
                                   ('A20', 'Airline 20 Description');
091357 (42601): SQL Compilation error: Iceberg table AIRLINES with an external catalog integration is a read-only table and cannot be modified
zzeng#COMPUTE_WH@ZZENG.PUBLIC>

 

 

 

Hive Insert

 

INSERT INTO `zzeng_airlines_ice`.`airlines` (code, description) VALUES
('A1', 'Airline 1 Description'),
('A2', 'Airline 2 Description'),
('A3', 'Airline 3 Description'),
('A4', 'Airline 4 Description'),
('A5', 'Airline 5 Description'),
('A6', 'Airline 6 Description'),
('A7', 'Airline 7 Description'),
('A8', 'Airline 8 Description'),
('A9', 'Airline 9 Description'),
('A10', 'Airline 10 Description'),
('A11', 'Airline 11 Description'),
('A12', 'Airline 12 Description'),
('A13', 'Airline 13 Description'),
('A14', 'Airline 14 Description'),
('A15', 'Airline 15 Description'),
('A16', 'Airline 16 Description'),
('A17', 'Airline 17 Description'),
('A18', 'Airline 18 Description'),
('A19', 'Airline 19 Description'),
('A20', 'Airline 20 Description');

 

zzeng_4-1710470285696.png

Refresh the metadata for an Iceberg table created from files in object storage

Issue: Hive changed the Iceberg table, but after that, even when I ran the Snowflake query, the data was not updated:

 

zzeng#COMPUTE_WH@ZZENG.PUBLIC>select count(*) from AIRLINES;
+----------+
| COUNT(*) |
|----------|
|     1491 |
+----------+
1 Row(s) produced. Time Elapsed: 0.678s
zzeng#COMPUTE_WH@ZZENG.PUBLIC>

 

Spoiler

In Snowflake, The metadata files do not identify the most recent snapshot of an Iceberg table.

Ref URL: Iceberg tables

I have to alter my Snowflake Iceberg table definition manually:

zzeng_0-1710470370313.png

Then refresh the metadata:

 

ALTER ICEBERG TABLE AIRLINES REFRESH 'metadata/00002-c33ae888-1af3-4f64-830b-eac9e0a95983.metadata.json';

 

Result:

 

zzeng#COMPUTE_WH@ZZENG.PUBLIC>ALTER ICEBERG TABLE AIRLINES REFRESH 'metadata/00002-c33ae888-1af3-4f64-830b-eac9e0a95983.metadata.json';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 10.199s
zzeng#COMPUTE_WH@ZZENG.PUBLIC>select count(*) from AIRLINES;
+----------+
| COUNT(*) |
|----------|
|     1511 |
+----------+
1 Row(s) produced. Time Elapsed: 0.204s
zzeng#COMPUTE_WH@ZZENG.PUBLIC>

 

Considerations and limitations

Iceberg tables

The following is an excerpt from Snowflake documents, where considerations and limitations apply to Iceberg tables, and are subject to change:

Iceberg

  • Versions 1 and 2 of the Apache Iceberg specification are supported, excluding the following features:

    • Row-level deletes (either position deletes or equality deletes).

    • Using the history.expire.min-snapshots-to-keep table property to specify the default minimum number of snapshots to keep. For more information, see Metadata and snapshots.

  • Iceberg partitioning with the bucket transform function impacts performance for queries that use conditional clauses to filter results.

  • Iceberg tables created from files in object storage aren’t supported if the following conditions are true:

    • The table contains a partition spec that defines an identity transform.

    • The source column of the partition spec does not exist in a Parquet file.

  • For Iceberg tables that are not managed by Snowflake, time travel to any snapshot generated after table creation is supported as long as you periodically refresh the table before the snapshot expires.

File formats

  • Support is limited to Apache Parquet files.

  • Parquet files that use the unsigned integer logical type are not supported.

External volumes

  • You must access the cloud storage locations in external volumes using direct credentials. Storage integrations are not supported.

  • The trust relationship must be configured separately for each external volume that you create.

Metadata files

  • The metadata files do not identify the most recent snapshot of an Iceberg table.

  • You cannot modify the location of the data files or snapshot using the ALTER ICEBERG TABLE command. To modify either of these settings, you must recreate the table (using the CREATE OR REPLACE ICEBERG TABLE syntax).

 

527 Views