Join table by string matching in Hive or Impala or Pig
<p>I have two tables <code>A</code> and <code>B</code>, where <code>B</code> is huge (20 million by 300) and <code>A</code> is of moderate size (300k by 10). <code>A</code> contains one column that is address and <code>B</code> contains 3 columns that can be put together to form a proper street address. For example, in <code>A</code>, the address column could be:</p>

<pre><code>id  | Address
-----------
233 | 123 Main St
</code></pre>

<p>and in <code>B</code> we could have:</p>

<pre><code>Number  |  Street_name  | Street_suffix | Tax
------------------------------------------------
123     |  Main         | Street        | 320.2
</code></pre>

<p>I want to join them using string matching similar to <code>LIKE</code> with something like the following:</p>

<pre><code>select A.id, B.Tax
from A
  left join B
    on A.Address **contains** B.Number
    and A.Address **contains** B.Street_name;
</code></pre>

<p>Basically I am trying to match the records by saying that if <code>A</code>'s address contains <code>B</code>'s number and street_name, then I say they are the same address (In reality, I also have city, state and zip code. But I chose to ignore those for illustration purposes).</p>

<p>The 2 contains part is something I am not sure how to implement. Any ideas?</p>

<p>I am on Cloudera's Hue Hadoop distribution, where I have access to Hive (1.1.0, unfortunately because 1.2.0 has Levenshtein distance function), Impala (v2.3.0) and Pig (0.12.0-cdh5.5.0).</p>