Example: NOS and Nondefault Field Delimiter, Header - Advanced SQL Engine - Teradata Database

This example shows how to create a foreign table with a nondefault field delimiter with NOS on (the default) and off. The CSV file has a header and its field delimiter is tab (\t).

rivers.tsv

09379180 LAGUNA CREEK AT DENNEHOTSO, AZ
09379910 COLORADO RIVER BELOW GLEN CANYON DAM, AZ
09380000 COLORADO RIVER AT LEES FERRY, AZ
09382000 PARIA RIVER AT LEES FERRY, AZ
09383300 FILLER DITCH AT GREER, AZ
09383400 LITTLE COLORADO RIVER AT GREER, AZ
09383100 COLORADO R ABV LITTLE COLORADO R NR DESERT VIEW
09379200 CHINLE CREEK NEAR MEXICAN WATER, AZ
09379050 LUKACHUKAI CREEK NEAR LUKACHUKAI, AZ
09379025 CHINLE CREEK AT CHINLE, AZ

Using NOS (Default)

NOS detects the field delimiter. You do not have to specify it.

CREATE FOREIGN TABLE rivers_t,
  EXTERNAL SECURITY MyAuthObj
  USING (
    LOCATION ('/s3/YOUR-BUCKET.s3.amazonaws.com/RIVERS/rivers.tsv')
  );

Not Using NOS

You must specify the field delimiter with ROWFORMAT.

CREATE FOREIGN TABLE rivers_t1,
EXTERNAL SECURITY MyAuthObj 
USING
(
   LOCATION ('/s3/YOUR-BUCKET.s3.amazonaws.com/RIVERS/rivers.tsv')
   ROWFORMAT ('{"field_delimiter":"\t"}')
);