Hive与HBase整合（实例）

 

实例1

1.先在Hbase中创建表（三列族）：

create 'ceshi7',
{NAME=>'TIME',VERSIONS=>1,BLOCKCACHE=>true,BLOOMFILTER=>'ROW',COMPRESSION=>'SNAPPY',
DATA_BLOCK_ENCODING => 'PREFIX_TREE', BLOCKSIZE => '65536'},
{SPLITS => ['1','2','3','4','5','6','7','8','9','a','b','c','d','e','f']},
{NAME=>'ORDERITEM',VERSIONS=>1,BLOCKCACHE=>true,BLOOMFILTER=>'ROW',COMPRESSION=>'SNAPPY',
DATA_BLOCK_ENCODING => 'PREFIX_TREE', BLOCKSIZE => '65536'},
{SPLITS => ['1','2','3','4','5','6','7','8','9','a','b','c','d','e','f']},
{NAME=>'ORDERTYPE',VERSIONS=>1,BLOCKCACHE=>true,BLOOMFILTER=>'ROW',COMPRESSION=>'SNAPPY',
DATA_BLOCK_ENCODING => 'PREFIX_TREE', BLOCKSIZE => '65536'},
{SPLITS => ['1','2','3','4','5','6','7','8','9','a','b','c','d','e','f']}

2.之后在Hive中创建外部表：

create external table lqioc_ioc_mid.ceshi7(
rowid string,
ordertypeno string,
ordertypename string,
ordertypecost string,
yearid string,
monthid string,
orderitemname string,
orderitemnum string)STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES("hbase.columns.mapping" 
=":key,ORDERTYPE:ORDERTYPENO,ORDERTYPE:ORDERTYPENAME,ORDERTYPE:ORDERTYPECOST,
TIME:YEARID,TIME:MONTHID,
ORDERITEM:ORDERITEMNAME,ORDERITEM:ORDERITEMNUM")
TBLPROPERTIES("hbase.table.name" = "ceshi7","hbase.mapred.output.outputtable" = "ceshi7");

3.报错信息：

Error: Error while processing statement: 
FAILED: Execution Error, return code 1 
from org.apache.hadoop.hive.ql.exec.DDLTask. 
MetaException(message:MetaException(message:
Cannot CREATE EXTERNAL TABLE when 
hive.server2.enable.doAs is set to false.)

hive.server2.enable.doAs=false，则yarn作业获取到的hiveserver2用户都为hive用户

hive.server2.enable.doAs=true，则为实际的用户名

实例2

1.直接在Hive中建内部表：

create table lqioc_ioc_mid.ceshi6(
rowid string,
ordertypeno string,
ordertypename string,
ordertypecost string,
yearid string,
monthid string,
orderitemname string,
orderitemnum string)STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES("hbase.columns.mapping" 
=":key,ORDERTYPE:ORDERTYPENO,ORDERTYPE:ORDERTYPENAME,ORDERTYPE:ORDERTYPECOST,
TIME:YEARID,TIME:MONTHID,
ORDERITEM:ORDERITEMNAME,ORDERITEM:ORDERITEMNUM")
TBLPROPERTIES("hbase.table.name" = "ceshi6","hbase.mapred.output.outputtable" = "ceshi6");

2.hive中执行插入语句：

insert into table lqioc_ioc_mid.z_area_monitor_pasq
select 
concat(FROM_UNIXTIME(UNIX_TIMESTAMP()),'_',AREA_NAME,'_',ORDER_TYPE_NO,
'_',ORDER_TYPE,'_',YEAR_ID,'_',MONTH_ID,'_',ORDER_ITEM),
ORDER_TYPE_NO,
ORDER_TYPE,
ORDER_TYPE_COST,
YEAR_ID,
MONTH_ID,
ORDER_ITEM,
ORDER_ITEM_NUM from lqioc_ioc_dw.pasq_modify;

3.检测执行情况

select * from lqioc_ioc_dw.pasq_modify;
select * from lqioc_ioc_mid.ceshi6;
scan 'ceshi6'
get 'ceshi6','Z641201812龙洲新城'

4.先删除Hbase表，再去查询Hive映射表，会报错(*^▽^*)

disable 'ceshi7'
drop 'ceshi7'
select * from lqioc_io_mid.ceshi7;

Error: java.io.IOException: org.apache.hadoop.hbase.TableNotFoundException: ceshi (state=,code=0)

 5.之后在Hive查看有这张表，但是删除Hive中的表会报错，show之后查不到表：

drop table lqioc_ioc_mid.ceshi6;
use lqioc_ioc_mid;
show tables;

Error: Error while processing statement: FAILED: 
Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 
MetaException(message:org.apache.hadoop.hbase.TableNotFoundException: ceshi7

6.根据rowkey查询历史数据

scan 'z_area_monitor_pasq', {STARTROW=>'2019-01-04 15:31:52', STOPROW=>'2019-01-04 15:36:52'}
scan 'z_area_monitor_pasq', {STARTROW=>'2019-01-04 15:31:52', ENDROW=>'2019-01-04 15:36:52'}

7.修改数据版本，获取多版本数据

alter 'z_area_monitor_pasq',{NAME=>'ROWKEY1',VERSIONS=>3}
get 'z_area_monitor_pasq','rowkey1',{COLUMN=>'rowkey1:name',VERSIONS=>3}

 

引申：RowKey查询(Scan和Get比较)

一、HBase查询方式
    hbase的查询实现只提供两种方式：

按指定rowkey获取唯一一条记录：get方法。
按指定条件获取一批记录：scan方法。
    实现条件查询功能使用的就是scan方式，scan在使用时有以下几点值的注意：

scan可以通过setCaching与setBatch方法提高速度（以空间换时间）
scan可以通过setStartRow与setEndRow来限定范围。范围越小，性能越高。
scan可以通过setFilter方法添加过滤器，这也是分页(性能差)、多条件查询的基础。
二、RowFilter介绍

operator	description
less	小于
less_or_equal	小于等于
equal	等于
not_equal	不等于
greater_or_equal	大于等于
greater	大于
no_op	排除所有

Comparator	description
BinaryComparator	使用bytes.comparaTo()比较
BinaryPrefixComparator	和BinaryComparator差不多，从前面开始比较
NullComparator
BitComparator
RegexStringComparator	正则表达式
subStringComparator	把数字当成字符串，用contains()来判断

参考：
Get：http://hbase.apache.org/apidocs/org/apache/hadoop/hbase/client/Get.html#setFilter-org.apache.hadoop.hbase.filter.Filter-
Scan：http://hbase.apache.org/apidocs/org/apache/hadoop/hbase/client/Scan.html#setFilter-org.apache.hadoop.hbase.filter.Filter-
RowKey使用数字比字符串好：https://blog.csdn.net/alphags/article/details/53786777 
扫描参数设置： http://grokbase.com/t/hbase/user/126vtkfr7h/scan-vs-put-vs-get
RowKey查询(Scan和Get比较)：https://blog.csdn.net/high2011/article/details/80205000
RowKey设计：http://blog.chedushi.com/archives/9720
对比：http://student-lp.iteye.com/blog/2309841
Hbase中多版本(version)数据获取办法：https://blog.csdn.net/shujuelin/article/details/83657485
Hbase常用操作整理：https://blog.csdn.net/sinat_36121406/article/details/82768846
Hadoop HBase概念学习系列之优秀行键设计（十六）：https://www.cnblogs.com/zlslch/p/6140487.html
HBase的RowKey设计：https://www.cnblogs.com/zlzhoulei/p/5594773.html