解决hive元数据库乱码问题

有两种方案，方案一可以一次性永久解决乱码问题；如果只针对当前数据库，推荐使用方案二，不会影响其他库。

解决方案一：

1、查看字符集：

MySQL [hive]> use hive;
MySQL [hive]> 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/share/mysql/charsets/ |
+--------------------------+----------------------------+

2、设置hive 元数据库字符集

character_set_database 不是 utf8
设置为utf8
alter database hive character set utf8;

3、 修改服务端的编码格式为 utf8

character_set_server 不是 utf8
设置为utf8
编辑my.cnf文件，在[mysqld]下添加一行character_set_server = utf8，然后重启mysql服务，再去查询字符集

4、更改如下表字段为字符集编码为 utf8

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_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;

解决方案二：

1、修改hive-site.xml配置文件

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://IP:3306/db_name?useUnicode=true&characterEncoding=UTF-8</value>
    <description>JDBC connect string for a JDBC metastore</description>
</property>

添加useUnicode=true&characterEncoding=UTF-8 的作用：指定字符的编码、解码格式。

例如：mysql数据库默认用的是latin1编码，而项目数据库用的是utf-8编码。这时候如果添加了useUnicode=true&characterEncoding=UTF-8 ，那么作用有如下两个方面：

存数据时：

数据库在存放项目数据的时候会先用UTF-8格式将数据解码成字节码，然后再将解码后的字节码重新使用latin1编码存放到数据库中。

取数据时：

在从数据库中取数据的时候，数据库会先将数据库中的数据按latin1格式解码成字节码，然后再将解码后的字节码重新按UTF-8格式编码数据，最后再将数据返回给客户端。

2、更改如下表字段为字符集编码为 utf8

MySQL [hive]>  show create table COLUMNS_V2 \G;
*************************** 1. row ***************************
       Table: COLUMNS_V2
Create Table: CREATE TABLE `COLUMNS_V2` (
  `CD_ID` bigint(20) NOT NULL,
  `COMMENT` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
  `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `TYPE_NAME` mediumtext,
  `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

如果 CHARSET=latin1，则需要做以下设置：

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_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;

3、如果在Beeline中汉字展示为乱码，或直接在Beeline中输入汉字为乱码

则在hive-env.sh文件中添加如下设置，然后重启hiveserver2服务
export HADOOP_OPTS="$HADOOP_OPTS -Dfile.encoding=UTF-8"

总结分析

1、jdbc不设置characterEncoding=utf8，服务端character_set_server为latin1，jdbc以latin1字符集连接数据库，数据库服务端将latin1转成utf8然后存储到磁盘（因为character_set_connection和创建表指定的字符集都是utf8），这时就造成了乱码
2、jdbc不设置characterEncoding=utf8，服务端character_set_server为utf8，或者jdbc设置characterEncoding=utf8，jdbc都以utf8字符集连接数据库，数据库服务端以utf8存储到磁盘，这时数据就是正常的