payload_column - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

[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 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

    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.