DWHPro Insights

Teradata strtok_split_to_table, CSVLD, Unpack: Column Splitting in Teradata

By Roland Wenzlofsky ·

Teradata strtok_split_to_table, CSVLD, Unpack: Column Splitting in Teradata

Splitting column content can be a challenging task. Teradata provides multiple methods to achieve this, each with unique pros and cons. In this article, we will examine these techniques in detail. To begin, we will generate a table containing sufficient random data to assess every option’s efficiency and resource utilization.

CREATE TABLE DWHPRO.Split
(
    PK INTEGER,
    ToBeSplit   VARCHAR(200)
) PRIMARY INDEX (PK);
INSERT INTO DWHPRO.Split
SELECT 
	ROW_NUMBER() OVER (ORDER  BY 1) AS PK,
	'Row' || TRIM(PK) || 'Col1,' || 'Row' || TRIM(PK) || 'Col2,' || 'Row' || TRIM(PK) || 'Col3'
FROM 
(
	SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
	UNION ALL SELECT * FROM SYS_CALENDAR.CALENDAR
) t01;

The Teradata “CSVLD” Function

This function requires all defined columns to be present. An error message will be shown if any columns cannot be created.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Run these examples in a dedicated session with no pre-existing query band. Clear the band afterward with NONE. Limit log analysis to the benchmark session and time window as well as the markers below; concurrent work by the same user must be excluded.


SET QUERY_BAND = 'Method=CSVLDFunc;' FOR SESSION;
SELECT * FROM TABLE (CSVLD(Split.ToBeSplit, ',', '')
RETURNS (col1 varchar(200), col2 varchar(200), col3 varchar(200))) as T1;

The Teradata “Unpack” Function

In contrast to the previously discussed function, the Unpack function also allows you to omit rows that cannot be split into all defined columns. The parameter “IgnoreInvalid” is used for this purpose:


SET QUERY_BAND = 'Method=UnpackFunc;' FOR SESSION;
SELECT 
	PK,
	CAST(Col1 AS VARCHAR(255)),
	CAST(Col2 AS VARCHAR(255)),
	CAST(Col3 AS VARCHAR(255))
FROM Unpack (
  ON Split
  USING
  TargetColumn ('ToBeSplit')
  OutputColumns ('Col1', 'Col2', 'Col3')
  OutputDataTypes ('varchar', 'varchar', 'varchar')
  Delimiter (',') 
  Regex ('(.*)')
  RegexSet (1)
  IgnoreInvalid ('true')
) AS t01 
;

The Teradata “strtok_split_to_table” Function

STRTOK_SPLIT_TO_TABLE emits one row per token, with a token number; it does not directly produce separate output columns.

SET QUERY_BAND = 'Method=strtok_split_to_tab;' FOR SESSION;
WITH T1 as
( 
 SELECT d.* FROM TABLE (strtok_split_to_table( Split.PK, Split.ToBeSplit,  ',')
RETURNS (PK integer, tokennum integer, Col varchar(100)) ) as d
)
SELECT T1.* FROM t1
;
SELECT QueryBand, SpoolUsage, TotalIOCount, AMPCPUTime
FROM DBC.DBQLogTbl
WHERE UserName = USER
  AND CAST(StartTime AS DATE) = CURRENT_DATE
  AND (QueryBand LIKE '%Method=CSVLDFunc;%'
    OR QueryBand LIKE '%Method=UnpackFunc;%'
    OR QueryBand LIKE '%Method=strtok_split_to_tab;%');
SET QUERY_BAND = NONE FOR SESSION;

Performance Considerations

The demonstrated CSVLD and Unpack calls produce column output, while STRTOK produces token rows. They are not interchangeable without matching treatment of empty tokens, quoted delimiters, NULL and invalid input. Those edge cases were not tested. Confirm function availability on the target installation.

The historical measurements below show about 43% less spool and 59% fewer I/Os for the displayed Unpack call, but the projections differ: Unpack includes PK and CSVLD does not. These figures are not a controlled or reproduced performance comparison. The audit verified A,B,C output only. Compare identical projections and semantics before choosing a function for performance.

QuerybandSpoolUsageTotalIOCountAMPCPUTime
=S> Method=CSVLDFunc;110 559 232332,001,89
=S> Method=CSVLDFunc;110 559 232332,001,89
=S> Method=CSVLDFunc;110 559 232332,001,90
=S> Method=strtok_split_to_tab;245 719 040608,004,71
=S> Method=strtok_split_to_tab;245 719 040608,004,72
=S> Method=strtok_split_to_tab;245 719 040608,004,98
=S> Method=UnpackFunc;63 406 080136,001,90
=S> Method=UnpackFunc;63 406 080136,001,79
=S> Method=UnpackFunc;63 406 080136,001,87

Discover this fascinating article on Stack Overflow.

https://stackoverflow.com/questions/68931466/teradata-splitting-field-into-several-rows-using-strtok-split-to-table-for-b

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam