Categories:

String & Binary Functions (Compression/Decompression)

DECOMPRESS_BINARY

Decompresses the compressed BINARY input parameter.

See also:

COMPRESS , DECOMPRESS_STRING

Syntax

DECOMPRESS_BINARY(<input>, <method>)

Arguments

Required:

input

A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS.

If you attempt to decompress a compressed string, rather than a compressed BINARY value, you will not get an error; the function will return a BINARY value. See the Usage Notes below for details.

method

The compression method originally used to compress the input. See COMPRESS for a list of compression methods.

The DECOMPRESS_BINARY method, unlike the COMPRESS method, does not require you to specify the compression level. If you do specify the compression level, DECOMPRESS_BINARY ignores it and uses the actual compression level.

Returns

The data type of the returned value is BINARY.

Usage Notes

  • If the compression method is unknown or invalid, the query fails.

  • The compression method name, e.g. ZLIB is case insensitive.

  • The DECOMPRESS_BINARY function can decompress data that was originally in string format. However, the output of DECOMPRESS_BINARY will still be BINARY, not string. For example SELECT DECOMPRESS_BINARY(COMPRESS('Hello', 'SNAPPY), 'SNAPPY') returns a BINARY value; if you display that value, it is shown as 48656C6C6F, which is the hexadecimal representation of ‘Hello’. To avoid confusion, Snowflake recommends decompressing string data by using DECOMPRESS_STRING rather than DECOMPRESS_BINARY.

Returns

A BINARY value with decompressed data.

Examples

This shows a simple example of decompressing BINARY data that contains a compressed value.

SELECT DECOMPRESS_BINARY(TO_BINARY('0920536E6F77666C616B65', 'HEX'), 'SNAPPY');
+-------------------------------------------------------------------------+
| DECOMPRESS_BINARY(TO_BINARY('0920536E6F77666C616B65', 'HEX'), 'SNAPPY') |
|-------------------------------------------------------------------------|
| 536E6F77666C616B65                                                      |
+-------------------------------------------------------------------------+