analytics icon indicating copy to clipboard operation
analytics copied to clipboard

Published 20 hours ago • verified publisher icon plausible

PoC: keep log_comment metadata in ClickHouse queries

Open aerosol opened this issue 7 months ago • 0 comments

Iteration on https://github.com/plausible/analytics/pull/3699

@macobo feel free to take it from here, if this is what you were looking for. There's certainly docs and tests missing 😜 Mind you, neither IngestRepo, ImportDeletionRepo nor AsyncInsertRepo are instrumented but I guess it's reads that you're interested in.

SELECT
    event_time,
    current_database,
    log_comment
FROM system.query_log
WHERE (type = 1) AND (length(log_comment) > 2) AND (current_database = 'plausible_events_db')
ORDER BY event_time DESC
LIMIT 20

Query id: f960341e-e871-4f1b-bef7-ee5ddce8f089

┌──────────event_time─┬─current_database────┬─log_comment────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 2024-01-18 06:43:03 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/entry-pages","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"goal\":\"Visit /register\",\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}} │
└─────────────────────┴─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
┌──────────event_time─┬─current_database────┬─log_comment────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 2024-01-18 06:42:56 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/operating-systems","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"goal\":\"Visit /register\",\"source\":\"Twitter\",\"page\":\"~a\"}","period":"year","with_imported":"true"}} │
│ 2024-01-18 06:42:56 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/referrers/Twitter","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"goal\":\"Visit /register\",\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","referrer":"Twitter","with_imported":"true"}} │
│ 2024-01-18 06:42:56 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/exit-pages","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"goal\":\"Visit /register\",\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}} │
│ 2024-01-18 06:42:56 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/cities","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"goal\":\"Visit /register\",\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}} │
└─────────────────────┴─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
┌──────────event_time─┬─current_database────┬─log_comment────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 2024-01-18 06:42:51 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/current-visitors","query_type":"current_visitors"}}                                                                                                                                                  │
│ 2024-01-18 06:42:48 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/referrers/Twitter","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","referrer":"Twitter","with_imported":"true"}} │
│ 2024-01-18 06:42:48 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/operating-systems","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"source\":\"Twitter\",\"page\":\"~a\"}","period":"year","with_imported":"true"}}                  │
│ 2024-01-18 06:42:48 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/cities","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}}                 │
│ 2024-01-18 06:42:48 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/exit-pages","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"source\":\"Twitter\",\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}}             │
└─────────────────────┴─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
┌──────────event_time─┬─current_database────┬─log_comment────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 2024-01-18 06:42:45 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/suggestions/source","query_type":"filter_suggestions","date":"2024-01-18","domain":"dummy.site","filter_name":"source","filters":"{\"page\":\"~a\"}","period":"year","q":"","with_imported":"true"}} │
│ 2024-01-18 06:42:30 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/exit-pages","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}}                                    │
│ 2024-01-18 06:42:30 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/cities","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}}                                        │
│ 2024-01-18 06:42:30 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/operating-systems","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"page\":\"~a\"}","period":"year","with_imported":"true"}}                                         │
│ 2024-01-18 06:42:30 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/sources","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{\"page\":\"~a\"}","limit":"9","period":"year","with_imported":"true"}}                                       │
│ 2024-01-18 06:42:21 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/operating-systems","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{}","period":"year","with_imported":"true"}}                                                        │
│ 2024-01-18 06:42:21 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/cities","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{}","limit":"9","period":"year","with_imported":"true"}}                                                       │
│ 2024-01-18 06:42:21 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/exit-pages","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{}","limit":"9","period":"year","with_imported":"true"}}                                                   │
│ 2024-01-18 06:42:21 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/exit-pages","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{}","limit":"9","period":"year","with_imported":"true"}}                                                   │
│ 2024-01-18 06:42:21 │ plausible_events_db │ {"log_comment":{"request_path":"/api/stats/dummy.site/sources","query_type":"breakdown","date":"2024-01-18","domain":"dummy.site","filters":"{}","limit":"9","period":"year","with_imported":"true"}}                                                      │
└─────────────────────┴─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

aerosol avatar Jan 18 '24 06:01 aerosol