CSV を sqlite3 に取り込んで集計する - sort | uniq -c の次の一手

2026-10-24 読了時間: 約15分 難易度: 中級以上

この記事で解決できること

• sort | uniq -c で足りる集計と、SQL に切り替えるべき集計を 判断できる
• awk で作った CSV を sqlite3 の表に 1 行で取り込める
• GROUP BY / JOIN で 2 軸の集計・別表との結合・割合 を出し、結果を再びパイプへ戻せる

用語を先にそろえておく。

• 表（テーブル）: 行と列でできたデータの入れ物。CSV 1 ファイルが表 1 つに当たる
• 列（カラム）: 表の縦の項目。ip や status など。CSV の「フィールド」と同じもの
• SQL: 表から行を選び、まとめ、数えるための問い合わせ言語。SELECT ... で始まる文がそれに当たる
• ドットコマンド: .import のように . で始まる sqlite3 独自の命令。SQL ではない
• 集計: 行をグループにまとめて件数・合計・割合を出すこと

sort | uniq -c と sqlite3 はどう使い分けるか？

結論: 件数を縦に並べるだけならパイプで足りる。集計値を横に並べる、別表と突き合わせる、割合を出すなら sqlite3 に取り込む。

判断の基準は 3 つ。「1 行に複数の集計値を横に並べたいか」「別の表と突き合わせるか」「割合を出すか」。どれかに当てはまれば SQL を使う。

クロス集計: 行に 1 つ目の軸（時間帯）、列に 2 つ目の軸（ok / errors など）を置いて、交わるところに件数を入れる集計のこと。

準備: サンプルの access.log を用意する

結論: nginx 形式の小さな access.log を自作して手順を試す。既定の combined 形式のログなら同じ手順が使える。

次のコマンドで 18 行の access.log を作る。IP アドレスは例示用に予約された範囲（RFC 5737）を使っている。

$ 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

<<'EOF' のように終わりの印を引用符で囲むと、中の $ や " がシェルに展開されず、そのままファイルに書かれる。wc -l で 18 行あることを確かめておく。

まず 1 軸の集計を従来の方法で出しておく。空白区切りの 9 番目のフィールドがステータスコード。

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

      8 200
      4 500
      4 404
      2 304

1 軸ならこれで十分。この先は、この形では書きにくい集計を SQL で出していく。

Step 1: awk で access.log を CSV にする

結論: awk で必要な列だけを抜き出し、1 行目に列名を付けた CSV にする。列名はそのまま表の列名になる。

$ 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: 空白で区切った 1・4・6・7・9・10 番目のフィールド。順に IP・時刻・メソッド・パス・ステータス・バイト数
• OFS = ",": 出力の区切り文字をカンマにする
• print "ip,time,...": BEGIN ブロックで見出し行（ヘッダ行）を 1 回だけ出す
• sub(/^\[/, "", $4): 時刻の先頭の [ を消す。sub(/^"/, "", $6) はメソッドの先頭の " を消す

行数も確かめておく。CSV はヘッダ行の分だけ 1 行多い。

$ wc -l access.log access.csv

  18 access.log
  19 access.csv
  37 total

Step 2: .import で CSV を表に取り込む

結論: .import --csv access.csv access の 1 行で表が作られる。CSV の 1 行目は列名になり、データには入らない。

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

18

• logs.db: データベースファイル。表をまとめて保存する 1 つのファイルで、無ければ新しく作られる
• ".import --csv access.csv access": access.csv を CSV として読み、access という表に入れる。成功すると何も表示しない
• -csv: 結果を CSV 形式で表示する。2 行目の SELECT は件数を数える SQL

公式ドキュメントによると、取り込み先の表が まだ無い 場合、.import は表を自動で作り、CSV の 1 行目を列名に使う。データとして入るのは 2 行目以降なので、件数は access.log の行数と同じ 18 になる。同じ表へ 2 回取り込むと件数が倍になるので、やり直すときは トラブルシューティング の手順で表を消してから取り込む。

列の型を確認する。pragma_table_info('表名') は、表の列名と型を一覧にする SQLite の組み込み関数。-header は結果の 1 行目に列名を付けるオプション。

$ 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

自動で作られた列はすべて TEXT（文字列）になる。status や bytes のような数字も文字列として入る。3.53.0 以降のソース（src/shell.c.in の zAutoColumn）では列に型を付けずに表を作るため type 欄は空になるが、値が文字列で入る点は同じ。数値として扱う場面では CAST(bytes AS INTEGER) のように変換する。

Step 3: GROUP BY でステータス別・IP 別に数える

結論: GROUP BY 列 で同じ値の行をまとめ、COUNT(*) で数える。ORDER BY count DESC LIMIT n で上位だけ出す。

この記事の SQL は、次の部品の組み合わせでできている。左から順に読めば、パイプと同じ感覚で追える。

$ 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

cut | sort | uniq -c と同じ件数が出る。COUNT(*) AS count の AS は、結果の列に count という名前を付ける指定。この名前は ORDER BY でも使える。ORDER BY count DESC, status の 2 つ目の status は、件数が同じ行の並び順を固定するための指定。これを書かないと、同数の行の順番は保証されない。

上位 3 件の IP アドレスも同じ形で出せる。

$ 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

時間帯別の件数は、時刻の文字列から「時」の 2 文字を substr で切り出してまとめる。24/Oct/2026:09:02:11 の 13 文字目から 2 文字が時。

$ 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: 時間帯 × ステータスの 2 軸で集計する

結論: 2 軸目は SUM(条件) で列として足す。条件が真なら 1、偽なら 0 になるので、合計がそのまま条件付きの件数になる。

時間帯とステータスの組み合わせを縦に並べるだけなら、パイプでもできる。awk で 2 つのキーをつなげて 1 行にし、sort | uniq -c で数える。$4 は先頭に [ が付いているため、時は 14 文字目から 2 文字になる。

$ 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

ただし、時間帯ごとに「成功」「エラー」「合計」を 1 行に横並びで見たいとき（クロス集計）は、この形からさらに組み替えが要る。SQL なら列を足すだけで済む。

$ 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 は文字列なので、比べる相手も '200' と引用符付きで書く
• CAST(status AS INTEGER) >= 400: 範囲で比べるときは数値に変換してから比べる
• ok + errors は total と一致しないことがある。304（キャッシュ済み）はどちらの条件にも入らないため

IP ごとのエラー率（割合）も同じ考え方で出せる。

$ 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

100.0 * と小数で掛けているのは、整数どうしの割り算だと小数点以下が切り捨てられるため。198.51.100.7 は POST /login が 3 回続けて 500 を返している。こうした偏りは 1 軸の件数だけでは見えない。

Step 5: JOIN で国名の対応表と結合する

結論: 対応表も CSV にして別の表として取り込み、JOIN ... ON で共通の列をつなぐ。対応が無い行は LEFT JOIN で見つける。

IP アドレスと国コードの対応表 countries.csv を、access.log と同じく here-document で作る。

$ 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

同じデータベースに 2 つ目の表として取り込み、ip 列でつなぐ。

$ 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

合計は 16 件で、全体の 18 件より 2 件少ない。JOIN は両方の表に一致する行だけを残すため、対応表に無い IP の行が落ちている。落ちた行は LEFT JOIN で探す。

$ 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 は左の表（access）の行をすべて残し、右の表に相手が無い列を NULL（値なし）で埋める。WHERE c.ip IS NULL で、その「相手なし」の行だけを取り出している。

$ 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

結合する前に、対応表のキーに重複が無いかを確かめる。HAVING は GROUP BY でまとめた後のグループを条件で絞る句（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

元の logs.db で同じ SQL を実行すると何も表示されない。重複が無いことを示している。

Step 6: 結果を CSV で吐いて sort に戻す

結論: sqlite3 -csv の出力は普通の CSV なので sort や head へ渡せる。行末の \r は先に消しておく。

$ 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

• -header を付けないと見出し行が出ないので、sort にそのまま渡せる
• sort -t, -k2,2nr: カンマ区切りの 2 列目を数値として降順に並べる
• tr -d '\r': 3.54 系の公式ドキュメント（CLI の出力形式の解説）では、CSV 出力の行末は常に CRLF（\r\n）とされている。一方 3.45.1 の -csv の行末は LF だけだった（od -c で確認）。版によって行末が変わるため、どちらでも同じ結果になるように挟んでいる

SQL で集計し、並べ替えや整形はいつものコマンドに任せる。この分担なら、既存のシェルスクリプトにも組み込みやすい。

なぜ 2 軸以上は SQL なのか: awk と並べて比べる

結論: awk でも同じ集計はできるが、軸や表が増えるたびに配列と特殊な書き方が増える。SQL は列と条件を足すだけで済む。

Step 4 の「IP ごとのエラー率」を 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

結果は同じ。ただし、読み手は次の点を頭の中で追う必要がある。

• 配列が 2 つ（total と err）あり、キーがそろっている前提で END で突き合わせている
• for (ip in total) の順番は決まっていないため、最後に sort が要る
• 列を番号（$1 / $5）で指定しているため、CSV の列順が変わると黙って壊れる

Step 5 の結合を 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

NR == FNR（1 つ目のファイルを読んでいる間だけ真）という定番の書き方を知らないと、何をしているか読み取れない。また awk -F, は CSV の引用符を解釈しないため、"a,b" のようにカンマを含む値が 1 つでもあると列がずれる。一方 .import は RFC 4180（CSV の標準仕様）に沿って引用符を外して読む。

SQL なら「どの列でまとめるか（GROUP BY）」「何を数えるか（COUNT / SUM）」「どの表をつなぐか（JOIN）」が名前で書かれる。軸を 1 つ増やすときも、GROUP BY と SELECT に列名を足すだけで済む。

COUNT(*) が wc -l と合わないときは？

結論: ずれの原因はヘッダ行・空行・引用符の中の改行のどれかが多い。空行は CSV ではなく元のログ側で探す。

空行は元のログ側で確かめる。

$ grep -c '^$' access.log

0

CSV 側を grep しても空行は見つからない。Step 1 の awk は空行も 1 行として処理し、6 列すべてが空の ,,,,, という行を出すためだ。末尾に空行を 1 行足したログで確かめる。

$ 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

,,,,,

この行は列数が合っているため、.import は警告を出さずに取り込む。取り込んだ後は、status が空文字か NULL の行を数えれば見つけられる。

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

1

awk を通さずに作った CSV に空行がある場合は、expected 6 columns but found 1 - filling the rest with NULL という警告が出て、残りの列が NULL の行として入る。どちらの形も同じ SQL で数えられる。

予防するには、Step 1 の awk の本体の前に NF >= 10 という条件を付ける。フィールドが 10 個以上ある行だけを CSV にするので、空行や途中で切れた行は出力されない。

$ 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

ヘッダ行 1 行と、空行を除いた 18 行だけが出力された。本記事の logs.db には空の行が無いことも確かめておく。

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

0

トラブルシューティング

結論: 取り込みの不具合は「列数」「型」「日付の形式」「二重取り込み」「サイズ」の 5 つで大半を説明できる。

症状: 列数が合わない（expected 6 columns but found 7）

原因: パスやクエリ文字列にカンマが含まれ、awk が出した CSV の列が 1 つ増えている。

確認:

$ 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

6 列のはずが 7 列になっている。このまま取り込むと、.import は余った列を捨てて警告を出し、値が 1 列ずつずれる。

$ 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

1 行目は .import の警告（20 行目で 7 列見つかったので余りを捨てた）。結果を見ると、path は /search?q=sqlite、status は csv、bytes は 200 になっている。パスの一部が status 列に入ってしまった。

対処: カンマを含み得る列を " で囲む。値の中の " は "" に置き換える（RFC 4180 の決まり）。

$ 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

access.log と comma.log をまとめて、この形の CSV にして取り込む。

$ 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

カンマを含んだまま 1 つの値として入り、status 列も正しく 200 になっている。出力側でも、カンマを含む値は " で囲まれる。

症状: 数値の並び順がおかしい（150 の次に 1840 が来る）

原因: .import で自動作成した列は文字列（TEXT）なので、ORDER BY bytes は辞書順（1 文字ずつ左から比べる順）になる。

確認:

$ 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

対処: 並べ替え・比較・合計の前に CAST(列 AS INTEGER) で数値にする。

$ 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

毎回 CAST を書くのが面倒なら、先に型を決めた表を作ってから取り込む方法もある。表がすでにあると 1 行目もデータとして扱われるため、--skip 1 でヘッダ行を飛ばす。既存の logs.db とぶつからないよう、別のファイル typed.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

CAST を書かなくても数値の順に並ぶ。INTEGER と宣言した列では、数字だけの文字列が数値として保存されるため。

症状: date() や strftime() が空（NULL）を返す

原因: 24/Oct/2026:09:02:11 は SQLite の日付関数が読める形式ではない。日付も文字列として入っているだけ。

確認:

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

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

d が空になっている。

対処: CSV にする段階で 2026-10-24 09:02:11 の形（ISO 8601）へ変換する。月の名前を数字に置き換える表を BEGIN で作っておく。

$ 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

この形なら strftime('%H', time) や date(time) がそのまま使える。別のファイル iso.db に取り込んで確かめる。

$ 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

症状: 件数が 2 倍になった / ヘッダ行がデータに混ざった

原因: 同じ表に .import を 2 回実行した。表がすでにあると、.import は 1 行目も含めてすべてデータとして追加する（公式ドキュメントの仕様）。

確認: 列名と同じ値を持つ行を探す。

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

0

1 以上なら、ヘッダ行がデータとして入っている。logs.db の写し（twice.db）にもう 1 回取り込むと、件数とヘッダ行は次のようになる。

$ 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

元の 18 件に、ヘッダ行を含む 19 行が足されて 37 件になった。

対処: 表を消してから取り込み直す。

$ 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

症状: 巨大なログの取り込みに時間がかかる

原因: 必要のない行まで CSV にして取り込んでいる。

対処: 取り込む前に grep で行を絞る。たとえば 4xx / 5xx だけを調べるなら次のようにする。

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

8

18 行のうち 8 行まで減った。この grep の出力を Step 1 の awk に渡して CSV にすれば、取り込む行も同じだけ減る。

作業完了チェックリスト

結論: 集計に進む前に、取り込み直後の件数と、数値・日付の列の扱いを 1 つずつ確かめる。

• [ ] wc -l access.log（元のログの行数）と SELECT COUNT(*) が一致した
• [ ] grep -c '^$' access.log で空行が 0 件だった（または awk に NF >= 10 を付けた）
• [ ] .import が警告を 1 行も出さなかった
• [ ] 結合する対応表のキーに重複がない（GROUP BY ... HAVING n > 1 が空）
• [ ] カンマを含み得る列を " で囲んで CSV にした
• [ ] 数値として並べる列・比べる列に CAST(... AS INTEGER) を付けた
• [ ] 日付を使う集計では、CSV の段階で ISO 8601 形式に変換した
• [ ] 同点の行がある集計では ORDER BY に 2 つ目のキーを付けた
• [ ] パイプへ戻す出力に tr -d '\r' を挟んだ

まとめ

結論: 1 軸はパイプ、2 軸以上と結合は SQL。awk で CSV にし、.import で取り込み、結果は -csv でパイプへ戻す。