The following sections describe the AEAD encryption functions that BigQuery supports. For a description of how the AEAD encryption functions work, see AEAD encryption concepts.
KEYS.NEW_KEYSET
KEYS.NEW_KEYSET(key_type)
Description
Returns a serialized keyset containing a new key based on key_type
. The
returned keyset is a serialized BYTES
representation of
google.crypto.tink.Keyset
that contains a primary cryptographic key and no additional keys. You can use
the keyset with the AEAD.ENCRYPT
, AEAD.DECRYPT_BYTES
, and
AEAD.DECRYPT_STRING
functions for encryption and decryption, as well as with
the KEYS
group of key- and keyset-related functions.
key_type
is a STRING
literal representation of
the type of key to create. key_type
cannot be NULL
. The only supported value
for key_type
is currently AEAD_AES_GCM_256
.
KEYS.NEW_KEYSET(AEAD_AES_GCM_256)
creates a 256-bit key with the pseudo-random
number generator provided by OpenSSL. The key will use AES-GCM for encryption
and decryption operations.
Return Data Type
BYTES
Example
The following query creates a keyset for each row in CustomerIds
, which can
subsequently be used to encrypt data. Each keyset contains a single encryption
key with randomly-generated key data. Each row in the output contains a
customer_id
and an 'AEAD_AES_GCM_256'
key in
BYTES
.
SELECT customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset
FROM (
SELECT 1 AS customer_id UNION ALL
SELECT 2 UNION ALL
SELECT 3
) AS CustomerIds;
KEYS.ADD_KEY_FROM_RAW_BYTES
KEYS.ADD_KEY_FROM_RAW_BYTES(keyset, key_type, raw_key_bytes)
Description
Returns a serialized keyset as BYTES
with the
addition of a key to keyset
based on key_type
and raw_key_bytes
.
The primary cryptographic key remains the same as in keyset
. The expected
length of raw_key_bytes
depends on the value of key_type
. The following are
supported key_types
:
'AES_CBC_PKCS'
: Creates a key for AES decryption using cipher block chaining and PKCS padding.raw_key_bytes
is expected to be a raw keyBYTES
value of length 16, 24, or 32; these lengths have sizes of 128, 192, and 256 bits, respectively. BigQuery AEAD functions do not support keys of these types for encryption; instead, prefer'AEAD_AES_GCM_256'
or'AES_GCM'
keys.'AES_GCM'
: Creates a key for AES decryption or encryption using Galois/Counter Mode.raw_key_bytes
must be a raw keyBYTES
value of length 16 or 32; these lengths have sizes of 128 and 256 bits, respectively. When keys of this type are inputs toAEAD.ENCRYPT
, the output ciphertext does not have a Tink-specific prefix indicating which key was used as input.
Return Data Type
BYTES
Example
The following query creates a table of customer IDs along with raw key bytes,
called CustomerRawKeys
, and a table of unique IDs, called CustomerIds
. It
creates a new 'AEAD_AES_GCM_256'
keyset for each customer_id
; then it adds a
new key to each keyset, using the raw_key_bytes
value corresponding to that
customer_id
. The output is a table where each row contains a customer_id
and
a keyset in BYTES
, which contains the raw key added
using KEYS.ADD_KEY_FROM_RAW_BYTES.
WITH CustomerRawKeys AS (
SELECT 1 AS customer_id, b'0123456789012345' AS raw_key_bytes UNION ALL
SELECT 2, b'9876543210543210' UNION ALL
SELECT 3, b'0123012301230123'
), CustomerIds AS (
SELECT 1 AS customer_id UNION ALL
SELECT 2 UNION ALL
SELECT 3
)
SELECT
ci.customer_id,
KEYS.ADD_KEY_FROM_RAW_BYTES(
KEYS.NEW_KEYSET('AEAD_AES_GCM_256'),
'AES_CBC_PKCS',
(SELECT raw_key_bytes FROM CustomerRawKeys AS crk
WHERE crk.customer_id = ci.customer_id)
) AS keyset
FROM CustomerIds AS ci;
The output keysets each contain two things: the primary cryptographic key
created using KEYS.NEW_KEYSET('AEAD_AES_GCM_256')
, and the raw key added using
KEYS.ADD_KEY_FROM_RAW_BYTES
. If a keyset in the output is used with
AEAD.ENCRYPT
, BigQuery uses the primary cryptographic key created using
KEYS.NEW_KEYSET('AEAD_AES_GCM_256')
to encrypt the input plaintext. If the
keyset is used with AEAD.DECRYPT_STRING
or AEAD.DECRYPT_BYTES
, BigQuery
returns the resulting plaintext if either key succeeds in decrypting the
ciphertext.
AEAD.DECRYPT_BYTES
AEAD.DECRYPT_BYTES(keyset, ciphertext, additional_data)
Description
Uses the matching key from keyset
to decrypt ciphertext
and verifies the
integrity of the data using additional_data
. Returns an error if decryption or
verification fails.
keyset
is a serialized BYTES
value returned by
one of the KEYS
functions. keyset
must contain the key that was used to
encrypt ciphertext
, and the key must be in an 'ENABLED'
state, or else the
function returns an error. AEAD.DECRYPT_BYTES
identifies the matching key
in keyset
by finding the key with the key ID that matches the one encrypted in
ciphertext
.
ciphertext
is a BYTES
value that is the result of
a call to AEAD.ENCRYPT
where the input plaintext
was of type
BYTES
.
additional_data
is a STRING
or
BYTES
value that ensures the authenticity and
integrity of the encrypted data. This function casts any
STRING
value to BYTES
.
This must be the same as the additional_data
provided to AEAD.ENCRYPT
to
encrypt ciphertext
, ignoring its type, or else the function returns an error.
Return Data Type
BYTES
Example
This example creates a table of unique IDs with associated plaintext values and
keysets. Then it uses these keysets to encrypt the plaintext values as
BYTES
and store them in a new table. Finally, it
uses AEAD.DECRYPT_BYTES
to decrypt the encrypted values and display them as
plaintext.
The following statement creates a table CustomerKeysets
containing a column of
unique IDs, a column of AEAD_AES_GCM_256
keysets, and a column of favorite
animals.
CREATE TABLE aead.CustomerKeysets AS
SELECT
1 AS customer_id,
KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
b'jaguar' AS favorite_animal
UNION ALL
SELECT
2 AS customer_id,
KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
b'zebra' AS favorite_animal
UNION ALL
SELECT
3 AS customer_id,
KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
b'nautilus' AS favorite_animal;
The following statement creates a table EncryptedCustomerData
containing a
column of unique IDs and a column of ciphertext. The statement encrypts the
plaintext favorite_animal
using the keyset value from CustomerKeysets
corresponding to each unique ID.
CREATE TABLE aead.EncryptedCustomerData AS
SELECT
customer_id,
AEAD.ENCRYPT(keyset, favorite_animal, CAST(customer_id AS STRING))
AS encrypted_animal
FROM
aead.CustomerKeysets AS ck;
The following query uses the keysets in the CustomerKeysets
table to decrypt
data in the EncryptedCustomerData
table.
SELECT
ecd.customer_id,
AEAD.DECRYPT_BYTES(
(SELECT ck.keyset
FROM aead.CustomerKeysets AS ck
WHERE ecd.customer_id = ck.customer_id),
ecd.encrypted_animal,
CAST(ecd.customer_id AS STRING)
) AS favorite_animal
FROM aead.EncryptedCustomerData AS ecd;
AEAD.DECRYPT_STRING
AEAD.DECRYPT_STRING(keyset, ciphertext, additional_data)
Description
Like AEAD.DECRYPT_BYTES
, but where plaintext
is
of type STRING
.
Return Data Type
STRING
AEAD.ENCRYPT
AEAD.ENCRYPT(keyset, plaintext, additional_data)
Description
Encrypts plaintext using the algorithm and primary cryptographic key in the
keyset
. Incorporates additional_data
into the returned ciphertext. Returns
NULL
if any input is NULL
.
keyset
is a serialized BYTES
value returned by
one of the KEYS
functions.
plaintext
is the STRING
or
BYTES
value to be encrypted.
additional_data
is a STRING
or
BYTES
value to incorporate into the returned
ciphertext. plaintext
and additional_data
must be of the same type.
AEAD.ENCRYPT(keyset, string1, string2)
is equivalent to
AEAD.ENCRYPT(keyset, CAST(string1 AS BYTES), CAST(string2 AS BYTES))
.
The output is ciphertext BYTES
. The ciphertext will
contain a Tink-specific prefix indicating the key used to perform the
encryption, except when that key is an 'AES_GCM'
key.
Return Data Type
BYTES
Example
The following query uses the keysets for each customer_id
in the
CustomerKeysets
table to encrypt the value of the plaintext favorite_animal
in the PlaintextCustomerData
table corresponding to that customer_id
. The
output contains a column of customer_id
values and a column of
corresponding ciphertext output as BYTES
.
WITH CustomerKeysets AS (
SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset UNION ALL
SELECT 2, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') UNION ALL
SELECT 3, KEYS.NEW_KEYSET('AEAD_AES_GCM_256')
), PlaintextCustomerData AS (
SELECT 1 AS customer_id, 'elephant' AS favorite_animal UNION ALL
SELECT 2, 'walrus' UNION ALL
SELECT 3, 'leopard'
)
SELECT
pcd.customer_id,
AEAD.ENCRYPT(
(SELECT keyset
FROM CustomerKeysets AS ck
WHERE ck.customer_id = pcd.customer_id),
pcd.favorite_animal,
CAST(pcd.customer_id AS STRING)
) AS encrypted_animal
FROM PlaintextCustomerData AS pcd;
KEYS.KEYSET_FROM_JSON
KEYS.KEYSET_FROM_JSON(json_keyset)
Description
Returns the input json_keyset
STRING
as
serialized BYTES
, which is a valid input for other
KEYS
and AEAD
functions. The JSON STRING
must
be compatible with the definition of the
google.crypto.tink.Keyset
protocol buffer message: the JSON keyset should be a JSON object containing
objects and name-value pairs corresponding to those in the "keyset" message in
the google.crypto.tink.Keyset definition. You can convert the output serialized
BYTES
representation back to a JSON
STRING
using KEYS.KEYSET_TO_JSON
.
Return Data Type
BYTES
Example
KEYS.KEYSET_FROM_JSON
takes JSON-formatted STRING
values like the following:
{
"key":[
{
"keyData":{
"keyMaterialType":"SYMMETRIC",
"typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
"value":"GiD80Z8kL6AP3iSNHhqseZGAIvq7TVQzClT7FQy8YwK3OQ=="
},
"keyId":3101427138,
"outputPrefixType":"TINK",
"status":"ENABLED"
}
],
"primaryKeyId":3101427138
}
The following query creates a new keyset from a JSON-formatted
STRING
json_keyset
:
SELECT KEYS.KEYSET_FROM_JSON(json_keyset);
This returns the json_keyset
serialized as BYTES
, like the following:
\x08\x9d\x8e\x85\x82\x09\x12d\x0aX\x0a0
type.googleapis.com/google.crypto.tink.AesGcmKey\x12\"\x1a qX\xe4IG\x87\x1f\xde
\xe3)+e\x98\x0a\x1c}\xfe\x88<\x12\xeb\xc1t\xb8\x83\x1a\xcd\xa8\x97\x84g\x18\x01
\x10\x01\x18\x9d\x8e\x85\x82\x09 \x01
KEYS.KEYSET_TO_JSON
KEYS.KEYSET_TO_JSON(keyset)
Description
Returns a JSON STRING
representation of the input
keyset
. The returned JSON STRING
is compatible
with the definition of the
google.crypto.tink.Keyset
protocol buffer message. You can convert the JSON
STRING
representation back to
BYTES
using KEYS.KEYSET_FROM_JSON
.
Return Data Type
STRING
Example
The following query returns a new 'AEAD_AES_GCM_256'
keyset as a
JSON-formatted STRING
.
SELECT KEYS.KEYSET_TO_JSON(KEYS.NEW_KEYSET('AEAD_AES_GCM_256'));
The result is a STRING
like the following.
{
"key":[
{
"keyData":{
"keyMaterialType":"SYMMETRIC",
"typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
"value":"GiD80Z8kL6AP3iSNHhqseZGAIvq7TVQzClT7FQy8YwK3OQ=="
},
"keyId":3101427138,
"outputPrefixType":"TINK",
"status":"ENABLED"
}
],
"primaryKeyId":3101427138
}
KEYS.ROTATE_KEYSET
KEYS.ROTATE_KEYSET(keyset, key_type)
Description
Adds a new key to keyset
based on key_type
. This new key becomes the primary
cryptographic key of the new keyset. Returns the new keyset serialized as
BYTES
.
The old primary cryptographic key from the input keyset
remains an additional
key in the returned keyset.
Return Data Type
BYTES
Example
The following statement creates a table containing a column of unique
customer_id
values and 'AEAD_AES_GCM_256'
keysets. Then, it creates a new
primary cryptographic key within each keyset in the source table using
KEYS.ROTATE_KEYSET
. Each row in the output contains a customer_id
and an
'AEAD_AES_GCM_256'
keyset in BYTES
.
WITH ExistingKeysets AS (
SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset
UNION ALL
SELECT 2, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') UNION ALL
SELECT 3, KEYS.NEW_KEYSET('AEAD_AES_GCM_256')
)
SELECT customer_id, KEYS.ROTATE_KEYSET(keyset, 'AEAD_AES_GCM_256') AS keyset
FROM ExistingKeysets;
KEYS.KEYSET_LENGTH
KEYS.KEYSET_LENGTH(keyset)
Description
Returns the number of keys in the provided keyset.
Return Data Type
INT64
Example
This example references a JSON-formatted STRING
called json_keyset
that contains two keys:
{
"primaryKeyId":1354994251,
"key":[
{
"keyData":{
"keyMaterialType":"SYMMETRIC",
"typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
"value":"GiD9sxQRgFj4aYN78vaIlxInjZkG/uvyWSY9a8GN+ELV2Q=="
},
"keyId":1354994251,
"outputPrefixType":"TINK",
"status":"ENABLED"
}
],
"key":[
{
"keyData":{
"keyMaterialType":"SYMMETRIC",
"typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
"value":"PRn76sxQRgFj4aYN00vaIlxInjZkG/uvyWSY9a2bLRm"
},
"keyId":852264701,
"outputPrefixType":"TINK",
"status":"DISABLED"
}
]
}
The following query converts json_keyset
to a keyset and then returns
the number of keys in the keyset:
SELECT KEYS.KEYSET_LENGTH(KEYS.KEYSET_FROM_JSON(json_keyset)) as key_count;
+-----------+
| key_count |
+-----------+
| 2 |
+-----------+