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.

Before you begin¶

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

Private connectivity (Optional)¶

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. Connect an external query engine to Iceberg tables through Horizon Catalog.

5. 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. 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>'

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"

Step 5: 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. This connection allows you to 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 topics:

• 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 6: 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:

• For tables in Snowflake:

  • Only Snowflake-managed Iceberg tables are supported.

  • Querying remote or externally managed Iceberg tables including Delta Direct and Parquet Direct tables and Snowflake native tables, aren’t supported.

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

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

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

• You can’t query an Iceberg table through the Horizon Iceberg REST API if the following fine-grained access control (FGAC) policies are defined on the table:

  • Row access policies

  • Column-level security

• Snowflake roles that include the hyphen character (-) in the role name aren’t supported when you access Iceberg tables through the Horizon Catalog endpoint.

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