topic Re: BINARY data type in Data Engineering

BINARY data type

Aidin — Fri, 22 Dec 2023 18:57:23 GMT

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:

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?

Re: BINARY data type

quakenbush — Fri, 22 Dec 2023 22:35:48 GMT

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

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

Re: BINARY data type

Aidin — Sat, 23 Dec 2023 10:22:21 GMT

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

Re: BINARY data type

Wojciech_BUK — Sat, 23 Dec 2023 17:07:20 GMT

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

Re: BINARY data type

Aidin — Sun, 24 Dec 2023 15:03:33 GMT

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.