Hive从入门到放弃——Hive中文乱码的问题（七）

╭⌒若隐_RowYet——大数据

已于 2022-06-20 15:58:56 修改

阅读量2.5k

点赞数 1

分类专栏： Hadoop Hive 文章标签： hive

于 2020-06-04 18:45:59 首次发布

本文链接：https://blog.csdn.net/LXWalaz1s1s/article/details/106550672

版权

Hadoop Hive 专栏收录该内容

23 篇文章 6 订阅

订阅专栏

文章目录

背景

实际开发时，建表的时候，身为中国人，为了方便一般都会给字段加上中文的`comment,来方便记录字段的中文意思，如下sql建表语句；

CREATE EXTERNAL TABLE ods_tbb_movie(
userID int comment '用户ID',
movieID int comment '电影ID',
rating int comment '电影评分',
timestamped bigint comment '评分时间戳',
movieName string comment '电影名字',
movieType string comment '电影类型',
sex string comment '性别',
age int comment '年龄',
occupation string comment '职业',
zipcode string comment '邮政编码'
) 
COMMENT '影评三表合一'
PARTITIONED BY ( 
  `event_week` int, 
  `event_day` string, 
  `event_hour` string)
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
  '/hive/warehouse/ods/ods_tbb_movie'
TBLPROPERTIES ('parquet.compression'='snappy')

但是在查看表结构的字段中文comment时时，竟然出现了中文乱码，如图1，这是什么情况呢？
在这里插入图片描述

图1 查看表字段的comment时显示中文乱码

原因分析

通过博客Hive从入门到放弃——Hive元数据简介（六）,我们知道，其实Hive表的这些描述信息，都是存在MySQL的元数据库hive里面的，如字段的comment描述，就是对应表COLUMNS_V2的COMMENT字段，而在Hive默认的该表元数据建表语句用的字符集和字符比较排序规则分别是CHARACTER SET latin1 COLLATE latin1_bi,默认建表DDL SQL如下；

CREATE TABLE IF NOT EXISTS `COLUMNS_V2` (
  `CD_ID` bigint(20) NOT NULL,
  `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `TYPE_NAME` MEDIUMTEXT DEFAULT NULL,
  `INTEGER_IDX` int(11) NOT NULL,
  PRIMARY KEY (`CD_ID`,`COLUMN_NAME`),
  KEY `COLUMNS_V2_N49` (`CD_ID`),
  CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

而字符集latin1 在MySQL上对中文的支持不是很好，容易出现乱码，这就是根本原因，那只要把相关字段的字符集修改掉就行了，改成CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 或CHARACTER SET utf8 COLLATE utf8_bin；
扩展：MySQL凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci。
mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行,例如，utf8mb4能识别字段内① 和1的区别，会认为是两个不同的字符,而utf8搞不定，认为是同一个。

解决方式

分析好了原因，就好对症下药了，我们只要改掉存在这些中文的字段或者表的字符集类型就行了，个人提供以下两种思路；

先查看元数据库mysql本身字符集是否正常

以下语句查看字符集，一定要是utf8或者utf8mb4类的字符集，如果不是，请修改你的mysql配置文件
/etc/my.cnf，如character_set_server不是utf8话则将/etc/my.cnf下 [client]新增default-character-set=utf8;

mysql> show variables like 'char%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8                                                      |
| character_set_connection | utf8                                                      |
| character_set_database   | utf8                                                      |
| character_set_filesystem | binary                                                    |
| character_set_results    | utf8                                                      |
| character_set_server     | utf8                                                      |
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-5.7.31-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.00 sec)

直接修改Hive元数据建表语句字符集(适合Hive安装前)

如果你只是在UAT发现了这个问题，PROD的Hive还没来及装，为了克服这个问题，你可以考虑直接改掉Hive元数据的建表scripts，准确的来说是在没有初始化Hive，执行shellschematool -dbType mysql -initSchema前都来的急（详情可以参考Hive的安装博客Hive从入门到放弃——Hive安装和基本使用（二））；

常用的使用MySQL作为元数据库的做法，那么hive的元数据初始化建表语句在apache-hive-2.3.5-bin/scripts/metastore/upgrade/mysql目录下，具体文件如下；

total 872
-rw-r--r-- 1 hadoop supergroup   157 Sep 27  2018 001-HIVE-972.mysql.sql
-rw-r--r-- 1 hadoop supergroup   149 Sep 27  2018 002-HIVE-1068.mysql.sql
-rw-r--r-- 1 hadoop supergroup   193 Sep 27  2018 003-HIVE-675.mysql.sql
-rw-r--r-- 1 hadoop supergroup   827 Sep 27  2018 004-HIVE-1364.mysql.sql
-rw-r--r-- 1 hadoop supergroup  1396 Sep 27  2018 005-HIVE-417.mysql.sql
-rw-r--r-- 1 hadoop supergroup   505 Sep 27  2018 006-HIVE-1823.mysql.sql
-rw-r--r-- 1 hadoop supergroup  6587 Sep 27  2018 007-HIVE-78.mysql.sql
-rw-r--r-- 1 hadoop supergroup  6592 Sep 27  2018 008-HIVE-2246.mysql.sql
-rw-r--r-- 1 hadoop supergroup   737 Sep 27  2018 009-HIVE-2215.mysql.sql
-rw-r--r-- 1 hadoop supergroup  2314 Sep 27  2018 010-HIVE-3072.mysql.sql
-rw-r--r-- 1 hadoop supergroup   459 Sep 27  2018 011-HIVE-3649.mysql.sql
-rw-r--r-- 1 hadoop supergroup  2280 Sep 27  2018 012-HIVE-1362.mysql.sql
-rw-r--r-- 1 hadoop supergroup   664 Sep 27  2018 013-HIVE-3255.mysql.sql
-rw-r--r-- 1 hadoop supergroup   411 Sep 27  2018 014-HIVE-3764.mysql.sql
-rw-r--r-- 1 hadoop supergroup   159 Sep 27  2018 016-HIVE-6386.mysql.sql
-rw-r--r-- 1 hadoop supergroup  1147 Sep 27  2018 017-HIVE-6458.mysql.sql
-rw-r--r-- 1 hadoop supergroup   698 Sep 27  2018 018-HIVE-6757.mysql.sql
-rw-r--r-- 1 hadoop supergroup  1309 Sep 27  2018 019-HIVE-7784.mysql.sql
-rw-r--r-- 1 hadoop supergroup   536 Sep 27  2018 020-HIVE-9296.mysql.sql
-rw-r--r-- 1 hadoop supergroup  1472 Sep 27  2018 021-HIVE-7018.mysql.sql
-rw-r--r-- 1 hadoop supergroup   679 Nov  1  2018 022-HIVE-11970.mysql.sql
-rw-r--r-- 1 hadoop supergroup    63 Nov  1  2018 023-HIVE-12807.mysql.sql
-rw-r--r-- 1 hadoop supergroup    67 Nov  1  2018 024-HIVE-12814.mysql.sql
-rw-r--r-- 1 hadoop supergroup    67 Nov  1  2018 025-HIVE-12816.mysql.sql
-rw-r--r-- 1 hadoop supergroup   446 Nov  1  2018 026-HIVE-12818.mysql.sql
-rw-r--r-- 1 hadoop supergroup    54 Nov  1  2018 027-HIVE-12819.mysql.sql
-rw-r--r-- 1 hadoop supergroup   105 Nov  1  2018 028-HIVE-12821.mysql.sql
-rw-r--r-- 1 hadoop supergroup    53 Nov  1  2018 029-HIVE-12822.mysql.sql
-rw-r--r-- 1 hadoop supergroup    59 Nov  1  2018 030-HIVE-12823.mysql.sql
-rw-r--r-- 1 hadoop supergroup   118 Nov  1  2018 031-HIVE-12831.mysql.sql
-rw-r--r-- 1 hadoop supergroup   185 Nov  1  2018 032-HIVE-12832.mysql.sql
-rw-r--r-- 1 hadoop supergroup   645 Nov  1  2018 034-HIVE-13076.mysql.sql
-rw-r--r-- 1 hadoop supergroup   321 Nov  1  2018 035-HIVE-13395.mysql.sql
-rw-r--r-- 1 hadoop supergroup   135 Nov  1  2018 036-HIVE-13354.mysql.sql
-rw-r--r-- 1 hadoop supergroup   321 May  7  2019 037-HIVE-14496.mysql.sql
-rw-r--r-- 1 hadoop supergroup   340 Nov  1  2018 038-HIVE-10562.mysql.sql
-rw-r--r-- 1 hadoop supergroup  1508 May  3  2019 039-HIVE-12274.mysql.sql
-rw-r--r-- 1 hadoop supergroup    58 Nov  1  2018 040-HIVE-16399.mysql.sql
-rw-r--r-- 1 hadoop supergroup 31962 Sep 27  2018 hive-schema-0.10.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 31962 Sep 27  2018 hive-schema-0.11.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 32920 Sep 27  2018 hive-schema-0.12.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 36889 Sep 27  2018 hive-schema-0.13.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 36764 Sep 27  2018 hive-schema-0.14.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 12322 Sep 27  2018 hive-schema-0.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 12322 Sep 27  2018 hive-schema-0.4.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 12322 Sep 27  2018 hive-schema-0.4.1.mysql.sql
-rw-r--r-- 1 hadoop supergroup 13565 Sep 27  2018 hive-schema-0.5.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 13798 Sep 27  2018 hive-schema-0.6.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 24716 Sep 27  2018 hive-schema-0.7.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 25914 Sep 27  2018 hive-schema-0.8.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 25914 Sep 27  2018 hive-schema-0.9.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 35192 Sep 27  2018 hive-schema-1.1.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 35192 Sep 27  2018 hive-schema-1.2.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 34845 Nov  1  2018 hive-schema-1.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 34845 Nov  1  2018 hive-schema-2.0.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 35488 Nov  1  2018 hive-schema-2.1.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 35543 May  7  2019 hive-schema-2.2.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup 35543 May  7  2019 hive-schema-2.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  2845 Sep 27  2018 hive-txn-schema-0.13.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  2845 Sep 27  2018 hive-txn-schema-0.14.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  4139 Nov  1  2018 hive-txn-schema-1.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  3770 Nov  1  2018 hive-txn-schema-2.0.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  4165 Nov  1  2018 hive-txn-schema-2.1.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  4165 Nov  1  2018 hive-txn-schema-2.2.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  4224 Nov  1  2018 hive-txn-schema-2.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup  5332 Sep 27  2018 README
-rw-r--r-- 1 hadoop supergroup   141 Sep 27  2018 upgrade-0.10.0-to-0.11.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   311 Sep 27  2018 upgrade-0.11.0-to-0.12.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   386 Sep 27  2018 upgrade-0.12.0-to-0.13.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   282 Sep 27  2018 upgrade-0.13.0-to-0.14.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   278 Sep 27  2018 upgrade-0.14.0-to-1.1.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   271 Sep 27  2018 upgrade-0.5.0-to-0.6.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   230 Sep 27  2018 upgrade-0.6.0-to-0.7.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   201 Sep 27  2018 upgrade-0.7.0-to-0.8.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   137 Sep 27  2018 upgrade-0.8.0-to-0.9.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   235 Sep 27  2018 upgrade-0.9.0-to-0.10.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   243 Sep 27  2018 upgrade-1.1.0-to-1.2.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   703 Nov  1  2018 upgrade-1.2.0-to-1.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   638 Nov  1  2018 upgrade-1.2.0-to-2.0.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   343 Nov  1  2018 upgrade-2.0.0-to-2.1.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   343 Nov  1  2018 upgrade-2.1.0-to-2.2.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   277 Nov  1  2018 upgrade-2.2.0-to-2.3.0.mysql.sql
-rw-r--r-- 1 hadoop supergroup   235 May  7  2019 upgrade.order.mysql

这么多？别慌，其实你只要用到对应版本的两个文件hive-schema-2.3.0.mysql.sql,hive-txn-schema-2.3.0.mysql.sql，把里面的57张元数据表涉及到的字符集做修改；步骤如下；

CHARACTER SET latin1 COLLATE latin1_bin 改成CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 或CHARACTER SET utf8 COLLATE utf8_bin
CHARACTER SET latin1 改成CHARSET=utf8mb4或CHARACTER SET utf8
然后再执行Hive的安装，确保初始化shellschematool -dbType mysql -initSchema元数据建表顺利即可；
如果通过jdbc连接乱码异常的，可以检查下自己hive的配置文件hive-site.xmljdbc连接时的指定字符串编码是否准确，以utf8为例子如下，改了最好重启下Hive service；

<!--hive的元数据库连接配置-->
<property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEnconding=UTF-8</value>
</property>

修改建好的表或字段字符集(适合Hive安装完成)

针对生米已经煮成熟饭的情况，就是Hive已经投产使用，突然发现某一天要用到中文时，出现了中文乱码，其实我们大多数情况下遇到的也是这种情况啦；这个时候就要更有针对性的对存储你遇到的中文乱码的元数据字段进行字符集修改了，其实原理都一样，那我们列举一些常用元数据字段容易导致中文乱码的，一样的CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 或CHARACTER SET utf8 COLLATE utf8_bin都行，CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 较优，具体操作如下；

--修改表字段注释支持中文字符集
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8 COLLATE utf8_bin;

-- 修改表注释支持中文字符集
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 COLLATE utf8_bin;

-- 修改分区表的属性支持中文
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 COLLATE utf8_bin;

-- 修改分区字段的描述支持中文
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8 COLLATE utf8_bin;

-- 修改分区字段的值支持中文
alter table PARTITION_KEY_VALS  modify column PART_KEY_VAL varchar(256) character set utf8 COLLATE utf8_bin;

-- 修改索引注释支持中文
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 COLLATE utf8_bin;

-- 修改数据库中文描述
alter table DBS modify column `DESC` varchar(4000) character set utf8 COLLATE utf8_unicode_ci;

以上就是列举的常见的出现中文乱码的Hive元数据字符集调整，授人以渔的话，建议可以看看博客Hive从入门到放弃——Hive元数据简介（六）,只要对元数据的结构分布了解了，那你就可以为所欲为的按照你的设想来修改了这些字符集了，彻底解决这些中文乱码问题；
一样，如果通过jdbc连接乱码异常的，可以检查下自己hive的配置文件hive-site.xmljdbc连接时的指定字符串编码是否准确，以utf8为例子如下，改了最好重启下Hive service；

<!--hive的元数据库连接配置-->
<property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEnconding=UTF-8</value>
</property>

╭⌒若隐_RowYet——大数据

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
Hive从入门到放弃——Hive中文乱码的问题（七）

背景实际开发时，建表的时候，身为中国人，为了方便一般都会给字段加上中文的`comment,来方便记录字段的中文意思，如下sql建表语句；CREATE EXTERNAL TABLE ods_tbb_movie(userID int comment '用户ID',movieID int comment '电影ID',rating int comment '电影评分',timestamped bigint comment '评分时间戳',movieName string comment '电影
复制链接

扫一扫