Filter a smaller file using another huge file
<p>I have a huge csv file with about 10^9 lines where each line has a pair of ids such as:</p>

<pre><code>IDa,IDb
IDb,IDa
IDc,IDd
</code></pre>

<p>Call this file1.  I have another much smaller csv file with about 10^6 lines in the same format. Call this file2. </p>

<p>I want to simply find the lines in file2 which contain at least one ID that exists somewhere in file1.</p>

<p>Is there a fast way to do this?  I don't mind if it is in awk, python or perl.</p>

<p>I would actually use <code>sqlite</code> for something like that. You could create a new database from the same directory as two files with <code>sqlite3 test.sqlite</code> and then do something like that:</p>

<pre><code>create table file1(id1, id2);
create table file2(id1, id2);
.separator ","
.import file1.csv file1
.import file2.csv file2
WITH all_ids AS (
    SELECT id1 FROM file1 UNION SELECT id2 FROM file1
)
SELECT * FROM file2 WHERE id1 IN all_ids OR id2 IN all_ids;
</code></pre>

<p>The advantage of using <code>sqlite</code> is that you can manage the memory more intelligently than a simple script that you could write in some scripting language.</p>

python perl awk