How to Use SQLite for Log Analysis From the Shell Fast

The first question about a log is easy with grep and sort | uniq -c. The fifth, "of the 500 errors after 14:00 from clients that also hit /login, which paths were slowest?", turns a pipeline into a puzzle. Loading the log into SQLite once makes every question after the first cheap and exact. This post shows the load and the queries, as a use of SQLite as a script store. Examples use SQLite 3.46 and nginx's combined log format.

Parse into columns

awk '{
  ip=$1; ts=substr($4,2); gsub(/\[|\]/,"",ts)
  split(ts, t, /[\/:]/)       # 03/Nov/2026:09:12:44 -> day mon year h m s
  months="JanFebMarAprMayJunJulAugSepOctNovDec"
  mon=(index(months,t[2])+2)/3
  iso=sprintf("%s-%02d-%sT%s:%s:%s", t[3], mon, t[1], t[4], t[5], t[6])
  method=substr($6,2); path=$7; status=$9; bytes=($10=="-")?0:$10
  printf "%s\t%s\t%s\t%s\t%s\t%s\n", iso, ip, method, path, status, bytes
}' access.log > access.tsv
head -n 2 access.tsv

2026-11-03T09:12:44	203.0.113.9	GET	/index.html	200	5120
2026-11-03T09:12:45	198.51.100.4	POST	/login	302	0

Parse the log format once into tab-separated fields with ISO timestamps, which SQLite understands. Tabs are safe separators here because the fields do not contain them. Real logs have oddities (a request line missing fields, quoted user agents with spaces), so validate: awk -F'\t' 'NF!=6' access.tsv | head shows malformed lines. For a complex format, a few lines of Python with a regular expression are easier than awk; see regex in awk.

Import

sqlite3 logs.db <<'SQL'
drop table if exists hits;
create table hits (ts text, ip text, method text, path text, status integer, bytes integer);
.mode tabs
.import access.tsv hits
create index hits_ts on hits(ts);
create index hits_status on hits(status);
create index hits_path on hits(path);
SQL
sqlite3 logs.db "select count(*) from hits"

The table exists first, so the columns have the right types, and .mode tabs sets the separator for the import. When the table exists, .import treats every line as data; with no table, SQLite would create one using the first line as headers. Indexes after loading are faster than before. A few million lines import in seconds.

Ask questions

sqlite3 -header -column logs.db "
select status, count(*) as n from hits group by status order by n desc;
"
sqlite3 -header -column logs.db "
select path, count(*) as n, round(avg(bytes)) as avg_bytes
from hits where status >= 500 and ts >= '2026-11-03T14:00'
group by path order by n desc limit 10;
"
sqlite3 -header -column logs.db "
select strftime('%Y-%m-%d %H:%M', ts) as minute, count(*) as reqs
from hits group by minute order by reqs desc limit 5;
"

minute            reqs
----------------  ----
2026-11-03 14:07  4120
2026-11-03 14:08  3988

Because the timestamps are ISO text, comparisons and sorting work as strings, and strftime truncates to the bucket you want. The per-minute count finds traffic spikes; the second query lists failing paths after a given time. Joins let you correlate: clients that hit /login and then errors, with where ip in (select ip from hits where path='/login').

Output for other tools

sqlite3 -csv -header logs.db "select ts, ip, path from hits where status = 500" > errors.csv
sqlite3 -json logs.db "select status, count(*) n from hits group by status" | jq .

-csv and -json (SQLite 3.33 and later) feed spreadsheets and jq. For a chart, pipe numbers into gnuplot.

Where this does not apply

One question needs no database: awk '$9==500' access.log | wc -l is faster than parsing and loading. Logs of many gigabytes strain a row store, and DuckDB or a log system handles them better; see DuckDB on the command line. A database of access logs holds IP addresses, which are personal data in some jurisdictions; protect and expire it. For a live view of a log, awk reports are the right tool.

Why a database beats grep for repeated questions

A log file is a good place to look at one thing and a poor one to ask twenty different questions. Each grep or awk pass reads the whole file again, and each question needs a new pipeline. Loading the log into SQLite once pays the parsing cost a single time, and after that every question is a query: errors per hour, the slowest paths, the clients behind a spike, a comparison of two periods. SQLite handles tables of many millions of rows on a laptop, and indexes make selective queries instant. The cost is the parsing step, where you decide the columns, and a bad decision there, such as keeping the timestamp as a string in a format that does not sort, makes the later queries awkward. Spend the effort at that step: normalize timestamps to ISO 8601 in UTC, split the request line into method, path and query, convert sizes and durations to numbers and keep the original line in a column for reference.

The same approach works for any line-oriented output, such as a build log, a firewall log or the output of a monitoring tool, and the schema is the only part that changes.

Getting the data in quickly

Use awk or a small script to turn each line into tab-separated fields, then load with the shell's .import. Choose a separator that cannot occur in the data, since a comma in a request path breaks CSV unless it is quoted, and tabs are safer for logs. Set the mode before importing with .mode tabs, create the table first with typed columns so the importer does not guess, and skip a header with the --skip 1 option if there is one. For millions of rows, speed matters: wrap the load in one transaction, which the importer does for you, and for a throwaway database turn off the safety settings during the load with PRAGMA journal_mode = OFF and PRAGMA synchronous = OFF, accepting that a crash during the load means starting again. Create indexes after the data is loaded, because building an index once over the full table is much faster than updating it for every inserted row. Check the count against wc -l of the input to catch lines that failed to parse.

Asking good questions and passing results on

Start with the questions you usually ask: the top paths by count, the status code distribution, the response time percentiles, the busiest minutes and the clients making the most requests. strftime('%Y-%m-%d %H:00', ts) groups by hour, and a window over ordered durations gives approximate percentiles when the version supports window functions. A query that joins the log table with a small table of known bad addresses finds suspicious clients in one statement. Print results with .mode column and headers for reading, or -json and -csv when another tool consumes them, and save useful queries as files in the same directory as the log so the analysis can be repeated. For logs larger than memory or in columnar formats, DuckDB handles them more efficiently, as the companion post shows, and for ongoing analysis a proper log store is the better home. A database built from yesterday's log is a snapshot, so keep the load script, and rebuild when the log changes.

A small worked example and its limits

Take an access log in the common format. An awk program splits each line into the client, the time, the method, the path, the status and the bytes, converts the time to an ISO string, and prints the fields separated by tabs. Importing that into a table with indexes on the time and the path takes a minute for a few million lines. A query that counts requests by status per hour then returns in milliseconds, and one that lists the paths with the most errors follows. The same file would need a fresh pass of awk for each of those questions. The limit is the first step: if the log has several formats, multi-line entries such as stack traces, or fields that contain your separator, the parser needs care, and bad lines should be written to a reject file, not dropped silently, so you know what the table does not contain.

Remember that the analysis is only as good as the load. Count the rows, spot-check a few against the original, and look at the earliest and latest timestamps to confirm the load covered the period you meant.

See importing files in the sqlite3 shell and date and time functions.