ApsaraDB for SelectDB:Hive data source

Use cases

Supported configurations

Prerequisites

Before you begin, make sure that:

• All nodes in your Hive cluster are in the same virtual private cloud (VPC) as the SelectDB instance, or you have established cross-VPC connectivity. For help with connection failures, 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 Hive cluster nodes are added to the SelectDB instance's IP address whitelist. See Configure an IP address whitelist.

• If the Hive cluster enforces its own whitelist, the SelectDB instance's VPC IP addresses are also added to the Hive cluster's whitelist. To find the SelectDB instance's VPC IP address, see How do I view the IP addresses in the VPC to which my ApsaraDB SelectDB instance belongs?

• If the Hive cluster uses HDFS as storage, the following ports are open so that data can transfer between the Hive cluster and SelectDB:

  Parameter	Default port
  hive.metastore.uris	9083
  dfs.namenode.rpc-address	8020
  dfs.datanode.address	9866

• You are familiar with catalog concepts and operations. See Data lakehouse.

Sample environment

The walkthrough in this topic uses the following setup. Substitute your own values wherever these parameters appear.

Prepare the source data

1. Log on to the Hive cluster you want to query.

2. Create a database named test_db.

   CREATE DATABASE IF NOT EXISTS test_db;

3. Create a table named test_t.

   CREATE TABLE IF NOT EXISTS test_t (
       id   INT,
       name STRING,
       age  INT
   );

4. Insert sample data.

   INSERT INTO TABLE test_t VALUES
       (1, 'Alice',   25),
       (2, 'Bob',     30),
       (3, 'Charlie', 35),
       (4, 'David',   40),
       (5, 'Eve',     45);

Connect a Hive cluster to SelectDB

CREATE CATALOG hive_catalog PROPERTIES (
    'type'                = 'hms',
    'hive.metastore.uris' = 'thrift://<metastore-host>:9083'
);

CREATE CATALOG hive_catalog PROPERTIES (
    'type'                = 'hms',
    'hive.metastore.uris' = 'thrift://master-1-1.c-7fa25a1a****.cn-hangzhou.emr.aliyuncs.com:9083'
);

CREATE CATALOG <catalog_name> PROPERTIES (
    'type'                                                = 'hms',
    'hive.metastore.uris'                                 = 'thrift://<metastore-host>:9083',
    'hadoop.username'                                     = '<hadoop-username>',
    'dfs.nameservices'                                    = '<nameservice-id>',
    'dfs.ha.namenodes.<nameservice-id>'                   = '<nn1>,<nn2>',
    'dfs.namenode.rpc-address.<nameservice-id>.<nn1>'     = '<host1>:<port1>',
    'dfs.namenode.rpc-address.<nameservice-id>.<nn2>'     = '<host2>:<port2>',
    'dfs.client.failover.proxy.provider.<nameservice-id>' = '<dfs.client.failover.proxy.provider.your-nameservice>'
);

CREATE CATALOG hive_catalog PROPERTIES (
    "type"                = "hms",
    "hive.metastore.uris" = "thrift://172.0.0.1:9083",
    "oss.endpoint"        = "oss-cn-beijing.aliyuncs.com",
    "oss.access_key"      = "<your-access-key-id>",
    "oss.secret_key"      = "<your-access-key-secret>"
);

SHOW CATALOGS;

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

-- Style 1: Switch catalog, then use database and table
SWITCH hive_catalog;
USE test_db;
SELECT * FROM test_t;

-- Style 2: Use the fully qualified table name (no switch required)
SELECT * FROM hive_catalog.test_db.test_t;

SWITCH hive_catalog;
SHOW DATABASES;   -- list all databases in the Hive catalog
USE test_db;
SHOW TABLES;      -- list all tables in test_db

Migrate data from Hive to SelectDB

After the Hive catalog is connected, use INSERT INTO statements to migrate historical Hive data into SelectDB internal tables. See Import data by using INSERT INTO statements.

Column data type mappings

SelectDB automatically maps Hive column types to SelectDB types when you create a catalog. The mappings below apply to Hive, Iceberg, and Hudi metadata stored in a Hive metastore.

Complex types can be nested. For example:

• array<map<string, int>>

• map<string, array<int>>

• struct<col1: array<int>, col2: map<int, date>>

What's next

• Data lakehouse — Learn more about external catalogs and federated query concepts.

• Import data by using INSERT INTO statements — Move Hive data into SelectDB internal tables.