Import CSV into sqlite3 and Aggregate It: The Next Step After sort | uniq -c

2026-10-24 Reading time: About 15 min Difficulty: Intermediate+

What You'll Learn

• How to decide which counts sort | uniq -c can handle and which ones call for SQL
• How to load a CSV file made with awk into a sqlite3 table in one line
• How to use GROUP BY / JOIN for two-axis counts, joins with another table, and ratios, then send the result back into a pipe

First, some terms.

• Table: a container of data made of rows and columns. One CSV file maps to one table
• Column: a vertical item in a table, such as ip or status. The same thing as a "field" in CSV
• SQL: a query language for selecting, grouping, and counting rows in tables. Statements that start with SELECT ... are SQL
• Dot-command: a sqlite3-specific command that starts with ., such as .import. It is not SQL
• Aggregation: grouping rows and computing counts, totals, or ratios

How do you choose between sort | uniq -c and sqlite3?

Conclusion: Listing counts vertically: pipes. Side-by-side values, joins, ratios: sqlite3.

There are three criteria: do you want several aggregated values side by side on one line, do you need to match against another table, and do you need a ratio? If any of them applies, use SQL.

Cross-tabulation: a count that puts the first axis (hour) in the rows and the second axis (ok / errors, and so on) in the columns, with the counts where they meet.

Preparation: create a sample access.log

Conclusion: Practice on a small hand-made nginx access.log in the default format.

Create the 18-line access.log with the following command. The IP addresses come from ranges reserved for documentation (RFC 5737). The same steps work for logs in nginx's default combined format.

$ cat > access.log <<'EOF'
203.0.113.10 - - [24/Oct/2026:09:02:11 +0900] "GET / HTTP/1.1" 200 5120 "-" "Mozilla/5.0"
203.0.113.10 - - [24/Oct/2026:09:02:12 +0900] "GET /css/main.css HTTP/1.1" 200 1840 "-" "Mozilla/5.0"
198.51.100.7 - - [24/Oct/2026:09:15:40 +0900] "GET /login HTTP/1.1" 200 2310 "-" "curl/8.5.0"
198.51.100.7 - - [24/Oct/2026:09:15:41 +0900] "POST /login HTTP/1.1" 500 512 "-" "curl/8.5.0"
192.0.2.44 - - [24/Oct/2026:09:48:03 +0900] "GET /docs HTTP/1.1" 404 320 "-" "Mozilla/5.0"
203.0.113.10 - - [24/Oct/2026:10:05:27 +0900] "GET /api/items HTTP/1.1" 200 9800 "-" "Mozilla/5.0"
203.0.113.25 - - [24/Oct/2026:10:06:02 +0900] "GET / HTTP/1.1" 304 0 "-" "Mozilla/5.0"
198.51.100.7 - - [24/Oct/2026:10:11:19 +0900] "POST /login HTTP/1.1" 500 512 "-" "curl/8.5.0"
198.51.100.7 - - [24/Oct/2026:10:11:20 +0900] "POST /login HTTP/1.1" 500 512 "-" "curl/8.5.0"
192.0.2.44 - - [24/Oct/2026:10:30:45 +0900] "GET /docs/install HTTP/1.1" 200 7200 "-" "Mozilla/5.0"
203.0.113.25 - - [24/Oct/2026:10:31:10 +0900] "GET /api/items HTTP/1.1" 200 9800 "-" "Mozilla/5.0"
203.0.113.10 - - [24/Oct/2026:10:42:33 +0900] "GET /favicon.ico HTTP/1.1" 404 150 "-" "Mozilla/5.0"
198.51.100.99 - - [24/Oct/2026:13:01:05 +0900] "GET /wp-login.php HTTP/1.1" 404 150 "-" "python-requests/2.31"
198.51.100.99 - - [24/Oct/2026:13:01:06 +0900] "GET /.env HTTP/1.1" 404 150 "-" "python-requests/2.31"
203.0.113.10 - - [24/Oct/2026:13:20:14 +0900] "GET /api/items HTTP/1.1" 200 9800 "-" "Mozilla/5.0"
192.0.2.44 - - [24/Oct/2026:13:22:50 +0900] "GET / HTTP/1.1" 200 5120 "-" "Mozilla/5.0"
203.0.113.25 - - [24/Oct/2026:13:40:09 +0900] "GET /api/items HTTP/1.1" 500 512 "-" "Mozilla/5.0"
203.0.113.10 - - [24/Oct/2026:13:55:31 +0900] "GET /css/main.css HTTP/1.1" 304 0 "-" "Mozilla/5.0"
EOF
$ wc -l access.log

18 access.log

Quoting the end marker, as in <<'EOF', keeps the shell from expanding $ and " inside, so the text is written to the file as is. Use wc -l to confirm that it has 18 lines.

Start with a one-axis count using the familiar method. The 9th space-separated field is the status code.

$ cut -d' ' -f9 access.log | sort | uniq -c | sort -rn

      8 200
      4 500
      4 404
      2 304

For one axis, this is enough. From here on, SQL handles the counts that are awkward to write this way.

Step 1: Turn access.log into CSV with awk

Conclusion: Extract only the fields you need with awk; line 1 holds the column names.

$ awk 'BEGIN { OFS = ","; print "ip,time,method,path,status,bytes" }
{ sub(/^\[/, "", $4); sub(/^"/, "", $6); print $1, $4, $6, $7, $9, $10 }' access.log > access.csv
$ head -n 4 access.csv

ip,time,method,path,status,bytes
203.0.113.10,24/Oct/2026:09:02:11,GET,/,200,5120
203.0.113.10,24/Oct/2026:09:02:12,GET,/css/main.css,200,1840
198.51.100.7,24/Oct/2026:09:15:40,GET,/login,200,2310

• $1 / $4 / $6 / $7 / $9 / $10: the 1st, 4th, 6th, 7th, 9th, and 10th space-separated fields. In order: IP, time, method, path, status, and bytes
• OFS = ",": sets the output separator to a comma
• print "ip,time,...": prints the header row once in the BEGIN block. These names become the table's column names
• sub(/^\[/, "", $4): removes the leading [ from the time. sub(/^"/, "", $6) removes the leading " from the method

Check the line counts too. The CSV has one extra line for the header row.

$ wc -l access.log access.csv

  18 access.log
  19 access.csv
  37 total

Step 2: Load the CSV into a table with .import

Conclusion: .import --csv creates the table. CSV line 1 becomes the column names.

$ sqlite3 logs.db ".import --csv access.csv access"
$ sqlite3 -csv logs.db "SELECT COUNT(*) FROM access;"

18

• logs.db: the database file. One file stores all the tables; it is created if it does not exist
• ".import --csv access.csv access": reads access.csv as CSV and loads it into a table named access. It prints nothing on success
• -csv: prints the results in CSV format. The SELECT on the second line is SQL that counts rows

According to the official documentation, if the target table does not exist yet, .import creates it and uses the first CSV line as the column names. Only lines 2 and later become data, so the count is 18, the same as the number of lines in access.log. Importing into the same table twice doubles the count, so when you redo the import, drop the table first as shown in Troubleshooting.

Now check the column types. pragma_table_info('table') is a built-in SQLite function that lists a table's column names and types. -header is the option that adds the column names as the first line of the result.

$ sqlite3 -csv -header logs.db "SELECT name, type FROM pragma_table_info('access');"

name,type
ip,TEXT
time,TEXT
method,TEXT
path,TEXT
status,TEXT
bytes,TEXT

Every auto-created column is TEXT (a string). Numbers such as status and bytes are stored as strings too. In the 3.53.0 and later source (zAutoColumn in src/shell.c.in), the table is created without column types, so the type field is empty, but the values are still stored as strings. When you need numbers, convert them, for example with CAST(bytes AS INTEGER).

Step 3: Count by status and by IP with GROUP BY

Conclusion: GROUP BY merges equal values; COUNT(*) counts. LIMIT n keeps the top rows.

The SQL in this article is built from the following parts. Read them from left to right and you can follow them the same way as a pipe.

$ sqlite3 -csv -header logs.db "SELECT status, COUNT(*) AS count FROM access GROUP BY status ORDER BY count DESC, status;"

status,count
200,8
404,4
500,4
304,2

The counts match cut | sort | uniq -c. The AS in COUNT(*) AS count gives the result column the name count. You can use that name in ORDER BY as well. The second key status in ORDER BY count DESC, status fixes the order of rows that have the same count. Without it, the order of tied rows is not guaranteed.

The top 3 IP addresses use the same shape.

$ sqlite3 -csv -header logs.db "SELECT ip, COUNT(*) AS count FROM access GROUP BY ip ORDER BY count DESC, ip LIMIT 3;"

ip,count
203.0.113.10,6
198.51.100.7,4
192.0.2.44,3

For counts per hour, cut the two hour characters out of the time string with substr and group by them. In 24/Oct/2026:09:02:11, the hour is the 2 characters starting at position 13.

$ sqlite3 -csv -header logs.db "SELECT substr(time, 13, 2) AS hour, COUNT(*) AS count FROM access GROUP BY hour ORDER BY hour;"

hour,count
09,5
10,7
13,6

Step 4: Count along two axes: hour × status

Conclusion: Add the second axis as SUM(condition). True is 1, so the sum counts matches.

If you only need the hour and status combinations listed vertically, a pipe can do it. Join the two keys on one line with awk and count them with sort | uniq -c. $4 starts with [, so the hour is the 2 characters starting at position 14.

$ awk '{ print substr($4, 14, 2), $9 }' access.log | sort | uniq -c

      3 09 200
      1 09 404
      1 09 500
      3 10 200
      1 10 304
      1 10 404
      2 10 500
      2 13 200
      1 13 304
      2 13 404
      1 13 500

But if you want "ok", "errors", and "total" side by side on one line per hour (cross-tabulation), this output needs further reshaping. In SQL, you just add columns.

$ sqlite3 -csv -header logs.db "SELECT substr(time, 13, 2) AS hour, SUM(status = '200') AS ok, SUM(CAST(status AS INTEGER) >= 400) AS errors, COUNT(*) AS total FROM access GROUP BY hour ORDER BY hour;"

hour,ok,errors,total
09,3,2,5
10,3,3,7
13,2,3,6

• status = '200': status is a string, so write the value you compare it with as the quoted string '200'
• CAST(status AS INTEGER) >= 400: when you compare ranges, convert to a number first
• ok + errors does not always equal total, because 304 (cached) matches neither condition

The error rate (ratio) per IP follows the same idea.

$ sqlite3 -csv -header logs.db "SELECT ip, COUNT(*) AS total, SUM(CAST(status AS INTEGER) >= 400) AS errors, ROUND(100.0 * SUM(CAST(status AS INTEGER) >= 400) / COUNT(*), 1) AS error_pct FROM access GROUP BY ip ORDER BY error_pct DESC, total DESC, ip;"

ip,total,errors,error_pct
198.51.100.99,2,2,100.0
198.51.100.7,4,3,75.0
192.0.2.44,3,1,33.3
203.0.113.25,3,1,33.3
203.0.113.10,6,1,16.7

The multiplication uses 100.0 * because dividing one integer by another drops the fractional part. 198.51.100.7 got a 500 from POST /login three times in a row. A one-axis count does not reveal a skew like this.

Step 5: Join with a country lookup table using JOIN

Conclusion: Load the lookup CSV as a second table and JOIN ... ON the shared column.

Create countries.csv, a lookup table from IP address to country code, with a here-document just like access.log.

$ cat > countries.csv <<'EOF'
ip,country
203.0.113.10,JP
203.0.113.25,JP
198.51.100.7,US
192.0.2.44,BR
EOF
$ wc -l countries.csv

5 countries.csv

Load it into the same database as a second table and connect the two on the ip column.

$ sqlite3 logs.db ".import --csv countries.csv countries"
$ sqlite3 -csv -header logs.db "SELECT c.country, COUNT(*) AS count FROM access AS a JOIN countries AS c ON a.ip = c.ip GROUP BY c.country ORDER BY count DESC;"

country,count
JP,9
US,4
BR,3

The total is 16, which is 2 fewer than the overall 18. JOIN keeps only rows that match in both tables, so rows whose IP is missing from the lookup table were dropped. Use LEFT JOIN to find them.

$ sqlite3 -csv -header logs.db "SELECT a.ip, COUNT(*) AS count FROM access AS a LEFT JOIN countries AS c ON a.ip = c.ip WHERE c.ip IS NULL GROUP BY a.ip;"

ip,count
198.51.100.99,2

LEFT JOIN keeps every row of the left table (access) and fills the right table's columns with NULL (no value) when there is no match. WHERE c.ip IS NULL picks out only those "no match" rows.

$ cp countries.csv countries2.csv
$ echo '203.0.113.10,JP' >> countries2.csv
$ sqlite3 dup.db ".import --csv access.csv access"
$ sqlite3 dup.db ".import --csv countries2.csv countries"
$ sqlite3 -csv -header dup.db "SELECT c.country, COUNT(*) AS count FROM access AS a JOIN countries AS c ON a.ip = c.ip GROUP BY c.country ORDER BY count DESC;"

country,count
JP,15
US,4
BR,3

Before joining, check the lookup table's keys for duplicates. HAVING is the clause that filters the groups after GROUP BY (the group version of WHERE).

$ sqlite3 -csv -header dup.db "SELECT ip, COUNT(*) AS n FROM countries GROUP BY ip HAVING n > 1;"

ip,n
203.0.113.10,2

Running the same SQL on the original logs.db prints nothing, which means there are no duplicates.

Step 6: Output CSV and hand it back to sort

Conclusion: sqlite3 -csv prints plain CSV for sort and head. Strip \r first.

$ sqlite3 -csv logs.db "SELECT path, SUM(CAST(bytes AS INTEGER)) FROM access GROUP BY path;" | tr -d '\r' | sort -t, -k2,2nr | head -n 3

/api/items,29912
/,10240
/docs/install,7200

• Without -header, no header row is printed, so you can pass the output straight to sort
• sort -t, -k2,2nr: sorts by the 2nd comma-separated column as a number, in descending order
• tr -d '\r': the official documentation for the 3.54 series (the guide to CLI output formats) says CSV output always ends lines with CRLF (\r\n). With 3.45.1, -csv ended lines with LF only (checked with od -c). The line ending depends on the version, so this keeps the result the same on both

Let SQL do the aggregation and leave sorting and formatting to the usual commands. With this split, the steps fit easily into existing shell scripts.

Why SQL for two or more axes: compared with awk

Conclusion: awk can do it, but each axis or table adds arrays and idioms. SQL adds names.

Here is the "error rate per IP" from Step 4 written in awk.

$ awk -F, 'NR > 1 { total[$1]++; if ($5 >= 400) err[$1]++ }
END { for (ip in total) printf "%s,%d,%d,%.1f\n", ip, total[ip], err[ip], 100 * err[ip] / total[ip] }' access.csv | sort -t, -k4,4nr -k2,2nr

198.51.100.99,2,2,100.0
198.51.100.7,4,3,75.0
192.0.2.44,3,1,33.3
203.0.113.25,3,1,33.3
203.0.113.10,6,1,16.7

The result is the same. However, the reader has to keep the following in mind.

• There are two arrays (total and err), and END matches them on the assumption that their keys line up
• The order of for (ip in total) is not defined, so a final sort is required
• Columns are referenced by number ($1 / $5), so if the CSV column order changes, the command breaks silently

The join from Step 5 is even harder to read in awk.

$ awk -F, 'NR == FNR { if (FNR > 1) country[$1] = $2; next }
FNR > 1 && ($1 in country) { n[country[$1]]++ }
END { for (c in n) print c "," n[c] }' countries.csv access.csv | sort -t, -k2,2nr

JP,9
US,4
BR,3

If you do not know the NR == FNR idiom (true only while reading the first file), you cannot tell what this does. In addition, awk -F, does not understand CSV quoting, so a single value containing a comma, such as "a,b", shifts the columns. .import, on the other hand, follows RFC 4180 (the standard CSV specification) and removes the quotes when it reads.

In SQL, "which column to group by (GROUP BY)", "what to count (COUNT / SUM)", and "which tables to connect (JOIN)" are written as names. Adding one more axis only means adding a column name to GROUP BY and SELECT.

What if COUNT(*) does not match wc -l?

Conclusion: Usual causes: header row, blank lines, quoted newlines. Find blanks in the log.

If the difference is exactly 1, it is the header row. Otherwise, suspect blank lines and newlines. Check for blank lines in the original log.

$ grep -c '^$' access.log

0

Running grep on the CSV does not find blank lines. The awk in Step 1 also processes a blank line and prints a line whose 6 columns are all empty: ,,,,,. Try it with a log that has one blank line added at the end.

$ cp access.log blank.log
$ echo '' >> blank.log
$ awk 'BEGIN { OFS = ","; print "ip,time,method,path,status,bytes" }
{ sub(/^\[/, "", $4); sub(/^"/, "", $6); print $1, $4, $6, $7, $9, $10 }' blank.log > blank.csv
$ tail -n 1 blank.csv

,,,,,

This line has the right number of columns, so .import loads it without a warning. After the import, count the rows whose status is an empty string or NULL to find them.

$ sqlite3 blank.db ".import --csv blank.csv access"
$ sqlite3 -csv blank.db "SELECT COUNT(*) FROM access WHERE status = '' OR status IS NULL;"

1

If a CSV made without awk contains a blank line, .import prints the warning expected 6 columns but found 1 - filling the rest with NULL and loads a row whose remaining columns are NULL. The same SQL counts both kinds.

To prevent this, put the condition NF >= 10 before the body of the awk in Step 1. Only lines with 10 or more fields become CSV, so blank lines and truncated lines are not printed.

$ awk 'BEGIN { OFS = ","; print "ip,time,method,path,status,bytes" }
NF >= 10 { sub(/^\[/, "", $4); sub(/^"/, "", $6); print $1, $4, $6, $7, $9, $10 }' blank.log | wc -l

19

Only the header row and the 18 non-blank lines were printed. Also confirm that this article's logs.db has no empty rows.

$ sqlite3 -csv logs.db "SELECT COUNT(*) FROM access WHERE status = '' OR status IS NULL;"

0

Troubleshooting

Conclusion: Most problems are columns, types, dates, double imports, or size.

Symptom: Column count does not match (expected 6 columns but found 7)

Cause: A path or query string contains a comma, so the CSV from awk has one extra column.

Check:

$ printf '%s\n' '192.0.2.44 - - [24/Oct/2026:14:03:12 +0900] "GET /search?q=sqlite,csv HTTP/1.1" 200 2048 "-" "Mozilla/5.0"' > comma.log
$ awk 'BEGIN { OFS = "," } { sub(/^\[/, "", $4); sub(/^"/, "", $6); print $1, $4, $6, $7, $9, $10 }' comma.log | awk -F, '{ print NF }'

7

It should have 6 columns but has 7. If you import it as is, .import drops the extra column with a warning, and the values shift by one column.

$ awk 'BEGIN { OFS = ","; print "ip,time,method,path,status,bytes" } { sub(/^\[/, "", $4); sub(/^"/, "", $6); print $1, $4, $6, $7, $9, $10 }' access.log comma.log > bad.csv
$ sqlite3 bad.db ".import --csv bad.csv access"
$ sqlite3 -csv -header bad.db "SELECT path, status, bytes FROM access WHERE time LIKE '%14:03%';"

bad.csv:20: expected 6 columns but found 7 - extras ignored
path,status,bytes
/search?q=sqlite,csv,200

The first line is the .import warning (line 20 had 7 columns, so the extra one was dropped). In the result, path is /search?q=sqlite, status is csv, and bytes is 200. Part of the path ended up in the status column.

Fix: Wrap the columns that may contain commas in ". Replace any " inside a value with "" (the RFC 4180 rule).

$ awk 'BEGIN { OFS = "," } { sub(/^\[/, "", $4); sub(/^"/, "", $6); gsub(/"/, "\"\"", $7); print $1, $4, $6, "\"" $7 "\"", $9, $10 }' comma.log

192.0.2.44,24/Oct/2026:14:03:12,GET,"/search?q=sqlite,csv",200,2048

Turn access.log and comma.log together into CSV of this form and load it.

$ awk 'BEGIN { OFS = ","; print "ip,time,method,path,status,bytes" }
{ sub(/^\[/, "", $4); sub(/^"/, "", $6); gsub(/"/, "\"\"", $7)
  print $1, $4, $6, "\"" $7 "\"", $9, $10 }' access.log comma.log > access_q.csv
$ sqlite3 logs2.db ".import --csv access_q.csv access"
$ sqlite3 -csv -header logs2.db "SELECT path, status FROM access WHERE path LIKE '%,%';"

path,status
"/search?q=sqlite,csv",200

The value is stored as one value with the comma intact, and the status column correctly holds 200. On output, values containing commas are wrapped in " as well.

Symptom: Numbers sort in a strange order (1840 comes after 150)

Cause: Columns auto-created by .import are strings (TEXT), so ORDER BY bytes uses dictionary order (comparing one character at a time from the left).

Check:

$ sqlite3 -csv logs.db "SELECT DISTINCT bytes FROM access ORDER BY bytes;" | tr -d '\r' | paste -sd' '

0 150 1840 2310 320 512 5120 7200 9800

Fix: Convert to a number with CAST(column AS INTEGER) before sorting, comparing, or summing.

$ sqlite3 -csv logs.db "SELECT DISTINCT bytes FROM access ORDER BY CAST(bytes AS INTEGER);" | tr -d '\r' | paste -sd' '

0 150 320 512 1840 2310 5120 7200 9800

If writing CAST every time is tedious, you can create a table with fixed types first and then import into it. When the table already exists, line 1 is also treated as data, so --skip 1 skips the header row. Try it in a separate file, typed.db, so it does not clash with the existing logs.db.

$ sqlite3 typed.db "CREATE TABLE access(ip TEXT, time TEXT, method TEXT, path TEXT, status INTEGER, bytes INTEGER);"
$ sqlite3 typed.db ".import --csv --skip 1 access.csv access"
$ sqlite3 -csv typed.db "SELECT DISTINCT bytes FROM access ORDER BY bytes;" | tr -d '\r' | paste -sd' '

0 150 320 512 1840 2310 5120 7200 9800

The values sort in numeric order without CAST, because a column declared INTEGER stores digit-only strings as numbers.

Symptom: date() or strftime() returns empty (NULL)

Cause: 24/Oct/2026:09:02:11 is not a format that SQLite's date functions can read. The date is just stored as a string.

Check:

$ sqlite3 -csv -header logs.db "SELECT time, date(time) AS d FROM access LIMIT 1;"

time,d
24/Oct/2026:09:02:11,

d is empty.

Fix: Convert the time to the 2026-10-24 09:02:11 form (ISO 8601) while creating the CSV. Build a table in BEGIN that maps month names to numbers.

$ awk 'BEGIN { OFS = ","; split("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec", m, " ")
  for (i = 1; i <= 12; i++) mon[m[i]] = sprintf("%02d", i)
  print "ip,time,method,path,status,bytes" }
{ split(substr($4, 2), t, /[\/:]/)
  iso = t[3] "-" mon[t[2]] "-" t[1] " " t[4] ":" t[5] ":" t[6]
  sub(/^"/, "", $6); print $1, iso, $6, $7, $9, $10 }' access.log > access_iso.csv
$ head -n 2 access_iso.csv

ip,time,method,path,status,bytes
203.0.113.10,2026-10-24 09:02:11,GET,/,200,5120

In this form, strftime('%H', time) and date(time) work as they are. Load it into a separate file, iso.db, to check.

$ sqlite3 iso.db ".import --csv access_iso.csv access"
$ sqlite3 -csv -header iso.db "SELECT date(time) AS day, COUNT(*) AS count FROM access GROUP BY day;"

day,count
2026-10-24,18

Symptom: The count doubled / the header row got mixed into the data

Cause: You ran .import into the same table twice. When the table already exists, .import adds every line, including line 1, as data (as specified in the official documentation).

Check: Look for rows whose value equals the column name.

$ sqlite3 -csv logs.db "SELECT COUNT(*) FROM access WHERE ip = 'ip';"

0

If the result is 1 or more, the header row is in the data. Importing once more into a copy of logs.db (twice.db) gives the following count and header rows.

$ cp logs.db twice.db
$ sqlite3 twice.db ".import --csv access.csv access"
$ sqlite3 -csv twice.db "SELECT COUNT(*) FROM access;"
$ sqlite3 -csv twice.db "SELECT COUNT(*) FROM access WHERE ip = 'ip';"

37
1

The 19 lines, including the header row, were added to the original 18 rows, for 37.

Fix: Drop the table and import again.

$ sqlite3 twice.db "DROP TABLE IF EXISTS access;"
$ sqlite3 twice.db ".import --csv access.csv access"
$ sqlite3 -csv twice.db "SELECT COUNT(*) FROM access;"

18

Symptom: Importing a huge log takes a long time

Cause: You are converting and importing rows you do not need.

Fix: Filter the rows with grep before the import. For example, to look only at 4xx / 5xx responses:

$ grep -E '" [45][0-9]{2} ' access.log | wc -l

8

The 18 lines went down to 8. Pass this grep output to the awk from Step 1 to create the CSV, and the import shrinks by the same amount.

Completion Checklist

Conclusion: Before aggregating, check the row count and the number and date columns.

• [ ] wc -l access.log (the line count of the original log) matched SELECT COUNT(*)
• [ ] grep -c '^$' access.log found 0 blank lines (or awk uses NF >= 10)
• [ ] .import printed no warning at all
• [ ] The lookup table used in the join has no duplicate keys (GROUP BY ... HAVING n > 1 is empty)
• [ ] Columns that may contain commas were wrapped in " in the CSV
• [ ] Columns sorted or compared as numbers use CAST(... AS INTEGER)
• [ ] For date-based counts, the time was converted to ISO 8601 when creating the CSV
• [ ] Counts that can tie have a second key in ORDER BY
• [ ] Output sent back into a pipe goes through tr -d '\r'

Summary

Conclusion: One axis: pipes. Two or more and joins: SQL. Back to pipes with -csv.