hive与hbase整合

最新推荐文章于 2024-11-23 09:42:34 发布

原创最新推荐文章于 2024-11-23 09:42:34 发布 · 102 阅读

0 ·

CC 4.0 BY-SA版权

Linux 专栏收录该内容

88 篇文章

订阅专栏

本文介绍如何在HBase中创建表，并通过Hive建立外表与之关联，实现数据的查询和导入。演示了两种不同的Hive外表创建方式及数据导入过程。

Hbase中建立表test1:

hbase(main):016:0> describe 'test1'
Table test1 is ENABLED                                                                                                                              
test1                                                                                                                                               
COLUMN FAMILIES DESCRIPTION                                                                                                                         
{NAME => 'cf1', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', TTL => 'F
OREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}                          
{NAME => 'cf2', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', TTL => 'F
OREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}                          
2 row(s) in 0.0300 seconds

第一种方式

在hive中建立外表hive_test_hbase_test1 与 hbase中的test1关联

hive> create external table hive_test_hbase_test1(key int,cf1 map<string,string>,cf2 map<string,string>)
    > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    > WITH SERDEPROPERTIES ("hbase.columns.mapping" ="cf1:,cf2:")
    > TBLPROPERTIES  ("hbase.table.name" = "test1");
OK
Time taken: 1.161 seconds
hive>

在Hbase中插入数据：

hbase(main):019:0> scan 'test1'
ROW                                    COLUMN+CELL                                                                                                  
 1                                     column=cf1:age, timestamp=1497409407103, value=15                                                            
 1                                     column=cf1:name, timestamp=1497407543323, value=tom                                                          
1 row(s) in 0.0170 seconds

hbase(main):020:0>

在Hive中可以进行查看：

hive> select * from hive_test_hbase_test1;
OK
1	{"age":"15","name":"tom"}	{}
Time taken: 0.223 seconds, Fetched: 1 row(s)
hive>

第二种方式

在hive中建立外表，与hbase中的test1关联

hive> create external table hive_test3_hbase_test1(rowkey string,name string,age string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:name,cf1:age") TBLPROPERTIES("hbase.table.name" = "test1");
OK
Time taken: 0.229 seconds
hive>

hive中外表不能直接从本地导入数据，新建一个数据表data_test5_2

hive> CREATE TABLE data_test5_2 (rowkey string, name string, age string) ROW FORMAT DELIMITED FIELDS terminated by '\t' stored as textfile;OK
Time taken: 0.16 seconds
hive>

导入数据

hive> load data local inpath '/home/tom/data2.txt' overwrite into table data_test5_2;
Loading data to table default.data_test5_2
OK
Time taken: 0.653 seconds
hive> select * from data_test5_2;
OK
2	jerry	20
3	Serena	21
4	Jane	18
Time taken: 0.134 seconds, Fetched: 3 row(s)
hive>

data2.txt数据如下：

2       jerry   20
3       Serena  21
4       Jane    18

导入至外表

hive> INSERT OVERWRITE TABLE hive_test5_hbase_test1 SELECT rowkey,name,age FROM  data_test5_2;

LOAD DATA LOCAL INPATH '/home/tom/data_json.txt' OVERWRITE INTO TABLE hive_test3_hbase_test1