hive中创建关联hbase表的几种方案

快来打我* 2022-05-26 13:48 307阅读 0赞

【运行环境】

hive-1.2.1 hbase-1.1.2

【需求背景】  
有时候我们需要把已存在Hbase中的用户画像数据导到hive里面查询，也就是通过hive就能查到hbase里的数据。但是我又不想使用sqoop或者DataX等工具倒来倒去。这时候可以在hive中创建关联表的方式来查询hbase中的数据。  
  
【创建关联表的几种方案】

前提是：hbase中已经存在了一张表。

可选的方案：既可以在hive中关联此表的所有列簇，也可以仅关联一个列簇，也可以关联单一列蔟下的单一列，还可以关联单一列簇下的多个列。

假设我在hbase中的 users 名称空间下面有一个表 china\_mainland，此表的视图如下：

hbase(main):001:0> desc "users:china\_mainland"  
Table users:china\_mainland is ENABLED  
users:china\_mainland, \{TABLE\_ATTRIBUTES => \{METADATA => \{'OWNER' => 'hbase'\}\}

COLUMN FAMILIES DESCRIPTION

\{NAME => 'act', BLOOMFILTER => 'ROW', VERSIONS => '3', IN\_MEMORY => 'false', KEEP\_DELETED\_CELLS => 'FALSE', DATA\_BLOCK\_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'SNAPPY', MIN\_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION\_SCOPE => '0'\}

\{NAME => 'basic', BLOOMFILTER => 'ROW', VERSIONS => '3', IN\_MEMORY => 'false', KEEP\_DELETED\_CELLS => 'FALSE', DATA\_BLOCK\_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'SNAPPY', MIN\_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION\_SCOPE => '0'\}

\{NAME => 'docs', BLOOMFILTER => 'ROW', VERSIONS => '3', IN\_MEMORY => 'false', KEEP\_DELETED\_CELLS => 'FALSE', DATA\_BLOCK\_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'SNAPPY', MIN\_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION\_SCOPE => '0'\}

\{NAME => 'pref', BLOOMFILTER => 'ROW', VERSIONS => '3', IN\_MEMORY => 'false', KEEP\_DELETED\_CELLS => 'FALSE', DATA\_BLOCK\_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'SNAPPY', MIN\_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION\_SCOPE => '0'\}

\{NAME => 'rc', BLOOMFILTER => 'ROW', VERSIONS => '3', IN\_MEMORY => 'false', KEEP\_DELETED\_CELLS => 'FALSE', DATA\_BLOCK\_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'SNAPPY', MIN\_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION\_SCOPE => '0'\}

5 row(s) in 0.2430 seconds

可以看到有5个列簇。我的列簇个数太多了，应该控制在3个列簇以内的，这个问题今后再说吧，计划是按照每个列簇分别拆分成不同的表吧，不然读写性能会随着数据量的增长而下降得很厉害。

下面演示如何在hive中创建外部表，注意：不能使用load data加载数据到这个hive的外部表，因为外部表是使用HBaseStorageHandler创建的。但是内部表就可以load data。

【方案一】创建一个hive外表，使其与hbase中的china\_mainland表的所有列簇映射(包括每个列簇下的所有列)

注意这里的关键步骤是在建表的时候，在WITH SERDEPROPERTIES指定关联到hbase表的哪个列簇或列！

hive> CREATE EXTERNAL TABLE china\_mainland(

> **rowkey**string,  
> act **map**<STRING,FLOAT>,  
> basic **map**<STRING,FLOAT>,  
> docs **map**<STRING,FLOAT>,  
> pref **map**<STRING,FLOAT>,  
> rc **map**<STRING,FLOAT>  
> ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "**:key,act:,basic:,docs:,pref:,rc:**")  
> TBLPROPERTIES ("hbase.table.name" = "**users:china\_mainland**")  
> ;

【方案二】与单一列簇下的单个列映射

hive表china\_mainland\_acturl中的2个字段rowkey、act\_url分别映射到Hbase表users:china\_mainland中的行健和“act列簇下的一个url列”

hive> CREATE EXTERNAL TABLE china\_mainland\_acturl(  
> **rowkey** string,  
> **act\_url** STRING  
> ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
> WITH SERDEPROPERTIES (**"hbase.columns.mapping" = ":key,act:url"**)  
> TBLPROPERTIES ("hbase.table.name" = "**users:china\_mainland**")  
> ;

【方案三】与单一列簇下的多个列映射

hive表china\_mainland\_kylin\_test中的3个字段pp\_professionact、pp\_salary、pp\_gender，分别映射到Hbase表users:china\_mainland中的列簇act下的3个列pp\_profession、pp\_salary、pp\_gender

hive> CREATE EXTERNAL TABLE china\_mainland\_kylin\_test(  
> rowkey string,  
> **pp\_profession**string,  
> pp\_salary double,  
> pp\_gender int)  
> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
> WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key,**act:pp\_profession**,act:pp\_salary,act:pp\_gender")  
 > TBLPROPERTIES ("hbase.table.name" = "**users:china\_mainland**");

【方案四】

关联到hbase表的单一列簇下的所有列

hive> CREATE EXTERNAL TABLE china\_mainland\_pref(  
> rowkey STRING,  
> pref map<STRING, STRING>  
> )  
> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
> WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,pref:")  
> TBLPROPERTIES ("hbase.table.name" = "users:china\_mainland")  
> ;  
OK  
Time taken: 0.164 seconds  
hive> DESC FORMATTED china\_mainland\_pref;  
OK  
\# col\_name data\_type comment  
rowkey string from deserializer  
pref map<string,string> from deserializer  
  
\# Detailed Table Information  
Database: lmy\_test   
Owner: hive   
CreateTime: Thu May 03 15:12:32 CST 2018  
LastAccessTime: UNKNOWN   
Protect Mode: None   
Retention: 0   
Location: hdfs://ks-hdfs/apps/hive/warehouse/tony\_test.db/china\_mainland\_pref  
Table Type: EXTERNAL\_TABLE   
Table Parameters:  
COLUMN\_STATS\_ACCURATE\{\\"BASIC\_STATS\\":\\"true\\"\}  
EXTERNAL TRUE  
hbase.table.name users:china\_mainland  
numFiles 0  
numRows 0  
rawDataSize 0  
storage\_handler org.apache.hadoop.hive.hbase.HBaseStorageHandler  
totalSize 0  
transient\_lastDdlTime1525331552  
  
\# Storage Information  
SerDe Library: org.apache.hadoop.hive.hbase.HBaseSerDe  
InputFormat: null   
OutputFormat: null   
Compressed: No   
Num Buckets: \-1   
Bucket Columns: \[\]   
Sort Columns: \[\]   
Storage Desc Params:  
hbase.columns.mapping:key,pref:  
serialization.format1  
Time taken: 0.165 seconds, Fetched: 36 row(s)

建好china\_mainland\_pref以后，马上就能用了——

\# 查看前几条  
 hive> SELECT \* FROM china\_mainland\_pref LIMIT 5;  
OK  
P\_0\{"preference\_game":"2","preference\_shopping":"29"\}  
P\_00\{"preference\_news":"2","preference\_shopping":"3","preference\_travel":"2"\}  
P\_0000001876382F627351AA1353507E7E\{"preference\_news":"1","preference\_science":"9","preference\_shopping":"2","profession\_communication":"1","train\_collegeexam":"8","train\_postgraduexam":"1"\}  
P\_00000050B563527DE611805C3513BFCF\{"preference\_news":"2","preference\_shopping":"68","preference\_sns":"2","title\_doc2vec\_vector":"\[-0.029652609855638477, 0.16829780398795727, 0.0634797563895506, 0.09985980261821016, -0.027206807371619682, 0.04609297546347725, 0.22684986310548136, 0.07509010726482222, -0.35608007793539426, 0.17766480221945294, 0.46286574267486735, 0.2597907394226127, -0.14725957574341025, 0.07262948642247423, 0.07125068438490716, 0.19818145833107004, -0.11506854374877365, 0.22868833573045896, -0.43365914508786096, -0.3630766762536705\]"\}  
P\_00000051992B3C6B48DF65CFCD00F570\{"preference\_automobile":"2","preference\_entertainment":"3","preference\_financial":"2","preference\_game":"2","preference\_house":"1","preference\_maternal":"2","preference\_medcine":"2","preference\_news":"1043","preference\_science":"1","preference\_shopping":"153","preference\_sns":"1","preference\_sport":"11","preference\_travel":"1","profession\_Agriculture":"9","profession\_appliance":"1","profession\_building":"4","profession\_businesstrade":"4","profession\_electronic":"1","profession\_food":"11","profession\_logistics":"14","profession\_metallurgy":"4","profession\_otherindustry":"2","shopping\_frequence":"0:7 11:0.286 14:0.143 15:0.143 19:0.143 20:0.143 21:0.143 25:0.571 29:0.143 30:0.286","title\_doc2vec\_vector":"\[-0.24758818000253893, 0.2338153449865942, -0.05848859099970545, -0.02417380306100351, 0.14515214525269712, 0.30990456699703955, -0.09866324401812501, -0.2331386034898917, -0.33209567698858355, 0.07699443458979872, 0.1471409695212827, 0.45493278257739733, 0.3085450975389932, -0.32623349923656875, -0.13339757411540565, -0.05206131438941852, 0.1003091645774786, 0.24435056196480162, -0.15905020331297162, -0.19182652834852418\]","train\_postgraduexam":"1"\}  
Time taken: 0.385 seconds, Fetched: 5 row(s)  
  
\# 查询此表的总记录数  
hive> select count(rowkey) from china\_mainland\_pref;  
Query ID = hive\_20180503151327\_21874623-dd01-4770-97be-65772cd20a6c  
Total jobs = 1  
Launching Job 1 out of 1  
Tez session was closed. Reopening...  
Session re-established.  
Status: Running (Executing on YARN cluster with App id application\_1523618137752\_0743)  
  
\--------------------------------------------------------------------------------  
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED  
\--------------------------------------------------------------------------------  
Map 1 .......... SUCCEEDED 102 102 0 0 0 0  
Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0  
\--------------------------------------------------------------------------------  
VERTICES: 02/02 \[==========================>>\] 100% ELAPSED TIME: 1072.51 s  
\--------------------------------------------------------------------------------  
OK  
851887471  
Time taken: 1080.993 seconds, Fetched: 1 row(s)