comment on

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
[download]

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
[download]

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")
[download]

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")
[download]

In reply to Re^2: Rosetta Code: Long List is Long - SQLite code by marioroy
in thread Rosetta Code: Long List is Long by eyepopslikeamosquito

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Keep It Simple, Stupid
	PerlMonks