Comparing query_log With Different Intervals

The query filters the data from the ‘system.query_log’ table to include only entries of type 2 (QueryFinish) and 4 (ExceptionWhileProcessing) and groups the results by the ‘normalized_query_hash’. The results are then sorted by the difference between ‘SelectedRangesAfter’ and ‘SelectedRangesBefore’ in descending order and limited to the top 10 rows.

The provided output shows that all metrics have values of 0, which suggests that there may be no data matching the specified criteria in the database for the given time intervals and query types. Further investigation may be needed to determine the cause of these zero values.

WITH
    toStartOfInterval(event_time, toIntervalMinute(5)) = '2023-06-30 13:00:00' AS before,
    toStartOfInterval(event_time, toIntervalMinute(5)) = '2023-06-30 15:00:00' AS after
SELECT
    normalized_query_hash,
    anyIf(query, before) AS QueryBefore,
    anyIf(query, after) AS QueryAfter,
    countIf(before) AS CountBefore,
    sumIf(query_duration_ms, before) / 1000 AS QueriesDurationBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'RealTimeMicroseconds')], before) / 1000000 AS RealTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')], before) / 1000000 AS UserTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')], before) / 1000000 AS SystemTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskReadElapsedMicroseconds')], before) / 1000000 AS DiskReadTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskWriteElapsedMicroseconds')], before) / 1000000 AS DiskWriteTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkSendElapsedMicroseconds')], before) / 1000000 AS NetworkSendTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkReceiveElapsedMicroseconds')], before) / 1000000 AS NetworkReceiveTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'ZooKeeperWaitMicroseconds')], before) / 1000000 AS ZooKeeperWaitTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSIOWaitMicroseconds')], before) / 1000000 AS OSIOWaitTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUWaitMicroseconds')], before) / 1000000 AS OSCPUWaitTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')], before) / 1000000 AS OSCPUVirtualTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedBytes')], before) AS SelectedBytesBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedRanges')], before) AS SelectedRangesBefore,
    sumIf(read_rows, before) AS ReadRowsBefore,
    formatReadableSize(sumIf(read_bytes, before) AS ReadBytesBefore),
    sumIf(written_rows, before) AS WrittenTowsBefore,
    formatReadableSize(sumIf(written_bytes, before)) AS WrittenBytesBefore,
    sumIf(result_rows, before) AS ResultRowsBefore,
    formatReadableSize(sumIf(result_bytes, before)) AS ResultBytesBefore,
    countIf(after) AS CountAfter,
    sumIf(query_duration_ms, after) / 1000 AS QueriesDurationAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'RealTimeMicroseconds')], after) / 1000000 AS RealTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')], after) / 1000000 AS UserTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')], after) / 1000000 AS SystemTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskReadElapsedMicroseconds')], after) / 1000000 AS DiskReadTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskWriteElapsedMicroseconds')], after) / 1000000 AS DiskWriteTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkSendElapsedMicroseconds')], after) / 1000000 AS NetworkSendTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkReceiveElapsedMicroseconds')], after) / 1000000 AS NetworkReceiveTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'ZooKeeperWaitMicroseconds')], after) / 1000000 AS ZooKeeperWaitTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSIOWaitMicroseconds')], after) / 1000000 AS OSIOWaitTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUWaitMicroseconds')], after) / 1000000 AS OSCPUWaitTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')], after) / 1000000 AS OSCPUVirtualTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedBytes')], after) AS SelectedBytesAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedRanges')], after) AS SelectedRangesAfter,
    sumIf(read_rows, after) AS ReadRowsAfter,
    formatReadableSize(sumIf(read_bytes, after) AS ReadBytesAfter),
    sumIf(written_rows, after) AS WrittenTowsAfter,
    formatReadableSize(sumIf(written_bytes, after)) AS WrittenBytesAfter,
    sumIf(result_rows, after) AS ResultRowsAfter,
    formatReadableSize(sumIf(result_bytes, after)) AS ResultBytesAfter
FROM system.query_log
WHERE (before OR after) AND (type IN (2, 4))
GROUP BY normalized_query_hash
    WITH TOTALS
ORDER BY SelectedRangesAfter - SelectedRangesBefore DESC
LIMIT 10
FORMAT Vertical

Output:

Totals:
───────
normalized_query_hash:                         0
QueryBefore:                                   
QueryAfter:                                    
CountBefore:                                   0
QueriesDurationBefore:                         0
RealTimeBefore:                                0
UserTimeBefore:                                0
SystemTimeBefore:                              0
DiskReadTimeBefore:                            0
DiskWriteTimeBefore:                           0
NetworkSendTimeBefore:                         0
NetworkReceiveTimeBefore:                      0
ZooKeeperWaitTimeBefore:                       0
OSIOWaitTimeBefore:                            0
OSCPUWaitTimeBefore:                           0
OSCPUVirtualTimeBefore:                        0
SelectedBytesBefore:                           0
SelectedRangesBefore:                          0
ReadRowsBefore:                                0
formatReadableSize(sumIf(read_bytes, before)): 0.00 B
WrittenTowsBefore:                             0
WrittenBytesBefore:                            0.00 B
ResultRowsBefore:                              0
ResultBytesBefore:                             0.00 B
CountAfter:                                    0
QueriesDurationAfter:                          0
RealTimeAfter:                                 0
UserTimeAfter:                                 0
SystemTimeAfter:                               0
DiskReadTimeAfter:                             0
DiskWriteTimeAfter:                            0
NetworkSendTimeAfter:                          0
NetworkReceiveTimeAfter:                       0
ZooKeeperWaitTimeAfter:                        0
OSIOWaitTimeAfter:                             0
OSCPUWaitTimeAfter:                            0
OSCPUVirtualTimeAfter:                         0
SelectedBytesAfter:                            0
SelectedRangesAfter:                           0
ReadRowsAfter:                                 0
formatReadableSize(sumIf(read_bytes, after)):  0.00 B
WrittenTowsAfter:                              0
WrittenBytesAfter:                             0.00 B
ResultRowsAfter:                               0
ResultBytesAfter:                              0.00 B

• QueryBefore and QueryAfter: The actual SQL queries executed.
• CountBefore and CountAfter: The count of queries in each interval.
• QueriesDurationBefore and QueriesDurationAfter: The total duration of queries in seconds.
• RealTimeBefore and RealTimeAfter: The real-time duration in seconds.
• UserTimeBefore and UserTimeAfter: The user CPU time in seconds.
• SystemTimeBefore and SystemTimeAfter: The system CPU time in seconds.
• DiskReadTimeBefore and DiskReadTimeAfter: Disk read time in seconds.
• DiskWriteTimeBefore and DiskWriteTimeAfter: Disk write time in seconds.
• NetworkSendTimeBefore and NetworkSendTimeAfter: Network send time in seconds.
• NetworkReceiveTimeBefore and NetworkReceiveTimeAfter: Network receive time in seconds.
• ZooKeeperWaitTimeBefore and ZooKeeperWaitTimeAfter: ZooKeeper wait time in seconds.
• OSIOWaitTimeBefore and OSIOWaitTimeAfter: OS I/O wait time in seconds.
• OSCPUWaitTimeBefore and OSCPUWaitTimeAfter: OS CPU wait time in seconds.
• OSCPUVirtualTimeBefore and OSCPUVirtualTimeAfter: OS CPU virtual time in seconds.
• SelectedBytesBefore and SelectedBytesAfter: The total selected bytes.
• SelectedRangesBefore and SelectedRangesAfter: The total selected ranges.
• ReadRowsBefore and ReadRowsAfter: The total rows read.
• ReadBytesBefore and ReadBytesAfter: The total bytes read in a human-readable format.
• WrittenTowsBefore and WrittenTowsAfter: The total rows written.
• WrittenBytesBefore and WrittenBytesAfter: The total bytes written in a human-readable format.
• ResultRowsBefore and ResultRowsAfter: The total result rows.
• ResultBytesBefore and ResultBytesAfter: The total result bytes in a human-readable format.