Hive的安装与配置

淩亂°似流年 2023-05-31 15:21 7阅读 0赞

## 一、Hive 简单介绍 ##

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

### 1. 什么是Hive ###

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

*  由Facebook 开源，最初用于解决海量结构化的日志数据统计问题
 *  构建在Hadoop之上的数据仓库
 *  Hive 定义了一种类SQL查询语言：HQL
 *  通常用于进行离线数据处理 (采用MapReduce)
 *  底层支持多种不同的执行引擎
 *  支持多种不同的压缩格式、存储格式以及自定义函数

### 2. Hive 产生背景 ###

*  MapReduce编程的不便性
 *  HDFS上的文件缺少Schema
 *  Hive简单容易上手(提供了类SQL查询语言)
 *  Hive是为超大数据集群设计的计算/存储
 *  统一的元数据管理(可与Presto/Impala/SparkSQL等共享数据)

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

## 二、Hive 的安装与配置 ##

### 基本步骤 ###

1.  下载压缩包。
2.  解压。
3.  配置环境变量。
    
    首先需要复制与重命名`conf/hive-env.sh.template` 文件，然后编辑这个文件，修改其中的hadoop属性，指定Hadoop的路径。
    
        $ cd conf/
        $ cp hive-env.sh.template hive-env.sh
        $ vi hive-env.sh
    
    找到 `HADOOP_HOME` ，去掉前面注释符，编辑如下：
    
        # Set HADOOP_HOME to point to a specific hadoop install directory
        HADOOP_HOME=/home/yan/hadoop-3.1.2
    
    编辑`/etc/profile` 文件，设置环境变量。
    
        export HIVE_HOME=/home/yan/hive-3.1.2
        export PATH=${HIVE_HOME}/bin:$PAT
4.  配置MySQL为元数据。
    
    首先不妨查看一下默认配置文件。即`conf/hive-default.xml.template` 文件，重点关注前面几行如下：
    
        <configuration>
          
          
          
          
          ......
    
    特别注意其中提到了 `You must make your changes in hive-site.xml instead.` 这句话，也就是告诫开发者，需要把自己当的配置写到 hive-site.xml 文件中。
    
    在conf文件夹下编辑 `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.ConnectionUserName</name>
                <value>root</value>
            </property>
            <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>Yan1996></value>
            </property>
           <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value>
            </property>
            <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
            </property>
        </configuration>
5.  初始化MySQL连接。
    
    注意以下的命令是在命令行中输入，而不是在hive命令行或者mysql命令行。
    
        schematool -dbType mysql -initSchema
    
    接着登录本地mysql，就可以看到新建了一个database，`hive`，并且可以看到里面也新建了很多表。
    
    可以看出已经设置mysql为hive数据元已经成功了。
6.  启动Hive
    
    输入命令如下：(请确保已经配置好了Hive相关环境变量)
    
        hive
    
    可以看到提示信息大致如下：
    
        which: no hbase in (/home/yan/hive-3.1.2/bin:/usr/local/java/jdk1.8/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/yan/.local/bin:/home/yan/bin)
        SLF4J: Class path contains multiple SLF4J bindings.
        SLF4J: Found binding in [jar:file:/home/yan/hive-3.1.2/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
        SLF4J: Found binding in [jar:file:/home/yan/hadoop-3.1.2/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]
        Hive Session ID = cc350858-e78d-4d36-b46b-f2c6027660ce
        
        Logging initialized using configuration in jar:file:/home/yan/hive-3.1.2/lib/hive-common-3.1.2.jar!/hive-log4j2.properties Async: true
        ....
7.  具体DDL操作请查看下一篇文章。
8.  首次配置成功后，再次运行时直接输入 `hive` 即可。

### 常见问题 ###

1.  提示SSL相关警告。
    
        Thu Sep 26 06:18:16 EDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    
    解决方法：在连接MySQL的url中指定 useSSL=false
2.  提示Hadoop没有启动。
    
        Exception in thread "main" java.lang.RuntimeException: java.net.ConnectException: Call From localhost/127.0.0.1 to localhost:9000 failed on connection exception: java.net.ConnectException: Connection refused; For more details see:  http://wiki.apache.org/hadoop/ConnectionRefused
        	at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:651)
        	at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:591)
        	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:747)
        	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:683)
        	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        	at java.lang.reflect.Method.invoke(Method.java:498)
        	at org.apache.hadoop.util.RunJar.run(RunJar.java:318)
        	at org.apache.hadoop.util.RunJar.main(RunJar.java:232)
        Caused by: java.net.ConnectException: Call From localhost/127.0.0.1 to localhost:9000 failed on connection exception: java.net.ConnectException: Connection refused; For more details see:  http://wiki.apache.org/hadoop/ConnectionRefused
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        	at org.apache.hadoop.net.NetUtils.wrapWithMessage(NetUtils.java:831)
        	at org.apache.hadoop.net.NetUtils.wrapException(NetUtils.java:755)
        	at org.apache.hadoop.ipc.Client.getRpcResponse(Client.java:1515)
        	at org.apache.hadoop.ipc.Client.call(Client.java:1457)
        	at org.apache.hadoop.ipc.Client.call(Client.java:1367)
        	at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:228)
        	at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:116)
        	at com.sun.proxy.$Proxy28.getFileInfo(Unknown Source)
        	at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getFileInfo(ClientNamenodeProtocolTranslatorPB.java:900)
        	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        	at java.lang.reflect.Method.invoke(Method.java:498)
        	at org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:422)
        	at org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeMethod(RetryInvocationHandler.java:165)
        	at org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invoke(RetryInvocationHandler.java:157)
        	at org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeOnce(RetryInvocationHandler.java:95)
        	at org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:359)
        	at com.sun.proxy.$Proxy29.getFileInfo(Unknown Source)
        	at org.apache.hadoop.hdfs.DFSClient.getFileInfo(DFSClient.java:1660)
        	at org.apache.hadoop.hdfs.DistributedFileSystem$29.doCall(DistributedFileSystem.java:1583)
        	at org.apache.hadoop.hdfs.DistributedFileSystem$29.doCall(DistributedFileSystem.java:1580)
        	at org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
        	at org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus(DistributedFileSystem.java:1595)
        	at org.apache.hadoop.fs.FileSystem.exists(FileSystem.java:1683)
        	at org.apache.hadoop.hive.ql.exec.Utilities.ensurePathIsWritable(Utilities.java:4486)
        	at org.apache.hadoop.hive.ql.session.SessionState.createRootHDFSDir(SessionState.java:760)
        	at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:701)
        	at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:627)
        	... 9 more
        Caused by: java.net.ConnectException: Connection refused
        	at sun.nio.ch.SocketChannelImpl.checkConnect(Native Method)
        	at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:717)
        	at org.apache.hadoop.net.SocketIOWithTimeout.connect(SocketIOWithTimeout.java:206)
        	at org.apache.hadoop.net.NetUtils.connect(NetUtils.java:531)
        	at org.apache.hadoop.ipc.Client$Connection.setupConnection(Client.java:690)
        	at org.apache.hadoop.ipc.Client$Connection.setupIOstreams(Client.java:794)
        	at org.apache.hadoop.ipc.Client$Connection.access$3700(Client.java:411)
        	at org.apache.hadoop.ipc.Client.getConnection(Client.java:1572)
        	at org.apache.hadoop.ipc.Client.call(Client.java:1403)
        	... 34 more
    
    解决方法：启动hadoop的dfs，跳转到Hadoop目录下，然后输入命令如下，
    
        sbin/start-dfs.sh
3.  当启动Hive时，发现mysql中数据库`hive` 已经创建了，但是这个数据库中却没有一张表。
    
    删除这个数据库，需要运行命令如下：
    
        schematool -dbType mysql -initSchema
    
    然后再次启动Hive。
4.  当输入`schematool -dbType mysql -initSchema` 命令时，提示错误如下：
    
        SLF4J: Class path contains multiple SLF4J bindings.
        SLF4J: Found binding in [jar:file:/home/yan/hive-3.1.2/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
        SLF4J: Found binding in [jar:file:/home/yan/hadoop-3.1.2/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://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false
        Metastore Connection Driver :	 com.mysql.jdbc.Driver
        Metastore connection User:	 root
        org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
        Underlying cause: java.sql.SQLException : Access denied for user 'root'@'localhost' (using password: YES)
        SQL Error code: 1045
        Use --verbose for detailed stacktrace.
        *** schemaTool failed ***
    
    解决方法：仔细检查 `hive-site.xml` 是否配置正常，是否出现错误，主要错误包括url出错，密码出错等。
5.  当提示错误如下：
    
        Exception in thread "main" [com.ctc.wstx.exc.WstxLazyException] com.ctc.wstx.exc.WstxUnexpectedCharException: Unexpected character '=' (code 61); expected a semi-colon after the reference for entity 'useSSL'
         at [row,col,system-id]: [15,85,"file:/home/yan/hive-3.1.2/conf/hive-site.xml"]
        	at com.ctc.wstx.exc.WstxLazyException.throwLazily(WstxLazyException.java:40)
        	at com.ctc.wstx.sr.StreamScanner.throwLazyError(StreamScanner.java:724)
        	at com.ctc.wstx.sr.BasicStreamReader.safeFinishToken(BasicStreamReader.java:3758)
        	at com.ctc.wstx.sr.BasicStreamReader.getTextCharacters(BasicStreamReader.java:914)
        	at org.apache.hadoop.conf.Configuration$Parser.parseNext(Configuration.java:3283)
        	at org.apache.hadoop.conf.Configuration$Parser.parse(Configuration.java:3071)
        	at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:2964)
        	at org.apache.hadoop.conf.Configuration.loadResources(Configuration.java:2930)
        	at org.apache.hadoop.conf.Configuration.getProps(Configuration.java:2805)
        	at org.apache.hadoop.conf.Configuration.get(Configuration.java:1459)
        	...
        Caused by: com.ctc.wstx.exc.WstxUnexpectedCharException: Unexpected character '=' (code 61); expected a semi-colon after the reference for entity 'useSSL'
         at [row,col,system-id]: [15,85,"file:/home/yan/hive-3.1.2/conf/hive-site.xml"]
        	at com.ctc.wstx.sr.StreamScanner.throwUnexpectedChar(StreamScanner.java:653)
        	at com.ctc.wstx.sr.StreamScanner.parseEntityName(StreamScanner.java:2067)
        	at com.ctc.wstx.sr.StreamScanner.fullyResolveEntity(StreamScanner.java:1525)
        	at com.ctc.wstx.sr.BasicStreamReader.readTextSecondary(BasicStreamReader.java:4783)
        	at com.ctc.wstx.sr.BasicStreamReader.finishToken(BasicStreamReader.java:3802)
        	at com.ctc.wstx.sr.BasicStreamReader.safeFinishToken(BasicStreamReader.java:3756)
        	....
    
    解决方法：MySQL连接的URL不能使用 `&` 字符，需要使用转义符 `&` 替代。
6.  当运行`schematool -dbType mysql -initSchema` 命令时，发现如下报错：
    
        Error: Table 'CTLGS' already exists (state=42S01,code=1050)
        org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
        Underlying cause: java.io.IOException : Schema script failed, errorcode 2
        Use --verbose for detailed stacktrace.
    
    说明mysql中这个数据库已经存在了，可以考虑删除这个数据库重新运行上面的语句，也可以不运行上面的语句，直接运行Hive。

### 参考文章 ###

1.  [https://www.cnblogs.com/dxxblog/p/8193967.html][https_www.cnblogs.com_dxxblog_p_8193967.html]
2.  [https://cwiki.apache.org/confluence/display/Hive/GettingStarted\#GettingStarted-ConfigurationManagementOverview][https_cwiki.apache.org_confluence_display_Hive_GettingStarted_GettingStarted-ConfigurationManagementOverview]

### 总结 ###

虽说这个安装配置也算是比较简单，但是我进了不少坑，遇到了不少麻烦，所以耽误了不少时间，特地在这里记录一下可能遇到的问题。

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

> Smileyan  
> 2019年10月18日 12:22

[https_www.cnblogs.com_dxxblog_p_8193967.html]: https://www.cnblogs.com/dxxblog/p/8193967.html
[https_cwiki.apache.org_confluence_display_Hive_GettingStarted_GettingStarted-ConfigurationManagementOverview]: https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-ConfigurationManagementOverview