ROWFORMAT Option | CREATE FOREIGN TABLE | Teradata Vantage - ROWFORMAT - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Specifies the encoding format of the external row, for example:

ROWFORMAT('{"field_delimiter":",", "record_delimiter":"\n", "character_set":"LATIN"}')

The ROWFORMAT specification uses JSON formatting, even if the ROWFORMAT is specifying the format for other types of data. It can include only the three keys shown above, and the field_delimiter is only valid for CSV data. Key names and values are case-specific, except for the value for "character_set", which can use any combination of letter cases.

If the Payload column is defined to have a DATASET CSV … WITH SCHEMA data type, the schema information takes precedence over the ROWFORMAT specification.

The ROWFORMAT character set specification must be compatible with the Payload column character set.

Do not specify ROWFORMAT for Parquet format data.

For each JSON column character set definition, the default values are as follows:

Character Set Default Definition
UNICODE
ROWFORMAT('{"record_delimiter":"\n", "character_set":"UTF8"}')
LATIN
ROWFORMAT('{"record_delimiter":"\n", "character_set":"LATIN"}')

For each CSV column character set definition, the default values are as follows:

Character Set Default Definition
UNICODE
ROWFORMAT('{"character_set":"UTF8"}') 
LATIN
ROWFORMAT('{"character_set":"LATIN"}')
You can specify the following options, which are case sensitive:
field_delimiter
The default is "," (comma). You can also specify a custom field delimiter, such as tab "\t".
field_delimiter is valid only for CSV data. If the CSV data is associated with a CSV schema, and the schema also specifies a field delimiter character, the schema specification takes precedence over the ROWFORMAT specification. For more information about CSV schemas, see CSV File Headers and Schemas.
record_delimiter
Line feed character: "\n". The record delimiter must be a line feed character.
character_set
"UTF8" or "LATIN"