The Parser UDX lets you call s-Server’s predefined parsers in a function. You can use this function to call any of the parsers described in the topic Input Formats for Reading. This parser takes as input a cursor with data to be parsed.
See the topic CREATE FUNCTION in the SQLstream Streaming SQL Reference Guide* for more details on functions.
The Parser UDX is useful for scenarios in which data cannot be fully parsed on input, such as a case where JSON data is embedded within CSV data, or when data arrives through a JDBC connection instead of the ECD adapter or agent.
To use the Parser UDX, you need to do the following:
The following example first creates and sets a schema for the function, sets a path for the function, creates a foreign stream to input data, creates a function using the Parser UDX, creates a view to input options for the parser, and then creates a view to select from the function. (The view makes it easier to select from this function in the future.)
CREATE OR REPLACE SCHEMA PARSERUDXTEST;
SET SCHEMA 'PARSERUDXTEST';
--Sets path so that this function can be called by
--function name when this path is set.
SET PATH 'PARSERUDXTEST';
--Server for foreign stream
CREATE OR REPLACE SERVER FileReaderServer type 'FILE'
FOREIGN DATA WRAPPER ECDA;
--Stream with raw data
CREATE OR REPLACE FOREIGN STREAM test_table (
MESSAGE VARCHAR(1024)
)
SERVER FileReaderServer
--options for data source
OPTIONS(
DIRECTORY '/tmp/',
FILENAME_PATTERN 'animals.csv',
CHARACTER_ENCODING 'ISO-8859-1',
PARSER 'CSV',
STATIC_FILES 'true',
SKIP_HEADER 'true',
SEPARATOR '@' --we ignore separators
--so that data is not parsed on input
);
CREATE OR REPLACE FUNCTION parseColumn(
input cursor,
columnName varchar(256),
parserType varchar(256),
options cursor
)
--columns need to be declared with types that match
--data to be parsed
RETURNS TABLE(
ROWTIME timestamp not null,
ANIMAL_ID VARCHAR(64),
 COMMON_NAME VARCHAR(64),
   GENUS VARCHAR(64),
   SPECIES VARCHAR(64),
   FAMILY VARCHAR(64),
   ORDO VARCHAR(64),
   CLASSIS VARCHAR(64)
)
 LANGUAGE JAVA
 PARAMETER STYLE SYSTEM DEFINED JAVA
 NO SQL
 EXTERNAL NAME 'class com.sqlstream.aspen.namespace.common.ParserUdx.parseColumn';
--These are options for the parser
CREATE VIEW CSV_OPTIONS(SEPARATOR) AS VALUES(',');
--This selects from the function.
CREATE OR REPLACE VIEW readParsed as
SELECT STREAM * FROM STREAM(parseColumn(
   CURSOR(SELECT stream * FROM test_table),
   'MESSAGE',
   'CSV',
   CURSOR(select * from CSV_OPTIONS)));
SELECT STREAM * from readParsed;