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

在搭建Hive测试环境并使用MySQL存储Hive元数据时遇到中文乱码问题。尝试将数据库编码更改为UTF8或latin1都无法解决。最终解决方案是保持数据库整体为latin1编码,但将涉及中文的COLUMNS和TABLE_PARAMS两个表的特定列设定为UTF8。
摘要由CSDN通过智能技术生成

最近在搭一套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 INTO `COLUMNS` (`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;

javax.jdo.option.ConnectionURL

jdbc:mysql://IP:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8

JDBC connect string for a JDBC metastore

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;语句也没有出现乱码。

问题解决。

相关推荐

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值