SQL查询样例

本章以ELB日志为例进行介绍，对LTS中的ELB原始日志进行查询，具体查询步骤如下。

1. 登录云日志服务控制台，在日志管理页面，单击对应日志流名称，进入日志详情页面。
2. 系统获取ELB原始日志，在原始日志页面查看具体日志。
3. 单击右上角，在弹出页面中，选择“云端结构化解析”。
4. 选择结构化模板，根据ELB模板进行结构化配置。其他日志内容可选择其他结构化模板。
5. 在“可视化”页签下输入SQL查询语句对相应的字段进行查询，即可返回所需的日志内容。

SELECT "router_request_uri" as "request_uri", "host", COUNT(*) as pv,
round(sum(body_bytes_sent) / 1024.0 , 5) as "body_bytes_sent(MB)",
round(sum(case when status >= 200 and status < 300 then 1 else 0 end ) * 100.0 / COUNT(1), 6) as "2xx ratio(%)",
round(sum(case when status >= 300 and status < 400 then 1 else 0 end ) * 100.0 / count(1), 6) as "3xx ratio(%)",
round(sum(case when status >= 400 and status < 500 then 1 else 0 end ) * 100.0 / count(1), 6) as "4xx ratio(%)",
round(sum(case when status >= 500 and status < 600 then 1 else 0 end ) * 100.0 / count(1), 6) as "5xx ratio(%)"
GROUP BY "host", "router_request_uri"
ORDER BY pv DESC
LIMIT 100

柱状图

根据以下语句查询结果绘制柱状图，x轴选择"request_uri"，y轴选择"pv"，表示每种requestURL请求分别有多少条。

SELECT "router_request_uri" as "request_uri", "host", COUNT(*) as pv,
round(sum(body_bytes_sent) / 1024.0 , 5) as "body_bytes_sent(MB)",
round(sum(case when status >= 200 and status < 300 then 1 else 0 end ) * 100.0 / COUNT(1), 6) as "2xx ratio(%)",
round(sum(case when status >= 300 and status < 400 then 1 else 0 end ) * 100.0 / count(1), 6) as "3xx ratio(%)",
round(sum(case when status >= 400 and status < 500 then 1 else 0 end ) * 100.0 / count(1), 6) as "4xx ratio(%)",
round(sum(case when status >= 500 and status < 600 then 1 else 0 end ) * 100.0 / count(1), 6) as "5xx ratio(%)"
GROUP BY "host", "router_request_uri"
ORDER BY pv DESC
LIMIT 100

折线图

根据以下语句查询结果绘制折线图，x轴选择"_time_"，y轴选择"QPS"。表示查询时间段内间隔5s的QPS变化。

select TIME_FORMAT(TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT5S'),'yyyy-MM-dd HH:mm:ss','+08:00') AS _time_ , COUNT(*) as QPS from log group by _time_

饼图

根据以下语句查询结果绘制饼图，类目选择"status"，数据选择"rm"，表示查询时间段内status不同值的占比。

SELECT status, COUNT(1) AS rm GROUP BY status

数字图

根据以下语句查询结果绘制数字图，查看最近一小时一共有多少次正常的请求。

SELECT count(*) AS normalRequest WHERE status = 200