hive create table: Specified key was too long; max

把hive 的 metadata配置成mysql,在mysql数据库里创建了hive db后,修改hive的conf目录下的hive-site.xml

并且把mysql-connector-java-5.1.18-bin.jar拷到hive的lib目录下面.

结果运行hive, create table:

hive> 

      > create table user_info(userid int, user_name string);

FAILED: Error in metadata: 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.MySQLSyntaxErrorExceptio n: Specified key was too long; max key length is 767 bytes

...

查看hive 的log:

vi /tmp/root/hive.log

   12 2014-02-13 16:48:59,910 INFO   ql.Driver (PerfLogger.java:PerfLogEnd(115)) -

   13 2014-02-13 16:48:59,968 ERROR DataNucleus.Datastore (Log4JLogger.java:error(115)) - Error thrown executing CREATE TABLE `SD_PARAMS`

   14 (

   15       `SD_ID` BIGINT NOT NULL,

   16       `PARAM_KEY` VARCHAR(256) BINARY NOT NULL,

   17       `PARAM_VALUE` VARCHAR(4000) BINARY NULL,

   18       CONSTRAINT `SD_PARAMS_PK` PRIMARY KEY (`SD_ID`,`PARAM_KEY`)

   19 ) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes

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

搜了下,在mysql上执行:

在mysql机器的上运行:

alter database hive character set latin1;

问题解决.

hive> 

      > 

      > create table user_info(userid int, user_name string);

OK

Time taken: 0.593 seconds

hive> show tables;

OK

user_info

Time taken: 0.451 seconds

hive>                   

      > load data local inpath "/u/opt/hive-0.10.0-cdh4.5.0/examples/files/kv1.txt" overwrite into table user_info;

Copying data from file:/u/opt/hive-0.10.0-cdh4.5.0/examples/files/kv1.txt

Copying file: file:/u/opt/hive-0.10.0-cdh4.5.0/examples/files/kv1.txt

Loading data to table default.user_info

Table default.user_info stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 5812, raw_data_size: 0]

OK

Time taken: 0.691 seconds

hive> 

      > select * from user_info limit 10;

OK

238       val_238

86         val_86

311       val_311

27         val_27

165       val_165

409       val_409

255       val_255

278       val_278

98         val_98

484       val_484

Time taken: 0.262 seconds

hive>