[ 笔记列表 ]
所属分类:Hive
浏览:116
内容:
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服务,如果问题解决,就不需要重启。
链接:
|