BigQuery MD5, SHA1, SHA256, SHA512 Hash Functions

BigQuery provides quite a few built-in hash functions including MD5, SHA1, SHA256, SHA512 and FARM_FINGERPRINT.

Code snippet

The following standard SQL scripts use those functions:

SELECT MD5('ABC') AS HASH_MD5,
SHA1('ABC') AS HASH_SHA1,
SHA256('ABC') AS HASH_SHA256,
SHA512('ABC') AS HASH_SHA512,
FARM_FINGERPRINT('ABC') AS HASH_FARM_FINGERPRINT;

Results in JSON format:

[
{
"HASH_MD5": "kC+90rHfDE9wtKXSNSXpMg==",
"HASH_SHA1": "PAG9uybzWLqyfyZ5JKosmgP8/bg=",
"HASH_SHA256": "tdQEXD9Gb6kf4sxqvnkjKhpXzfEE96JucW4KHieJ33g=",
"HASH_SHA512": "OXEY/ayNg62YgTxQdZyFuMR1Zdgmi/ENpIMVO3R6dHQ6WKkOhaqfcFzmmE/8Eo21Z0iYF+QJLQUNihzFlt3BGQ==",
"HASH_FARM_FINGERPRINT": "228873345217803866"
}]

For the first 4 functions, the results are bytes type and are displayed as BASE64 string.

Code snippet

In this article