Apache Hive is data warehouse software built on Apache Hadoop. It uses SQL to enable users to read, write, and manage large-scale datasets. Its main features include distributed storage, fault tolerance, and large-scale data analysis, which simplify the query and analysis of data stored in distributed systems.
This topic describes how to access OBKV-HBase directly by using Hive without modifying code.
Prerequisites
Before you use Hive to access OBKV-HBase, make sure that the following conditions are met:
- The OBKV-HBase Java client is version 2.0.0-alpha4 or later, which supports native Hive capabilities.
- Ensure that the Hive version is 3.0.0 to 3.1.3.
Create a table
When using Hive to access an OBKV-HBase wide table, you can create the table in one of the following ways:
- Use the HBase-compatible interfaces to create the table.
- Create the table by using SQL. If you create the table by using SQL, follow the OBKV-HBase table creation rules. For more information, see Data operation examples.
Set OBKV-HBase client connection parameters
The method for setting client connection parameters differs between public cloud and on-premises deployments of OBKV-HBase. For an on-premises OceanBase AI Database cluster, configure the parameters as described in Direct connection mode configuration. For a public cloud OBKV service, configure them as described in Cloud mode configuration.
Regardless of the deployment mode, you can set all parameters in either of the following ways:
- In the
hive-site.xmlconfiguration file of the Hive client. - In your code, by using the
setmethod of HiveConfigurationbefore initializing theConnectionobject.
Direct connection mode configuration
Direct connection mode applies to on-premises OceanBase AI Database clusters. It connects directly to the OceanBase AI Database cluster by using Configurl, which is used to obtain the real address of the underlying OceanBase AI Database servers. The following describes how to configure connection parameters in Hive for direct connection mode:
Notice
The paramURL contains the XML special character &, which must be converted to &; otherwise, parsing will fail.
Method 1: Configure the following parameters in the Hive configuration file
hive-site.xml:<!-- Assume the current cluster is as follows: * ClusterName: obkvcluster TenantName: obkv DatabaseName: test UserName: root User password: SYS_USER_NAME: sysroot SYS user password: --> <configuration> <!-- Replace the HBase implementation class with that of OceanBase AI Database --> <property> <name>hbase.client.connection.impl</name> <value>com.alipay.oceanbase.hbase.util.OHConnectionImpl</value> </property> <!-- OceanBase AI Database connection configuration --> <property> <!-- The URL for obtaining the RSlist from the OBConfig server. For details, see Obtaining the config URL. --> <name>hbase.oceanbase.paramURL</name> <value>PARAM_URL</value> </property> <property> <!-- Format: userName@tenantName#clusterName --> <name>hbase.oceanbase.fullUserName</name> <value>root@obkv#obkvcluster</value> </property> <property> <!-- The password for userName to access OceanBase AI Database --> <name>hbase.oceanbase.password</name> <value></value> </property> <property> <!-- The username under the sys tenant. Only users in the sys tenant have the permission to access the routing table. --> <name>hbase.oceanbase.sysUserName</name> <value>sysroot</value> </property> <property> <!-- The password for the user under the sys tenant. --> <name>hbase.oceanbase.sysPassword</name> <value></value> </property> </configuration>Method 2: Configure parameters by using
HiveConfin your code./* * Assume the current cluster is as follows: * ClusterName: obkvcluster * TenantName: obkv * DatabaseName: test * UserName: root * User password: * SYS_USER_NAME: sysroot * SYS user password: */ HiveConf conf = new HiveConf(); conf.set("hbase.client.connection.impl", "com.alipay.oceanbase.hbase.util.OHConnectionImpl"); conf.set("hbase.oceanbase.fullUserName", "root@obkv#obkvcluster"); conf.set("hbase.oceanbase.password", ""); // Obtain the RSlist URL from the OBConfig server. For details, see Obtain the Config URL. conf.set("hbase.oceanbase.paramURL", ""); conf.set("hbase.oceanbase.sysUserName", "sysroot"); conf.set("hbase.oceanbase.sysPassword", "");
Obtain the Config URL:
You can obtain the Config URL by using one of the following methods:
Obtain the Config URL from OCP
If you use OCP, you can obtain the Config URL from OCP as follows:
a. Log on to the OceanBase AI Database console. b. In the left navigation pane, choose Clusters, scroll down to Cluster List, and click the name of the cluster that you want to access. c. On the cluster details page, find the ConfigURL parameter. You need to specify this parameter during client initialization.

Deploy a Config Server by using OBD and obtain
ObRootServiceInfoUrlIf you do not use OCP, you must deploy a Config Server by using OBD and obtain
ObRootServiceInfoUrl.
Cloud mode configuration
Cloud mode applies to OceanBase AI Database clusters deployed on public clouds. It connects to the OceanBase AI Database cluster through ODP. The following describes how to configure connection parameters for Hive in cloud mode:
Method 1: Configure the following parameters in the Hive configuration file
hive-site.xml:<!-- Assume the current cluster is as follows: DatabaseName: test UserName: root --> <configuration> <!-- Replace the HBase implementation class with that of OceanBase AI Database --> <property> <name>hbase.client.connection.impl</name> <value>com.alipay.oceanbase.hbase.util.OHConnectionImpl</value> </property> <!-- Configure the OceanBase AI Database connection --> <property> <name>hbase.oceanbase.fullUserName</name> <value>root</value> </property> <property> <name>hbase.oceanbase.password</name> <value></value> </property> <property> <!-- For details, see Obtain the ODP address. --> <name>hbase.oceanbase.odpAddr</name> <value></value> </property> <property> <name>hbase.oceanbase.odpPort</name> <value>3307</value> </property> <property> <name>hbase.oceanbase.odpMode</name> <value>true</value> </property> <property> <name>hbase.oceanbase.database</name> <value>test</value> </property> </configuration>Method 2: Configure parameters by using
HiveConfin your code:/* * Assume the current cluster is as follows: * DatabaseName: test * UserName: root */ HiveConf conf = new HiveConf(); conf.set("hbase.client.connection.impl", "com.alipay.oceanbase.hbase.util.OHConnectionImpl"); conf.set(HBASE_OCEANBASE_FULL_USER_NAME, "root"); // User password. conf.set(HBASE_OCEANBASE_PASSWORD, ""); // For details, see Obtain the ODP address. conf.set(HBASE_OCEANBASE_ODP_ADDR, ""); // The port for OBKV is 3307 (fixed). conf.setInt(HBASE_OCEANBASE_ODP_PORT, 3307); // Use ODP mode on the cloud (fixed). conf.setBoolean(HBASE_OCEANBASE_ODP_MODE, true); // The name of the database. conf.set(HBASE_OCEANBASE_DATABASE, "test");
Notice
paramURL contains the XML special character &, which must be converted to &; otherwise, parsing will fail.
Obtain the ODP address:
You can obtain the ODP address as follows: Go to the OB Cloud console, open the tenant workspace, and view the deployment diagram. 
Other parameters
In addition to the required connection parameters described above, you can set other parameters. For common parameters and their meanings, see Connect to a cluster by using the OBKV-HBase Java client.
Usage examples
Add dependencies
Add the following OBKV-HBase dependency to the pom.xml file of your project:
<dependency>
<groupId>com.oceanbase</groupId>
<artifactId>obkv-hbase-client</artifactId>
<version>2.2.1</version>
</dependency>
Add the Hive dependencies. When you add Hive-HbaseHandler, you must exclude the hbase-common and hadoop-common dependencies that come with Hive to avoid conflicts with the OBKV-HBase dependencies:
<!-- Hive JDBC Driver -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>${hive.version}</version>
<exclusions>
<exclusion>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-common</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- Hive HBase Handler -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-hbase-handler</artifactId>
<version>${hive.version}</version>
<exclusions>
<exclusion>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-common</artifactId>
</exclusion>
</exclusions>
</dependency>
Hive read/write examples
The following examples show how Hive reads data from and writes data to OBKV-HBase by creating and querying external tables and by inserting data.
Hive create external table
The syntax for creating an external table is as follows:
CREATE EXTERNAL TABLE IF NOT EXISTS {HiveTableName}(key string, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,{HbaseFamilyName}:{HbaseQualifierName}")
TBLPROPERTIES ("hbase.table.name" = "{HbaseTableName}", "hbase.mapred.output.outputtable" = "{HbaseTableName}");
The parameters are described as follows:
HiveTableNamespecifies the name of the external table in Hive.HbaseFamilyNamespecifies the column family name in HBase.HbaseQualifierNamespecifies the qualifier name in HBase.hbase.table.namespecifies the name of the corresponding HBase table.hbase.mapred.output.outputtablespecifies the name of the target HBase table for writing.
Read/write examples
Notice
Hive cannot directly read the underlying files of OBKV-HBase. To read data from and write data to OBKV-HBase, you can create or associate external tables.
Assume that the following OBKV-HBase table exists:
-- Create a table group.
CREATE TABLEGROUP `ecommerce_data` SHARDING = 'ADAPTIVE';
-- Create a user behavior table.
CREATE TABLE `ecommerce_data$user_behavior` (
`K` varbinary(1024) NOT NULL, -- User ID
`Q` varbinary(256) NOT NULL, -- Behavior type field name
`T` bigint(20) NOT NULL, -- Timestamp
`V` varbinary(1024) DEFAULT NULL, -- Behavioral data value
PRIMARY KEY (`K`, `Q`, `T`)
) TABLEGROUP = `ecommerce_data`;
The data in OBKV-HBase is as follows:
obclient> SELECT * FROM ecommerce_data$user_behavior;
+----------+-----------------+----------------+-------------+
| K | Q | T | V |
+----------+-----------------+----------------+-------------+
| user_001 | avg_order_value | -1640995200000 | 172.03 |
| user_001 | last_login | -1640995200000 | 2024-01-15 |
| user_001 | login_frequency | -1640995200000 | 25 |
| user_001 | total_amount | -1640995200000 | 2580.5 |
| user_001 | total_orders | -1640995200000 | 15 |
| user_001 | user_age | -1640995200000 | 28 |
| user_001 | user_city | -1640995200000 | Shenzhen |
| user_001 | user_name | -1640995200000 | Zhang San |
| user_001 | user_phone | -1640995200000 | 138****8001 |
| user_002 | avg_order_value | -1640995200000 | 150.0 |
| user_002 | last_login | -1640995200000 | 2024-01-14 |
| user_002 | login_frequency | -1640995200000 | 18 |
| user_002 | total_amount | -1640995200000 | 1200.0 |
| user_002 | total_orders | -1640995200000 | 8 |
| user_002 | user_age | -1640995200000 | 32 |
| user_002 | user_city | -1640995200000 | Shanghai |
| user_002 | user_name | -1640995200000 | Li Si |
| user_002 | user_phone | -1640995200000 | 138****8002 |
| user_003 | avg_order_value | -1640995200000 | 159.13 |
| user_003 | last_login | -1640995200000 | 2024-01-16 |
| user_003 | login_frequency | -1640995200000 | 35 |
| user_003 | total_amount | -1640995200000 | 3500.8 |
| user_003 | total_orders | -1640995200000 | 22 |
| user_003 | user_age | -1640995200000 | 25 |
| user_003 | user_city | -1640995200000 | Beijing |
| user_003 | user_name | -1640995200000 | Wang Wu |
| user_003 | user_phone | -1640995200000 | 138****8003 |
| user_004 | avg_order_value | -1640995200000 | 150.02 |
| user_004 | last_login | -1640995200000 | 2024-01-13 |
| user_004 | login_frequency | -1640995200000 | 22 |
| user_004 | total_amount | -1640995200000 | 1800.25 |
| user_004 | total_orders | -1640995200000 | 12 |
| user_004 | user_age | -1640995200000 | 30 |
| user_004 | user_city | -1640995200000 | Guangzhou |
| user_004 | user_name | -1640995200000 | Zhao Liu |
| user_004 | user_phone | -1640995200000 | 138****8004 |
| user_005 | avg_order_value | -1640995200000 | 122.26 |
| user_005 | last_login | -1640995200000 | 2024-01-16 |
| user_005 | login_frequency | -1640995200000 | 28 |
| user_005 | total_amount | -1640995200000 | 2200.75 |
| user_005 | total_orders | -1640995200000 | 18 |
| user_005 | user_age | -1640995200000 | 27 |
| user_005 | user_city | -1640995200000 | Shenzhen |
| user_005 | user_name | -1640995200000 | Qian Qi |
| user_005 | user_phone | -1640995200000 | 138****8005 |
| user_006 | avg_order_value | -1640995200000 | 168.0 |
| user_006 | last_login | -1640995200000 | 2024-01-15 |
| user_006 | login_frequency | -1640995200000 | 40 |
| user_006 | total_amount | -1640995200000 | 4200.0 |
| user_006 | total_orders | -1640995200000 | 25 |
| user_006 | user_age | -1640995200000 | 35 |
| user_006 | user_city | -1640995200000 | Hangzhou |
| user_006 | user_name | -1640995200000 | Sun Ba |
| user_006 | user_phone | -1640995200000 | 138****8006 |
| user_007 | avg_order_value | -1640995200000 | 175.03 |
| user_007 | last_login | -1640995200000 | 2024-01-17 |
| user_007 | login_frequency | -1640995200000 | 32 |
| user_007 | total_amount | -1640995200000 | 2800.5 |
| user_007 | total_orders | -1640995200000 | 16 |
| user_007 | user_age | -1640995200000 | 29 |
| user_007 | user_city | -1640995200000 | Chengdu |
| user_007 | user_name | -1640995200000 | Zhou Jiu |
| user_007 | user_phone | -1640995200000 | 138****8007 |
| user_008 | avg_order_value | -1640995200000 | 150.08 |
| user_008 | last_login | -1640995200000 | 2024-01-14 |
| user_008 | login_frequency | -1640995200000 | 15 |
| user_008 | total_amount | -1640995200000 | 1350.75 |
| user_008 | total_orders | -1640995200000 | 9 |
| user_008 | user_age | -1640995200000 | 26 |
| user_008 | user_city | -1640995200000 | Xi'an |
| user_008 | user_name | -1640995200000 | Wu Shi |
| user_008 | user_phone | -1640995200000 | 138****8008 |
+----------+-----------------+----------------+-------------+
72 rows in set
Create a Hive external table for the OBKV-HBase table:
hive> CREATE EXTERNAL TABLE IF NOT EXISTS user_behavior_analysis(
> user_id string, -- Maps to the K field in OBKV.
> user_name string, -- Maps to the V value where Q='user_name'.
> user_age string, -- Maps to the V value where Q='user_age'.
> user_city string, -- Maps to the V value where Q='user_city'.
> total_orders string, -- Maps to the V value where Q='total_orders'.
> total_amount string, -- Maps to the V value where Q='total_amount'.
> last_login string -- Maps to the V value where Q='last_login'.
> )
> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
> WITH SERDEPROPERTIES (
> "hbase.columns.mapping" = ":key,user_behavior:user_name,
> user_behavior:user_age,
> user_behavior:user_city,
> user_behavior:total_orders,
> user_behavior:total_amount,
> user_behavior:last_login"
> )
> TBLPROPERTIES (
> "hbase.table.name" = "ecommerce_data",
> "hbase.mapred.output.outputtable" = "ecommerce_data"
> );
Query the external table by using Hive SQL:
-- Query basic information of all users.
hive> SELECT user_id, user_name, user_age, user_city FROM user_behavior_analysis LIMIT 5;
OK
user_001 Zhang San 28 Shenzhen
user_002 Li Si 32 Shanghai
user_003 Wang Wu 25 Beijing
user_004 Zhao Liu 30 Guangzhou
user_005 Qian Qi 27 Shenzhen
Time taken: 5.127 seconds, Fetched: 5 row(s)
-- Query information of high-value users (users whose consumption amount is greater than 2,000).
hive> SELECT user_id, user_name, user_city, total_amount, total_orders FROM user_behavior_analysis WHERE CAST(total_amount AS DOUBLE) > 2000;
OK
user_001 Zhang San Shenzhen 2580.5 15
user_003 Wang Wu Beijing 3500.8 22
user_005 Qian Qi Shenzhen 2200.75 18
user_006 Sun Ba Hangzhou 4200.0 25
user_007 Zhou Jiu Chengdu 2800.5 16
Time taken: 5.194 seconds, Fetched: 5 row(s)
-- Query user consumption by city.
hive> SELECT user_city, COUNT(*) as user_count, ROUND(AVG(CAST(total_amount AS DOUBLE)), 2) as avg_spending FROM user_behavior_analysis GROUP BY user_city ORDER BY avg_spending DESC;
OK
Hangzhou 1 4200.0
Beijing 1 3500.8
Chengdu 1 2800.5
Shenzhen 2 2390.63
Guangzhou 1 1800.25
Xi'an 1 1350.75
Shanghai 1 1200.0
Time taken: 11.499 seconds, Fetched: 7 row(s)
-- Insert new user data.
hive> INSERT INTO user_behavior_analysis VALUES
> ('user_011', 'Zhao Xiaoming', '26', 'Nanjing', '3', '450.0', '2024-01-23');
OK
Time taken: 8.714 seconds
hive> SELECT * FROM user_behavior_analysis WHERE user_id = 'user_011';
OK
user_011 Zhao Xiaoming 26 Nanjing 3 450.0 2024-01-23
Time taken: 5.294 seconds, Fetched: 1 row(s)
-- Query the data from OBKV.
obclient> SELECT * FROM ecommerce_data$user_behavior WHERE K = 'user_011';
+----------+--------------+----------------+------------+
| K | Q | T | V |
+----------+--------------+----------------+------------+
| user_011 | last_login | -1750072416837 | 2024-01-23 |
| user_011 | total_amount | -1750072416837 | 450.0 |
| user_011 | total_orders | -1750072416837 | 3 |
| user_011 | user_age | -1750072416837 | 26 |
| user_011 | user_city | -1750072416837 | Nanjing |
| user_011 | user_name | -1750072416837 | Zhao Xiaoming |
+----------+--------------+----------------+------------+
6 rows in set
-- Query information about the user Wang Wu.
hive> SELECT * FROM user_behavior_analysis WHERE user_name = 'Wang Wu';
OK
user_003 Wang Wu 25 Beijing 22 3500.8 2024-01-16
Time taken: 5.259 seconds, Fetched: 1 row(s)
-- Update the information of the user Wang Wu.
hive> INSERT INTO user_behavior_analysis
> SELECT
> user_id,
> user_name,
> user_age,
> user_city,
> CASE
> WHEN user_id = 'user_003' THEN '25' -- Wang Wu has added 3 orders.
> ELSE total_orders
> END as total_orders,
> CASE
> WHEN user_id = 'user_003' THEN '4200.0' -- The total consumption increases from 3500.8 to 4200.0.
> ELSE total_amount
> END as total_amount,
> CASE
> WHEN user_id = 'user_003' THEN '2024-01-25' -- Update the last login time.
> ELSE last_login
> END as last_login
> FROM user_behavior_analysis;
hive> SELECT * FROM user_behavior_analysis WHERE user_name = 'Wang Wu';
OK
user_003 Wang Wu 25 Beijing 25 4200.0 2024-01-25
Time taken: 5.172 seconds, Fetched: 1 row(s)