hive和presto的求数组长度函数区别及注意事项

1、任务

获取邮箱字符串’@'后字符串 ，求长度

2、hive & spark-sql 求数组长度的函数 size hive & spark-sql 求数组长度的函数 size select size(split(email, '@')),split(email, '@'),split(email, '@')[0],split(email, '@')[1] FROM (select "jack@126 " as email union select "tom@126 " as email) tb_mid; select size(split(email, '@')),split(email, '@'),split(email, '@')[0],split(email, '@')[1] FROM (select 'jack@126 ' as email union select 'tom@126 ' as email) tb_mid; 2 ["tom","126 "] tom 126 2 ["jack","126 "] jack 126 Time taken: 0.723 seconds, Fetched 2 row(s) 3、presto  求数组长度的函数 cardinality presto 求数组长度的函数 cardinality select cardinality(split(email, '@')),split(email, '@'),split(email, '@')[1],split(email, '@')[2] FROM (select 'jack@126 ' as email union select 'tom@126 ' as email) tb_mid; _col0 | _col1 | _col2 | _col3 -------+-------------------+-------+------------ 2 | [tom, 126 ] | tom | 126 2 | [jack, 126 ] | jack | 126 (2 rows) select cardinality(split(email, '@')),split(email, '@'),split(email, '@')[1],split(email, '@')[2] FROM (select "jack@126 " as email union select "tom@126 " as email) tb_mid; Query 20231019_070945_20009_n9u2s failed: line 3:9: Column 'jack@126 ' cannot be resolved select cardinality(split(email, '@')),split(email, '@'),split(email, '@')[1],split(email, '@')[2] FROM (select "jack@126 " as email union select "tom@126 " as email) tb_mid 4、注意事项

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

2）、presto 不支持双引号 ，而hive 既支持单引号，也支持双引号

presto> SELECT -> email, -> (case when cardinality(split(email, '@')) = 2 then split(email, '@')[1] else '' end ) as email_suffix -> FROM -> (select "jack@126 " as email union select "tom@126 " as email) tb_mid; Query 20231016_070153_17958_p9f2s failed: line 5:9: Column 'jack@126 ' cannot be resolved SELECT email, (case when cardinality(split(email, '@')) = 2 then split(email, '@')[1] else '' end ) as email_suffix FROM (select "jack@126 " as email union select "tom@126 " as email) tb_mid