Example: Reading Specified Portions of an Avro Instance

Teradata Vantageā„¢ DATASET Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1198-162K
CREATE TABLE avroTable(id INTEGER, avroCol DATASET STORAGE FORMAT AVRO);
/*insert some data composed of a record with five fields as mentioned above*/
/* 
{"id":1,"First":"Leo","Last":"Tolstoy","Phone":"(800)-123-4657","Age":187} 
*/
INSERT INTO avroTable(1, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226964222C2274797065223A22696E74227D2C7B226E616D65223A224669727374222C2274797065223A22737472696E67227D2C7B226E616D65223A224C617374222C2274797065223A22737472696E67227D2C7B226E616D65223A2250686F6E65222C2274797065223A22737472696E67227D2C7B226E616D65223A22416765222C2274797065223A22696E74227D5D7D0002064C656F0E546F6C73746F791C28383030292D3132332D34363537F602'xb); 
/* 
{"id":2,"First":"Mark","Last":"Twain","Phone":"(800)-123-4657","Age":180} 
*/
INSERT INTO avroTable(2, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226964222C2274797065223A22696E74227D2C7B226E616D65223A224669727374222C2274797065223A22737472696E67227D2C7B226E616D65223A224C617374222C2274797065223A22737472696E67227D2C7B226E616D65223A2250686F6E65222C2274797065223A22737472696E67227D2C7B226E616D65223A22416765222C2274797065223A22696E74227D5D7D0004084D61726B0A547761696E1C28383030292D3132332D34363537E802'xb);
/* 
{"id":3,"First":"William","Last":"Shakespeare","Phone":"(800)-123-4657","Age":451} 
*/
INSERT INTO avroTable(3, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226964222C2274797065223A22696E74227D2C7B226E616D65223A224669727374222C2274797065223A22737472696E67227D2C7B226E616D65223A224C617374222C2274797065223A22737472696E67227D2C7B226E616D65223A2250686F6E65222C2274797065223A22737472696E67227D2C7B226E616D65223A22416765222C2274797065223A22696E74227D5D7D00060E57696C6C69616D165368616B657370656172651C28383030292D3132332D343635378607'xb);
/* 
{"id":4,"First":"Charles","Last":"Dickens","Phone":"(800)-123-4657","Age":203} 
*/
INSERT INTO avroTable(4, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226964222C2274797065223A22696E74227D2C7B226E616D65223A224669727374222C2274797065223A22737472696E67227D2C7B226E616D65223A224C617374222C2274797065223A22737472696E67227D2C7B226E616D65223A2250686F6E65222C2274797065223A22737472696E67227D2C7B226E616D65223A22416765222C2274797065223A22696E74227D5D7D00080E436861726C65730E4469636B656E731C28383030292D3132332D343635379603'xb);

/*now perform the projection*/
SELECT id, 
avroCol.AvroProject('{"type":"record","name":"rec_0","fields":[{"name":"First","type":"string"},{"name":"Age","type":"int"}]}') 
FROM avroTable ORDER BY id;