Presto和Hive语法对比

工作中经常遇到些时间转换问题：

1) log_date:20200110 需要转换为标准日期，或者与时间戳数据进行比较

2) 工作环境涉及到presto与hive, 利用presto检查查询时速度更快，因此一般需要同时用presto和hive的语法对日期进行转换

下面对最近用到的时间转换进行梳理

• 问题1：时间格式转换（时间格式化）

例子: 当前时间20200110 转化为2020-01-10

1. --输出 2020-01-10

2. --hive

3. select to_date(from_unixtime(UNIX_TIMESTAMP('20200110','yyyyMMdd')));

5. --presto

6. select (format_datetime(date_parse('20200110','%Y%m%d'),'yyyy-MM-dd') ;

hive string与各种格式时间的转换: [转] String to Date conversion in hive - 在 Hive 中各种字符串转换成日期格式  [转] String to Date conversion in hive - 在 Hive 中各种字符串转换成日期格式

• 问题2： 时间的加减

例子: 原时间为20200110 需先转化为标准日期形式再加减

1. --hive

2. select date_add('2020-01-12',10);

3. select date_add(to_date(from_unixtime(UNIX_TIMESTAMP('20200110','yyyyMMdd'))),10);

5. --presto

6. select date_add('day',-6,cast('2020-07-07' as date));

7. --第三个参数不转换为date格式, 会报错 第三个参数必须为date格式

8. select

9. date_add('day',-6,cast(format_datetime(date_parse('20200110','%Y%m%d'),'yyyy-MM-dd') as date));

hive中date_add, date_sub, date_diff的用法： HIve中 datediff，date_add和date_sub的用法

date_sub与date_add用法几乎一样

• 问题3： 时间戳转日期

1. --hive

2. select from_unixtime(1578585600);

3. --加格式

4. select from_unixtime(1578585600,'yyyyMMdd');

6. --presto

7. select from_unixtime(1578585600);

8. --加格式

9. select format_datetime(from_unixtime(1578585600),'yyyy-MM-dd');

1. 问题4： 日期转时间戳

1. --hive

2. select unix_timestamp('20200110' ,'yyyyMMdd'); --10位时间戳

4. -- presto

5. select to_unixtime(cast('2020-01-10' as date));

6. select to_unixtime(cast(format_datetime(date_parse('20200110','%Y%m%d'),'yyyy-MM-dd') as d

• 问题5： 计算两个日期之间的diff

1. --hive

2. select datediff('2017-09-15','2017-09-01');-- 结果14

4. --presto

5. select date_diff('day',cast('2018-09-05' as date),cast('2018-09-07' as date));

7. -- 1)需要提供参数'day'，表示要查询的是天数间隔；要查询小时，则提供参数'hour'

8. -- 2)并且后面传参限制为date类型；

9. -- 3)最后要注意是后面减去前面 --与hive不同

json字符串提取，行转列，位运算

1. json字符串提取

1. --hive

2. select get_json_object(json, '$.book');

3. --Presto

4. select json_extract_scalar(json, '$.book');

5. --注意这里Presto中json_extract_scalar返回值是一个string类型,其还有一个函数json_extract是直接返回一个json串，所以使用的时候你得自己知道取的到底是一个什么类型的值.

2. 列转行

有两个字段, user_id, scores(分别是用户的得分)

示例: yn 98,97,95

如果我们想将其转化为 user_id， score(一个分值)，则需要用到列转行，结果是

yn 98

yn 97

yn 95

对应的sql写法为

1. --hive

2. select student, score from tests lateral view explode(split(scores, ',')) t as score;

4. --presto

5. select student, score from tests cross json unnest(split(scores, ',') as t (score);

3. 位运算

如果想判断某个数字的某一二级制位是否为1， 则需要用到位运算

示例: 判断某一数字的二进制表示的右起第6位是否为1

注意若第6位为1，则结果为64

1. --hive

2. select 8 & 64 != 64; -- 0,

3. select 64 & 64 != 64; --64,

4. --Presto

5. select bitwise_and(64,64); --结果: 64

6. select bitwise_and(2,64); --结果: 0

presto其它的位运算相关函数
bit_count(x, bits) → bigint 返回 x 的补码中置 1 的位数
bitwise_and(x, y) → bigint 位与函数
bitwise_not(x) → bigint 取非操作
bitwise_or(x, y) → bigint 位或函数
bitwise_xor(x, y) → bigint 抑或函数
bitwise_and_agg(x) → bigint 返回 x 中所有值的与操作结果，x 为数组
bitwise_or_agg(x) → bigint 返回 x 中所有值的或操作结果，x 位数组

4.日期转周函数:weekofyear

语法: weekofyear (string date)

返回值: int

说明:返回日期在当前的周数。

举例：

hive> select weekofyear('2011-12-08 10:03:01') from lxw_dual;

49

注意：这里的时间参数格式是yyyy-MM-dd