Hive与Hbase的整合

港控/mmm° 2022-05-25 13:51 234阅读 0赞

### Hive与Hbase的整合 ###

Hive版本是2.1.1，Hbase版本是1.4.3

#### 1. Hbase表映射到Hive表中 ####

**1.1 Hive的配置文件修改**

1.1.1 修改hive-site.xml配置文件，添加配置属性

<property>
        <name>hbase.zookeeper.quorum</name>
        <value>server01:2181,server02:2181,server03:2181</value>
    </property>

![image][]

1.1.2 修改hive-env.sh文件

# 添加一个环境变量
    export HIVE_CLASSPATH=$HIVE_CLASSPATH:/hadoop/hbase/lib/*

**1.2 Hbase数据创建**

![image][image 1]

1.2.1 在hbase中创建表，表名为`hbase_test`，有三个列族，分别为f1,f2,f3

create 'hbase_test',{NAME => 'f1',VERSION => 1},{NAME => 'f2',VERSION => 1},{NAME => 'f3',VERSION => 1}

![image][image 2]

1.2.2 向hbase\_test表中插入数据

put 'hbase_test','001','f1:name','liubei'
    put 'hbase_test','001','f2:age','30'
    put 'hbase_test','001','f3:sex','male'
    
    put 'hbase_test','002','f1:name','guanyu'
    put 'hbase_test','002','f2:age','28'
    put 'hbase_test','002','f3:sex','female'
    
    put 'hbase_test','003','f1:name','zhangfei'
    put 'hbase_test','003','f2:age','27'
    put 'hbase_test','003','f3:sex','male'

1.2.3 scan查询

scan 'hbase_test'

![image][image 3]

**1.3 Hive创建数据库，关联Hbase**

1.3.1 创建Hive表

# 这里使用的是外部表，即便是删除hive表，Hbase的数据也存在
    
    CREATE EXTERNAL TABLE hiveFromHbase(
    rowkey string,
    f1 map<STRING,STRING>,
    f2 map<STRING,STRING>,
    f3 map<STRING,STRING>
    ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:,f2:,f3:")
    TBLPROPERTIES ("hbase.table.name" = "hbase_test");

*  hbase.columns.mapping：Hive表和HBase表的字段映射关系。
    
    分别为：Hive表中第一个字段映射:key(rowkey)，第二个字段映射列族f1，第三个字段映射列族f2,第四个字段映射列族f3
 *  hbase.table.name：hbase表名称。

![image][image 4]

1.3.2 使用hive的HQL语句查询

select * from hivefromhbase;

![image][image 5]

1.3.3 Hive插入数据到Hbase表

insert into table hivefromhbase select '004' as rowkey, map('name','caocao') as f1, map('age','34') as f2, map('sex','male') as f3 from person;

![image][image 6]

1.3.4 Hbase中查询数据

scan 'hbase_test'

![image][image 7]

--------------------

#### 2. Hive表映射到Hbase中 ####

2.1 在Hive中创建名称为hive\_test的表

create table hive_test( id string, name string, age int, )STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:name,f2:age,f3:address") TBLPROPERTIES ("hbase.table.name" = "hbasefromhive");

![image][image 8]

2.2 在Hbase中查看是否有`hbasefromhive`表生成

list

![image][image 9]

2.3 查看hbase的表结构

desc 'hbasefromhive'

![image][image 10]

2.4 将数据插入Hive的hive\_test中

load data local inpath '/home/hadoop/student.txt' overwrite into table person;

![image][image 11]

2.5 查看Hive的hive\_test表信息

select * from hive_test;

![image][image 12]

2.6 查看Hbase中的表信息

scan 'hbasefromhive'

![image][image 13]

[image]: /images/20220525/8e26067465334484b3f2d72ffd4f759d.png
[image 1]: /images/20220525/c0d5f891b58e453a913bdab3f2a3e984.png
[image 2]: /images/20220525/c82ebf1ccbf9448083051c58b8b47887.png
[image 3]: /images/20220525/490c85a247b64912bcfb613016026e3c.png
[image 4]: /images/20220525/9aea707cf76946a09d5c735b65c97212.png
[image 5]: /images/20220525/ac5db497bccc435ab82a78aee98cdcef.png
[image 6]: /images/20220525/ca8cd1488c6e47219e3f336f8c341c79.png
[image 7]: /images/20220525/ff744a3475364d2c91af60f40c9e39eb.png
[image 8]: /images/20220525/f0624d5da9f24f93ace6200b4dfe603a.png
[image 9]: /images/20220525/acefc62f0b89429b8b9998d240952346.png
[image 10]: /images/20220525/b617027fd24d481d8013f81a29f20c8e.png
[image 11]: /images/20220525/fdbb944da5e44555b9e7e6cf18c13d02.png
[image 12]: /images/20220525/96a86d01749641f59f642a60a2a0a19e.png
[image 13]: /images/20220525/2bde0cd5843d41288538a7f807c0ba47.png