How do we achieve Currency Conversion in Hive?

Question

I would like to convert the salary for all employees to USD

Lets say I have a Hive table 'emp'

id    name        currency_code   salary_as_per_currency_code  
100   Surender    EUR             10
101   Raja        INR             100
102   Ajay        USD             5

I need to write a select query that should give me the below result

My expected output is

id    name        currency_code   salary_as_per_currency_code     salary_in_USD
100   Surender    EUR             10                              $10.92
101   Raja        INR             100                             $1.496                                  
102   Ajay        USD             5                               $5

My table has more Currency_code such as NZD,CAD,JPY and all.

Is there any generic builtin function in hive to do that?

Please help me on giving me a possible route to find a solution for this

Answer 1

There is no builtin function available for this conversion, but you can create your own UDF in hive. Refer the below link, which will guide you to create UDFs in hive.

UDF tutorial

all you need to do is to write your conversion logic in the below block,

public class currency_conversion extends  UDF {
public Text evaluate(String currency_code, String salary_as_per_currency_code){
----- write your logic here---
    if (currency_code.contains("INR")){
        --write your conversion logic here---
    }
}}

Let me know if any queries you have.