BigQuery - encryption exemples (with KMS) - AEAD and DETERMINISTIC encrypt

BigQuery - encryption exemples (with KMS).md

BigQuery - encryption exemples (with KMS)

0. Create keys in KMS
1. AEAD encrypt / decrypt
1.1. With gcloud
    1.2. With Tinkey
2. DETERMINISTIC encrypt / decrypt
    2.1. With gcloud
    2.2. With Tinkey
3. Sources/resources

0 - Create keys in KMS

Init global variables:

export GCP_PROJECT_ID='my_project' LOCATION='europe' KEYRING_NAME='keyring-data' KEY_NAME='key-encryption-data'

Create keyring:

gcloud kms keyrings create $KEYRING_NAME --location $LOCATION --project $GCP_PROJECT_ID

Create key (symmetric encrypt/decrypt):

gcloud kms keys create $KEY_NAME --keyring $KEYRING_NAME --purpose encryption --location $LOCATION --project $GCP_PROJECT_ID

You will also need to give some permissions to your final user/group/service account, for exemple:

roles/cloudkms.cryptoKeyEncrypterDecrypter

1. AEAD encrypt / decrypt

1.1. With gcloud

Generate AEAD_AES_GCM_256 keyset:

bq --project_id=$GCP_PROJECT_ID query --use_legacy_sql=false "SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256')"
# Copy the value
# COOZoMwJEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIKcbZc5XhDGriBl8pzeDpYd+yYXcS1HWwq6D5XQf4TkIGAEQARjjmaDMCSAB

# Write key in in file (bytes format)
echo "COOZoMwJEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIKcbZc5XhDGriBl8pzeDpYd+yYXcS1HWwq6D5XQf4TkIGAEQARjjmaDMCSAB" |base64 --decode > /tmp/decoded_key 
# Generate ciphertext
gcloud kms encrypt --plaintext-file=/tmp/decoded_key \
   --key=projects/$GCP_PROJECT_ID/locations/$LOCATION/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME \
   --ciphertext-file=/tmp/bankaccounts_wrapped
# Read the cipher text (solution 1):
od -An -t o1 /tmp/bankaccounts_wrapped | tr -d '\n' |tr -s ' ' | tr ' ' '\' | sed 's/.$//'
# \012\044\000\066\050\123\065\105\054\320\252\302\365\323\116\322\222\107\225\354\327\000\240\124\144\172\027\014\001\307\255\376\146\240\257\077\064\215\022\225\001\000\120\041\115\002\250\002\355\263\117\341\262\076\137\372\137\114\123\322\265\360\200\176\044\335\234\376\137\217\226\112\222\277\161\217\377\075\251\101\156\117\054\310\023\025\213\372\220\245\040\157\112\252\304\122\310\347\364\134\035\341\147\067\235\354\014\336\344\317\201\270\331\362\325\134\171\221\053\240\220\214\202\103\236\031\014\044\057\106\152\237\347\377\263\163\002\271\040\242\264\014\056\143\355\050\221\146\107\216\053\265\000\367\001\204\023\356\025\334\266\105\335\006\073\207\372\164\300\253\350\151\325\217\124\314\134\313\055\367\160\262\160\212\236\372\045\211\026\061
# Optionnal - Read the cipher text (solution 2):
python -c "f = open('/tmp/bankaccounts_wrapped', 'rb'); print(f.read()); f.close()"
# b'\n$\x006(S5E,\xd0\xaa\xc2\xf5\xd3N\xd2\x92G\x95\xec\xd7\x00\xa0Tdz\x17\x0c\x01\xc7\xad\xfef\xa0\xaf?4\x8d\x12\x95\x01\x00P!M\x02\xa8\x02\xed\xb3O\xe1\xb2>_\xfa_LS\xd2\xb5\xf0\x80~$\xdd\x9c\xfe_\x8f\x96J\x92\xbfq\x8f\xff=\xa9AnO,\xc8\x13\x15\x8b\xfa\x90\xa5 oJ\xaa\xc4R\xc8\xe7\xf4\\\x1d\xe1g7\x9d\xec\x0c\xde\xe4\xcf\x81\xb8\xd9\xf2\xd5\\y\x91+\xa0\x90\x8c\x82C\x9e\x19\x0c$/Fj\x9f\xe7\xff\xb3s\x02\xb9 \xa2\xb4\x0c.c\xed(\x91fG\x8e+\xb5\x00\xf7\x01\x84\x13\xee\x15\xdc\xb6E\xdd\x06;\x87\xfat\xc0\xab\xe8i\xd5\x8fT\xcc\\\xcb-\xf7p\xb2p\x8a\x9e\xfa%\x89\x161'

Encrypt on BigQuery:

SELECT "example_plaintext",
 AEAD.ENCRYPT(
   KEYS.KEYSET_CHAIN(
     "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME", 
b"\012\044\000\066\050\123\065\105\054\320\252\302\365\323\116\322\222\107\225\354\327\000\240\124\144\172\027\014\001\307\255\376\146\240\257\077\064\215\022\225\001\000\120\041\115\002\250\002\355\263\117\341\262\076\137\372\137\114\123\322\265\360\200\176\044\335\234\376\137\217\226\112\222\277\161\217\377\075\251\101\156\117\054\310\023\025\213\372\220\245\040\157\112\252\304\122\310\347\364\134\035\341\147\067\235\354\014\336\344\317\201\270\331\362\325\134\171\221\053\240\220\214\202\103\236\031\014\044\057\106\152\237\347\377\263\163\002\271\040\242\264\014\056\143\355\050\221\146\107\216\053\265\000\367\001\204\023\356\025\334\266\105\335\006\073\207\372\164\300\253\350\151\325\217\124\314\134\313\055\367\160\262\160\212\236\372\045\211\026\061"
     ),
   "example_plaintext",
   "additional_data");
-- AZmIDOP95mqMeh5ZTAr1rKFWpu4dhJXHZXxnXLNA+h3OAlPIFDtb1DNgxxst+sAGQCI=

Decrypt on BigQuery:

SELECT
 AEAD.DECRYPT_STRING(
   KEYS.KEYSET_CHAIN(
     "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME", 
b"\012\044\000\066\050\123\065\105\054\320\252\302\365\323\116\322\222\107\225\354\327\000\240\124\144\172\027\014\001\307\255\376\146\240\257\077\064\215\022\225\001\000\120\041\115\002\250\002\355\263\117\341\262\076\137\372\137\114\123\322\265\360\200\176\044\335\234\376\137\217\226\112\222\277\161\217\377\075\251\101\156\117\054\310\023\025\213\372\220\245\040\157\112\252\304\122\310\347\364\134\035\341\147\067\235\354\014\336\344\317\201\270\331\362\325\134\171\221\053\240\220\214\202\103\236\031\014\044\057\106\152\237\347\377\263\163\002\271\040\242\264\014\056\143\355\050\221\146\107\216\053\265\000\367\001\204\023\356\025\334\266\105\335\006\073\207\372\164\300\253\350\151\325\217\124\314\134\313\055\367\160\262\160\212\236\372\045\211\026\061"
     ),
   FROM_BASE64('AZmIDOP95mqMeh5ZTAr1rKFWpu4dhJXHZXxnXLNA+h3OAlPIFDtb1DNgxxst+sAGQCI='),
   "additional_data"),
FROM DATASET_NAME.TABLE_NAME;
-- example_plaintext

1.2. With Tinkey

tinkey create-keyset --key-template AES256_GCM  \
--master-key-uri "gcp-kms://projects/$GCP_PROJECT_ID/locations/$LOCATION/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME" \
--out encrypted_keyset.json

{"encryptedKeyset":"CiQANihTNYK4e4F0p6zIeX3QLCbm+oBb+lnloVz/Brh41yvj2pMSlQEAUCFNAqQejM3OlSmefuDwQ0Fob1bOxnxd9FdMbwiVkIM5OIn+uoAzSxIwSY54hfBEmTo9gUKgO5cJZ5pptnULg5NjKXJiCtSIOvTJBnLSDkraPzMaXh21cfde4H2YEFPaZmU1uV9LdjYoUmFhDxFBiGBFR+Nv6rze4HQeFyuaj97jUix1HXaYiTQCbQmeTm5v7/se8w==","keysetInfo":{"primaryKeyId":926751462,"keyInfo":[{"typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey","status":"ENABLED","keyId":926751462,"outputPrefixType":"TINK"}]}}

Encrpyt data in BigQuery:

DECLARE kms_resource_name string;
DECLARE first_level_keyset bytes;
SET kms_resource_name = "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME";
SET first_level_keyset = from_base64('CiQANihTNYK4e4F0p6zIeX3QLCbm+oBb+lnloVz/Brh41yvj2pMSlQEAUCFNAqQejM3OlSmefuDwQ0Fob1bOxnxd9FdMbwiVkIM5OIn+uoAzSxIwSY54hfBEmTo9gUKgO5cJZ5pptnULg5NjKXJiCtSIOvTJBnLSDkraPzMaXh21cfde4H2YEFPaZmU1uV9LdjYoUmFhDxFBiGBFR+Nv6rze4HQeFyuaj97jUix1HXaYiTQCbQmeTm5v7/se8w=='); 

SELECT "example_plaintext",
 AEAD.ENCRYPT(
   KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME, FIRST_LEVEL_KEYSET),
   "example_plaintext",
   "additional_data");

Or you can convert the base64 string to bytes, like this:

echo "CiQANihTNYK4e4F0p6zIeX3QLCbm+oBb+lnloVz/Brh41yvj2pMSlQEAUCFNAqQejM3OlSmefuDwQ0Fob1bOxnxd9FdMbwiVkIM5OIn+uoAzSxIwSY54hfBEmTo9gUKgO5cJZ5pptnULg5NjKXJiCtSIOvTJBnLSDkraPzMaXh21cfde4H2YEFPaZmU1uV9LdjYoUmFhDxFBiGBFR+Nv6rze4HQeFyuaj97jUix1HXaYiTQCbQmeTm5v7/se8w==" | base64 --decode > .tmp
od -An -t o1 .tmp | tr -d '\n' | tr -s ' ' | tr ' ' '\'
# IMPORTANT! If the last character is a '\', remove it
# \012\044\000\066\050\123\065\202\270\173\201\164\247\254\310\171\175\320\054\046\346\372\200\133\372\131\345\241\134\377\006\270\170\327\053\343\332\223\022\225\001\000\120\041\115\002\244\036\214\315\316\225\051\236\176\340\360\103\101\150\157\126\316\306\174\135\364\127\114\157\010\225\220\203\071\070\211\376\272\200\063\113\022\060\111\216\170\205\360\104\231\072\075\201\102\240\073\227\011\147\232\151\266\165\013\203\223\143\051\162\142\012\324\210\072\364\311\006\162\322\016\112\332\077\063\032\136\035\265\161\367\136\340\175\230\020\123\332\146\145\065\271\137\113\166\066\050\122\141\141\017\021\101\210\140\105\107\343\157\352\274\336\340\164\036\027\053\232\217\336\343\122\054\165\035\166\230\211\064\002\155\011\236\116\156\157\357\373\036\363
# Or alternatively you can do
# python -c "f = open('.tmp', 'rb'); print(f.read()); f.close()"
# b'\n$\x006(S5\x82\xb8{\x81t\xa7\xac\xc8y}\xd0,&\xe6\xfa\x80[\xfaY\xe5\xa1\\\xff\x06\xb8x\xd7+\xe3\xda\x93\x12\x95\x01\x00P!M\x02\xa4\x1e\x8c\xcd\xce\x95)\x9e~\xe0\xf0CAhoV\xce\xc6|]\xf4WLo\x08\x95\x90\x8398\x89\xfe\xba\x803K\x120I\x8ex\x85\xf0D\x99:=\x81B\xa0;\x97\tg\x9ai\xb6u\x0b\x83\x93c)rb\n\xd4\x88:\xf4\xc9\x06r\xd2\x0eJ\xda?3\x1a^\x1d\xb5q\xf7^\xe0}\x98\x10S\xdafe5\xb9_Kv6(Raa\x0f\x11A\x88`EG\xe3o\xea\xbc\xde\xe0t\x1e\x17+\x9a\x8f\xde\xe3R,u\x1dv\x98\x894\x02m\t\x9eNno\xef\xfb\x1e\xf3'

And use in BigQuery like this to encrypt data:

SELECT "example_plaintext",
 AEAD.ENCRYPT(
   KEYS.KEYSET_CHAIN(
      "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME", 
      b"\012\044\000\066\050\123\065\105\054\320\252\302\365\323\116\322\222\107\225\354\327\000\240\124\144\172\027\014\001\307\255\376\146\240\257\077\064\215\022\225\001\000\120\041\115\002\250\002\355\263\117\341\262\076\137\372\137\114\123\322\265\360\200\176\044\335\234\376\137\217\226\112\222\277\161\217\377\075\251\101\156\117\054\310\023\025\213\372\220\245\040\157\112\252\304\122\310\347\364\134\035\341\147\067\235\354\014\336\344\317\201\270\331\362\325\134\171\221\053\240\220\214\202\103\236\031\014\044\057\106\152\237\347\377\263\163\002\271\040\242\264\014\056\143\355\050\221\146\107\216\053\265\000\367\001\204\023\356\025\334\266\105\335\006\073\207\372\164\300\253\350\151\325\217\124\314\134\313\055\367\160\262\160\212\236\372\045\211\026\061"),
   "example_plaintext",
   "additional_data");
-- AZmIDOPspcPyIStk5iXfpcEyakWYZK3ZaLp3QgUOEryJe8EH1QZpSYTUICFvOsT8L4Y=

To decrypt data, it's the same logic.

2. DETERMINISTIC encrypt / decrypt

2.1. With gcloud

Generate DETERMINISTIC_AEAD_AES_SIV_CMAC_256 keyset:

bq --project_id=$GCP_PROJECT_ID query --use_legacy_sql=false "SELECT KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')"
# Copy the value
# CN6orqYBEoQBCngKMHR5cGUuZ29vZ2xlYXBpcy5jb20vZ29vZ2xlLmNyeXB0by50aW5rLkFlc1NpdktleRJCEkALPQu3RFANla4jWq6tRyIMqoEE/HyDUgf4Xh0wJbZApW9dGYSXh2lnu7u2DPmpCZS/unHCnKcsQj1pSxlDw5dIGAEQARjeqK6mASAB

# Write key in in file (bytes format)
echo "CN6orqYBEoQBCngKMHR5cGUuZ29vZ2xlYXBpcy5jb20vZ29vZ2xlLmNyeXB0by50aW5rLkFlc1NpdktleRJCEkALPQu3RFANla4jWq6tRyIMqoEE/HyDUgf4Xh0wJbZApW9dGYSXh2lnu7u2DPmpCZS/unHCnKcsQj1pSxlDw5dIGAEQARjeqK6mASAB" |base64 --decode > /tmp/decoded_key 
# Generate ciphertext
gcloud kms encrypt --plaintext-file=/tmp/decoded_key \
   --key=projects/$GCP_PROJECT_ID/locations/$LOCATION/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME \
   --ciphertext-file=/tmp/bankaccounts_wrapped
# Read the cipher text (solution 1):
od -An -t o1 /tmp/bankaccounts_wrapped | tr -d '\n' |tr -s ' ' | tr ' ' '\' 
# IMPORTANT! If the last character is a '\', remove it
# \012\044\000\066\050\123\065\117\256\031\072\323\375\063\027\341\227\324\112\071\243\356\143\243\343\347\216\235\056\215\376\263\251\053\322\111\375\155\022\267\001\000\120\041\115\002\242\301\154\373\141\272\333\267\140\330\371\125\216\345\377\131\311\004\311\054\134\275\333\137\013\315\041\321\130\145\216\262\115\046\011\172\330\117\342\375\006\006\256\242\324\351\351\233\212\244\073\340\215\347\342\026\133\320\011\262\326\137\027\243\141\074\303\027\223\263\333\357\331\156\340\370\264\220\174\177\303\145\303\017\172\273\131\205\051\371\306\011\310\150\341\076\230\341\150\173\345\252\306\046\330\277\242\321\125\075\337\002\267\270\334\132\041\214\047\315\314\006\300\271\273\024\362\225\344\034\126\061\141\221\227\003\122\047\014\161\010\136\263\017\061\166\320\057\360\132\043\346\343\175\261\045\327\053\044\147\261\031\012\227\364\125\124\245\103\257\157\140\334\336\101\103\102\200
# Optionnal - Read the cipher text (solution 2):
python -c "f = open('/tmp/bankaccounts_wrapped', 'rb'); print(f.read()); f.close()"
#  b"\n$\x006(S5O\xae\x19:\xd3\xfd3\x17\xe1\x97\xd4J9\xa3\xeec\xa3\xe3\xe7\x8e\x9d.\x8d\xfe\xb3\xa9+\xd2I\xfdm\x12\xb7\x01\x00P!M\x02\xa2\xc1l\xfba\xba\xdb\xb7`\xd8\xf9U\x8e\xe5\xffY\xc9\x04\xc9,\\\xbd\xdb_\x0b\xcd!\xd1Xe\x8e\xb2M&\tz\xd8O\xe2\xfd\x06\x06\xae\xa2\xd4\xe9\xe9\x9b\x8a\xa4;\xe0\x8d\xe7\xe2\x16[\xd0\t\xb2\xd6_\x17\xa3a<\xc3\x17\x93\xb3\xdb\xef\xd9n\xe0\xf8\xb4\x90|\x7f\xc3e\xc3\x0fz\xbbY\x85)\xf9\xc6\t\xc8h\xe1>\x98\xe1h{\xe5\xaa\xc6&\xd8\xbf\xa2\xd1U=\xdf\x02\xb7\xb8\xdcZ!\x8c'\xcd\xcc\x06\xc0\xb9\xbb\x14\xf2\x95\xe4\x1cV1a\x91\x97\x03R'\x0cq\x08^\xb3\x0f1v\xd0/\xf0Z#\xe6\xe3}\xb1%\xd7+$g\xb1\x19\n\x97\xf4UT\xa5C\xafo`\xdc\xdeACB\x80"

Encrypt on BigQuery:

SELECT
 DETERMINISTIC_ENCRYPT(
   KEYS.KEYSET_CHAIN(
     "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME", 
b"\012\044\000\066\050\123\065\117\256\031\072\323\375\063\027\341\227\324\112\071\243\356\143\243\343\347\216\235\056\215\376\263\251\053\322\111\375\155\022\267\001\000\120\041\115\002\242\301\154\373\141\272\333\267\140\330\371\125\216\345\377\131\311\004\311\054\134\275\333\137\013\315\041\321\130\145\216\262\115\046\011\172\330\117\342\375\006\006\256\242\324\351\351\233\212\244\073\340\215\347\342\026\133\320\011\262\326\137\027\243\141\074\303\027\223\263\333\357\331\156\340\370\264\220\174\177\303\145\303\017\172\273\131\205\051\371\306\011\310\150\341\076\230\341\150\173\345\252\306\046\330\277\242\321\125\075\337\002\267\270\334\132\041\214\047\315\314\006\300\271\273\024\362\225\344\034\126\061\141\221\227\003\122\047\014\161\010\136\263\017\061\166\320\057\360\132\043\346\343\175\261\045\327\053\044\147\261\031\012\227\364\125\124\245\103\257\157\140\334\336\101\103\102\200"
     ),
   "example_plaintext",
   "additional_data");
-- ARTLlF5BharpRGl/i2G8/meX0Z4cPy7cdSej93AYStjdc+jhVOw=

Decrypt on BigQuery:

SELECT 
 DETERMINISTIC_DECRYPT_STRING(
   KEYS.KEYSET_CHAIN(
     "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME", 
b"\012\044\000\066\050\123\065\117\256\031\072\323\375\063\027\341\227\324\112\071\243\356\143\243\343\347\216\235\056\215\376\263\251\053\322\111\375\155\022\267\001\000\120\041\115\002\242\301\154\373\141\272\333\267\140\330\371\125\216\345\377\131\311\004\311\054\134\275\333\137\013\315\041\321\130\145\216\262\115\046\011\172\330\117\342\375\006\006\256\242\324\351\351\233\212\244\073\340\215\347\342\026\133\320\011\262\326\137\027\243\141\074\303\027\223\263\333\357\331\156\340\370\264\220\174\177\303\145\303\017\172\273\131\205\051\371\306\011\310\150\341\076\230\341\150\173\345\252\306\046\330\277\242\321\125\075\337\002\267\270\334\132\041\214\047\315\314\006\300\271\273\024\362\225\344\034\126\061\141\221\227\003\122\047\014\161\010\136\263\017\061\166\320\057\360\132\043\346\343\175\261\045\327\053\044\147\261\031\012\227\364\125\124\245\103\257\157\140\334\336\101\103\102\200"
     ),
   from_base64("ARTLlF5BharpRGl/i2G8/meX0Z4cPy7cdSej93AYStjdc+jhVOw="),
   "additional_data");
-- example_plaintext

2.2. With Tinkey

tinkey create-keyset --key-template AES256_SIV  \
--master-key-uri "gcp-kms://projects/$GCP_PROJECT_ID/locations/$LOCATION/keyRings/$KEYRING_NAME/cryptoKeys/$KEY_NAME" \
--out encrypted_keyset.json

{"encryptedKeyset":"CiQANihTNYMDJCI0ZDHCBvogoNBorx8E52R0mKqiCERTW2UUmhkStQEAUCFNArXzjzx+melga8tDMzE/o0shLzQ/9Msf6GBJnKKjqvseSwRT3oZum4OzsDP6R5jwBZ1X1swRBcWiZTalEsT3gu0ys3FNnBb+qSKo62C7JMpAJFEf8nitYPru3aPPulq6Pmit+WZ3cy8HVepsWTCjJjb5vN3vM3kXFJvgN7OMF0yJni0ZK9MbIQT+JFSQOi3B1HE1enYWWOJhsJXcLuSCDRC/cP0Sw3xkzX4F4ng/igNj","keysetInfo":{"primaryKeyId":212974075,"keyInfo":[{"typeUrl":"type.googleapis.com/google.crypto.tink.AesSivKey","status":"ENABLED","keyId":212974075,"outputPrefixType":"TINK"}]}}

Encrpyt data in BigQuery:

DECLARE kms_resource_name string;
DECLARE first_level_keyset bytes;
SET kms_resource_name = "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME";
SET first_level_keyset = from_base64('CiQANihTNYMDJCI0ZDHCBvogoNBorx8E52R0mKqiCERTW2UUmhkStQEAUCFNArXzjzx+melga8tDMzE/o0shLzQ/9Msf6GBJnKKjqvseSwRT3oZum4OzsDP6R5jwBZ1X1swRBcWiZTalEsT3gu0ys3FNnBb+qSKo62C7JMpAJFEf8nitYPru3aPPulq6Pmit+WZ3cy8HVepsWTCjJjb5vN3vM3kXFJvgN7OMF0yJni0ZK9MbIQT+JFSQOi3B1HE1enYWWOJhsJXcLuSCDRC/cP0Sw3xkzX4F4ng/igNj'); 

SELECT 
 DETERMINISTIC_ENCRYPT(
   KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME, FIRST_LEVEL_KEYSET),
   "example_plaintext",
   "additional_data");
-- AQyxuftT44tHW/AnPeXwDcn1OH99PJZzVmk3LV2AhiUHehxjkq8=

Or you can convert the base64 string to bytes, like this:

echo "CiQANihTNYMDJCI0ZDHCBvogoNBorx8E52R0mKqiCERTW2UUmhkStQEAUCFNArXzjzx+melga8tDMzE/o0shLzQ/9Msf6GBJnKKjqvseSwRT3oZum4OzsDP6R5jwBZ1X1swRBcWiZTalEsT3gu0ys3FNnBb+qSKo62C7JMpAJFEf8nitYPru3aPPulq6Pmit+WZ3cy8HVepsWTCjJjb5vN3vM3kXFJvgN7OMF0yJni0ZK9MbIQT+JFSQOi3B1HE1enYWWOJhsJXcLuSCDRC/cP0Sw3xkzX4F4ng/igNj" | base64 --decode > .tmp
od -An -t o1 .tmp | tr -d '\n' | tr -s ' ' | tr ' ' '\'
# IMPORTANT! If the last character is a '\', remove it
# \012\044\000\066\050\123\065\203\003\044\042\064\144\061\302\006\372\040\240\320\150\257\037\004\347\144\164\230\252\242\010\104\123\133\145\024\232\031\022\265\001\000\120\041\115\002\265\363\217\074\176\231\351\140\153\313\103\063\061\077\243\113\041\057\064\077\364\313\037\350\140\111\234\242\243\252\373\036\113\004\123\336\206\156\233\203\263\260\063\372\107\230\360\005\235\127\326\314\021\005\305\242\145\066\245\022\304\367\202\355\062\263\161\115\234\026\376\251\042\250\353\140\273\044\312\100\044\121\037\362\170\255\140\372\356\335\243\317\272\132\272\076\150\255\371\146\167\163\057\007\125\352\154\131\060\243\046\066\371\274\335\357\063\171\027\024\233\340\067\263\214\027\114\211\236\055\031\053\323\033\041\004\376\044\124\220\072\055\301\324\161\065\172\166\026\130\342\141\260\225\334\056\344\202\015\020\277\160\375\022\303\174\144\315\176\005\342\170\077\212\003\143
# Or alternatively you can do
# python -c "f = open('.tmp', 'rb'); print(f.read()); f.close()"
# b'\n$\x006(S5\x83\x03$"4d1\xc2\x06\xfa \xa0\xd0h\xaf\x1f\x04\xe7dt\x98\xaa\xa2\x08DS[e\x14\x9a\x19\x12\xb5\x01\x00P!M\x02\xb5\xf3\x8f<~\x99\xe9`k\xcbC31?\xa3K!/4?\xf4\xcb\x1f\xe8`I\x9c\xa2\xa3\xaa\xfb\x1eK\x04S\xde\x86n\x9b\x83\xb3\xb03\xfaG\x98\xf0\x05\x9dW\xd6\xcc\x11\x05\xc5\xa2e6\xa5\x12\xc4\xf7\x82\xed2\xb3qM\x9c\x16\xfe\xa9"\xa8\xeb`\xbb$\xca@$Q\x1f\xf2x\xad`\xfa\xee\xdd\xa3\xcf\xbaZ\xba>h\xad\xf9fws/\x07U\xealY0\xa3&6\xf9\xbc\xdd\xef3y\x17\x14\x9b\xe07\xb3\x8c\x17L\x89\x9e-\x19+\xd3\x1b!\x04\xfe$T\x90:-\xc1\xd4q5zv\x16X\xe2a\xb0\x95\xdc.\xe4\x82\r\x10\xbfp\xfd\x12\xc3|d\xcd~\x05\xe2x?\x8a\x03c'

And use in BigQuery like this to encrypt data:

SELECT 
 DETERMINISTIC_ENCRYPT(
   KEYS.KEYSET_CHAIN(
    "gcp-kms://projects/GCP_PROJECT_ID/locations/LOCATION/keyRings/KEYRING_NAME/cryptoKeys/KEY_NAME", b'\012\044\000\066\050\123\065\203\003\044\042\064\144\061\302\006\372\040\240\320\150\257\037\004\347\144\164\230\252\242\010\104\123\133\145\024\232\031\022\265\001\000\120\041\115\002\265\363\217\074\176\231\351\140\153\313\103\063\061\077\243\113\041\057\064\077\364\313\037\350\140\111\234\242\243\252\373\036\113\004\123\336\206\156\233\203\263\260\063\372\107\230\360\005\235\127\326\314\021\005\305\242\145\066\245\022\304\367\202\355\062\263\161\115\234\026\376\251\042\250\353\140\273\044\312\100\044\121\037\362\170\255\140\372\356\335\243\317\272\132\272\076\150\255\371\146\167\163\057\007\125\352\154\131\060\243\046\066\371\274\335\357\063\171\027\024\233\340\067\263\214\027\114\211\236\055\031\053\323\033\041\004\376\044\124\220\072\055\301\324\161\065\172\166\026\130\342\141\260\225\334\056\344\202\015\020\277\160\375\022\303\174\144\315\176\005\342\170\077\212\003\143'),
   "example_plaintext",
   "additional_data");
-- AQyxuftT44tHW/AnPeXwDcn1OH99PJZzVmk3LV2AhiUHehxjkq8=

To decrypt data, it's the same logic.

3. Sources/resources

• https://cloud.google.com/bigquery/docs/column-key-encrypt#encrypt-columns
• https://developers.google.com/tink/install-tinkey
• https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions
• https://aliz.ai/en/blog/bigquery-compatible-encryption-in-java/
• https://cloud.google.com/kms/docs/envelope-encryption
• https://github.com/google/tink/blob/master/docs/PYTHON-HOWTO.md
• https://developers.google.com/tink/deterministic-aead