共计 3120 个字符，预计需要花费 8 分钟才能阅读完成。
简介：怎么对齐两段 union 脚本的数据类型
第 1 章 问题概述
1.1 UNION 中隐式类型转换问题
近期参加的一个公有云我的项目要降级，因为 maxcompute 要降级到更新的版本，对之前的一些 SQL 写法有个更高的要求，就引出了这个 union 隐式转换的问题。运维同学扫描到外部的异样是：union.string.meet.non.string。
在 ODPS 某些模式中在 union 两侧对应列如果类型不同时会尝试隐式类型转换，其行为是一边为 string，另一边为数字或 datetime 类型时，转为另一边的类型（string）。然而绝大多数的数据库或者开源生态而言，应用的都不是这种转换规则，比方 hive，mysql 等会优先转成 string。这种不确定的转换规则有时候会很危险，如用户从 hive 往 odps 迁徙时，可能会导致无声无息的精度损失，语义谬误等。ODPS2.0 为了平安禁止此隐式类型转换（这也是目前 oracle 的默认行为）, 如果须要请应用 CAST 函数。（之前好好的，当初要报错了）所以当初项目组要求脚本作者查看本人脚本，明确要转到的类型，如果须要退出显式转换。
例：select * from (–（谬误）
select a_bigint c1 from t1
union all
select 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 ab
from xlog
union ALL
select getdate() as aa,0 as ab
from 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 的执行打算，尽管会不会刚好会展现输入的数据类型呢？答案：会的。
explain
select 123 as aa,0 as ab
from xlog
;
Job Queueing…
job0 is root job
In Job job0:
root Tasks: M1
In Task M1:
Data source: mujiao.xlog
TS: mujiao.xlog
SEL: 123L aa, 0L ab
FS: output: Screen
schema:
aa (bigint)
ab (bigint)
OK
explain
select getdate() as aa,0 as ab
from xlog;
;
Job Queueing…
job0 is root job
In Job job0:
root Tasks: M1
In 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.disable=false;
sql running …..
WARNING:[4,8] implicit conversion from bigint to datetime,use cast function to suppress
这个 warning 会让所有的隐式转换都抛出来，在现场环境中，显著比我理论依照 explain 的办法判断进去的要多很多。这两种办法，在理论应用中该如何应用，大家能够自行判断。
祝大家好运！
原文链接：http://click.aliyun.com/m/100…
本文为阿里云原创内容，未经容许不得转载。
