例: FNC_GetDatasetSchema - Advanced SQL Engine - Teradata Database

セットアップ例

この例では、次のテーブルとデータを参照します。

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));

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

以下はサンプル出力です。

SELECT from_bytes((GetSchema(avroFile,0) RETURNS BLOB),'ascii')
FROM datasetTable; 

> {"type":"record","name":"rec_0","fields":[{"name":"a","type":"int"}]}