安装hive并配置mysql信息

前提，安装好hadoop/mysql/java环境变量等

我用的hadoop-2.7.5,mysql-5.7,hive-2.3.3

下载压缩包，解压，配置环境变量

将hive安装包conf下的hive-default.xml.template修改为hive-site.xml

下面需要修改hive-site.xml中的配置信息

1、添加如下配置信息

<!-->此段落配置hive连接mysql并在mysql中存储元信息的配置<-->   <!-->执行的命令为schematool -dbType mysql -initSchema<-->         <property>                 <name>javax.jdo.option.ConnectionURL</name>                 <value>jdbc:mysql://localhost:3306/hahive?useSSL=true&amp;characterEncoding=utf-8</value>（mysql地址）         </property>         <property>                 <name>javax.jdo.option.ConnectionDriverName</name>（mysql的驱动）                 <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>root</value>         </property>         <property>                 <name>hive.metastore.schema.verification</name>                 <value>false</value>         </property>

 

需要放在配置文件的最开始的位置

2、同时需要注释掉下面几行配置(是分开的，并不在一块)

<property>     <name>javax.jdo.option.ConnectionURL</name>     <value>jdbc:derby:;databaseName=metastore_db;create=true</value>     <description>       JDBC connect string for a JDBC metastore.       To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.       For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.     </description> </property>   <property>     <name>javax.jdo.option.ConnectionDriverName</name>     <value>org.apache.derby.jdbc.EmbeddedDriver</value>     <description>Driver class name for a JDBC metastore</description> </property>   <property>     <name>javax.jdo.option.ConnectionUserName</name>     <value>APP</value>     <description>Username to use against metastore database</description> </property>   <property>     <name>javax.jdo.option.ConnectionPassword</name>     <value>mine</value>     <description>password to use against metastore database</description></property>

 

即与1中配置的name相同的配置信息，否则，在执行schematool -dbType mysql -initSchema命令进行初始化时会报错：

导致初始化不成功schemaTool failed.

3、需要修改配置中的相对路径为绝对路径，其中../tmp文件夹需要自己创建并赋予读写的权限，如下

<property>     <name>hive.exec.local.scratchdir</name>     <!--><value>${system:java.io.tmpdir}/${system:user.name}</value><-->     <value>/home/apache-hive-2.3.3-bin/tmp/${user.name}</value>     <description>Local scratch space for Hive jobs</description> </property> <property>     <name>hive.downloaded.resources.dir</name>     <!--><value>${system:java.io.tmpdir}/${hive.session.id}_resources</value><-->     <value>/home/apache-hive-2.3.3-bin/tmp/${hive.session.id}_resources</value> <description>Temporary local directory for added resources in the remote file system.</description> </property>   <property>     <name>hive.querylog.location</name>     <!--><value>${system:java.io.tmpdir}/${system:user.name}</value><-->     <value>/home/apache-hive-2.3.3-bin/tmp/${user.name}</value>     <description>Location of Hive run time structured log file</description> </property> <property>     <name>hive.server2.logging.operation.log.location</name>     <!--><value>${system:java.io.tmpdir}/${system:user.name}/operation_logs</value><-->     <value>/home/apache-hive-2.3.3-bin/tmp/${user.name}/operation_logs</value>     <description>Top level directory where operation logs are stored if logging functionality is enabled</description> </property>

否则在进入hive客户端时或者在show databases时会报错，如下：

Exception in thread "main" java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
    at org.apache.hadoop.fs.Path.initialize(Path.java:205)
    at org.apache.hadoop.fs.Path.<init>(Path.java:171)
    at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:659)
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:582)
    at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:549)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:750)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686)
    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:221)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
    at java.net.URI.checkPath(URI.java:1823)
    at java.net.URI.<init>(URI.java:745)
    at org.apache.hadoop.fs.Path.initialize(Path.java:202)
    ... 12 more

Failed with exception java.io.IOException:java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:user.name%7D

4、在配置mysql地址信息时即javax.jdo.option.ConnectionURL需要加上useSSL=true;如中配置所示，并且“&”字符需要用转义字符“&amp;”代替       

        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://localhost:3306/hahive?useSSL=true&amp;characterEncoding=utf-8</value>（mysql地址）
        </property>

否则，在执行查询语句时，例如show databases时，会出现一堆你肯定不想看到的信息，如下：

Mon May 06 16:48:25 CST 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 SSLconnection 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.
Mon May 06 16:48:25 CST 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 SSLconnection 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.
Mon May 06 16:48:25 CST 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 SSLconnection 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.
Mon May 06 16:48:25 CST 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 SSLconnection 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.
Mon May 06 16:48:27 CST 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 SSLconnection 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.
Mon May 06 16:48:27 CST 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 SSLconnection 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.
Mon May 06 16:48:27 CST 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 SSLconnection 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.
Mon May 06 16:48:27 CST 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 SSLconnection 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.