question unhex in Impala in Archives of Support Questions (Read Only)

unhex in Impala

scratch28 — Fri, 16 Sep 2022 10:26:22 GMT

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

Re: unhex in Impala

casey — Mon, 20 Jun 2016 21:52:39 GMT

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                 |
+--------------------+

Re: unhex in Impala

scratch28 — Tue, 21 Jun 2016 17:21:44 GMT

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

Re: unhex in Impala

casey — Tue, 21 Jun 2016 18:54:45 GMT

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.