Example: FNC_GetDatasetSchema - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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