[大数据]hive和presto的求数组长度函数区别(hive&cardinality)

1.数据示例:

123456@qq.com

2.目的:

获取邮箱字符串’@'后字符串

3.实现

3.1 hive用法

SELECT 
email.email,
(case when size(split(email.email, '@')) = 2 then split(t1.email, '@')[1] else '' end ) as email_suffix
FROM `email`

3.2 presto用法

SELECT 
email.email,
(case when cardinality(split(email.email, '@')) = 2 then split(t1.email, '@')[2] else '' end ) as email_suffix
FROM `email`

4.比较

在计算数组长度的时候，hive和presto的函数不同
其中hive的size函数默认数组的下标从0开始
presto的cardinality函数默认数组的下标从1开始