在进入Hive后可以show databases; 但create table, show tables;等命令都报错,如下: [ihadoop@Master ~]$ cd /home/ihadoop/hadoop/hive-0.11.0 [ihadoop@Master hive-0.11.0]$ bin/hive Logging initialized using configuration in file:/home/ihadoop/hadoop/hive-0.11.0/conf/hive-log4j.properties Hive history file=/tmp/ihadoop/hive_job_log_ihadoop_5723@Master.Hadoop_201306251428_1159551556.txt hive> show tables; FAILED: Error in metadata: MetaException(message:Got exception: org.apache.hadoop.hive.metastore.api.MetaException 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:525) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386)...................................... 通过网上查找,发现是数据库字符集问题。只要修改MySQL中Hive元数据库MetaStore的字符集便可。 alter database dbname character set latin1;
===========================================================================================
更改后执行建库建表语句没问题,执行load data和 alter table tablename add partition (logdate='20151107') location '/cmslogs/20151107/46_log/' 语句,依旧报错。
Specified key was too long; max key length is 767 bytes。
搜索问题找到如下帖子:
Changing the MySQL settings does not change existing tables. You may
need to convert them:
改变mysql设置,不能改变已经存在的表。你需要转换表的编码。
进入mysql。查看hive的元数据表。
show create table PARTITIONS; 发现还是utf8编码。
更改表编码:
MariaDB [hive]> alter table PARTITIONS convert to character set latin1;
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hive]> alter table PARTITION_KEYS convert to character set latin1; Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
再次在hive命令行执行
hive> alter table originallogs add partition (logdate='20151107') location '/cmslogs/20151107/46_log/';
OK
Time taken: 0.249 seconds
已经解决。
由此得出两个结论: 1,改变数据库编码,不会改变已经存在的表的编码。
2,如果先建库,然后改变数据库编码,然后再启动hive元数据服务,是否能让表的编码变成latin1?
验证:
1,删除mysql上的hive元数据库。
MariaDB [hive]> drop database hive;
Query OK, 30 rows affected (0.03 sec)
2,停止hive的元数据进程。
[careland@master bin]$ ps -ef |grep hive;
careland 17600 17025 0 09:46 pts/2 00:00:00 grep --color=auto hive careland 22999 1 0 11月18 ? 00:00:56 /home/careland/hadoop/jdk1.7.0_79/bin/java -Xmx256m -Djava.net.preferIPv4Stack=true -Dhadoop.log.dir=/home/careland/hadoop/hadoop-2.6.0/logs -Dhadoop.log.file=hadoop.log -Dhadoop.home.dir=/home/careland/hadoop/hadoop-2.6.0 -Dhadoop.id.str=careland -Dhadoop.root.logger=INFO,console -Djava.library.path=/home/careland/hadoop/hadoop-2.6.0/lib/native -Dhadoop.policy.file=hadoop-policy.xml -Djava.net.preferIPv4Stack=true -Xmx512m -Dhadoop.security.logger=INFO,NullAppender org.apache.hadoop.util.RunJar /home/careland/hadoop/apache-hive-1.2.1-bin//lib/hive-service-1.2.1.jar org.apache.hadoop.hive.metastore.HiveMetaStore
[careland@master bin]$ kill -9 22999
3,在mysql上建立hive元数据。
MariaDB [(none)]> create database hive;
Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use hive; Database changed MariaDB [hive]> show tables;
Empty set (0.01 sec)
4,改变该库的编码 。
MariaDB [hive]> alter database hive character set latin1;
Query OK, 1 row affected (0.01 sec)
5,启动hive元数据服务。
[careland@master bin]$ pwd
/home/careland/hadoop/apache-hive-1.2.1-bin/bin [careland@master bin]$ nohup ./hive --service metastore & [1] 17690 [careland@master bin]$ nohup: 忽略输入并把输出追加到"nohup.out"
[careland@master bin]$
6,查看mysql中的hive元数据表的编码。
MariaDB [hive]> use hive;
Database changed
MariaDB [hive]> show tables;
MariaDB [hive]> show create table PARTITIONS;
已经改为latin1编码 。
验证结论2正确。
综述: hive命令出现
Specified key was too long; max key length is 767 bytes 错误。是由于元数据的编码导致。
需要更改元数据库编码。如果元数据库中已经有了数据,不能删除。那么就更改对应的表的编码。如果元数据库为空。那么就删除元数据库。再重新建立数据库,并更改编码,然后再让hive来建表。
更改表的编码方式:
1,
ALTER
TABLE
tbl_name
CONVERT
TO
CHARACTER
SET
charset_name;
|
hive执行命令Specified key was too long; max key length is 767 bytes 问题
最新推荐文章于 2024-07-20 17:06:41 发布