Join table using LIKE or RLIKE in Hive
<p>I'm trying to (INNER) join two tables in Hive using RLIKE. </p>

<pre><code>select a.col_x, b.col_y, count(*) as n
from tableA a
join tableB b
ON a.col_x RLIKE concat('^', b.col_z)
group by a.col_x, b.col_y
</code></pre>

<p>(tableA apprx. 100M records, tableB apprx. 1k records)</p>

<p>This query won't work since Hive only supports equality join.</p>

<p>I move inequalities to the <code>where</code> clause (Ref: <a href="https://stackoverflow.com/questions/25821166/error-in-hive-query-while-joining-tables">Error in Hive Query while joining tables</a>). </p>

<pre><code>select a.col_x, b.col_y, count(*) as n
from tableA a , tableB b
WHERE a.col_x RLIKE concat('^', b.col_z)
group by a.col_x, b.col_y
</code></pre>

<p>Second approach doesn't return any error. However, it run very very slow. Run approx 1 hour but still unable to get the output (normally, when I use equality join <code>ON a.col_x RLIKE b.col_z</code> it only need less than 5 minutes).</p>

<p>Any solution (using Hive or other hadoop applications) is appreciated.</p>