Hash functions

GoogleSQL for BigQuery supports the following hash functions.

Function list

Name	Summary
FARM_FINGERPRINT	Computes the fingerprint of a STRING or BYTES value, using the FarmHash Fingerprint64 algorithm.
MD5	Computes the hash of a STRING or BYTES value, using the MD5 algorithm.
SHA1	Computes the hash of a STRING or BYTES value, using the SHA-1 algorithm.
SHA256	Computes the hash of a STRING or BYTES value, using the SHA-256 algorithm.
SHA512	Computes the hash of a STRING or BYTES value, using the SHA-512 algorithm.

FARM_FINGERPRINT

 FARM_FINGERPRINT(value) 

Description

Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library . The output of this function for a particular input will never change.

Return type

INT64

Examples

 WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
/*---+-------+-------+----------------------*
 | 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 the MD5 algorithm . The input can either be STRING or BYTES . The string version treats the input as an array of bytes.

This function returns 16 bytes.

Return type

BYTES

Example

 SELECT MD5("Hello World") as md5;

-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
/*--------------------------*
 | md5                      |
 +--------------------------+
 | sQqNsWTgdUEFt6mb5y4/5Q== |
 *--------------------------*/ 

SHA1

 SHA1(input) 

Description

Computes the hash of the input using the SHA-1 algorithm . The input can either be STRING or BYTES . The string version treats the input as an array of bytes.

This function returns 20 bytes.

Return type

BYTES

Example

 SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
/*------------------------------*
 | sha1                         |
 +------------------------------+
 | Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
 *------------------------------*/ 

SHA256

 SHA256(input) 

Description

Computes the hash of the input using the SHA-256 algorithm . The input can either be STRING or BYTES . The string version treats the input as an array of bytes.

This function returns 32 bytes.

Return type

BYTES

Example

 SELECT SHA256("Hello World") as sha256; 

SHA512

 SHA512(input) 

Description

Computes the hash of the input using the SHA-512 algorithm . The input can either be STRING or BYTES . The string version treats the input as an array of bytes.

This function returns 64 bytes.

Return type

BYTES

Example

 SELECT SHA512("Hello World") as sha512;