teradata columnar

 

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

In order to be able to have a faster access to the rows, various types of indexes have been introduced. 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 in a different way).

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

In the times of of Big Data, an ever increasing number of rows has to be stored. At the same time, the number of columns per row is growing rapidly.

This leads to the situation, that queries, probably only in need of 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, the complete row has to be moved from the disk to the AMP’s FSG cache.

Therefore, the 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 means further, the usual restrictions for NOPI tables: Only MULTISET tables are allowed, no identity column can be used, no hash indexes can be added, etc.

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

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 way of 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 amount of rows in the exact order (the relative row number). A query selecting only “Column A” will only require to move 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 above example) which looks like this:

SELECT COLUMN_A FROM THE_TABLE WHERE COLUMN_B = ‘C’;

In order to lookup the related value for COLUMN_A, both column stores (COLUMN_A and COLUMN_B) have to be moved to the FSG cache. The exact order of the data across containers allows to pick up the value of COLUMN_A via the relative row number (which is 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. In order to create a multi-column 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, which are 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 multi-column containers. Furthermore, Teradata allows a kind of mixed storing of above formats.

Sets of columns can be stored like traditional rows, while the rest of the columns is 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 together 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 huge amount of data is accessed, but only a few columns are relevant for the result set (“sum of 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.

The reason is, as we anyway want to see information from “all customers” in OLAP, indexing the data (which the Teradata hashing algorithm basically does) is not improving performance. Such all-row retrievals render direct access indexing methods useless.

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

Teradata handles row orientation and column orientation in a convincing way, but it is the developers task to carve out which design to use when. There are many considerations to be done during this decision process as each kind of storage (row or column oriented) has its own advantages and disadvantages.

Maybe 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 together into rows).

Further, updates and deletes are done in a inefficient way: 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. This means, in case you update each single record of a table, 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 case of single column containers.

While row level compression is quite limited as data tuples most times have a 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 does it’s work but acts on the already decompressed data.

This is the reason why it is still recommended to think about multi value compression 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 be aware, that 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, 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 to move 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 INDEX

PARTITION BY COLUMN, RANGE_N (DATE BETWEEN ‘2014-01-01’ AND ‘2014-12-31’ EACH INTERVALL ‘1’ DAY));

 

Our Reader Score
[Total: 9    Average: 4.2/5]
The Teradata Columnar Database Feature written by Roland Wenzlofsky on April 17, 2014 average rating 4.2/5 - 9 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here