Example: Validating a DATASET Type Instance (AVRO)
To create a table with a DATASET column and insert data with validation disabled, use the table 'myAVROTable06' and add one invalid row (the last 4 bytes of the Avro binary encoded value are missing):
avro08b.data
7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A2253616C65222C2274797065223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A224974656D5F4944222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F4E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F436F6C6F72222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F5374796C65222C2274797065223A22737472696E67227D2C7B226E616D65223A225175616E746974795F507572636861736564222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F5072696365222C2274797065223A22646F75626C65227D2C7B226E616D65223A22546F74616C5F5072696365222C2274797065223A22646F75626C65227D5D7D7D5D7D006E0E62696379636C650672656408626F7973020000000000005940000000000000
/*DDL included as a reference*/
/*CREATE TABLE myAVROTable06(
id INTEGER,
avroFile DATASET STORAGE FORMAT Avro);*/
.import vartext file avro08b.data
USING (avroData VARCHAR(10000), encoding VARCHAR(20))
INSERT INTO myAVROTable06(2, cast(TO_BYTES(:avroData,:encoding) AS DATASET STORAGE FORMAT AVRO));
SELECT id, avroFile.validate() FROM myAVROTable06 ORDER BY 1;
id |
avroFile.validate() |
1 |
1 |
2 |
0 |
Example: Validating a DATASET Type Instance (CSV)
SELECT id, csvFile.validate() FROM myCSVTable06 ORDER BY 1;
id |
csvFile.validate()
|
0 |
1 |