标签:Hive
浏览:60
内容:


1、

确定hive元数据的存储位置,登录后做以下检查,发现字符集 CHARACTER SET latin1 COLLATE latin1_bin ,就是原因所在。


***** 表字段注解 *****

mysql> show create table columns_v2 ;

mysql> 

CREATE TABLE `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,

  `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



*****  表注解  *****

mysql> show create table table_params ;

mysql> 

CREATE TABLE `table_params` (

  `TBL_ID` bigint(20) NOT NULL,

  `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

  `PARAM_VALUE` mediumtext CHARACTER SET latin1 COLLATE latin1_bin,

  PRIMARY KEY (`TBL_ID`,`PARAM_KEY`),

  KEY `TABLE_PARAMS_N49` (`TBL_ID`),

  CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`tbl_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1



*****  分区字段注解  *****

mysql> show create table partition_keys ;

mysql> 

CREATE TABLE `partition_keys` (

  `TBL_ID` bigint(20) NOT NULL,

  `PKEY_COMMENT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,

  `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

  `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

  `INTEGER_IDX` int(11) NOT NULL,

  PRIMARY KEY (`TBL_ID`,`PKEY_NAME`),

  KEY `PARTITION_KEYS_N49` (`TBL_ID`),

  CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`tbl_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1



*****  分区字段注解  *****

mysql> show create table partition_params ;

mysql> 

CREATE TABLE `partition_params` (

  `PART_ID` bigint(20) NOT NULL,

  `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

  `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,

  PRIMARY KEY (`PART_ID`,`PARAM_KEY`),

  KEY `PARTITION_PARAMS_N49` (`PART_ID`),

  CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`part_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1



*****  索引注解  *****

mysql> show create table index_params ;

mysql> 

CREATE TABLE `index_params` (

  `INDEX_ID` bigint(20) NOT NULL,

  `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

  `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,

  PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`),

  KEY `INDEX_PARAMS_N49` (`INDEX_ID`),

  CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `idxs` (`index_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1


2、

修改相关表字段的字符集


** 修改表字段注解

mysql> alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;


** 修改表注解

mysql> alter table TABLE_PARAMS modify column PARAM_VALUE  mediumtext  character set utf8;


** 修改分区字段注解

mysql> alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

mysql> alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;


** 修改索引注解

mysql> alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;


3、

再进行第1步检查,检查相关字段的字符集是否已经修改。


4、

修改 metastore 的连接 URL,添加 &useUnicode=true&characterEncoding=UTF-8 


<property>

      <name>javax.jdo.option.ConnectionURL</name>

      <value>jdbc:mysql://localhost:3306/hive_metadata_db?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8</value>

</property>


5、

重启hive服务,如果问题解决,就不需要重启。