背景：最近一直在做基于Hive的离线数据仓库的构建，随着仓库的初具规模，发现在使用过程往往会有同事发现hive表的英文注释并不容易理解，因此想着能够使用中文进行hive表的元数据管理，但在时间过程中也发现了一些中文乱码的问题，在这里进行笔记。
问题现象
hive (default)> CREATE TABLE `t2`(
`id` int COMMENT 'id',
`name` string COMMENT '名称');
hive (default)> desc formatted t2;
OK
# col_name data_type comment
id int id
name string ????
为什么不直接使用utf8
由于Hive使用关系型数据库MySQL进行管理元数据，而Hive表在存储过程中由于字符长度的问题，默认使用了latin1字符集，这个时候，我们千万不能为了修改中文乱码的问题，而把全部的库、表、行都改成utf-8，否则会有意外的惊喜。
分析排查
知道了为什么存在中文乱码的问题后，我们就可以根据以下几点逐一排查。
- 数据库本身相关环境的默认字符集是否为utf8
- 业务数据库的库表行使用的默认字符集
- 客户端连接默认使用的字符集
检查MySQL环境
mysql> \s;
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 396097
Current database:
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.28 MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
....
mysql> show variables like "%collation%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
由上面信息知道，当前MySQL实例默认的字符集就设置成了latin1，如果想要修改默认字符集，可以通过修改MySQL的配置文件，并重启完成。
# 修改MySQL配置文件/etc/my.cnf 并重启MySQL
[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
[client]
default-character-set=utf8
排查业务数据库相关的字符集
mysql> show create database hive;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| hive | CREATE DATABASE `hive` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
mysql> show create table hive.SDS\G;
*************************** 1. row ***************************
Table: SDS
Create Table: CREATE TABLE `SDS` (
`SD_ID` bigint(20) NOT NULL,
`CD_ID` bigint(20) DEFAULT NULL,
`INPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`IS_COMPRESSED` bit(1) NOT NULL,
`IS_STOREDASSUBDIRECTORIES` bit(1) NOT NULL,
`LOCATION` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`NUM_BUCKETS` int(11) NOT NULL,
`OUTPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`SERDE_ID` bigint(20) DEFAULT NULL,
PRIMARY KEY (`SD_ID`),
KEY `SDS_N50` (`SERDE_ID`),
KEY `SDS_N49` (`CD_ID`),
CONSTRAINT `SDS_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`),
CONSTRAINT `SDS_FK2` FOREIGN KEY (`SERDE_ID`) REFERENCES `SERDES` (`SERDE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
由上面输出可以知道，hive库以及库中的相关表均使用latin1作为默认的字符集，因此，可以针对需要支持中文的地方进行修改默认字符集即可。
排查客户端连接(JDBC)默认字符集
# 查看hive的JDBC连接串
$ cat hive/conf/hive-site.xml | grep mysql
<value>jdbc:mysql://10.0.0.1:3306/hive?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8</value>
<value>com.mysql.jdbc.Driver</value>
由上面输出可以看到，客户端的JDBC连接串是使用的utf8编码。
修复元数据中文乱码
从以上三个排查过程中发现，数据库默认字符集和库表字符集均是latin1，那我们知道作为hive的元数据存储一般只有库、表、行的注释部分才可能会有中文字符，因此，为了避免对过多的库表进行字符集设置，我们仅需要对相关库、表、行注释部分进行设置utf8字符即可。
# 修改表字段和表注释
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8
# 修改分区字段注释
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;
# 修改索引注释
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
修改完成后，我们重新来创建一张表:
# hive建表，查看注释
hive (default)> create table t3(name string comment '名字') comment '测试表';
OK
hive (default)> desc formatted t3;
OK
# col_name data_type comment
name string 名字
# MySQL查看相关字符集
mysql> select * from COLUMNS_V2 where CD_ID > 1001;
+-------+---------+-------------+-----------+-------------+
| CD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX |
+-------+---------+-------------+-----------+-------------+
| 1006 | 名字 | name | string | 0 |
+-------+---------+-------------+-----------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM TABLE_PARAMS WHERE TBL_ID >920;
+--------+-----------------------+-------------+
| TBL_ID | PARAM_KEY | PARAM_VALUE |
+--------+-----------------------+-------------+
| 921 | comment | 测试表 |
| 921 | transient_lastDdlTime | 1548340084 |
+--------+-----------------------+-------------+
至此，我们的Hive表已经支持中文元数据注释了。
结语
虽然我们临时修改了表的字符集解决了几个问题，但是我们仍然需要注意一下几个问题：
- 在国内，生产环境中，我们的
MySQL数据库通常要提前设置成utf8的字符集，以避免后期的字典维护的问题 - 建议在Hive在刚开始使用时就将相关库表字符集改成
utf8，否则在改之前的数据依然是乱码的 - 如果在中途修改了字符集，如果需要将历史数据进行中文字符集修改，需要进行库表重建(或者直接修改表注释)
alter table t1 CHANGE COLUMN name name string comment '名称';
# 修改完之后立刻生效
注意:如果源表已经存在乱码的comment，通过上述语句修改表描述并不能直接修改为中文注释，此时需要将表重建，进行重新生成元数据，如果源表为外部分区表，直接将分区导入即可，如果是普通管理表，需要重新导入数据
# 外部分区表重建过程
drop table host;
create
external
table if not exists dwd.host (
ip string comment '主机地址',
appname string comment '主机关联应用名',
ip_state string comment '主机状态(资源分配|在线|下线)',
group string comment '主机所在应用的分组',
envtype string comment '主机所在应用的环境类型',
idc string comment '主机所属机房')
PARTITIONED BY (dt string)
stored as ORC;
desc dwd.fact_sure_host;
# 数据导入(需要提前查找表的分区，并按照分区导入)
alter table dwd.host add partition(dt='20190106');
alter table dwd.host add partition(dt='20190105');
alter table dwd.host add partition(dt='20190104');
网友评论