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.
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 bookRun 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.
| Queryband | SpoolUsage | TotalIOCount | AMPCPUTime |
| =S> Method=CSVLDFunc; | 110 559 232 | 332,00 | 1,89 |
| =S> Method=CSVLDFunc; | 110 559 232 | 332,00 | 1,89 |
| =S> Method=CSVLDFunc; | 110 559 232 | 332,00 | 1,90 |
| =S> Method=strtok_split_to_tab; | 245 719 040 | 608,00 | 4,71 |
| =S> Method=strtok_split_to_tab; | 245 719 040 | 608,00 | 4,72 |
| =S> Method=strtok_split_to_tab; | 245 719 040 | 608,00 | 4,98 |
| =S> Method=UnpackFunc; | 63 406 080 | 136,00 | 1,90 |
| =S> Method=UnpackFunc; | 63 406 080 | 136,00 | 1,79 |
| =S> Method=UnpackFunc; | 63 406 080 | 136,00 | 1,87 |
Discover this fascinating article on Stack Overflow.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
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