unhex in Impala

[#1]

Running in Impala I try the following 

select unhex('4B')

and I get a string result of K

If I go to Hex to decimal converter to convert hex value '4B' I get the number 75 which is what I expect

Is unhex not a hexadecimal to decimal converter?

according to http://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_math_functions.html

unhex(string a)

Purpose: Returns a string of characters with ASCII values corresponding to pairs of hexadecimal digits in the argument.

Return type: string

I don't even know where to begin to start troubleshooting this

[#2 · Accepted solution]

Hello,

Impala implemented this function for Hive compatibility. The behavior is the same is in Hive. I'm not sure why Hive decided to implement the function this way. The input "4B" is converted to a character using the same logic as shown in http://www.asciitable.com/, you'll see "4B" corresponds to the character "K" (column "Hx" to column "Chr"). In the "4B" case, the ASCII() function can be used to give the value you expected.

Query: select ascii(unhex('4b'))
+--------------------+
| ascii(unhex('4b')) |
+--------------------+
| 75                 |
+--------------------+

[#3]

Thanks very helpful

but the ASCII function only translates the first character

and the decode function (in hive) is in conflict with Impala interpretation of decode UDF to be similar to case (a poor decision IMHO since case is much more readable than decode)   Anyway opinions aside

How do I convert a large hex value into a large number?

  
   select ascii(unhex('130F9C6D6A2972')) largenumber
   , ascii(unhex('13')) firstpart 

both yeild the value of 19

when the first one suppose to yeild 5365189082491250

[#4]

Unfortunately there doesn't seem to be a function that does this. If you are familiar with Java or C++, writing a UDF shouldn't be too hard. UDF documentation is at http://www.cloudera.com/documentation/enterprise/latest/topics/impala_udf.html and of course if you have questions we can help you here.