大数据运维（42）Hive 2.3.7搭建

安装hive之前先安装hadoop集群。

hive安装在master节点上，理论上是可以任意安装在其他节点的，不过要配置。

安装mysql：hive需要mysql，测试用root账号即可。

mysql> create user 'hive'@'%' identified by 'hive';    //创建一个账号：用户名为hive，密码为hive
mysql> GRANT ALL PRIVILEGES ON *.* to 'hive'@'%' IDENTIFIED BY 'hive' WITH GRANT OPTION;   //将权限授予host为%即所有主机的hive用户
mysql> GRANT ALL PRIVILEGES ON *.* to 'hive'@'master' IDENTIFIED BY 'hive' WITH GRANT OPTION;  //将权限授予host为master的hive用户
mysql> GRANT ALL PRIVILEGES ON *.* to 'hive'@'localhost' IDENTIFIED BY 'hive' WITH GRANT OPTION; //将权限授予host为localhost的hive用户（其实这一步可以不配）
mysql> flush privileges;

下载hive：

http://mirror.bit.edu.cn/apache/hive/ 
wget http://mirror.bit.edu.cn/apache/hive/hive-2.3.7/apache-hive-2.3.7-bin.tar.gz

解压：

tar -xf apache-hive-2.3.7-bin.tar.gz -C /usr/local/
cd /usr/local/
ln -sv apache-hive-2.3.7-bin/ hive

下载连接mysql的包

cd /usr/local/hive/lib
wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.36/mysql-connector-java-5.1.36.jar

配置环境变量：

cat > /etc/profile.d/hive.sh <<EOF
export HIVE_HOME=/usr/local/hive
export PATH=\$PATH:\$HIVE_HOME/bin
EOF

创建配置：

cd /usr/local/hive/conf
cp hive-default.xml.template hive-site.xml

hive-site.xml：去掉原有其他的配置。

<configuration>
  <property>
    <name>hive.exec.scratchdir</name>
    <!--这个目录是在hdfs文件系统里的-->
    <value>/user/hive/tmp</value>
  </property>
 
  <!--Hive作业的HDFS根目录创建写权限 -->
  <property>
    <name>hive.scratch.dir.permission</name>
    <value>777</value>
  </property>
 
  <!--hdfs上hive元数据存放位置 -->
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <!--这个目录是在hdfs文件系统里的-->
    <value>/user/hive/warehouse</value>
  </property>
 
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://mysql:3306/hive?createDatabaseIfNotExist=true</value>
  </property>
 
  <!--连接数据库驱动 -->
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
  </property>
 
  <!--连接数据库用户名称 -->
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>
 
  <!--连接数据库用户密码 -->
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>123456</value>
  </property>
 
  <!--客户端显示当前查询表的头信息 -->
  <property>
    <name>hive.cli.print.header</name>
    <value>true</value>
  </property>
 
  <!--客户端显示当前数据库名称信息 -->
  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
  </property>
   
  <!--web页面的监听地址和依赖包位置-->
  <property>
    <name>hive.hwi.listen.host</name>
    <value>0.0.0.0</value>
    <description>This is the host address the Hive Web Interface will listen on</description>
  </property>
  <property>
    <name>hive.hwi.listen.port</name>
    <value>9999</value>
    <description>This is the port the Hive Web Interface will listen on</description>
  </property>
  <property>
    <name>hive.hwi.war.file</name>
    <value>lib/hive-hwi-1.2.1.war</value>
    <description>This sets the path to the HWI war file, relative to ${HIVE_HOME}. </description>
  </property>
</configuration>

其他配置：

cp hive-log4j2.properties.template hive-log4j2.properties

修改个日志的目录：

property.hive.log.dir = /usr/local/hive/logs

创建日志目录：

mkdir /usr/local/hive/logs
chmod g+w /usr/local/hive/logs

修改属组属主：

cd /usr/local/
chown -R  hadoop.hadoop hive/ hive

初始化：

su hadoop
~]$ schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.7-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-2.10.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL:  jdbc:mysql://mysql:3306/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :     com.mysql.jdbc.Driver
Metastore connection User:     root
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.mysql.sql
Initialization script completed
schemaTool completed

启动：和操作mysql的命令是一样的，这是个命令行的客户端。

]$ hive
which: no hbase in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/hadoop/bin:/usr/local/hadoop/sbin:/usr/local/hive/bin:/usr/local/jdk/bin:/usr/local/jdk/jre/bin:/root/bin:/usr/local/hadoop/bin:/usr/local/hadoop/sbin:/usr/local/hive/bin:/usr/local/jdk/bin:/usr/local/jdk/jre/bin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.7-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-2.10.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
 
Logging initialized using configuration in file:/usr/local/apache-hive-2.3.7-bin/conf/hive-log4j2.properties Async: true
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. spark, tez) or using Hive 1.X releases.
hive (default)> show databases;
OK
database_name
default
starbucks
Time taken: 3.312 seconds, Fetched: 2 row(s)
hive (default)>

在数据库中创建库、表之后再插入一些数据：

show databases;
create database starbucks;
use starbucks;
create table book (id bigint, name string) row format delimited fields terminated by '\t';
insert into book(id,name) values(1,'liushiting');

查看数据：这时查看hdfs文件系统中的文件即可看到创建的文件。

hdfs dfs -ls -R /

到如hdfs的下页面可以看到相应的文件：

http://10.3.149.35:50070/explorer.html#/

启动服务：这个服务是api接口，会监听在10000端口上。

hiveserver2 &

关闭：直接kill掉hive进程即可。

ps -ef | grep hive
kill 3436