1. Concatenating VARCHAR Columns - Teradata Vantage

Apache Iceberg and Delta Lake Open Table Format on VantageCloud Lake Getting Started

Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
December 2024
ft:locale
en-US
ft:lastEdition
2025-01-03
dita:mapPath
bsr1702324250454.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
bsr1702324250454

Currently, the VARCHAR columns are assigned the maximum size allowed (32K UNICODE) while identifying the Variable Length string in Iceberg data. This can cause an issue while trying to CONCATENATE two VARCHAR strings as we have used the Maximum Size. Starting in version 20.00.19, the VARCHAR size is determined from the OTF metadata. If the VARCHAR size cannot be determined from the metadata, then 32000 is used by default.

  • A workaround is available to get the actual maximum length of the VARCHAR column and then cast it to the actual size and then concatenate it.

The example shown below:

SELECT (PICKUP_ZONE || ITINERARY) FROM ICEBERG_GLUE_1.glue_catalog.itinerary;
*** Failure 3798 A column or character expression is larger than the max size.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
# WORKAROUND 
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
select max(character_length (PICKUP_ZONE)), MAX(character_length (DROPOFF_ZONE)) from ICEBERG_GLUE_1.glue_catalog.itinerary;
 *** Query completed. One row found. 2 columns returned. 
 *** Total elapsed time was 2 seconds.
Maximum(Characters(pickup_zone))  Maximum(Characters(dropoff_zone))
--------------------------------  ---------------------------------
                              18                                 18
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
SELECT (cast (PICKUP_ZONE as char(18)) || cast(ITINERARY as char(18)) ) FROM  ICEBERG_GLUE_1.glue_catalog.itinerary;;                     
 *** Query completed. One row found. One column returned.                                                              
 *** Total elapsed time was 2 seconds.                                                                                
Minimum(avg_dropoff_latitude)                                                                                         
-----------------------------                              
        4.10000000000000E 001