Search code examples
hivebinaryhexclouderaimpala

Impala CONV function not consistently converting BASE-16 to BASE-2

I have hex strings that I need to convert to Base-2 binary strings, but I cannot get Impala to perform consistently.

E.g.

I would expect this statement:

select conv('0020008000',16,2) union
select conv('000006040A',16,2);

To return:

0000 0000 0010 0000 0000 0000 1000 0000 0000 0000
0000 0000 0000 0000 0000 0110 0000 0100 0000 1010

However, instead it's returning:

0000 0000 0010 0000 0000 0000 1000 0000 0000 0000
                          1100 0000 1000 0001 010

The 1st HEX value is converted correctly, but the 2nd is missing the first 21 digits (all zeros).

Can anyone explain why this is happening and how I can fix this behaviour?

Solution

  • Impala/Hive treats multiple leading zeros as redundant and trims them. I'm not sure if this behavior can be toggled on/off. I worked around it using lpad function.