In this post, we will see why we need Lateral View UDTF and how to use.

As we know Hive supports complex datatypes (like array, map and struct) to store list of values for a row in a single columns and also be queried. This way you can reduce the number of rows in our table.

Now if you want to access or iterate the individual elements, we use Lateral view with Built-in Table-Generating Functions (UDTF) available in Hive. UDFT transforms a single row to multiple rows.

Lets start exploring how to use lateral view explode() function with example.

Creating table EMPLOYEE with the following columns :

• emp_id – INT
• emp_name – STRING
• assets  – ARRAY
• expenses – MAP

 
CREATE TABLE bigdataplaybook.employee(
   emp_id SMALLINT,
   emp_name STRING,
   assets ARRAY<STRING>, 
   expenses MAP<STRING,INT>
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

Now loading sample data from file and by executing below command:

cat /home/bigdataplaybook/employee.txt
1|SATISH|Laptop,VOIP,Monitor|cab:500,toll:100,meal:500
2|RAMYA|Computer,Mobile|cab:500,meal:700
3|Teja|Computer,Mobile|meal:600

LOAD DATA INPATH '/home/bigdataplaybook/employee.txt' INTO TABLE bigdataplaybook.employee;

Now we have 3 entries in employee table as seen by below select query:

hive> select * from employee;
OK
1 SATISH ["Laptop","VOIP","Monitor"] {"cab":500,"toll":100,"meal":500}
2 RAMYA ["Computer","Mobile"] {"cab":500,"meal":700}
3 Teja ["Computer","Mobile"] {"meal":600}
Time taken: 0.122 seconds, Fetched: 3 row(s)

Below query shows, how to use explode on the column of type array:

SELECT 
 e.emp_id,
 e.emp_name,
 asset_list.asset 
FROM bigdataplaybook.employee1 e 
LATERAL VIEW EXPLODE(e.assets) asset_list as asset;
OK
1 SATISH Laptop
1 SATISH VOIP
1 SATISH Monitor
2 RAMYA Computer
2 RAMYA Mobile
3 Teja Computer
3 Teja Mobile
Time taken: 0.211 seconds, Fetched: 7 row(s)

Below query shows, how to use explode on the column of type map:

SELECT 
 e.emp_id,
 e.emp_name,
 expenses_list.exp_des,
 expenses_list.exp_cost
FROM bigdataplaybook.employee1 e 
LATERAL VIEW EXPLODE(e.expenses) expenses_list as exp_des,exp_cost;
OK
1 SATISH cab 500
1 SATISH toll 100
1 SATISH meal 500
2 RAMYA cab 500
2 RAMYA meal 700
3 Teja meal 600
Time taken: 0.198 seconds, Fetched: 6 row(s)

Lateral View first applies the UDFT to each row of the base table and then joins resulting output rows to the input rows to form a virtual table with supplied table alias.

I hope this post has clarified how to use Lateral View UDTF’s in Hive.  If you want to share your thoughts, please feel free to comment below to let me know.

Happy Learning !!!

When we create Hive table on top of raw data that received from different sources, it becomes necessary to convert some values into date type or converting into different date formats.

In this blog post, we will summarize the most commonly used Hive Date Functions with some of examples on the usage of the functions.

Hive Date Functions

Hive supports most of the date functions that are exists in relational database.

Get current Date of the current system

SELECT CURRENT_DATE();
2017-06-30

Get current date with timestamp of the current system

SELECT CURRENT_TIMESTAMP();
2017-06-30 08:55:43.324

Gets current time stamp using the default time zone in UNIX epoch.

SELECT UNIX_TIMESTAMP() 
1498838153

Converts time string in format yyyy-MM-dd HH:mm:ss to Unix time stamp.

 
SELECT UNIX_TIMESTAMP('2017-06-30 08:55:53'); 
1498838153

Converts the Unix epoch to a STRING that represents DATE and TIMESTAMP of the current system timezone in the format of “1970-01-01 00:00:00”

 
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); 
2017-06-30 08:59:06

Converts the TIMESTAMP to specified format.

 
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_DATE()), 'yyyymmdd'); 
20170630

Cast Date type to different format

 
SELECT CAST(CURRENT_TIMESTAMP() AS STRING); 
2017-06-30 09:00:43.637

SELECT CAST(CURRENT_TIMESTAMP() AS BIGINT); 
1498838443

Get the date part of the TIMESTAMP in DATE format ‘YYYY-mm-dd’

 
SELECT TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())); 
2017-06-30

Get the YEAR/MONTH/DAY/HOUR/MINUTE/SECOND from the specified date

SELECT YEAR('2017-06-30 09:07:06') 
2017

SELECT MONTH('2017-06-30 09:07:06') 
6

SELECT DAY('2017-06-30 09:07:06') 
30

SELECT HOUR('2017-06-30 09:07:06') 
9

SELECT MINUTE('2017-06-30 09:07:06') 
7

SELECT SECOND('2017-06-30 09:07:06') 
6

Get the WEEKOFYEAR from the input date

 
SELECT WEEKOFYEAR('2017-06-30 09:07:06') 
26

Get day number of the week that takes input date as STRING with format (starting from Monday=1)

 
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('20170630','yyyyMMdd'),'u') 
5

Get number of days between the two specified dates

 
SELECT DATEDIFF('2017-06-12', '2017-06-02') 
10

Add days to the specified date

 
SELECT DATE_ADD('2017-06-30', 1); 
2017-07-01

Subtract the number of days to the specified date

 
SELECT DATE_SUB('2017-07-01', 2); 
2017-06-29

Change date format to specified format

select from_unixtime(unix_timestamp('2012/06/12','yyyy/MM/dd'),'yyyy-MM-dd') from table1;
2012-06-12

select from_unixtime(unix_timestamp('JUN 30 2017 053019','MMM dd yyyy HHmmss'),'yyyy-MM-dd HH:mm:ss')
2017-06-30 05:30:19

select from_unixtime(unix_timestamp('JUN 11 2017 053019 GMT','MMM dd yyyy HHmmss zzzz'),'yyyy-MM-dd HH:mm:ss')
2017-06-10 22:30:19

Select Max and Min date of the Column of String dataType

SELECT
MIN(cast(to_date(from_unixtime(unix_timestamp(colName , 'dd/MM/yyyy'))) as date)) as MinDate,
MAX(cast(to_date(from_unixtime(unix_timestamp(colName , 'dd/MM/yyyy'))) as date)) as MaxDate
FROM tableName;

Retrieve data between specific dates when date is of type String

 
select * from tableName 
WHERE 
unix_timestamp(colName, 'yyyy-MM-dd') >= unix_timestamp('2017-02-01', 'yyyy-MM-dd') 
AND
unix_timestamp(colName, 'yyyy-MM-dd') <= unix_timestamp('2017-06-30', 'yyyy-MM-dd')

This blog is mostly notes for myself from what I explored and learned. Hope this blog has been helpful.

Please drop me a comment if you like my post or have any thoughts/suggestions.

Keep Learning… Enjoy Learning… Share Learning…