I tried a key-value store implementation using SQLite. There are two scripts; one for importing files and another to output the results.
Note: See this thread for generating the big input files.
$ wc -l big1.txt big2.txt big3.txt
3515200 big1.txt
3515200 big2.txt
3515200 big3.txt
10545600 total
$ time bash llil_sql_imp1 hash1.db big1.txt big2.txt big3.txt
Importing big1.txt
Importing big2.txt
Importing big3.txt
real 0m12.913s
user 0m12.701s
sys 0m0.197s
$ time bash llil_sql_qry1 hash1.db >out.txt
real 0m6.297s
user 0m6.157s
sys 0m0.158s
$ ls -lh hash1.db
-rw-r--r-- 1 mario mario 330M Feb 7 22:55 hash1.db
Updated on 2023-02-08
llil_sql_imp1:
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=11150229
# SQLite llil import script.
# Usage: bash llil_sql_imp1 [-reset] hash1.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 UNIQUE,
"value" INTEGER DEFAULT 0,
PRIMARY KEY("name")
);
-- Create a view for the kv_store table.
--
CREATE VIEW IF NOT EXISTS properties("name", "value")
AS SELECT "name", "value" FROM "kv_store";
-- Create a trigger for inserting into properties.
--
CREATE TRIGGER IF NOT EXISTS insert_trg
INSTEAD OF INSERT ON properties
BEGIN
--------------------------------------------------------------------
+-----
-- This form (ON DUPLICATE KEY) is not supported in SQLite.
--
-- INSERT INTO "kv_store" ("name", "value") VALUES (NEW.name, NEW.va
+lue)
-- ON DUPLICATE KEY UPDATE "value" = kv_store.value + NEW.value;
--------------------------------------------------------------------
+-----
-- Try this instead; supported since SQLite version 3.24.0 (2018-06-
+04).
-- https://sqlite.org/lang_UPSERT.html
--
INSERT INTO "kv_store" ("name", "value") VALUES (NEW.name, NEW.va
+lue)
ON CONFLICT("name") DO UPDATE SET "value" = kv_store.value + NE
+W.value;
--------------------------------------------------------------------
+-----
-- For SQLite before version 3.24.0 (2018-06-04) or Berkeley DB (dbs
+ql).
--
-- -- Try updating an existing record.
-- UPDATE "kv_store"
-- SET "value" = kv_store.value + NEW.value WHERE "name" = NEW.nam
+e;
-- -- Insert a row unless the update occurred.
-- INSERT INTO "kv_store" ("name", "value")
-- SELECT NEW.name, NEW.value WHERE (SELECT changes() = 0);
END;
-- Drop the query index to maximize import performance.
--
DROP INDEX IF EXISTS "query_idx";
-- 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 ] hash1.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}' properties"
done
exec "$SQLCMND" "$DB" < <(echo "$SQLSCRIPT")
llil_sql_qry1:
This is the complementary script to output the data with proper formatting and sort order.
#!/usr/bin/env bash
# https://perlmonks.org/?node_id=11150229
# SQLite llil query script.
# Usage: bash llil_sql_qry1 hash1.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
-- A query index is not needed for one-time use.
--
-- CREATE INDEX IF NOT EXISTS "query_idx" ON "kv_store" (
-- "value" DESC, "name"
-- );
-- Output records.
--
.mode csv
.headers off
.separator ROW "\n"
.separator "\t"
SELECT "name", "value" FROM "kv_store"
ORDER BY "value" DESC, "name";
EOF
echo "$var"
}
# RUN
DB="$1"; shift
if [[ ! "$DB" =~ .db$ ]]; then
echo "usage: bash $0 hash1.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")