just sal's blog > Articles > 2022 > Bring your own Key for BigQuery SQL column-level encryption

Bring your own Key for BigQuery SQL column-level encryption

2022-06-26

This article covers the basic usage of SQL column-level encryption with Cloud KMS key as well as sample how you can define your own AES key for encryption.

For basic usage, we will demo both Deterministic and non-Deterministic Encryption where the AES key gets generated by BQ’s KEYS.NEW_KEYSET() function

For importing or bring your own keys, we will use TINK to generate keys and then use that key within BQ functions.

If you want to generate you own key and use python, see Using TINKKEY to create EncryptedKeyset for BigQuery.

For more information about creating a TINK key from scratch, see Import and use an external AES GCM Key with Tink

Why am i writing this?
Well, the documentation is not clear and there like no documentation anywhere for Tink or in BQ that shows how to bring your own key…

Note: You can also use Bigquery External functions to decrypt data with any KMS provider (eg AWS).

In this mode, a Cloud Run or Cloud Function performs the decryption. For more information, see

AEAD Encryption with BQ Remote Functions

Setup

First we will create a KMS key and allow your user use the KEY for both direct encryption/decryption and to allow BQ to Decrypt on your behalf.

export PROJECT_ID=`gcloud config get-value core/project`
export GCLOUD_USER=`gcloud config get-value core/account`
export KEYRING_NAME=bqr
export LOCATION=us
export KEY_NAME=k1

gcloud kms keyrings create $KEYRING_NAME --location=$LOCATION
gcloud kms keys create $KEY_NAME --keyring=$KEYRING_NAME  --purpose=encryption  --default-algorithm=google-symmetric-encryption  --location=$LOCATION

# for bq delegation
gcloud kms keys add-iam-policy-binding $KEY_NAME \
  --keyring=$KEYRING_NAME --location=$LOCATION \
  --member=user:$GCLOUD_USER \
  --role=roles/cloudkms.cryptoKeyDecrypterViaDelegation

ok, now we have a KMS key setup.. you can now generate a AEAD key using BQ’s built in function

The encrypted KMS wrapped key shown will not work for you since its encrypted by my kms key..its just here as a demonstration…you have to generate your own

bq --project_id=$PROJECT_ID query --use_legacy_sql=false '
SELECT KEYS.NEW_KEYSET("AEAD_AES_GCM_256") AS raw_keyset
'

+--------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                    raw_keyset                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| CNiR0KkOEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIGADP6eSxJY+61o2x+1HU7q4him0vNF1oBPv9iIMbNIkGAEQARjYkdCpDiAB |
+--------------------------------------------------------------------------------------------------------------------------------------------------+

# now encrypt this raw key

echo "CNiR0KkOEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIGADP6eSxJY+61o2x+1HU7q4him0vNF1oBPv9iIMbNIkGAEQARjYkdCpDiAB" \
   | base64 --decode > /tmp/decoded_key

gcloud kms encrypt --plaintext-file=/tmp/decoded_key \
    --key=projects/$PROJECT_ID/locations/$LOCATION/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME --ciphertext-file=/tmp/wrapped

# Encode the encrypted keyset so we can use it
cat /tmp/wrapped | openssl enc -base64  -A

  CiQAWDlaMHOZZDELM3qwPKk+VisKUQE+/kn51sHGViwNJYtjhA8SlQEATkaXW10Uj9g7VWY2OoqVYOO89TxVOb61iWkbnCRyOzmvVmVh6HPC5nQOE+aXNOhPkXUTb4QUphEIvXNGiZ+9/yoHbot2glnMOW5LS8HSnuxm3Pso3ao0GM3Ck8RobcTi0w3q+ucVLlQtO8OdSK+6re8WlyzSGwCTTIhxUdfiQzBK/cpgxNnUI+c2P0/MSNZY3dCYHA==

Now use that encrypted keyset in BQ function

bq query --use_legacy_sql=false  "
  DECLARE
    KMS_RESOURCE_NAME STRING;
  DECLARE
    FIRST_LEVEL_KEYSET BYTES;
  SET
    KMS_RESOURCE_NAME = 'gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME';
  SET
    FIRST_LEVEL_KEYSET = FROM_BASE64('CiQAWDlaMHOZZDELM3qwPKk+VisKUQE+/kn51sHGViwNJYtjhA8SlQEATkaXW10Uj9g7VWY2OoqVYOO89TxVOb61iWkbnCRyOzmvVmVh6HPC5nQOE+aXNOhPkXUTb4QUphEIvXNGiZ+9/yoHbot2glnMOW5LS8HSnuxm3Pso3ao0GM3Ck8RobcTi0w3q+ucVLlQtO8OdSK+6re8WlyzSGwCTTIhxUdfiQzBK/cpgxNnUI+c2P0/MSNZY3dCYHA==');
  SELECT
     ecd1.item_description, AEAD.ENCRYPT(KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME,
        FIRST_LEVEL_KEYSET),
      ecd1.item_description,
      'additional_data') as ff
  FROM
    bigquery-public-data.iowa_liquor_sales.sales AS ecd1
  LIMIT
    5;
"

+-------------------------------------------+------------------------------------------------------------------------------------------------------+
|             item_description              |                                                  ff                                                  |
+-------------------------------------------+------------------------------------------------------------------------------------------------------+
| Dekuyper Triple Sec                       |                             AeU0CNi1kvZWNOdNcMyNWVggIa+Bl94KhisOWON9YRLAVZovl5+KaccpmBs92tir0SrBAw== |
| Bacardi Gold                              |                                         AeU0CNhpPYMJmt8U+m/CHp6nuLd8xWhoTIpEdOFe8QMnu3+Nt1UF6UHNFCCY |
| Captain Morgan OSR w/50ml CM Sliced Apple | AeU0CNi6lTAM/qj3HwUDUu6zi1WYSLfgfL+O1lQaYNGsnc6cW4zTWzmFC8XXyUo1+O4QdUCZagvPReCRrbPXWPRp+o2hbTzKu5M= |
| Juarez Gold DSS                           |                                     AeU0CNivVnidaqtGtsrpS8VriVnxYASRJpa18S2V2O1jV8cq/NMeV6jRWp5+goTM |
| Tortilla Triple Sec                       |                             AeU0CNhTIQE3uZvJGq9XhRDpQaBg6w8W8H7VbIz6g2bM6GrNKPCC5YSOP16+Nk8Ikjbl7g== |
+-------------------------------------------+------------------------------------------------------------------------------------------------------+

encrypt and then decrypt:

bq query --use_legacy_sql=false  "
  DECLARE
    KMS_RESOURCE_NAME STRING;
  DECLARE
    FIRST_LEVEL_KEYSET BYTES;
  SET
    KMS_RESOURCE_NAME = 'gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME';
  SET
    FIRST_LEVEL_KEYSET = FROM_BASE64('CiQAWDlaMHOZZDELM3qwPKk+VisKUQE+/kn51sHGViwNJYtjhA8SlQEATkaXW10Uj9g7VWY2OoqVYOO89TxVOb61iWkbnCRyOzmvVmVh6HPC5nQOE+aXNOhPkXUTb4QUphEIvXNGiZ+9/yoHbot2glnMOW5LS8HSnuxm3Pso3ao0GM3Ck8RobcTi0w3q+ucVLlQtO8OdSK+6re8WlyzSGwCTTIhxUdfiQzBK/cpgxNnUI+c2P0/MSNZY3dCYHA==');
  SELECT
    ecd1.item_description, AEAD.DECRYPT_STRING( 
        KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME, FIRST_LEVEL_KEYSET),
        AEAD.ENCRYPT( 
          KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME, FIRST_LEVEL_KEYSET), 
          ecd1.item_description,
          'additional_data'),
        'additional_data') as ff
  FROM
    bigquery-public-data.iowa_liquor_sales.sales AS ecd1
  LIMIT
    5;
"

+-------------------------------------------+-------------------------------------------+
|             item_description              |                    ff                     |
+-------------------------------------------+-------------------------------------------+
| Dekuyper Triple Sec                       | Dekuyper Triple Sec                       |
| Bacardi Gold                              | Bacardi Gold                              |
| Captain Morgan OSR w/50ml CM Sliced Apple | Captain Morgan OSR w/50ml CM Sliced Apple |
| Juarez Gold DSS                           | Juarez Gold DSS                           |
| Tortilla Triple Sec                       | Tortilla Triple Sec                       |
+-------------------------------------------+-------------------------------------------+

Deterministic

Now generate a new deterministic key and do the same

bq --project_id=$PROJECT_ID query --use_legacy_sql=false '
SELECT KEYS.NEW_KEYSET("DETERMINISTIC_AEAD_AES_SIV_CMAC_256") AS raw_keyset
'
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                          raw_keyset                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CPmim/EIEoQBCngKMHR5cGUuZ29vZ2xlYXBpcy5jb20vZ29vZ2xlLmNyeXB0by50aW5rLkFlc1NpdktleRJCEkDem4BDlZbtgEVGPXt5T9asV9HWWa91Ts0EhIQI1c5mJBFdzcDVHrAfp/SE0oMJ97iTPUrpkUCmevadeiNhEHABGAEQARj5opvxCCAB |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


echo "CPmim/EIEoQBCngKMHR5cGUuZ29vZ2xlYXBpcy5jb20vZ29vZ2xlLmNyeXB0by50aW5rLkFlc1NpdktleRJCEkDem4BDlZbtgEVGPXt5T9asV9HWWa91Ts0EhIQI1c5mJBFdzcDVHrAfp/SE0oMJ97iTPUrpkUCmevadeiNhEHABGAEQARj5opvxCCAB" \
   | base64 --decode > /tmp/decoded_key


gcloud kms encrypt --plaintext-file=/tmp/decoded_key \
    --key=projects/$PROJECT_ID/locations/$LOCATION/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME --ciphertext-file=/tmp/wrapped


cat /tmp/wrapped | openssl enc -base64  -A

CiQAWDlaMGpYWGnAY58sXRWOQ3Ey7DMTuGlNCdBO1CZcUYVvNmgStgEATkaXW/hgF+imcJat5uZi+3280MIGfJH5Yz6rK4ojQzfjtzVutE+KqGfTzC5EYnhPCqjXKbISqVF3W1WHGi7TvarBF7yt4t5cyQ8Zrv2Ys+5gUsE2EXu4ce0/kx76R6pLXAiOf8F+VWJpHBSHFxIUaxX7FV44bigMdNA+2lvOLwOfo50bSMJYRFuHGQSsnKyAHpVaRzSmFLf688o8xfj5XbqW1qOtUaxJEaaTW61lS1n8Cv8t3g==

encrypt

bq query --use_legacy_sql=false  "
  DECLARE
    KMS_RESOURCE_NAME STRING;
  DECLARE
    FIRST_LEVEL_KEYSET BYTES;
  SET
    KMS_RESOURCE_NAME = 'gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME';
  SET
    FIRST_LEVEL_KEYSET = FROM_BASE64('CiQAWDlaMGpYWGnAY58sXRWOQ3Ey7DMTuGlNCdBO1CZcUYVvNmgStgEATkaXW/hgF+imcJat5uZi+3280MIGfJH5Yz6rK4ojQzfjtzVutE+KqGfTzC5EYnhPCqjXKbISqVF3W1WHGi7TvarBF7yt4t5cyQ8Zrv2Ys+5gUsE2EXu4ce0/kx76R6pLXAiOf8F+VWJpHBSHFxIUaxX7FV44bigMdNA+2lvOLwOfo50bSMJYRFuHGQSsnKyAHpVaRzSmFLf688o8xfj5XbqW1qOtUaxJEaaTW61lS1n8Cv8t3g==');
  SELECT
    ecd1.item_description,DETERMINISTIC_ENCRYPT(KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME,
        FIRST_LEVEL_KEYSET),
      ecd1.item_description,
      'additional_data') as ff
  FROM
    bigquery-public-data.iowa_liquor_sales.sales AS ecd1
  LIMIT
    5;
"

+-------------------------------------------+--------------------------------------------------------------------------------------+
|             item_description              |                                          ff                                          |
+-------------------------------------------+--------------------------------------------------------------------------------------+
| Dekuyper Triple Sec                       |                             AY4m0XmJyCv0Xi5CQRsYhRfSHfrJGWaO1lYngrIZzLmK52UhK96wiA== |
| Bacardi Gold                              |                                         AY4m0XmxFKN/59uWoyoxrhKOuYQSiDuvl1Zn67qe83DJ |
| Captain Morgan OSR w/50ml CM Sliced Apple | AY4m0XnGCTG2HAtfYdspA6Vvt//mOhtad66tTOZcTqP6bFMH3VKJY++ikpcW3em2Ipk0T7WJGSF4P6Klfxc= |
| Juarez Gold DSS                           |                                     AY4m0Xn6e+xFc9zj7O5ghs/uS4d2nKDZBvu3wRYSFC4Y11Hf |
| Tortilla Triple Sec                       |                             AY4m0Xl8WTEVbJ4uoErmIqxn77g5G/G4fS0yu5nJuvrb/xdWf3EY5A== |
+-------------------------------------------+--------------------------------------------------------------------------------------+

If you run the same query twice, you’ll see the same encrypted value…thats expected since its deterministic.

Create EncryptedKeySet using Golang

If you want to create an encrypted keyset using go, first allow your user access to directly encrypt and decrypt

# for go ADC
gcloud kms keys add-iam-policy-binding $KEY_NAME \
  --keyring=$KEYRING_NAME --location=$LOCATION \
  --member=user:$GCLOUD_USER \
  --role=roles/cloudkms.cryptoKeyDecrypter
  
gcloud kms keys add-iam-policy-binding $KEY_NAME \
  --keyring=$KEYRING_NAME --location=$LOCATION \
  --member=user:$GCLOUD_USER \
  --role=roles/cloudkms.cryptoKeyEncrypter

The following code will generate an encrypted TINK Keyset and then use that to encrypt some static text.

$  go run generate/main.go  -keyURI gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME


   Key CiQAWDlaMMg+8PDvc5BBvJFBmanJXOAwn5eh/dffF1e2NBRG6ZMSlQEATkaXW3p6bB1vQayprOET+3csaahBomxkCygsVecyXStJqpR6dtdH5bKGHb/gZDdmCcB2IzzEZ98O7wZtf4jDC71kHwq/Ei3EQKELNEOvbMhYIKCn5nPFbZzYLYpwIxkrc8F5PVjAupz3yBXJ3B43GpQuRv7sGHmYRQqTwQU6sguVHR2jKThBYamIZGKZ4BlRTCnGug==
   
   Cipher text: AZrLBEJMiaTCOo5eRL26dDz0eCLQp5HFIm8yjXRS8N3wKUhcnd9Qx1oGjO7LcA/u

## so now use that key and cipher text in BQ

bq query --use_legacy_sql=false  "
  DECLARE
    KMS_RESOURCE_NAME STRING;
  DECLARE
    FIRST_LEVEL_KEYSET BYTES;
  SET
    KMS_RESOURCE_NAME = 'gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME';
  SET
    FIRST_LEVEL_KEYSET = FROM_BASE64('CiQAWDlaMMg+8PDvc5BBvJFBmanJXOAwn5eh/dffF1e2NBRG6ZMSlQEATkaXW3p6bB1vQayprOET+3csaahBomxkCygsVecyXStJqpR6dtdH5bKGHb/gZDdmCcB2IzzEZ98O7wZtf4jDC71kHwq/Ei3EQKELNEOvbMhYIKCn5nPFbZzYLYpwIxkrc8F5PVjAupz3yBXJ3B43GpQuRv7sGHmYRQqTwQU6sguVHR2jKThBYamIZGKZ4BlRTCnGug==');
  SELECT
    AEAD.DECRYPT_STRING( 
        KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME, FIRST_LEVEL_KEYSET),
        FROM_BASE64('AZrLBEJMiaTCOo5eRL26dDz0eCLQp5HFIm8yjXRS8N3wKUhcnd9Qx1oGjO7LcA/u'),
        'additional_data') as ff
"
+-----------------+
|       ff        |
+-----------------+
| Juarez Gold DSS |
+-----------------+

Decrypt BQ Encrypted data using Golang

$ go run external/main.go -keyURI gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME \
    --encryptedKeySet "CiQAWDlaMHOZZDELM3qwPKk+VisKUQE+/kn51sHGViwNJYtjhA8SlQEATkaXW10Uj9g7VWY2OoqVYOO89TxVOb61iWkbnCRyOzmvVmVh6HPC5nQOE+aXNOhPkXUTb4QUphEIvXNGiZ+9/yoHbot2glnMOW5LS8HSnuxm3Pso3ao0GM3Ck8RobcTi0w3q+ucVLlQtO8OdSK+6re8WlyzSGwCTTIhxUdfiQzBK/cpgxNnUI+c2P0/MSNZY3dCYHA==" \
    --cipherText "AeU0CNivVnidaqtGtsrpS8VriVnxYASRJpa18S2V2O1jV8cq/NMeV6jRWp5+goTM"


Encrypted Keyset:
 {
	"encryptedKeyset": "CiQAWDlaMHOZZDELM3qwPKk+VisKUQE+/kn51sHGViwNJYtjhA8SlQEATkaXW10Uj9g7VWY2OoqVYOO89TxVOb61iWkbnCRyOzmvVmVh6HPC5nQOE+aXNOhPkXUTb4QUphEIvXNGiZ+9/yoHbot2glnMOW5LS8HSnuxm3Pso3ao0GM3Ck8RobcTi0w3q+ucVLlQtO8OdSK+6re8WlyzSGwCTTIhxUdfiQzBK/cpgxNnUI+c2P0/MSNZY3dCYHA==",
	"keysetInfo": null
}

Decrypted Keyset CNiR0KkOEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIGADP6eSxJY+61o2x+1HU7q4him0vNF1oBPv9iIMbNIkGAEQARjYkdCpDiAB

insecurecleartextkeyset Keyset:
 {
	"encryptedKeyset": "CiQAWDlaMHOZZDELM3qwPKk+VisKUQE+/kn51sHGViwNJYtjhA8SlQEATkaXW10Uj9g7VWY2OoqVYOO89TxVOb61iWkbnCRyOzmvVmVh6HPC5nQOE+aXNOhPkXUTb4QUphEIvXNGiZ+9/yoHbot2glnMOW5LS8HSnuxm3Pso3ao0GM3Ck8RobcTi0w3q+ucVLlQtO8OdSK+6re8WlyzSGwCTTIhxUdfiQzBK/cpgxNnUI+c2P0/MSNZY3dCYHA==",
	"keysetInfo": null
}{
	"primaryKeyId": 3845392600,
	"key": [
		{
			"keyData": {
				"typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey",
				"value": "GiBgAz+nksSWPutaNsftR1O6uIYptLzRdaAT7/YiDGzSJA==",
				"keyMaterialType": "SYMMETRIC"
			},
			"status": "ENABLED",
			"keyId": 3845392600,
			"outputPrefixType": "TINK"
		}
	]
}
   Plain text: Juarez Gold DS

Create EncryptedKeySet using TinkKey

You can also use tinkey to generate the encrypted keysets:

./tinkey create-keyset --key-template AES256_GCM --out-format json \
  --out encrypted_aead_keyset.json  \
  --master-key-uri gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME

$ cat encrypted_aead_keyset.json  | jq '.'
{
  "encryptedKeyset": "CiQA0Rg9Sy4mdEcA4hGeYEoGROY3eXaO9oramlmH0ZJ1kvYmWMUSlQEAQ8LPmIeWO1lbBzk7f2ZQm76PAopOhUsGXxJ8X2opwVqcoY6zJ3iEBirz1XKy08wDDcApQE8zQWTGmk8cF+kbB6OrywFldtmTViifD+6l+depOd8HwD/jsYu2/fQ1h8rCuIa0pZ2zGng2e2yXW/ug2+lfDbkbMhK/5LL6pT5CBh1UNn8ooE+9s6U1hcLHCZkgls8cSw==",
  "keysetInfo": {
    "primaryKeyId": 865308868,
    "keyInfo": [
      {
        "typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey",
        "status": "ENABLED",
        "keyId": 865308868,
        "outputPrefixType": "TINK"
      }
    ]
  }
}


./tinkey add-key --key-template AES256_GCM \
  --in encrypted_aead_keyset.json --in-format json \
  --out encrypted_aead_keyset_v2.json --out-format json \
   --master-key-uri gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME

cat encrypted_aead_keyset_v2.json  | jq '.'
{
  "encryptedKeyset": "CiQA0Rg9Sy2bAW9y5ct1/uXMOhN3W34mtR1Joa4f4ixWuVHWPGYS/AEAQ8LPmJp0ZX+ZTD1l8VIJZoSm/I9kv0bNClIwZfIuyD07e71M80v2yC1sefSwRqUIbK7B52lE+anxPFuF1cZ+lMME+Xr/xaHTbNkU0xFyZWvEUGcEEDzEkd37zrA7SaIA6mLmswBleYbbm0k/E31+/pOsEFE2OadsV4rvUECgx5Z9+AGvqpVpE/0y1005+ZxOp010HhJnWAGcWYdOnx3zaQ+mZYjsrm+8xr8ojWjufqYWwzWenpv9BK9t3Xic+g6a7YdqF2hu12V5gHaRfMDWk0zM6p/ZEiTZLrET3rrBcDGSIE4BvFziT+j+WYyOrugoZNZS1gHPbE2bHWM=",
  "keysetInfo": {
    "primaryKeyId": 865308868,
    "keyInfo": [
      {
        "typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey",
        "status": "ENABLED",
        "keyId": 865308868,
        "outputPrefixType": "TINK"
      },
      {
        "typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey",
        "status": "ENABLED",
        "keyId": 1372406102,
        "outputPrefixType": "TINK"
      }
    ]
  }
}

You can use the value of encryptedKeyset in your query as normal:

bq query --use_legacy_sql=false  "
  DECLARE
    KMS_RESOURCE_NAME STRING;
  DECLARE
    FIRST_LEVEL_KEYSET BYTES;
  SET
    KMS_RESOURCE_NAME = 'gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME';
  SET
    FIRST_LEVEL_KEYSET = FROM_BASE64('CiQA0Rg9Sy2bAW9y5ct1/uXMOhN3W34mtR1Joa4f4ixWuVHWPGYS/AEAQ8LPmJp0ZX+ZTD1l8VIJZoSm/I9kv0bNClIwZfIuyD07e71M80v2yC1sefSwRqUIbK7B52lE+anxPFuF1cZ+lMME+Xr/xaHTbNkU0xFyZWvEUGcEEDzEkd37zrA7SaIA6mLmswBleYbbm0k/E31+/pOsEFE2OadsV4rvUECgx5Z9+AGvqpVpE/0y1005+ZxOp010HhJnWAGcWYdOnx3zaQ+mZYjsrm+8xr8ojWjufqYWwzWenpv9BK9t3Xic+g6a7YdqF2hu12V5gHaRfMDWk0zM6p/ZEiTZLrET3rrBcDGSIE4BvFziT+j+WYyOrugoZNZS1gHPbE2bHWM=');
  SELECT
    ecd1.item_description, AEAD.ENCRYPT(KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME,
        FIRST_LEVEL_KEYSET),
      ecd1.item_description,
      'additional_data') as ff
  FROM
    bigquery-public-data.iowa_liquor_sales.sales AS ecd1
  LIMIT
    5;
"
+----------------------------------------------+----------------------------------------------------------------------------------------------------------+
|               item_description               |                                                    ff                                                    |
+----------------------------------------------+----------------------------------------------------------------------------------------------------------+
| Juarez Gold DSS                              |                                         ATOTkMRKVgbdsbhs1STwRHxsTIxLF+vkFkImaPQ7g7bYsfYymAvFxJr3WaPCqg9b |
| Cedar Ridge Single Malt Single Barrel Select | ATOTkMQ2u/dxwo4JKPqme+jBL3XcwnEx17tZJPfRvZ/VSTzYv2kUBDjO+LfSfZfO027pDbfM3ogjAYZx5v5LoYzPnacNRqxuH0NPUS8= |
| Members Mark Vodka                           |                                     ATOTkMTKMv4s5gI7Efqu+Tfeng9V3UF0IRPOmixJmH8ixjzw1/Fc68AWOcPnDgERfEQA |
| Kahlua Coffee Liqueur                        |                                 ATOTkMRDK0IiS63tsWsosNwiTfpSSx8S5TQDDdKTc/gd04nNp9P918KMukSoxugGhBiDLRh1 |
| Kahlua Coffee                                |                                         ATOTkMRnJ7zpey7I/LdQvlhDfqnE/BE4616dOFLdnIEzoxJAWIB4muZoKiXzsg== |
+----------------------------------------------+----------------------------------------------------------------------------------------------------------+

## then decrypt a value

bq query --use_legacy_sql=false  "
  DECLARE
    KMS_RESOURCE_NAME STRING;
  DECLARE
    FIRST_LEVEL_KEYSET BYTES;
  SET
    KMS_RESOURCE_NAME = 'gcp-kms://projects/$PROJECT_ID/locations/us/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME';
  SET
    FIRST_LEVEL_KEYSET = FROM_BASE64('CiQA0Rg9Sy2bAW9y5ct1/uXMOhN3W34mtR1Joa4f4ixWuVHWPGYS/AEAQ8LPmJp0ZX+ZTD1l8VIJZoSm/I9kv0bNClIwZfIuyD07e71M80v2yC1sefSwRqUIbK7B52lE+anxPFuF1cZ+lMME+Xr/xaHTbNkU0xFyZWvEUGcEEDzEkd37zrA7SaIA6mLmswBleYbbm0k/E31+/pOsEFE2OadsV4rvUECgx5Z9+AGvqpVpE/0y1005+ZxOp010HhJnWAGcWYdOnx3zaQ+mZYjsrm+8xr8ojWjufqYWwzWenpv9BK9t3Xic+g6a7YdqF2hu12V5gHaRfMDWk0zM6p/ZEiTZLrET3rrBcDGSIE4BvFziT+j+WYyOrugoZNZS1gHPbE2bHWM=');
  SELECT
    AEAD.DECRYPT_STRING(KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME,
        FIRST_LEVEL_KEYSET),
      FROM_BASE64('ATOTkMRKVgbdsbhs1STwRHxsTIxLF+vkFkImaPQ7g7bYsfYymAvFxJr3WaPCqg9b'),
      'additional_data') as ff
"


+-----------------+
|       ff        |
+-----------------+
| Juarez Gold DSS |
+-----------------+

Note that you can also decode the decrypted keyset using protoc

git clone https://github.com/google/tink.git
cd tink

$ echo -n "CNiR0KkOEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIGADP6eSxJY+61o2x+1HU7q4him0vNF1oBPv9iIMbNIkGAEQARjYkdCpDiAB" | base64 -d |  protoc --decode google.crypto.tink.Keyset  proto/tink.proto

primary_key_id: 3845392600
key {
  key_data {
    type_url: "type.googleapis.com/google.crypto.tink.AesGcmKey"
    value: "\032 `\003?\247\222\304\226>\353Z6\307\355GS\272\270\206)\264\274\321u\240\023\357\366\"\014l\322$"
    key_material_type: SYMMETRIC
  }
  status: ENABLED
  key_id: 3845392600
  output_prefix_type: TINK
}

Encrypting data externally

Here is a snippet in python that encryps some data using a key generated by tinkkey and then decrypts it using BQ functions

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

Bring your own Key for BigQuery SQL column-level encryption

bq kms audit log

Setup

Deterministic

Create EncryptedKeySet using Golang

Decrypt BQ Encrypted data using Golang

Create EncryptedKeySet using TinkKey

Encrypting data externally