We've collected many months of FT8 traffic in daily log files. Now we wonder what can we do with these in an SQLite3 database. wikipedia 
I'd read serious treatments of SQLite's architecture. This week my friend Michael Kelley Harris tracked down starting points for me. download fiddle cli
See SQLite Internals
We choose one day for a large but not huge collection of records. From this we extract fields as csv.
cat data/2023-10-24 |\ jq -r '.[]|.call+","+.time+","+.msg' >data.csv
We create a database, add a table and then import csv.
% sqlite3 spots create table spots(call text, time text, msg text) .import --csv data.csv spots
We try a simple query.
select * from spots limit 10
NK0V|2023-10-24T00:00:00Z|K4CS NK0V RR73 K0LWC|2023-10-24T00:00:00Z|CQ K0LWC EN35 K1RH|2023-10-24T00:00:00Z|WA0TUC K1RH FM19 VE3CMM|2023-10-24T00:00:00Z|W5XX VE3CMM EN93 N2WK|2023-10-24T00:00:00Z|K1MCO N2WK FN03 NG9J|2023-10-24T00:00:00Z|WA0TUC NG9J 73 K8HLB|2023-10-24T00:00:00Z|CQ K8HLB EN91 N4OME|2023-10-24T00:00:00Z|IZ8EYN N4OME R-15 WY6Y|2023-10-24T00:00:00Z|ES5QA WY6Y EM15 JA9IFF|2023-10-24T00:00:00Z|W8DDS JA9IFF PM95
Then one that is hard to do in just command line tools.
sqlite3 spots ' select substr(time,11,3), count(*) from spots where msg like "CQ VOTA %" group by substr(time,11,3)'
This gives us hour by hour the number of calls looking to work "volunteers on the air". arrl See Things on the Air
T00|256 T01|236 T02|134 T03|200 T04|85 T05|36 T06|44 T07|93 T08|97 T09|113 T10|97 T11|126 T12|162 T13|157 T14|170 T15|207 T16|193 T17|403 T18|301 T19|542 T20|521 T21|411 T22|354 T23|373
Times for this query look promising.
0.05s user 0.06s system 38% cpu 0.284 total
Files sizes look good too.
ls -l data.* spots -rw-r--r-- 2 ward 68062770 Oct 22 17:00 data.json -rw-r--r-- 1 ward 9263635 Oct 26 18:35 data.csv -rw-r--r-- 1 ward 10870784 Oct 26 18:41 spots
.
Where do VOTA calls come from? wikipedia
select count(distinct call), substr(msg,-4) from spots where msg like "CQ VOTA %" group by substr(msg,-4);
1|CM96 1|CM97 1|CM98 1|CN84 1|CN85 1|DM09 1|DM13 1|DM44 1|DM45 1|DM62 1|DM72 2|DM79 1|DN18 1|DN70 1|DN71 1|DN98 1|DO33 1|EL09 1|EL29 1|EL98 1|EM12 1|EM16 1|EM20 2|EM29 1|EM42 1|EM48 1|EM73 1|EM74 1|EM75 3|EM79 1|EM83 1|EM88 1|EM95 1|EM97 1|EN25 1|EN33 1|EN51 1|EN72 1|EN81 2|EN82 2|EN90 1|FM08 1|FM16 1|FM17 3|FM18 2|FM19 1|FN31 1|FN32 3|FN41
CREATE TABLE spots(call text, time text, msg text); CREATE TABLE us(square text primary key, city text);
select count(distinct s.call) as count, substr(s.msg,-4) as square, g.city from spots as s, us as g where msg like "CQ VOTA %" and g.square = square group by substr(msg,-4) order by count;