MapReduce服务 MRS-条件表达式:TRY

时间:2023-11-01 16:25:24

TRY

评估一个表达式,如果出错,则返回Null。类似于编程语言中的try catch。try函数一般结合COALESCE使用,COALESCE可以将异常的空值转为0或者空,以下情况会被try捕获:

  • 分母为0
  • 错误的cast操作或者函数入参
  • 数字超过了定义长度

不推荐使用,应该明确以上异常,做数据预处理

示例:

假设有以下表,字段origin_zip中包含了一些无效数据:

-- 创建表create  table shipping (origin_state varchar,origin_zip varchar,packages int ,total_cost int);-- 插入数据insert into shipping values ('California','94131',25,100),('California','P332a',5,72),('California','94025',0,155),('New Jersey','08544',225,490);-- 查询数据SELECT * FROM shipping; origin_state | origin_zip | packages | total_cost --------------+------------+----------+------------  California   |      94131 |       25 |        100  California   |      P332a |        5 |         72 California   |      94025 |        0 |        155  New Jersey   |      08544 |      225 |        490 (4 rows) 

不使用Try查询失败:

SELECT CAST(origin_zip AS BIGINT) FROM shipping;Query failed: Cannot cast 'P332a' to BIGINT 
使用Try返回NULL:
SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping; origin_zip ------------       94131      NULL     94025     08544 (4 rows) 

不使用try查询失败:

SELECT total_cost/packages AS per_package FROM shipping;Query failed: Division by zero 

使用TRY和COALESCE返回默认值:

SELECT COALESCE(TRY(total_cost/packages),0) AS per_package FROM shipping;   per_package -------------    4      14         0          19 (4 rows)
support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_249103.html