If the requirement calls for a one-time report, then I tried another implementation using SQLite; without a view, trigger, and primary key. A group by clause is used for the select statement, now that the DB has duplicate keys. The total time sheds 5 seconds including smaller DB size. Note: Variant two is not compatible with variant one.
running:
$ wc -l big1.txt big2.txt big3.txt
3515200 big1.txt
3515200 big2.txt
3515200 big3.txt
10545600 total
$ time bash llil_sql_imp2 hash2.db big1.txt big2.txt big3.txt
Importing big1.txt
Importing big2.txt
Importing big3.txt
real 0m4.350s
user 0m4.298s
sys 0m0.049s
$ time bash llil_sql_qry2 hash2.db >out.txt
real 0m9.668s
user 0m9.537s
sys 0m0.122s
$ ls -lh hash2.db
-rw-r--r-- 1 mario mario 160M Feb 7 22:57 hash2.db
q - Text as Data:
I came across q - Run SQL directly on delimited files and multi-file sqlite databases on GitHub. Specify -T for tab-delimited output. You may like this tool. Check it out.
$ time ./linux-q -T "select name, sum(value) from hash.db:::kv_store g
+roup by name order by sum(value) desc, name" >out.txt
real 0m49.136s
user 0m48.667s
sys 0m0.423s
llil_sql_imp2:
Like variant one, the optional -reset argument to the importer script removes the DB file. The .output to /dev/null is to silence SQLite when setting PRAGMA statements. The .output without an argument restores it back to standard output.
#!/usr/bin/env bash
# https://perlmonks.org/?node_id=11150232
# SQLite llil import script.
# Usage: bash llil_sql_imp2 [-reset] hash2.db big1.txt big2.txt big3.t
+xt
# SQL
function make_script () {
read -r -d '' var << 'EOF'
-- Set SQLite pragmas.
--
.output /dev/null
PRAGMA cache_size = 10000;
PRAGMA page_size = 4096;
PRAGMA journal_mode = OFF;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
.output
-- Create the kv_store table.
--
CREATE TABLE IF NOT EXISTS "kv_store" (
"name" TEXT NOT NULL,
"value" INTEGER DEFAULT 0
);
-- Import CSV/TXT file(s).
--
.mode csv
.headers off
.separator ROW "\n"
.separator "\t"
EOF
echo "$var"
}
# RUN
if [[ "$1" == "-reset" ]]; then
DB="$2"; shift; shift
rm -fr "$DB" "$DB"-journal "$DB"-shm "$DB"-wal
else
DB="$1"; shift
fi
if [[ ! "$DB" =~ .db$ ]]; then
echo "usage: bash $0 [ -reset ] hash2.db [ in1.txt ... ]" >&2
exit 1
fi
SQLCMND=$( command -v sqlite3 )
if [[ -z "$SQLCMND" ]]; then
echo "sqlite3: command not found" >&2
exit 1
fi
SQLSCRIPT="$( make_script )"
NL=$'\n'
for infile in "$@"; do
if [[ ! -r "$infile" ]]; then
echo "$infile: cannot open '$infile', skipping..." >&2
continue
fi
SQLSCRIPT+="${NL}.print Importing ${infile}"
SQLSCRIPT+="${NL}.import '${infile}' kv_store"
done
exec "$SQLCMND" "$DB" < <(echo "$SQLSCRIPT")
llil_sql_qry2:
This produces output with proper formatting and sort order.
#!/usr/bin/env bash
# https://perlmonks.org/?node_id=11150232
# SQLite llil query script.
# Usage: bash llil_sql_qry2 hash2.db > out.txt
# SQL
function make_script () {
read -r -d '' var << 'EOF'
-- Set SQLite pragmas.
--
.output /dev/null
PRAGMA cache_size = 10000;
PRAGMA page_size = 4096;
PRAGMA journal_mode = OFF;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
.output
-- Output records.
--
.mode csv
.headers off
.separator ROW "\n"
.separator "\t"
SELECT "name", sum("value") FROM "kv_store"
GROUP BY "name"
ORDER BY sum("value") DESC, "name";
EOF
echo "$var"
}
# RUN
DB="$1"; shift
if [[ ! "$DB" =~ .db$ ]]; then
echo "usage: bash $0 hash2.db" >&2
exit 1
elif [[ ! -r "$DB" ]]; then
echo "$DB: cannot open '$DB' (No such file)" >&2
exit 1
fi
SQLCMND=$( command -v sqlite3 )
if [[ -z "$SQLCMND" ]]; then
echo "sqlite3: command not found" >&2
exit 1
fi
SQLSCRIPT="$( make_script )"
exec "$SQLCMND" "$DB" < <(echo "$SQLSCRIPT")