The Teradata Columnar Database Feature

Roland Wenzlofsky

April 17, 2014

minutes reading time


Historically, relational databases like Teradata stored data as logical rows. For the primary purposes of relational databases, namely, transaction handling and querying the data, this was a helpful way of laying out the data on the disks. Columns of a record are kept together, laid out in the same data blocks.

Various types of indexes have been introduced for faster access to the rows. While many relational databases rely heavily on B+ trees for index storage, Teradata uses a hashing algorithm to look up the table rows (an exception is the non-unique secondary index, which is designed differently).

As long as the quantity of data is relatively small, the data layout in rows allows for fast data access. OLTP and strategic queries can co-exist together. The data retrieval can be sped up by using indexes.

In the times of Big Data, Teradata must store an ever-increasing number of rows. At the same time, the number of columns per row is snowballing.

This leads to the situation that queries probably only need a few columns. Unfortunately still have to fetch all columns.

The number of IOs required to access the data increases with the width of the data rows. From a performance and storage perspective, this makes query processing less effective. Even if only one column is accessed, Teradata must move the complete row from the disk to the AMP’s FSG cache.

Therefore, column-oriented databases have been introduced, offering an alternative way of laying out the data on the disks. Teradata 14.00 incorporates this column-orientated storage by adding the columnar feature.

Columnar tables are No Primary Index (NOPI) tables. Therefore, no hashing algorithm is used to distribute the data across the AMPs. This further means the usual restrictions for NOPI tables: Only MULTISET tables are allowed, no identity column can be used, we can add no hash indexes, etc.

In a column-orientated database, as opposed to a row-orientated database, single columns or sets of columns are stored together in data blocks, not rows. The columns making up one row are randomly distributed across different data blocks.

However, columns belonging together are handled by the same AMP, one AMP still holds the data from an entire row, but it’s a vertical partitioning.

Below you can see an example showing the difference between the row storage and the columnar storage:

teradata columnar

All containers hold the same number of rows in the same order (the relative row number). A query selecting only “Column A” will only require moving this one column into the AMP’s FSG cache.

You may wonder how the retrieving of a record works in case of a query (again related to the above example) which looks like this:

SELECT COLUMN_A FROM THE_TABLE WHERE COLUMN_B = 'C';

To look up the related value for COLUMN_A, both column stores (COLUMN_A and COLUMN_B) must be moved to the FSG cache. The data’s exact order across containers allows picking up the value of COLUMN_A via the relative row number (3 in this example).

The simple syntax for creating a columnar table in Teradata 14.00 is the following:

CREATE TABLE the_table
( PK INTEGER,
COLUMN_A CHAR(01),
COLUMN_B CHAR(01)
) NO PRIMARY INDEX
PARTITION BY COLUMN;

This will create one container per column. To create a multicolumn container, the syntax below has to be used:

CREATE TABLE the_table
(
PK INTEGER,
COLUMN_A CHAR(01),
COLUMN_B CHAR(01)
) NO PRIMARY INDEX
PARTITION BY COLUMN (PK, (COLUMN_A,COLUMN_B))
;

PK will be placed in a single container, but columns COLUMN_A and COLUMN_B will be stored together in a second multicolumn container.

Teradata implements two different kinds of physical column store designs. Columns can be stored in so-called containers, physical rows holding one or several columns of a table together. Each container holds the same number of rows, and each row has a relative row number (used to find columns from the same date row together).

Teradata allows to create column containers per column or to create multicolumn containers. Furthermore, Teradata enables a kind of mixed storing of the above formats.

Sets of columns can be stored like traditional rows, while the rest of the columns are stored in column containers. When column storage is used, Teradata decides what is the most appropriate storage format on its own. This mixed kind of storage can be achieved with a statement such as the following:

CREATE TABLE <TABLE>
(
PK INTEGER,
COLUMN_1 INTEGER,
COLUMN2_2 INTEGER
) NO PRIMARY INDEX
PARTITION BY COLUMN (COLUMN_1, ROW(PK,COLUMN_2);

Such mixed setups make sense in specific situations. In the previous example, COLUMN_1 may be highly compressible, while PK and COLUMN_2 are often used in queries.

One container is one block of data only containing a subset of columns belonging to several rows. If the space of one container is not sufficient to hold the column values of all rows, new containers are created on the fly.

Column-orientated data storage is especially useful in OLAP, where a vast amount of data is accessed. Still, only a few columns are relevant for the result set (“sum of the balance of all customers,” for example).

In Teradata, column stored tables are not hashed in the usual way. Therefore, Primary Index access is not possible.

As we want to see “all customers” information in OLAP, indexing the data (which the Teradata hashing algorithm does) does not improve performance. Such all-row retrievals render direct access indexing methods useless.

What helps improve performance is that fewer blocks of data must be accessed (only the ones containing the columns we are interested in).

Teradata handles row and column orientation convincingly, but it is the developer’s task to determine which design to use and when. There are many considerations during this decision process as each kind of storage (row or column-oriented) has advantages and disadvantages.

Perhaps the most outstanding disadvantage of the column storage implementation of Teradata is that joins become more complicated (columns from different column containers have to be brought back into rows).

Further, updates and deletes are done inefficiently: Deleted records are marked as deleted but not removed from the disk. In case of updates, the original record is marked as deleted, and the new record is inserted if you update each single table record, the table space usage doubles!

On the other hand, column containers can be compressed very efficiently (Teradata automatically compresses the containers if you don’t explicitly tell the system not to do this); Compression works best in the case of single-column containers.

While row-level compression is quite limited as data tuples most times have high cardinality, columns may only consist of a few values (imagine code columns like “male” and “female”), which means that the column values of all rows can be dictionary compressed into a few bits only (dictionary compression is implemented into Teradata since single value compression and MVC is available).

Still, the Teradata implementation of column storage is not top of the art: Teradata retrieves the data from the column containers, decompresses it, and reconstructs the rows. In a subsequent step, the row-based traditional database engine works but acts on the already decompressed data.

This is why it is still recommended to think about multi-value and block-level compression, even for tables stored in the column format.

While this approach is less IO intense than, for example, the one Oracle (Exadata) uses (keeping mini-column stores locally in the data blocks, which are only related to the same data block rows), there are better solutions available, which are not based on traditional row-based engines (SAP HANA, Vertica).

Please note that the above considerations are related to workload perfectly appropriate for column stores. It does not mean that Oracle’s implementation is always worse than the Teradata implementation or the other way around. It’s always a matter of workload characteristics.

The Teradata columnar feature is available starting with Version 14.00

Here is an example of how you can create a columnar table in Teradata:

CREATE TABLE the_table
(
PK INTEGER,
ATTRIB CHAR(10) ) NO PRIMARY INDEX PARTITION BY COLUMN;

A query like “SELECT ATTRIB FROM <TABLE>” would only require moving column ATTRIB into the AMP’s FSG memory.

The Teradata column storage is not fully integrated into the ETL chain. Fastload and Multiload into column store tables are not possible. The best practice is to create a NoPi table for loading purposes. In a subsequent step, you can to an INSERT…SELECT into the final columnar table.

Column storage and row-level partitioning can be combined in the same table. This is the syntax:

CREATE TABLE the_table
(PK INTEGER,MY_DATE DATE ) NO PRIMARY INDEXPARTITION BY COLUMN, RANGE_N (DATE BETWEEN '2014-01-01' AND '2014-12-31' EACH INTERVALL' 1' DAY));
  • Avatar
    MALIK HASSAN QAYYUM says:

    Deleted records are marked as deleted but not removed from the disk. What is the logic behind that?

  • Nice article.Clearly explained.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >