Hive搭建的坑

1.配置hive-site.xml时

   不要只是把网上的代码加入到配置文件中，因为default配置文件中也有这些默认配置，会导致配置失效。

2.“WARN: Establishing SSL connection without server’s identity verification is not recommended.”

   修改配置文件的url

  <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://lmastert:3306           /hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>

   </property>

3.Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061)

   数据库重复创建的原因

   进入到mysql 把 hive和hive_metastore库删了即可。

 

4.创建表之后，show tables;出错

   Failed with exception Java.io.IOException:java.lang.IllegalArgumentException: java.NET.URISyntaxException: Relative path in absolute URI: ${system:user.name}
  在hive-site.xml配置文件中

  修改

  <property>
    <name>hive.exec.local.scratchdir</name>
    <value>/home/sparknode/hive/tmp/user/${user.name}</value>
    <description>Local scratch space for Hive jobs</description>
  </property>

 然后重新初始化

 

5. schematool -dbType mysql -initSchema --verbose 初始化失败

org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException : Communications link failure

Caused by: java.net.ConnectException: 拒绝连接 (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:301)
 

原因：拒绝连接mysql

解决办法：1.给mysql用户远程连接的权限

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;    

flush privileges;

                  2.mysql连接的ip被捆绑了

vim  /etc/mysql/mysql.conf.d/mysqld.cnf

注释掉bind-address           = 127.0.0.1

记得重启服务    service mysql restart

 

6.查询时，输出表名

  <property>
    <name>hive.cli.print.header</name>
    <value>true</value>
    <description>Whether to print the names of the columns in query output.</description>
  </property>

  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
    <description>Whether to include the current database in the Hive prompt.</description>
  </property>