External Catalog

语法

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

示例

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

DDL 与 DML 操作

自 AnalyticDB for MySQL 3.2.8 版本起，Hive Catalog 支持以下操作：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS <catalog_name>.<db_name>;

-- 创建表（仅支持 PARQUET 格式）
CREATE TABLE IF NOT EXISTS <catalog_name>.<db_name>.<table_name> (
    <col1> <data_type>,
    <col2> <data_type>
)
STORED AS PARQUET;

-- 写入数据
INSERT INTO <catalog_name>.<db_name>.<table_name> VALUES (...);

-- 批量写入
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 TABLE IF EXISTS <catalog_name>.<db_name>.<table_name>;
DROP DATABASE IF EXISTS <catalog_name>.<db_name>;

语法

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

参数说明同 Hive Catalog，将 type 改为 iceberg。

示例

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

DDL 与 DML 操作

自 AnalyticDB for MySQL 3.2.8 版本起，Iceberg Catalog 支持的 DDL/DML 操作与 Hive Catalog 一致。建表时使用 STORED AS ICEBERG 指定表格式：

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

语法

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

参数说明同 Hive Catalog，将 type 改为 delta。

示例

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

支持的元数据服务

FileSystem（无独立元数据服务）

Paimon 将元数据直接存储在数据仓库目录中，适合轻量级场景。

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "paimon",
    "paimon.catalog.metastore" = "filesystem",
    "paimon.catalog.warehouse" = "oss://<bucket>/<path>/",
    -- 可选优化参数
    "paimon.enable_raw_delete_vector" = "true",
    "paimon.enable_metadata_cache" = "true",
    "paimon.enable_bucket_join" = "true",
    "paimon.meta_cache_expired_time" = "1800"
);

-- 示例
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（推荐生产场景）

使用阿里云数据湖构建（DLF 2.5）作为统一元数据服务。

使用前提：

1. 已开通 DLF 2.5 服务，并在 DLF 控制台 数据目录 中创建对应 Catalog，后续 paimon.catalog.warehouse 参数填写该 Catalog 名称。

2. 在 DLF 控制台 数据权限 中，给 AnalyticDB for MySQL 服务关联角色 AliyunServiceRoleForAnalyticDBForMySQL 授予目标 Catalog 的权限：预置权限类型选择 Data Editor。

语法

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",
    -- 以下参数在非托管认证场景下需要手动指定
    "paimon.catalog.dlf.access-key-id" = "<access_key_id>",
    "paimon.catalog.dlf.access-key-secret" = "<access_key_secret>"
);

参数说明

示例

使用托管认证：

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

手动指定 AccessKey：

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（已有 Hadoop 生态场景）

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

关键参数说明

DDL 与 DML 操作

自 3.2.8 版本起，Paimon Catalog 支持以下操作。Paimon 表通过 TBLPROPERTIES 子句指定表属性（如 primary-key、partition、bucket 等），支持 Append Only、主键表（MOR/COW/MOW）、聚合表和 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'
);

支持的数据源

语法

CREATE EXTERNAL CATALOG <catalog_name>
PROPERTIES
(
    "type"                = "mysql",
    "connection-url"      = "jdbc:mysql://<host>:<port>",
    "connection-user"     = "<username>",
    "connection-password" = "<password>"
    -- 连接 StarRocks 时追加：
    -- , "mysql.use-mariadb-driver" = "true"
);

示例

-- 连接 RDS 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>"
);

-- 连接另一个 AnalyticDB MySQL 实例
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>"
);

-- 连接 StarRocks（FE 默认端口 9030，必须启用 MariaDB 驱动）
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"
);

查看与删除

-- 查看 External Catalog 的创建语句
SHOW CREATE CATALOG <catalog_name>;

-- 删除 External Catalog（不影响外部数据源原始数据）
DROP CATALOG <catalog_name>;

权限控制

External Catalog 提供独立的 Catalog 级权限控制：

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

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

从外部数据源导入到 AnalyticDB for MySQL 内表

使用 INSERT INTO ... SELECT 将 External Catalog 中的数据导入 AnalyticDB for MySQL 内表，适用于所有类型的 External Catalog。

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

-- 从 Hive Catalog 导入数据
INSERT INTO analytics_db.orders_adb
SELECT * FROM hive_hms.warehouse_db.orders;

-- 从 Iceberg Catalog 导入数据
INSERT INTO analytics_db.events_adb
SELECT * FROM iceberg_hms.lakehouse.events;

-- 从 Delta Catalog 导入数据
INSERT INTO analytics_db.transactions_adb
SELECT * FROM delta_hms.finance_db.transactions;

-- 从 Paimon Catalog 导入数据
INSERT INTO analytics_db.lineitem_adb
SELECT * FROM paimon_oss.tpch.lineitem;

Hive Catalog 查询示例

-- 查看 Hive Catalog 中的数据库
SHOW DATABASES FROM hive_hms;

-- 查询 Hive 表数据
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 查询示例

-- 查看 Iceberg Catalog 中的数据库
SHOW DATABASES FROM iceberg_hms;

-- 查询 Iceberg 表数据
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 查询示例

-- 查看 Delta Catalog 中的数据库
SHOW DATABASES FROM delta_hms;

-- 查询 Delta 表数据
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 查询示例

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 查询示例

-- 查看 MySQL Catalog 中的数据库
SHOW DATABASES FROM rds_mysql_catalog;

-- 直接查询 RDS MySQL 中的表
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;

湖仓联邦查询

在同一 SQL 中关联内表与外部 Catalog 表：

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;