hive join tables and extracts value based on column values

mark_hadoop — Mon, 01 Oct 2018 20:14:23 GMT

Hi All,

I need help to get the below result.

I have two tables

table name: match
+-----------------------------------+----------------+--+
|            hint                   | remarks        |
+-----------------------------------+----------------+--+
| 1.1.1.1                           | ip             |
| 123456789                         | contact        |
| http://123123123123123123.some_n  | url            |
+-----------------------------------+----------------+--+

table name : t1

+-------------------------------------------------------------------------------+-------------------+--+
|                                     t1.text                                   |       t1.b        |
+-------------------------------------------------------------------------------+-------------------+--+
| This ip is found 1.1.1.1 and is matched with match                            | table name match  |
| This ip is found 1.1.1.2 and is matched with match                            | table name match  |
| This contact is found 123456789 and is matched with match                     | table name match  |
| This contact is found 123456789123456789 and is matched with match            | table name match  |
| This url is found http://123456789123456789.some_n and is matched with match  | table name match  |
+-------------------------------------------------------------------------------+-------------------+--+

I want to search hint column of match table in text column of t1 table and get complete text column values.

so, basically I want to do a query like

select t1.text from t1 join match where t1.text contains (any value in match.hint);

It will be helpful if this can be done in hive or I can live with pyspark, so pyspark help is also welcome

P.S: table t1 is a big table and match is a small table with limite values(say 1500).

Thank you

Re: hive join tables and extracts value based on column values

nramanaiah — Fri, 05 Oct 2018 21:59:40 GMT

Please try the below query, it should return required results

select text from t1 join match where instr(text, hint) !=0;

If my suggestion helped to solve the problem, accept the answer. It might help others in the community.

question Re: hive join tables and extracts value based on column values in Support Questions

hive join tables and extracts value based on column values

Re: hive join tables and extracts value based on column values