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
}
}
|