refreshing: db_logs

temaon_lieto

Jul 4th, 2025 (edited)

114

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

SQL 4.78 KB | None | 0 0

raw download clone embed print report

-- DATABASE LOGS QUERIES
-- -----------------------------------------------------
-- Explanation of status codes:
-- kind: 0 = refresh_clickhouse_tables_started, 1 = refresh_clickhouse_tables_finished
-- severity: 0 = success (no error), 1 = failure (with error)
-- QUERY 1: Latest successful log entries
-- -----------------------------------------------------
-- Shows the most recent successful log entry for each source (table_name)
-- Only includes entries where process started (kind=0) without errors (severity=0)
WITH db_logs AS (
SELECT
*,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 0 AND db_logs.severity = 0
AND db_logs.created_at BETWEEN '2025-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
)
SELECT db_logs.*
FROM db_logs
WHERE db_logs.rank = 1
ORDER BY db_logs.created_at DESC;
-- QUERY 2: Latest failed log entries
-- -----------------------------------------------------
-- Shows the most recent failed log entry for each source (table_name)
-- Only includes entries where process started (kind=0) with errors (severity=1)
WITH db_logs AS (
SELECT
*,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 0 AND db_logs.severity = 1
AND db_logs.created_at BETWEEN '2024-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
)
SELECT db_logs.*
FROM db_logs
WHERE db_logs.rank = 1
ORDER BY db_logs.created_at DESC;
-- QUERY 3: Latest successful log entries (finished)
-- -----------------------------------------------------
-- Shows the most recent successful log entry for each source (table_name)
-- Only includes entries where process finished (kind=1) without errors (severity=0)
WITH db_logs AS (
SELECT
*,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 1 AND db_logs.severity = 0
AND db_logs.created_at BETWEEN '2024-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
)
SELECT db_logs.*
FROM db_logs
WHERE db_logs.rank = 1
ORDER BY db_logs.created_at DESC;
-- QUERY 4: Latest failed log entries (finished)
-- -----------------------------------------------------
-- Shows the most recent failed log entry for each source (table_name)
-- Only includes entries where process finished (kind=1) with errors (severity=1)
WITH db_logs AS (
SELECT
*,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 1 AND db_logs.severity = 1
AND db_logs.created_at BETWEEN '2024-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
)
SELECT db_logs.*
FROM db_logs
WHERE db_logs.rank = 1
ORDER BY db_logs.created_at DESC;
-- QUERY 5: SUMMARY OF LOG ENTRIES
-- -----------------------------------------------------
WITH db_error_logs AS (
SELECT
*,
DATE(created_at) AS log_date,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 1 AND db_logs.severity = 1
AND db_logs.created_at BETWEEN '2024-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
ORDER BY db_logs.created_at DESC
), db_completed_logs AS (
SELECT
*,
DATE(created_at) AS log_date,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 1 AND db_logs.severity = 0
AND db_logs.created_at BETWEEN '2024-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
ORDER BY db_logs.created_at DESC
), db_logs AS (
SELECT
*,
DATE(created_at) AS log_date,
RANK() OVER (PARTITION BY SOURCE ORDER BY created_at DESC) AS rank
FROM db_logs
WHERE db_logs.kind = 0 AND db_logs.severity IN (0, 1)
AND db_logs.created_at BETWEEN '2024-07-04 00:00:00' AND '2025-07-04 23:59:59.999999'
ORDER BY db_logs.created_at DESC
)
SELECT
db_logs.SOURCE AS SOURCE,
CASE WHEN cl.created_at > el.created_at OR el.created_at IS NULL THEN 'finished' ELSE 'failed' END AS lastest_result,
db_logs.log_date AS log_date,
cl.created_at AS finished_at,
el.created_at AS failed_at,
CASE WHEN db_logs.severity = 0 THEN 'info' ELSE 'error' END AS kind,
CASE WHEN cl.created_at > el.created_at OR el.created_at IS NULL THEN cl.description ELSE el.description END AS description,
CASE WHEN cl.created_at > el.created_at OR el.created_at IS NULL THEN cl.metadata ELSE el.metadata END AS metadata
FROM db_logs
LEFT JOIN db_error_logs el ON db_logs.SOURCE = el.SOURCE AND el.rank = 1 AND el.log_date = db_logs.log_date
LEFT JOIN db_completed_logs cl ON db_logs.SOURCE = cl.SOURCE AND cl.rank = 1 AND cl.log_date = db_logs.log_date
WHERE db_logs.rank = 1

Add Comment

Please, Sign In to add comment