Examples | CreateDATASET Function | Teradata Vantage - Example: Creating DATASET Data Type Instances - Analytics Database - Teradata Vantage

In this AVRO example, a DATASET data type instance is created where the schema and data are not included in the data payload.

CREATE TABLE nonStandardAVRO(id INTEGER, avroFile DATASET STORAGE FORMAT Avro);

/*Load Avro schema and data separately*/
avroSchemaAndData.txt
7B2274797065223A226172726179222C226974656D73223A5B7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A2253616C65222C2274797065223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A224974656D5F4944222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F4E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F436F6C6F72222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F5374796C65222C2274797065223A22737472696E67227D2C7B226E616D65223A225175616E746974795F507572636861736564222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F5072696365222C2274797065223A22646F75626C65227D2C7B226E616D65223A22546F74616C5F5072696365222C2274797065223A22646F75626C65227D5D7D7D5D7D2C7B2274797065223A227265636F7264222C226E616D65223A227265635F32222C226669656C6473223A5B7B226E616D65223A2253616C65222C2274797065223A7B2274797065223A227265636F7264222C226E616D65223A227265635F33222C226669656C6473223A5B7B226E616D65223A224974656D5F4944222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F4E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F436F6C6F72222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F5374796C65222C2274797065223A226E756C6C227D2C7B226E616D65223A225175616E746974795F507572636861736564222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F5072696365222C2274797065223A22646F75626C65227D2C7B226E616D65223A22546F74616C5F5072696365222C2274797065223A22646F75626C65227D5D7D7D5D7D2C7B2274797065223A227265636F7264222C226E616D65223A227265635F34222C226669656C6473223A5B7B226E616D65223A2253616C65222C2274797065223A7B2274797065223A227265636F7264222C226E616D65223A227265635F35222C226669656C6473223A5B7B226E616D65223A224974656D5F4944222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F4E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F436F6C6F72222C2274797065223A226E756C6C227D2C7B226E616D65223A224974656D5F5374796C65222C2274797065223A226E756C6C227D2C7B226E616D65223A225175616E746974795F507572636861736564222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F5072696365222C2274797065223A22646F75626C65227D2C7B226E616D65223A22546F74616C5F5072696365222C2274797065223A22646F75626C65227D5D7D7D5D7D5D7D| 06006E0E62696379636C650672656408626F79730200000000000059400000000000005940026E10746F7920626F61740870696E6B023333333333332E403333333333332E4004D20108736F617002AE47E17A14AEEF3FAE47E17A14AEEF3F00|1

.import vartext file avroSchemaAndData.txt
USING (c1 VARBYTE(10000), c2 VARBYTE(1000), c3 VARCHAR(10))
INSERT INTO nonStandardAVRO(:c3,CreateDATASET(:c1, :c2, Avro));

/*Retrieve the loaded data using the toJSON method*/
SELECT avroFile.toJSON() FROM nonStandardAVRO;

	avroFile
	----------
	> [
		{"rec_0" : {"Sale" : {
			"Item_ID" : 55,
			"Item_Name" : "bicycle",
			"Item_Color" : "red",
			"Item_Style" : "boys",
			"Quantity_Purchased" : 1,
			"Item_Price" : 100.00,
			"Total_Price" : 100.00
		}}},
		{"rec_2" : {"Sale" : {
			"Item_ID" : 55,
			"Item_Name" : "toy boat",
			"Item_Color" : "pink",
			"Item_Style" : null,
			"Quantity_Purchased" : 1,
			"Item_Price" : 15.10,
			"Total_Price" : 15.10
		}}},
		{"rec_4" : {"Sale" : {
			"Item_ID" : 105,
			"Item_Name" : "soap",
			"Item_Color" : null,
			"Item_Style" : null,
			"Quantity_Purchased" : 1,
			"Item_Price" : 0.99,
			"Total_Price" : 0.99
		}}}
	]

The following example is for CSV:

CREATE TABLE nonStandardCSV(id INTEGER, csvFile DATASET STORAGE FORMAT CSV);

/*Create a DATASET data type instance of CSV data with non-standard delimiters in the LATIN character set. */

INSERT INTO nonStandardCSV (1, CreateDATASET('{"field_delimiter":"&", "record_delimiter":"#"}', 'Item ID&Item Name&Item Color&Item Style&Quantity Purchased&Item Price&Total Price#55&bicycle&red&boys&1&100.00&100.00#88&toy boat&pink&&1&15.10&15.10#105&soap&&&1&0.99&0.99', CSV, LATIN));

/*Create a DATASET data type instance of CSV data with no header line in the LATIN character set. */ 

INSERT INTO nonStandardCSV (2, CreateDATASET(
'{"field_delimiter":"&", "record_delimiter":"#", "field_names":["Item ID", "Item Name", "Item Color", "Item Style", "Quantity Purchased", "Item Price", "Total Price"]}', '55&bicycle&red&boys&1&100.00&100.00#88&toy boat&pink&&1&15.10&15.10#105&soap&&&1&0.99&0.99', CSV, LATIN ));

/*Create a DATASET data type instance of CSV data with character set UNICODE. */ 

INSERT INTO nonStandardCSV (3, CreateDATASET('{"field_delimiter":"&", "record_delimiter":"#" }', 'Item ID&Item Name&Item Color&Item Style&Quantity Purchased&Item Price&Total Price#55&bicycle&red&boys&1&100.00&100.00#88&toy boat&pink&&1&15.10&15.10#105&soap&&&1&0.99&0.99', CSV, UNICODE));


/*Create a DATASET data type instance of CSV data where some fields are enclosed in double quotes in the UNICODE character set*/ 

INSERT INTO nonStandardCSV (4, CreateDATASET('{"field_delimiter":"&", "record_delimiter":"#"}', 'Item ID&Item Name&Item Color&Item Style&Quantity Purchased&Item Price&Total Price#55&bicycle&red&boys&1&100.00&100.00#88&toy boat&pink&&1&15.10&15.10#105&"soap&shampoo combo"&&&1&0.99&0.99', CSV, UNICODE));

 
/*Retrieve these rows of data that were inserted*/

SELECT * FROM nonStandardCSV ORDER BY id;