commit b134593dad3b349ea9e599ab99f99bad7699f4bb
parent d5884b7daeb4652b94ad48989b09a740bcddfe71
Author: Silas Brack <silasbrack@gmail.com>
Date: Sun, 5 Jul 2026 18:14:15 +0200
refactor: query-logs as pure DuckDB macros
Replace bash URL generation with DuckDB table macros. Bash just
launches DuckDB with an init file. All logic lives in SQL:
query-logs
D SELECT * FROM app('2026-04-19 15:00', '2026-04-19 16:00') WHERE message ILIKE '%error%';
D SELECT status, count(*) FROM nginx('2026-07-05 16:00', '2026-07-05 17:00') GROUP BY status;
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
| M | flake.nix | | | 46 | +++++++++++++++------------------------------- |
1 file changed, 15 insertions(+), 31 deletions(-)
diff --git a/flake.nix b/flake.nix
@@ -76,42 +76,25 @@
pkgs = import nixpkgs {
inherit system;
};
- query-logs = pkgs.writeShellScriptBin "query-logs" ''
- set -euo pipefail
- BASE="http://10.100.0.7:9090"
- SINCE="''${1:-$(date -d '24 hours ago' +%Y-%m-%dT%H)}"
- UNTIL="''${2:-$(date +%Y-%m-%dT%H)}"
-
- gen_urls() {
- local dir=$1 ext=$2 urls="" current end url
- current=$(date -d "''${SINCE}:00:00" +%s)
- end=$(date -d "''${UNTIL}:00:00" +%s)
- while [ "$current" -le "$end" ]; do
- url="$BASE/$dir/$(date -d @$current +%Y/%m/%d/%H).$ext"
- if ${pkgs.curl}/bin/curl -sf --head "$url" > /dev/null 2>&1; then
- urls="$urls'$url',"
- fi
- current=$((current + 3600))
- done
- echo "[''${urls%,}]"
- }
-
- APP_URLS=$(gen_urls app json.gz)
- NGINX_URLS=$(gen_urls nginx log.gz)
-
- ${pkgs.duckdb}/bin/duckdb -init <(cat <<DUCKDB
+ initSql = pkgs.writeText "query-logs-init.sql" ''
LOAD httpfs;
- CREATE TABLE app AS
+ CREATE MACRO urls(dir, ext, since, until) AS
+ list_transform(
+ generate_series(since::TIMESTAMP, until::TIMESTAMP, INTERVAL '1 HOUR'),
+ ts -> 'http://10.100.0.7:9090/' || dir || '/' || strftime(ts, '%Y/%m/%d/%H') || '.' || ext
+ );
+
+ CREATE MACRO app(since, until) AS TABLE
SELECT
to_timestamp(j."__REALTIME_TIMESTAMP"::BIGINT / 1000000) as timestamp,
j."MESSAGE" as message,
j."PRIORITY" as priority,
j."SYSLOG_IDENTIFIER" as identifier
- FROM read_json($APP_URLS, format='newline_delimited', ignore_errors=true,
+ FROM read_json(urls('app', 'json.gz', since, until), format='newline_delimited', ignore_errors=true,
columns={__REALTIME_TIMESTAMP: 'VARCHAR', MESSAGE: 'VARCHAR', PRIORITY: 'VARCHAR', SYSLOG_IDENTIFIER: 'VARCHAR'}) j;
- CREATE TABLE nginx AS
+ CREATE MACRO nginx(since, until) AS TABLE
SELECT
regexp_extract(line, '^\S+', 0) as remote_addr,
regexp_extract(line, '\[([^\]]+)\]', 1) as timestamp,
@@ -119,12 +102,13 @@
regexp_extract(line, '"(\S+) (\S+) \S+"', 2) as path,
CAST(regexp_extract(line, '" (\d+) ', 1) AS INTEGER) as status,
CAST(regexp_extract(line, '" \d+ (\d+)', 1) AS INTEGER) as bytes,
- regexp_extract(line, '"([^"]*)"[^"]*\$', 1) as user_agent
- FROM read_csv($NGINX_URLS, header=false, columns={'line': 'VARCHAR'}, auto_detect=false,
+ regexp_extract(line, '"([^"]*)"[^"]*$', 1) as user_agent
+ FROM read_csv(urls('nginx', 'log.gz', since, until), header=false, columns={'line': 'VARCHAR'}, auto_detect=false,
delim=chr(1), quote=chr(2), escape=chr(2)) t
WHERE line IS NOT NULL AND length(line) > 0;
- DUCKDB
- )
+ '';
+ query-logs = pkgs.writeShellScriptBin "query-logs" ''
+ ${pkgs.duckdb}/bin/duckdb -init ${initSql}
''
;
in
