Get Month Name in Apache Hive & Impala

It’s sometimes important to display the month name for the specified date when creating summary reports, pivot tables, and charts. We commonly save data in the database in one of two formats: date with timestamp or plain date. Without having to develop User Defined Functions, we can get the part of the date as well as the month name using built-in functions. Let’s look at how Apache Hive and Apache Impala can help.

Apache Hive:
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(),’MMMMM’);
The query above returns the month of November.
You can use your date column instead of “CURRENT TIMESTAMP.”

Apache Impala:
Without having to think about workarounds, Apache Impala provides a large number of built-in functions. One of these is the DAYNAME function, which shows the day-name for the given date. 

SELECT MONTHNAME(NOW());
Returned: November

Please note that this function is available in the latest versions of Apache Impala. If you are using older versions then you should depend on the work-around only as shown below:

SELECT DECODE(MONTH(NOW()),
1, “January”,
2, “February”,
3, “March”,
4, “April”,
5, “May”,
6, “June”,
7, “July”,
8, “August”,
9, “September”,
10, “October”,
11, “November”,
12, “December”) as month_name;

Hope you find this article helpful.

Please subscribe for more interesting updates.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s