BigQuery HMAC-SHA256 User-defined Function

2020-07-20

Google Cloud BigQuery User-defined function to perform HMAC with SHA256. BigQuery built in functions support various HASH functions like SHA256 but currently (6/21/20) do not support HMAC.

The following Standard SQL User-defined function for BQ provides a simple way to do hmac+sha.

As with a UDF, you need to source javascript.

The library below uses crypto-js:

<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.9-1/crypto-js.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.9-1/hmac-sha256.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.9-1/enc-base64.min.js"></script>
<script>
  var hash = CryptoJS.HmacSHA256("Message", "secret");
  var hashInBase64 = CryptoJS.enc.Base64.stringify(hash);
  document.write(hashInBase64);
</script>

Which you can import into a UDF as shown here

First download that file (or create your own from the sources above. THen upload the js file to your GCS bucket eg,

wget https://gist.githubusercontent.com/salrashid123/839e5aab524312c15469ad85a0d940c5/raw/d775f5c28809d524aebbc52e165078e2f86fd6df/bigquery_udf.js

gsutil cp bigquery_udf.js gs://your_bucket/bigquery_udf.js

Then reference that in a query

CREATE TEMP FUNCTION myFunc(a STRING, b STRING)
  RETURNS STRING
  LANGUAGE js AS
"""  
    return hmacsha256(a,b);
"""    
OPTIONS (
  library=["gs://mineral-minutia-820/bigquery_udf.js"]
);
SELECT
  title,locations, myFunc(locations,"mypassword")
FROM
  `bigquery-public-data.san_francisco_film_locations.film_locations`;

images/bq_hmac_1.png

The output column will show the hmac hash of the “locations” column

{
    "title": "Greed",
    "locations": "Bush and Sutter Streets",
    "f0_": "YjOp5hNQofOJbzSC7hOermCLXWTHE6jslKtW9GOteMg="
  },

To verify, just use openssl for HMAC

$ echo -n "Bush and Sutter Streets" | openssl dgst -sha256 -hmac "mypassword"  -hex -r | xxd -r -p | base64
YjOp5hNQofOJbzSC7hOermCLXWTHE6jslKtW9GOteMg=

This site supports webmentions. Send me a mention via this form.