CREATE ICEBERG DATABASE

Reference and syntax for the CREATE ICEBERG DATABASE command.

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

Creates an Iceberg database that mounts an external Iceberg catalog. Once created, you can query every table the catalog exposes using a fully qualified three-part name, without registering each table individually with CREATE ICEBERG TABLE.

An Iceberg database stores only a pointer to an Iceberg LOCATION and a freshness setting. Each query resolves the table against the upstream catalog at run time, so any table added to the catalog becomes visible on the next query.

Syntax

CREATE ICEBERG DATABASE [ IF NOT EXISTS ] <database_name> WITH
  LOCATION = '<location_name>'
  [ MAX_STALENESS = '<interval>' ]
  [ DESCRIPTION = '<description>' ]

Parameters

Parameter	Description
<database_name>	The name of the new Iceberg database.
LOCATION	The name of an existing Iceberg LOCATION. The LOCATION must be warehouse-/catalog-scoped: its CATALOG_OPTIONS must not set NAMESPACE (REST), DATABASE (AWS_GLUE), SCHEMA (DATABRICKS_UNITY), or TABLE.
MAX_STALENESS	(Optional) An interval string (for example, '30 seconds', '5 minutes', '1 hour'). Controls how long Firebolt can serve queries from cached catalog metadata. The default is '0' (no caching). For details, see Configurable data freshness with MAX_STALENESS.
DESCRIPTION	(Optional) A description of up to 64 characters.

Supported catalog types

CREATE ICEBERG DATABASE works with any Iceberg LOCATION object that targets one of the following catalog types:

• FILE_BASED
• REST
• AWS_GLUE
• SNOWFLAKE_OPEN_CATALOG
• DATABRICKS_UNITY

Name resolution

A fully qualified table name has three parts: <database>.<schema>.<table>.

• <database> is the Iceberg database created with CREATE ICEBERG DATABASE.
• <schema> maps to the upstream Iceberg namespace.
• <table> maps to the Iceberg table within that namespace.

SELECT * FROM lake."analytics.sales.q1".orders;

Examples

REST catalog

CREATE LOCATION rest_loc WITH
  SOURCE = ICEBERG
  CATALOG = REST
  CATALOG_OPTIONS = (
    URL = 'https://catalog.example.com/v1'
    WAREHOUSE = 'analytics'
  )
  CREDENTIALS = (
    OAUTH_CLIENT_ID = '<client_id>'
    OAUTH_CLIENT_SECRET = '<client_secret>'
  );

CREATE ICEBERG DATABASE lake WITH
  LOCATION = 'rest_loc'
  MAX_STALENESS = '30 seconds';

SELECT * FROM lake.sales.orders LIMIT 10;

AWS Glue catalog

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

CREATE ICEBERG DATABASE lake WITH
  LOCATION = 'glue_loc'
  MAX_STALENESS = '5 minutes';

SELECT * FROM lake.sales.orders LIMIT 10;

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.

File-based catalog

CREATE LOCATION file_loc WITH
  SOURCE = ICEBERG
  CATALOG = FILE_BASED
  CATALOG_OPTIONS = ( URL = 's3://my-bucket/iceberg-warehouse' )
  CREDENTIALS = ( AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/IcebergAccess' AWS_ROLE_EXTERNAL_ID = 'my-external-id' );

CREATE ICEBERG DATABASE lake WITH
  LOCATION = 'file_loc'
  MAX_STALENESS = '5 minutes';

SELECT * FROM lake.sales.orders LIMIT 10;

Limitations

• The LOCATION must be warehouse-/catalog-scoped. A LOCATION that pins NAMESPACE (REST), DATABASE (AWS_GLUE), SCHEMA (DATABRICKS_UNITY), or TABLE fails at CREATE ICEBERG DATABASE time.
• Tables in a mounted database are resolved on demand by name. SHOW TABLES and information_schema.tables do not list them.

See also

• CREATE LOCATION (Iceberg)
• CREATE ICEBERG TABLE
• READ_ICEBERG
• DROP DATABASE