17.10 - 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.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

[Optional for JSON and CSV tables.] Specify one column with one of the following definitions. The data type in the definition must match the type of data at the specified LOCATION.

The location_column and the payload_column or data_column_definition fields are optional, because NOS generates them. For CSV, if NOS is cannot generate the columns, it generates the payload column of DATASET CSV instead.

  • 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
    You cannot define the following attributes on a Payload column:
    • NOT NULL
    • COMPRESS
    • CONSTRAINT
    • REFERENCES
    • CHECK

    For more information about schema objects, see CSV File Headers and Schemas.

    NOS automatically discovers the schema for you.

Default CSV file format has these characteristics:
  • The first record is the header.
  • The field delimiter is comma (",").
  • The record delimiter is line feed ("\n") or carriage return line feed ("\r\n").
    ROWFORMAT must specify that the record delimiter is line feed.
Table Type payload_column
JSON Optional. If omitted, NOS generates a payload column whose type depends on extension of files in location path (see following table).
CSV

Optional. If omitted, NOS discovers the schema automatically.

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.

For information about NOS, see Teradata Vantage™ - Native Object Store Getting Started Guide, B035-1214.