Version: 2.5

Delta Lake catalog

A Delta Lake catalog is a kind of external catalog that enables you to query data from Delta Lake without ingestion.

Also, you can directly transform and load data from Delta Lake by using INSERT INTO based on Delta Lake catalogs. StarRocks supports Delta Lake catalogs from v2.5 onwards.

To ensure successful SQL workloads on your Delta Lake cluster, your StarRocks cluster must be able to access the storage system and metastore of your Delta Lake cluster. StarRocks supports the following storage systems and metastores:

Object storage or distributed file system like AWS S3 or HDFS
Metastore like Hive metastore or AWS Glue

Usage notes

The file format of Delta Lake that StarRocks supports is Parquet. Parquet files support the following compression formats: SNAPPY, LZ4, ZSTD, GZIP, and NO_COMPRESSION.
The data types of Delta Lake that StarRocks does not support are MAP and STRUCT.

Integration preparations

Before you create a Delta Lake catalog, make sure your StarRocks cluster can integrate with the storage system and metastore of your Delta Lake cluster.

AWS IAM

If your Delta Lake cluster uses AWS S3 as storage or AWS Glue as metastore, choose your suitable authentication method and make the required preparations to ensure that your StarRocks cluster can access the related AWS cloud resources.

The following authentication methods are recommended:

Instance profile
Assumed role
IAM user

Of the above-mentioned three authentication methods, instance profile is the most widely used.

For more information, see Preparation for authentication in AWS IAM.

HDFS

If you choose HDFS as storage, configure your StarRocks cluster as follows:

(Optional) Set the username that is used to access your HDFS cluster and Hive metastore. By default, StarRocks uses the username of the FE and BE processes to access your HDFS cluster and Hive metastore. You can also set the username by adding export HADOOP_USER_NAME="<user_name>" at the beginning of the fe/conf/hadoop_env.sh file of each FE and at the beginning of the be/conf/hadoop_env.sh file of each BE. After you set the username in these files, restart each FE and each BE to make the parameter settings take effect. You can set only one username for each StarRocks cluster.
When you query Delta Lake data, the FEs and BEs of your StarRocks cluster use the HDFS client to access your HDFS cluster. In most cases, you do not need to configure your StarRocks cluster to achieve that purpose, and StarRocks starts the HDFS client using the default configurations. You need to configure your StarRocks cluster only in the following situations:
- High availability (HA) is enabled for your HDFS cluster: Add the hdfs-site.xml file of your HDFS cluster to the $FE_HOME/conf path of each FE and to the $BE_HOME/conf path of each BE.
- View File System (ViewFs) is enabled for your HDFS cluster: Add the core-site.xml file of your HDFS cluster to the $FE_HOME/conf path of each FE and to the $BE_HOME/conf path of each BE.

NOTE

If an error indicating an unknown host is returned when you send a query, you must add the mapping between the host names and IP addresses of your HDFS cluster nodes to the /etc/hosts path.

Kerberos authentication

If Kerberos authentication is enabled for your HDFS cluster or Hive metastore, configure your StarRocks cluster as follows:

Run the kinit -kt keytab_path principal command on each FE and each BE to obtain Ticket Granting Ticket (TGT) from Key Distribution Center (KDC). To run this command, you must have the permissions to access your HDFS cluster and Hive metastore. Note that accessing KDC with this command is time-sensitive. Therefore, you need to use cron to run this command periodically.
Add JAVA_OPTS="-Djava.security.krb5.conf=/etc/krb5.conf" to the $FE_HOME/conf/fe.conf file of each FE and to the $BE_HOME/conf/be.conf file of each BE. In this example, /etc/krb5.conf is the save path of the krb5.conf file. You can modify the path based on your needs.

Create a Delta Lake catalog

Syntax

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

Parameters

catalog_name

The name of the Delta Lake 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 Delta Lake catalog. This parameter is optional.

type

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

MetastoreParams

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

Hive metastore

If you choose Hive metastore as the metastore of your data source, configure MetastoreParams as follows:

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

NOTE

Before querying Delta Lake data, you must add the mapping between the host names and IP addresses of your Hive metastore nodes to the /etc/hosts path. Otherwise, StarRocks may fail to access your Hive metastore when you start a query.

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

Parameter	Required	Description
hive.metastore.type	Yes	The type of metastore that you use for your Delta Lake cluster. Set the value to `hive`.
hive.metastore.uris	Yes	The URI of your Hive metastore. Format: `thrift://<metastore_IP_address>:<metastore_port>`. If high availability (HA) is enabled for your Hive metastore, you can specify multiple metastore URIs and separate them with commas (`,`), for example, `"thrift://<metastore_IP_address_1>:<metastore_port_1>,thrift://<metastore_IP_address_2>:<metastore_port_2>,thrift://<metastore_IP_address_3>:<metastore_port_3>"`.

AWS Glue

If you choose AWS Glue as the metastore of your data source, take one of the following actions:

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

"hive.metastore.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:

"hive.metastore.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:

"hive.metastore.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.

Parameter	Required	Description
hive.metastore.type	Yes	The type of metastore that you use for your Delta Lake cluster. Set the value to `glue`.
aws.glue.use_instance_profile	Yes	Specifies whether to enable the instance profile-based authentication method and the assumed role-based authentication method. Valid values: `true` and `false`. Default value: `false`.
aws.glue.iam_role_arn	No	The ARN of the IAM role that has privileges on your AWS Glue Data Catalog. If you use the assumed role-based authentication method to access AWS Glue, you must specify this parameter.
aws.glue.region	Yes	The region in which your AWS Glue Data Catalog resides. Example: `us-west-1`.
aws.glue.access_key	No	The access key of your AWS IAM user. If you use the IAM user-based authentication method to access AWS Glue, you must specify this parameter.
aws.glue.secret_key	No	The secret key of your AWS IAM user. If you use the IAM user-based authentication method to access AWS Glue, you must specify this parameter.

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 StarRocks integrates with your storage system. This parameter set is optional.

You need to configure StorageCredentialParams only when your Delta Lake cluster uses AWS S3 as storage.

If your Delta Lake cluster uses any other storage system, you can ignore StorageCredentialParams.

AWS S3

If you choose AWS S3 as storage for your Delta Lake 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.

Parameter	Required	Description
aws.s3.use_instance_profile	Yes	Specifies whether to enable the instance profile-based authentication method and the assumed role-based authentication method. Valid values: `true` and `false`. Default value: `false`.
aws.s3.iam_role_arn	No	The ARN of the IAM role that has privileges on your AWS S3 bucket. If you use the assumed role-based authentication method to access AWS S3, you must specify this parameter.
aws.s3.region	Yes	The region in which your AWS S3 bucket resides. Example: `us-west-1`.
aws.s3.access_key	No	The access key of your IAM user. If you use the IAM user-based authentication method to access AWS S3, you must specify this parameter.
aws.s3.secret_key	No	The secret key of your IAM user. If you use the IAM user-based authentication method to access AWS S3, you must specify this parameter.

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.

S3-compatible storage system

Delta Lake catalogs support S3-compatible storage systems from v2.5 onwards.

If you choose an S3-compatible storage system, such as MinIO, as storage for your Delta Lake cluster, configure StorageCredentialParams as follows to ensure a successful integration:

"aws.s3.enable_ssl" = "{true | false}",
"aws.s3.enable_path_style_access" = "{true | false}",
"aws.s3.endpoint" = "<s3_endpoint>",
"aws.s3.access_key" = "<iam_user_access_key>",
"aws.s3.secret_key" = "<iam_user_secret_key>"

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

Parameter	Required	Description
aws.s3.enable_ssl	Yes	Specifies whether to enable SSL connection. Valid values: `true` and `false`. Default value: `true`.
aws.s3.enable_path_style_access	Yes	Specifies whether to enable path-style access. Valid values: `true` and `false`. Default value: `false`. For MinIO, you must set the value to `true`. Path-style URLs use the following format: `https://s3.<region_code>.amazonaws.com/<bucket_name>/<key_name>`. For example, if you create a bucket named `DOC-EXAMPLE-BUCKET1` in the US West (Oregon) Region, and you want to access the `alice.jpg` object in that bucket, you can use the following path-style URL: `https://s3.us-west-2.amazonaws.com/DOC-EXAMPLE-BUCKET1/alice.jpg`.
aws.s3.endpoint	Yes	The endpoint that is used to connect to your S3-compatible storage system instead of AWS S3.
aws.s3.access_key	Yes	The access key of your IAM user.
aws.s3.secret_key	Yes	The secret key of your IAM user.

MetadataUpdateParams

A set of parameters about how StarRocks updates the cached metadata of Delta Lake. This parameter set is optional.

StarRocks implements the automatic asynchronous update policy by default.

In most cases, you can ignore MetadataUpdateParams and do not need to tune the policy parameters in it, because the default values of these parameters already provide you with an out-of-the-box performance.

However, if the frequency of data updates in Delta Lake is high, you can tune these parameters to further optimize the performance of automatic asynchronous updates.

NOTE

In most cases, if your Delta Lake data is updated at a granularity of 1 hour or less, the data update frequency is considered high.

Parameter	Required	Description
enable_metastore_cache	No	Specifies whether StarRocks caches the metadata of Delta Lake tables. Valid values: `true` and `false`. Default value: `true`. The value `true` enables the cache, and the value `false` disables the cache.
enable_remote_file_cache	No	Specifies whether StarRocks caches the metadata of the underlying data files of Delta Lake tables or partitions. Valid values: `true` and `false`. Default value: `true`. The value `true` enables the cache, and the value `false` disables the cache.
metastore_cache_refresh_interval_sec	No	The time interval at which StarRocks asynchronously updates the metadata of Delta Lake tables or partitions cached in itself. Unit: seconds. Default value: `7200`, which is 2 hours.
remote_file_cache_refresh_interval_sec	No	The time interval at which StarRocks asynchronously updates the metadata of the underlying data files of Delta Lake tables or partitions cached in itself. Unit: seconds. Default value: `60`.
metastore_cache_ttl_sec	No	The time interval at which StarRocks automatically discards the metadata of Delta Lake tables or partitions cached in itself. Unit: seconds. Default value: `86400`, which is 24 hours.
remote_file_cache_ttl_sec	No	The time interval at which StarRocks automatically discards the metadata of the underlying data files of Delta Lake tables or partitions cached in itself. Unit: seconds. Default value: `129600`, which is 36 hours.

Examples

The following examples create a Delta Lake catalog named deltalake_catalog_hms or deltalake_catalog_glue, depending on the type of metastore you use, to query data from your Delta Lake cluster.

HDFS

If you use HDFS as storage, run a command like below:

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

AWS S3

If you choose instance profile-based credential

If you use Hive metastore in your Delta Lake cluster, run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_hms
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.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 Delta Lake cluster, run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_glue
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.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 Delta Lake cluster, run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_hms
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.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 Delta Lake cluster, run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_glue
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.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 Delta Lake cluster, run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_hms
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.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 Delta Lake cluster, run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_glue
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.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"
);

S3-compatible storage system

Use MinIO as an example. Run a command like below:

CREATE EXTERNAL CATALOG deltalake_catalog_hms
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.type" = "hive",
    "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083",
    "aws.s3.enable_ssl" = "true",
    "aws.s3.enable_path_style_access" = "true",
    "aws.s3.endpoint" = "<s3_endpoint>",
    "aws.s3.access_key" = "<iam_user_access_key>",
    "aws.s3.secret_key" = "<iam_user_secret_key>"
);

View the schema of a Delta Lake table

You can use one of the following syntaxes to view the schema of a Delta Lake 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 a Delta Lake table

Use the following syntax to view the databases in your Delta Lake cluster:
```
SHOW DATABASES FROM <catalog_name>
```
Use the following syntax to connect to your target Delta Lake database:
```
USE <catalog_name>.<database_name>
```
Use the following syntax to query the Delta Lake table:
```
SELECT count(*) FROM <table_name> LIMIT 10
```

Load data from Delta Lake

Suppose you have an OLAP table named olap_tbl, you can transform and load data like below:

INSERT INTO default_catalog.olap_db.olap_tbl SELECT * FROM deltalake_table

Delta Lake catalog

Usage notes​

Integration preparations​

AWS IAM​

HDFS​

Kerberos authentication​

Create a Delta Lake catalog​

Syntax​

Parameters​

catalog_name​

comment​

type​

MetastoreParams​

Hive metastore​

AWS Glue​

StorageCredentialParams​

AWS S3​

S3-compatible storage system​

MetadataUpdateParams​

Examples​

HDFS​

AWS S3​

If you choose instance profile-based credential​

If you choose assumed role-based credential​

If you choose IAM user-based credential​

S3-compatible storage system​

View the schema of a Delta Lake table​

Query a Delta Lake table​

Load data from Delta Lake​

What did you think of this doc?

Usage notes

Integration preparations

AWS IAM

HDFS

Kerberos authentication

Create a Delta Lake catalog

Syntax

Parameters

catalog_name

comment

type

MetastoreParams

Hive metastore

AWS Glue

StorageCredentialParams

AWS S3

S3-compatible storage system

MetadataUpdateParams

Examples

HDFS

AWS S3

If you choose instance profile-based credential

If you choose assumed role-based credential

If you choose IAM user-based credential

S3-compatible storage system

View the schema of a Delta Lake table

Query a Delta Lake table

Load data from Delta Lake