Create an external Paimon catalog in StarRocks to query Paimon data - E-MapReduce

StarRocks 3.1 and later support Paimon catalogs. A Paimon catalog is an external catalog that lets you query Paimon data directly from an EMR StarRocks cluster without moving or copying data.

Prerequisites

Before you begin, ensure that you have:

A cluster containing the Paimon service, such as a DataLake cluster or a custom cluster. See Create a cluster.
A cluster containing the StarRocks service, such as an online analytical processing (OLAP) cluster or a custom cluster, and you are logged on to the cluster. See Create a cluster and Getting started.

Limitations

Both clusters must be deployed in the same virtual private cloud (VPC) and zone.

Create a Paimon catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
  "key"="value",
  ...
);

Parameters

`catalog_name` (required)

The name of the Paimon catalog. Requirements:

Contains only letters, digits, and underscores (_), and starts with a letter.
1 to 64 characters in length.

`PROPERTIES` (required)

The properties of the Paimon catalog. StarRocks Paimon catalog properties map one-to-one to the native Paimon API catalog configuration.

Property	Required	Description
`type`	Yes	The data source type. Set to `paimon`.
`paimon.catalog.type`	Yes	The metadata storage type. Valid values: `hive` (Hive Metastore), `filesystem` (file system), `dlf` (Data Lake Formation).
`paimon.catalog.warehouse`	Yes	The warehouse path. Supports HDFS and OSS paths.
`hive.metastore.uris`	No	The Hive Metastore URI. Required when `paimon.catalog.type` is `hive`. Format: `thrift://<IP address>:<port>`. Default port: 9083.
`aliyun.oss.endpoint`	No	The OSS endpoint. Required when `paimon.catalog.warehouse` is an OSS path.
`dlf.catalog.id`	No	The Data Lake Formation (DLF) data catalog ID. Required only when `paimon.catalog.type` is `dlf`. If not set, the default DLF catalog is used.

Examples

The following examples show how to create a Paimon catalog for different catalog and storage combinations.

DLF catalog with OSS storage

CREATE EXTERNAL CATALOG paimon_catalog
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "dlf",
    "paimon.catalog.warehouse" = "oss://<yourBucketName>/<yourPath>/",
);

Hive Metastore catalog with OSS storage

CREATE EXTERNAL CATALOG paimon_catalog_hive
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "hive",
    "paimon.catalog.warehouse" = "oss://<yourBucketName>/<yourPath>/",
    "hive.metastore.uris" = "thrift://<hive_metastore_ip>:9083",
    "aliyun.oss.endpoint" = "<oss_endpoint>"
);

Filesystem catalog with HDFS storage

CREATE EXTERNAL CATALOG paimon_catalog_fs
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "filesystem",
    "paimon.catalog.warehouse" = "hdfs://<namenode_ip>:<port>/<path>/"
);

Query data in a Paimon table

Use the following workflow to explore and query Paimon data.

List the databases in your Paimon catalog:
```
SHOW DATABASES FROM <catalog_name>;
```
Switch to the Paimon catalog and select a database:
```
SET CATALOG <catalog_name>;
USE <database_name>;
```
Query a table:
```
SELECT * FROM <table_name> LIMIT 10;
```
Alternatively, use the fully qualified three-part name to query without switching catalogs:
```
SELECT * FROM <catalog_name>.<database_name>.<table_name>;
```

What's next

For more information about Paimon, see Overview.