CSV Headers and Schemas | READ_NOS | Teradata Vantage - 17.05 - CSV File Schemas - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

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

CSV files usually include a header row as the first line of the file. The header row defines the names for the columns defined by the corresponding fields of data in each subsequent row of the file. By default, commas (,) separate each field in a record and line breaks (CRLF carriage return/line feeds) separate each record. For example:

First Name,Last Name,City
John,Smith,Los Angeles
Mary,Jones,New York

If the CSV files in the external data do not have header rows, you must create a schema object in the database. The schema object specifies column names that correspond to the fields in the CSV file. Vantage uses these column names when creating the foreign table for the CSV data. (Do not create schemas for CSV files that include a header row, or the headers will be the first row of the foreign table.)

To create a schema for CSV files, use the SQL statement CREATE storage_format SCHEMA, documented in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Schemas use a JSON style syntax for field and delimiter specifications, even if the schema is defined for CSV data.

If the CSV files do not have header rows, but you do not want to manually assign specific column names, such as when there are a very large number of columns, you can have Vantage assign column names automatically. Create a schema for the CSV files and include the "field_names":[] option in the schema specification, leaving the contents of the square brackets empty. Vantage automatically generates column names of the form csvfldn .

A schema can also indicate the field delimiter character used in the CSV file. Use the "field_delimiter" parameter in cases where the delimiter is not the standard comma character, such as for TSV files, which are tab-delimited. If a USING...ROWFORMAT option also specifies a field delimiter character, the schema specification takes precedence.

Use the READ_NOS USING...HEADER option to specify whether the CSV data has a header row or uses an associated schema.