StarRocks supports querying, writing to, and managing Iceberg data through an Iceberg Catalog and is compatible with multiple storage systems and file formats. - E-MapReduce

Background

With an Iceberg Catalog, you can:

Query data in Iceberg directly through the Iceberg Catalog without manually creating tables.
Process, model, and load data from Iceberg into StarRocks using INSERT INTO or an asynchronous materialized view (available since v2.5).
Create or drop Iceberg databases and tables from StarRocks, or write data from StarRocks tables to Parquet-formatted Iceberg tables using INSERT INTO (available since v3.1).

To ensure proper access to your Iceberg data, the StarRocks cluster must be able to access the storage system and metadata service of the Iceberg cluster. StarRocks supports the following storage systems and metadata services:

Hadoop Distributed File System (HDFS) or Alibaba Cloud Object Storage Service (OSS).
Supported metadata services include Hive Metastore (HMS) and Data Lake Formation (DLF) 1.0 (Legacy).

Usage notes

When you query Iceberg data from StarRocks, note the following:

File format	Compression formats	Iceberg table version
Parquet	SNAPPY, LZ4, ZSTD, GZIP, and NO_COMPRESSION	v1 tables: Supported. v2 tables: Position deletes are supported from v3.1. Equality deletes are supported from v3.1.10+, v3.2.5+, and v3.3.
ORC	ZLIB, SNAPPY, LZO, LZ4, ZSTD, and NO_COMPRESSION	v1 tables: Supported. v2 tables: Position deletes are supported from v3.0. Equality deletes are supported from v3.1.8+, v3.2.3+, and v3.3.

Create Iceberg catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
[COMMENT <comment>]
PROPERTIES
(
    "type" = "iceberg",
    MetastoreParams
)

Parameters

Parameters depend on the metadata service.

HMS

catalog_name: Required. The name of the Iceberg Catalog. The naming conventions are as follows:
- Must start with a letter and can contain only letters (a-z or A-Z), digits (0-9), or underscores (_).
- The name cannot exceed 64 characters.
- The catalog name is case-sensitive.
comment: Optional. The description of the Iceberg Catalog.
type: The type of the data source. Set to iceberg.

MetastoreParams: Parameters for StarRocks to access the Iceberg cluster's metadata service.

Property	Description
iceberg.catalog.type	The Iceberg catalog type. Must be `hive`.
hive.metastore.uris	The Hive Metastore URI. The format is `thrift://<hive_metastore_ip>:<port>`. The default port is 9083.

DLF 1.0 (Legacy)

catalog_name: Required. The name of the Iceberg Catalog. The naming conventions are as follows:
- It must start with a letter and can contain only letters (a-z or A-Z), digits (0-9), or underscores (_).
- The name cannot exceed 64 characters.
- The catalog name is case-sensitive.
comment: Optional. The description of the Iceberg Catalog.
type: The type of the data source. Set to iceberg.

MetastoreParams: Parameters for StarRocks to access the metadata service of the Iceberg cluster.

Property	Description
iceberg.catalog.type	The Iceberg catalog type. Set to `dlf`.
dlf.catalog.id	The ID of an existing DLF data catalog. If `dlf.catalog.id` is not set, StarRocks uses the default DLF catalog.

DLF

Important

If you use DLF, you must use a configured RAM user to connect to StarRocks Manager and perform the required operations. For details, see Use a DLF catalog.

catalog_name: Required. The name of the Iceberg Catalog. The naming conventions are as follows:
- It must start with a letter and can contain only letters (a-z or A-Z), digits (0-9), or underscores (_).
- The name cannot exceed 64 characters.
comment: Optional. The description of the Iceberg Catalog.
type: The type of the data source. Set to iceberg.

CatalogParams: Parameters for StarRocks to access the Iceberg cluster's metadata. The configuration varies depending on the metadata type used.

Parameter	Required	Description
`<catalog_name>`	Yes	The name of the DLF data catalog, for example, `dlf_catalog`.
`type`	Yes	The catalog type. For an Iceberg data source, this must be set to `iceberg`.
`uri`	Yes	The DLF REST API address. The format is `http://<VPC_endpoint>/iceberg`, where `<VPC_endpoint>` is the VPC access domain name of DLF in the specified region. For specific values, see service endpoints. Example: `http://cn-hangzhou-vpc.dlf.aliyuncs.com/iceberg`.
`iceberg.catalog.type`	Yes	The Iceberg Catalog type. For DLF, this must be set to `dlf_rest`.
`warehouse`	Yes	The name of the catalog in DLF. You can obtain this name from the Catalogs page of the Data Lake Formation console.
`rest.signing-region`	Yes	The `region ID` of the DLF service, for example, `cn-hangzhou`.

Examples

The following example creates an Iceberg Catalog named iceberg_catalog_hms.

HMS

CREATE EXTERNAL CATALOG iceberg_catalog_hms
PROPERTIES
(
    "type" = "iceberg",
    "iceberg.catalog.type" = "hive",
    "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083"
);

DLF 1.0 (Legacy)

CREATE EXTERNAL CATALOG iceberg_catalog_hms
PROPERTIES
(
    "type" = "iceberg",
    "iceberg.catalog.type" = "dlf",
    "dlf.catalog.id" = "sr_dlf"
);

DLF

CREATE EXTERNAL CATALOG iceberg_catalog
properties
( 
    "type" = "iceberg",
    "iceberg.catalog.type" = "dlf_rest",
    "uri" = "http://cn-hangzhou-vpc.dlf.aliyuncs.com/iceberg",
    "warehouse" = "iceberg_test",
    "rest.signing-region" = "cn-hangzhou"
);

View Iceberg catalog

You can use SHOW CATALOGS to query all catalogs in the current StarRocks cluster.

SHOW CATALOGS;

You can also use SHOW CREATE CATALOG to query the creation statement of an external catalog. For example, run the following command to query the creation statement of the Iceberg Catalog iceberg_catalog_hms.

SHOW CREATE CATALOG iceberg_catalog_hms;

Create Iceberg database

Similar to an internal catalog in StarRocks, if you have the CREATE DATABASE privilege on an Iceberg Catalog, you can use CREATE DATABASE to create a database within that catalog. This feature is available from v3.1.

Note

You can use the GRANT and REVOKE statements to grant and revoke privileges for users and roles.

Syntax

Switch to the target Iceberg Catalog, and use the following statement to create an Iceberg database.

CREATE DATABASE <database_name>
[PROPERTIES ("location" = "<prefix>://<path_to_database>/<database_name.db>/")]

Parameters

The location parameter specifies the database's file path. It supports HDFS and Alibaba Cloud Object Storage Service (OSS):

If you choose HDFS as the storage system, the prefix is hdfs.
If you choose Alibaba Cloud Object Storage Service (OSS) as the storage system, the prefix is oss.

Note

If you do not specify the location parameter, StarRocks creates the database in the default path of the current Iceberg Catalog.

Switch between Iceberg catalogs and databases

You can switch to the target Iceberg Catalog and database by using one of the following methods:

Use SET CATALOG to specify the active Iceberg Catalog for the current session, and then use USE to specify the database.

-- Switch the active catalog for the current session.
SET CATALOG <catalog_name>;

-- Specify the active database for the current session.
USE <db_name>;

Use USE to switch the session directly to a specified database within the target Iceberg Catalog.
```
USE <catalog_name>.<db_name>;
```

Drop Iceberg database

Similar to a StarRocks internal database, if you have the DROP privilege on an Iceberg database, you can use DROP DATABASE to drop it. This feature is available from v3.1 and supports only dropping empty databases.

Note

You can use the GRANT and REVOKE statements to grant and revoke privileges for users and roles.

Dropping a database does not delete the corresponding file path from HDFS or object storage. Switch to the target Iceberg Catalog, and then use the following statement to drop an Iceberg database.

DROP DATABASE <database_name>;

Drop Iceberg catalog

You can use DROP CATALOG to drop an external catalog. For example, run the following command to drop iceberg_catalog_hms.

DROP Catalog iceberg_catalog_hms;

Create Iceberg table

Similar to a StarRocks internal database, if you have the CREATE TABLE privilege on an Iceberg database, you can use CREATE TABLE or CREATE TABLE AS SELECT (CTAS) to create a table within that database. This feature is available from v3.1. Switch to the target Iceberg Catalog and database, and then use the following syntax to create an Iceberg table.

Syntax

CREATE TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...
partition_column_definition1,partition_column_definition2...])
[partition_desc]
[PROPERTIES ("key" = "value", ...)]
[AS SELECT query]

Parameters

column_definition

The syntax for column_definition is as follows.

col_name col_type [COMMENT 'comment']

The following table describes the related parameters.

Parameter

Description

col_name

The name of the column.

col_type

The data type of the column.

The following data types are currently supported: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, CHAR, VARCHAR(length), ARRAY, MAP, and STRUCT.
LARGEINT, HLL, and BITMAP are not supported.

Note

The default value for all non-partition key columns is NULL. Partition key columns must be declared last and cannot be NULL.

partition_desc

The syntax for partition_desc is as follows.
```
PARTITION BY (par_col1[, par_col2...])
```
StarRocks supports only identity transforms, which create one partition for each unique partition value.

Note
Partition key columns must be declared last. Supported data types exclude FLOAT, DOUBLE, DECIMAL, and DATETIME. NULL values are not supported.

PROPERTIES

You can declare properties for an Iceberg table in the PROPERTIES clause in the "key"="value" format. For details, see Iceberg table properties. The following are several common properties.

Property	Description
location	The file path where the Iceberg table is located. You do not need to specify the `location` parameter when you use HMS as the metadata service.
file_format	The file format of the Iceberg table. Currently, only the Parquet format is supported. Default value: `parquet`.
compression_codec	The compression format for Iceberg tables. The supported formats are SNAPPY, GZIP, ZSTD, and LZ4. The default value is `gzip`. This property has been deprecated since version 3.2.3. After this version, the compression algorithm for writing to Iceberg tables is controlled by the `connector_sink_compression_codec` session variable.

Examples

Create a non-partitioned table named unpartition_tbl that contains two columns: id and score.
```
CREATE TABLE unpartition_tbl
(
    id int,
    score double
);
```
Create a partitioned table partition_tbl_1 with the action, id, and dt columns, where id and dt are specified as the partition key columns.
```
CREATE TABLE partition_tbl_1
(
    action varchar(20),
    id int NOT NULL,
    dt date NOT NULL
)
PARTITION BY (id,dt);
```
Query data from the original table partition_tbl_1, create a partitioned table partition_tbl_2 based on the query result, and define id and dt as the partition key columns for partition_tbl_2.
```
CREATE TABLE partition_tbl_2
PARTITION BY (id, dt)
AS SELECT * from partition_tbl_1;
```

View Iceberg table schema

You can use the following methods to view the schema of an Iceberg table:

View the table schema.

DESC[RIBE] <catalog_name>.<database_name>.<table_name>;

View the table schema and the file storage location from the CREATE statement.
```
SHOW CREATE TABLE <catalog_name>.<database_name>.<table_name>;
```

Insert data into Iceberg table

Similar to a StarRocks internal table, if you have the INSERT privilege on an Iceberg table, you can use INSERT to write data from a StarRocks table to that Iceberg table. Currently, writing data is supported only for Parquet-formatted Iceberg tables. This feature is available from v3.1.

Note

You can use the GRANT and REVOKE statements to grant and revoke privileges for users and roles.

Switch to the target Iceberg Catalog and database, and then use the following syntax to write data from a StarRocks table to a Parquet-formatted Iceberg table.

Syntax

INSERT {INTO | OVERWRITE} <table_name>
[ (column_name [, ...]) ]
{ VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

-- Write data to a specific partition.
INSERT {INTO | OVERWRITE} <table_name>
PARTITION (par_col1=<value> [, par_col2=<value>...])
{ VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

Note

The partition key column cannot be NULL, so you must ensure that it has a value during import.

Parameters

Parameter	Description
INTO	Appends data to the target table.
OVERWRITE	Overwrites data in the target table.
column_name	The destination columns for the import. You can specify one or more columns. If you specify multiple columns, you must separate them with a comma (`,`). The specified columns must exist in the target table and must include the partition key column. The column names can be different from those in the source table, but their order must correspond. If you do not specify this parameter, data is imported into all columns of the target table by default. If a non-partition column in the target table is not included in the list of destination columns, the default value `NULL` is written to it. If the data type of a result column from the query statement does not match the data type of the corresponding destination column, an implicit conversion is attempted. If the conversion fails, the `INSERT INTO` statement returns a syntax parsing error.
expression	An expression used to assign a value to the corresponding column.
DEFAULT	Assigns the default value to the corresponding column.
query	A query statement whose results are loaded into the target table. The query statement can be any SQL query syntax supported by StarRocks.
PARTITION	The target partition for the import. You must specify all partition key columns of the target table. The order of the specified partition key columns can be different from the order defined when the table was created. When you specify a partition, you cannot specify the target columns for the import by using column names (`column_name`).

Examples

The following write statements use the default Parquet format as an example.

Insert the following three rows of data into the table partition_tbl_1.

INSERT INTO partition_tbl_1
VALUES
    ("buy", 1, "2023-09-01"),
    ("sell", 2, "2023-09-02"),
    ("buy", 3, "2023-09-03");

Insert the results of a SELECT query that includes a simple calculation into the partition_tbl_1 table in the specified column order.
```
INSERT INTO partition_tbl_1 (id, action, dt) SELECT 1+1, 'buy', '2023-09-03';
```

Insert into the partition_tbl_1 table the results of a SELECT query that reads data from the table itself.

INSERT INTO partition_tbl_1 SELECT 'buy', 1, date_add(dt, INTERVAL 2 DAY) FROM partition_tbl_1
WHERE id=1;

Insert the results of a SELECT query into the partition in the partition_tbl_2 table where dt='2023-09-01' and id=1.

Method 1

INSERT INTO partition_tbl_2 SELECT 'order', 1, '2023-09-01';

Method 2

INSERT INTO partition_tbl_2 partition(dt='2023-09-01',id=1) SELECT 'order';

For the partition_tbl_1 table, in the partition where dt='2023-09-01' and id=1, overwrite all values in the action column to close:

Method 1

INSERT OVERWRITE partition_tbl_1 SELECT 'close', 1, '2023-09-01';

Method 2

INSERT OVERWRITE partition_tbl_1 partition(dt='2023-09-01',id=1) SELECT 'close';

Query data from Iceberg table

Use SHOW DATABASES to view the databases in the Iceberg cluster that belong to the specified catalog.
```
SHOW DATABASES FROM <catalog_name>;
```
Switch to the target Iceberg Catalog and database.
Use SELECT to query the target table in the target database.
```
SELECT count(*) FROM <table_name> LIMIT 10;
```

Drop Iceberg table

Similar to a StarRocks internal table, if you have the DROP privilege on an Iceberg table, you can use DROP TABLE to drop it. This feature is available from v3.1.

Note

You can use the GRANT and REVOKE statements to grant and revoke privileges for users and roles.

Deleting a table does not delete the corresponding file paths and data on HDFS or object storage. Forcibly deleting a table (by adding the FORCE keyword) deletes the data on HDFS or object storage, but does not delete the corresponding file path. Switch to the target Iceberg Catalog and database, and then use the following statement to delete the Iceberg table.

DROP TABLE <table_name> FORCE;

References

For more information about Iceberg, see Iceberg Overview.

Background

Usage notes

Create Iceberg catalog

Syntax​

Parameters

HMS

DLF 1.0 (Legacy)

DLF

Examples

HMS

DLF 1.0 (Legacy)

DLF

View Iceberg catalog

Create Iceberg database

Syntax

Parameters

Switch between Iceberg catalogs and databases

Drop Iceberg database

Drop Iceberg catalog

Create Iceberg table

Syntax​

Parameters

Examples

View Iceberg table schema

Insert data into Iceberg table

Syntax

Parameters

Examples

Query data from Iceberg table

Drop Iceberg table

References

Syntax

Syntax