ApsaraDB for SelectDB:Hive data source

Prerequisites

• The IP addresses of all nodes in your Hive cluster are added to the IP address whitelist of an SelectDB instance. For more information, see Configure an IP address whitelist.

• If your Hive cluster is built on a Hadoop Distributed File System (HDFS), the following ports are enabled for the HDFS to transfer data between the Hive cluster and SelectDB:

  • Port specified by the hive.metastore.uris parameter. Default value: 9083.

  • Port specified by the dfs.namenode.rpc-address parameter. Default value: 8020.

  • Port specified by the dfs.datanode.address parameter. Default value: 9866.

• You have basic knowledge of catalogs and know the operations that you can perform on catalogs. For more information, see Data lakehouse.

Usage notes

• Hive 1, Hive 2, and Hive 3 are supported.

• Managed tables, external tables, and some Hive views are supported.

• Hive, Iceberg, and Hudi metadata stored in a Hive metastore can be identified.

• ApsaraDB for SelectDB can only read data in external catalogs.

Sample environment

In this example, the Linux operating system is used, and federated queries are performed in ApsaraDB for SelectDB to query the test_db.test_t table in a Hive cluster that is built on an HDFS. You can change the parameter settings in the example based on your operating system and environment. Sample environment parameters:

• Business scenario: non-high availability (HA)

• Hive cluster type: HDFS

• Source database: test_db

• Source table: test_t

Prepare the source data

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

2. Create a database named testdb.

   CREATE database if NOT EXISTS test_db;

3. Create a table named Hive_t.

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

4. Insert data into the table.

   --Insert data into the table.
   INSERT INTO TABLE test_t VALUES
   (1, 'Alice', 25),
   (2, 'Bob', 30),
   (3, 'Charlie', 35),
   (4, 'David', 40),
   (5, 'Eve', 45);

Procedure

Step 1: Connect to an ApsaraDB for SelectDB instance

Connect to an SelectDB instance. For more information, see Connect to an ApsaraDB for SelectDB instance by using a MySQL client.

Step 2: Create a Hive catalog

You can create an external catalog in SelectDB to integrate an external data source. The catalog parameters vary with business scenarios. Select an appropriate syntax and configure the required parameters based on your business scenario.

After you create a catalog, ApsaraDB for SelectDB automatically synchronizes the databases and tables in the catalog. ApsaraDB for SelectDB maps data types of columns in a catalog to ApsaraDB for SelectDB data types. For more information, see the "Column data type mappings" section of this topic.

CREATE CATALOG <catalog_name> PROPERTIES (
    'type'='<type>',
    'hive.metastore.uris' = '<hive.metastore.uris>',
    'hadoop.username' = '<hadoop.username>',
    'dfs.nameservices'='<hadoop.username>',
    'dfs.ha.namenodes.your-nameservice'='<dfs.ha.namenodes.your-nameservice>',
    'dfs.namenode.rpc-address.your-nameservice.nn1'='<dfs.namenode.rpc-address.your-nameservice.nn1>',
    'dfs.namenode.rpc-address.your-nameservice.nn2'='<dfs.namenode.rpc-address.your-nameservice.nn2>',
    'dfs.client.failover.proxy.provider.your-nameservice'='<dfs.client.failover.proxy.provider.your-nameservice>'
);

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

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" = "ak",
    "oss.secret_key" = "sk"
);

Step 3: View the catalog

You can execute the following statement to check whether the catalog is created:

SHOW CATALOGS; --Check whether the catalog is created.

+--------------+--------------+----------+-----------+-------------------------+---------------------+------------------------+
| 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 |
+--------------+--------------+----------+-----------+-------------------------+---------------------+------------------------+

Step 4: View data in Hive databases and tables

• View the Hive databases and tables in the external catalog that you created.

  Note

  After you connect to an ApsaraDB for SelectDB instance, the internal catalog is used by default.

  1. Switch to the external catalog that you want to manage.

     SWITCH hive_catalog;

  2. View the data.

     After you switch to the external catalog, you can access the data in the external catalog in the same way as you use the internal catalog. Examples:

     • To view the database list, execute the SHOW DATABASES; statement.

     • To switch to the specified database, execute the USE test_db; statement.

     • To view the table list, execute the SHOW TABLES; statement.

     • To view the data in the specified table, execute the SELECT * FROM test_t; statement.

• View the Hive databases and tables in the internal catalog.

  --View the data in the test_t table of the test_db database in the hive_catalog catalog. 
  SELECT * FROM hive_catalog.test_db.test_t;

Related operation: Migrate data

After the Hive data source is integrated, you can execute INSERT INTO statements to migrate historical data from the Hive data source to SelectDB. For more information, see Import data by using INSERT INTO statements.

Column data type mappings