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

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 query. 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 querying Iceberg tables by using an external query engine¶

To query 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

Step 1: Create Iceberg tables¶

In this step, you create Snowflake-managed Iceberg tables that use Snowflake as the catalog, so you can query 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 query 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 instructions, see Configuring access control.

The following example sets up a service account user in Snowflake with read-only access to an Iceberg table. It creates a DATA_ENGINEER role, grants the role usage and monitor privileges on the ICEBERG_TEST_DB database and its PUBLIC schema, grants SELECT privileges on the TEST_TABLE Iceberg table, creates a service user named HORIZON_REST_SRV_ACCOUNT_USER, and assigns the DATA_ENGINEER role to that user.

CREATE OR REPLACE ROLE DATA_ENGINEER;

GRANT USAGE,MONITOR ON DATABASE ICEBERG_TEST_DB TO ROLE DATA_ENGINEER;
GRANT USAGE,MONITOR 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;

For more information about access control in Snowflake, see Overview of Access Control.

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 query 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: Query Iceberg tables¶

This step provides the following code examples for using Apache Spark™ to query Iceberg tables:

• Show namespaces

• Use namespaces

• Show tables

• Query a table

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()

Considerations for querying Iceberg tables with an external query engine¶

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

• Iceberg

  • For tables in Snowflake:

    • Only Snowflake-managed Iceberg tables are supported.

    • 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

  • You can query but can’t write to Iceberg tables.

  • The external reads are supported only on Iceberg version 2 or earlier.

• 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™.

• 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.

• Listings:

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

• Clouds:

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

  • 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.

  • 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.