Hive中desc和show create table中文注释乱码解决方案

Hive中desc和show create table中文注释乱码解决方案

1.修改hive-site.xml

<property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://你的IP:3306/hive?createDatabaseIfNotExsit=true&amp;characterEncoding=UTF-8</value>
</property>

2.解决desc命令注释中文乱码

修改mysql元数据相关信息

2-1.修改字段注释字符集

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

2-2.修改表注释字符集

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

2-3.修改分区表参数支持中文表示

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;

2-4.修改索引注解

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

2-5.验证desc中文显示

新建一张表newtable

CREATE EXTERNAL TABLE `newtable`(
`total_time` double COMMENT '总时间'
)PARTITIONED BY (
  `dt` string COMMENT '日')
row format delimited fields terminated by '\t'
STORED AS textfile;

mysql> select * from COLUMNS_V2;

发现新建的表的注释都是中文了

hive>desc table newtable

发现中文显示正常
但此时若执行

show create table newtable

注释中的中文还是乱码的，下面我们进一步解决该问题

3.解决Hive show create table中文乱码

下载Hive源码，如hive2.1.1。
下载地址：http://mirrors.shu.edu.cn/apache/hive/hive-2.1.1/

3-1.修改hive源码中org.apache.hadoop.hive.ql.exec.DDLTask类

outStream.writeBytes(createTab_stmt.toString());

改成

outStream.write(createTab_stmt.toString().getBytes(“UTF-8”));

同时将

outStream.writeBytes(createTab_stmt.render());

改成

outStream.write(createTab_stmt.render().getBytes(“UTF-8”));

3-2执行如下指令重新编译打包

mvn install -DskipTests -Dcheckstyle.skip=true
mvn clean package -Phadoop-2 -Pdist -DskipTests -Dtar

执行打包指令时

mvn clean package -Phadoop-2 -Pdist -DskipTests -Dtar

log中有如下日志

[INFO] Copying hive-jdbc-2.1.1-standalone.jar to /你的目录/hive/apache-hive-2.1.1-src/packaging/target/apache-hive-2.1.1-jdbc.jar

指明了打包好的jar的路径
解压apache-hive-2.1.1-jdbc.jar

tar -zxf apache-hive-2.1.1-jdbc.jar /目标临时目录

3-3将其中lib目录下的hive-exec-2.1.1.jar 替换hive安装目录lib下的该文件

此时重新执行show create table 中文正常显示了

show create table newtable

至此解决了Hive中desc和show create table中文注释乱码问题
点击下载-编译后的hive-exec-2.1.1.jar

参考

• https://cwiki.apache.org/confluence/display/Hive/GettingStarted
• http://blog.csdn.net/m0_37692438/article/details/73225002
• https://issues.apache.org/jira/browse/HIVE-11837
• https://issues.apache.org/jira/secure/attachment/12791019/HIVE-11837.1.patch
• http://blog.csdn.net/duck_genuine/article/details/50896532