时间戳

首页 > 技术文章 > SQL学习笔记（十一）Hive SQL和Presto SQL对比

SQL学习笔记（十一）Hive SQL和Presto SQL对比

HLBBLOG 2021-08-28 14:50 原文

时间转换

注意此处的"timestamp"指'2020-07-20 10:58:59'这种格式，timestamp才是指'1595932031'这种格式，日期格式指2021-08-28

获取当前时间

Hive Presto
sysdate() localtimestamp

"timestamp"转日期

Hive Presto
to_date('2021-08-28 14:00:00')/get_date('2021-08-28 14:00:00') format_datetime(cast('2015-11-11 10:00:00' as timestamp), 'yyyy-MM-dd')
  • unix_timestamp('2021-08-28 10:00:00')的结果是1630116000；
  • cast('2021-08-28 10:00:00' as timestamp)的结果是2021-08-28 10:00:00.0；大家timestamp的格式不一样

以上转换后的结果为：'2021-08-28'

timestamp转"timestamp"

Hive Presto
from_unixtime(1630130400,'yyyy-MM-dd HH:mm:ss') from_unixtime(1630130400)
  • Hive的from_unixtime()如果不加'yyyy-MM-dd HH:mm:ss'同样可以返回到秒的结果;如果只需要格式化到小时、分钟等的话只给出到对应位置的格式化参数即可
  • Presto的from_unixtime()不需要使用格式化参数来指定格式化的位数而且其中的类型必须是数值型，如果不是需要先使用cast转为数值型才可，或者会报错，默认返回到毫秒经度。如果需要指定返回的精度配合format_datetime使用即可。也就是Presto必须依靠format_datetime来返回指定精度，这一点Hive不需要。
select format_datetime(from_unixtime(1630130400),'yyyy-MM-dd HH:mm:ss')

以上转换后的结果为：'2021-08-28 14:00:00.000'

"timestamp"转timestamp

Hive Presto
unix_timestamp('2021-08-28 14:00:00') cast(to_unixtime( cast ( '2021-08-28 14:00:00' as timestamp)) as bigint)
  • presto这里的转换使用起来比较麻烦，需要to_unixtime和timestamp结合起来使用才行
to_unixtime( cast ( '2021-08-28 14:00:00' as timestamp))
--输出结果为1.63008E9，因此要用cast再次转换

以上转换后的结果为：'1630130400'

计算两个时间相差的天数

Hive Presto
datediff('2021-08-28','2021-08-27 15:25') date_diff('day',cast('2021-08-27' as date),cast('2021-08-28' as date))
  • Hive：计算两个日期之间的天数差值,是拿'2021-08-28'减去'2021-08-27'，即使date1和date2精确到的粒度不一致，如date1到天,date2到分钟结果仍然只是天粒度的差值。
  • Presto：这里需要三个参数，第一个参数指定计算的是“天”差值、“小时”差值等，另外需要把date1和date2转为date类型，否则SQL会报错，并且Presto是后面的日期减去前面的日期

以上转换后的结果为：1

计算某个日期的前N天或者后N天

Hive Presto
date_add('2021-08-28',1)/date_sub('2021-08-28',1)/get_date('2021-08-28',1)/get_date('2021-08-28',-1) date_add('day', 1, cast('2021-08-28' as date) )
  • Hive:date_add和date_sub分别是向后推N天和向前推N天，另外这里增加或减少后日期只精确到天，即使date1是精确到秒粒度的计算结果最终仍然是到天粒度。
  • Presto:这里同样需要三个参数后推日期的粒度，后推多少天,基准日期。另外需要注意preto这里没有date_sub函数，需要使用的话可以把第二个参数改为负值即可。
Hive:select date_add('20210828',1)
--会反映NULL
Hive：select get_date('20210828',1)
--同样可以转换为2021-08-29

以上转换后的结果为：'2021-08-28','2021-08-27'

带杠日期转不带杠

Hive Presto
get_dt_date('2021-08-28') format_datetime(cast('2021-08-11' as timestamp),'yyyyMMdd')

不带杠日期转带杠

Hive Presto
get_date('2021-08-28') format_datetime(parse_datetime('20210828', 'yyyyMMdd'), 'yyyy-MM-dd')
  • cast只能识别'2021-08-28'的这种带杠日期格式，因此用parse_datetime来转换

URL提取

URL知识

一个URL由以下几部分组成：scheme://host:[port]/path/[;parameters]?query-string=xxx#anchor

协议部分（scheme）

URL的协议部分为 "http："，这代表网页使用的是 HTTP 协议。在 Internet 中可以使用多种协议，如HTTP，FTP等等。

域名(主机)部分(host)

主机名，域名，比如www.baidu.com

端口部分（port）

跟在域名后面的是端口，域名和端口之间使用“:”作为分隔符。端口不是一个URL必须的部分，如果省略端口部分，将采用默认端口。当你访问一个网站的时候，浏览器默认使用80端口。

查找路径（path）

查找路径。比如：https://zhuanlan.zhihu.com/p/260660023，后面的/p/260660023就是path

query-string

查询字符串，比如：www.baidu.com/s?wd=python，后面的wd=python就是查询字符串

parameter

参数,如果要向服务器传入参数，在这部分输入

提取URL host

Hive Presto
parse_url('http://www.vip.com/1111?tagId=dress', 'HOST') url_extract_host('http://www.vip.com/1111?tagId=dress')
  • 其实presto同样可以使用parse_url函数

提取URL path

Hive Presto
parse_url('http://www.vip.com/1111?tagId=dress', 'PATH') url_extract_path('http://www.vip.com/1111?tagId=dress')
  • 其实presto同样可以使用parse_url函数

提取URL query

Hive Presto
parse_url('http://www.vip.com/1111?tagId=sport', 'QUERY') url_extract_query('http://www.vip.com/1111?tagId=dress')
  • 其实presto同样可以使用parse_url函数

提取URL parameter

Hive Presto
parse_url('http://www.vip.com/1111?tagId=sport', 'QUERY', 'tagId') url_extract_parameter('http://www.vip.com/1111?tagId=dress', 'tagId')
  • presto不支持parse_url的tagId

正则提取

正则知识

字符集合

字符 匹配字符
\d 从0-9的任一数字
\D 任一非数字字符
\w 任一单词字符，包括A-Z,a-z,0-9和下划线
\W 任一非单词字符
\s 任一空白字符，包括制表符，换行符，回车符，换页符和垂直制表符
\S 任一非空白字符
. 任一字符

字符簇

字符簇 描述
[[:alpha:]] 任何字母
[[:digit:]] 任何数字
[[:alnum:]] 任何字母和数字
[[:space:]] 任何空白字符
[[:upper:]] 任何大写字母
[[:lower:]] 任何小写字母
[[:punct:]] 任何标点符号
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]

边界集合

字符 描述
^ 每一行的开头，单行模式下等价于字符串的开头
$ 每一行的结尾，单行模式下等价于字符串的结尾

重复次数集合

贪婪模式会获取尽可能多的字符，而非贪婪模式会获取尽可能少的字符

贪婪 非贪婪 描述
* *? 零次或多次
? ?? 零次或一次
+ +? 一次或多次
{m} {m}? 正好m次，贪婪与非贪婪一样的
{m,} {m,}? 至少m次
{m, n} {m, n}? 最少m最多n次

组合操作符

优先级比较：圆括号>重复次数操作符>和>或

组合操作符 描述
[...] 方括号内任意字符或字符集合中的一个
[^…] 方括号内^为第一个字符时，表示与其后所有字符都不匹配的字符
(…) 圆括号，将复杂表达式当作单一表达式来处理
…|… 或
abc 和,直接将字符连在一起写

匹配操作符

匹配操作符 描述
\n 即后向引用。n为1~9，标识由圆括号里取得的匹配字符串。方向是从左到右在regexp_replace函数中，允许在模式表达式和替换表达式中都使用\n

正则提取 -首次

Hive Presto
regexp_extract('http://www.vip.com-123456-4321html', '(\D*)(\d+)', 2) url_extract_query('regexp_extract('http://www.vip.com-123456-4321html', '(\D*)(\d+)', 2)')
  • 正则提取-首次的函数在Hive和Presto是相通的
  • 语法：regexp_extract(string subject, string pattern, int index)
  • 返回值：string
  • 说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
  • 第一参数:要处理的字段
  • 第二参数:需要匹配的正则表达式
  • 第三个参数：
    • 0是显示与之匹配的整个字符串
    • 1是显示第一个括号里面的
    • 2是显示第二个括号的字段
regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0),  
-- x=18abc

regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','^x=([a-z]+)([0-9]+)',0), 
-- x=a3
 
regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',0),    
-- id=522228774076

regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',1),    
-- 522228774076
 
regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',0),            
-- i41915173660

regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',1)             
-- 41915173660

json提取

Hive Presto
get_json_object(activity_property,'$.brandlist') json_extract_scalar(activity_property,'$.brandlist')
  • json_extract_scalar返回varchar类型
  • json_extract(activity_property,'$.brandlist') 返回json类型

字符串转换

字符串截取

Hive Presto
substr('string' ,1,3) substr('string' ,1,3)
  • Hive可用substring()/substr(),Presto只能用substr()
  • 转换后的结果为：'str'

字符首次出现位置

Hive Presto
instr('abcdefg', 'cd') strpos('abcdefg', 'cd') /position('cd' IN 'abcdefg')
  • 转换后的结果为：3

字符串拼接

Hive Presto
concat('goods_name','','brand_store_name','','new_category_3rd_name') concat('goods_name','','brand_store_name','','new_category_3rd_name')
  • Hive和Presto相同
  • 输出结果：goods_name_brand_store_name_new_category_3rd_name

参考

推荐阅读