Hive字符集不匹配解决方案

**

Hive字符集不匹配解决方案

**

#hive-mysql字符编码错乱问题
#/etc/my.cnf添加下列字段 然后 service mysqld restart
init_connect=‘SET collation_connection = utf8_unicode_ci’
init_connect=‘SET NAMES utf8’
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

强调的一点:hive的元数据metastore在mysql的数据库,不管是数据库本身,还是里面的表编码都必须是latin1(CHARACTER SET latin1 COLLATE latin1_bin)!!!
#hive-site.xml里面的路径 useUnicode=true&haracterEncoding=UTF-8 加入字符码的设定


javax.jdo.option.ConnectionURL
jdbc:mysql://hadoop3:3306/hive?useUnicode=true&haracterEncoding=UTF-8&useSSL=false

#mysql执行如下过程

#执行下面的大命令后
alter table sds change LOCATION LOCATION CHARACTER SET utf8 COLLATE utf8_general_ci;
alter table HIVE_LOCKS change HL_PARTITION HL_PARTITION CHARACTER SET utf8 COLLATE utf8_general_ci;

#先执行一波大命令
ALTER TABLE AUX_TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE BUCKETING_COLS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE CDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE COLUMNS_V2 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE COMPACTION_QUEUE CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE COMPLETED_COMPACTIONS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE COMPLETED_TXN_COMPONENTS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE DATABASE_PARAMS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE DBS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE DB_PRIVS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE DELEGATION_TOKENS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE FUNCS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE FUNC_RU CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE GLOBAL_PRIVS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE HIVE_LOCKS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE IDXS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE INDEX_PARAMS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE KEY_CONSTRAINTS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE MASTER_KEYS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE NEXT_COMPACTION_QUEUE_ID CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE NEXT_LOCK_ID CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE NEXT_TXN_ID CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE NOTIFICATION_LOG CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE NOTIFICATION_SEQUENCE CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE NUCLEUS_TABLES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE PARTITIONS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE PARTITION_EVENTS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE PARTITION_KEYS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE PARTITION_KEY_VALS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE PARTITION_PARAMS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE PART_PRIVS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE ROLES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE ROLE_MAP CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SD_PARAMS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SEQUENCE_TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SERDES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SERDE_PARAMS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SKEWED_COL_NAMES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SKEWED_COL_VALUE_LOC_MAP CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SKEWED_STRING_LIST CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SKEWED_STRING_LIST_VALUES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SKEWED_VALUES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE SORT_COLS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TABLE_PARAMS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TAB_COL_STATS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TBLS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TBL_PRIVS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TXNS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TXN_COMPONENTS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TYPES CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TYPE_FIELDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE VERSION CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE WRITE_SET CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
再执行下述代码即可
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;