解决hive表中文乱码问题

问题背景

使用的mysql版本：Server version: 5.7.31

现象描述：字段名称和字段描述等存储进如hivemetastore是中文，查询出来也是???等无法识别的中文乱码；

0: jdbc:hive2://hadoop03:10000/> show create table agnt_operation_log_big
. . . . . . . . . . . . . . . .> ;
+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `agnt_operation_log_big`(             |
|   `opt_menucount` string COMMENT '????',           |
|   `opt_menu` string COMMENT '??',                  |
|   `opt_time` string COMMENT '???????')             |
| PARTITIONED BY (                                   |
|   `inc_day` string COMMENT '??')                   |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'  |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' |
| LOCATION                                           |
|   'hdfs://hadoop02:8020/data/hive/warehouse/db1_fang.db/agnt_operation_log_big' |
| TBLPROPERTIES (                                    |
|   'transient_lastDdlTime'='1658129989')            |
+----------------------------------------------------+

中文乱码的情况分为两种情况：

• 新建hive集群防止中文乱码
• 已有的hive集群中文乱码的纠正

一、新建hive集群防止中文乱码

在构建hive开发环境的时候，需要实现构建号对应的hivemetastore依赖的mysql数据库和表

1. 创建mysql支持中文的数据库

-- 设置utf8的中文编码
CREATE DATABASE `hive_metastore_ljgk` /*!40100 DEFAULT CHARACTER SET utf8 */
-- 或者
CREATE DATABASE `hive_metastore_ljgk` DEFAULT CHARACTER SET utf8 COLLATE utf_general_ci;

1. 如下是整个hivemetastore以来的mysql的所有表的sql脚本，里面已经改变支持中文，例如支持中文表描述，字段描述等（hive_metastore_ljgk.sql脚本）；例如如下，其中已经将编码改变为utf8:

----------------------------
-- Table structure for CDS
-- ----------------------------
DROP TABLE IF EXISTS `CDS`;
CREATE TABLE `CDS`  (
  `CD_ID` bigint(20) NOT NULL,
  PRIMARY KEY (`CD_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 

1. 将设置好的hivemetastore以来的mysql的hive_metastore_ljgk.sql脚本，执行并导入到mysql的hive_metastore_ljgk库中：

mysql -h 172.18.8.77 -P 3306 -u root -p xxxxxxxxxxxxx -D hive_metastore_ljgk < /home/fangct/hive_metastore_ljgk.sql

1. 全部设置全了之后 ，重启hivemetastore服务和hiveserve2服务(hiveserve2应该不用重启);
2. 验证是否支持中文生效

在hive sql命令行中（此处是beeline），执行hivesql

示例：

create table agnt_operation_log_big
(
 opt_menucount string comment '统计数据',
 opt_menu      string comment '菜单',
 opt_time      string comment '日志搜集表日期'
)PARTITIONED by (inc_day string comment '分区') ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat';

显示所有的hive 创建表字段信心

0: jdbc:hive2://hadoop03:10000/> show create table agnt_operation_log_big;
+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `agnt_operation_log_big`(             |
|   `opt_menucount` string COMMENT '统计数据',           |
|   `opt_menu` string COMMENT '菜单',                  |
|   `opt_time` string COMMENT '日志搜集表日期')             |
| PARTITIONED BY (                                   |
|   `inc_day` string COMMENT '分区')                   |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'  |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' |
| LOCATION                                           |
|   'hdfs://hadoop02:8020/data/hive/warehouse/db_fang.db/agnt_operation_log_big' |
| TBLPROPERTIES (                                    |
|   'transient_lastDdlTime'='1658130710')            |
+----------------------------------------------------+

如上所示，hive表中字段的comment已经支持中文正常显示；

注意事项：hive不支持中文表名称，仅仅支持中文字段描述，中文表的描述信息；如下所示

create table if not exists dwd_user (
cookie string COMMENT 'cookie',
country string COMMENT '国家',
province string COMMENT '省',
city string  COMMENT '城市'
) COMMENT '埋点中用户信息筛选'
partitioned by (everyday string)
row format delimited 
fields terminated by '\001'
lines terminated by '\n'
stored as orc;

二、已有的hive集群中文乱码的纠正

网上已有现成案例(未验证)：

hive中文乱码解决方法

hive中解决中文乱码

hive 表支持中文设置

注意mysql中字符定义规则：

mysql中共有4种级别的字符集编码的设置：Mysql Server级别、 数据库级别、表级别、列级别。

如果要支持中文，最好是构建集群的时候，就将mysql服务器级别设置成支持中文格式的服务器级别的；例如utf8