Example Setup
This example references the following table and data.
CREATE TABLE datasetTable( id INTEGER, avroFile DATASET STORAGE FORMAT Avro);
avro01.data:
7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A2261222C2274797065223A22696E74227D5D7D0002 |1|base16
.import vartext file avro01.data USING (avroData VARCHAR(1000), id varchar(10), encoding VARCHAR(20)) INSERT INTO datasetTable (cast(:id AS INTEGER),cast(TO_BYTES(:avroData, :encoding) AS DATASET STORAGE FORMAT AVRO));
Example Using FNC_GetDatasetSchema
CREATE FUNCTION getSchema ( a1 TD_ANYTYPE, a2 integer) RETURNS TD_ANYTYPE NO SQL PARAMETER STYLE SQL DETERMINISTIC LANGUAGE C EXTERNAL NAME 'CS!getSchema!getSchema.c!F!getSchema';
getSchema.c:
#define SQL_TEXT Latin_Text #include <sqltypes_td.h> #include <string.h> #include <stdio.h> #define BUFFER_SIZE 10000 void getSchema(void *input, INTEGER *schemaEncoding, LOB_RESULT_LOCATOR *result, int *input1NullIndicator, int *input2NullIndicator, int *outputNullIndicator, char sqlstate[6], SQL_TEXT extname[129], SQL_TEXT specific_name[129], SQL_TEXT error_message[257]) { void* schemaBuf; int schemaBufLen = 0; int actualSchemaLength = 0; FNC_LobLength_t writeLen = 0; int maxLength, inlineLength, schemaLength, rawDataLength = 0; dataset_storage_et storageFormat = 0; boolean_t dataLob, schemaLob = 0; FNC_GetDatasetInfo(*((DATASET_HANDLE*)(input)), &maxLength, &inlineLength, &schemaLength, &rawDataLength, &storageFormat, &dataLob, &schemaLob); if (*schemaEncoding == datasetSchemaUTF8) { schemaBufLen = schemaLength; } else { schemaBufLen = (schemaLength*2); /*worst case is each UTF8 character is an ascii character, which will require 2 bytes in UTF16*/; } schemaBuf = FNC_malloc((size_t)schemaBufLen); FNC_GetDatasetSchema(*((DATASET_HANDLE*)(input)),(void*) schemaBuf,schemaBufLen,&actualSchemaLength,*schemaEncoding); FNC_LobAppend(*result,(BYTE*)schemaBuf,(FNC_LobLength_t)actualSchemaLength,&writeLen); FNC_free(schemaBuf); sprintf(sqlstate, "00000\0"); *outputNullIndicator = 0; }
The following is sample output:
SELECT from_bytes((GetSchema(avroFile,0) RETURNS BLOB),'ascii') FROM datasetTable;
Result:
> {"type":"record","name":"rec_0","fields":[{"name":"a","type":"int"}]}