Hive部署及整合Hbase

1.软件版本信息

hadoop: 2.8.3
hbase: 1.0.0
hive: 2.3.4
mysql：5.7.24

2.准备mysql

# mysql 主要用于存储hive表的元数据，不会存储具体的数据

2.1 创建名称为hive的数据库

2.2 修改数据库编码格式为 latin1 ,排序规则为 latin1_bin

3. hive配置

--我将下载好的tar.gz包解压到 /home/etc/这个目录下，成为 /home/etc/apache-hive-2.3.4/

3.1 复制 /home/etc/apache-hive-2.3.4/conf/ 目录下 hive-default.xml.template 一份命名为 hive-site.xml

cp hive-default.xml.template hive-site.xml

3.2 修改内容

<!--根据name找到如下的配置并修改-->

<!--mysql连接的地址-->
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
    <description>JDBC connect string for a JDBC metastore</description>
</property>

<!--mysql连接DiverName-->
<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
</property>

<!--mysql连接用户名-->
<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>zabbix</value>
    <description>username to use against metastore database</description>
</property>

<!--mysql连接密码-->
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>root</value>
    <description>password to use against metastore database</description>
</property>

<!--io临时目录，需手动创建此目录-->
<property>
    <name>hive.exec.local.scratchdir</name>
    <value>/home/etc/apache-hive-2.3.4/iotmp</value>
    <description>Local scratch space for Hive jobs</description>
</property>

<!--io临时目录，同上目录-->
<property>
    <name>hive.querylog.location</name>
    <value>/home/etc/apache-hive-2.3.4/iotmp</value>
    <description>Location of Hive run time structured log file</description>
</property>

<!--io临时目录，同上目录-->
<property>
    <name>hive.downloaded.resources.dir</name>
    <value>/home/etc/apache-hive-2.3.4/iotmp</value>
    <description>Temporary local directory for added resources in the remote file system.         </description>
</property>

3.3 创建 iotmp目录

在 apache-hive-2.3.4目录下创建iotmp目录

3.4 配置环境变量

修改 ~/.bash_profile(用户环境变量)

# 1.配置HIVE_HOME
export HIVE_HOME=、home/etc/apache-hive-2.3.4
# 2.将HIVE_HOME加到PATH环境变量中
export PATH=$PATH:$HIVE_HOME/bin
# 3.重新加载环境变量
source ~/.bash_profile

3.5 导入hbase的jar包和mysql的驱动包

# 1.放入mysql驱动包 到 /home/etc/apache-hive-2.3.4/lib/ 下
mysql-connector-java-5.1.9.jar
# 2.放入hbase相关包 到 /home/etc/apache-hive-2.3.4/lib/ 下
进入 hbase的lib目录下
cp hbase-protocol-1.0.0.jar /home/etc/apache-hive-2.3.4/lib/
cp hbase-server-1.0.0.jar /home/etc/apache-hive-2.3.4/lib/
cp hbase-client-1.0.0.jar /home/etc/apache-hive-2.3.4/lib/
cp hbase-common-1.0.0.jar /home/etc/apache-hive-2.3.4/lib/
cp hbase-common-1.0.0-tests.jar /home/etc/apache-hive-2.3.4/lib/

3.6 在hive中加入hbase的配置

修改 /home/etc/apache-hive-2.3.4/conf/ 下的 hive-site.xml配置

<!--file下的jar路径加入到path中-->
<property>
    <name>hive.reloadable.aux.jars.path</name>
    <value>
        file:///home/etc/apache-hive-2.3.4/lib/hive-hbase-handler-2.3.4.jar,
        file:///home/etc/apache-hive-2.3.4/lib/hbase-protocol-1.0.0.jar,
        file:///home/etc/apache-hive-2.3.4/lib/hbase-server-1.0.0.jar,
        file:///home/etc/apache-hive-2.3.4/lib/hbase-client-1.0.0.jar,
        file:///home/etc/apache-hive-2.3.4/lib/hbase-common-1.0.0.jar,
        file:///home/etc/apache-hive-2.3.4/lib/hbase-common-1.0.0-tests.jar,
        file:///home/etc/apache-hive-2.3.4/lib/zookeeper-3.4.6.jar,
        file:///home/etc/apache-hive-2.3.4/lib/guava-14.0.1.jar
    </value>
    <description>
      The locations of the plugin jars, which can be a comma-separated folders or jars. Jars can be renewed
      by executing reload command. And these jars can be used as the auxiliary classes like creating a UDF or SerDe.
    </description>
</property>

<!--防止启动时报MissingTableException:Required table missing : "VERSION" in Catalog ""
Schema "". DataNucleus requires this table to perform its persistence operations-->
<property>
    <name>datanucleus.schema.autoCreateAll</name>
    <value>true</value>
    <description>Auto creates necessary schema on a startup if one doesn't exist</description>
</property>

4.启动hive

4.1 交互式启动

启动一个hive交互shell，在任意地方输入hive即可启动

set hive.cli.print.current.db=true; (让提示符显示当前库)
set hive.cli.print.header=true;(显示查询结果时显示字段名称)

4.2 启动hive服务

在 /home/etc/apache-hive-2.3.4/bin/目录下

hiveserver2 -hiveconf hive.root.logger=DEBUG,console

或者后台启动

hiveserver2 1>/dev/null 2>&1 &

5.hive建表

5.1 创建hive表的同时，创建hbase的表

在hive交互式环境下执行

# Hive中的表名tbl_name
# 指定存储处理器
# 声明列族，列名
# hbase.table.name 声明HBase表名， 为可选属性默认与Hive的表名相同， 
# hbase.mapred.output.outputtable 指定插入数据时写入的表， 如果以后需要往该表插入数据就需要指定该值
# key为rowkey,cf1为列族，val为列
CREATE TABLE tbl_name(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'   
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val")
TBLPROPERTIES ("hbase.table.name" = "tbl_name", "hbase.mapred.output.outputtable" = "tbl_name");

5.2 根据hbase表创建hive表