Hive 中文分区展示乱码注释乱码问题

最新推荐文章于 2024-02-20 22:51:53 发布

唐予之_

最新推荐文章于 2024-02-20 22:51:53 发布

阅读量4.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： -----Hive 大数据

本文链接：https://blog.youkuaiyun.com/lxhandlbb/article/details/80943931

大数据同时被 2 个专栏收录

29 篇文章

订阅专栏

-----Hive

6 篇文章

订阅专栏

本文针对Hive中出现的中文注释乱码、中文分区名插入失败及查询结果乱码等问题，提供了一套详细的解决方案。通过修改元数据库字符集为UTF-8，并调整Hive配置，成功解决了中文乱码的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

遇到神一样的客户，遇到神一样的问题，用神一样的脚本来解决。

一、神问题：

1.中文注释乱码

例如:
hive> desc table;
OK
content                 string
hour                    string                  ？？

# Partition Information
# col_name              data_type               comment

hour                    string                  ？？

2.要求分区名是中文例如 dt=今天/city=杭州

不改编码，插入不进去。报错参考下面。

3.Select * from table 时,展示乱码

例如：

hive>
    > select * from table;
OK
工作流目录   ?????

可见这里本来两个都是中文结果分区字段显示乱码了。
而hdfs路径，和hive的mysql元数据库都没问题！！！

二、报错记录：

1:
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Transaction failed to commit)

2:

18/07/04 16:56:46 ERROR Hive: MetaException(message:Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MPartition' AS `NUCLEUS_TYPE`,`A0`.`CREATE_TIME`,`A0`.`LAST_ACCESS_TIME`,`A0`.`PART_NAME`,`A0`.`PART_ID` FROM `PARTITIONS` `A0` LEFT OUTER JOIN `TBLS` `B0` ON `A0`.`TBL_ID` = `B0`.`TBL_ID` LEFT OUTER JOIN `DBS` `C0` ON `B0`.`DB_ID` = `C0`.`DB_ID` WHERE `B0`.`TBL_NAME` = ? AND `C0`.`NAME` = ? AND `A0`.`PART_NAME` = ?)

三、神脚本：

-- 先use相应的 database。执行失败的地方略过继续往下执行

alter database hive_meta default character set utf8;
alter table BUCKETING_COLS default character set utf8;
alter table CDS default character set utf8;
alter table COLUMNS_V2 default character set utf8;
alter table DATABASE_PARAMS default character set utf8;
alter table DBS default character set utf8;
alter table FUNCS default character set utf8;
alter table FUNC_RU default character set utf8;
alter table GLOBAL_PRIVS default character set utf8;
alter table PARTITIONS default character set utf8;
alter table PARTITION_KEYS default character set utf8;
alter table PARTITION_KEY_VALS default character set utf8;
alter table PARTITION_PARAMS default character set utf8;
-- alter table PART_COL_STATS default character set utf8;
alter table ROLES default character set utf8;
alter table SDS default character set utf8;
alter table SD_PARAMS default character set utf8;
alter table SEQUENCE_TABLE default character set utf8;
alter table SERDES default character set utf8;
alter table SERDE_PARAMS default character set utf8;
alter table SKEWED_COL_NAMES default character set utf8;
alter table SKEWED_COL_VALUE_LOC_MAP default character set utf8;
alter table SKEWED_STRING_LIST default character set utf8;
alter table SKEWED_STRING_LIST_VALUES default character set utf8;
alter table SKEWED_VALUES default character set utf8;
alter table SORT_COLS default character set utf8;
alter table TABLE_PARAMS default character set utf8;
alter table TAB_COL_STATS default character set utf8;
alter table TBLS default character set utf8;
alter table VERSION default character set utf8;
alter table BUCKETING_COLS convert to character set utf8;
alter table CDS convert to character set utf8;
alter table COLUMNS_V2 convert to character set utf8;
alter table DATABASE_PARAMS convert to character set utf8;
alter table DBS convert to character set utf8;
alter table FUNCS convert to character set utf8;
alter table FUNC_RU convert to character set utf8;
alter table GLOBAL_PRIVS convert to character set utf8;
alter table PARTITIONS convert to character set utf8;
alter table PARTITION_KEYS convert to character set utf8;
alter table PARTITION_KEY_VALS convert to character set utf8;
alter table PARTITION_PARAMS convert to character set utf8;
-- alter table PART_COL_STATS convert to character set utf8;
alter table ROLES convert to character set utf8;
alter table SDS convert to character set utf8;
alter table SD_PARAMS convert to character set utf8;
alter table SEQUENCE_TABLE convert to character set utf8;
alter table SERDES convert to character set utf8;
alter table SERDE_PARAMS convert to character set utf8;
alter table SKEWED_COL_NAMES convert to character set utf8;
alter table SKEWED_COL_VALUE_LOC_MAP convert to character set utf8;
alter table SKEWED_STRING_LIST convert to character set utf8;
alter table SKEWED_STRING_LIST_VALUES convert to character set utf8;
alter table SKEWED_VALUES convert to character set utf8;
alter table SORT_COLS convert to character set utf8;
alter table TABLE_PARAMS convert to character set utf8;
alter table TAB_COL_STATS convert to character set utf8;
alter table TBLS convert to character set utf8;
alter table VERSION convert to character set utf8;
-- alter table PART_COL_STATS convert to character set utf8;
SET character_set_client = utf8 ;
-- SET character_set_connection = utf8 ;

-- alter table PART_COL_STATS convert to character set utf8;
SET character_set_database = utf8 ;
SET character_set_results = utf8 ;
SET character_set_server = utf8 ;
-- SET collation_connection = utf8 ;
-- SET collation_database = utf8 ;
-- SET collation_server = utf8 ;
SET NAMES 'utf8';
SET NAMES 'utf8';

四、解决后效果：

注释:

hive> desc test_table_xuehuan15;
OK
content                 string
hour                    string                  时间

# Partition Information
# col_name              data_type               comment

hour                    string                  时间

数据：


hive>
    > select * from table;
OK
工作流目录   工作流目录