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;