FIleWriter Agent

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Appendix A: Legacy Adapters and Agents > Legacy Agents >

FIleWriter Agent

Previous pageReturn to chapter overviewNext page

The FileWriter Agent is supplied either as part of the distributed SQLstream product or as part of the ClientTools download from the SQLstream website (via SQLstream-5.1.0-clienttools-linux.run or SQLstream-client-tools-5.1.0.-windows.exe).

The Agent installs as a script, either filewriteragent.sh (Linux) or filewriteragent.cmd (Windows). Configuration works the same way on both platforms.

The FileWriter Agent provides most of the same features available in SQLstream's FileWriter Adapter. The differences are covered in the following table.

Agent  or  Adapter

Handles XML

Options in Parameter FIle

Runs on
SQLstream s-Server

Can Run on other Servers

ROWTIME_COLUMN
option

Agent

No

Options must be in parameter file

Yes

Yes

Not valid/usable
& not needed: see
FileWriter Agent Usage

Adapter

Yes

Options can be in a parameter file
OR

the OPTIONS clause of the CREATE OR REPLACE SERVER command

Yes

No

Valid/usable

 

Options can be specified in the OPTIONS clause of the CREATE OR REPLACE SERVER command for each distinct foreign server using its own set of options different from other servers in use.

Like the FileWriter Adapter, the FileWriter Agent converts input rows into character-separated output based on columnNames. It operates as a "sink" adapter converting streaming tuples into a character-separated file. Among its useful applications are the following:

Persistence of streaming results to a comma-delimited (CSV), pipe-delimited, or tab-delimited file.

NOTE: The FileWriter Agent does not create XML files.

Data integration of streaming data with scripted applications (like PHP, Ruby) that are good at file handling but less good at interacting with SQLstream's JDBC driver.

FileWriter Agent Features

The FileWriter Agent can do the following:

Rotate output files based on configurable options (rotation mode, filename pattern, etc.).
Create output formats with fields delimited by commas, pipes, or tabs.
Gracefully handle error conditions, such as read-only volume, permissions errors, or volume out of space, with trace log entries.

FileWriter Agent Controls and Parameters

To get the output you want, you specify the controls and parameters (as listed below) that FileWriter Agent is to use.

The order of the columns in the RowType of incoming tuples establishes the order and names of the tags used in the output. If the query you specify on the command line uses ROWTIME, then the first column output will be ROWTIME. (For FileWriter Agent, the existence of ROWTIME in the query creates the same effect as when the FileWriter Adapter finds its ROWTIME_COLUMN option set to true.)

The following list describes the FileWriter Agent controls and parameters:

The path and filename of the output character-delimited (commas, pipes, or tabs) file.The filename supports a base name, e.g., "output", and a pattern that includes the ROWTIME, e.g., "output_[ROWTIME]" for log rotation.
SimpleDateFormat strings to use in converting ROWTIME to text for filenames or for tuple values.
Rotation modes, such as
oFile-size-based, e.g., rotate whenever current file more than 200K in size,
oSystem-time-based, e.g., rotate every hour or day, or
oROWTIME-based, e.g., rotate whenever the ROWTIME changes
Output type and data mode: Character-delimited, with output type as Character-delimited. Data mode is any one of "csv", "comma", "tab", or "pipe".

Directory Structure

After installation, the FileWriter Agent directory contains a jar, a script, and a properties file:

FileWriterAgent/
olfilewriteragent.sh (Linux) OR lfilewriteragent.cmd (Windows)
ofwaparams.sh (Linux) OR fwaparams.cmd (Windows)
/lib/:
oFileWriterAgent.jar
oFileWriterAgentPath.jar
/trace/:
otrace.properties

Unzipping the file produces the directory tree expected by filewriter.sh or filewriter.cmd. These scripts are configured the same on Linux or Windows.

Filewriter Agent Options

The table below, Options Definable for Filewriter Agent, shows in alphabetical order the options that can be specified on the command line that invokes the Filewriter Agent. The following command shows a Linux example:

filewriteragent.sh   -v -sn <user>  -sp <password>   -ap <PropertiesFilePathName>  '<query>']

 

The following command shows a Windows example:

filewriteragent.cmd   -v -sn <user>  -sp <password>   -ap <PropertiesFilePathName>  '<query>']

 

Note that options must appear before the stream-name. They can be specified preceded by either a single dash or a double dash.

Options Definable for Filewriter Agent

Option (case insensitive)

Description

Possible values
default in bold

FILENAME_ DATEFORMAT

Java time format string for date conversion used during file rotation.

yyyy-MM-dd_HH:mm:ss
Uses java SimpleDateFormat

FILENAME_BASE

Target name for output and basename for file rotation;
Filewriter appends an underscore to the specified name.

No default name

FILENAME_ROTATED

Pattern specifying how to rename file at rotation time.
[FNB] = filename_base,
[RT] = filename_dateformat

[FNB]_[RT]
Example:
my-[FNB]_rotated-at_[RT]

FILEPATH

Directory where resultant files are located

No default

HEADER_ROW

Non-XML files only: Include a header line in a character-separated value file.

true
 
false

MAX_IDLE_TIME

Optional timeout in milliseconds to force file rotation:
 
If no data has been written during the specified time, then a flush to the existing or current output file is forced and a new file begins, using the specifications provided in the filename options.

[Integer value]

 

(If no time unit is specified, milliseconds is used.)

MAX_TIME_BETWEEN_FLUSH

Specifies number of seconds after which a flush is done (formerly after every row)

Default:  1-second

(If no time unit is specified, milliseconds is used.)

OUTPUT_ENCODING

Character set to use for file output, uses Canonical Name for java.nio API

UTF-8
 
See Oracle's encoding documentation.

OUTPUT_TYPE

Type of file to generate

csv
tab
pipe

<query>

The SQL statement generating data

One example would be  'select stream ROWTIME, * from "Test-FileWriter"."dataStream"'.

ROTATION_FILE_ROWS

Specifies maximum number of rows per file, after which a new rotation file is created

No default; any value can be used.

ROTATION_FILE_SIZE

Approximate file size to trigger ROTATION  for FILE_SIZE rotation option (above);

 

Actual file sizes may be larger by the amount that can be written in the time specified by MAX_TIME_BETWEEN_FLUSH.

 

Default: 10m
 
k – kilobyte
m – megabyte
g – gigbyte
t – terabyte
no unit indicates bytes
Case insensitive

ROTATION_ROW_TIME

 

Either  ROTATION_SYS_TIME or ROTATION_ROW_TIME is required.  You cannot use both.

Time unit to trigger file rotation for rowtime rotation option, using  ROWTIME changes, not wall-clock/system time.

Also, if a rowtime bound is received that indicates that any next row would cause rotation to happen, then rotation is performed without actually waiting to receive such a row.

Time units can be as shown in either Note A or B.

 

See Note C for examples of usage.

 

 

ROTATION_SYS_TIME

Either  ROTATION_SYS_TIME or ROTATION_ROW_TIME is required.  You cannot use both.

ROTATION_SYS_TIME=NEVER is acceptable.

Time unit to trigger file rotation for system rotation (SYS_TIME) option, using wall-clock time, not ROWTIME.

Time units can be as shown in either Note A or B.

 

See Note C for examples of usage.

 

ROWTIME_FORMAT

Java date format string for ROWTIME element.

HH:mm:ss.SSS
(See Oracle's Simple Date Format description.)

 

Note A:

h - hours

s - seconds

m - minutes

h - hours

d - days

w - weeks

Specifying no time unit indicates seconds s.

Note B:

MINUTE

HOUR

DAY

WEEK

MONTH

 

Note C:  

(1)  When a rotation time t less than one day is specified, then every day at midnight, midnight + t, midnight + 2t, etc.,  there will be a rotation as long as at least one row was output during that period.

(2)  When the rotation time t specified is an integral multiple N of 1 day, rotation will always happen on midnight ever Nth day starting at Jan 1, 1900.
For example, Jan 1, 1900 happens to be a Monday - so if a rotation time of t=7d is specified, rotation will always happen Sunday/Monday, 12:00 in the evening/morning.

(3)  When the rotation time t specified is greater than 1d, but not an integral multiple of 1d, rotation will happen on regular intervals of size t starting at Jan 1,1900, with no adjustments for daylight savings etc.

Configuration and Usage

The filewriteragent.sh startup script enables you to configure the FileWriter Agent by using the parameters shown in the Filewriter Agent Options section below.  The directory where you run filewriteragent.sh needs to have a trace file; otherwise, the trace log won’t be created/collected.  You should run filewriteragent.sh or filewriteragent.cmd as

Linux:

<YOUR INSTALLATIONDIRECTORY>/clienttools/FileWriterAgent/filewriteragent.sh

 

Windows:

<YOUR INSTALLATIONDIRECTORY>/clienttools/FileWriterAgent/filewriteragent.cmd

 

As shown in the examples below, you specify parameters for the location and name of the properties file that you intend to use, as well as the query generating the file you intend to create.

Examples:

A sample command on Linux would look like the following:

./filewriteragent.sh -v -sn sa -sp mumble -su jdbc:sqlstream:sdp://localhost -ap test.properties
'select stream ROWTIME, * from "Test-FileWriter"."dataStream"'

./filewriteragent.sh -v -sn sa -sp mumble -su jdbc:sqlstream:sdp://localhost -ap salesBids.properties
'select stream ROWTIME, * from SALES.BIDS'

 

where the options have the following meanings:

-sn specifies the username  ---  in the examples above, the user is "sa"
-sp specifies the password  ---  in the examples above, the password is "mumble"
-su specifies the host      ---  in the examples above, the host is "jdbc:sqlstream:sdp://localhost";
...... ...... ...... ....... the port can also be specified, as in jdbc:sqlstream:sdp://<host>:<port>
-ap specifies the properties file containing the filewriter options  ---  in the first example above, "test.properties"
and at the end, enclosed in single-quotes, the query generating the data populating the output file of the first example:
                          ---  'select stream ROWTIME, * from "Test-FileWriter"."dataStream"'

The test.properties file specifies the following options:

FILENAME_BASE=/tmp/logfile
ROTATION=FILE_SIZE
ROTATION_FILE_SIZE=1m
ROTATION_FILE_ROWS=10k
ROTATION_SYS_TIME=1h

MAX_TIME_BETWEEN_FLUSH=1000ms
MAX_IDLE_TIME=2s
#HEADER_ROW=true

 

The salesBids.properties file specifies the following options:

FILEPATH=/tmp
OUTPUT_TYPE=csf
OUTPUT_ENCODING=UTF-8
FILENAME_ROTATED=[fnb]_[rt].csv
FILENAME_DATEFORMAT=yyyyMMdd-HHmmss
ROTATION=FILE_SIZE
ROTATION_FILE_SIZE=1m
ROTATION_FILE_ROWS=50k

ROTATION_SYS_TIME=1h
MAX_TIME_BETWEEN_FLUSH=1000ms
MAX_IDLE_TIME=2s
HEADER_ROW=false
ROWTIME_FORMAT=yyyy-MM-dd HH:mm:ss
FILENAME_BASE=destination.csv
 

(Within a properties file, a line beginning with # indicates a comment line, as in #HEADER_ROW=true.

The previous section provides descriptions for all FileWriter Agent options.

Application Tracing

To facilitate debugging problems with application deployment the FileWriter Agent supports application tracing using the Java Logging package java.util.logging. FileWriter Agent creates log files in a sub-directory named "trace" in the FileWriter Agent directory tree created during installation. Logging configuration is located in the file trace/trace.properties. The properties file is similar in use and content to the trace.properties file used by the SQLstream s-Server.

For detailed information on what is provided at the various tracing levels:

See the topic Configuring System Parameters in the Administrator Guide.
See the topic Error Handling in the s-Server Streaming SQL Reference Guide

The name and location of the FileWriter Agent trace log are specified by the value set in java.util.logging.FileHandler.patter in the trace.properties file located at

 

The trace directory is located relative to the directory from which you are running the FileWriter Agent, and this directory must exist before you run the FileWriter Agent .

Tracing channels and logging levels

The FileWriter Agent supports four tracing channels:

For FileWriter Agent activity, com.sqlstream.tools.filewriteragent and com.sqlstream.plugin.filewriter are the main channels.
For FileWriter Agent activity, com.sqlstream.tools.filewriteragent is the main channel.

For the JDBC driver:

com.sqlstream.aspen.vjdbc.AspenDriver traces JDBC driver activity.

This channel can provide details on all communication activity between the Log File Adapter and the SQLstream s-Server. JDBC connection and handshaking, query delivery to the server and result delivery to the application are examples of information available from this tracing channel. For detailed information, see the topic Log File Agent in this guid.

com.sqlstream.aspen.sdp2.StreamingDataProtocol and com.sqlstream.aspen.sdp2.clients

These channels trace activity at the data transport level, providing details on low level TCP connection handling and on interactions with the SQLstream s-Server. For detailed information on what is provided at the various tracing levels:

See the topic Configuring System Parameters in the Administrator Guide.
See the topic Error Handling in the s-Server Streaming SQL Reference Guide

For the SQLstream s-Server, the correct SDP2 tracing level is called com.sqlstream.aspen.native.sdp

Logging (trace) levels

FileWriter Agent uses four of the possible logging levels.

INFO messages (the default installation level) give a brief indication of what the system is working on, from a high user-facing level.
They report context:  startup/shutdown operations, operational parameters being used by the application, and network connection tracking.
FINE Is used to report internal details for debugging. The general upper limit on FINE tracing is one message per data row, though FINE messages are often less frequent, and can indicate minor state transitions in some component
FINER is used to report more internal details for debugging.
FINEST is used to report even more internal details for debugging. *** CAUTION *** Using the FINEST level may generate a large amount of tracing information, since each line is echoed to the application trace file.

Each level from INFO to FINEST increases the frequency of trace messages roughly by a factor of 2–10 each step. Although tracing can lower system performance, usually only messages at level FINE or worse impact performance.

 

Sample trace.properties File

The following is a sample trace.properties file for the FileWriter Agent.

# FileWriterAgent tracing properties.
handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.level=FINEST
java.util.logging.FileHandler.formatter=com.sqlstream.aspen.util.IndentFormatter
java.util.logging.FileHandler.pattern=trace/FileWriterAgentTrace.log
java.util.logging.FileHandler.count=20
com.sqlstream.aspen.util.IndentFormatter.threads=true
com.sqlstream.aspen.util.IndentFormatter.timestamp=true
# Minimal JDBC driver tracing
com.sqlstream.aspen.jdbc.level=INFO
# suppress SDP client tracing - only warnings and severe failures
com.sqlstream.aspen.sdp.level=WARNING
# FileWriterAgent  tracing
com.sqlstream.tools.filewriteragent.level=INFO
com.sqlstream.plugin.filewriter.level=INFO

 

The first section at the top of the example describes to the Java Logging system what processing modules to use and where to direct the output. For more information see the documentation for java.util.logging.

The next three entries are the tracing control lines. They instruct the logging system to accept and process messages for the specified modules. Further they set the minimum thresholds for message acceptance. For list and description of the various logging levels see the documentation for java.util.logging.level.

For detailed information on what is provided at the various tracing levels:

See the topic Configuring System Parameters in the Administrator Guide.
See the topic Error Handling in the s-Server Streaming SQL Reference Guide