Presto常用命令

一、基本资料

1、官方文档

https://prestodb.github.io/docs/current/sql/select.html

 

二、常用命令

1、kill任务，登录presto客户端

CALL system.runtime.kill_query(query_id => '20220317_083239_04518_ik27u', message => 'Using too many resources');

2、日期函数

--前7天
select date_format(date_add('day', -7, CURRENT_DATE), '%Y%m%d')
--前1天
select format_datetime(date_add('day', -1, CURRENT_DATE), 'yyyyMMdd')
--获取前一个小时
select date_format(current_timestamp - interval '1' hour,'%H')
--日期转data
select from_unixtime(unix_timestamp('20220324','yyyyMMdd'),'yyyy-MM-dd') as dt
--日期转周
select  weekofyear('2022-03-24') as dt

3、字符串处理

--字符串截取
select substring('20220324',0,4) as dt
--字符串拼接
select concat('a','b') as str

4、分区操作

--查看分区
show partitions bdg_inf.table1
--删除分区
alter table bdg_inf.table1 drop partition(dt=20241122);

 

三、分析Sql

1、提取出filter.conditions数组中，field字段内容，并且只展示.之后的字符串[app_version、page_version]

数据内容：

{
	"filter": {
		"conditions": [{
			"field": "event.app_version",
			"function": "equal",
			"params": ["", "4.99.20", "6.2.0.0.1"]
		}, {
			"field": "event.page_version",
			"function": "equal",
			"params": ["", "14.1"]
		}],
		"relation": "or"
	},
	"gid": "compass",
	"t": "m3r2sww2"
}

执行sql：

select field_last,count(1) as request_num from (
    SELECT
        json_extract_scalar(c.value, '$.field') AS field
    	,regexp_extract(json_extract_scalar(c.value, '$.field'), '.*\.(.*)', 1) AS field_last
    FROM
        table
    CROSS JOIN
        UNNEST(CAST(json_extract(params, '$.filter.conditions') AS ARRAY<JSON>)) AS c (value)
    WHERE
        dt>= '20241021' and dt<= '20241121' 
        AND event_id = '777'
        AND page_url in ('/eventAnalysis')
        AND json_format(json_extract(params, '$.filter')) <> '{}'
    --    AND md5_log='12222222'
)
group by field_last