Hash functions in GoogleSQL

GoogleSQL for Spanner 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.
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 |
 *---+-------+-------+----------------------*/

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;