Extracting lines by key from very large file

Question

I have a 42M line text file. Th first nine characters of each line are a numeric key. What is the most efficient way to extract only the lines whose key exists in another list of about 1.5M keys? Both the file and the list of keys are sorted.

Answer 1

Using awk should be efficient enough - it provides builtin associative arrays, where the key lookup time is logarithmically proportional to the number of keys (of your lookup table - which is relatively small in your example).

For your input this would be:

42M * log2(1.5M) -> 42M * 20 key comparisons 

(where M means 10^6)

In case your awk uses hash tables, every key lookup would only cost a constant amount of time.

An example of an efficient awk based solution (using the default field separator):

$ awk 'ARGIND == 1 { a[$1] = 1; next } a[$1] { print $0 }' keys.dat largefile.dat

Since both inputs are sorted you could write a script that would be more efficient (with a runtime scaling linearly with both input file sizes). But it would cost more time programming it.

Or you could use join which expect sorted files as input - restriction is that your key needs to be alphabetically sorted - and perhaps you have to tweak the output format. For example:

$ join -j1 keys.dat largefile.dat

Use -t to configure the field separator and -o to adjust the output format.

This should run in time linear to the input size.

Answer 2

Note that this method uses the length of a fixed-length key which begins at the first byte of the record.

By using \x01 (or any unique single-byte char) as a temporary field separator, records can be more easily manipulated.

join -t$'\x01' <(sed -r 's/.{9}/&\x01/' main) <(cut -b -9 keys) |sed -r 's/(.{9})./\1/'

maxschlepzig's awk example was faster for 45,000,000 records, but it failed on a bigger file. How much free RAM do you have?

Here are the results:

45,000,000 unique records, 1,500,000 keys
=========================
awk

real    0m31.971s
user    0m28.782s
sys     0m2.972s

join

real    0m53.733s
user    0m54.255s
sys     0m0.708s

(2x45) 90,000,000 records, 1,500,000 keys
=========================
awk
awk: (FILENAME=main2 FNR=54334297) fatal: assoc_lookup: bucket->ahname_str: can't allocate 11 bytes of memory (Cannot allocate memory)

join

real    1m35.306s
user    1m34.754s
sys     0m1.344s

===================

Answer 3

Assuming it's a line based file, grep should be pretty efficient. Use -f keyfile and -F for fixed strings:

grep -F -f keys textfile

Note: heed the warning about false positives by PeterO in the comments below.