Hive Query Optimisation

Hive query small tips

Photo by Vivek Doshi on Unsplash

This article only talks about the optimisation on DML layer with an explanation on Hadoop MapReduce mechanisms.

Suppose we have two tables: fact_order, dim_customer. fact_order keeps all customer order transaction history, dim_customer is the main dimension table for customer attributes.

The table structures are as below:

hive> desc fact_order;
OK
order_id                    string                  None
customer_id                 string                  None
product_name                string                  None
transaction_id              string                  None
order_status                string                  None
transaction_status          string                  None
create_time                 timestamp               None
update_time                 timestamp               None
hive> desc dim_customer;
OK
id                    string                  None
name                  string                  None
gender                string…