Avro Formatter UDX

You can invoke the Avro formatter as a UDX. To do so, define a function along the following lines:

CREATE OR REPLACE FUNCTION format_key(
  input cursor,
  schema_file_path varchar(256), result_col_name varchar(256)
   )
   returns table(
       input.*,
       KAFKA_KEY VARBINARY(128)
   )
   language java
   parameter style system defined java
   no sql
   --name of Java class
   external name 'sys_boot.sys_boot.avroformatter:com.sqlstream.aspen.namespace.common.AvroFormatterUdx.toAvro';

You can then invoke the function using the following code.

This function will read the schema file supplied as a parameter, look for columns with names that appear as fields in the schema file and serialize the values of those columns into AVRO format and return that as a VARBINARY column with name KAFKA_KEY.

CREATE OR REPLACE VIEW format_key_view AS
SELECT STREAM
  KAFKA_KEY, CAST(NULL AS INTEGER) AS KAFKA_PARTITION,
  "boolean_value", "char_value",
  "date_value", "decimal_value", "double_value", "real_value", "time_value",
  "timestamp_value", "tinyint_value", "varchar_value"
   FROM STREAM(format_key(
                   CURSOR(SELECT STREAM * FROM test_stream),
                   '/home/my-location/schema_key_types.avsc',
                   'KAFKA_KEY'
               ));