CREATE ICEBERG TABLE

For an overview of querying, tuning, and exporting Iceberg tables, see the Iceberg guide. Creates an Iceberg table that is registered in Firebolt’s catalog and backed by an Apache Iceberg table in external storage. Once created, you can query it with regular SELECT statements just like a managed table, without needing to use the READ_ICEBERG table-valued function. The table’s schema is automatically inferred from the Iceberg metadata at creation time. Firebolt reads the Iceberg catalog to determine column names and types.

To export data from Firebolt into a new Iceberg table, use CREATE ICEBERG TABLE AS SELECT instead.

Syntax

CREATE ICEBERG TABLE <table_name>
LOCATION = '<location_name>'

Parameters

Parameter	Description
`<table_name>`	An identifier that specifies the name of the table. This name must be unique within the database.
`<location_name>`	The name of an Iceberg `LOCATION` object that points to the Iceberg table in external storage.

Examples

Creating an Iceberg table

The following example creates a LOCATION pointing to an Iceberg table in S3, then creates an Iceberg table that you can query directly. The schema is inferred from the Iceberg metadata, so no column definitions are specified.

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

CREATE ICEBERG TABLE my_iceberg_table LOCATION = 'my_iceberg_location';

Querying an Iceberg table

Once created, an Iceberg table can be queried like any other table in Firebolt:

SELECT * FROM my_iceberg_table LIMIT 10;

SELECT customer_name, SUM(amount) AS total
FROM my_iceberg_table
GROUP BY customer_name
ORDER BY total DESC;

You can also join Iceberg tables with Firebolt-managed tables:

SELECT o.order_id, o.amount, c.region
FROM my_iceberg_table o
JOIN customers c ON o.customer_name = c.name;

Dropping an Iceberg table

Iceberg tables are dropped using the standard DROP TABLE command. Dropping an Iceberg table removes it from Firebolt’s catalog but does not delete the underlying data in external storage.

DROP TABLE my_iceberg_table;

If a view depends on the Iceberg table, use CASCADE:

DROP TABLE my_iceberg_table CASCADE;

Comparison with other approaches

Firebolt provides multiple ways to work with Iceberg data:

Approach	Use case
`CREATE ICEBERG TABLE` (this page)	Register an external Iceberg table in Firebolt’s catalog so it can be queried like a regular table.
`CREATE ICEBERG DATABASE`	Mount an external Iceberg catalog as a database so all its tables can be queried by name, without registering each one.
`READ_ICEBERG`	Query an Iceberg table ad hoc using a table-valued function, without registering it in the catalog.
`CREATE ICEBERG TABLE AS SELECT`	Export data from Firebolt into a new Iceberg table in external storage.

Remarks

The LOCATION must reference an Iceberg source (SOURCE = ICEBERG). Using a non-Iceberg location will result in an error.
The table schema is inferred from the Iceberg metadata. Column definitions must not be specified in the CREATE statement.
All Firebolt query optimizations for Iceberg tables apply, including caching, pruning, and co-located joins.
Iceberg tables support aggregating indexes, which can be created with CREATE AGGREGATING INDEX to accelerate repeated aggregation queries.
Firebolt can automatically collect Automated Column Statistics (ACS) for Iceberg tables, enabling the query planner to generate more efficient execution plans.

Limitations

CREATE OR REPLACE is not supported for Iceberg tables.
IF NOT EXISTS is not supported for Iceberg tables.
Only Iceberg LOCATION objects are supported. The location must point to a valid Iceberg catalog.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Examples

Creating an Iceberg table

Querying an Iceberg table

Dropping an Iceberg table

Comparison with other approaches

Remarks

Limitations

​Syntax

​Parameters

​Examples

​Creating an Iceberg table

​Querying an Iceberg table

​Dropping an Iceberg table

​Comparison with other approaches

​Remarks

​Limitations

Syntax

Parameters

Examples

Creating an Iceberg table

Querying an Iceberg table

Dropping an Iceberg table

Comparison with other approaches

Remarks

Limitations