利用hive查看Hbase的数据

利用hive查看Hbase的数据

通过建立hive的一个外部表，建立Hbase与hive的关联关系，

CREATE EXTERNAL TABLE meter_record (KEY STRING,ID STRING,CUSTID INT, METERID STRING, DATAVALUE  STRING, DATATIME STRING, SYSTEMID TINYINT, RECORDTIME TINYINT, RECORDMANID STRING) STORED BY "org.apache.hadoop.hive.hbase.HBaseStorageHandler" WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key,info:ID,info:CUSTID, info:METERID, info:DATAVALUE, info:DATATIME,info:SYSTEMID, info:RECORDTIME, info:RECORDMANID","hbase.table.default.storage.type"="binary") TBLPROPERTIES ("hbase.table.name"="meter_record");

需要注意的是：在Hbase中会有一个rowkey，所以在HIve表中多添加一个字段KEY，不然出现一个问题：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hadoop.hive.hbase.HBaseSerDe: columns has 8 elements while hbase.columns.mapping has 9 elements (counting the key if implicit))

这样就可以通过表hive的meter_record间接的去查询Hbase的数据，

hive> SELECT * FROM meter_record LIMIT 10;
OK
2020-07-16 10:38:25,784 INFO  [9420a24b-f137-4a12-bc21-87d930965b67 main] zookeeper.RecoverableZooKeeper: Process identifier=hconnection-0x56afbace connecting to ZooKeeper ensemble=masterhadoop:2181,secondhadoop:2181,slave01hadoop:2181,slave02hadoop:2181,slave03hadoop:2181
2020-07-16 10:38:25,797 INFO  [9420a24b-f137-4a12-bc21-87d930965b67 main] mapreduce.RegionSizeCalculator: Calculating region sizes for table "meter_record".
2020-07-16 10:38:47,499 INFO  [9420a24b-f137-4a12-bc21-87d930965b67 main] client.ConnectionImplementation: Closing zookeeper sessionid=0x2000826e5a80003
2020-07-16 10:38:47,530 INFO  [9420a24b-f137-4a12-bc21-87d930965b67 main] zookeeper.RecoverableZooKeeper: Process identifier=hconnection-0x7cd2d3b6 connecting to ZooKeeper ensemble=masterhadoop:2181,secondhadoop:2181,slave01hadoop:2181,slave02hadoop:2181,slave03hadoop:2181
2020-07-16 10:38:47,541 INFO  [9420a24b-f137-4a12-bc21-87d930965b67 main] mapreduce.TableInputFormatBase: Input split length: 2.9 G bytes.
"152871512"		NULL	"17030725"	78	"2019-01-01 10:45:00"	34	34	"10000002"
"152871513"		NULL	"17094008"	53.8999	"2019-01-01 10:45:00"	34	34	"10000002"
"152871514"		NULL	"17094033"	71.0999	"2019-01-01 10:45:00"	34	34	"10000002"
"152871515"		NULL	"17094072"	8.8	"2019-01-01 10:45:00"	34	34	"10000002"
"152871516"		NULL	"17094097"	19.3999	"2019-01-01 10:45:00"	34	34	"10000002"
"152871517"		NULL	"17094106"	45.7999	"2019-01-01 10:45:00"	34	34	"10000002"
"152871518"		NULL	"17094131"	42.1	"2019-01-01 10:45:00"	34	34	"10000002"
"152871519"		NULL	"17094140"	2.5	"2019-01-01 10:45:00"	34	34	"10000002"
"152871520"		NULL	"17094165"	11.8	"2019-01-01 10:45:00"	34	34	"10000002"
"152871521"		NULL	"17094174"	41.7999	"2019-01-01 10:45:00"	34	34	"10000002"
2020-07-16 10:38:47,705 INFO  [9420a24b-f137-4a12-bc21-87d930965b67 main] client.ConnectionImplementation: Closing zookeeper sessionid=0x2000826e5a80004
Time taken: 0.224 seconds, Fetched: 10 row(s)