How to convert BASE64 to HEX in Google BigQuery

Finally!

Biguery released on December 2016 their hashed functions. This was a relief until we discovered that the only possible output codec was … BASE64 (This is how BYTES are represented in BQ). And yes, that was literally killing us as all our representations of these hashed were in hexadecimal!

Our solution

Of course there is no base conversion function integrated in BQ! So we found a way to create our own function, using BQ’s UDF (User Defined Functions), and we have to tell: it rocks!!

A UDF is a function we can define inside our query (have look to the example bellow). This function helps to calculate some fields with a personalised logic: for example, we could create a SQUARED function which take a value, and multiply it by itself. Another example, would be to calculate a distance between 2 couples of coordinates.

Example: Convert BQ’s BASE64 STRING to its representation in HEX

UDF functions examples

• Convert accented characters to their plain ascii equivalents

– ae