Hive与HBase实现数据互导

Hive与HBase实现数据互导

建立与HBase的识别表

hive> create table hive_hbase_1(key int,value string)

> stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

> WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:info")

> TBLPROPERTIES ("hbase.table.name" = "userinfo");

OK

Time taken: 8.896 seconds

hive>

查看Hbase中的表

hbase(main):001:0> list

TABLE

blog

friend

friend02

heroes

heroesIndex

myt

stu

table

tanggao

tanggao11

tanggao111

tanggaozhou

test

userinfo

word

word2

16 row(s) in 0.7760 seconds

=> ["blog", "friend", "friend02", "heroes", "heroesIndex", "myt", "stu", "table", "tanggao", "tanggao11", "tanggao111", "tanggaozhou", "test", "userinfo", "word", "word2"]

hbase(main):002:0>

2.使用sql导入数据

i.预先准备数据 在hdfs的user/tg目录下放一个a.txt

1tanggao

2zhousiyuan

3mother

4father

a)新建hive的数据表

hive> create table famaly(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;

OK

Time taken: 0.483 seconds

2.0.0版本会默认到你的hdfs根目录下的user/hadoop用户下找 ，比如我的是user/tg

hive> load data inpath 'a.txt' overwrite into table famaly;

Loading data to table default.famaly

OK

Time taken: 2.139 seconds

查看信息

hive> select * from famaly;

OK

1tanggao

2zhousiyuan

3mother

4father

Time taken: 2.912 seconds, Fetched: 4 row(s)

查看表结构

hive> desc famaly;

OK

id int

name string

Time taken: 0.549 seconds, Fetched: 2 row(s)

第二种方法查看表结构 同上

hive> describe famaly;

OK

id int

name string

Time taken: 0.087 seconds, Fetched: 2 row(s)

hive>

使用sql导入数据到hive_hbase_1

hive> insert overwrite table hive_hbase_1 select * from famaly where id=1;

WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. tez, spark) or using Hive 1.X releases.

Query ID = tg_20160528223128_abf71520-622b-42b5-94c3-3bbb5492b558

Total jobs = 1

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_1464498775870_0001, Tracking URL = http://master:8088/proxy/application_1464498775870_0001/

Kill Command = /software/hadoop-2.6.4/bin/hadoop job -kill job_1464498775870_0001

Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0

2016-05-28 22:57:58,093 Stage-0 map = 0%, reduce = 0%

2016-05-28 22:58:28,439 Stage-0 map = 100%, reduce = 0%, Cumulative CPU 3.81 sec

MapReduce Total cumulative CPU time: 3 seconds 810 msec

Ended Job = job_1464498775870_0001

MapReduce Jobs Launched:

Stage-Stage-0: Map: 1 Cumulative CPU: 3.91 sec HDFS Read: 10964 HDFS Write: 0 SUCCESS

Total MapReduce CPU Time Spent: 3 seconds 910 msec

OK

Time taken: 89.327 seconds

查看数据

会显示刚刚插入的数据

hive> select * from hive_hbase_1;

OK

1tanggao

Time taken: 0.916 seconds, Fetched: 1 row(s)

hive>

登录HBase 查看HBase数据

hbase(main):002:0> scan 'userinfo'

ROW COLUMN+CELL

1 column=cf1:info, timestamp=1464501508097, value=tanggao

1 row(s) in 0.7990 seconds

hbase(main):003:0>

是不是很神奇，在hive中添加的数据已经在Hbase中了

下面再看看在Hbase中插入数据，看看hive中是不是也有了?试试看

hbase(main):003:0> put 'userinfo','fid','cf1:info','tangshaoyan'

0 row(s) in 0.2270 seconds

hbase(main):004:0>

查看hive

hive> select * from hive_hbase_1;

OK

1tanggao

NULLtangshaoyan

Time taken: 0.235 seconds, Fetched: 2 row(s)

hive>

没错，刚刚在hbase中插入的数据，已经在hive里了

hive访问Hbase中已经存在的Hbase表

HBase表报备

：已经存在了heroes表

hbase(main):007:0> scan 'heroes'

ROW COLUMN+CELL

0 column=info:email, timestamp=1463743975381, value=0@qq.com

0 column=info:name, timestamp=1463743975381, value=peter

0 column=info:power, timestamp=1463743975381, value=Idotknow

1 column=info:email, timestamp=1463743975391, value=1@qq.com

1 column=info:name, timestamp=1463743975391, value=hiro

1 column=info:power, timestamp=1463743975391, value=Idotknow

2 column=info:email, timestamp=1463743975396, value=2@qq.com

2 column=info:name, timestamp=1463743975396, value=sylar

2 column=info:power, timestamp=1463743975396, value=Idotknow

3 column=info:email, timestamp=1463743975399, value=3@qq.com

3 column=info:name, timestamp=1463743975399, value=claire

3 column=info:power, timestamp=1463743975399, value=Idotknow

4 column=info:email, timestamp=1463743975403, value=4@qq.com

4 column=info:name, timestamp=1463743975403, value=noah

4 column=info:power, timestamp=1463743975403, value=Idotknow

5 row(s) in 0.2140 seconds

hbase(main):008:0>

使用CREATE EXTERNAL TABLE：

hive> create external table hbase_hive_1(key int,value string)

> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "info:name")

> TBLPROPERTIES("hbase.table.name" = "heroes");

OK

Time taken: 0.424 seconds

hive> select * from hbase_hive_1;

OK

0peter

1hiro

2sylar

3claire

4noah

Time taken: 0.222 seconds, Fetched: 5 row(s)

hive>

从上面的操作后，hive已经可以访问HBase中已经存在的原有数据了

三、多列和多列族(Multiple Columns and Families)

hive> CREATE TABLE hive_hbase_add1(key int, value1 string, value2 int, value3 int)

> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

> WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:col1,info:col2,city:area")

> TBLPROPERTIES("hbase.table.name" = "student_info");

OK

Time taken: 2.624 seconds

hive> select * from hbase_hive_1;

OK

0peter

1hiro

2sylar

3claire

4noah

Time taken: 0.225 seconds, Fetched: 5 row(s)

hive> set hive.cli.print.header=true;

hive> select * from hbase_hive_1;

OK

hbase_hive_1.keyhbase_hive_1.value

0peter

1hiro

2sylar

3claire

4noah

Time taken: 0.203 seconds, Fetched: 5 row(s)

hive> desc hbase_hive_1;

OK

col_namedata_typecomment

key int

value string

Time taken: 0.198 seconds, Fetched: 2 row(s)

hive> insert overwrite table hive_hbase_add1 select key ,value,key+1,value from hbase_hive_1;

WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. tez, spark) or using Hive 1.X releases.

Query ID = tg_20160528223128_abf71520-622b-42b5-94c3-3bbb5492b558

Total jobs = 1

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_1464498775870_0002, Tracking URL = http://master:8088/proxy/application_1464498775870_0002/

Kill Command = /software/hadoop-2.6.4/bin/hadoop job -kill job_1464498775870_0002

Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0

2016-05-28 23:48:40,536 Stage-0 map = 0%, reduce = 0%

2016-05-28 23:49:06,565 Stage-0 map = 100%, reduce = 0%, Cumulative CPU 3.52 sec

MapReduce Total cumulative CPU time: 3 seconds 520 msec

Ended Job = job_1464498775870_0002

MapReduce Jobs Launched:

Stage-Stage-0: Map: 1 Cumulative CPU: 3.52 sec HDFS Read: 5082 HDFS Write: 0 SUCCESS

Total MapReduce CPU Time Spent: 3 seconds 520 msec

OK

_col0_col1_col2_col3

Time taken: 86.323 seconds

hive> insert overwrite table hive_hbase_add1 select key ,value,key+1,key+1000 from hbase_hive_1;

WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. tez, spark) or using Hive 1.X releases.

Query ID = tg_20160528223128_abf71520-622b-42b5-94c3-3bbb5492b558

Total jobs = 1

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_1464498775870_0003, Tracking URL = http://master:8088/proxy/application_1464498775870_0003/

Kill Command = /software/hadoop-2.6.4/bin/hadoop job -kill job_1464498775870_0003

Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0

2016-05-28 23:54:01,682 Stage-0 map = 0%, reduce = 0%

2016-05-28 23:54:24,725 Stage-0 map = 100%, reduce = 0%, Cumulative CPU 4.23 sec

MapReduce Total cumulative CPU time: 4 seconds 230 msec

Ended Job = job_1464498775870_0003

MapReduce Jobs Launched:

Stage-Stage-0: Map: 1 Cumulative CPU: 4.23 sec HDFS Read: 5166 HDFS Write: 0 SUCCESS

Total MapReduce CPU Time Spent: 4 seconds 230 msec

OK

keyvaluec2c3

Time taken: 72.543 seconds

hive> select * from hive_hbase_add1;

OK

hive_hbase_add1.keyhive_hbase_add1.value1hive_hbase_add1.value2hive_hbase_add1.value3

0peter1NULL

1hiro2NULL

2sylar3NULL

3claire4NULL

4noah5NULL

Time taken: 0.454 seconds, Fetched: 5 row(s)

第四列类型不匹配，没有插进，都是null

下面换为正确的类型

hive> select * from hive_hbase_add1;

OK

hive_hbase_add1.keyhive_hbase_add1.value1hive_hbase_add1.value2hive_hbase_add1.value3

0peter11000

1hiro21001

2sylar31002

3claire41003

4noah51004

Time taken: 0.746 seconds, Fetched: 5 row(s)

hive>

登录HBase查看数据

hbase(main):008:0> list

TABLE

blog

friend

friend02

heroes

heroesIndex

myt

stu

student_info

table

tanggao

tanggao11

tanggao111

tanggaozhou

test

userinfo

17 row(s) in 0.1140 seconds

=> ["blog", "friend", "friend02", "heroes", "heroesIndex", "myt", "stu", "student_info", "table", "tanggao", "tanggao11", "tanggao111", "tanggaozhou", "test", "userinfo", "word", "word2"]

hbase(main):009:0> scan 'student_info'

ROW COLUMN+CELL

0 column=city:area, timestamp=1464504

863521, value=1000

0 column=info:col1, timestamp=1464504

863521, value=peter

0 column=info:col2, timestamp=1464504

863521, value=1

1 column=city:area, timestamp=1464504

863521, value=1001

1 column=info:col1, timestamp=1464504

863521, value=hiro

1 column=info:col2, timestamp=1464504

863521, value=2

2 column=city:area, timestamp=1464504

863521, value=1002

2 column=info:col1, timestamp=1464504

863521, value=sylar

2 column=info:col2, timestamp=1464504

863521, value=3

3 column=city:area, timestamp=1464504

863521, value=1003

3 column=info:col1, timestamp=1464504

863521, value=claire

3 column=info:col2, timestamp=1464504

863521, value=4

4 column=city:area, timestamp=1464504

863521, value=1004

4 column=info:col1, timestamp=1464504

863521, value=noah

4 column=info:col2, timestamp=1464504

863521, value=5

5 row(s) in 0.0720 seconds

hbase(main):010:0>

发现hive中的四列 key int, value1 string, value2 int, value3 int

key对应HBase的行健

value1，value2对应Hbase的列族info的col1,col2

value3 对应Hbase列族city:area

是不是发现了hive中的表，多列存放到hbase少量固定的列簇中。