hive 建表异常处理字节太长

hive create table: Specified key was too long; max key length is 767 bytes  

2013-12-26 14:37:04|  分类： Linux|举报|字号 订阅

 

 

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

<?xml version="1.0"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<!--

   Licensed to the Apache Software Foundation (ASF) under one or more

   contributor license agreements.  See the NOTICE file distributed with

   this work for additional information regarding copyright ownership.

   The ASF licenses this file to You under the Apache License, Version 2.0

   (the "License"); you may not use this file except in compliance with

   the License.  You may obtain a copy of the License at

 

       http://www.apache.org/licenses/LICENSE-2.0

 

   Unless required by applicable law or agreed to in writing, software

   distributed under the License is distributed on an "AS IS" BASIS,

   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

   See the License for the specific language governing permissions and

   limitations under the License.

-->

 

<configuration>

 

<!-- WARNING!!! This file is provided for documentation purposes ONLY!     -->

<!-- WARNING!!! Any changes you make to this file will be ignored by Hive. -->

<!-- WARNING!!! You must make your changes in hive-site.xml instead.       -->

 

 

<!-- Hive Execution Parameters -->

 

<property>

  <name>javax.jdo.option.ConnectionURL</name>

  <value>jdbc:mysql://127.0.0.1:3306/hive?createDatabaseIfNotExist=true</value>

  <description>JDBC connect string for a JDBC metastore</description>

</property>

 

<property>

  <name>javax.jdo.option.ConnectionDriverName</name>

  <value>com.mysql.jdbc.Driver</value>

  <description>Driver class name for a JDBC metastore</description>

</property>

 

<property>

  <name>javax.jdo.option.ConnectionUserName</name>

  <value>root</value>

  <description>username to use against metastore database</description>

</property>

 

<property>

  <name>javax.jdo.option.ConnectionPassword</name>

  <value>root</value>

  <description>password to use against metastore database</description>

</property>

 

</configuration>

 

并且把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.MySQLSyntaxErrorException: 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)) - </PERFLOG method=TimeToSubmit start=1392281339891 end=1392281339910 duration=19>

  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.MySQLSyntaxErrorException: 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>