Presto中无法将空字符串转为bigint问题求助

我已经在查询里用COALESCE处理NULL值了，但运行时还是抛出错误：Can not cast '' to BIGINT。以下是我的查询语句和错误信息，求帮忙解决！

我的查询语句 #

select m.app, m.hour,m.user_id, m.avg_minutes_watched, n.userid, n.watched_mins, n.active 
FROM (
    SELECT app, substr(hour,1,8) hour, 
           CAST(COALESCE(json_extract_scalar(json, '$.user_id'), '-999999') as BigInt) user_id, 
           CAST(COALESCE(json_extract_scalar(json, '$.playback_time'), '-999999') as BigInt) /60000 avg_minutes_watched 
    FROM prod 
    WHERE event_type = 'user_session_complete' AND hour > '20180331' and hour < '20180501'
)m 
left join (
    select userid, watched/60000 watched_mins, 
           (case when watched/60000 >= 2 then 'active' else 'not_active' end) active 
    from est 
    where realdate > '2018-03-31' and realdate < '2018-05-01'
) n on m.user_id = n.userid 
order by m.hour, m.user_id;

错误信息 #

Query 20180510_220127_17857_bxg5s, FAILED, 72 nodes Splits: 5,178 total, 644 done (12.44%) 0:04 [39.2M rows, 1.93GB] [9.32M rows/s, 469MB/s]
Query 20180510_220127_17857_bxg5s failed: Can not cast '' to BIGINT

老哥，问题出在你只处理了NULL，但你的数据里存在空字符串（''）——这玩意儿和NULL不是一回事，COALESCE只会替换NULL值，空字符串它管不着，所以转BIGINT的时候就炸了。

给你个简单的解决方案：用NULLIF函数把空字符串先转成NULL，再交给COALESCE处理，这样就能同时覆盖NULL和空字符串两种情况了。

修改后的查询语句里，把原来的CAST(COALESCE(...))部分改成这样：

CAST(COALESCE(NULLIF(json_extract_scalar(json, '$.user_id'), ''), '-999999') as BigInt) user_id,
CAST(COALESCE(NULLIF(json_extract_scalar(json, '$.playback_time'), ''), '-999999') as BigInt) /60000 avg_minutes_watched

完整的修改后SQL如下：

select m.app, m.hour,m.user_id, m.avg_minutes_watched, n.userid, n.watched_mins, n.active 
FROM (
    SELECT app, substr(hour,1,8) hour, 
           CAST(COALESCE(NULLIF(json_extract_scalar(json, '$.user_id'), ''), '-999999') as BigInt) user_id, 
           CAST(COALESCE(NULLIF(json_extract_scalar(json, '$.playback_time'), ''), '-999999') as BigInt) /60000 avg_minutes_watched 
    FROM prod 
    WHERE event_type = 'user_session_complete' AND hour > '20180331' and hour < '20180501'
)m 
left join (
    select userid, watched/60000 watched_mins, 
           (case when watched/60000 >= 2 then 'active' else 'not_active' end) active 
    from est 
    where realdate > '2018-03-31' and realdate < '2018-05-01'
) n on m.user_id = n.userid 
order by m.hour, m.user_id;

简单解释下：NULLIF(a, b)的作用是如果a等于b就返回NULL，否则返回a。这里我们把空字符串转成NULL，这样COALESCE就能正常把它替换成'-999999'，再转成BIGINT就不会报错了。

如果之后还遇到其他非数字格式的字符串报错，可能还需要加额外的判断，但当前这个空字符串的问题，这么改应该就能解决啦。

内容的提问来源于stack exchange，提问作者joey