Hive使用过程中踩过的坑

hive 启动时错误 1

Cannot execute statement:impossible to write to binary long since BINLOG_FORMAT = STATEMENT...
当启动时报错
Caused by: javax.jdo.JDOException:Couldnt obtain a new sequence(unique id):Cannot execute statement:impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITED.
NestedThrowables: java.sql.SQLException:Cannot execute statement:impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITED or READ UNCOMMITED.

 原因：这是由于 hive 的元数据存储 MYSQL 配置不当引起的

解决方案 1（临时解决）：mysql> set global binlog_format='MIXED'

解决方案 2（永久解决）：修改 /etc/my.cnf, 添加属性
# binary logging format - mixed recommended 
binlog_format=mixed

hive 启动时错误 2

Exception in thread "main" java.lang.IncompatibleClassChangeError: Found class jline.Terminal, but interface was expected
    at jline.console.ConsoleReader.<init>(ConsoleReader.java:230)
    at jline.console.ConsoleReader.<init>(ConsoleReader.java:221)
    at jline.console.ConsoleReader.<init>(ConsoleReader.java:209)
    at org.apache.hadoop.hive.cli.CliDriver.setupConsoleReader(CliDriver.java:787)
    at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:721)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
    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)

 原因：hadoop 目录下存在老版本 jline：/hadoop-2.7.2/share/hadoop/yarn/lib：-rw-r--r-- 1 root root   87325 Mar 10 18:10 jline-0.9.94.jar

解决：将 hive 安装路径下的 lib 目录下的 jline- 版本号.jar 文件拷贝到 hadoop 安装目录 /share/hadoop/yarn/lib 下即可
如：cp -r /hive/lib/jline-2.12.jar /hadoop-2.7.2/share/hadoop/yarn/lib

hive 建表时出现

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.) (state=08S01,code=1)。

 原因：在创建 mysql 时使用的字符集不对，需要修改 hive 数据库的字符集。解决：在 mysql 中使用命令修改 hive 数据库字符集：alter database hive character set latin1;

在 drop 表时卡死

 原因：由于是先创建的表，之后再修改的 hive 数据库的字符集，所以卡死。解决：1. 进入 mysql, 将 mysql 下建立的元数据库 hive 删除，再使用 mysql 重新创建，创建后修改字符集为 latin1，在 mysql 删除 hive 数据库时，需要将 hive 停止，不然 mysql 也会卡死。2. 也可以修改 /etc/my.cnf 文件，将里边涉及到字符的属性都设置为 latin1