Access Apache Iceberg™ tables with an external engine through Snowflake Horizon Catalog¶

Query Iceberg tables¶

By connecting an external query engine to Iceberg tables through Horizon Catalog, you can perform the following tasks:

• Use any external query engine that supports the open Iceberg REST protocol to query these tables, such as Apache Spark™.

• Query any existing and new Snowflake-managed Iceberg tables in a new or existing Snowflake account by using a single Horizon Catalog endpoint.

• Query the tables by using your existing users, roles, policies, and authentication in Snowflake.

• Use vended credentials.

For more information about Snowflake Horizon Catalog, see Snowflake Horizon Catalog.

Write to Iceberg tables¶

Writing to Iceberg tables by using an external query engine through Horizon Catalog is in public preview. To write to tables, follow the workflow for accessing Iceberg tables by using an external query engine. When you configure access control, ensure that you configure write access to your tables.

Then write to Iceberg tables.

The following diagram shows external query engines reading and writing to Snowflake-managed Iceberg tables through Horizon Catalog and Snowflake reading and writing to these tables:

Billing¶

• The Horizon Iceberg REST Catalog API is available in all Snowflake editions.

• The API requests are billed as 0.5 credit per million calls and charged as Cloud Services.

• For cross-region data access, standard cross-region data egress charges as stated in the Snowflake Service Consumption Table are applicable.

Supported external engines and catalogs¶

The following tables, although not exhaustive, show many external engines and catalogs that integrate with the Horizon Iceberg REST Catalog API. This integration enables access to Snowflake managed Iceberg tables through external systems.

Prerequisites¶

Retrieve the account identifier for your Snowflake account that contains the Iceberg tables that you want to access. For instructions, see Account identifiers. You specify this identifier when you connect an external query engine to your Iceberg tables.

SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();

(Optional) Private connectivity¶

For secure connectivity, consider configuring Inbound and Outbound private connectivity for your Snowflake account while you access the Horizon Catalog endpoint.

Workflow for accessing Iceberg tables by using an external query engine¶

To access Iceberg tables by using an external query engine, complete the following steps:

1. Create Iceberg tables

2. Configure access control

3. Obtain an access token for authentication

4. Verify access token permissions

5. (Optional) Configure data protection policies

6. Connect an external query engine to Iceberg tables through Horizon Catalog

7. Query Iceberg tables or write to Iceberg tables

Step 1: Create Iceberg tables¶

In this step, you create Snowflake-managed Iceberg tables that use Snowflake as the catalog, so you can access them with an external query engine. For instructions, see the following topics:

• Tutorial: Create your first Apache Iceberg™ table: A tutorial that shows how to create a database, create a Snowflake-managed Iceberg table, and load data into the table.

• Create a Snowflake-managed Iceberg table: Example code for creating a Snowflake-managed Iceberg table.

Step 2: Configure access control¶

In this step, you configure access control for the Snowflake-managed Iceberg tables that you want to access with an external query engine. For example, you can set up the following roles in Snowflake:

• data_engineer role, which has access to all schemas and all Snowflake-managed Iceberg tables in a database.

• data_analyst role, which has access to one schema in the database and only access to two Snowflake-managed Iceberg tables within that schema.

For more information, see the following sections:

• Configure read access to your Iceberg tables

• Configure write access to your Iceberg tables

CREATE OR REPLACE ROLE data_engineer;

GRANT USAGE ON DATABASE iceberg_test_db TO ROLE data_engineer;
GRANT USAGE ON SCHEMA iceberg_test_db.public TO ROLE data_engineer;

GRANT SELECT ON TABLE iceberg_test_db.public.test_table TO ROLE data_engineer;

CREATE OR REPLACE USER horizon_rest_srv_account_user TYPE=SERVICE DEFAULT_ROLE=data_engineer;

GRANT ROLE data_engineer TO USER horizon_rest_srv_account_user;

GRANT SELECT ON FUTURE ICEBERG TABLES IN SCHEMA my_db.my_schema TO ROLE data_engineer;

Step 3: Obtain an access token for authentication¶

In this step, you obtain an access token, which you must have to authenticate to the Horizon Catalog endpoint for your Snowflake account. You need to obtain an access token for each user — service or human — and role that is configured with access to Snowflake-managed Iceberg tables. For example, you need to obtain one access token for a user with DATA_ENGINEER role and another user with a DATA_ANALYST role.

You specify this access token later when you connect an external query engine to Iceberg tables through Horizon Catalog.

You can obtain an access token by using one of the following authentication options:

• External OAuth

• Key-pair authentication

• Programmatic access token (PAT)

GRANT ROLE ENGINEER to user my_service_user;

snowsql --private-key-path "<private_key_file>" \
  --generate-jwt \
  -h "<account_identifier>.snowflakecomputing.com" \
  -a "<account_locator>" \
  -u "<user_name>"

curl -i --fail -X POST "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens" \
 --header 'Content-Type: application/x-www-form-urlencoded' \
 --data-urlencode 'grant_type=client_credentials' \
 --data-urlencode 'scope=session:role:<role>' \
 --data-urlencode 'client_secret=<JWT_token>'

ALTER USER IF EXISTS HORIZON_REST_SRV_ACCOUNT_USER
ADD PAT HORIZON_REST_SRV_ACCOUNT_USER_PAT
  DAYS_TO_EXPIRY = 7
  ROLE_RESTRICTION = 'DATA_ENGINEER'
  COMMENT = 'HORIZON REST API PAT FOR SERVICE ACCOUNT';

curl -i --fail -X POST "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens" \
 --header 'Content-Type: application/x-www-form-urlencoded' \
 --data-urlencode 'grant_type=client_credentials' \
 --data-urlencode 'scope=session:role:<role>' \
 --data-urlencode 'client_secret=<PAT_token>'

Step 4: Verify access token permissions¶

In this step, you verify the permissions for the access token that you obtained in the previous step.

• Verify access to the Horizon IRC endpoint

• Retrieve the metadata for a table

curl -i --fail -X GET "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog/v1/config?warehouse=<database_name>" \
-H "Authorization: Bearer <access_token>" \
-H "Content-Type: application/json"

{
  "defaults": {
    "default-base-location": ""
  },
  "overrides": {
    "prefix": "MY-DATABASE"
  }
}

curl -i --fail -X GET "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog/v1/<database_name>/namespaces/<namespace_name>/tables/<table_name>" \
 -H "Authorization: Bearer <access_token>" \
 -H "Content-Type: application/json"

(Optional) Step 5: Configure data protection policies¶

In this step, you configure data protection policies for Iceberg tables. If you don’t have tables that you need to protect with Snowflake data policies, you can proceed to the next step.

For instructions on how to configure data protection policies, see Configure data protection policies on Iceberg tables accessed over Horizon Iceberg REST API and using Apache Spark™.

Step 6: Connect an external query engine to Iceberg tables through Horizon Catalog¶

In this step, you connect an external query engine to Iceberg tables through Horizon Catalog. With this connection, you can access the tables by using the external query engine.

The external engines use the Apache Iceberg™ REST endpoint exposed by Snowflake. For your Snowflake account, this endpoint is in the following format:

https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog

The example code in this step shows how to set up a connection in Spark, and the example code is in PySpark. For more information, see the following sections:

• Connect by using External OAuth or key pair authentication

• Connect by using a programmatic access token (PAT)

# Snowflake Horizon Catalog Configuration, change as per your environment

CATALOG_URI = "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog"
HORIZON_SESSION_ROLE = f"session:role:<role>"
CATALOG_NAME = "<database_name>" #provide in UPPER CASE

# Cloud Service Provider Region Configuration (where the Iceberg data is stored)
REGION = "eastus2"

# Paste the External Oauth Access token that you generated in Snowflake here
ACCESS_TOKEN = "<your_access_token>"

# Iceberg Version
ICEBERG_VERSION = "1.9.1"

def create_spark_session():
  """Create and configure Spark session for Snowflake Iceberg access."""
  spark = (
      SparkSession.builder
      .appName("SnowflakeIcebergReader")
      .master("local[*]")

# JAR Dependencies for Iceberg and Azure
      .config(
          "spark.jars.packages",
          f"org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:{ICEBERG_VERSION},"
          f"org.apache.iceberg:iceberg-aws-bundle:{ICEBERG_VERSION}"
          # for Azure storage, use the below package and comment above azure bundle
          # f"org.apache.iceberg:iceberg-azure-bundle:{ICEBERG_VERSION}"
      )

      # Iceberg SQL Extensions
      .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions")
      .config("spark.sql.defaultCatalog", CATALOG_NAME)

      # Horizon REST Catalog Configuration
      .config(f"spark.sql.catalog.{CATALOG_NAME}", "org.apache.iceberg.spark.SparkCatalog")
      .config(f"spark.sql.catalog.{CATALOG_NAME}.type", "rest")
      .config(f"spark.sql.catalog.{CATALOG_NAME}.uri", CATALOG_URI)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.warehouse", CATALOG_NAME)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.token", ACCESS_TOKEN)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.scope", HORIZON_SESSION_ROLE)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.client.region", REGION)

      # Required for vended credentials
      .config(f"spark.sql.catalog.{CATALOG_NAME}.header.X-Iceberg-Access-Delegation", "vended-credentials")
      .config("spark.sql.iceberg.vectorization.enabled", "false")
      .getOrCreate()
  )
  spark.sparkContext.setLogLevel("ERROR")
  return spark

# Snowflake Horizon Catalog Configuration, change as per your environment

CATALOG_URI = "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog"
HORIZON_SESSION_ROLE = f"session:role:<role>"
CATALOG_NAME = "<database_name>" #provide in UPPER CASE

# Cloud Service Provider Region Configuration (where the Iceberg data is stored)
REGION = "eastus2"

# Paste the PAT you generated in Snowflake here
PAT_TOKEN = "<your_PAT_token>"

# Iceberg Version
ICEBERG_VERSION = "1.9.1"

def create_spark_session():
  """Create and configure Spark session for Snowflake Iceberg access."""
  spark = (
      SparkSession.builder
      .appName("SnowflakeIcebergReader")
      .master("local[*]")

# JAR Dependencies for Iceberg and Azure
      .config(
          "spark.jars.packages",
          f"org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:{ICEBERG_VERSION},"
          f"org.apache.iceberg:iceberg-aws-bundle:{ICEBERG_VERSION}"
          # for Azure storage, use the below package and comment above azure bundle
          # f"org.apache.iceberg:iceberg-azure-bundle:{ICEBERG_VERSION}"
      )

      # Iceberg SQL Extensions
      .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions")
      .config("spark.sql.defaultCatalog", CATALOG_NAME)

      # Horizon REST Catalog Configuration
      .config(f"spark.sql.catalog.{CATALOG_NAME}", "org.apache.iceberg.spark.SparkCatalog")
      .config(f"spark.sql.catalog.{CATALOG_NAME}.type", "rest")
      .config(f"spark.sql.catalog.{CATALOG_NAME}.uri", CATALOG_URI)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.warehouse", CATALOG_NAME)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.credential", PAT_TOKEN)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.scope", HORIZON_SESSION_ROLE)
      .config(f"spark.sql.catalog.{CATALOG_NAME}.client.region", REGION)

      # Required for vended credentials
      .config(f"spark.sql.catalog.{CATALOG_NAME}.header.X-Iceberg-Access-Delegation", "vended-credentials")
      .config("spark.sql.iceberg.vectorization.enabled", "false")
      .getOrCreate()
  )
  spark.sparkContext.setLogLevel("ERROR")
  return spark

Step 7: Access Iceberg tables¶

This section includes code examples for using Apache Spark™ to query and write to Iceberg tables.

spark.sql("show namespaces").show()

spark.sql("use namespace <your_schema_name_in_snowflake>")

spark.sql("show tables").show()

spark.sql("use namespace spark_demo")
spark.sql("select * from <your_table_name_in_snowflake>").show()

spark.sql("CREATE TABLE MY_TABLE (COLUMN1 INT) USING ICEBERG").show();

spark.sql("INSERT INTO MY_TABLE VALUES (600)").show()

spark.sql("ALTER TABLE MY_TABLE ADD COLUMN COLUMN2 INT").show()

spark.sql("UPDATE MY_TABLE SET COLUMN2 = 10 WHERE COLUMN1 = 100").show()

spark.sql("DELETE FROM MY_TABLE WHERE COLUMN2 = 10").show()

spark.sql("TRUNCATE TABLE MY_TABLE").show()

spark.sql("ALTER TABLE MY_TABLE RENAME TO MY_NEW_TABLE")

spark.sql("DROP TABLE MY_TABLE")

Considerations for accessing Iceberg tables with an external query engine¶

This section lists the considerations for accessing, querying, and writing to Iceberg tables with an external query engine.

Consider the following items when you access Iceberg tables with an external query engine:

• Iceberg

  • For tables in Snowflake:

    • Only Snowflake-managed Iceberg tables are supported.

• Listings:

  • Iceberg tables that you share through auto-fulfillment for listings aren’t accessible through the consumer account’s Horizon Iceberg REST Catalog API.

• Network and private connectivity:

  • Using network policies that are set at the user level isn’t supported with this feature.

  • For Snowflake-managed network rules, egress IP addresses that are static aren’t supported.

  • Explicitly granting the Horizon Catalog endpoint access to your storage accounts isn’t supported. We recommend that you use private connectivity for secure connectivity from external engines to Horizon Catalog and from Horizon Catalog to your storage account.

• Clouds:

  • Commercial: This feature is only supported for Snowflake-managed Iceberg tables that are stored on Amazon S3, Google Cloud, or Microsoft Azure for all commercial cloud regions. S3-compatible non-AWS storage isn’t yet supported.

  • FedRAMP (Moderate): This feature is supported for Snowflake-managed Iceberg tables that are stored on FedRAMP (Moderate) deployments on AWS Commercial Gov (US) in the us-east-1 and us-west-2 regions.

  • For Iceberg tables stored on Amazon S3:

    • If you want to use SSE-KMS encryption, contact customer support or your account team for assistance with enabling access.

      Note

      Writing to KMS-encrypted external volumes is not supported.

  • For Iceberg tables stored on Azure:

    • Azure Virtual Network (VNet) isn’t supported.

• Authentication:

  • For key-pair authentication, key-pair rotation isn’t supported.

  • Workload identity federation isn’t supported with this feature.

Consider the following items when you query (read) Iceberg tables with an external query engine:

• Iceberg

  • Querying the following tables isn’t supported:

    • Remote tables

    • Snowflake native tables

    • Externally managed Iceberg tables including Delta-based Iceberg tables and Snowflake-managed Iceberg tables that you loaded with data from Iceberg-compatible Parquet data files by using the COPY INTO table command

  • Reading Iceberg v2 tables is supported.

  • Reading Iceberg V3 tables (public preview) is supported for the following capabilities:

    • Variant data type

    • Row lineage

    All other Iceberg V3 capabilities, including default values and the geography data type, aren’t supported.

• Access control:

  • Tables protected by the following fine-grained data policies can be accessed over Apache Spark™ through Snowflake Horizon Catalog:

    • Masking policies

    • Tag-based masking policies

    • Row access policies

    For more information, see Enforce data protection policies when querying Apache Iceberg™ tables from Apache Spark™.

• Cloned and converted tables:

  • Reading cloned or converted tables is not supported with vended credentials. To read these tables, use direct access to object storage.

Consider the following items when you write to Iceberg tables with an external query engine:

• Table operations:

  • You can’t specify a base location with your CREATE TABLE statement.

    When you create a Snowflake-managed table without specifying a base location, Snowflake constructs the following path for your table: STORAGE_BASE_URL/database/schema/table_name.randomId/[data | metadata]/

  • CREATE TABLE AS SELECT (CTAS) from an external engine is not supported.

  • Equality deletes aren’t supported.

  • You can’t write to tables by using row-level deletes; only copy-on-write mode is supported.

  • Creating Iceberg tags and branches isn’t supported.

  • The external engine writes are supported only on Iceberg version 2; writing to Iceberg version 3 (v3) tables (public preview) is not currently supported.

  • Writing to KMS-encrypted external volumes is not supported.

  • Writing to dynamic tables in Snowflake isn’t supported.

  • Writing to shared Iceberg tables isn’t supported.

  • Registering Iceberg tables isn’t supported.

• Maintenance operations

  • You can’t roll back a table to a previous snapshot.

  • The snapshot expiration operation isn’t supported.

  • You can’t upgrade an Iceberg table from v2 to v3.

• Cloned and converted tables:

  • Writing to cloned or converted tables is not supported with vended credentials. To write to these tables, connect your external query engine directly to the object storage where your tables are stored.

  • You can’t write to an Iceberg table that was converted from externally managed to Snowflake managed.

• Streams:

  • On Iceberg V2 tables, copy-on-write operations cause standard streams to represent an updated or relocated row as a DELETE record followed by an INSERT record for the same row.

• Fine-grained access control policies:

  • Writing to tables that have fine-grained access control policies or tags isn’t supported.