Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

hive join tables and extracts value based on column values

Solved Go to solution
Highlighted

hive join tables and extracts value based on column values

Expert Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

Expert Contributor

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.

View solution in original post

1 REPLY 1

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

Expert Contributor

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.

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here