17.10 - 例: スキーマの指定 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - DATASETデータ型

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1198-171K-JPN
Language
日本語 (日本)

この例では、出力形式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"}}]

または、出力形式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;

テーブルの結果は次のとおりです。

fileSchema fileData

{

"field_delimiter":"*",

"record_delimiter":"/"

}

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