convert integer to varbinary

phil2

Hello

Is it possible to convert int value directly into binary data type? For now I figured out that I can cast int to hex string and then convert it to binary like this HEX_TO_BINARY(LPAD(TO_HEX(i & 0xffffffff), 8, '0'))
You can say that there is no need for binary as all binary operators available for integer as well. And I would say that I need varbinary to combine many integer features togeather in a binary hash like this binary_feature1 || binary_feature2 || binary_feature3 || ...

So I'm wondering if there is something more efficient than TO_HEX -> LPAD -> HEX_TO_BINARY to convert int to binary

marcothesane

Hi Phil2 -

If you want to combine several integer based features into one variable:

Could you not do it just using bit operations on integers?

Let's say that:

• is_married is 0b1000
• has_children is 0b0100
• has_parents is 0b0010
• has_siblings is 0b0001

SQL in general does not provide the possibility of using base-2 numeric literals, unfortunately.

But I work around that by using bitwise operations.

For the constants, I create a view:

CREATE VIEW features AS                                                                                                                                           
  SELECT
    1<<3 AS is_married   -- 0b1000
  , 1<<2 AS has_children -- 0b0100
  , 1<<1 AS has_parents  -- 0b0010
  , 1<<0 AS has_siblings -- 0b0001
;

Then, I combine the features by or-ing them together:

  SELECT
      is_married 
    | has_children 
    | has_parents 
    | has_siblings 
    AS married_child_with_children_and_siblings
  ,   is_married                
    | has_parents 
    | has_siblings 
    AS married_child_with_siblings                                                                                                                       
  FROM features ;

And then, you can get the individual features back as Booleans like so:

WITH example AS (                                                                                                                                        
      SELECT
          is_married
        | has_children
        | has_parents
        | has_siblings
        AS married_child_with_children_and_siblings
      ,   is_married            
        | has_parents
        | has_siblings
        AS married_child_with_siblings
      FROM features
)
SELECT
  (married_child_with_siblings & is_married  ) <> 0 AS is_married
, (married_child_with_siblings & has_children) <> 0 AS has_children
, (married_child_with_siblings & has_parents ) <> 0 AS has_parents
, (married_child_with_siblings & has_siblings) <> 0 AS has_siblings
FROM example CROSS JOIN features;

All you'd have to do is to CROSS JOIN your queries with that one-row view ...

Just a suggestion ...

Marco

phil2

Hello, Marco.

I have too many features and int8 is not enough for me. Currently I need 16 bytes to store them. And there going to be more features. So I dont wont to split them

For now I wrote scalar UDF to perform int to binary cast. And it works much faster than string manipulations

Jim_Knicely

@phil2 - Do you mind sharing your UDX with the community?

phil2

Yep, I can do this

phil2

I finally got some free time and published some of my code.
Here we go: https://github.com/phil-88/vertica-udf/blob/master/README.md

Jim_Knicely

@phil2 - Thanks for sharing your code with the community!!!

alfiqmiq

CREATE OR REPLACE FUNCTION int2bin (input int) RETURN VARBINARY
AS BEGIN
   RETURN HEX_TO_BINARY(LPAD(TO_HEX(input & 0xffffffff), 8, '0'))::VARBINARY;
END;

SELECT int2bin(2);