Select command for cloudera impala to convert hexadecimal to ip address

Question

I need select statement format for cloudera impala to convert hexadecimal to ip address.

Suppose a hexadecimal number is given (as shown below). How to convert it into impala with select statement like:-

A.59.FC.6

Answer 1

You could do this in Hive using split to split your string and conv to convert from base 16 to base 10; unfortunately although Impala does support conv, it doesn't seem that Impala has a split UDF built in, so if you're stuck with Impala you might have to write your own :(

Hive code:

 select concat(
           cast(conv(split(ipcolumn,'[.]')[0],16,10) as string), '.',
           cast(conv(split(ipcolumn,'[.]')[1],16,10) as string), '.',
           cast(conv(split(ipcolumn,'[.]')[2],16,10) as string), '.',
           cast(conv(split(ipcolumn,'[.]')[3],16,10) as string)
          )      
 from mytable;