Cfrgtkky

Our team is currently exploring the ways to encrypt PII data on the field level within BigQuery and we found out the following way to encrypt/decrypt using Crypto-JS:

#standardSQL

CREATE TEMPORARY FUNCTION encrypt(_text STRING) RETURNS STRING LANGUAGE js AS

"""

  let key = CryptoJS.enc.Utf8.parse("<key>");

  let options = { iv: CryptoJS.enc.Utf8.parse("<iv>"), mode: CryptoJS.mode.CBC };

  let _encrypt = CryptoJS.AES.encrypt(_text, key, options);

  return _encrypt;

""";

CREATE TEMPORARY FUNCTION decrypt(_text STRING) RETURNS STRING LANGUAGE js AS

"""

  let key = CryptoJS.enc.Utf8.parse("<key>");

  let options = { iv: CryptoJS.enc.Utf8.parse("<iv>"), mode: CryptoJS.mode.CBC };

  let _decrypt = CryptoJS.AES.decrypt(_text, key, options).toString(CryptoJS.enc.Utf8);

  return _decrypt;

""" OPTIONS (library="gs://path/to/Crypto-JS/crypto-js.js");



-- query to encrypt fields

SELECT

  <fields>, encrypt(<pii-fields>)

FROM

  `<project>.<dataset>.<table>`



-- query to decrypt fields

SELECT

      <fields>, decrypt(<pii-fields>)

    FROM

      `<project>.<dataset>.<table>`

I am trying to benchmark the performance of AES CBC encryption & decryption using Crypto JS library in the big query before deploying it into our production. We found out the rate of data to encrypt & decrypt is growing exponential per records with increasing number of data compared to the usual query. However with the increasing number of data to process, the progress of processing per record & record processing time is improving.

As there are no available documentation regarding this, could someone from the community help provide better ways, optimize query, best practices to use field level encryption & decryption within the big query?