VSQL, Visual Storytelling through Query Language

Posted May 21, 2020 by Moshe Goldberg, Vertica System Engineer

Visual Storytelling with SQL

This tip expands on my earlier post: “Pure Vertica SQL Graph Flavors

Visual story telling has not just the ability to capture attention, it make things simple to understand. Consider the following to boost your query result set readability.

What is the visual correlation between CPU utilization and Canary Queries elapsed time?

with -- chart scale = 30
result0 as (select time_slice(start_timestamp, 1, 'hour') as hour_slice,
count(1) q_count,
min(request_duration_ms) min_ms,
max(request_duration_ms) max_ms,
round(avg(request_duration_ms)) avg_ms
from   query_requests
where
request_type = 'QUERY' and
-- request_label = 'your_canary_label' and
start_timestamp > now() -24
group by 1
order by 1),
result1 as (select
node_name,
time_slice(start_time, 1,'hour') as hour_slice,
round(avg(average_cpu_usage_percent)) as avg_cpu,
round(max(average_cpu_usage_percent)) as max_cpu
from
cpu_usage
where
( NOW() - start_time) between '0 day' and '1 day' -- < '24 hour'
group by 1,2
order by 1),
result2 as (select row_number() over(order by r0.hour_slice) as row_id, node_name, r0.hour_slice, avg_cpu, max_cpu, avg_ms, q_count
from result0 r0, result1 r1
where r0.hour_slice = r1.hour_slice
order by r0.hour_slice, node_name),
norm0 as (select round(max(avg_ms)/30)+1  as norm_factor1 from result2),
norm1 as (select round(max(max_cpu)/30)+1 as norm_factor1 from result2),
norm2 as (select round(max(q_count)/30)+1 as norm_factor1 from result2),
chart1 as (select row_id, repeat('*',(max_cpu / norm_factor1)::int ) || repeat(' ',30 - (max_cpu / norm_factor1)::int) as max_cpu_chart from result2, norm1),
chart2 as (select result2.row_id, insert(max_cpu_chart, (avg_cpu / norm_factor1)::int +1, 1, ']') as avg_and_max_cpu_chart
from result2, norm1, chart1
where result2.row_id = chart1.row_id),
chart3 as (select row_id, repeat('*',(avg_ms / norm_factor1)::int ) || repeat(' ',30 - (avg_ms / norm_factor1)::int) as avg_ms_chart from result2, norm0),
chart4 as (select result2.row_id, insert(avg_ms_chart, (q_count / norm_factor1)::int +1, 1, '}') as count_and_avg_ms_chart
from result2, norm2, chart3
where result2.row_id = chart3.row_id)
select hour_slice, node_name, avg_cpu, max_cpu,avg_and_max_cpu_chart as '[average] and *max* cpu usage', q_count, avg_ms as q_avg_ms, count_and_avg_ms_chart as '{count} and *q_avg_ms*'
from result2, chart2, chart4
where result2.row_id = chart2.row_id and
result2.row_id = chart4.row_id
order by hour_slice, node_name
;
    hour_slice      |   node_name    | avg_cpu | max_cpu | [average] and *max* cpu usage  | q_count | q_avg_ms |     {count} and *q_avg_ms*
--------------------+----------------+---------+---------+--------------------------------+---------+----------+--------------------------------
2020-05-09 15:00:00 | v_vdb_node0001 |      44 |      51 | ***********]*                  |      61 |   424347 | ************}****************
2020-05-09 15:00:00 | v_vdb_node0002 |      99 |     100 | *************************]     |      61 |   424347 | ************}****************
2020-05-09 15:00:00 | v_vdb_node0003 |      45 |      51 | ***********]*                  |      61 |   424347 | ************}****************
2020-05-09 16:00:00 | v_vdb_node0001 |      56 |      96 | **************]*********       |      72 |   445163 | **************}***************
2020-05-09 16:00:00 | v_vdb_node0002 |      98 |     100 | ************************]      |      72 |   445163 | **************}***************
2020-05-09 16:00:00 | v_vdb_node0003 |      55 |      97 | **************]*********       |      72 |   445163 | **************}***************
2020-05-09 17:00:00 | v_vdb_node0001 |      60 |      94 | ***************]********       |     107 |   368967 | *********************}***
2020-05-09 17:00:00 | v_vdb_node0002 |      98 |     100 | ************************]      |     107 |   368967 | *********************}***
2020-05-09 17:00:00 | v_vdb_node0003 |      60 |      98 | ***************]********       |     107 |   368967 | *********************}***
2020-05-09 18:00:00 | v_vdb_node0001 |      55 |      97 | **************]*********       |      89 |   390494 | ******************}*******
2020-05-09 18:00:00 | v_vdb_node0002 |      97 |     100 | ************************]      |      89 |   390494 | ******************}*******
2020-05-09 18:00:00 | v_vdb_node0003 |      56 |      97 | **************]*********       |      89 |   390494 | ******************}*******
2020-05-09 19:00:00 | v_vdb_node0001 |      51 |      93 | *************]*********        |     113 |   330886 | ********************** }
2020-05-09 19:00:00 | v_vdb_node0002 |      97 |      99 | ************************]      |     113 |   330886 | ********************** }
2020-05-09 19:00:00 | v_vdb_node0003 |      51 |      97 | *************]**********       |     113 |   330886 | ********************** }
2020-05-09 20:00:00 | v_vdb_node0001 |      54 |      99 | **************]**********      |      92 |   262930 | ******************}
2020-05-09 20:00:00 | v_vdb_node0002 |      96 |     100 | ************************]      |      92 |   262930 | ******************}
2020-05-09 20:00:00 | v_vdb_node0003 |      56 |      96 | **************]*********       |      92 |   262930 | ******************}
2020-05-09 21:00:00 | v_vdb_node0001 |      16 |      86 | ****]*****************         |      15 |    95739 | ***}**
2020-05-09 21:00:00 | v_vdb_node0002 |      24 |      89 | ******]***************         |      15 |    95739 | ***}**
2020-05-09 21:00:00 | v_vdb_node0003 |      16 |      86 | ****]*****************         |      15 |    95739 | ***}**
2020-05-10 12:00:00 | v_vdb_node0001 |       0 |       0 | ]                              |       1 |        3 | }
2020-05-10 12:00:00 | v_vdb_node0002 |       0 |       0 | ]                              |       1 |        3 | }
2020-05-10 12:00:00 | v_vdb_node0003 |       0 |       0 | ]                              |       1 |        3 | }
2020-05-10 13:00:00 | v_vdb_node0001 |       0 |       7 | ]*                             |       2 |    12264 | }
2020-05-10 13:00:00 | v_vdb_node0002 |       1 |      14 | ]***                           |       2 |    12264 | }
2020-05-10 13:00:00 | v_vdb_node0003 |       0 |       7 | ]*                             |       2 |    12264 | }
2020-05-10 15:00:00 | v_vdb_node0001 |       0 |       0 | ]                              |       2 |     1114 | }
2020-05-10 15:00:00 | v_vdb_node0002 |       0 |       0 | ]                              |       2 |     1114 | }
2020-05-10 15:00:00 | v_vdb_node0003 |       0 |       0 | ]                              |       2 |     1114 | }
(30 rows)