17.05 - payload_column - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

You can optionally specify a payload column for foreign tables that have JSON and CSV data. If you explicitly specify a payload column in your foreign table definition you must also specify a location column.

You cannot specify a payload column for foreign tables that have Parquet data.

If you define a foreign table but do not explicitly specify a payload column, the automatically generated payload column data type depends on the extension of the files in the location path.

File Extension Payload Column Type
.json or .json.gz JSON
.csv or .csv.gz DATASET STORAGE FORMAT CSV
No file extension or unrecognized file extension JSON, with unsupported file type warning
mixed .json and .csv files at the location path Based on the type of the first valid file Vantage encounters. Subsequent files from the location that have different extensions are ignored.

For this reason, it is best to specify a payload column for a location that has mixed format data, if you cannot arrange to have only one data format at the specified LOCATION.

The payload column specification can take one of the following forms, where the data type must match the type of data at the USING...LOCATION path:
  • For JSON data:
    • Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE
    • Payload JSON(16776192) INLINE LENGTH 64000 CHARACTER SET LATIN
  • For CSV data:
    • Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET UNICODE
    • Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET LATIN

    If the external CSV data files do not include header records specifying column names, you must define a schema object that specifies column names for the CSV field data. (If the CSV file has a header record, and you also associate it with a schema, the header is treated as a regular data row in the foreign table.) For more information about schema objects, see CSV File Headers and Schemas.

    In these cases, use a payload column specification that indicates the schema to use for the CSV data:
    • Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET UNICODE WITH SCHEMA schema_name
    • Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET LATIN WITH SCHEMA schema_name
If you define the payload column as a DATASET … STORAGE FORMAT CSV without a schema, and you do not specify the USING...ROWFORMAT parameter, the data is assumed to be in the default CSV file format:
  • The first record is read as the header.
  • Fields are delimited by commas.
  • Records are delimited by line feeds (LFs), "\n" or carriage return line feeds (CRLFs), "\r\n".
    You can only specify line feeds (LFs), "\n", in the schema or the ROWFORMAT clause, not carriage return line feeds (CRLFs), "\r\n".