hive乱码问题完全解决方案

修改数据库编码集
SET GLOBAL character_set_client = utf8;
SET GLOBAL character_set_connection = utf8;
SET GLOBAL character_set_database = utf8;
SET GLOBAL character_set_results = utf8;
SET GLOBAL character_set_server = utf8;

show variables like ‘character_set%’;
show create database hive;

alter database hive character set latin1;

FLUSH PRIVILEGES;

修改元数据的编码
A:修改字段注释和表注解
alter table COLUMNS_V2 modify column COMMENT varchar(255) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(255) character set utf8;
B:修改分区字段注解
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(255) character set utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(255) character set utf8;
C:修改索引注解
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(255) character set utf8;
#修改视图，支持视图中文
ALTER TABLE TBLS modify COLUMN VIEW_EXPANDED_TEXT mediumtext CHARACTER SET utf8;
ALTER TABLE TBLS modify COLUMN VIEW_ORIGINAL_TEXT mediumtext CHARACTER SET utf8;

show create table COLUMNS_V2;
show create table TABLE_PARAMS;
show create table PARTITION_PARAMS;
show create table PARTITION_KEYS;
show create table INDEX_PARAMS;

FLUSH PRIVILEGES;

在hive-site.xml里面的mysql jdbc配置中增加?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8

javax.jdo.option.ConnectionURL
jdbc:mysql://re****ger001:3306/hive?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8

重启hive