commit d5884b7daeb4652b94ad48989b09a740bcddfe71
parent fe789ee72a77a5247e6e0019adfe9bb6ed125298
Author: Silas Brack <silasbrack@gmail.com>
Date: Sun, 5 Jul 2026 18:08:49 +0200
refactor: simplify query-logs, keep SQL in DuckDB
Bash only generates the URL list (with HEAD checks to skip missing
files). All table definitions and SQL live in DuckDB init script.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
| M | flake.nix | | | 71 | +++++++++++++++++++++++++++++------------------------------------------ |
1 file changed, 29 insertions(+), 42 deletions(-)
diff --git a/flake.nix b/flake.nix
@@ -82,62 +82,49 @@
SINCE="''${1:-$(date -d '24 hours ago' +%Y-%m-%dT%H)}"
UNTIL="''${2:-$(date +%Y-%m-%dT%H)}"
- collect_urls() {
- local dir=$1 ext=$2 urls="" current end path url
+ 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
- path=$(date -d "@$current" +%Y/%m/%d/%H)
- url="$BASE/$dir/$path.$ext"
+ 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',"
+ urls="$urls'$url',"
fi
current=$((current + 3600))
done
- echo "''${urls%,}"
+ echo "[''${urls%,}]"
}
- echo "Scanning $SINCE → $UNTIL ..."
- app_urls=$(collect_urls app json.gz)
- nginx_urls=$(collect_urls nginx log.gz)
+ APP_URLS=$(gen_urls app json.gz)
+ NGINX_URLS=$(gen_urls nginx log.gz)
- init=""
- init="$init LOAD httpfs;"
+ ${pkgs.duckdb}/bin/duckdb -init <(cat <<DUCKDB
+ LOAD httpfs;
- if [ -n "$app_urls" ]; then
- init="$init CREATE TABLE app AS SELECT
- to_timestamp(json_extract_string(j, '\$.__REALTIME_TIMESTAMP')::BIGINT / 1000000) as timestamp,
- json_extract_string(j, '\$.MESSAGE') as message,
- json_extract_string(j, '\$.PRIORITY') as priority,
- json_extract_string(j, '\$.SYSLOG_IDENTIFIER') as identifier
- FROM read_json_auto([$app_urls], format='newline_delimited', ignore_errors=true) j;"
- echo " app: ready"
- else
- echo " app: no logs found"
- fi
+ CREATE TABLE app AS
+ 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,
+ columns={__REALTIME_TIMESTAMP: 'VARCHAR', MESSAGE: 'VARCHAR', PRIORITY: 'VARCHAR', SYSLOG_IDENTIFIER: 'VARCHAR'}) j;
- if [ -n "$nginx_urls" ]; then
- init="$init CREATE TABLE nginx AS SELECT
+ CREATE TABLE nginx AS
+ SELECT
regexp_extract(line, '^\S+', 0) as remote_addr,
regexp_extract(line, '\[([^\]]+)\]', 1) as timestamp,
- regexp_extract(line, '\"(\S+) (\S+) \S+\"', 1) as method,
- 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, '\"([^\"]*)\"\s*\$', 1) as user_agent
- FROM read_csv([$nginx_urls], header=false, columns={'line': 'VARCHAR'}, auto_detect=false, delim=E'\x01', quote=E'\x02', escape=E'\x02') t
- WHERE line IS NOT NULL AND length(line) > 0;"
- echo " nginx: ready"
- else
- echo " nginx: no logs found"
- fi
-
- if [ -z "$app_urls" ] && [ -z "$nginx_urls" ]; then
- echo "No logs found for the given range."
- exit 0
- fi
-
- ${pkgs.duckdb}/bin/duckdb -init <(echo "$init")
+ regexp_extract(line, '"(\S+) (\S+) \S+"', 1) as method,
+ 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,
+ delim=chr(1), quote=chr(2), escape=chr(2)) t
+ WHERE line IS NOT NULL AND length(line) > 0;
+ DUCKDB
+ )
''
;
in
