hive创建表时COMMENT带中文报错问题处理

版权声明：原创文章，欢迎转载但请备注来源及原文链接 https://blog.csdn.net/levy_cui/article/details/51646145
环境：CDH 5.5.1

hive创建表时带有中文就报错，如下：
CREATE TABLE IF NOT EXISTS test(pid bigint COMMENT '测试id',fid int COMMENT 'luntanid')COMMT 'zhuti' ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: Transaction failed to commit
at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)
at org.datanucleus.api.jdo.JDOTransaction.rollback(JDOTransaction.java:186)
at org.apache.hadoop.hive.metastore.ObjectStore.rollbackTransaction(ObjectStore.java:541)
at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:919)
at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:98)
at com.sun.proxy.$Proxy6.createTable(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1412)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1445)
at sun.reflect.GeneratedMethodAccessor54.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:133)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:99)
at com.sun.proxy.$Proxy8.create_table_with_environment_context(Unknown Source)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9207)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9191)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1723)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

hive-site
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop01.test:3306/hive?useUnicode=true&characterEncoding=UTF-8</value>
</property>

都没有问题啊

后来在网上无意中看到以下内容：
"""
默认情况下我们需要将数据库的编码设置为lanin1.
alter database hive character set latin1;

但为了以下是为了支持hive建表时插入中文注释需要在mysql中做如下设置：

use hive;

//修改字段注释字符集
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;
//修改分区注释字符集
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
"""

尝试执行以上4条命令，执行这个命令后就正常了。

数据库字符集变latin1

当时我创建hive库的时候使用如下命令：
create database hive DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

不知道为什么会这样，但是目前解决的建表中文问题，先这样处理了。
---------------------
作者：levy_cui
来源：CSDN
原文：https://blog.csdn.net/levy_cui/article/details/51646145
版权声明：本文为博主原创文章，转载请附上博文链接！