Compression functions

GoogleSQL for BigQuery supports compression functions.

Compression functions compress or decompress bytes or string values using the Zstandard (Zstd) lossless data compression algorithm.

Function list

ZSTD_COMPRESS

ZSTD_COMPRESS(string_or_bytes_value [, [ level => ] 3])

Description

Compresses STRING or BYTES input into BYTES output using the Zstandard (Zstd) lossless data compression algorithm.

Arguments:

• string_or_bytes_value: The SQL value to compress.
• level: The Zstd compression level. The default is 3. You can set level to an integer value between -5 and 22. A higher value results in a better compression ratio at the cost of slower performance. If level is NULL, this function returns NULL.

Return type

BYTES: Base64-encoded bytes.

Example

SELECT ZSTD_COMPRESS('string_value') AS result;

/*------------------------------+
 | result                       |
 +------------------------------+
 | KLUv/SAMYQAAc3RyaW5nX3ZhbHVl |
 +------------------------------*/

SELECT ZSTD_COMPRESS(b'bytes_value', level => 1);

/*------------------------------+
 | result                       |
 +------------------------------+
 | KLUv/SALWQAAYnl0ZXNfdmFsdWU= |
 +------------------------------*/

This function returns NULL if the input is NULL:

SELECT ZSTD_COMPRESS(NULL) AS result;

/*------------+
 | result     |
 +------------+
 | NULL       |
 +------------*/

ZSTD_DECOMPRESS_TO_BYTES

ZSTD_DECOMPRESS_TO_BYTES(bytes_value [, size_limit => 128 * 1024 * 1024])

Description

Decompresses BYTES input into BYTES using the Zstandard (Zstd) lossless data compression algorithm.

Arguments:

• bytes_value: The bytes to decompress. Must be a valid Zstd frame whose header includes the uncompressed content size (which ZSTD_COMPRESS always writes).
• size_limit: The size limit of returned decompressed bytes. The default and maximum supported value is 128 MiB. You can set this limit to a lower value to minimize the risk of ZSTD_DECOMPRESS_TO_BYTES causing server memory issues. If size_limit is NULL, this function returns NULL.

Return type

BYTES: Base64-encoded bytes.

Example

SELECT ZSTD_DECOMPRESS_TO_BYTES(ZSTD_COMPRESS(b'bytes')) AS result;

/*------------+
 | result     |
 +------------+
 | Ynl0ZXM=   |
 +------------*/

If compressed bytes exceed the size_limit value, ZSTD_DECOMPRESS_TO_BYTES returns an error:

SELECT ZSTD_DECOMPRESS_TO_BYTES(ZSTD_COMPRESS(b'bytes'), size_limit => 1) AS result;

Statement failed: ZSTD output is too large: (5 bytes) > limit (1 bytes)

This function returns NULL if the input is NULL:

SELECT ZSTD_DECOMPRESS_TO_BYTES(NULL) AS result;

/*------------+
 | result     |
 +------------+
 | NULL       |
 +------------*/

ZSTD_DECOMPRESS_TO_STRING

ZSTD_DECOMPRESS_TO_STRING(bytes_value [, size_limit => 128 * 1024 * 1024])

Description

Decompress BYTES input into STRING output using the Zstandard (Zstd) lossless data compression algorithm.

Arguments:

• bytes_value: The bytes to decompress. Must be a valid Zstd frame whose header includes the uncompressed content size (which ZSTD_COMPRESS always writes).
• size_limit: The size limit of returned decompressed string. The default and maximum supported value is 128 MiB. You can set this limit to a lower value to minimize the risk of ZSTD_DECOMPRESS_TO_STRING causing server memory issues. If size_limit is NULL, this function returns NULL.

Return type

STRING

Example

SELECT ZSTD_DECOMPRESS_TO_STRING(ZSTD_COMPRESS('zstd')) AS result;

/*----------+
 | result   |
 +----------+
 | "zstd"   |
 +----------*/

If compressed bytes exceed the size_limit value, ZSTD_DECOMPRESS_TO_STRING returns an error:

SELECT ZSTD_DECOMPRESS_TO_STRING(ZSTD_COMPRESS('zstd'), size_limit => 1) AS result;

Statement failed: ZSTD output is too large: (4 bytes) > limit (1 bytes)

This function returns NULL if the input is NULL:

SELECT ZSTD_DECOMPRESS_TO_STRING(NULL) AS result;

/*------------+
 | result     |
 +------------+
 | NULL       |
 +------------*/