Catalog

Sail supports various catalog providers to manage your datasets as external tables. Catalogs help organize and maintain metadata about your data, so that you can refer to them by table names in your SQL queries.

By default, Sail uses a memory catalog provider that stores table metadata in memory for the duration of your session. You can configure remote catalog providers to persist your table metadata across sessions. This is done using the Sail configuration options.

For example, you can configure memory catalogs using the catalog.list option and set the default catalog using the catalog.default_catalog option. The configuration can be done via environment variables before starting the Sail server.

export SAIL_CATALOG__LIST='[{name="c1", type="memory", initial_database=["default"]}, {name="c2", type="iceberg-rest", uri="https://catalog.example.com"}]'
export SAIL_CATALOG__DEFAULT_CATALOG="c1"

Then you can interact with the catalogs using the Spark API.

INFO

In the code below, spark refers to a Spark client session connected to the Sail server. You can refer to the Getting Started guide for how it works.

spark.catalog.listCatalogs()
spark.catalog.currentCatalog()
spark.catalog.listTables()
spark.catalog.setCurrentCatalog("c2")

You can also interact with catalogs using SQL statements.

-- show the current catalog
SELECT current_catalog()
-- show tables in the current catalog
SHOW TABLES

The next few pages describe the different catalog providers supported by Sail and how to configure them.

Common Options

All remote catalog providers (excluding the Memory catalog) support the following common options for caching database and table listings. This is particularly useful for reducing the number of requests to external services.

• database_cache_type (optional): The scope of the database listing cache. Valid values are none, global, which shares the cache across sessions, and session, which keeps the cache private to one session. The default is none.
• database_cache_size (optional): The maximum number of entries in the database listing cache. Set this option to 0 for an unbounded cache.
• database_cache_ttl_secs (optional): The time-to-live for cached database listings. Set this option in seconds. Set it to 0 to disable expiration.
• table_cache_type (optional): The scope of the table listing cache. Valid values are none, global, and session. The default is none.
• table_cache_size (optional): The maximum number of entries in the table listing cache. Set this option to 0 for an unbounded cache.
• table_cache_ttl_secs (optional): The time-to-live for cached table listings. Set this option in seconds. Set it to 0 to disable expiration.
• view_cache_type (optional): The scope of the view listing cache. Valid values are none, global, and session. The default is none.
• view_cache_size (optional): The maximum number of entries in the view listing cache. Set this option to 0 for an unbounded cache.
• view_cache_ttl_secs (optional): The time-to-live for cached view listings. Set this option in seconds. Set it to 0 to disable expiration.

The cache is automatically invalidated when a write operation (like CREATE TABLE or DROP DATABASE) is performed through Sail.

Support Matrix

The following catalog providers are available in Sail. Table DDL capabilities are listed below.

Catalog Provider	Supported
Memory	✅
Iceberg REST	✅
Unity Catalog	✅
AWS Glue	✅
OneLake	✅
Hive Metastore	✅

Lakehouse DDL

The following matrices show Sail's Delta Lake and Iceberg DDL support by catalog provider. For SQL syntax, see Delta Lake table DDL and Iceberg Spark DDL.

✅ Supported · ⚠️ Supported with limitations · 🚧 Not supported

ALTER COLUMN rows refer to top-level columns unless stated otherwise. HMS refers to Hive Metastore.

Delta Lake

DDL operation	Memory	HMS	AWS Glue	Iceberg REST	Unity (managed)	Unity (external)	OneLake
CREATE TABLE [IF NOT EXISTS]	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE ... AS SELECT (CTAS)	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE ... LOCATION (register existing table)	✅	✅	✅	🚧	🚧	✅	🚧
CREATE TABLE ... PARTITIONED BY	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE with column defaults	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE with generated columns	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE with identity columns	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE with NOT NULL columns	✅	✅	✅	🚧	✅	✅	🚧
CREATE TABLE with table/column comments	✅	✅	✅	🚧	✅	✅	🚧
REPLACE TABLE / CREATE OR REPLACE TABLE	✅	🚧	🚧	🚧	🚧	🚧	🚧
REPLACE TABLE ... AS SELECT / CREATE OR REPLACE TABLE ... AS SELECT	✅	🚧	🚧	🚧	🚧	🚧	🚧
CREATE TABLE ... LIKE	🚧	🚧	🚧	🚧	🚧	🚧	🚧
DROP TABLE [IF EXISTS]	✅	✅	✅	🚧	✅	✅	🚧
DROP TABLE ... PURGE (delete data)	🚧	🚧	🚧	🚧	🚧	🚧	🚧
TRUNCATE TABLE	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... RENAME TO	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... ADD COLUMNS	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... DROP COLUMNS	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... RENAME COLUMN	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... REPLACE COLUMNS	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER/CHANGE COLUMN ... TYPE	✅	✅	✅	🚧	✅	⚠️	🚧
ALTER/CHANGE COLUMN ... TYPE (nested field)	✅	🚧	🚧	🚧	✅	⚠️	🚧
ALTER COLUMN ... SET DEFAULT	✅	✅	✅	🚧	✅	⚠️	🚧
ALTER COLUMN ... DROP DEFAULT	✅	✅	✅	🚧	✅	⚠️	🚧
ALTER/CHANGE COLUMN ... COMMENT	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER/CHANGE COLUMN ... FIRST/AFTER	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER COLUMN ... SET/DROP NOT NULL	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER COLUMN ... SYNC IDENTITY	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... ADD CONSTRAINT ... CHECK	✅	✅	✅	🚧	🚧	⚠️	🚧
ALTER TABLE ... DROP CONSTRAINT	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... SET TBLPROPERTIES	✅	✅	✅	🚧	✅	⚠️	🚧
ALTER TABLE ... UNSET TBLPROPERTIES [IF EXISTS]	✅	✅	✅	🚧	✅	⚠️	🚧
ALTER TABLE ... SET LOCATION	🚧	🚧	🚧	🚧	🚧	🚧	🚧
COMMENT ON TABLE/COLUMN	🚧	🚧	🚧	🚧	🚧	🚧	🚧
CREATE/ALTER TABLE ... CLUSTER BY	🚧	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... DROP FEATURE	🚧	🚧	🚧	🚧	🚧	🚧	🚧
CREATE [OR REPLACE] TABLE ... SHALLOW CLONE	🚧	🚧	🚧	🚧	🚧	🚧	🚧
CONVERT TO DELTA	🚧	🚧	🚧	🚧	🚧	🚧	🚧

Delta type widening requires delta.enableTypeWidening=true and a supported type promotion. Nested type changes are available through Memory and Unity; HMS and Glue support top-level type changes. Column defaults target top-level columns. Identity columns must be non-partition BIGINT columns with a nonzero step.

For external Delta tables in Unity, ALTER TABLE updates the Delta log, but DESCRIBE and SHOW TBLPROPERTIES can still show the schema and properties recorded when the table was registered. Managed Delta tables support schema and property changes, but do not support ADD CONSTRAINT.

Iceberg

DDL operation	Memory	HMS	AWS Glue	Iceberg REST	Unity	OneLake
CREATE TABLE [IF NOT EXISTS]	✅	✅	✅	✅	🚧	🚧
CREATE TABLE ... AS SELECT (CTAS)	✅	✅	✅	✅	🚧	🚧
CREATE TABLE ... LOCATION (register existing table)	✅	✅	✅	⚠️	🚧	🚧
CREATE TABLE ... PARTITIONED BY (including transforms)	✅	✅	✅	✅	🚧	🚧
CREATE TABLE with column defaults	✅	✅	✅	⚠️	🚧	🚧
CREATE TABLE with NOT NULL columns	✅	✅	✅	✅	🚧	🚧
CREATE TABLE with table/column comments	✅	✅	✅	✅	🚧	🚧
REPLACE TABLE / CREATE OR REPLACE TABLE	✅	🚧	🚧	🚧	🚧	🚧
REPLACE TABLE ... AS SELECT / CREATE OR REPLACE TABLE ... AS SELECT	✅	🚧	🚧	🚧	🚧	🚧
CREATE TABLE ... LIKE	🚧	🚧	🚧	🚧	🚧	🚧
DROP TABLE [IF EXISTS]	✅	✅	✅	✅	🚧	🚧
DROP TABLE ... PURGE (delete data)	🚧	🚧	🚧	⚠️	🚧	🚧
TRUNCATE TABLE	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... RENAME TO	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... ADD COLUMN(S)	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... DROP COLUMN(S)	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... RENAME COLUMN	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... REPLACE COLUMNS	🚧	🚧	🚧	🚧	🚧	🚧
ALTER/CHANGE COLUMN ... TYPE	✅	✅	✅	✅	🚧	🚧
ALTER/CHANGE COLUMN ... TYPE (nested field)	🚧	🚧	🚧	🚧	🚧	🚧
ALTER COLUMN ... SET DEFAULT	✅	✅	✅	⚠️	🚧	🚧
ALTER COLUMN ... DROP DEFAULT	✅	✅	✅	⚠️	🚧	🚧
ALTER COLUMN ... COMMENT	🚧	🚧	🚧	🚧	🚧	🚧
ALTER COLUMN ... FIRST/AFTER	🚧	🚧	🚧	🚧	🚧	🚧
ALTER COLUMN ... DROP NOT NULL	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... SET TBLPROPERTIES	✅	✅	✅	✅	🚧	🚧
ALTER TABLE ... UNSET TBLPROPERTIES [IF EXISTS]	✅	✅	✅	✅	🚧	🚧
ALTER TABLE ... SET TBLPROPERTIES ('format-version' = ...) (upgrade)	✅	✅	✅	⚠️	🚧	🚧
ALTER TABLE ... SET LOCATION	🚧	🚧	🚧	🚧	🚧	🚧
COMMENT ON TABLE/COLUMN	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... ADD PARTITION FIELD	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... DROP PARTITION FIELD	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... REPLACE PARTITION FIELD	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... WRITE ORDERED BY	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... WRITE LOCALLY ORDERED BY	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... WRITE UNORDERED	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... WRITE DISTRIBUTED BY PARTITION	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... SET/DROP IDENTIFIER FIELDS	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... CREATE/REPLACE/DROP BRANCH	🚧	🚧	🚧	🚧	🚧	🚧
ALTER TABLE ... CREATE/REPLACE/DROP TAG	🚧	🚧	🚧	🚧	🚧	🚧

Iceberg type promotions permit INT to BIGINT, FLOAT to DOUBLE, and decimal precision increases at the same scale. Type and default changes target top-level columns. Column defaults require format version 3 and typed literals.

Iceberg REST operations require server support for the requested endpoints and format version. See Iceberg REST table DDL for registration, format-version, and data-deletion requirements. Branch/tag rows include the IF NOT EXISTS and CREATE OR REPLACE variants.

Catalog and Storage Requirements

Register an existing table using CREATE TABLE ... USING delta|iceberg LOCATION '...' without a column list. The location must be accessible to Sail. Unity location registration applies to external Delta tables; native Iceberg DDL uses an Iceberg REST catalog connection instead of the Unity table API.

Memory and HMS drop table registrations without deleting the data, including when PURGE is supplied. Glue and Unity reject PURGE.