Hive data source

Prerequisites

• Ensure that all nodes in your Hive cluster have network connectivity with the SelectDB instance.

  • All nodes in your Hive cluster and the SelectDB instance are in the same virtual private cloud (VPC). If they are not in the same VPC, you must first establish network connectivity. For more information, see What do I do if a connection fails to be established between an ApsaraDB for SelectDB instance and a data source?

  • Add the IP addresses of all nodes in the Hive cluster to the SelectDB whitelist. For more information, see Configure an IP address whitelist.

  • If the Hive cluster has a whitelist mechanism, add the IP CIDR blocks of the network where the SelectDB instance resides to the Hive cluster's whitelist.

    • To get the IP range of the VPC where your SelectDB instance resides, see How do I find the IP CIDR block of the VPC where my ApsaraDB for SelectDB instance is located?

    • To get the public IP address of your SelectDB instance, use the ping command on its public endpointSelectDB.

• If your Hive is built on HDFS, ensure that the following ports are open for data transfer between Hive and SelectDB.

  • The port specified in hive.metastore.uris. The default port is 9083.

  • The port specified in dfs.namenode.rpc-address. The default port is 8020.

  • The port specified in dfs.datanode.address. The default port is 9866.

• Understand what a catalog is and how to perform basic operations on it. For more information, see lakehouse.

Considerations

• Supports Hive versions 1, 2, and 3.

• Supports managed tables, external tables, and some Hive views.

• Supports discovery of Hive, Iceberg, and Hudi metadata stored in a Hive metastore.

• Currently, ApsaraDB for SelectDB supports only read operations on data in an external catalog.

Sample environment

This topic uses a Linux system as an example. In SelectDB, you run a federated query against test_db.test_t in a Hive cluster built on HDFS. Replace the example values with those that match your actual system and environment. The sample environment is as follows:

• Use case: non-HA

• Hive storage backend: HDFS

• Source database: test_db

• Source table: test_t

Prepare the source data

1. Log on to your Hive cluster.

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 data into the table.

   -- Insert data
   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 instance

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

Step 2: Integrate Hive

SelectDB integrates external data sources by creating an external catalog. Different use cases require different catalog properties. Choose the syntax and parameter values that match your environment.

After you create the catalog, ApsaraDB for SelectDB automatically syncs databases and tables from the data source. ApsaraDB for SelectDB maps columns between different data sources and table formats. If you are not familiar with the type mapping between Hive and SelectDB, see Column type mapping.

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 catalogs

Run the following statement to verify that the catalog is created successfully.

SHOW CATALOGS; -- Verify whether the catalog is created successfully.

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

• View Hive databases and tables from the external catalog.

  Note

  After you connect to a SelectDB instance, the default working directory is the internal catalog.

  1. Switch to the target external catalog.

     SWITCH hive_catalog;

  2. View data.

     After switching to the target catalog, you can access its data in the same way you access data in the internal catalog. For example:

     • List databases: SHOW DATABASES;

     • Switch to a database: USE test_db;

     • List tables in a database: SHOW TABLES;

     • Query table data: SELECT * FROM test_t;

• View Hive data from the internal catalog.

  -- View data from the test_t table in the test_db database under the hive_catalog.
  SELECT * FROM hive_catalog.test_db.test_t;

More operations: Migrate data

After you integrate the data source, use the INSERT INTO statement to migrate historical data from Hive to SelectDB. For more information, see INSERT INTO.

Column type mapping