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.
Related Article
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;
Related Article
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)
Related Articles
Hope this helps 🙂
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
Hi Jingchen,
thanks for information.
Thanks