unable to join hive tables using phone number prefix in hive

Question

hi all I have a hive table which consist of phone number with country prefix sample table as below all are strings their are no integer values in my tables

table1:

     number          totalcalls  totalmin
    91992834943       6          12
    9954345438        4          15
    1684999932453     5           3

now i have table 2:-

now i want to join this tables using mobile number prefix and prefix will be in starting 5 digits only

sample output :

    number          totalcalls  totalmin   country         countrycode
    91992834943       6          12        india            91
    9954345438        4          15        abkhazia         995
    1684999932453     5           3       american samoa    1684

Does MySQL have anything to do with your question, and if not, can you remove that tag? — Tim Biegeleisen

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-08-19T04:44:17

Assuming the prefixes are unambiguous, you can do:

select t2.*, p.*
from table2 t2 left join
     prefixes p
     on t2.number = concat(p.country_code, '%');

Unambiguous means that you don't have values like 12 and 123 where a phone number like 123456789 could match either one.

If you can have ambiguity, then you want the longest prefix:

select tp.*
from (select t2.*, p.*,
             row_number() over (partition by t2.number order by length(p2.country_code) desc) as seqnum
      from table2 t2 left join
           prefixes p
           on t2.number = concat(p.country_code, '%')
     ) tp
where seqnum = 1;

unable to join hive tables using phone number prefix in hive

1 Answers