E-MapReduce:Iceberg Catalog

Prerequisites

Before you begin, make sure your StarRocks cluster can reach both the storage system and the metadata service of the Iceberg cluster.

Supported storage systems:

• Hadoop Distributed File System (HDFS)

• Alibaba Cloud Object Storage Service (OSS)

Supported metadata services:

• Hive Metastore (HMS)

• Data Lake Formation (DLF) 1.0 (Legacy)

• Data Lake Formation (DLF)

Usage notes

The following table summarizes supported file formats, compression formats, and Iceberg table versions when querying Iceberg data from StarRocks.

Create an Iceberg catalog

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

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

CREATE EXTERNAL CATALOG iceberg_catalog_hms
PROPERTIES
(
    "type" = "iceberg",
    "iceberg.catalog.type" = "dlf",
    "dlf.catalog.id" = "sr_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 an Iceberg catalog

Run SHOW CATALOGS to list all catalogs in the current StarRocks cluster.

SHOW CATALOGS;

To view the creation statement of a specific catalog, run SHOW CREATE CATALOG. For example:

SHOW CREATE CATALOG iceberg_catalog_hms;

Create an Iceberg database

If you have the CREATE DATABASE permission on an Iceberg catalog, use CREATE DATABASE to create a database in that catalog. Available from v3.1.

Switch to the target Iceberg catalog, then run:

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

The location parameter specifies the database file path. Set <prefix> based on your storage system:

• HDFS: hdfs

• OSS: oss

If you omit location, StarRocks creates the database in the default path of the current Iceberg catalog.

Switch between Iceberg catalogs and databases

Use either of the following approaches:

• Set the catalog, then set the database:

  -- Switch the active catalog for the current session.
  SET CATALOG <catalog_name>;
  
  -- Set the active database.
  USE <db_name>;

• Switch to a specific database in the target catalog directly:

  USE <catalog_name>.<db_name>;

Drop an Iceberg database

If you have the DROP permission on an Iceberg database, use DROP DATABASE to drop it. Available from v3.1. Only empty databases can be dropped.

Dropping a database does not delete the corresponding file path on HDFS or OSS. Switch to the target Iceberg catalog, then run:

DROP DATABASE <database_name>;

Drop an Iceberg catalog

Run DROP CATALOG to drop an external catalog. For example:

DROP CATALOG iceberg_catalog_hms;

Create an Iceberg table

If you have the CREATE TABLE permission on an Iceberg database, use CREATE TABLE or CREATE TABLE AS SELECT (CTAS) to create a table. Available from v3.1.

Switch to the target Iceberg catalog and database, then run:

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]

col_name col_type [COMMENT 'comment']

PARTITION BY (par_col1[, par_col2...])

CREATE TABLE unpartition_tbl
(
    id int,
    score double
);

CREATE TABLE partition_tbl_1
(
    action varchar(20),
    id int NOT NULL,
    dt date NOT NULL
)
PARTITION BY (id, dt);

CREATE TABLE partition_tbl_2
PARTITION BY (id, dt)
AS SELECT * FROM partition_tbl_1;

View an Iceberg table schema

View the table schema:

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

View the schema and storage location from the CREATE statement:

SHOW CREATE TABLE <catalog_name>.<database_name>.<table_name>;

Insert data into an Iceberg table

If you have the INSERT permission on an Iceberg table, use INSERT to write data from a StarRocks table. Only Parquet-formatted Iceberg tables are supported. Available from v3.1.

Switch to the target Iceberg catalog and database, then run:

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 }

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

INSERT INTO partition_tbl_1 (id, action, dt) SELECT 1+1, 'buy', '2023-09-03';

INSERT INTO partition_tbl_1 SELECT 'buy', 1, date_add(dt, INTERVAL 2 DAY) FROM partition_tbl_1
WHERE 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';

-- 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 an Iceberg table

1. List databases in the Iceberg cluster for the specified catalog:

   SHOW DATABASES FROM <catalog_name>;

2. Switch to the target Iceberg catalog and database.

3. Query the target table:

   SELECT count(*) FROM <table_name> LIMIT 10;

Drop an Iceberg table

If you have the DROP permission on an Iceberg table, use DROP TABLE to drop it. Available from v3.1.

Dropping a table does not delete the corresponding file path and data on HDFS or OSS. A force drop (using the FORCE keyword) deletes the data but not the file path.

Switch to the target Iceberg catalog and database, then run:

DROP TABLE <table_name> FORCE;

References

For more information about Iceberg, see Overview.