Category: hive

Lateral view Explode

Lateral view explode, explodes the array data into multiple rows. for example, let’s say our table look like this, where Telephone is an array of string.

Applying a lateral view explode on the above table will expand the both Telephone and Cities and do a cross join, your final table will look like this.

Lateral View POSExplode

However, this is not what you probably want, if you want to map first telephone number to first city and second with second one, and that for all the records. Then you can use posexplode (positional explode)

posexplode gives you an index along with value when you expand any error, and then you can use this indexes to map values with each other as mentioned below.

select 
    name, 
    phone_number, 
    city 
from temp.test_laterla_view_posexplode
lateral view posexplode(phone_numbers) pn as pos_phone, phone_number
lateral view posexplode(cities) pn as pos_city, city 
where 
    pos_phone == pos_city

With above query you will get following results, where phone number is mapped with corresponding city.

Predicate Pushdown in hive is a feature to Push your predicate ( where condition) further up in the query. It tries to execute the expression as early as possible in plan. 

Let’s try to understand this by example. let’s consider we have two tables, product and sales and we want to answer following question. 

How many products of brand Washington has been sold so far?

Non-Optimized Query

Following query will answer the above question. However, if you are familiar with sql you will realize that above query is not optimized.  It applies first joins the two table and then  applies the condition (predicate).

select sum(s.unit_sales) from foodmart.product p 
join 
	foodmart.sales_fact_dec_1998 s 
on 
	p.product_id = s.product_id
where 
	p.brand_name = "Washington"

Optimized Query

We could easily optimize this above query by applying condition first on product table and then joining it to sales table as mentioned below.

SELECT sum(s.unit_sales)
FROM foodmart.sales_fact_dec_1998 s
JOIN (
	SELECT product_id, brand_name
	FROM foodmart.product
	WHERE 
		brand_name = "Washington"
	) p
ON 
	p.product_id = s.product_id

This is what PPD (predicate pushdown) does internally.  if you have ppd enabled your first query will automatically be converted to a second optimized query.

Let’s see this in action.  The product table has total 1560 rows (product) with only 11 products with the brand name Washington.

For better understanding, I have disabled the vectorization.  If you are not sure what vectorization is, please read the following blog post – What is vectorization? 

Running Query with PPD Disabled

Following is the DAG of the first query with PPD disabled. 
Please set the following parameter to false, to disable the PPD.

set hive.optimize.ppd=false;

if you notice, it’s reading all rows from the product table and then passing it to the reducer for join. 

Running Query with PPD Enabled.

And Following is the DAG of the same query with PPD Enabled.
Please set the following parameter to true, to enable the PPD.

set hive.optimize.ppd=true;

Once, we enable the PPD, it first applies the condition on product table and sends only 11 rows to the reducer for join.

Vectorization in hive is a feature (available from Hive 0.13.0) which when enabled rather than reading one row at a time it reads a block on 1024 rows .  This Improves the CPU Usage for operation like,  Scan, Filter, join and aggregations. 

Note that, Vectorization is only available if data is stored in ORC format

How to Enable Vectorized Execution?

To Enable Vectorized

set hive.vectorized.execution.enabled = true;

To Disable Vectorized

set hive.vectorized.execution.enabled = false;

Difference Vectorized vs Non-Vectorized Queries. 

I have a Product Table with 1560 rows and I want to know how many products has name with Washington in it. 

Non-Vectorized Query. 

set hive.vectorized.execution.enabled = false;
select count(*) from foodmart.product 
where 
	product.product_name like "%Washington%"

In the below image you will notice that INPUT_RECORDS_PROCESSED is 1560.  

Vectorized Query

set hive.vectorized.execution.enabled = true;
select count(*) from foodmart.product 
where 
	product.product_name like "%Washington%"

In Below image you will see that INPUT_RECORDS_PROCESSED is only 2. This is because we have enabled the vectorized which rather than processing one row, it processed 1024 rows in a block. if you will divided 1560 by 1024, you will get two blocks. 1560/1024 = 2 (block has to be int value)