How to run bloom filter function in BigQuery

Question

How can I run a bloom filter function on BigQuery?

I've tried to look for such a function, but I can't find it. How do I implement this?

Answer 1

Create a table with an integer column. In BigQuery integers are stored as 64 bit numbers. Don't store numbers as a sequence of 0s and 1s (binary).

Some example data:

Number

154 (in binary 10011010)
53  (in binary 00110101)
148 (in binary 10010100)
38  (in binary 00100110)
59  (in binary 00111011)
106 (in binary 01101010)

And you need to find all entries matching 24 (00011000).

Then you can run a query like:

SELECT *
 FROM
  (SELECT 154 AS n),
  (SELECT 53 AS n),
  (SELECT 148 AS n),
  (SELECT 38 AS n),
  (SELECT 59 AS n),
  (SELECT 106 AS n)
WHERE n & 24 = 24

This returns:

154
59

Soon in BigQuery you will be able to run UDF - user defined functions in the JavaScript language.