Example: Specifying Schemas - Teradata Vantage - Analytics Database

In this example, specify schemas for the output format AVRO:

SELECT data.ToJson() FROM DATASET_PUBLISH
(
	ON (SELECT * FROM employeeTable) 
	RETURNS (data DATASET STORAGE FORMAT AVRO)
		USING SCHEMA 
		('
{
	"type": "array",
	"items": {
		"type": "record",
		"name": "employeeRecord",
		"fields": [
        {
            "name": "empID",
            "type": ["null","int"]
        },
        {
			"name": "empName",
			"type": ["null","string"]
		},
		{
			"name": "empDept",
			"type": ["null","string"]
		}]
	}
}
	 ')
) AS avroFiles;

Result:

data.ToJson()
[{"empID":{"int":3},"empName":{"string":"Steven Mazzo"},"empDept":{"string":"Engineering"}},{"empID":{"int":1},"empName":{"string":"George Smith"},"empDept":{"string":"Accounting"}},{"empID":{"int":2},"empName":{"string":"Pauline Kramer"},"empDept":{"string":"HR"}}]

Or specify the schema for the output format CSV:

SELECT * FROM DATASET_PUBLISH
(
	ON (SELECT * FROM employeeTable) 
	RETURNS (data DATASET STORAGE FORMAT CSV)
		USING SCHEMA 
		('{"field_delimiter":"*" , "record_delimiter" : "/",
		"field_names" : ["empIdentifier","empFullName",
						"department"] }')

) AS csvFiles;

The following are the results for the table: