CREATE LOCATION (Iceberg)

Reference and syntax for creating Iceberg locations.

For an overview of querying, tuning, and exporting Iceberg tables, see the Iceberg guide.

Creates a new location object in your Firebolt account, which is a secure, reusable object that stores the connection details and credentials for Iceberg data sources. Instead of entering these details each time you run a query or create a table, you can use a location object.

This document captures the syntax and examples for location objects for Iceberg. For a comprehensive guide to LOCATION objects, see LOCATION objects. For more on location objects syntax in general, see CREATE LOCATION.

Iceberg locations currently support five catalog types: REST, FILE_BASED, DATABRICKS_UNITY (as syntactic sugar on top of REST), SNOWFLAKE_OPEN_CATALOG (a REST-shaped catalog), and AWS_GLUE. Each catalog accepts a different set of parameters and credentials.

Syntax

CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = ICEBERG
  CATALOG = { FILE_BASED | REST | DATABRICKS_UNITY | SNOWFLAKE_OPEN_CATALOG | AWS_GLUE }
  CATALOG_OPTIONS = {
    -- FILE_BASED
    ( URL = '<file_based_catalog_url>' )
    |
    -- REST or SNOWFLAKE_OPEN_CATALOG
    ( URL = '<rest_catalog_url>'
      WAREHOUSE = '<warehouse>'
      [ NAMESPACE = '<namespace>' ]
      [ TABLE = '<table_name>' ] )
    |
    -- DATABRICKS_UNITY
    ( WORKSPACE_INSTANCE = '<workspace_instance>'
      CATALOG = '<uc_catalog_name>'
      SCHEMA = '<uc_schema_name>'
      TABLE = '<uc_table_name>' )
    |
    -- AWS_GLUE
    ( URL = '<aws_glue_endpoint_url>'
      CATALOG_ID = '<catalog_id>'
      [ DATABASE = '<database_name>' ]
      [ TABLE = '<table_name>' ] )
  }
  CREDENTIALS = {
    -- AWS Authentication
    ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
      AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
      [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
    |
    ( AWS_ROLE_ARN = '<aws_role_arn>'
      [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
    |
    -- OAuth Authentication
    ( OAUTH_CLIENT_ID = '<oauth_client_id>'
      OAUTH_CLIENT_SECRET = '<oauth_client_secret>'
      [ OAUTH_SCOPE = '<oauth_scope>' ]
      [ OAUTH_SERVER_URL = '<oauth_server_url>' ] )
    |
    ( BEARER_TOKEN = '<bearer_token>' )
  }
  -- STORAGE_CREDENTIALS: REST, DATABRICKS_UNITY, SNOWFLAKE_OPEN_CATALOG only
  [ STORAGE_CREDENTIALS = {
      ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
        AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
        [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
      |
      ( AWS_ROLE_ARN = '<aws_role_arn>'
        [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
    } ]
  [ DESCRIPTION = '<description>' ]

Parameters

Common Parameters

Parameter	Description
<location_name>	A unique identifier for the location within your account.
SOURCE	The external data source type. Currently, CLOUD_STORAGE and ICEBERG are supported. Additionally, AMAZON_S3 is supported as an alias for CLOUD_STORAGE with an S3 URL. This should be ICEBERG for Iceberg locations.
DESCRIPTION	Optional metadata describing the location’s purpose.

Iceberg Parameters

Parameter	Description
CATALOG	The type of Iceberg catalog. Supported values: FILE_BASED, REST, DATABRICKS_UNITY, SNOWFLAKE_OPEN_CATALOG, or AWS_GLUE.
CATALOG_OPTIONS	Configuration options specific to the chosen catalog type.
CREDENTIALS	Authentication credentials for accessing the Iceberg catalog. Depending on the type of catalog, either AWS or OAuth credentials are used.
STORAGE_CREDENTIALS	Optional AWS object-storage credentials for REST, DATABRICKS_UNITY, and SNOWFLAKE_OPEN_CATALOG catalogs, used as a fallback if the catalog does not have credential vending enabled. Accepts the same AWS authentication parameters as CREDENTIALS.

AWS Authentication Parameters

Use AWS authentication parameters for FILE_BASED and AWS_GLUE catalogs.

Parameter	Description
AWS_ACCESS_KEY_ID	Your AWS access key ID for key-based authentication.
AWS_SECRET_ACCESS_KEY	Your AWS secret access key for key-based authentication.
AWS_SESSION_TOKEN	Optional temporary session token for temporary credentials.
AWS_ROLE_ARN	The ARN of the IAM role to assume for role-based authentication.
AWS_ROLE_EXTERNAL_ID	Optional external ID for role assumption.

OAuth Authentication Parameters

Use OAuth authentication parameters for REST, DATABRICKS_UNITY, or SNOWFLAKE_OPEN_CATALOG catalogs.

Parameter	Description
OAUTH_CLIENT_ID	The OAuth client ID for authentication.
OAUTH_CLIENT_SECRET	The OAuth client secret for authentication.
OAUTH_SCOPE	Optional OAuth scope for authentication.
OAUTH_SERVER_URL	Optional OAuth server URL for authentication.
BEARER_TOKEN	OAuth bearer token for authentication. Only supported for DATABRICKS_UNITY catalog.

Iceberg Catalog-Specific Parameters

FILE_BASED Catalog

Parameter	Description
URL	The URL for the file-based catalog. This must be a valid S3 URL.

REST Catalog

Parameter	Description
URL	The REST catalog URL.
WAREHOUSE	The warehouse identifier.
NAMESPACE	The namespace identifier.
TABLE	The table name.

SNOWFLAKE_OPEN_CATALOG Catalog

Snowflake Open Catalog is a REST-shaped catalog and takes the same parameters as REST.

Parameter	Description
URL	The Snowflake Open Catalog REST URL.
WAREHOUSE	The warehouse identifier.
NAMESPACE	The namespace identifier.
TABLE	The table name.

DATABRICKS_UNITY Catalog

Parameter	Description
WORKSPACE_INSTANCE	The Databricks workspace instance.
CATALOG	The Unity Catalog name.
SCHEMA	The Unity Catalog schema name.
TABLE	The Unity Catalog table name.

AWS_GLUE Catalog

Parameter	Description
URL	The AWS Glue endpoint URL. Format: https://glue.<region>.amazonaws.com/iceberg
CATALOG_ID	The AWS Glue catalog ID. Must be the default catalog for your account. Nested and named catalogs are not currently supported.
DATABASE	The AWS Glue database name.
TABLE	The AWS Glue table name.

Examples

Register Iceberg tables for easy querying. You can use CREATE ICEBERG TABLE to register an Iceberg table in Firebolt’s catalog, allowing you to query it with regular SELECT statements. To query every table in a catalog without registering each one, mount the catalog with CREATE ICEBERG DATABASE; this requires a catalog-scoped LOCATION (no NAMESPACE, DATABASE, SCHEMA, or TABLE in CATALOG_OPTIONS). Alternatively, wrap READ_ICEBERG calls in a view. See Simplifying queries with views for examples.

File-based catalog

Used to access Iceberg tables directly from S3.

For file-based Iceberg catalogs, use AWS key-based or role-based authentication in CREDENTIALS.

With access key and secret:

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = FILE_BASED
  CATALOG_OPTIONS = (
    URL = 's3://my-bucket/path/to/iceberg/table'
  )
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '1231' AWS_SECRET_ACCESS_KEY = '567' );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

With access key, secret, and session token:

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = FILE_BASED
  CATALOG_OPTIONS = (
    URL = 's3://my-bucket/path/to/iceberg/table'
  )
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '1231' AWS_SECRET_ACCESS_KEY = '567' AWS_SESSION_TOKEN = 'session-token' );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

With role and external id (recommended):

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = FILE_BASED
  CATALOG_OPTIONS = (
    URL = 's3://my-bucket/path/to/iceberg/table'
  )
  CREDENTIALS = ( AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/S3Access' AWS_ROLE_EXTERNAL_ID = 'my-external-id' );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

For role-based AWS access you can additionally set an external ID. An external ID is a value you choose and control that AWS checks when Firebolt assumes your role, adding a second condition on top of your account’s unique IAM principal. Configuring one is a recommended best practice. See IAM roles.

With role only:

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = FILE_BASED
  CATALOG_OPTIONS = (
    URL = 's3://my-bucket/path/to/iceberg/table'
  )
  CREDENTIALS = ( AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/S3Access' );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

REST catalog

Used to access Iceberg in REST catalogs, that implement the Iceberg REST API spec.

For generic REST Iceberg catalogs, use OAuth parameters for CREDENTIALS.

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = REST
  CATALOG_OPTIONS = (
    URL = 'https://my-iceberg-rest-catalog/api'
    WAREHOUSE  = 'my_warehouse'
    NAMESPACE = 'my_namespace'
    TABLE = 'my_table_name'
  )
  CREDENTIALS = (
    OAUTH_CLIENT_ID = '00000000-0000-0000-0000-000000000000'
    OAUTH_CLIENT_SECRET = '1234'
    OAUTH_SCOPE = 'example_permission:all'
    -- OAUTH_SERVER_URL is only needed for REST catalogs that don't support the /v1/oauth/tokens endpoint. If /v1/oauth/tokens is supported, it can be omitted.
    OAUTH_SERVER_URL = 'https://my-iceberg-rest-catalog/example/token'
  );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

With a storage credential fallback:

Add STORAGE_CREDENTIALS with AWS object-storage credentials, used as a fallback if the catalog does not have credential vending enabled.

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = REST
  CATALOG_OPTIONS = (
    URL = 'https://my-iceberg-rest-catalog/api'
    WAREHOUSE  = 'my_warehouse'
    NAMESPACE = 'my_namespace'
    TABLE = 'my_table_name'
  )
  CREDENTIALS = (
    OAUTH_CLIENT_ID = '00000000-0000-0000-0000-000000000000'
    OAUTH_CLIENT_SECRET = '1234'
  )
  STORAGE_CREDENTIALS = (
    AWS_ACCESS_KEY_ID = '1231'
    AWS_SECRET_ACCESS_KEY = '567'
  );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

STORAGE_CREDENTIALS also accepts role-based AWS authentication (AWS_ROLE_ARN, optional AWS_ROLE_EXTERNAL_ID) and works the same way on DATABRICKS_UNITY and SNOWFLAKE_OPEN_CATALOG locations.

Databricks Unity Catalog

Used to access Iceberg tables in a Databricks Unity Catalog. Offered as syntactic sugar on top of CATALOG = REST, for users who may be more familiar with Databricks Unity Catalog terminology.

For configuring Unity Catalog in your Databricks workspace, see Databricks - Set up and manage Unity Catalog. Note that you will need to enable credential vending in your Unity Catalog, see Databricks - Unity Catalog credential vending for external system access. For general information about reading Databricks tables from Iceberg clients, see Databricks - Read Databricks tables from Iceberg clients.

For Databricks Unity Catalogs, use OAuth parameters for CREDENTIALS.

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = DATABRICKS_UNITY
  CATALOG_OPTIONS = (
    WORKSPACE_INSTANCE = '000-0000000000000.cloud.databricks.com'
    CATALOG  = 'my_uc_catalog_name'
    SCHEMA = 'my_uc_schema_name'
    TABLE = 'my_uc_table_name'
  )
  CREDENTIALS = (
    OAUTH_CLIENT_ID = '00000000-0000-0000-0000-000000000000'
    OAUTH_CLIENT_SECRET = '1234'
    -- OAUTH_SCOPE and OAUTH_SERVER_URL are not needed
  );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

If you’re using a PAT Databricks - Authenticate with Databricks personal access tokens (legacy), you can pass it via the BEARER_TOKEN parameter.

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = DATABRICKS_UNITY
  CATALOG_OPTIONS = (
    WORKSPACE_INSTANCE = '000-0000000000000.cloud.databricks.com'
    CATALOG  = 'my_uc_catalog_name'
    SCHEMA = 'my_uc_schema_name'
    TABLE = 'my_uc_table_name'
  )
  CREDENTIALS = (
    BEARER_TOKEN = '1234abcd'
    -- No OAUTH_ parameters are needed
  );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

Snowflake Open catalog

Iceberg tables in Snowflake Open Catalog can be read by using a LOCATION with CATALOG = SNOWFLAKE_OPEN_CATALOG, a REST-shaped catalog.

For setting up a Snowflake Open Catalog in your account, see Snowflake - Snowflake Open Catalog overview. Note that you will need to enable credential vending for your Iceberg tables, see Snowflake - Use catalog-vended credentials for Apache Iceberg™ tables. For general information about reading Snowflake Open Catalog tables from Iceberg clients, see Snowflake - Checking your REST catalog configuration.

For Snowflake Open catalogs, use OAuth parameters for CREDENTIALS.

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = SNOWFLAKE_OPEN_CATALOG
  CATALOG_OPTIONS = (
    URL = 'https://abc123.us-west-2.aws.myapi.com/polaris/api/catalog'
    WAREHOUSE  = 'my_warehouse'
    NAMESPACE = 'my_catalog_name'
    TABLE = 'my_table_name'
  )
  CREDENTIALS = (
    OAUTH_CLIENT_ID = '00000000-0000-0000-0000-000000000000'
    OAUTH_CLIENT_SECRET = '1234'
    OAUTH_SCOPE = 'PRINCIPAL_ROLE:ALL'
    -- OAUTH_SERVER_URL is not needed
  );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

AWS Glue catalog

Used to access Iceberg tables in AWS Glue Data Catalog.

With access key and secret:

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = AWS_GLUE
  CATALOG_OPTIONS = (
    URL = 'https://glue.us-east-1.amazonaws.com/iceberg'
    CATALOG_ID = '123456789123'
    DATABASE = 'my_database'
    TABLE = 'my_table'
  )
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '1231' AWS_SECRET_ACCESS_KEY = '567' );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

With role and external id (recommended):

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = AWS_GLUE
  CATALOG_OPTIONS = (
    URL = 'https://glue.us-east-1.amazonaws.com/iceberg'
    CATALOG_ID = '123456789123'
    DATABASE = 'my_database'
    TABLE = 'my_table'
  )
  CREDENTIALS = ( AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/GlueAccess' AWS_ROLE_EXTERNAL_ID = 'my-external-id' );

SELECT * FROM READ_ICEBERG(LOCATION => 'my_location') LIMIT 5;

For role-based AWS access you can additionally set an external ID. An external ID is a value you choose and control that AWS checks when Firebolt assumes your role, adding a second condition on top of your account’s unique IAM principal. Configuring one is a recommended best practice. See IAM roles.

Your IAM credentials must have the following permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowGlueCatalogTableAccess",
            "Effect": "Allow",
            "Action": [
                "glue:GetCatalog",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables"
            ],
            "Resource": [
                "arn:aws:glue:*:{catalog_id}:table/*/*",
                "arn:aws:glue:*:{catalog_id}:catalog",
                "arn:aws:glue:*:{catalog_id}:database/{database_name}"
            ]
        }
    ]
}

Your IAM credentials must also have permissions to access the underlying S3 storage. See Use AWS roles to access S3 for details. (Note that this may not be needed if you are using Lake Formation to manage access.)

(Optional) Lake Formation

If you use Lake Formation for access control, you’ll need to grant your IAM credentials permissions to Lake Formation, and register the underlying S3 location with Lake Formation:

Your IAM credentials will need the GetDataAccess permission - see AWS Lake Formation: Underlying data access control.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "lakeformation:GetDataAccess",
            "Resource": "*"
        }
    ]
}

You’ll need to register your underlying S3 storage location with Lake Formation - see AWS Lake Formation: Adding an Amazon S3 location to your data lake.

If you want Lake Formation to vend temporary credentials to access your underlying storage, you will need to configure Lake Formation to allow Firebolt’s external engine to access data in your S3 - see AWS Lake Formation: Application integration for full table access.

If credential vending is enabled, it is recommended to not grant your IAM credentials access to your underlying S3 storage. This helps ensure that any storage access is using the vended credentials only.