Hive报错To_Number函数无效，该替换为哪个函数？

问题场景 #

原Teradata查询在Hive执行时报错，错误提示：

Error while compiling statement: FAILED: SemanticException [Error 10011]: Invalid function To_Number

原Teradata查询语句：

Coalesce(CASE WHEN To_Number(STG_156_110_CUID.ID_ISSUE_DATE)  >= '29991231' OR 
To_Number(STG_156_110_CUID.ID_ISSUE_DATE)  <= '1901000' OR 
To_Number(STG_156_110_CUID.ID_ISSUE_DATE) IS NULL THEN Cast(NULL AS DATE Format 'YYYYMMDD') 
ELSE Cast(STG_156_110_CUID.ID_ISSUE_DATE AS DATE Format 'YYYYMMDD')
END, Cast ('19000101' AS DATE Format 'YYYYMMDD' )) AS ID_ISSUE_DATE

解决方案 #

Hive中没有To_Number()函数，可通过以下两种方式替换，同时适配Hive的日期转换语法：

方式1：类型转换替代 #

将字符串直接转为数值类型，同时替换Teradata特有的日期转换语法：

Coalesce(CASE WHEN cast(STG_156_110_CUID.ID_ISSUE_DATE as bigint) >= 29991231 OR 
cast(STG_156_110_CUID.ID_ISSUE_DATE as bigint) <= 1901000 OR 
STG_156_110_CUID.ID_ISSUE_DATE IS NULL THEN NULL 
ELSE to_date(STG_156_110_CUID.ID_ISSUE_DATE, 'yyyyMMdd')
END, to_date('19000101', 'yyyyMMdd')) AS ID_ISSUE_DATE

方式2：直接字符串比较（更高效） #

由于ID_ISSUE_DATE是固定长度的YYYYMMDD格式字符串，直接做字符串比较的逻辑和数值比较完全一致，无需类型转换：

Coalesce(CASE WHEN STG_156_110_CUID.ID_ISSUE_DATE >= '29991231' OR 
STG_156_110_CUID.ID_ISSUE_DATE <= '1901000' OR 
STG_156_110_CUID.ID_ISSUE_DATE IS NULL THEN NULL 
ELSE to_date(STG_156_110_CUID.ID_ISSUE_DATE, 'yyyyMMdd')
END, to_date('19000101', 'yyyyMMdd')) AS ID_ISSUE_DATE

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