ps -ef > data/psef.txtTurn Semi-Structured Text Output Into Tables
Problem
You have textual information you want to perfom DuckDB SQL operations on.
Solution
Use DuckDB’s read_text() and extensive text tooling.
Discussion
You can read in one or more plain text files into DuckDB from the filesystem or stdin and perform DuckDB string operations on them to turn these files into tables.
For example, say you wanted to look at the processes on your system and operate on them as columns in a table. I’ve not had luck getting read_text() to use /dev/stdin, so let’s cache the results of a call to ps -ef a file and use that for the examples.
duckdb<<EOF
FROM read_text('data/psef.txt')
EOF┌───────────────┬───────────────────────────────────────────────────────────────────────┬────────┬─────────────────────┐
│ filename │ content │ size │ last_modified │
│ varchar │ varchar │ int64 │ timestamp │
├───────────────┼───────────────────────────────────────────────────────────────────────┼────────┼─────────────────────┤
│ data/psef.txt │ UID PID PPID C STIME TTY TIME CMD\n 0 1 … │ 183129 │ 2024-03-27 14:59:17 │
└───────────────┴───────────────────────────────────────────────────────────────────────┴────────┴─────────────────────┘If we had read a whole directory in, there’d be one record per-file.
There’s a handy regexp_split_to_table() function in DuckDB that will split a string on a regular expression and return a table, so let’s get that blob into some preliminary records:
duckdb<<EOF
FROM read_text('data/psef.txt')
SELECT
trim(regexp_split_to_table(content, '\n')) AS t
EOF┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ t │
│ varchar │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ UID PID PPID C STIME TTY TIME CMD │
│ 0 1 0 0 18Mar24 ?? 54:53.72 /sbin/launchd │
│ 0 120 1 0 18Mar24 ?? 63:51.92 /usr/libexec/logd │
│ 0 122 1 0 18Mar24 ?? 2:14.81 /usr/libexec/UserEventAgent (System) │
│ 0 124 1 0 18Mar24 ?? 0:17.72 /System/Library/PrivateFrameworks/Uninstall.framework/Resources/unins… │
│ 0 125 1 0 18Mar24 ?? 25:59.81 /System/Library/Frameworks/CoreServices.framework/Versions/A/Framewor… │
│ 0 126 1 0 18Mar24 ?? 4:08.04 /System/Library/PrivateFrameworks/MediaRemote.framework/Support/media… │
│ 0 129 1 0 18Mar24 ?? 0:48.69 /usr/sbin/systemstats --daemon │
│ 0 132 1 0 18Mar24 ?? 1:37.95 /usr/libexec/configd │
│ 0 134 1 0 18Mar24 ?? 3:48.50 /System/Library/CoreServices/powerd.bundle/powerd │
│ 0 135 1 0 18Mar24 ?? 0:00.02 /usr/libexec/IOMFB_bics_daemon │
│ 0 140 1 0 18Mar24 ?? 0:00.25 /usr/libexec/remoted │
│ 0 143 1 0 18Mar24 ?? 0:15.62 /usr/libexec/lightsoutmanagementd │
│ 0 146 1 0 18Mar24 ?? 0:20.89 /usr/libexec/watchdogd │
│ 0 150 1 0 18Mar24 ?? 88:13.62 /System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata… │
│ 0 152 1 0 18Mar24 ?? 0:01.27 /usr/libexec/kernelmanagerd │
│ 0 153 1 0 18Mar24 ?? 13:57.16 /usr/libexec/diskarbitrationd │
│ 0 158 1 0 18Mar24 ?? 0:34.25 /usr/sbin/syslogd │
│ 0 161 1 0 18Mar24 ?? 1:37.28 /usr/libexec/thermalmonitord │
│ 0 162 1 0 18Mar24 ?? 81:58.61 /usr/libexec/opendirectoryd │
│ · │
│ · │
│ · │
│ 501 97468 4384 0 Fri12PM ?? 0:00.22 Raycast Helper (Extensions) │
│ 501 98680 1 0 Tue09AM ?? 0:01.20 /usr/libexec/bluetoothuserd │
│ 0 99525 1 0 10:05PM ?? 0:00.05 /System/Library/PrivateFrameworks/CoreKDL.framework/Support/corekdld │
│ 501 99881 1 0 8:05AM ?? 0:00.10 /System/Library/PrivateFrameworks/Categories.framework/Versions/A/X… │
│ 501 98653 1225 0 19Mar24 ttys001 0:01.02 -zsh │
│ 501 49104 1225 0 Fri03PM ttys005 0:10.25 -zsh │
│ 501 83910 49104 0 10:45AM ttys005 0:00.06 duckdb │
│ 501 58952 58868 0 10:01AM ttys007 0:00.73 /bin/zsh │
│ 501 49994 1225 0 Fri01PM ttys009 0:00.74 -zsh │
│ 501 57634 1225 0 Fri02PM ttys011 0:00.66 -zsh │
│ 501 90443 58868 0 10:56AM ttys012 0:00.67 /bin/zsh -l │
│ 501 90869 90443 0 10:56AM ttys012 0:00.01 /bin/bash /usr/local/bin/quarto preview /Users/hrbrmstr/projects/co… │
│ 501 90881 90869 0 10:56AM ttys012 0:02.91 /Applications/quarto/bin/tools/aarch64/deno run --unstable --no-con… │
│ 501 92540 90881 0 10:59AM ttys012 0:00.38 /Library/Frameworks/R.framework/Resources/bin/exec/R --no-echo --no… │
│ 501 92550 92540 0 10:59AM ttys012 0:00.00 sh -c 'bash' -c 'ps -ef > data/psef.txt' 2>&1 │
│ 501 92551 92550 0 10:59AM ttys012 0:00.00 bash -c ps -ef > data/psef.txt │
│ 0 92552 92551 0 10:59AM ttys012 0:00.00 ps -ef │
│ 501 2109 86323 0 Tue09AM ttys014 0:00.00 ssh rud.is │
│ 501 86323 1225 0 18Mar24 ttys014 0:06.46 -zsh │
│ │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 820 rows (40 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘There’s also a handy regexp_extract() function which will let us specify a column, a regular expression (with capture groups) and an array of names (to use with the capture groups). We can immediately UNNEST that to get a proper table of columns representing the output of ps -ef:
duckdb<<EOF
FROM (
SELECT
UNNEST(regexp_extract(t, '^([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)', ['uid', 'pid', 'ppid', 'c', 'stime', 'tty', 'time', 'cmd']))
FROM (
FROM read_text('/tmp/ps')
SELECT
trim(regexp_split_to_table(content, '\n')) AS t
)
)
WHERE NOT uid = 'UID'
EOF┌─────────┬─────────┬─────────┬─────────┬─────────┬─────────┬──────────┬───────────────────────────────────────────────┐
│ uid │ pid │ ppid │ c │ stime │ tty │ time │ cmd │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼──────────┼───────────────────────────────────────────────┤
│ 0 │ 1 │ 0 │ 0 │ 18Mar24 │ ?? │ 54:45.36 │ /sbin/launchd │
│ 0 │ 120 │ 1 │ 0 │ 18Mar24 │ ?? │ 63:43.78 │ /usr/libexec/logd │
│ 0 │ 122 │ 1 │ 0 │ 18Mar24 │ ?? │ 2:14.54 │ /usr/libexec/UserEventAgent │
│ 0 │ 124 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:17.70 │ /System/Library/PrivateFrameworks/Uninstall… │
│ 0 │ 125 │ 1 │ 0 │ 18Mar24 │ ?? │ 25:56.78 │ /System/Library/Frameworks/CoreServices.fra… │
│ 0 │ 126 │ 1 │ 0 │ 18Mar24 │ ?? │ 4:07.90 │ /System/Library/PrivateFrameworks/MediaRemo… │
│ 0 │ 129 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:48.51 │ /usr/sbin/systemstats │
│ 0 │ 132 │ 1 │ 0 │ 18Mar24 │ ?? │ 1:37.66 │ /usr/libexec/configd │
│ 0 │ 134 │ 1 │ 0 │ 18Mar24 │ ?? │ 3:47.47 │ /System/Library/CoreServices/powerd.bundle/… │
│ 0 │ 135 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:00.02 │ /usr/libexec/IOMFB_bics_daemon │
│ 0 │ 140 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:00.25 │ /usr/libexec/remoted │
│ 0 │ 143 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:15.60 │ /usr/libexec/lightsoutmanagementd │
│ 0 │ 146 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:20.85 │ /usr/libexec/watchdogd │
│ 0 │ 150 │ 1 │ 0 │ 18Mar24 │ ?? │ 87:31.74 │ /System/Library/Frameworks/CoreServices.fra… │
│ 0 │ 152 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:01.27 │ /usr/libexec/kernelmanagerd │
│ 0 │ 153 │ 1 │ 0 │ 18Mar24 │ ?? │ 13:55.08 │ /usr/libexec/diskarbitrationd │
│ 0 │ 158 │ 1 │ 0 │ 18Mar24 │ ?? │ 0:34.19 │ /usr/sbin/syslogd │
│ 0 │ 161 │ 1 │ 0 │ 18Mar24 │ ?? │ 1:37.12 │ /usr/libexec/thermalmonitord │
│ 0 │ 162 │ 1 │ 0 │ 18Mar24 │ ?? │ 81:48.16 │ /usr/libexec/opendirectoryd │
│ 0 │ 163 │ 1 │ 0 │ 18Mar24 │ ?? │ 2:04.51 │ /System/Library/PrivateFrameworks/ApplePush… │
│ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │
│ 501 │ 95875 │ 1 │ 0 │ Tue09AM │ ?? │ 0:00.58 │ /System/Library/Frameworks/NetFS.framework/… │
│ 501 │ 95986 │ 1 │ 0 │ Tue09AM │ ?? │ 0:11.90 │ /System/Library/Frameworks/ExtensionFoundat… │
│ 501 │ 95987 │ 1 │ 0 │ Tue09AM │ ?? │ 0:27.81 │ /System/Library/PrivateFrameworks/VoiceShor… │
│ 501 │ 96457 │ 3610 │ 0 │ 19Mar24 │ ?? │ 0:40.61 │ /Applications/LogiTune.app/Contents/Framewo… │
│ 501 │ 97468 │ 4384 │ 0 │ Fri12PM │ ?? │ 0:00.22 │ Raycast │
│ 501 │ 98680 │ 1 │ 0 │ Tue09AM │ ?? │ 0:01.15 │ /usr/libexec/bluetoothuserd │
│ 0 │ 99525 │ 1 │ 0 │ 10:05PM │ ?? │ 0:00.05 │ /System/Library/PrivateFrameworks/CoreKDL.f… │
│ 501 │ 99881 │ 1 │ 0 │ 8:05AM │ ?? │ 0:00.10 │ /System/Library/PrivateFrameworks/Categorie… │
│ 501 │ 98653 │ 1225 │ 0 │ 19Mar24 │ ttys001 │ 0:01.02 │ -zsh │
│ 501 │ 49104 │ 1225 │ 0 │ Fri03PM │ ttys005 │ 0:10.15 │ -zsh │
│ 0 │ 79099 │ 49104 │ 0 │ 10:36AM │ ttys005 │ 0:00.00 │ ps │
│ 501 │ 58952 │ 58868 │ 0 │ 10:01AM │ ttys007 │ 0:00.73 │ /bin/zsh │
│ 501 │ 49994 │ 1225 │ 0 │ Fri01PM │ ttys009 │ 0:00.74 │ -zsh │
│ 501 │ 74831 │ 58868 │ 0 │ 10:29AM │ ttys010 │ 0:00.66 │ /bin/zsh │
│ 501 │ 75269 │ 74831 │ 0 │ 10:29AM │ ttys010 │ 0:00.00 │ /bin/bash │
│ 501 │ 75281 │ 75269 │ 0 │ 10:29AM │ ttys010 │ 0:03.54 │ /Applications/quarto/bin/tools/aarch64/deno │
│ 501 │ 57634 │ 1225 │ 0 │ Fri02PM │ ttys011 │ 0:00.66 │ -zsh │
│ 501 │ 2109 │ 86323 │ 0 │ Tue09AM │ ttys014 │ 0:00.00 │ ssh │
│ 501 │ 86323 │ 1225 │ 0 │ 18Mar24 │ ttys014 │ 0:06.46 │ -zsh │
│ │ │ │ │ │ │ │ │
├─────────┴─────────┴─────────┴─────────┴─────────┴─────────┴──────────┴───────────────────────────────────────────────┤
│ 834 rows (40 shown) 8 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Now, you can go about CASTing various columns to proper types and SQL hack on the output of this and other system commands.