Apache Hive Type Conversion Functions and Examples

Apache Hive has some very strict rules regarding data types for function parameters that you provide while executing it. Hive type conversion functions are used to explicitly convert to the required type and format. For example, Hive does not convert DOUBLE to FLOAT, INT to STRING etc.

In my other post, we have discussed on Hive date functions and examples. In this article, we will check out Cloudera Hive type conversion functions with some examples.

Commonly used Apache Hive Date Functions and Examples

Apache Hive Type Conversion Functions

These type conversion functions uses common calling functions i.e. the first argument is the value to be formatted, and the second argument is a template that defines the output or input format.

Unlike Impala, Hive does not provide support to typeof function to verify the variable or column data types.

Now let use check data type conversion function available with Apace Hive.

Apache Hive CAST Function

Hive CAST function converts the value of an expression to any other type. The result of the function will be NULL in case if function cannot converts to particular data type.

You can use this function when passing a column value or literal to a function that expects a parameter with a different data type.

CAST (<from-type> AS <to-type>);

from-type & to-type could be any data type.

Hive CAST Function Examples

Below are the some of Hive CAST function examples.

Convert STRING to INTEGER values data type

 select cast('10' as int);
+------+--+
| _c0  |
+------+--+
| 10   |
+------+--+
1 row selected (0.141 seconds)

Convert INTEGER values to STRING data type

 select cast(10 as string);
+------+--+
| _c0  |
+------+--+
| 10   |
+------+--+
1 row selected (0.088 seconds)

Use CAST in Hive create table Command

CREATE TABLE test_table STORED AS ORC AS 
SELECT Cast(x AS BIGINT)    AS x, 
       Cast(y AS TIMESTAMP) AS y, 
       Cast(z AS SMALLINT)  AS z 
FROM   test;

Hive Create Table command and Examples

Hive from_unixtime Function

This function is useful when you are working with unix epoch. This function converts the number of seconds from the UNIX epoch to the specified time into a string in the local time zone. Second parameter determines the format of the date type.

from_unixtime(bigint unixtime[, string format]);

Hive from_unixtime Function Example

For example, convert 1392394861 epoch value to “yyyy-MM-dd” format.

select from_unixtime(1392394861,"yyyy-MM-dd");
+-------------+--+
|     _c0     |
+-------------+--+
| 2014-02-14  |
+-------------+--+
1 row selected (0.226 seconds)

Hive unix_timestamp Function

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

Hive unix_timestamp Function Example

SELECT unix_timestamp('15-Jun-19  10.00.00 AM', 'dd-MMM-yy hh.mm.ss a');
+-------------+--+
|     _c0     |
+-------------+--+
| 1560573000  |
+-------------+--+
1 row selected (0.529 seconds)

Hope this helps 🙂

This Post Has 2 Comments

Jingchen Liu October 2, 2019

Teradata Analytics Functions and Examples

Hi Vithal S,

Very useful article, just want to comment on last_value example, it did not return the max insurance amount, if you compare with your max() aggregation function.

I guess you might need to add something like

, LAST_VALUE(b) OVER (PARTITION BY a ORDER BY b DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”

To get the correct value.

Regards,
Jingchen
1. Vithal S October 3, 2019
  
  Hi Jingchen,
  
  thanks for information.
  
  Thanks

Comments are closed.