DLP encryption functions

GoogleSQL for BigQuery supports the following DLP functionsthat allow interoperable encryption and decryption between BigQuery andCloud Data Loss Prevention (Cloud DLP),usingAES-SIV.To use DLP functions, you need anew cryptographic key and then use that key to get a wrapped key.

Function list

NameSummary
DLP_DETERMINISTIC_ENCRYPTEncrypts data with a DLP compatible algorithm.
DLP_DETERMINISTIC_DECRYPTDecrypts DLP-encrypted data.
DLP_KEY_CHAINGets a data encryption key that's wrapped by Cloud Key Management Service.

DLP_DETERMINISTIC_ENCRYPT

DLP_DETERMINISTIC_ENCRYPT(key,plaintext,surrogate)
DLP_DETERMINISTIC_ENCRYPT(key,plaintext,surrogate,context)

Description

This function derives a data encryption key fromkey andcontext, and thenencryptsplaintext. You can usesurrogate to prepend theencryption result. To use DLP functions, you need anew cryptographic key and then use that key to get a wrapped key.

Definitions

  • key: A serializedBYTES value that's returned byDLP_KEY_CHAIN.key must be set toENABLED in Cloud KMS. Forinformation about how to generate a wrapped key, seegcloud kms encrypt.
  • plaintext: TheSTRING value to encrypt.
  • surrogate: ASTRING value that you can prepend to output. If you don'twant to usesurrogate, pass an empty string (enclosed in"").
  • context: A user-providedSTRING value that's used with aCloud KMS key to derive a data encryption key. For more information,seeCryptoDeterministicConfig:context.

Return data type

STRING

Examples

In the following query, the wrapped key is presented in aBYTES literal format:

SELECTDLP_DETERMINISTIC_ENCRYPT(DLP_KEY_CHAIN('gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-KEK',b'\012\044\000\325\155\264\153\246\071\172\130\372\305\103\047\342\356\061\077\014\030\126\147\041\126\150\012\036\020\202\215\044\267\310\331\014\116\233\022\071\000\363\344\230\067\274\007\340\273\016\212\151\226\064\200\377\303\207\103\147\052\267\035\350\004\147\365\251\271\133\062\251\246\152\177\017\005\270\044\141\211\116\337\043\035\263\122\340\110\333\266\220\377\247\204\215\233'),'Plaintext','','aad')ASresults;/*--------------------------------------+ | results                              | +--------------------------------------+ | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= | +--------------------------------------*/

In the following query, the wrapped key is presented in the base64 format:

DECLAREDLP_KEY_VALUEBYTES;SETDLP_KEY_VALUE=FROM_BASE64('CiQA1W20a6Y5elj6xUMn4u4xPwwYVmchVmgKHhCCjSS3yNkMTpsSOQDz5Jg3vAfguw6KaZY0gP/Dh0NnKrcd6ARn9am5WzKppmp/DwW4JGGJTt8jHbNS4EjbtpD/p4SNmw==');SELECTDLP_DETERMINISTIC_ENCRYPT(DLP_KEY_CHAIN('gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',DLP_KEY_VALUE),'Plaintext','your_surrogate','aad')ASresults;/*---------------------------------------------------------+ | results                                                 | +---------------------------------------------------------+ | your_surrogate(36):AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= | +---------------------------------------------------------*/

DLP_DETERMINISTIC_DECRYPT

DLP_DETERMINISTIC_DECRYPT(key,ciphertext,surrogate)
DLP_DETERMINISTIC_DECRYPT(key,ciphertext,surrogate,context)

Description

This function decryptsciphertext using an encryption key derived fromkeyandcontext. You can usesurrogate to prepend the decryptionresult. To use DLP functions, you need anew cryptographic key and then use that key to get a wrapped key.

Definitions

  • key: A serializedBYTES value returned byDLP_KEY_CHAIN.key must be set toENABLED in Cloud KMS. Forinformation about how to generate a wrapped key, seegcloud kms encrypt.
  • ciphertext: TheSTRING value to decrypt.
  • surrogate: ASTRING value that you can prepend to output. If you don'twant to usesurrogate, pass an empty string (enclosed in"").
  • context: ASTRING value that's used with aCloud KMS key to derive a data encryption key. For more information,seeCryptoDeterministicConfig:context.

Return data type

STRING

Examples

In the following query, the wrapped key is presented in aBYTES literal format:

SELECTDLP_DETERMINISTIC_DECRYPT(DLP_KEY_CHAIN('gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',b'\012\044\000\325\155\264\153\246\071\172\130\372\305\103\047\342\356\061\077\014\030\126\147\041\126\150\012\036\020\202\215\044\267\310\331\014\116\233\022\071\000\363\344\230\067\274\007\340\273\016\212\151\226\064\200\377\303\207\103\147\052\267\035\350\004\147\365\251\271\133\062\251\246\152\177\017\005\270\044\141\211\116\337\043\035\263\122\340\110\333\266\220\377\247\204\215\233'),'AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ=','','aad')ASresults;/*--------------------------------------+ | results                              | +--------------------------------------+ | Plaintext                            | +--------------------------------------*/

In the following query, the wrapped key is presented in the base64 format:

DECLAREDLP_KEY_VALUEBYTES;SETDLP_KEY_VALUE=FROM_BASE64('CiQA1W20a6Y5elj6xUMn4u4xPwwYVmchVmgKHhCCjSS3yNkMTpsSOQDz5Jg3vAfguw6KaZY0gP/Dh0NnKrcd6ARn9am5WzKppmp/DwW4JGGJTt8jHbNS4EjbtpD/p4SNmw==');SELECTDLP_DETERMINISTIC_DECRYPT(DLP_KEY_CHAIN('gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',DLP_KEY_VALUE),'your_surrogate(36):AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ=','your_surrogate','aad')ASresults;/*--------------------------------------+ | results                              | +--------------------------------------+ | Plaintext                            | +--------------------------------------*/

DLP_KEY_CHAIN

DLP_KEY_CHAIN(kms_resource_name,wrapped_key)

Description

You can use this function instead of thekey argument forDLP deterministic encryption functions. This function letsyou use theAES-SIV encryption functionswithout includingplaintext keys in a query. To use DLP functions, you need anew cryptographic key and then use that key to get a wrapped key.

Definitions

  • kms_resource_name: ASTRING literal that contains the resource path to theCloud KMS key.kms_resource_name can't beNULL and must residein the same Cloud region where this function is executed. This argument isused to derive the data encryption key in theDLP_DETERMINISTIC_DECRYPT andDLP_DETERMINISTIC_ENCRYPT functions. A Cloud KMS key looks likethis:

    gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key

  • wrapped_key: ABYTES literal that represents a secret text chosen by theuser. This secret text can be 16, 24, or 32 bytes. For information abouthow to generate a wrapped key, seegcloud kms encrypt.

Return data type

STRUCT

Examples

In the following query, the wrapped key is presented in aBYTES literal format:

SELECTDLP_DETERMINISTIC_ENCRYPT(DLP_KEY_CHAIN('gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',b'\012\044\000\325\155\264\153\246\071\172\130\372\305\103\047\342\356\061\077\014\030\126\147\041\126\150\012\036\020\202\215\044\267\310\331\014\116\233\022\071\000\363\344\230\067\274\007\340\273\016\212\151\226\064\200\377\303\207\103\147\052\267\035\350\004\147\365\251\271\133\062\251\246\152\177\017\005\270\044\141\211\116\337\043\035\263\122\340\110\333\266\220\377\247\204\215\233'),'Plaintext','','aad')ASresults;/*--------------------------------------+ | results                              | +--------------------------------------+ | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= | +--------------------------------------*/

In the following query, the wrapped key is presented in the base64 format:

DECLAREDLP_KEY_VALUEBYTES;SETDLP_KEY_VALUE=FROM_BASE64('CiQA1W20a6Y5elj6xUMn4u4xPwwYVmchVmgKHhCCjSS3yNkMTpsSOQDz5Jg3vAfguw6KaZY0gP/Dh0NnKrcd6ARn9am5WzKppmp/DwW4JGGJTt8jHbNS4EjbtpD/p4SNmw==');SELECTDLP_DETERMINISTIC_ENCRYPT(DLP_KEY_CHAIN('gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',DLP_KEY_VALUE),'Plaintext','','aad')ASresults;/*--------------------------------------+ | results                              | +--------------------------------------+ | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= | +--------------------------------------*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.