Defining Functions for Algorithmic Compression | Teradata Vantage - Defining Functions for Algorithmic Compression - Advanced SQL Engine - Teradata Database

SQL External Routine Programming
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™
You can use algorithmic compression to compress table columns with the following data types:
  • ARRAY
  • BYTE
  • VARBYTE
  • BLOB
  • CHARACTER
  • VARCHAR
  • CLOB
  • JSON, with some restrictions listed below
  • DATASET, with some restrictions listed below
  • TIME and TIME WITH TIME ZONE
  • TIMESTAMP and TIMESTAMP WITH TIME ZONE
  • Period types
  • Distinct UDTs, with some restrictions listed below
  • System-defined UDTs, with some restrictions listed below

You can use the CREATE TABLE or ALTER TABLE statement with the COMPRESS USING and DECOMPRESS USING options to specify the names of UDFs to use to compress and decompress the data stored in the column.

For example, consider the following table definition:

CREATE TABLE Descriptions
   (d_ID INTEGER
   ,d_Data VARCHAR(2400) COMPRESS USING Compress_Data
                         DECOMPRESS USING Decompress_Data
   );

The table definition specifies that Teradata Database is to use a UDF named Compress_Data to compress the character data in column d_Data. Similarly, to decompress the compressed data for column d_Data, Teradata Database is to use the UDF named Decompress_Data.

Although Teradata supplies some functions that you can use for algorithmic compression, these functions might not be suitable for your data. In such cases, you can write your own compression UDFs.

For information about the compression and decompression functions provided by Teradata, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

The following restrictions apply:
  • You cannot use ALC to compress columns that have a data type of structured UDT.
  • The TD_LZ_COMPRESS and TD_LZ_DECOMPRESS system functions compress all large UDTs including UDT-based system types such as Geospatial, XML, and JSON. However, if you write your own compression functions, the following restrictions apply:
    • Custom compression functions cannot be used to compress UDT-based system types (except for ARRAY and Period types).
    • Custom compression functions cannot be used to compress distinct UDTs that are based on UDT-based system types (except for ARRAY and Period types).
  • You cannot write your own compression functions to perform algorithmic compression on JSON type columns. However, Teradata provides the JSON_COMPRESS and JSON_DECOMPRESS functions that you can use to perform ALC on JSON type columns.
  • You cannot write your own compression functions to perform algorithmic compression on DATASET type columns. However, Teradata provides the SNAPPY_COMPRESS and SNAPPY_DECOMPRESS functions that you can use to perform ALC on DATASET type columns.
  • You cannot use ALC to compress temporal columns:
    • A column defined as SYSTEM_TIME, VALIDTIME, or TRANSACTIONTIME.
    • The DateTime columns that define the beginning and ending bounds of a temporal derived period column (SYSTEM_TIME, VALIDTIME, or TRANSACTIONTIME).

    You can use ALC to compress Period data types in columns that are nontemporal; however, you cannot use ALC to compress derived period columns.

    For more information about temporal tables, see Teradata Vantage™ - Temporal Table Support , B035-1182 and Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 .

  • You cannot specify multivalue or algorithmic compression for a row-level security constraint column.

UDFs that are used for algorithmic compression must be scalar UDFs.

You must thoroughly test the UDFs you develop for algorithmic compression. If the compression or decompression algorithm fails, compressed data may not be recoverable, or may be corrupted.