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 (http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html 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.
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.
The s-Server trace log includes information on readers’ and parsers’ progress. See Periodic Parser Statistics Logging in the Administering Guavus SQLstream guide.
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 Kafka, Kinesis, a network socket, a WebSocket, HTTP 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');
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 Kafka, Kinesis, a network socket, a WebSocket, HTTP 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