READ_ICEBERG

Reference material for READ_ICEBERG function

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

A table-valued function (TVF) that reads data from Apache Iceberg tables. The function can use either a location object or individual TVF parameters to access the data, and can read from file-based catalogs as well as REST catalogs. READ_ICEBERG returns a table with data from the specified Iceberg table.

Syntax

-- Using a LOCATION object (credentials stored in LOCATION)
READ_ICEBERG (
  LOCATION => '<location_name>'
  [, NAMESPACE => '<namespace_name>' ]
  [, TABLE => '<table_name>' ]
  [, MAX_STALENESS => <max_staleness_allowed> ]
)

-- Using individual TVF parameters
READ_ICEBERG (
  URL => '<s3_or_rest_api_url>'
  -- Parameters for Iceberg REST catalogs
  [, WAREHOUSE => '<warehouse_name>' ]
  [, NAMESPACE => '<namespace_name>' ]
  [, TABLE => '<table_name>' ]
  -- Parameters for Amazon S3
  [, 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>' ]
  -- Parameters for 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>' ]
  -- Other common parameters
  [, MAX_STALENESS => <max_staleness_allowed> ]
)

Parameters

Using a LOCATION object

Parameter	Description	Supported input types
LOCATION	The name of a location object that contains the Iceberg parameters and credentials. Firebolt recommends using LOCATION to store credentials for authentication. LOCATION must be specified as a string literal (e.g., LOCATION => 'my_location'). Unlike URL, it cannot be used as a positional parameter.	TEXT
NAMESPACE	The namespace an Iceberg table resides in. Used only for REST catalogs.	TEXT
TABLE	The name of the Iceberg table to read. Used only for REST catalogs.	TEXT
MAX_STALENESS	Specifies a maximum staleness for results returned by this function, e.g., INTERVAL '30 seconds'. The default value is 0 seconds, forcing Firebolt to fetch the latest version metadata from the catalog for every query. Values larger than zero instruct Firebolt to cache metadata and vended credentials in memory, and can typically reduce query latency by tens or hundreds of milliseconds.	INTERVAL

For a comprehensive guide to LOCATION objects, see LOCATION objects. For syntax details, see CREATE LOCATION (Iceberg).

Using individual TVF parameters

Common Parameters

Parameter	Description	Supported input types
URL	A url pointing to a table in an Iceberg file-based catalog, or a url pointing to an Iceberg REST catalog API endpoint. For file-based catalogs, the expected format is s3://{bucket_name}/{path}/{to}/{table} or s3://{bucket_name}/{path}/{to}/{table}/metadata/{version.metadata.json}. For REST catalogs, the expected format is https://{path}/{to}/{rest}/{host}.	TEXT
MAX_STALENESS	Specifies a maximum staleness for results returned by this function, e.g., INTERVAL '30 seconds'. The default value is 0 seconds, forcing Firebolt to fetch the latest version metadata from the catalog for every query. Values larger than zero instruct Firebolt to cache metadata and vended credentials in memory, and can typically reduce query latency by tens or hundreds of milliseconds.	INTERVAL

Common Parameters for Iceberg REST catalogs

The following parameters are used for all REST catalogs, including AWS Glue catalogs accessed via REST endpoint.

Parameter	Description	Supported input types
WAREHOUSE	The name of the warehouse an Iceberg table resides in.	TEXT
NAMESPACE	The namespace an Iceberg table resides in.	TEXT
TABLE	The name of the Iceberg table to read.	TEXT

Amazon S3 Parameters

The following parameters are used for authentication to both file-based catalogs and AWS Glue catalogs.

Parameter	Description	Supported input types
AWS_ACCESS_KEY_ID	The AWS access key ID.	TEXT
AWS_SECRET_ACCESS_KEY	The AWS secret access key.	TEXT
AWS_SESSION_TOKEN	The AWS session token.	TEXT
AWS_ROLE_ARN	The AWS role ARN.	TEXT
AWS_ROLE_EXTERNAL_ID	The AWS role external ID.	TEXT

Parameters for OAuth authentication

The following parameters apply to REST catalogs that use OAuth for authentication.

Parameter	Description	Supported input types
OAUTH_CLIENT_ID	An OAuth client ID for authenticating to the REST catalog.	TEXT
OAUTH_CLIENT_SECRET	An OAuth client secret for authenticating to the REST catalog.	TEXT
OAUTH_SCOPE	An OAuth scope for authenticating to the REST catalog.	TEXT
OAUTH_SERVER_URL	The URL to use when requesting an access token for the REST catalog. If not specified, {URL}/v1/oauth/tokens will be used.	TEXT
BEARER_TOKEN	An OAuth bearer token to use as an access token for the REST catalog.	TEXT

Return Type

The result is a table with data from the Iceberg files. Columns are read and parsed using their inferred data types.

Best practices

• Use views to simplify repeated queries. Wrap READ_ICEBERG in a view so you can query Iceberg tables without repeating the TVF call. See example below.
• It is recommended to specify all parameters using the named-parameter syntax rather than relying on parameter positions. For example: use URL => 'http://example.com' rather than omitting the URL => parameter name specifier.

For best practices that apply to all Iceberg access in Firebolt, such as choosing between access methods, storing credentials in a LOCATION, and setting MAX_STALENESS, see Best practices in the Iceberg guide.

Examples

Simplifying queries with views

You can wrap READ_ICEBERG TVFs in a view for easier querying of Iceberg tables. This also works with LOCATION-based READ_ICEBERG calls.

CREATE VIEW lineitem AS SELECT * FROM READ_ICEBERG(
    URL => 's3://firebolt-core-us-east-1/test_data/tpch/iceberg/tpch.db/lineitem'
  );

SELECT * FROM lineitem LIMIT 10;

CREATE VIEW orders AS SELECT * FROM READ_ICEBERG(
    URL => 's3://firebolt-core-us-east-1/test_data/tpch/iceberg/tpch.db/orders'
  );

SELECT * FROM orders LIMIT 10;

Once views are created, you can use them in joins and complex queries without referencing READ_ICEBERG directly:

SELECT o.o_orderkey, l.l_extendedprice
FROM orders o
JOIN lineitem l ON o.o_orderkey = l.l_orderkey
WHERE o.o_orderdate > '1995-01-01'
LIMIT 10;

For more information about views, see CREATE VIEW.

Reading using a LOCATION

The following code example reads the first 5 rows from an Iceberg table using a LOCATION object:

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',
  MAX_STALENESS => INTERVAL '30 seconds'
)
LIMIT 5;

LOCATION objects are supported for file-based (S3-hosted) catalogs as well as REST catalogs. For more examples of LOCATION, see CREATE LOCATION (Iceberg).

Using TVF parameters, from a public location in S3

The following code example reads the first 5 rows from an Iceberg table in a file-based catalog stored on S3:

SELECT * 
FROM READ_ICEBERG(
  URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_iceberg/tpch/iceberg/lineitem',
  MAX_STALENESS => INTERVAL '30 seconds'
) 
LIMIT 5;

Returns

l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipdate	l_commitdate	l_receiptdate	l_shipinstruct	l_shipmode	l_comment
1	156	4	1	17.00	17954.55	0.04	0.02	N	O	1996-03-13	1996-02-12	1996-03-22	DELIVER IN PERSON	TRUCK	to beans x-ray carefull
1	68	9	2	36.00	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	according to the final foxes. qui
1	64	5	3	8.00	7712.48	0.10	0.02	N	O	1996-01-29	1996-03-05	1996-01-31	TAKE BACK RETURN	REG AIR	ourts cajole above the furiou
1	3	6	4	28.00	25284.00	0.09	0.06	N	O	1996-04-21	1996-03-30	1996-05-16	NONE	AIR	s cajole busily above t
1	25	8	5	24.00	22200.48	0.10	0.04	N	O	1996-03-30	1996-03-14	1996-04-01	NONE	FOB	the regular, regular pa

Using a direct path to a metadata.json file

A URL pointing to an Iceberg metadata.json file will also return the same result:

SELECT * 
FROM READ_ICEBERG(
  URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_iceberg/tpch/iceberg/lineitem/metadata/00001-2b7ef7c8-a1d8-4239-abf5-41df838d20ba.metadata.json',
  MAX_STALENESS => INTERVAL '30 seconds'
) 
LIMIT 5;

Returns

l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipdate	l_commitdate	l_receiptdate	l_shipinstruct	l_shipmode	l_comment
1	156	4	1	17.00	17954.55	0.04	0.02	N	O	1996-03-13	1996-02-12	1996-03-22	DELIVER IN PERSON	TRUCK	to beans x-ray carefull
1	68	9	2	36.00	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	according to the final foxes. qui
1	64	5	3	8.00	7712.48	0.10	0.02	N	O	1996-01-29	1996-03-05	1996-01-31	TAKE BACK RETURN	REG AIR	ourts cajole above the furiou
1	3	6	4	28.00	25284.00	0.09	0.06	N	O	1996-04-21	1996-03-30	1996-05-16	NONE	AIR	s cajole busily above t
1	25	8	5	24.00	22200.48	0.10	0.04	N	O	1996-03-30	1996-03-14	1996-04-01	NONE	FOB	the regular, regular pa

Authenticated read from S3

The following code examples use various valid combinations of AWS secrets to read from S3:

Using access key + secret:

SELECT * 
FROM READ_ICEBERG(
  URL => 's3://my-bucket/path/to/iceberg/table',
  AWS_ACCESS_KEY_ID => '1231',
  AWS_SECRET_ACCESS_KEY => '567',
  MAX_STALENESS => INTERVAL '30 seconds'
) 
LIMIT 5;

Using access key, secret, and session token:

SELECT * 
FROM READ_ICEBERG(
  URL => 's3://my-bucket/path/to/iceberg/table',
  AWS_ACCESS_KEY_ID => '1231',
  AWS_SECRET_ACCESS_KEY => '567',
  AWS_SESSION_TOKEN => 'session-token',
  MAX_STALENESS => INTERVAL '30 seconds'
) 
LIMIT 5;

Using role + external id (recommended):

SELECT * 
FROM READ_ICEBERG(
  URL => 's3://my-bucket/path/to/iceberg/table',
  AWS_ROLE_ARN => 'arn:aws:iam::123456789012:role/S3Access',
  AWS_ROLE_EXTERNAL_ID => 'my-role-external-id',
  MAX_STALENESS => INTERVAL '30 seconds'
) 
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.

Using role only:

SELECT * 
FROM READ_ICEBERG(
  URL => 's3://my-bucket/path/to/iceberg/table',
  AWS_ROLE_ARN => 'arn:aws:iam::123456789012:role/S3Access',
  MAX_STALENESS => INTERVAL '30 seconds'
) 
LIMIT 5;

Reading from REST

The following code example reads the first 5 rows from an Iceberg table in a REST catalog:

SELECT *
FROM READ_ICEBERG(
  URL => 'https://my-iceberg-rest-catalog/api',
  WAREHOUSE  => 'my_warehouse',
  NAMESPACE => 'my_namespace',
  TABLE => 'my_table_name',
  OAUTH_CLIENT_ID => '00000000-0000-0000-0000-000000000000',
  OAUTH_CLIENT_SECRET => '1234',
  OAUTH_SCOPE => 'example_permission:all',
  MAX_STALENESS => INTERVAL '30 seconds'
)
LIMIT 5;

For more on Iceberg REST catalogs, see the Iceberg REST API spec.

Reading from REST, with a custom OAuth token URL

Some Iceberg REST catalogs do not support the /v1/oauth/tokens API, and serve OAuth tokens from a different API. For these catalogs, specify OAUTH_SERVER_URL.

SELECT *
FROM READ_ICEBERG(
  URL => 'https://my-iceberg-rest-catalog/api',
  WAREHOUSE  => 'my_warehouse',
  NAMESPACE => 'my_namespace',
  TABLE => 'my_table_name',
  OAUTH_CLIENT_ID => '00000000-0000-0000-0000-000000000000',
  OAUTH_CLIENT_SECRET => '1234',
  OAUTH_SCOPE => 'example_permission:all',
  OAUTH_SERVER_URL => 'https://my-iceberg-rest-catalog/example/token',
  MAX_STALENESS => INTERVAL '30 seconds'
)
LIMIT 5;

Reading from Databricks Unity Catalog

One example of an Iceberg REST catalog that requires a custom OAUTH_SERVER_URL is the Databricks Unity Catalog. The following code example reads the first 5 rows from a table in a Databricks Unity Catalog, showing how Databricks concepts map to READ_ICEBERG parameters:

SELECT *
FROM READ_ICEBERG(
  URL => 'https://000-0000000000000.cloud.databricks.com/api/2.1/unity-catalog/iceberg-rest',
  WAREHOUSE  => 'my_uc_catalog_name',
  NAMESPACE => 'my_uc_schema_name',
  TABLE => 'my_table_name',
  OAUTH_CLIENT_ID => '00000000-0000-0000-0000-000000000000',
  OAUTH_CLIENT_SECRET => '1234',
  OAUTH_SCOPE => 'all-apis',
  OAUTH_SERVER_URL => 'https://000-0000000000000.cloud.databricks.com/oidc/v1/token',
  MAX_STALENESS => INTERVAL '30 seconds'
)
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.

SELECT *
FROM READ_ICEBERG(
  URL => 'https://000-0000000000000.cloud.databricks.com/api/2.1/unity-catalog/iceberg/v1',
  WAREHOUSE  => 'my_uc_catalog_name',
  NAMESPACE => 'my_uc_schema_name',
  TABLE => 'my_table_name',
  BEARER_TOKEN => '00000000-0000-0000-0000-000000000000',
  MAX_STALENESS => INTERVAL '30 seconds'
)
LIMIT 5;

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.

Reading from Snowflake Open Catalog

SELECT *
FROM READ_ICEBERG(
  URL => 'https://abc123.us-west-2.aws.myapi.com/polaris/api/catalog',
  WAREHOUSE  => 'my_warehouse',
  NAMESPACE => 'my_catalog_name',
  TABLE => 'my_table_name',
  OAUTH_CLIENT_ID => '00000000-0000-0000-0000-000000000000',
  OAUTH_CLIENT_SECRET => '1234',
  OAUTH_SCOPE => 'PRINCIPAL_ROLE:ALL',
  MAX_STALENESS => INTERVAL '30 seconds'
)
LIMIT 5;

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.

Reading from AWS Glue catalog

The following code example reads the first 5 rows from an Iceberg table in an AWS Glue catalog. Note that for AWS Glue catalogs, the warehouse parameter maps to catalog_id and the namespace parameter maps to database:

SELECT *
FROM READ_ICEBERG(
  URL => 'https://glue.us-west-2.amazonaws.com/iceberg',
  WAREHOUSE  => '123456789123',
  NAMESPACE => 'my_database',
  TABLE => 'my_table',
  AWS_ACCESS_KEY_ID => '1231',
  AWS_SECRET_ACCESS_KEY => '567',
  AWS_SESSION_TOKEN => 'session-token'
)
LIMIT 5;

Firebolt supports authentication using either Lake Formation or direct access to underlying storage. For authentication details, refer to the AWS Glue Catalog section in CREATE LOCATION (Iceberg).

About Metadata Versions in File-Based Catalogs

When reading from a file-based catalog, Firebolt first looks for a version-hint.text file to determine the metadata version. If this file is unavailable, you can instead specify a direct path to a specific metadata.json file.

SELECT *
FROM READ_ICEBERG(
  URL => 's3://my-bucket/path/to/iceberg/table/metadata/v1.metadata.json',
  ...
)
LIMIT 5;

Alternatively, Firebolt can also attempt to ‘guess’ the correct metadata file. Because this approach may violate atomicity guarantees, it is disabled by default. For details, see Guessing Iceberg metadata version.