Cloudera Impala Type Conversion Functions and Examples

  • Post author:
  • Post last modified:September 30, 2019
  • Post category:BigData
  • Reading time:3 mins read

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

Impala Type Conversion Functions

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

Read:

Impala 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.

Impala typeof Function

You can use the typeof function to identify the data type corresponding to an expression.

typeof(type value);

Impala typeof Function Examples

Use this function to identify data types.

select typeof(7)
+-----------+
| typeof(2) |
+-----------+
| TINYINT |
+-----------+

select typeof(now())
+---------------+
| typeof(now()) |
+---------------+
| TIMESTAMP |
+---------------+

Impala CAST Function

Impala CAST function converts the value of an expression to any other type. The result 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.

Impala CAST Function Examples

Convert INTEGER values to STRING data type

cast(value as string);

Query: select cast(10 as string)
+--------------------+
| cast(10 as string) |
+--------------------+
| 10 |
+--------------------+

Create table with CAST function:

create table test_table stored as parquet
as select
 name,
 cast(x as bigint) as x,
 cast(y as timestamp) as y,
 cast(z as smallint) as z
from test;

Impala from_unixtime Function

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]);

Impala from_unixtime Function Example

select from_unixtime(1392394861,"yyyy-MM-dd");

Query: select from_unixtime(1392394861,"yyyy-MM-dd") 
+-----------------------------------------+ 
| from_unixtime(1392394861, 'yyyy-mm-dd') | 
+-----------------------------------------+ 
| 2014-02-14 | 
+-----------------------------------------+

Impala unix_timestamp Function

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

Impala unix_timestamp Function Example

unix_timestamp(string date);

select from_unixtime(unix_timestamp(now()), 'yyyymmdd');
Query: select from_unixtime(unix_timestamp(now()), 'yyyymmdd') 
+--------------------------------------------------+ 
| from_unixtime(unix_timestamp(now()), 'yyyymmdd') | 
+--------------------------------------------------+ 
| 20171808 | 
+--------------------------------------------------+
Tags: ,