Parsing Fixed Column Data

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Reading Data into s-Server > Reading from Other Sources  > Input Formats for Reading >

Parsing Fixed Column Data

Previous pageReturn to chapter overviewNext page

The Fixed Column option filters fixed-width fields and converts them to rows with columns of the declared types.

Starting position of column is 0. Column specifications for types DATE, TIME and TIMESTAMP support a format parameter allowing the user to specify exact time component layout. The parser uses the Java class java.lang.SimpleDateFormat to parse the strings for types DATE, TIME and TIMESTAMP. The Date and Time Patterns topic in the s-Server Streaming SQL Reference Guide gives a full description and examples of timestamp format strings. The following is an example of a column definition with a format string:

"name" TIMESTAMP 'dd/MMM/yyyy:HH:mm:ss'

 

You implement the Fixed Column parser of the Extensible Common Data Adapter by designating the option FCLP under stream options. Column names cannot be dynamically assigned with Fixed Column files. You need to declare these as part of a the foreign stream or table.

Note: You can also input data in larger chunks and parse it later using the Parser UDX. This UDX calls the parsers listed above in a function. For more information on using functions, see the topic Transforming Data in s-Server in this guide.

Sample Foreign Stream Implementing ECD Adapter to Parse FCLP Files

The following example will parse the first six columns from a file in /tmp with the filename_pattern transactions\.log and assign these to stream columns called called recNo, ts, accountNumber, sourceID, destIP, and customerID . To parse Fixed Column Data over other input/output systems, such as IBM MQ or AMQP, you would need to specify options for these formats. See Reading from Other Sources for more details.

Note: Information on file location, file name pattern and character encoding can also be set as server options.

CREATE OR REPLACE FOREIGN STREAM "Fixed_Column_Log_Parse"

("recNo" INTEGER,

"ts" TIMESTAMP NOT NULL,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER,)

SERVER "FileReaderServer"

OPTIONS

(directory '/tmp',

filename_pattern 'transactions\.log',

character_encoding 'UTF-8',

parser 'FCLP');

 

Sample Properties Implementing ECD Agent to Parse FCLP Files

To parse FCLP files with the ECD Agent, configure the options above using the ECD Agent property file with properties similar to the following. To parse Fixed Column Data over other input/output systems, such as IBM MQ or AMQP, you would need to specify options for these formats. See Reading from Other Sources for more details.

ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

FILENAME_PATTERN=TRANSACTIONS\.LOG

PARSER=FCLP

CHARACTER_ENCODING=UTF-8

SKIP_HEADER=TRUE