Tech Whims

生产环境下 hive 查询 hbase 数据技术架构的可能性

张晓龙 / 2023-05-19

线上hive 想查询 hbase 的数据,目前是将 hbase 数据同步到 hive 中,然后通过 hive 引擎查询。那 能不能通过 hive 直接查询 hbase 的数据呢首先答案是可以的

通过 Hive 外表的方式,利用 org.apache.hadoop.hive.hbase.HBaseStorageHandler 组件(维护Hive字段和HBase中的列),实际上是 Hive 提供对外的接口,然后通过实现这套接口来操作 hive 以外的数据存储。

先认识下 hive StorageHandlers:

StorageHandlers: a storage handler implementation is also available for Hypertable, and others are being developed for Cassandra, Azure Table, JDBC (MySQL and others), MongoDB, ElasticSearch, Phoenix HBase, VoltDB and Google Spreadsheets. A Kafka handler demo is available.

Hive storage handler support builds on existing extensibility features in both Hadoop and Hive:

These two distinctions (managed vs. external and native vs non-native) are orthogonal. Hence, there are four possibilities for base tables:

Hive feature : allows Hive QL statements to access HBase tables for both read (SELECT) and write (INSERT). It is even possible to combine access to HBase tables with native Hive tables via joins and unions.


hive> CREATE EXTERNAL TABLE hive_test (
   > rowkey string,
   > a string,
   > b string,
   > c string
   > ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH
   > SERDEPROPERTIES("hbase.columns.mapping" = ":key,cf:a,cf:b,cf:c")
   > TBLPROPERTIES("" = "some_existing_table","hbase.mapred.output.outputtable" = "some_existing_table");

需要特殊关注的就是hive 字段和 hbase column 的 map 处理。内部比较多的 cases 处理,详见相关资料[2]。


// external table
CREATE EXTERNAL TABLE tbl(id string, data map<string,string>)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,data:")
TBLPROPERTIES("" = "tbl");

// sql case 1 :  speed really fast!
select * from tbl", "select id from tbl", "select id, data
from tbl

// sql case 2 : speed incredibly slow!!!
select id from tbl where substr(id, 0, 5) = "12345"

这里的问题就是查询速度极慢!分析其原因 case 2 查询 scan 全表数据导致。

Hive HBase handler 不能很好的处理hbase rowkey 的起止位置,比如 substr(id, 0, 5) = “12345”,没有使用 start & stop row keys,导致查询慢。

explain hive sql,如果不存在filterExpr,则 query 会 scan 整表。

EXPLAIN SELECT * FROM tbl WHERE (id>='12345') AND (id<'12346')
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
            alias: tbl 
                expr: ((id>= '12345') and (id < '12346'))
                type: boolean
            Filter Operator

有一个方式能够利用 hbase 的 row-key prefixes,将上述 sql 的 substr(id, 0, 5) = “12345” 改为 id>=“12345” AND id<“12346” 即可,hbase 的 row key 会 SCAN (12345, 12346)。

hive on hbase 整合使用 Tips:

  1. Make sure you set the following properties to take advantage of batching to reduce the number of RPC calls (the number depends on the size of your columns)

    SET hbase.scan.cache=10000;

    SET hbase.client.scanner.cache=10000;

  2. Make sure you set the following properties to run a distributed job in your task trackers instead of running local job.

    SET mapred.job.tracker=[YOUR_JOB_TRACKER]:8021;

    SET hbase.zookeeper.quorum=[ZOOKEEPER_NODE_1],[ZOOKEEPER_NODE_2],[ZOOKEEPER_NODE_3];

  3. Reduce the amount of columns of your SELECT statement to the minimum. Try not to SELECT *

  4. Whenever you want to use start & stop row keys to prevent full table scans, always provide key>=x and key<y expressions (don’t use the BETWEEN operator)

  5. Always EXPLAIN SELECT your queries before executing them

综合来看:能不能通过 hive 直接查询 hbase 的数据应用到生产环境



1、不能用在线上 ETL pipeline(出于效率、SLA、数据管理考虑),可以用的离线数据分析场景

2、SQL 写法需要改造优化,避免全表 scan

3、只进行查询操作,不进行 insert。避免大数据量 insert 数据到外表hbase, 造成 hbase WAL 过载。


  1. Tuning Hive Queries That Uses Underlying HBase Table
  2. HBaseIntegration
  3. Using Hive to access an existing HBase table example