Example: Specifying Schemas

Teradata Vantageā„¢ DATASET Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1198-162K

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;

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:

fileSchema fileData

{

"field_delimiter":"*",

"record_delimiter":"/"

}

empIdentifier*empFullName*department/1*George Smith*Accounting/2*Pauline Kramer*HR/3*Steven Mazzo*Engineering