网站的验证码是怎么做的,网站app开发平台,小米手机网站建设目标,命令行连接wordpress第1章 问题概述
1.1 UNION中隐式类型转换问题
近期参与的一个私有云项目要升级#xff0c;因为maxcompute要升级到更新的版本#xff0c;对之前的一些SQL写法有个更高的要求#xff0c;就引出了这个union隐式转换的问题。运维同学扫描到内部的异常是#xff1a; union.st…第1章 问题概述
1.1 UNION中隐式类型转换问题
近期参与的一个私有云项目要升级因为maxcompute要升级到更新的版本对之前的一些SQL写法有个更高的要求就引出了这个union隐式转换的问题。运维同学扫描到内部的异常是 union.string.meet.non.string。 在ODPS某些模式中在union两侧对应列如果类型不同时会尝试隐式类型转换其行为是一边为string另一边为数字或datetime类型时转为另一边的类型string。然而绝大多数的数据库或者开源生态而言使用的都不是这种转换规则比如hivemysql等会优先转成string。这种不确定的转换规则有时候会很危险如用户从hive往odps迁移时可能会导致无声无息的精度损失语义错误等。 ODPS2.0为了安全禁止此隐式类型转换这也是目前oracle的默认行为,如果需要请使用CAST函数。之前好好的现在要报错了所以现在项目组要求脚本作者检查自己脚本明确要转到的类型如果需要加入显式转换。
例
select * from (--错误select a_bigint c1 from t1 union allselect a_string c1 from t2) x;
-- 如果希望结果c1为bigint类型这是目前ODPS的行为改为
select * from (--正确select a_bigint c1 from t1 union all select cast(a_string as bigint) c1 from t2) x;
-- 如果希望结果c1为string类型这是目前HIVE的行为改为
select * from (--正确select cast(a_bigint as string) c1 from t1 union all select a_string c1 from t2) x;
1.2 问题分析
因为还未升级目前脚本也不会报错maxcompute的异常我们也捕获不到改造的压力有点纯靠肉眼识别了着实有点难过。
错误示例
select 123 as aa,0 as abfrom xlogunion ALL select getdate() as aa,0 as abfrom xlog;FAILED: ODPS-0130241:[4,8] Illegalunion operation - type mismatch for column 0 of UNION, left is BIGINT while right is DATETIME
--注释这里的[4,8]是指第四行第八个字符开始也就是getdate().
那怎么去快速的定位到是哪个字段呢我翻了一下后台检索出来的上百个脚本脚本代码在500-1000行之间居多union 的数量在单个脚本中少则三五个多的有二十几个。呆了一早上毫无进展。
第2章 问题解决
简单的思考了一下要想获得Union的两个表数据类型是否对齐就得看下原来表结构中的数据类型目标表结构的数据类型还需看一下代码找到SQL逻辑执行后的数据类型这样才能找到哪些字段数据类型不一致。
于是按照这个思路开始看第一个脚本的代码就1000多行union的表字段数量也是100多个union还有6个。直接懵了完全肉眼无法识别。一早上就这么过去了不但一个没有搞定还把自己搞烦了。
2.1 利用执行计划
一抽莫展之际突然想到了执行计划。MaxCompute的执行计划虽然会不会刚好会展示输出的数据类型呢答案会的。
explainselect 123 as aa,0 as abfrom xlog;Job Queueing... job0 is root jobIn Job job0:root Tasks: M1In Task M1: Data source: mujiao.xlog TS: mujiao.xlog SEL: 123L aa, 0L ab FS: output: Screen schema: aa (bigint) ab (bigint)OKexplainselect getdate() as aa,0 as abfrom xlog;;Job Queueing...job0 is root jobIn Job job0:root Tasks: M1In Task M1: Data source: mujiao.xlog TS: mujiao.xlog SEL: 1655965081824 aa, 0L ab FS: output: Screen schema: aa (datetime) ab (bigint)OK
我们看到在FS:output:Screen 下面是schema:aa(bigint),ab(bigint)。这就是我们可以利用的数据类型了。所以我们可以把长脚本中的union一段一段的explain然后截取这部分内容比较多个schema的不同。
schema1: schema2: aa (bigint) aa (datetime) ab (bigint) ab (bigint)
这样就肉眼可视的发现其实union中两段SQL的字段aa是不同的。
2.2 其他问题
其他相关的一些问题
1) 执行计划中的max_pt()函数无法在开发环境使用因为开发环境没有分区这个函数会直接报错。要么删除、注释这个函数要么在表前面增加生产环境前缀。
2) 超长的SQL段执行计划可能有几百行上千行找不到最终的output。可以在日志中搜索“output: Screen”这段对应的就是最终的输出。
3) 太多的字段肉眼无法判断哪些类型不一样的时候建议在excel中来比较利用excel的筛选能力逐个数据类型筛选比较。
4) 执行计划在特别的情况下可能出不来使用create table as创建一个临时表来识别SQL输出的数据类型然后再desc表结构。不过每个字段都要给一个名称在create table的时候还有null这种写法也是需要cast后给一个明确的数据类型。
5) 日期转换因为string到日期转换的格式化类型不是能猜出来的建议实际看一下数据格式不要猜测。否则只能线上运行后报错才能排查出问题。
6) 对于Null值可以cast(null as datetime)、cast(null as double)给字段赋值。
即便这些都可以对于数百个长达几百行的脚本来说这项工作都足以让你烦躁不安失去耐心。建议研发同学还是劳逸结合再就是日后把这个工作变成一个习惯。一大段SQL的union就直接先explain别等报错一个一个看心烦。
最后你会发现这一切的缘由还是我们的基础工作没有做好。既然是union一起的数据字段理论数据类型和值域是一模一样的怎么会出这种问题。标准化的数据应该是日期就是日期数值就是数值字符就是字符不会数值存储成字符、日期存储成字符。显然现在的痛苦还是来源于之前的工作缺失做好每一步后面会越来越轻松。
2.3 另外一个方法
后来跟研发同学要到了一个可以让warning信息显示出来的提示。
setodps.compiler.warning.disablefalse;sql running .....WARNING:[4,8] implicit conversion from bigint to datetime,use cast function to suppress
这个warning会让所有的隐式转换都抛出来在现场环境中明显比我实际按照explain的方法判断出来的要多很多。这两种方法在实际使用中该如何使用大家可以自行判断。
祝大家好运
原文链接
本文为阿里云原创内容未经允许不得转载。
