GoogleSQL for BigQuery supports the following hash functions.

Function list

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

Description

Computes the fingerprint of theSTRING orBYTES input using theFingerprint64 function from theopen-source FarmHash library. The outputof this function for a particular input will never change.

Return type

INT64

Examples

WITHexampleAS(SELECT1ASx,"foo"ASy,trueASzUNIONALLSELECT2ASx,"apple"ASy,falseASzUNIONALLSELECT3ASx,""ASy,trueASz)SELECT*,FARM_FINGERPRINT(CONCAT(CAST(xASSTRING),y,CAST(zASSTRING)))ASrow_fingerprintFROMexample;/*---+-------+-------+----------------------+ | x | y     | z     | row_fingerprint      | +---+-------+-------+----------------------+ | 1 | foo   | true  | -1541654101129638711 | | 2 | apple | false | 2794438866806483259  | | 3 |       | true  | -4880158226897771312 | +---+-------+-------+----------------------*/

MD5

MD5(input)

Description

Computes the hash of the input using theMD5 algorithm. The input can either beSTRING orBYTES. The string version treats the input as an array of bytes.

This function returns 16 bytes.

SELECTMD5("Hello World")asmd5;-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string./*--------------------------+ | md5                      | +--------------------------+ | sQqNsWTgdUEFt6mb5y4/5Q== | +--------------------------*/

SHA1(input)

Computes the hash of the input using theSHA-1 algorithm. The input can either beSTRING orBYTES. The string version treats the input as an array of bytes.

This function returns 20 bytes.

SELECTSHA1("Hello World")assha1;-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string./*------------------------------+ | sha1                         | +------------------------------+ | Ck1VqNd45QIvq3AZd8XYQLvEhtA= | +------------------------------*/

SHA256(input)

Computes the hash of the input using theSHA-256 algorithm. The input can either beSTRING orBYTES. The string version treats the input as an array of bytes.

This function returns 32 bytes.

Return type

BYTES

Example

SELECTSHA256("Hello World")assha256;

SHA512

SHA512(input)

Description

Computes the hash of the input using theSHA-512 algorithm. The input can either beSTRING orBYTES. The string version treats the input as an array of bytes.

This function returns 64 bytes.

Return type

BYTES

Example

SELECTSHA512("Hello World")assha512;