Use an Iceberg data source in ApsaraDB for SelectDB-ApsaraDB for SelectDB(SelectDB)-阿里云帮助中心

Connect ApsaraDB for SelectDB to an Apache Iceberg data source by creating an external catalog, then run federated queries directly against Iceberg tables without moving data.

Limitations

SelectDB supports read-only access to external catalogs. Write operations are not supported.
Only Iceberg V1 and V2 tables are supported.
For Iceberg V2 tables, only position deletes are supported. Equality deletes are not supported.

Prerequisites

Before you begin, ensure that you have:

All nodes in the Iceberg cluster in the same virtual private cloud (VPC) as the SelectDB instance, or connected via cross-VPC networking. If connectivity is not established, see What do I do if a connection fails to be established between an ApsaraDB for SelectDB instance and a data source?
The IP addresses of all Iceberg cluster nodes added to the IP address whitelist of the SelectDB instance. See Configure an IP address whitelist
If the Iceberg cluster supports an IP address whitelist, the VPC IP addresses of the SelectDB instance added to it. To look up these addresses, see How do I view the IP addresses in the VPC to which my ApsaraDB SelectDB instance belongs?. To get the public IP address, run ping against the public endpoint of the SelectDB instance
Basic knowledge of catalogs in SelectDB. See Data lakehouse

Step 1: Connect to SelectDB

Connect to your SelectDB instance using a MySQL client. See Connect to an ApsaraDB for SelectDB instance by using a MySQL client.

Step 2: Create an Iceberg catalog

SelectDB integrates external data sources through external catalogs. The catalog type to create depends on how SelectDB accesses Iceberg metadata: via the Hive API or the Iceberg API.

The following sections use iceberg_catalog as the catalog name. Change it to match your environment.

Option A: Access metadata via the Hive API

Use this option when your Iceberg data source is backed by Hive metastore and you want SelectDB to access its metadata via the Hive API. The catalog type is hms, and the creation procedure is the same as for a Hive catalog. For full details, see Hive data source.

CREATE CATALOG iceberg_catalog PROPERTIES (
    'type'='hms',
    'hive.metastore.uris' = 'thrift://172.21.0.1:7004',
    'hadoop.username' = 'hive',
    'dfs.nameservices'='your-nameservice',
    'dfs.ha.namenodes.your-nameservice'='nn1,nn2',
    'dfs.namenode.rpc-address.your-nameservice.nn1'='172.21.0.2:4007',
    'dfs.namenode.rpc-address.your-nameservice.nn2'='172.21.0.3:4007',
    'dfs.client.failover.proxy.provider.your-nameservice'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

Option B: Access metadata via the Iceberg API

Use this option when you want SelectDB to access Iceberg metadata natively. The supported metadata services are: Hadoop Distributed File System (HDFS), Hive metastore, REST, and Data Lake Formation (DLF).

Important

Parameters related to the distributed file system in the Iceberg catalog map directly to parameters in the hdfs-site.xml file of the Iceberg cluster. Their values must match exactly.

Hadoop catalog

Use a Hadoop catalog when Iceberg data is stored in HDFS and managed by the Hadoop FileSystem catalog.

For a non-high availability (HA) Hadoop cluster:

CREATE CATALOG iceberg_hadoop PROPERTIES (
    'type'='iceberg',
    'iceberg.catalog.type' = 'hadoop',
    'warehouse' = 'hdfs://your-host:8020/dir/key'
);

For an HA Hadoop cluster:

CREATE CATALOG iceberg_hadoop_ha PROPERTIES (
    'type'='iceberg',
    'iceberg.catalog.type' = 'hadoop',
    'warehouse' = 'hdfs://your-nameservice/dir/key',
    'dfs.nameservices'='your-nameservice',
    'dfs.ha.namenodes.your-nameservice'='nn1,nn2',
    'dfs.namenode.rpc-address.your-nameservice.nn1'='172.21.0.2:4007',
    'dfs.namenode.rpc-address.your-nameservice.nn2'='172.21.0.3:4007',
    'dfs.client.failover.proxy.provider.your-nameservice'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

If your data is stored in an Object Storage Service (OSS) bucket, add the following parameters to PROPERTIES:

"oss.access_key" = "ak"
"oss.secret_key" = "sk"
"oss.endpoint" = "oss-cn-beijing-internal.aliyuncs.com"
"oss.region" = "oss-cn-beijing"

Parameters

OSS-related parameters are required only when data is stored in an OSS bucket.

Parameter	Required	Description
`type`	Yes	The catalog type. Set to `iceberg`.
`warehouse`	Yes	The HDFS path of the data warehouse.
`iceberg.catalog.type`	Yes	The Iceberg catalog type. Set to `hadoop`.
`dfs.nameservices`	No	The names of the nameservices.
`dfs.ha.namenodes.[nameservice ID]`	No	The IDs of the NameNodes.
`dfs.namenode.rpc-address.[nameservice ID].[name node ID]`	No	The Remote Procedure Call (RPC) addresses of the NameNodes. Specify one address per NameNode.
`dfs.client.failover.proxy.provider.[nameservice ID]`	No	The Java class used to connect to the active NameNode. Default: `org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider`.
`oss.region`	No	The region ID for OSS access.
`oss.endpoint`	No	The endpoint for OSS access. See Regions and endpoints.
`oss.access_key`	No	The AccessKey ID for OSS access.
`oss.secret_key`	No	The AccessKey secret for OSS access.

Hive metastore catalog

Use a Hive metastore catalog when Iceberg metadata is managed by Hive metastore and you want to use the Iceberg API (rather than the Hive API) to access it.

CREATE CATALOG iceberg_catalog PROPERTIES (
    'type'='iceberg',
    'iceberg.catalog.type'='hms',
    'hive.metastore.uris' = 'thrift://172.21.0.1:7004',
    'hadoop.username' = 'hive',
    'dfs.nameservices'='your-nameservice',
    'dfs.ha.namenodes.your-nameservice'='nn1,nn2',
    'dfs.namenode.rpc-address.your-nameservice.nn1'='172.21.0.2:4007',
    'dfs.namenode.rpc-address.your-nameservice.nn2'='172.21.0.3:4007',
    'dfs.client.failover.proxy.provider.your-nameservice'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

If your data is stored in an OSS bucket, add the following parameters to PROPERTIES:

"oss.access_key" = "ak"
"oss.secret_key" = "sk"
"oss.endpoint" = "oss-cn-beijing-internal.aliyuncs.com"
"oss.region" = "oss-cn-beijing"

Parameters

OSS-related parameters are required only when data is stored in an OSS bucket.

Parameter	Required	Description
`type`	Yes	The catalog type. Set to `iceberg`.
`iceberg.catalog.type`	Yes	The Iceberg catalog type. Set to `hms`.
`hive.metastore.uris`	Yes	The Uniform Resource Identifier (URI) of the Hive metastore.
`hadoop.username`	No	The username for HDFS login.
`dfs.nameservices`	No	The names of the nameservices.
`dfs.ha.namenodes.[nameservice ID]`	No	The IDs of the NameNodes.
`dfs.namenode.rpc-address.[nameservice ID].[name node ID]`	No	The RPC addresses of the NameNodes. Specify one address per NameNode.
`dfs.client.failover.proxy.provider.[nameservice ID]`	No	The Java class used to connect to the active NameNode. Default: `org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider`.
`oss.region`	No	The region ID for OSS access.
`oss.endpoint`	No	The endpoint for OSS access. See Regions and endpoints.
`oss.access_key`	No	The AccessKey ID for OSS access.
`oss.secret_key`	No	The AccessKey secret for OSS access.

REST catalog

Use a REST catalog when your Iceberg metadata service exposes a REST API. Deploy a REST service that implements the Iceberg REST API, then create the catalog:

CREATE CATALOG iceberg PROPERTIES (
    'type'='iceberg',
    'iceberg.catalog.type'='rest',
    'uri' = 'http://172.21.0.1:8181'
);

If data is stored in HDFS with HA enabled, include the HDFS HA parameters:

CREATE CATALOG iceberg PROPERTIES (
    'type'='iceberg',
    'iceberg.catalog.type'='rest',
    'uri' = 'http://172.21.0.1:8181',
    'dfs.nameservices'='your-nameservice',
    'dfs.ha.namenodes.your-nameservice'='nn1,nn2',
    'dfs.namenode.rpc-address.your-nameservice.nn1'='172.21.0.1:8020',
    'dfs.namenode.rpc-address.your-nameservice.nn2'='172.21.0.2:8020',
    'dfs.client.failover.proxy.provider.your-nameservice'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

Parameters

Parameter	Required	Description
`type`	Yes	The catalog type. Set to `iceberg`.
`iceberg.catalog.type`	Yes	The Iceberg catalog type. Set to `rest`.
`uri`	Yes	The URI of the REST service.
`dfs.nameservices`	No	The names of the nameservices.
`dfs.ha.namenodes.[nameservice ID]`	No	The IDs of the NameNodes.
`dfs.namenode.rpc-address.[nameservice ID].[name node ID]`	No	The RPC addresses of the NameNodes. Values must match those in `hdfs-site.xml`.
`dfs.client.failover.proxy.provider.[nameservice ID]`	No	The Java class used to connect to the active NameNode. Default: `org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider`.

Step 3: Verify the catalog

Run SHOW CATALOGS to confirm the catalog was created:

SHOW CATALOGS;

The output lists all catalogs, including the one you just created:

+--------------+-----------------+----------+-----------+-------------------------+---------------------+------------------------+
| CatalogId    | CatalogName     | Type     | IsCurrent | CreateTime              | LastUpdateTime      | Comment                |
+--------------+-----------------+----------+-----------+-------------------------+---------------------+------------------------+
| 436009309195 | iceberg_catalog | jdbc      |           | 2024-08-06 17:09:08.058 | 2024-07-19 18:04:37 |                        |
|            0 | internal        | internal | yes       | UNRECORDED              | NULL                | Doris internal catalog |
+--------------+-----------------+----------+-----------+-------------------------+---------------------+------------------------+

Step 4: Query Iceberg data

Query from the external catalog

After connecting to SelectDB, the internal catalog is used by default. Switch to the Iceberg catalog to query its data:

SWITCH iceberg_catalog;

Once switched, interact with the Iceberg catalog the same way as the internal catalog:

-- List databases
SHOW DATABASES;

-- Switch to a database
USE test_db;

-- List tables
SHOW TABLES;

-- Query the latest snapshot
SELECT * FROM test_t;

Query from the internal catalog

To query Iceberg data without switching catalogs, use the fully qualified three-part name:

-- Query the latest snapshot
SELECT * FROM iceberg_catalog.test_db.test_t;

Query historical snapshots (time travel)

Every write operation on an Iceberg table creates a new snapshot. By default, SelectDB reads from the latest snapshot. To query an earlier state, use FOR TIME AS OF or FOR VERSION AS OF.

To discover available snapshot IDs and timestamps, you can optionally use the ICEBERG_META function:

select * from iceberg_meta("table" = "iceberg_catalog.test_db.test_t", "query_type" = "snapshots");

Then query by timestamp or snapshot ID:

-- Query as of a specific point in time
SELECT * FROM test_t FOR TIME AS OF "2022-10-07 17:20:37";

-- Query as of a specific snapshot ID
SELECT * FROM test_t FOR VERSION AS OF 868895038****72;

The same syntax works with fully qualified names:

SELECT * FROM iceberg_catalog.test_db.test_t FOR TIME AS OF "2022-10-07 17:20:37";
SELECT * FROM iceberg_catalog.test_db.test_t FOR VERSION AS OF 868895038****72;

For the full ICEBERG_META function reference, see ICEBERG_META.

Migrate data to SelectDB

After the catalog is set up, use INSERT INTO statements to load Iceberg data into SelectDB internal tables. See Import data by using INSERT INTO statements.

Data type mappings

Iceberg-to-SelectDB column type mappings follow the same rules as Hive-to-SelectDB mappings. See Hive data source.