BINARY data type

Aidin
New Contributor II

‎12-22-2023 10:57 AM

Hello everyone.

I'm trying to understand how BINARY data type works in spark sql. According to examples in the documentation, using cast or literal 'X' should return HEX representation of the binary data type, but when I try the same code, I see base64 representation:

Screenshot 2023-12-22 at 19.55.15.png

I can not find any reference to such behaviour in the documentation. Is this the default behaviour? If so, what is the rationale behind it?

0 Kudos

quakenbush
Contributor

‎12-22-2023 02:35 PM

Maybe to_binary is what you're looking for...?

https://docs.databricks.com/en/sql/language-manual/functions/to_binary.html

0 Kudos

Aidin
New Contributor II
In response to quakenbush

‎12-23-2023 02:22 AM

Thank you for your answer but this doesn't answer my question. I'm looking for an explanation on why the mentioned behaviour happens by default and documentation examples are not consistent with the behaviour

0 Kudos

Wojciech_BUK
Valued Contributor III

‎12-23-2023 09:07 AM

If you are confused , please look at this thread, they explain that Databricks use base64 as binary default. This is not documented but can be tracked at source code level.

https://stackoverflow.com/questions/75753311/not-getting-binary-value-in-databricks 

0 Kudos

Aidin
New Contributor II
In response to Wojciech_BUK

‎12-24-2023 07:03 AM

It seems to be the only rational explanation, although I can not reproduce the same traceback on my 13.3 LTS runtime. Seems to be an earlier runtime. It's weird that the documentation is completely wrong about the behavior.

0 Kudos