hive创建表是报错： Specified key was too long; max key length is 767 bytes

今天在hive客户端创建表时报错，具体操作如下

 

hive> create table test2(id string);

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

 

 

此错误解决方法：修改mysql 数据库的编码为latin1即可

 

 

mysql> alter database hive character set latin1;    

Query OK, 1 row affected (0.54 sec)

 

mysql> show create database hive;

+-----------+----------------------------------------------------------------------+

| Database  | Create Database                                                      |

+-----------+----------------------------------------------------------------------+

| olap_hive | CREATE DATABASE `hive` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+-----------+----------------------------------------------------------------------+

1 row in set (0.17 sec)

 

 

然后再执行创建表则能正常执行：

hive> create table test2(id string);

OK

Time taken: 3.307 seconds

 

hive> show tables; 

OK

test2

Time taken: 0.061 seconds, Fetched: 1 row(s)