Steve Purcell purcell
purcell
/ wrap-pg-auth
Created
August 17, 2023 14:23
Invoke a postgres command via a bastion host with auth obtained from 1password
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash -e | |
| error_exit() { | |
| echo "$@" >&2 | |
| exit 1 | |
| } | |
| usage_exit() { | |
| cat <<EOF >&2 | |
| Allows you to run a local postgres command, e.g. psql or pg_dump, |
purcell
/ access-log-to-parquet.sql
Created
September 4, 2023 19:23
Use DuckDB to convert a compressed web access log in Combined Log Format to Parquet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| COPY ( | |
| WITH | |
| -- Read the raw log line by line by abusing CSV parser | |
| raw_log AS ( | |
| FROM read_csv_auto('/tmp/log/access.log-20230904.gz', header=false, delim='\0') | |
| ) | |
| , combined_log AS ( | |
| SELECT regexp_extract(column0 | |
| , '^(\S+) (\S+) (\S+) \[(.*?)\] "([A-Z]+?) (.*?) HTTP/(.*?)" (\d+) (\d+) "(.*?)" "(.*?)"$' | |
| , [ 'ip', 'identity', 'userid', 'timestamp', 'method' |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Latest ruby from homebrew: | |
| $ ruby --version | |
| ruby 3.4.2 (2025-02-15 revision d2930f8e7a) +PRISM [arm64-darwin24] | |
| $ cat Gemfile | |
| source "https://rubygems.org" | |
| gem "pg_query" | |
| $ bundle update --verbose | |
| Running `bundle update --verbose` with bundler 2.6.3 |
OlderNewer