Example: Using Column Based Schema that Loads Several Rows of Data - 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™
CREATE AVRO SCHEMA avroSaleSchema AS
'{
	"type":"record",
	"name":"rec_0",
	"fields":[
	{
		"name":"Sale",
		"type":
		{
			"type":"record",
			"name":"rec_1",
			"fields":[
				{"name":"Item_ID","type":"int"},
				{"name":"Item_Name","type":"string"},
				{"name":"Item_Color","type":"string"},
				{"name":"Item_Style","type":"string"},
				{"name":"Quantity_Purchased","type":"int"},
				{"name":"Item_Price","type":"double"},
				{"name":"Total_Price","type":"double"}
			]
		}
	}]
}';

CREATE TABLE avroSaleTable (
	id INTEGER,
	saleInfo DATASET STORAGE FORMAT AVRO WITH SCHEMA avroSaleSchema);

avrosaledata.data
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|1
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|2
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|3
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|4
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|5
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|6
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|7
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|8
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|9
6E0E62696379636C650672656408626F79730200000000000059400000000000005940|10

.import vartext file avrosaledata.txt
USING (c1 VARBYTE(1000), c2 INTEGER)
INSERT INTO avroSaleTable(cast:id AS INTEGER),CreateDATASET(null, TO_BYTES(:AvroData, :encoding), Avro));

/*retrieve the loaded data using the toJSON method*/
SELECT id, saleInfo.toJSON() FROM avroSaleTable WHERE id = 1;
id
avroCol.AvroProject(…)
1
{
  "Sale" : {
	  	"Item_ID" : 55,
			"Item_Name" : "bicycle",
			"Item_Color" : "red",
			"Item_Style" : "boys",
			"Quantity_Purchased" : 1,
			"Item_Price" : 100.00,
			"Total_Price" : 100.00
	}
}