添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
逼格高的沙发  ·  湖南大学2025年硕士研究生招生简章-湖南大 ...·  2 周前    · 
玉树临风的苦瓜  ·  Search | Samsung ...·  7 月前    · 
慷慨大方的跑步机  ·  python生成随机数总结_pyhton ...·  8 月前    · 
独立的奔马  ·  如何将从xlsx文件导入到RStudio的列 ...·  8 月前    · 
一身肌肉的冲锋衣  ·  密道_哭包美人被反派宠爱过度[快穿] - ...·  10 月前    · 
link管理  ›  Hive元数据MySQL编码导致Sqoop导入错误 | 一张假钞的真实世界
https://www.zhangjc.com/2021/08/10/Hive%E5%85%83%E6%95%B0%E6%8D%AEMySQL%E7%BC%96%E7%A0%81%E5%AF%BC%E8%87%B4Sqoop%E5%AF%BC%E5%85%A5%E9%94%99%E8%AF%AF/
近视的包子
7 月前

错误信息如下; 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
 
21/07/20 11:09:06 INFO mapreduce.Job:  map 100% reduce 0%
21/07/20 11:09:32 INFO mapreduce.Job: Job job_1593052953461_28645057 failed with state FAILED due to: Job commit failed: org.apache.hive.hcatalog.common.HCatException : 2006 : Error adding partition to metastore. Cause : MetaException(message:One or more instances could not be made persistent)
	at org.apache.hive.hcatalog.mapreduce.FileOutputCommitterContainer.registerPartitions(FileOutputCommitterContainer.java:966)
	at org.apache.hive.hcatalog.mapreduce.FileOutputCommitterContainer.commitJob(FileOutputCommitterContainer.java:250)
	at org.apache.hadoop.mapreduce.v2.app.commit.CommitterEventHandler$EventProcessor.handleJobCommit(CommitterEventHandler.java:285)
	at org.apache.hadoop.mapreduce.v2.app.commit.CommitterEventHandler$EventProcessor.run(CommitterEventHandler.java:237)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
Caused by: MetaException(message:One or more instances could not be made persistent)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:203)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:99)
	at com.sun.proxy.$Proxy86.add_partitions(Unknown Source)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.add_partitions(HiveMetaStoreClient.java:608)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:150)
	at com.sun.proxy.$Proxy87.add_partitions(Unknown Source)
	at org.apache.hive.hcatalog.mapreduce.FileOutputCommitterContainer.registerPartitions(FileOutputCommitterContainer.java:920)
	... 6 more
Caused by: javax.jdo.JDOUserException: One or more instances could not be made persistent
NestedThrowables:
org.datanucleus.exceptions.NucleusDataStoreException: Put request failed : INSERT INTO `PARTITION_PARAMS` (`PARAM_VALUE`,`PART_ID`,`PARAM_KEY`) VALUES (?,?,?) 
	at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistentAll(JDOPersistenceManager.java:775)
	at org.apache.hadoop.hive.metastore.ObjectStore.addPartitions(ObjectStore.java:1774)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:101)
	at com.sun.proxy.$Proxy84.addPartitions(Unknown Source)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.add_partitions_core(HiveMetaStore.java:2365)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.add_partitions(HiveMetaStore.java:2424)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:140)
	... 16 more
Caused by: org.datanucleus.exceptions.NucleusDataStoreException: Put request failed : INSERT INTO `PARTITION_PARAMS` (`PARAM_VALUE`,`PART_ID`,`PARAM_KEY`) VALUES (?,?,?) 
	at org.datanucleus.store.rdbms.scostore.JoinMapStore.putAll(JoinMapStore.java:233)
	at org.datanucleus.store.rdbms.mapping.java.MapMapping.postInsert(MapMapping.java:135)
	at org.datanucleus.store.rdbms.request.InsertRequest.execute(InsertRequest.java:522)
	at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObjectInTable(RDBMSPersistenceHandler.java:162)
	at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObject(RDBMSPersistenceHandler.java:138)
	at org.datanucleus.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.java:3363)
	at org.datanucleus.state.StateManagerImpl.makePersistent(StateManagerImpl.java:3339)
	at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2079)
	at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:1922)
	at org.datanucleus.ExecutionContextImpl.persistObjects(ExecutionContextImpl.java:1867)
	at org.datanucleus.ExecutionContextThreadedImpl.persistObjects(ExecutionContextThreadedImpl.java:231)
	at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistentAll(JDOPersistenceManager.java:764)
	... 30 more
Caused by: org.datanucleus.store.rdbms.exceptions.MappedDatastoreException: INSERT INTO `PARTITION_PARAMS` (`PARAM_VALUE`,`PART_ID`,`PARAM_KEY`) VALUES (?,?,?) 
	at org.datanucleus.store.rdbms.scostore.JoinMapStore.internalPut(JoinMapStore.java:1056)
	at org.datanucleus.store.rdbms.scostore.JoinMapStore.putAll(JoinMapStore.java:223)
	... 41 more
Caused by: java.sql.SQLException: Incorrect string value: '\xE9\xA2\x91\xE9\x81\x93...' for column 'PARAM_VALUE' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
	at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:205)
	at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeUpdate(ParamLoggingPreparedStatement.java:393)
	at org.datanucleus.store.rdbms.SQLController.executeStatementUpdate(SQLController.java:431)
	at org.datanucleus.store.rdbms.scostore.JoinMapStore.internalPut(JoinMapStore.java:1047)
	... 42 more


21/07/20 11:09:32 INFO mapreduce.Job: Counters: 31
	File System Counters
		FILE: Number of bytes read=0
		FILE: Number of bytes written=1367592
		FILE: Number of read operations=0
		FILE: Number of large read operations=0
		FILE: Number of write operations=0
		HDFS: Number of bytes read=476
		HDFS: Number of bytes written=67625
		HDFS: Number of read operations=16
		HDFS: Number of large read operations=0
		HDFS: Number of write operations=8
	Job Counters 
		Killed map tasks=1
		Launched map tasks=5

将异常信息中提示的字符串“\xE9\xA2\x91\xE9\x81\x93…”解码后为“频道”，因此怀疑是编码导致的问题。查看Hive表定义： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
hive> show create table tbl_test;
OK
CREATE TABLE `tbl_test`(
  `channel_id` string, 
  `uuid` string, 
  `channel_name` string, 
  `channel_status` string, 
  `tv_channel_logo` string, 
  `phone_channel_logo` string, 
  `definition_type` string COMMENT '频道码率', 
  `create_datetime` string, 
  `update_datetime` string)
PARTITIONED BY ( 
  `province_alias` string, 
  `dt` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://ycluster/user/hive/warehouse/ods.db/cos_channel_v2_bak_20210630'
TBLPROPERTIES (
  'last_modified_by'='data-platform', 
  'last_modified_time'='1628153252', 
  'spark.sql.create.version'='2.2 or prior', 
  'spark.sql.sources.schema.numPartCols'='2', 
  'spark.sql.sources.schema.numParts'='1', 
  'spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fields\":[{\"name\":\"channel_id\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"uuid\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"channel_name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"channel_status\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"tv_channel_logo\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"phone_channel_logo\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"definition_type\",\"type\":\"string\",\"nullable\":true,\"metadata\":{\"\u9891\u9053\u7801\u7387\"}},{\"name\":\"create_datetime\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"update_datetime\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"province_alias\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"dt\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}', 
  'spark.sql.sources.schema.partCol.0'='province_alias', 
  'spark.sql.sources.schema.partCol.1'='dt', 
  'transient_lastDdlTime'='1628650471')
Time taken: 0.041 seconds, Fetched: 31 row(s)

发现在Table的属性中“spark.sql.sources.schema.part.0”中有Union Code编码，而我们的环境都是UTF8编码。跟踪源代码发现在添加分区时会将表的属性信息依次记录到分区的参数中。因此检查MySQL表字段编码： partition params encoding

修改表字段编码后问题解决。参考SQL如下： 

1
 
ALTER TABLE `hive`.`partition_params` CHANGE COLUMN `PARAM_VALUE` `PARAM_VALUE` VARCHAR(4000) CHARACTER SET 'utf8' NULL DEFAULT NULL;

遗留问题：为什么在Hive表的属性中新增了spark相关的属性？

 
推荐文章
逼格高的沙发  ·  湖南大学2025年硕士研究生招生简章-湖南大学研究生院
2 周前
玉树临风的苦瓜  ·  Search | Samsung Semiconductor Global
7 月前
慷慨大方的跑步机  ·  python生成随机数总结_pyhton 根据均值和标准差生成一定范围的随机数-CSDN博客
8 月前
独立的奔马  ·  如何将从xlsx文件导入到RStudio的列中的第一个单词复制并粘贴到网页的搜索框中？
8 月前
一身肌肉的冲锋衣  ·  密道_哭包美人被反派宠爱过度[快穿] - 精美文-小说网
10 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台