Rewriting mysql query to hive

user_14217058 0
Viewed 59

I tried to join two tables:

Table X

PlayerID   | Name      | Team
007        | Sancho    | Dortmund
010        | Messi     | Barcelona
011        | Werner    | Chelsea
001        | De Gea    | Man Utd
009        | Lewan..ki | Bayern Mun
006        | Pogba     | Man Utd
017        | De Bruyne | Man City
029        | Harvertz  | Chelsea
005        | Upamecano | Leipzig

Table Y

PlayerID.   |Name      | Team
010         | Messi    | Man City
007         | Sancho   | Man Utd
006         | Pogba    | Man Utd
017         | De Bruyne| Man City
011         | Werner   | Liverpool
006         | Pogba    | Real Madrid

using this query

select avg(y.playerID is not null) as accuracy_ratio
from x
left join y 
    on  y.playerID = x.playerID
    and y.name     = x.name
    and y.team     = x.team

However, when I run the query, I get a Only numeric or string type arguments are accepted but boolean is passed. I am assuming the above query can only be done in mysql. How can I re-write it in Hive?

hiveql hive count sql
2 Answers

I realized that this is in relation to your previous post where GMB provided a solution in MySQL. Here is what you would need to do.

select avg(case when y.playerID is not null then 1 else 0 end) as accuracy_ratio
 from x
left join y 
    on  y.playerID = x.playerID
    and y.name     = x.name
    and y.team     = x.team
user_142366220

@learning_2_code I tried below code in hive as per your Dateset. It is giving me 0.22 . Please let me know if this works for your or not in Hive.

select count(y_pid)/count(*) from (   
select x.pid ,y.pid as y_pid  
from tablex x  
left join     
tabley y  
    on  y.pid = x.pid  
    and y.ply_name     = x.ply_name  
    and y.team     = x.team )A
user_30242540
Related