CREATE ICEBERG TABLE

Reference and syntax for the CREATE ICEBERG TABLE SQL command.

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.