How to create Hive Derived Tables?

user_10891325 0
Viewed 131

What are hive derived tables, how to create it and when to use it?

Should it be created using the same CREATE TABLE statement?

For instance, regarding this primary table

CREATE TABLE NYSE (`exchange` STRING, stock_symbol STRING, stock_date DATE, stock_price_open FLOAT, stock_price_high FLOAT, stock_price_low FLOAT, stock_price_close FLOAT, stock_volume INT, stock_price_avg_close FLOAT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

How to create a derived table to aggregate and do some max/min functions?

hiveql hive hadoop database
1 Answers

hive derived tables - is a fancy name of subquery or a common table expression. Its not a physical table but can act like a table in a hive query language and thats why the name. So, you understand that, Hive derived tables are built dynamically using additional SELECT within the query. They use temporary space and discarded as soon as the query execution is completed.

Example, You want to convert sales amount into multiple currency you can use below query, where i picked up all exchange rate in a derived table and then calculated rate for USD, EUR, or INR.
Using CTE -

WITH CTE_EX_RATE as (select ex_rate, from_currency, to_currency from ex_rate_table)
select 
sales_id,
ex.ex_rate * sales_amount sales_usd ,
ex_eur.ex_rate * sales_amount sales_eur, 
ex_inr.ex_rate * sales_amount sales_inr
from sales s
left join CTE_EX_RATE ex on ex.from_currency = s.transaction_currency and ex.to_currency = 'USD'
left join CTE_EX_RATE ex_eur on ex_eur.from_currency = s.transaction_currency and ex_eur.to_currency = 'EUR'
left join CTE_EX_RATE ex_eur on ex_inr.from_currency = s.transaction_currency and ex_inr .to_currency = 'INR'

Using Subquery - You can write same query using subquery too like below. And that will create derived table as well.

select 
sales_id,
ex.ex_rate * sales_amount sales_usd ,
ex_eur.ex_rate * sales_amount sales_eur, 
ex_inr.ex_rate * sales_amount sales_inr
from sales s
left join (select ex_rate, from_currency, to_currency from ex_rate_table) ex on ex.from_currency = s.transaction_currency and ex.to_currency = 'USD'
left join (select ex_rate, from_currency, to_currency from ex_rate_table) ex_eur on ex_eur.from_currency = s.transaction_currency and ex.to_currency = 'EUR'
left join (select ex_rate, from_currency, to_currency from ex_rate_table) ex_eur on ex_inr.from_currency = s.transaction_currency and ex_inr.to_currency = 'INR'
user_64305230
Related