Hive元数据存于mysql中文乱码解决

http://www.crazyant.net/1193.html

最近在搭一套Hive测试环境，使用mysql存储hive的元数据，然而当执行一个带有中文注释的建表文件时，出现了中文乱码的问题。实验过程中发现把整个数据库编码改成latin1或者Utf8都不行，只有把整个数据库设定为latin1而把要存储中文的数据表的编码设定为UTF8才能够解决。

出现问题：执行hive -f "createtable.sql"时说中文乱码不认识。

尝试1失败：将meta database（mysql）的编码全部设定为utf8

则报以下错误 
——————- 
FAILED: Error in metadata: 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:39) 
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) 
at java.lang.reflect.Constructor.newInstance(Constructor.java:513) 
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) 
at com.mysql.jdbc.Util.getInstance(Util.java:386) 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) 
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618) 
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568) 
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:842) 
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:681) 
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) 
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) 
at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:730) 
at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatementList(AbstractTable.java:681) 
at org.datanucleus.store.rdbms.table.AbstractTable.create(AbstractTable.java:402) 
at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:458) 
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:2689) 
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:2503) 
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2148) 
at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:113) 
at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:986) 
at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:952) 
at org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:919) 
at org.datanucleus.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:356) 
at org.datanucleus.store.rdbms.query.legacy.ExtentHelper.getExtent(ExtentHelper.java:48) 
at org.datanucleus.store.rdbms.RDBMSStoreManager.getExtent(RDBMSStoreManager.java:1332)
at org.datanucleus.ObjectManagerImpl.getExtent(ObjectManagerImpl.java:4149) 
at org.datanucleus.store.rdbms.query.legacy.JDOQLQueryCompiler.compileCandidates(JDOQLQueryCompiler.java:411) 
at org.datanucleus.store.rdbms.query.legacy.QueryCompiler.executionCompile(QueryCompiler.java:312) 
at org.datanucleus.store.rdbms.query.legacy.JDOQLQueryCompiler.compile(JDOQLQueryCompiler.java:225) 
at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.compileInternal(JDOQLQuery.java:175) 
at org.datanucleus.store.query.Query.executeQuery(Query.java:1628) 
at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.executeQuery(JDOQLQuery.java:245) 
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1499) 
at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:266) 
at org.apache.hadoop.hive.metastore.ObjectStore.getMTable(ObjectStore.java:775) 
at org.apache.hadoop.hive.metastore.ObjectStore.getTable(ObjectStore.java:709) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.is_table_exists(HiveMetaStore.java:965) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:887) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.access$600(HiveMetaStore.java:109) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$15.run(HiveMetaStore.java:945) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$15.run(HiveMetaStore.java:942) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.executeWithRetry(HiveMetaStore.java:307) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table(HiveMetaStore.java:942) 
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:348) 
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:469) 
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:3149) 
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:213) 
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:130) 
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57) 
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1063) 
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:900) 
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:748) 
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165) 
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:246) 
at org.apache.hadoop.hive.cli.CliDriver.processReader(CliDriver.java:267) 
at org.apache.hadoop.hive.cli.CliDriver.processFile(CliDriver.java:274) 
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:435) 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 
at java.lang.reflect.Method.invoke(Method.java:597) 
at org.apache.hadoop.mapred.util.RunJar.main(RunJar.java:210) 
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54) 
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65) 
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79) 
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)

NestedThrowables: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes 
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask 
——————-

尝试2失败：将meta server（mysql）的编码全部设定为latin1

则报以下错误： 
——— 
[peishuaishuai@www.crazyant.net sql]$ hive -f crazyant.sql 
Hive history file=/tmp/crazyant/hive_job_log_crazyant_20130904.txt 
OK 
Time taken: 2.064 seconds 
FAILED: Error in metadata: javax.jdo.JDODataStoreException: Add request failed : INSERT INTOCOLUMNS (SD_ID,COMMENT,COLUMN_NAME,TYPE_NAME,INTEGER_IDX) VALUES (?,?,?,?,?) 
NestedThrowables: 
java.sql.BatchUpdateException: Incorrect string value: ‘\xC2\x8A\xC2\xA8\xC3\xA7…’ for column ‘COMMENT’ at row 1 
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask 
———

解决方案：数据库编码为latin1，COLUMNS表和TABLE_PARAMS表编码设定为UTF8

1、首先按照latin1编码建立hive的meta数据库；

2、修改hive-default.xml的连接编码为UTF8；

<property> 
  <name>javax.jdo.option.ConnectionURL</name> 
  <value>jdbc:mysql://IP:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8</value> 
  <description>JDBC connect string for a JDBC metastore</description> 
</property>

3、然后进入数据库执行以下两条SQL语句：

该语句将HIVE的COLUMNS 表的column 列改成UTF8编码

alter table COLUMNS modify column COMMENT varchar(256) character set utf8

该语句将HIVE的TABLE_PARAMS 表的PARAM_VALUE 列改成UTF8编码

alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8

执行完后用hive -f create_table.sql 中文乱码没有出现，进入数据库执行desc table；语句也没有出现乱码。

问题解决。

======================================================================================

hive metadata 存mysql 注释中文乱码的有关

hive metadata 存mysql 注释中文乱码的问题

hive metadata 存mysql 注释中文乱码的问题
########### hive 中文乱码的问题#############

解决方案：数据库编码为latin1.将一下表的字段(涉及注释的字段都改)编码设定为UTF8

1、然后进入数据库执行以下5条SQL语句：

(1)修改表字段注解和表注解

alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8

alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8

(2) 修改分区字段注解：

 alter table PARTITION_PARAMS  modify column PARAM_VALUE varchar(4000) character set utf8 ; 

alter table PARTITION_KEYS  modify column PKEY_COMMENT varchar(4000) character set utf8; 

(3)修改索引注解：

alter table  INDEX_PARAMS  modify column PARAM_VALUE  varchar(4000) character set utf8;

 

2、修改hive连接mysql的连接为utf-8<property> 

   <name></name> 

  <value>jdbc:mysql://IP:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8</value> 
  <description>JDBC connect string for a JDBC metastore</description> 
</property>