Iceberg catalog

An Iceberg catalog is an external catalog that enables you to query data from Apache Iceberg without ingestion into CelerData Cloud Serverless. To ensure successful SQL workloads on your Iceberg cluster, CelerData must be able to access the storage system and metastore of your Iceberg cluster. CelerData supports the following storage systems and metastores:

• Object storage like AWS S3

• Metastore like Hive metastore or AWS Glue

Usage notes

• The file formats of Iceberg that CelerData supports are Parquet and ORC:

  • Parquet files support the following compression formats: SNAPPY, LZ4, ZSTD, GZIP, and NO_COMPRESSION.
  • ORC files support the following compression formats: ZLIB, SNAPPY, LZO, LZ4, ZSTD, and NO_COMPRESSION.

• In addition to v1 tables, Iceberg catalogs support ORC-formatted v2 tables and Parquet-formatted v2 tables.

Create an Iceberg catalog

Syntax

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

Parameters

catalog_name

The name of the Iceberg catalog. The naming conventions are as follows:

• The name can contain letters, digits (0-9), and underscores (_). It must start with a letter.
• The name is case-sensitive and cannot exceed 1023 characters in length.

comment

The description of the Iceberg catalog. This parameter is optional.

type

The type of your data source. Set the value to iceberg.

MetastoreParams

A set of parameters about how CelerData integrates with the metastore of your data source.

Hive metastore

If you choose Hive metastore as the metastore of your data source, check that CelerData can access the host of your Hive metastore.

NOTE

In normal cases, you can take one of the following actions to enable integration between CelerData and your Hive metastore:

• Deploy CelerData and your Hive metastore on the same VPC.
• Configure a VPC peering connection between the VPC hosting CelerData and the VPC hosting your Hive metastore.

Then, check the configurations of the security group of your Hive metastore to ensure that its inbound rules allow inbound traffic from CelerData's security group and that its port range covers the default port 9083.

"iceberg.catalog.type" = "hive",
"hive.metastore.uris" = "<hive_metastore_uri>"

The following table describes the parameter you need to configure in MetastoreParams.

AWS Glue

If you choose AWS Glue as the metastore of your data source, which is supported only when you choose AWS S3 as storage, take one of the following actions:

• To choose the instance profile-based authentication method, configure MetastoreParams as follows:

  "iceberg.catalog.type" = "glue",
  "aws.glue.use_instance_profile" = "true",
  "aws.glue.region" = "<aws_glue_region>"

• To choose the assumed role-based authentication method, configure MetastoreParams as follows:

  "iceberg.catalog.type" = "glue",
  "aws.glue.use_instance_profile" = "true",
  "aws.glue.iam_role_arn" = "<iam_role_arn>",
  "aws.glue.region" = "<aws_glue_region>"

• To choose the IAM user-based authentication method, configure MetastoreParams as follows:

  "iceberg.catalog.type" = "glue",
  "aws.glue.use_instance_profile" = "false",
  "aws.glue.access_key" = "<iam_user_access_key>",
  "aws.glue.secret_key" = "<iam_user_secret_key>",
  "aws.glue.region" = "<aws_s3_region>"

The following table describes the parameters you need to configure in MetastoreParams.

For information about how to choose an authentication method for accessing AWS Glue and how to configure an access control policy in the AWS IAM Console, see Authentication parameters for accessing AWS Glue.

StorageCredentialParams

A set of parameters about how CelerData integrates with your object storage.

AWS S3

If you choose AWS S3 as storage for your Hive cluster, take one of the following actions:

• To choose the instance profile-based authentication method, configure StorageCredentialParams as follows:

  "aws.s3.use_instance_profile" = "true",
  "aws.s3.region" = "<aws_s3_region>"

• To choose the assumed role-based authentication method, configure StorageCredentialParams as follows:

  "aws.s3.use_instance_profile" = "true",
  "aws.s3.iam_role_arn" = "<iam_role_arn>",
  "aws.s3.region" = "<aws_s3_region>"

• To choose the IAM user-based authentication method, configure StorageCredentialParams as follows:

  "aws.s3.use_instance_profile" = "false",
  "aws.s3.access_key" = "<iam_user_access_key>",
  "aws.s3.secret_key" = "<iam_user_secret_key>",
  "aws.s3.region" = "<aws_s3_region>"

The following table describes the parameters you need to configure in StorageCredentialParams.

For information about how to choose an authentication method for accessing AWS S3 and how to configure an access control policy in AWS IAM Console, see Authentication parameters for accessing AWS S3.

Examples

The following examples create an Iceberg catalog named iceberg_catalog_hms or iceberg_catalog_glue, depending on the type of metastore you use, to query data from your Iceberg cluster.

AWS S3

If you choose instance profile-based credential

• If you use Hive metastore in your Iceberg cluster, run a command like below:

  CREATE EXTERNAL CATALOG iceberg_catalog_hms
  PROPERTIES
  (
      "type" = "iceberg",
      "iceberg.catalog.type" = "hive",
      "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083",
      "aws.s3.use_instance_profile" = "true",
      "aws.s3.region" = "us-west-2"
  );

• If you use AWS Glue in your Amazon EMR Iceberg cluster, run a command like below:

  CREATE EXTERNAL CATALOG iceberg_catalog_glue
  PROPERTIES
  (
      "type" = "iceberg",
      "iceberg.catalog.type" = "glue",
      "aws.glue.use_instance_profile" = "true",
      "aws.glue.region" = "us-west-2",
      "aws.s3.use_instance_profile" = "true",
      "aws.s3.region" = "us-west-2"
  );

If you choose assumed role-based credential

• If you use Hive metastore in your HIceberg cluster, run a command like below:

  CREATE EXTERNAL CATALOG iceberg_catalog_hms
  PROPERTIES
  (
      "type" = "iceberg",
      "iceberg.catalog.type" = "hive",
      "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083",
      "aws.s3.use_instance_profile" = "true",
      "aws.s3.iam_role_arn" = "arn:aws:iam::081976408565:role/test_s3_role",
      "aws.s3.region" = "us-west-2"
  );

• If you use AWS Glue in your Amazon EMR Iceberg cluster, run a command like below:

  CREATE EXTERNAL CATALOG iceberg_catalog_glue
  PROPERTIES
  (
      "type" = "iceberg",
      "iceberg.catalog.type" = "glue",
      "aws.glue.use_instance_profile" = "true",
      "aws.glue.iam_role_arn" = "arn:aws:iam::081976408565:role/test_glue_role",
      "aws.glue.region" = "us-west-2",
      "aws.s3.use_instance_profile" = "true",
      "aws.s3.iam_role_arn" = "arn:aws:iam::081976408565:role/test_s3_role",
      "aws.s3.region" = "us-west-2"
  );

If you choose IAM user-based credential

• If you use Hive metastore in your Iceberg cluster, run a command like below:

  CREATE EXTERNAL CATALOG iceberg_catalog_hms
  PROPERTIES
  (
      "type" = "iceberg",
      "iceberg.catalog.type" = "hive",
      "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083",
      "aws.s3.use_instance_profile" = "false",
      "aws.s3.access_key" = "<iam_user_access_key>",
      "aws.s3.secret_key" = "<iam_user_access_key>",
      "aws.s3.region" = "us-west-2"
  );

• If you use AWS Glue in your Amazon EMR Iceberg cluster, run a command like below:

  CREATE EXTERNAL CATALOG iceberg_catalog_glue
  PROPERTIES
  (
      "type" = "iceberg",
      "iceberg.catalog.type" = "glue",
      "aws.glue.use_instance_profile" = "false",
      "aws.glue.access_key" = "<iam_user_access_key>",
      "aws.glue.secret_key" = "<iam_user_secret_key>",
      "aws.glue.region" = "us-west-2",
      "aws.s3.use_instance_profile" = "false",
      "aws.s3.access_key" = "<iam_user_access_key>",
      "aws.s3.secret_key" = "<iam_user_secret_key>",
      "aws.s3.region" = "us-west-2"
  );

View Iceberg catalogs

You can use SHOW CATALOGS to query all catalogs in your CelerData cloud account:

SHOW CATALOGS;

You can also use SHOW CREATE CATALOG to query the creation statement of an external catalog. The following example queries the creation statement of an Iceberg catalog named iceberg_catalog_glue:

SHOW CREATE CATALOG iceberg_catalog_glue;

Drop an Iceberg catalog

You can use DROP CATALOG to drop an external catalog.

The following example drops an Iceberg catalog named iceberg_catalog_glue:

DROP Catalog iceberg_catalog_glue;

View the schema of an Iceberg table

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

• View schema

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

• View schema and location from the CREATE statement

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

Query an Iceberg table

1. Use SHOW DATABASES to view the databases in your Iceberg cluster.

   SHOW DATABASES <catalog_name>;

2. Use SET CATALOG to switch to the destination catalog in the current session:

   SET CATALOG <catalog_name>;

   Then, use USE to specify the active database in the current session:

   USE <db_name>;

   Or, you can use USE to directly specify the active database in the destination catalog:

   USE <catalog_name>.<db_name>;

3. Use SELECT to query the destination table in the specified database:

   SELECT count(*) FROM <table_name> LIMIT 10

Create an Iceberg database

Similar to the internal catalog of CelerData, if you have the CREATE DATABASE privilege on an Iceberg catalog, you can use the CREATE DATABASE statement to create databases in that catalog.

NOTE

You can grant and revoke privileges by using GRANT and REVOKE.

CREATE DATABASE <database_name>
[properties ("location" = "s3://path_to_db/<database_name.db>/")]

You can use the location parameter to specify the file path in which you want to create the database. If you do not specify the location parameter, CelerData creates the database in the default file path of the Iceberg catalog.

Drop an Iceberg database

Similar to the internal databases of CelerData, if you have the DROP privilege on an Iceberg database, you can use the DROP DATABASE statement to drop that database.

NOTE

You can grant and revoke privileges by using GRANT and REVOKE.

When you drop an Iceberg database, the database's file path on your cloud storage will not be dropped along with the database.

DROP DATABASE <database_name>;

Create an Iceberg table

Similar to the internal databases of CelerData, if you have the CREATE TABLE privilege on an Iceberg database, you can use the CREATE TABLE or CREATE TABLE AS SELECT (CTAS) statement to create tables in that database.

NOTE

You can grant and revoke privileges by using GRANT and REVOKE.

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 of column_definition is as follows:

col_name col_type [COMMENT 'comment']

The following table describes the parameters.

NOTICE

All non-partition columns must use NULL as the default value. This means that you must specify DEFAULT "NULL" for each of the non-partition columns in the table creation statement. Additionally, partition columns must be defined following non-partition columns and cannot use NULL as the default value.

partition_desc

The syntax of partition_desc is as follows:

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

Currently CelerData only supports identity transforms, which means that CelerData creates a partition for each unique partition value.

NOTICE

Partition columns must be defined following non-partition columns. Partition columns support all data types excluding FLOAT, DOUBLE, DECIMAL, and DATETIME and cannot use NULL as the default value.

properties

You can specify the table attributes in the "key" = "value" format in properties. See Iceberg table attributes.

Examples

1. Create a non-partitioned table named unpartition_tbl. The table consists of two columns, id and score, as shown below:

   CREATE TABLE unpartition_tbl
   (
       id int,
       score double
   );

2. Create a partitioned table named partition_tbl. The table consists of three columns, action, id, and dt, of which id and dt are defined as partition columns, as shown below:

   CREATE TABLE partition_tbl
   (
       action varchar(20),
       id int,
       dt date
   )
   PARTITION BY (id,dt);

3. Create a partitioned table named partition_tbl. The table consists of four columns, v1, v2, k1, and k2, of which k1 and k2 are defined as partition columns. At the same time, specify that CelerData synchronously queries an existing table named employee and inserts the query result into the new table partition_tbl, as shown below:

   CREATE TABLE partition_tbl 
   (
       v1 int,
       v2 int,
       k1 int,
       k2 int
   )
   PARTITION BY (k1, k2)
   AS SELECT * from employee;

Sink data to an Iceberg table

NOTE

This feature is included in the Premium software edition. Please see the Software editions documentation for details on the differences between Standard and Premium editions if you are subscribed to the Standard edition.

Similar to the internal tables of CelerData, if you have the INSERT privilege on an Iceberg table, you can use the INSERT statement to sink the data of a CelerData table to a Parquet-formatted Iceberg table.

NOTE

You can grant and revoke privileges by using GRANT and REVOKE.

Syntax

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

-- If you want to sink data to specified partitions, use the following syntax:
INSERT {INTO | OVERWRITE} <table_name>
PARTITION (par_col1=<value> [, par_col2=<value>...])
{ VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

NOTICE

Partition columns do not allow NULL values. Therefore, you must make sure that no empty values are loaded into the partition columns of the Iceberg table.

Parameters

Examples

1. Insert a data row into the partition_tbl table:

   INSERT INTO partition_tbl SELECT 'pv', 1, '2023-07-21';

2. Insert the result of a SELECT query, which contains simple computations, into the partition_tbl table:

   INSERT INTO partition_tbl (id, action, dt) SELECT 1+1, 'buy', '2023-07-21';

3. Insert the result of a SELECT query, which reads data from the partition_tbl table, into the same table:

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

4. Insert the result of a SELECT query into the partitions that meet two conditions, dt=‘2023-07-21’ and id=1, of the partition_table table:

   INSERT INTO partition_table SELECT 'order', 1, '2023-07-21';
   INSERT INTO partition_table (dt='2023-07-21',id=1) SELECT 'order';

5. Overwrite all action column values in the partitions that meet two conditions, dt=‘2023-07-21’ and id=1, of the partition_table table with close:

   INSERT OVERWRITE partition_table SELECT 'close', 1, '2023-07-21';
   INSERT OVERWRITE partition_table (dt='2023-07-21',id=1) SELECT 'close';

Drop an Iceberg table

Similar to the internal tables of CelerData, if you have the DROP privilege on an Iceberg table, you can use the DROP TABLE statement to drop that Iceberg table.

NOTE

You can grant and revoke privileges by using GRANT and REVOKE.

When you drop an Iceberg table, the table's file path and data on your cloud storage will not be dropped along with the table.

When you forcibly drop an Iceberg table (namely, with the FORCE keyword specified in the DROP TABLE statement), the table's data on your cloud storage will be dropped along with the table, but the table's file path is retained.

DROP TABLE <table_name> [FORCE];