Example: Specifying Schemas - Advanced SQL Engine - Teradata Database

DATASET Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
des1556232910526.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1198
lifecycle
previous
Product Category
Teradata Vantageā„¢

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