External Catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "hive",
    "metastore-uri" = "<hms_thrift_uri>"
);

Example

CREATE EXTERNAL CATALOG hive_hms
PROPERTIES
(
    "type" = "hive",
    "metastore-uri" = "thrift://hms-host:9083"
);

DDL and DML

From AnalyticDB for MySQL 3.2.8 onward, Hive Catalog supports the following:

-- Create database
CREATE DATABASE IF NOT EXISTS <catalog_name>.<db_name>;

-- Create table (PARQUET only)
CREATE TABLE IF NOT EXISTS <catalog_name>.<db_name>.<table_name> (
    <col1> <data_type>,
    <col2> <data_type>
)
STORED AS PARQUET;

-- Insert
INSERT INTO <catalog_name>.<db_name>.<table_name> VALUES (...);

-- Bulk insert
INSERT INTO <catalog_name>.<db_name>.<table_name>
SELECT * FROM <source_catalog>.<source_db>.<source_table>;

-- CTAS
CREATE TABLE <catalog_name>.<db_name>.<new_table>
AS SELECT * FROM <source_catalog>.<source_db>.<source_table>;

-- Drop
DROP TABLE IF EXISTS <catalog_name>.<db_name>.<table_name>;
DROP DATABASE IF EXISTS <catalog_name>.<db_name>;

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "iceberg",
    "metastore-uri" = "thrift://<host>:<port>"
);

Property semantics are the same as Hive Catalog; just change type to iceberg.

Example

CREATE EXTERNAL CATALOG iceberg_hms
PROPERTIES
(
    "type" = "iceberg",
    "metastore-uri" = "thrift://hms-host:9083"
);

DDL and DML

From 3.2.8 onward, the supported DDL and DML are identical to Hive Catalog. Use STORED AS ICEBERG when creating a table:

CREATE TABLE IF NOT EXISTS <catalog_name>.<db_name>.<table_name> (
    <col1> <data_type>,
    <col2> <data_type>
)
STORED AS ICEBERG;

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "delta",
    "metastore-uri" = "thrift://<host>:<port>"
);

Property semantics are the same as Hive Catalog; just change type to delta.

Example

CREATE EXTERNAL CATALOG delta_hms
PROPERTIES
(
    "type" = "delta",
    "metastore-uri" = "thrift://hms-host:9083"
);

Supported metastores

FileSystem (no separate metastore)

Paimon writes its metadata directly into the warehouse directory. Best for lightweight setups.

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "filesystem",
    "paimon.catalog.warehouse" = "oss://<bucket>/<path>/",
    -- Optional tuning properties
    "paimon.enable_raw_delete_vector" = "true",
    "paimon.enable_metadata_cache" = "true",
    "paimon.enable_bucket_join" = "true",
    "paimon.meta_cache_expired_time" = "1800"
);

-- Example
CREATE EXTERNAL CATALOG paimon_oss
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "filesystem",
    "paimon.catalog.warehouse" = "oss://my-bucket/paimon/warehouse/"
);

DLF 2.5 (recommended for production)

Use Alibaba Cloud Data Lake Formation (DLF 2.5) as the unified metastore.

Prerequisites:

1. DLF 2.5 is enabled and a target Catalog has been created in the DLF console under Data Catalogs. Its name is what you fill into paimon.catalog.warehouse.

2. In the DLF console under Data Permissions, grant the AnalyticDB for MySQL service-linked role AliyunServiceRoleForAnalyticDBForMySQL permissions on the target Catalog: select the preset permission type Data Editor.

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "rest",
    "paimon.catalog.warehouse" = "<dlf_catalog_name>",
    "paimon.catalog.uri" = "<dlf_endpoint>",
    "paimon.catalog.token.provider" = "dlf",
    -- The following properties are required only outside the managed-credential scenario
    "paimon.catalog.dlf.access-key-id" = "<access_key_id>",
    "paimon.catalog.dlf.access-key-secret" = "<access_key_secret>"
);

Properties

Examples

With managed credentials:

CREATE EXTERNAL CATALOG paimon_catalog_dlf
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "rest",
    "paimon.catalog.warehouse" = "my_paimon_catalog",
    "paimon.catalog.uri" = "cn-hangzhou-vpc.dlf.aliyuncs.com",
    "paimon.catalog.token.provider" = "dlf"
);

With an explicit AccessKey pair:

CREATE EXTERNAL CATALOG paimon_catalog_dlf
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "rest",
    "paimon.catalog.warehouse" = "my_paimon_catalog",
    "paimon.catalog.uri" = "cn-hangzhou-vpc.dlf.aliyuncs.com",
    "paimon.catalog.token.provider" = "dlf",
    "paimon.catalog.dlf.access-key-id" = "<your_access_key_id>",
    "paimon.catalog.dlf.access-key-secret" = "<your_access_key_secret>"
);

HMS (existing Hadoop ecosystem)

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "hive",
    "paimon.catalog.metastore.thrift.uri.selection" = "SEQUENTIAL",
    "paimon.catalog.warehouse" = "oss://<bucket>/<path>/",
    "paimon.catalog.uri" = "thrift://<hms_host>:<port>"
);

Key properties

DDL and DML

From 3.2.8 onward, Paimon Catalog supports the operations below. Table properties such as primary-key, partition, and bucket go inside TBLPROPERTIES. Paimon supports several table types: Append Only, primary-key (MOR/COW/MOW), aggregation, and First Row.

CREATE TABLE IF NOT EXISTS <catalog_name>.<db_name>.<table_name> (
    order_key   BIGINT,
    order_status VARCHAR,
    total_price  DOUBLE,
    order_date   DATE
)
TBLPROPERTIES (
    'file.format'  = 'PARQUET',
    'partition'    = 'order_date',
    'bucket'       = '2',
    'bucket-key'   = 'order_key'
);

Supported data sources

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type"                = "mysql",
    "connection-url"      = "jdbc:mysql://<host>:<port>",
    "connection-user"     = "<username>",
    "connection-password" = "<password>"
    -- For StarRocks, also set:
    -- , "mysql.use-mariadb-driver" = "true"
);

Examples

-- Connect to RDS for MySQL
CREATE EXTERNAL CATALOG rds_mysql_catalog
PROPERTIES
(
    "type"                = "mysql",
    "connection-url"      = "jdbc:mysql://rm-bp1xxxxxxx.mysql.rds.aliyuncs.com:3306",
    "connection-user"     = "<your_username>",
    "connection-password" = "<your_password>"
);

-- Connect to another AnalyticDB for MySQL instance
CREATE EXTERNAL CATALOG adb_mysql_catalog
PROPERTIES
(
    "type"                = "mysql",
    "connection-url"      = "jdbc:mysql://am-bp1xxxxxxx.ads.aliyuncs.com:3306",
    "connection-user"     = "<your_username>",
    "connection-password" = "<your_password>"
);

-- Connect to StarRocks (FE port defaults to 9030; MariaDB driver is required)
CREATE EXTERNAL CATALOG sr_catalog
PROPERTIES
(
    "type"                     = "mysql",
    "connection-url"           = "jdbc:mysql://fe-host:9030",
    "connection-user"          = "<your_username>",
    "connection-password"      = "<your_password>",
    "mysql.use-mariadb-driver" = "true"
);

View and drop

-- View the creation statement of an External Catalog
SHOW CREATE CATALOG <catalog_name>;

-- Drop a catalog (the original data in the external source is untouched)
DROP CATALOG <catalog_name>;

Permission control

External Catalogs offer a separate catalog-level permission model (the Managed Catalog adb reuses the existing database/table/column three-level model):

-- Grant
GRANT <privilege> ON CATALOG <catalog_name> TO '<user_name>'@'%';

-- Revoke
REVOKE <privilege> ON CATALOG <catalog_name> FROM '<user_name>'@'%';

Import from an external source into AnalyticDB for MySQL

Use INSERT INTO ... SELECT to materialize data from an External Catalog into an internal table. The pattern works for every catalog type.

INSERT INTO <adb_db>.<adb_table>
SELECT * FROM <catalog_name>.<lake_db>.<lake_table>;

-- From Hive Catalog
INSERT INTO analytics_db.orders_adb
SELECT * FROM hive_hms.warehouse_db.orders;

-- From Iceberg Catalog
INSERT INTO analytics_db.events_adb
SELECT * FROM iceberg_hms.lakehouse.events;

-- From Delta Catalog
INSERT INTO analytics_db.transactions_adb
SELECT * FROM delta_hms.finance_db.transactions;

-- From Paimon Catalog
INSERT INTO analytics_db.lineitem_adb
SELECT * FROM paimon_oss.tpch.lineitem;

Hive Catalog query

-- List databases in the Hive Catalog
SHOW DATABASES FROM hive_hms;

-- Query a Hive table
SELECT
    order_id,
    customer_name,
    order_date,
    total_amount
FROM hive_hms.sales_db.orders
WHERE order_date >= '2024-01-01'
ORDER BY total_amount DESC
LIMIT 100;

Iceberg Catalog query

-- List databases in the Iceberg Catalog
SHOW DATABASES FROM iceberg_hms;

-- Query an Iceberg table
SELECT
    event_type,
    COUNT(*) AS event_count,
    AVG(duration_ms) AS avg_duration
FROM iceberg_hms.lakehouse.user_events
WHERE event_date >= '2024-01-01'
GROUP BY event_type
ORDER BY event_count DESC;

Delta Catalog query

-- List databases in the Delta Catalog
SHOW DATABASES FROM delta_hms;

-- Query a Delta table
SELECT
    product_category,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM delta_hms.finance_db.transactions
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY product_category
ORDER BY total_revenue DESC;

Paimon Catalog query

SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity)                                       AS sum_qty,
    SUM(l_extendedprice)                                  AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount))               AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity)                                       AS avg_qty,
    AVG(l_extendedprice)                                  AS avg_price,
    AVG(l_discount)                                       AS avg_disc,
    COUNT(*)                                              AS count_order
FROM   paimon_oss.tpch.lineitem
WHERE  l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP  BY l_returnflag, l_linestatus
ORDER  BY l_returnflag, l_linestatus;

MySQL Catalog query

-- List databases in the MySQL Catalog
SHOW DATABASES FROM rds_mysql_catalog;

-- Query an RDS MySQL table directly
SELECT
    user_id,
    username,
    email,
    created_at
FROM rds_mysql_catalog.user_db.users
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

Federated lakehouse query

Join an internal table with an external one in the same statement:

SELECT
    u.username,
    u.email,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM
    rds_mysql_catalog.user_db.users u
JOIN
    analytics_db.orders o ON u.user_id = o.user_id
GROUP BY u.username, u.email
ORDER BY total_spent DESC
LIMIT 50;