Dataworks实践—报错原因及处理

1.0 引言

目前maxcompute中已经有对应的错误码附录，具体链接请查看官网链接，这里就不一一详细注明了，这里也将其主要内容复制如下，具体内容还请通过官网查阅。

但官网的情况并没有涵盖所有的，或者说我只介绍我数据开发过程中遇到的”坑“或者更通常见到的错误，希望大家在以后的数据开发中避免。

2.0 数字溢出错误描述以及应对措施

2.1 数字溢出报错说明

这个错误是非常常见的一个问题，即double转换到int，数字溢出。那么这里来说下Maxcompute 2.0数据类型。

类型	常量示例	描述
TINYINT	1Y,-127Y	8位有符号整型，取值范围-128~127
SMALLINT	32767S,-100S	16位有符号整型，取值范围-32768~32767
INT	1000,-15645787	32位有符号整型，取值范围 -2^31 ~ 2^31-1
BIGINT	100000000000L,-1L	64位有符号整型，取值范围 -2^63 1 ~ 2^63-1
BINARY	无	二进制数据类型，目前长度限制为8MB
FLOAT	无	32位二进制浮点型
DOUBLE	3.1415926 1E 7	64位二进制浮点型
DECIMAL(precision,scale)	3.5BD,99999999999.9999999BD	10进制精确数字类型 precision:表示最多可以表示多少位的数字。取值范围:1<=precision<=38 scale:表示小数部分的位数。取值范围:0<=scale<=18 如果不指定以上两个参数，则默认为decimal(38,18)
VARCHAR(n)	无	变长字符类型，n为长度；取值范围:1~65535
CHAR(n)	无	固定长度字符类型，n为长度。最大取值255.长度不足则会填充空格，但空格不参与比较
STRING	"abc","bcd","alibaba"	字符串类型，目前长度限制为8 MB。
DATE	DATE'2023-06-09'	日期类型，格式为yyyy-mm-dd。取值范围：0000-01-01~9999-12-31。
DATETIME	DATETIME'2023-06-09 00:00:00'	日期时间类型。取值范围：0000-01-01 00:00:00.000~9999-12-31 23:59:59.999，精确到毫秒。
TIMESTAMP	TIMESTAMP'2023-06-09 00:00:00:123456789'	时间戳类型。取值范围：0000-01-01 00:00:00.000000000~9999-12-31 23:59:59.999999999，精确到纳秒。
BOOLEAN	True,False	BOOLEAN类型取值范围：True、False。

2.2 实际业务场景

其中可以看到，int类型的最大值约为2147483647，smallint类型的最大值为32767，tinyint类型的最大值为127。而上述截图中的报错即是由于超出了int的范围导致，不过通过此报错我想说的问题不在于此。具体的场景如下：

公司生产数据中，时间戳字段都是整型，如下：

时间戳是带有时分秒的，在实际宽表的建设中，我们需要对该时间戳进行解析，而最简单的处理方式是如下的，并且也不报错，以致于目前几乎所有的时间戳的解析均是利用该逻辑来处理。

代码语言：sql复制

select 
	from_unixtime(int(create_time/1000)) as create_time
from target_table

但是，如果数据库中的时间戳来到了2147483647，也就是日期超过2038-01-19 11:14:07一秒以后，整体的含上述解析逻辑的脚本全部会报错，就会导致整体的调度失败，影响生产数据的产出，带来重大的生产事故。因此，比较好的调整方式如下：

代码语言：sql复制

select 
	from_unixtime(cast(create_time/1000 as bigint)) as create_time
from target_table

2.3 报错的启示

对于业务中的脚本逻辑，要进一步思考其本身的合理性和脚本的适配性，保证脚本的健壮性。

3.0 插入列少于目标列数错误描述以及应对措施

3.1 插入列少于目标列数报错说明

这个错误其实可以通过报错上很容易看出来，意思是在目标表中有5列，插入的数据中只有四列。而遇到这样的报错的情况下，我们很容易的想到是我们在手工输入的时候少输入了一列导致的，但是我们来看下脚本：

3.2 实际业务场景

代码语言：sql复制

create table yht_dw_dev.t_charge_detail(
	charge_id string COMMENT '',
	keep_id string COMMENT '',
	bill_id string COMMENT '',
	ar_ap string COMMENT '',
	amount string COMMENT ''
);

insert overwrite table yht_dw_dev.t_charge_detail
select 
charge_id
,keep_id
bill_id
,ar_ap
,amount
from yht_dwd.dwd_sea_charge_detail 
where pt='${bizdate}'
limit 100

目标表中为五列分别是$charge_id,keep_id,bill_id,ar_ap,amount$,而脚本中的插入语句仔细数一数也同样是五列呀，我们再单独执行一下$select$ 语句，发现语法正确，不过只展示了四列结果出来：

但是我们仔细观察语句和结果的区别可以看到：keep_id和bill_id字段中间缺少了逗号字段，导致两个字短变成了一个字短，但是语法却仍然可以跑通，这也是需要我们注意的地方：报错缺少列除了我们真的少写了真实的列，实际上也可以是我们少写了一个逗号导致多列变成单列，这是我们需要在实际中需要注意的。

4.0 隐式转换错误描述以及应对措施

4.1隐式转换错误实际业务场景

这个错误其实不太容易发现，但是却很致命，比如下面两段脚本：

代码语言：sql复制

select 
id, 
charge_confirmation_id, 
charge_confirmation_id=1640966400016064464 
from yht_dw.ods_cw_fin_charge_df 
where pt='${bizdate}'
and charge_confirmation_id=1640966400016064464

结果如下：

代码语言：sql复制

select 
id, 
charge_confirmation_id,
charge_confirmation_id='1640966400016064464'
from yht_dw.ods_cw_fin_charge_df
where pt='${bizdate}'
and charge_confirmation_id='1640966400016064464'

4.2 隐式转换错误说明

上述两个脚本其实只存在一处差异，即在where条件中的数字加了单引号转化为字符串和没有加单引号为bigint类型，而最后的结果是第一段脚本执行的结果为6条记录，而第二段脚本的执行结果为7265条记录，而通过两段脚本的比较，我们也会发现第一段的结果是正确的，而第二段脚本的执行结果是错误的，具体对照第二列和条件即可辨别。而具体导致的因素就是hive本身的数据类型的隐式转换。

也就是说，第一段脚本，需要将charge_confirmation_id转化为条件中等号右侧的bigint类型，而charge_confirmation_id本身即为bigint类型，所以，不需要转换，直接过滤得到结果；第二段脚本，charge_confirmation_id为bigint类型，而等号右侧为字符串类型，这样在隐士转换中出现问题，导致转换丢失了精度，导致结果异常，以下是hive官方文档中的隐士转换结果表。

	void	boolean	tinyint	smallint	int	bigint	float	double	decimal	string	varchar	timestamp	date	binary
void to	true	true	true	true	true	true	true	true	true	true	true	true	true	true
varchar to	false	false	false	false	false	false	false	true	true	true	true	false	false	false
tinyint to	false	false	true	true	true	true	true	true	true	true	true	false	false	false
timestamp to	false	false	false	false	false	false	false	false	false	true	true	true	false	false
string to	false	false	false	false	false	false	false	true	true	true	true	false	false	false
smallint to	false	false	false	true	true	true	true	true	true	true	true	false	false	false
int to	false	false	false	false	true	true	true	true	true	true	true	false	false	false
float to	false	false	false	false	false	false	true	true	true	true	true	false	false	false
double to	false	false	false	false	false	false	false	true	true	true	true	false	false	false
decimal to	false	false	false	false	false	false	false	false	true	true	true	false	false	false
date to	false	false	false	false	false	false	false	false	false	true	true	false	true	false
boolean to	false	true	false	false	false	false	false	false	false	false	false	false	false	false
binary to	false	false	false	false	false	false	false	false	false	false	false	false	false	true
bigint to	false	false	false	false	false	true	true	true	true	true	true	false	false	false

隐式转换的总结主要有以下几点：

hive转换时包括隐式转换(implicit conversion)和显式转换(explicitly conversion) 比如我们对两个不同数据类型的数字进行比较，加入一个数据类型为int型，一个是smallint类型，那么smallint类型的数据就会被隐式转换为int类型；但是我们不能隐式地将一个int类型的数据转换为smallint或tinyint类型的数据，这将会返回错误，除非你用了cast操作任何整数类型都可以隐式地转换为一个范围更大的类型。tinyint,smallint,int,bigint,float和sring都可以隐式的转换为double boolean类型不能转换为其他任何数据类型！如果强制转换的换会返回null

4.3 报错的启示

跟我之前强调的一样，hiveSQL是一种对数据类型进行强相关的结构性语言，因此在建表、join关联以及where条件筛选过程中都需要对数据类型进行特别关注，避免隐式转换，这样就避免产生一些底层的难以预知的错误，除非你可以记得住隐式转换表的内容。

5.0 参考文献

1 hive数据类型及数据类型转换的注意事项

2 隐式转换：Hive可以写3>2>1没用过吧？

3 Apache Hive language manual

4 云原生大数据计算服务 MaxCompute - 通用参考错误码附录

腾讯技术创作特训营S8

0 人点赞