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