hive分区字段含中文导致的报错

最新推荐文章于 2024-11-04 15:24:05 发布

一定要努力努力再努力

最新推荐文章于 2024-11-04 15:24:05 发布

阅读量2.2k

点赞数 2

文章标签： hive mysql 数据库

本文链接：https://blog.csdn.net/weixin_42123844/article/details/121080353

版权

hive创建动态分区的时候，如果分区中有中文名会报如下错误：

Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.io.InterruptedIOException: Call interrupted
        at org.apache.hadoop.hive.ql.metadata.Hive.needToCopy(Hive.java:3073)
        at org.apache.hadoop.hive.ql.metadata.Hive.moveFile(Hive.java:2905)

原因是分区表的编码和全局编码不相同造成的
方案一：修改MySQL配置
临时修改：进入MySQL中设置参数

set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_results = utf8;
 
SET collation_server = utf8_general_ci
SET collation_database = utf8_general_ci

永久修改：修改mysql配置文件后重启

[root@ambari03 etc] vim /etc/my.cnf
# [client]下添加如下内容
[client]
default-character-set=utf8
# [mysqld]下添加如下内容
[mysqld]
default-character-set=utf8
init_connect='SET NAMES utf8'
# [mysql]下添加如下内容
[mysql]
default-character-set=utf8

方案二：修改mysql中Hive元数据库各个表的编码
进入mysql后，执行以下语句修改hive元数据表的编码信息

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';

修改后能够插入带有中文分区的表格，但是插入后虽然可以查询到数据已经插入，Hive仍然会报错

查看日志后定位到如下错误

2021-02-23T10:54:26,249 ERROR [HiveServer2-Background-Pool: Thread-248] exec.StatsTask: Failed to run stats task
org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Insert of object "org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics@710c136f" using statement "INSERT INTO `PART_COL_STATS` (`CS_ID`,`AVG_COL_LEN`,`BIT_VECTOR`,`CAT_NAME`,`COLUMN_NAME`,`COLUMN_TYPE`,`DB_NAME`,`BIG_DECIMAL_HIGH_VALUE`,`BIG_DECIMAL_LOW_VALUE`,`DOUBLE_HIGH_VALUE`,`DOUBLE_LOW_VALUE`,`LAST_ANALYZED`,`LONG_HIGH_VALUE`,`LONG_LOW_VALUE`,`MAX_COL_LEN`,`NUM_DISTINCTS`,`NUM_FALSES`,`NUM_NULLS`,`NUM_TRUES`,`PART_ID`,`PARTITION_NAME`,`TABLE_NAME`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" failed : Incorrect string value: '\xE6\xB9\x96\xE5\x8C\x97' for column 'PARTITION_NAME' at row 1)

Mysql中查看metastore库的ART_COL_STATS表

use metastore;
-- show create table PART_COL_STATS;
show full columns from PART_COL_STATS;

部分字段结果如下
在这里插入图片描述
可以看到需要PARTITION_NAME字段编码仍然为latin1，因此需要将PARTITION_NAME字段编码修改为utf8

use metastore;
alter table PART_COL_STATS modify column PARTITION_NAME varchar(500) character set utf8;

修改后重新插入带有中文分区的表格，可以正常运行

参考案例如下

create database mydb;
use mydb;
-- ① 创建不带分区的stu表
create table stu
(
    name string,
    age  int
) row format delimited fields terminated by '\t';

-- ② 上传数据到hdfs对应目录

-- ③ 创建分区表emp
create table emp
(
    name string,
    age  int
)
    partitioned by (provice string)
    row format delimited fields terminated by '\t';

-- ④ 插入数据含有中文分区的emp表中
insert into emp partition(provice = "湖北")
select * from stu;

方案三：若中文分区是不规范的，可以在脚本中直接用正则过滤掉中文

where  not regexp(t.event_app,'[\\u4E00-\\u9FFF]+') -- 过滤中文字符