CREATE LOCATION (Iceberg)

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 specific 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 four catalog types: REST, FILE_BASED, DATABRICKS_UNITY (as syntactic sugar on top of REST), and AWS_GLUE. Each catalog accepts a different set of parameters and credentials. Topics:

Syntax
Parameters
Examples

Syntax

CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = ICEBERG
  CATALOG = { FILE_BASED | REST | DATABRICKS_UNITY | AWS_GLUE }
  CATALOG_OPTIONS = {
    -- FILE_BASED
    ( URL = '<file_based_catalog_url>' )
    |
    -- REST
    ( 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>' ] )
  }
  [ 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, `AMAZON_S3` and `ICEBERG` are supported. 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`, 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.

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 or DATABRICKS_UNITY 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.

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.

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

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' SESSION_TOKEN = 'session-token' );

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

With role:

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;

With role and external id:

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;

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;

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;

Snowflake Open catalog (as a generic REST catalog)

Iceberg tables in Snowflake Open Catalog can currently be read by using a LOCATION with CATALOG = REST. 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 = REST
  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. Firebolt automatically chooses the appropriate authentication method:

Vended credentials: If Lake Formation is enabled and returns vended credentials, Firebolt uses those credentials to access your data storage.
Standard credentials: When vended credentials are unavailable, Firebolt uses your provided credentials to access both the Glue catalog and underlying storage.

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:

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

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

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 data through one of the following methods: Option 1: Lake Formation Use Lake Formation to grant data permissions. See the Amazon documentation for details. You’ll also need to grant getDataAccess permission to your IAM credentials:

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

Option 2: Direct Storage Access Ensure your IAM credentials have permissions to access the underlying storage directly. See Use AWS roles to access S3 for details.

What is Firebolt?

Overview

Performance and Observability

Guides

SQL reference

General reference

API reference

Syntax

Parameters

Common Parameters

Iceberg Parameters

AWS Authentication Parameters

OAuth Authentication Parameters

Iceberg Catalog-Specific Parameters

FILE_BASED Catalog

REST Catalog

DATABRICKS_UNITY Catalog

AWS_GLUE Catalog

Examples

File-based catalog

REST catalog

Databricks Unity Catalog

Snowflake Open catalog (as a generic REST catalog)

AWS Glue catalog

What is Firebolt?

Overview

Performance and Observability

Guides

SQL reference

General reference

API reference

​Syntax

​Parameters

​Common Parameters

​Iceberg Parameters

​AWS Authentication Parameters

​OAuth Authentication Parameters

​Iceberg Catalog-Specific Parameters

FILE_BASED Catalog

REST Catalog

DATABRICKS_UNITY Catalog

AWS_GLUE Catalog

​Examples

​File-based catalog

​REST catalog

​Databricks Unity Catalog

​Snowflake Open catalog (as a generic REST catalog)

​AWS Glue catalog

Syntax

Parameters

Common Parameters

Iceberg Parameters

AWS Authentication Parameters

OAuth Authentication Parameters

Iceberg Catalog-Specific Parameters

Examples

File-based catalog

REST catalog

Databricks Unity Catalog

Snowflake Open catalog (as a generic REST catalog)

AWS Glue catalog