大数据项目实践（四）——之Hive配置

一、 首先解压安装包

tar -zxf hive-1.1.0-cdh5.10.2.tar.gz -C /opt/modules/

二、 进入hive安装目录

[bigdata@bigdata-pro02 opt]$ cd /opt/modules/hive-1.1.0-cdh5.10.2/
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ ll
total 460
drwxr-xr-x 2 bigdata bigdata   4096 Jun 27  2017 auxlib
drwxr-xr-x 4 bigdata bigdata   4096 Aug 26 09:39 bin
drwxr-xr-x 2 bigdata bigdata   4096 Aug 26 09:42 conf
drwxr-xr-x 3 bigdata bigdata   4096 Jun 27  2017 data
-rw-rw-r-- 1 bigdata bigdata    693 Aug 25 21:26 derby.log
drwxr-xr-x 6 bigdata bigdata   4096 Jun 27  2017 docs
drwxr-xr-x 4 bigdata bigdata   4096 Jun 27  2017 examples
drwxr-xr-x 7 bigdata bigdata   4096 Jun 27  2017 hcatalog
drwxr-xr-x 4 bigdata bigdata  12288 Aug 26 09:24 lib
-rw-r--r-- 1 bigdata bigdata  24754 Jun 27  2017 LICENSE
drwxrwxr-x 2 bigdata bigdata   4096 Aug 26 09:06 logs
drwxrwxr-x 5 bigdata bigdata   4096 Aug 25 21:26 metastore_db
-rw-r--r-- 1 bigdata bigdata    397 Jun 27  2017 NOTICE
-rw-r--r-- 1 bigdata bigdata   4048 Jun 27  2017 README.txt
-rw-r--r-- 1 bigdata bigdata 376416 Jun 27  2017 RELEASE_NOTES.txt
drwxr-xr-x 3 bigdata bigdata   4096 Jun 27  2017 scripts
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 

三、 进入conf文件，配置参数

1.新建hive-site.xml文件

touch hive-site.xml 

2.编辑hive-site.xml，将以下内容粘贴进去

vim hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://bigdata-pro02.mars.com/metastore?createDatabaseIfNotExist=true</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>123456</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>

        <!--> 配置客户端参数：表头显示字段名， 显示db名 <-->
        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
        </property>

        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
        </property>
</configuration>

四、 下载jdbc驱动包

1.去官网下载mysql-connector-java-5.1.* 驱动安装包
官网下载地址 https://downloads.mysql.com/a...

2.将下载下来的压缩文件mysql-connector-java-5.1.46.tar.gz解压到 /opt/modules/hive-1.1.0-cdh5.10.2/lib/

tar -zxf mysql-connector-java-5.1.46.tar.gz -C /opt/modules/hive-1.1.0-cdh5.10.2/lib/

五、 启动hive

前提是已经启动了hdfs , yarn , MySQL等服务

[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ ./hive
-bash: ./hive: No such file or directory
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ bin/hive
which: no hbase in (/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/opt/modules/jdk1.8.0_171/bin:/home/bigdata/bin)
18/08/26 20:40:35 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

Logging initialized using configuration in file:/opt/modules/hive-1.1.0-cdh5.10.2/conf/hive-log4j.properties
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive (default)> show databases;
OK
database_name
default
Time taken: 2.436 seconds, Fetched: 1 row(s)
hive (default)> show tables;
OK
tab_name
test
Time taken: 0.034 seconds, Fetched: 1 row(s)
hive (default)> 

六、 Hive 和 hbase 集成

1.配置hive-site.xml文件

    <!--> hive 和 hbase 集成，配置zookeeper信息 <-->
    <property>
        <name>hbase.zookeeper.quorum</name>
        <value>bigdata-pro01.mars.com,bigdata-pro02.mars.com,bigdata-pro03.mars.com,bigdata-pro04.mars.com,bigdata-pro05.mars.com</value>
    </property>

2.将hbase的这9个包拷贝到hive/lib下，或者做软连接，如果cdh 同版本的， 就不需要拷贝，因为cdh本身已经做了集成。

ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-hadoop-compat-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-hadoop-compat-1.2.0-cdh5.10.2.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-hadoop2-compat-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-hadoop2-compat-1.2.0-cdh5.10.2.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-it-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-it-1.2.0-cdh5.10.2.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-client-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-client-1.2.0-cdh5.10.2.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-server-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-server-1.2.0-cdh5.10.2.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-protocol-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-protocol-1.2.0-cdh5.10.2.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/htrace-core-3.2.0-incubating.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/htrace-core-3.2.0-incubating.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/high-scale-lib-1.1.1.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/high-scale-lib-1.1.1.jar
ln -s /opt/modules/hbase-1.2.0-cdh5.10.2/lib/hbase-common-1.2.0-cdh5.10.2.jar /opt/modules/hive-1.1.0-cdh5.10.2/lib/hbase-common-1.2.0-cdh5.10.2.jar

3.然后启动bin/hive，新建hive外部表

[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ bin/hive
2018-08-26 22:57:45,868 WARN  [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
18/08/26 22:57:47 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

Logging initialized using configuration in file:/opt/modules/hive-1.1.0-cdh5.10.2/conf/hive-log4j.properties
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive (default)> create external table t1_hbase(
              > id string 
              > ,username string 
              > ,sex string 
              > ,age string 
              > ) 
              > stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
              > with serdeproperties("hbase.columns.mapping"=
              > ":key,f1:username,f1:sex,f1:age")
              > tblproperties("hbase.table.name"="t1") ;
OK
Time taken: 2.627 seconds
hive (default)> show tables;
OK
tab_name
t1_hbase
test
Time taken: 0.068 seconds, Fetched: 2 row(s)

查看表内容：

hive (default)> select * from t1_hbase;
OK
t1_hbase.id     t1_hbase.username       t1_hbase.sex    t1_hbase.age
0001    zhangsan        F       30
0002    lisi    M       40
Time taken: 0.238 seconds, Fetched: 2 row(s)
hive (default)> 

七、 hiveserver2 /beeline 的使用

hive cli 和 beeline
hive提供了很多种连接方式:
cli //基于Apache thrift
beeline //基于jdbc
1.hive-site.xml添加配置，关闭 【可以不配】

    <!-->beeline hiveserver2 配置，关闭hiveserver认证<-->
    <property>
        <name>hive.server2.enable.doAs</name>
        <value>false</value>
    </property>

2.启动hiveserver2服务

[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ bin/hive --service hiveserver2 &
[1] 76294
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 2018-08-26 23:05:01,884 WARN  [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
18/08/26 23:05:02 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 
[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ 

也可以使用bin/hiveserver2 启动

/hiveserver2 
2018-08-27 17:39:35,209 WARN  [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
18/08/27 17:39:36 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

3.查看服务端口

[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ sudo netstat -anop | grep 76294
tcp        0      0 0.0.0.0:10002               0.0.0.0:*                   LISTEN      76294/java          off (0.00/0/0)
tcp        0      0 0.0.0.0:10000               0.0.0.0:*                   LISTEN      76294/java          off (0.00/0/0)
tcp        0      0 192.168.10.132:33144        192.168.10.132:3306         ESTABLISHED 76294/java          keepalive (6734.61/0/0)
tcp        0      0 192.168.10.132:33140        192.168.10.132:3306         ESTABLISHED 76294/java          keepalive (6733.80/0/0)
tcp        0      0 192.168.10.132:33139        192.168.10.132:3306         ESTABLISHED 76294/java          keepalive (6733.80/0/0)
tcp        0      0 192.168.10.132:33143        192.168.10.132:3306         ESTABLISHED 76294/java          keepalive (6734.61/0/0)
tcp        0      0 127.0.0.1:10000             127.0.0.1:59652             ESTABLISHED 76294/java          keepalive (6771.82/0/0)
unix  2      [ ]         STREAM     CONNECTED     900505 76294/java          
unix  2      [ ]         STREAM     CONNECTED     899452 76294/java

10002 //hiveserver2 WebUI
10000 //hiveserver2 rpc 用来远程jdbc连接

4.使用beeline链接
bin/beeline -u jdbc:hive2://
或者
bin/beeline -u jdbc:hive2://localhost:10000

[bigdata@bigdata-pro02 hive-1.1.0-cdh5.10.2]$ bin/beeline -u jdbc:hive2://localhost:10000
2018-08-26 23:17:49,944 WARN  [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
scan complete in 1ms
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 1.1.0-cdh5.10.2)
Driver: Hive JDBC (version 1.1.0-cdh5.10.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.10.2 by Apache Hive
0: jdbc:hive2://localhost:10000> show databases;
INFO  : Compiling command(queryId=bigdata_20180826231717_6f3ff603-f579-4d0e-b28a-52296fbea193): show databases
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:database_name, type:string, comment:from deserializer)], properties:null)
INFO  : Completed compiling command(queryId=bigdata_20180826231717_6f3ff603-f579-4d0e-b28a-52296fbea193); Time taken: 0.02 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=bigdata_20180826231717_6f3ff603-f579-4d0e-b28a-52296fbea193): show databases
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing command(queryId=bigdata_20180826231717_6f3ff603-f579-4d0e-b28a-52296fbea193); Time taken: 0.022 seconds
INFO  : OK
+----------------+--+
| database_name  |
+----------------+--+
| default        |
+----------------+--+
1 row selected (0.132 seconds)
0: jdbc:hive2://localhost:10000> select * from t1_hbase;
INFO  : Compiling command(queryId=bigdata_20180826231818_0909ec2e-789f-4f05-a036-ca218b2ee590): select * from t1_hbase
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:t1_hbase.id, type:string, comment:null), FieldSchema(name:t1_hbase.username, type:string, comment:null), FieldSchema(name:t1_hbase.sex, type:string, comment:null), FieldSchema(name:t1_hbase.age, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=bigdata_20180826231818_0909ec2e-789f-4f05-a036-ca218b2ee590); Time taken: 0.118 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=bigdata_20180826231818_0909ec2e-789f-4f05-a036-ca218b2ee590): select * from t1_hbase
INFO  : Completed executing command(queryId=bigdata_20180826231818_0909ec2e-789f-4f05-a036-ca218b2ee590); Time taken: 0.0 seconds
INFO  : OK
+--------------+--------------------+---------------+---------------+--+
| t1_hbase.id  | t1_hbase.username  | t1_hbase.sex  | t1_hbase.age  |
+--------------+--------------------+---------------+---------------+--+
| 0001         | zhangsan           | F             | 30            |
| 0002         | lisi               | M             | 40            |
+--------------+--------------------+---------------+---------------+--+
2 rows selected (0.275 seconds)
0: jdbc:hive2://localhost:10000>