Share via

Azure (Synpase) Binary == Hex?

Bohnstedt, Timo 1 Reputation point
2022-03-22T13:14:56.213+00:00

**Update **

I just noticed there is a spark function "bin" which works perfectly fine for my purpose.
Unluckely that function has not been implemented in Synpase DataFlow.
Is there a reason why the function is missing and can I hope that this function will be in synpase at some time?

I wonder how to get "real" binary dtypes in azure synpase.

If I use the following expression it seems to me that the function returns HEX values (see image) instead of binary type values: 

toBinary(toInteger(zlm_flags))

Since my col of interest is a flag, I actually I want to have the first bit.

Is it a bug, did I got sth. wrong here or does the preview show binarys as hex values?

Thanks for you time and help!

185590-lhe3y.png

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,695 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MartinJaffer-MSFT 26,061 Reputation points
    2022-03-23T20:10:16.117+00:00

    Hello @Bohnstedt, Timo and welcome to Microsoft Q&A.

    To my understanding, your goal is to grab the first / lowest order bit from some datatype.
    But you are hung up on converting types. Specifically the toBinary function.

    I agree things are weird with that toBinary function. I have struggled to help people with it in the past, as it doesn't appear to do what people expect.
    putting aside the toBinary function, I do have a solution for you.

    I think what you are looking for is: 

    bitwiseAnd( myInteger , 0x1 )

    All data is binary in its truest form. The data types are just different ways to read and interpret the bits. This is why we can use bitwiseAnd without needing to convert to binary. I would argue that integers are easier to work with than binary.

    From my tests, toBinary(stuff) has the same visual representation as toBase64(stuff)

    1 person found this answer helpful.
    0 comments
  2. Bohnstedt, Timo 1 Reputation point
    2022-04-12T06:45:11.247+00:00

    Hi,

    Thanks for your reply. It helped me to understand what was going on under the hood. However, my goal is not always to grab the first bit. Instead, I tried to get some bit like in this code snipped retrieved from a cockroach db: 

     when substr((\"fs_flags\"::bigint)::bit(5),0,2) = '1' then 1
 else 0 end  as \"flags_zuggelenkt\",

    So far, I transformed the int as a bit string with the help of a notebook. Afterward, I used the substring method together with a case. The case was necessary since my bitstring has no zero bits in front of the highest bit-value. If I remember my fundamentals of cs class correctly, I can only use XOR operation to retrieve first/last bits, right?

    0 comments