E-MapReduce:Iceberg Catalog

Usage notes​

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

Syntax​

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

Parameters

The parameters vary depending on the metadata service that Iceberg uses.

Example

The following example creates an Iceberg catalog named iceberg_catalog_hms.

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"
);

Syntax

Switch to the target Iceberg catalog and then run 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 file path of the database. It supports HDFS and Alibaba Cloud OSS:

• If you use HDFS as the storage system, set Prefix to hdfs.

• If you use Alibaba Cloud OSS as the storage system, set Prefix to oss.

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 parameters are described in the following table.

  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. The LARGEINT, HLL, and BITMAP types are not supported.

  Note

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

• partition_desc

  The syntax for partition_desc is as follows.

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

  Currently, StarRocks only supports identity transforms. This means a partition is created for each unique partition value.

  Note

  Partition key columns must be declared last in the column list. They support data types other than FLOAT, DOUBLE, DECIMAL, or DATETIME. NULL values are not supported.

• PROPERTIES

  You can declare the properties of the Iceberg table in the PROPERTIES clause in the "key"="value" format. For more information, see Iceberg table properties. The following table lists some common properties.

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

Examples

• Create a non-partitioned table unpartition_tbl that contains the id and score columns.

  CREATE TABLE unpartition_tbl
  (
      id int,
      score double
  );

• Create a partitioned table partition_tbl_1 that contains the action, id, and dt columns, and define id and dt as 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 source table partition_tbl_1 and create a partitioned table partition_tbl_2 based on the query result. 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;

Syntax

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

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

Parameters

Examples

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

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

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

• Insert the result data 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 the result data of a SELECT query that reads data from the partition_tbl_1 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 result data of a SELECT query into the partition where dt='2023-09-01' and id=1 in the partition_tbl_2 table.

  • 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';

• Overwrite all values in the action column with close in the partition where dt='2023-09-01' and id=1 in the partition_tbl_1 table:

  • 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';