Column-Level Schemas - Teradata Vantage

There are storage and performance advantages when you use a column-level schema:

• The schema is stored once within the data dictionary, not stored with each DATASET value in the row. This can cause significant storage space savings and minor performance savings due to less I/O.
• Often, the schema must be parsed only once for the DATASET column during a query run. This can cause significant performance gains because schema parsing can be costly.

The following example shows a DATASET column in the Avro storage format with a column-level schema:

CREATE AVRO SCHEMA avro_schema_1 as '{"type":"record",
    "name":"rec_0","fields":[
        {"name":"ProductID","type":"int"},
        {"name":"Price","type":"int"}
]}';

CREATE TABLE mytable(c1 INTEGER, c2 DATASET STORAGE FORMAT AVRO WITH SCHEMA avro_schema_1);

The following example shows a DATASET column in the CSV storage format with a column-level schema:

CREATE CSV SCHEMA myCSVSchema AS
'{
"field_delimiter" : "\t",
"record_delimiter" : ";"
}';

CREATE TABLE myDatasetTable02
(
id INTEGER,
csvFile DATASET(100000) INLINE LENGTH 5000 STORAGE FORMAT CSV CHARACTER SET LATIN WITH SCHEMA myCSVSchema
);