Drawing on data in s-Server, the Extensible Common Data file writer writes a variety of file types into designated locations.
To write to files with the Extensible Common Data Adapter, you set up an adapter instance through SQL. To write to files with the Extensible Common Data agent, you launch the agent from its directory at the command line, using the --output flag.
All adapter or agent implementations involve configuring options. For adapters, you configure and launch the adapter in SQL, using either server or foreign stream/table options. For agents, you configure such options using a properties file and launch the agent at the command line. Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the Streaming SQL Reference Guide has a complete list of options for the ECD adapter.
The following options are valid for all formats.
You can rotate output files either by specifying a maximum number of bytes or a maximum amount of time since the last file was written.
To write files with the Extensible Common Data Adapter, you need to take the following steps:
|1.||Designate a transport medium as a server option: file system, network socket, Kafka, AMQP, Websphere MQSeries, a mail server, or Hadoop.|
|2.||Designate foreign stream or table options, including:|
|a.||A location for the file.|
|b.||Options for how the file will be written, such as prefixes, suffixes, and format-specific options.|
|c.||Options for how the file will rotate.|
|3.||Run a pump containing an INSERT statement against the stream. (This is when the file actually writes.)|
Note: Because of the nature of streaming data, you will need to set up a pump in order to move rows continually from an s-Server stream to another stream, file, Kafka topic, RDBMS table or other location. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. A model for setting up a pump is provided below. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.
|1.||Identify a stream in s-Server from which the agent will draw data to write files.|
|2.||Navigate to the clienttools/EcdaAgent directory.|
Note: The location of this directory depends on where you installed the client tools suite. In a default s-Server installation, it is above the $SQLSTREAM_HOME directory, and will be something like /opt/sqlstream/5.0.XXXX/clienttools/EcdaAgent.
|3.||Create a properties file options for the agent, such as example.properties, and save it. This file needs to include information such as directory, filename pattern, filename date format, filename prefix and suffix, as well as options for the origin stream, such as schema_name, table_name, and rowtype. The code below represents a sample property file that writes to a CSV formatted file with a prefix "MYPREFIX-" in a directory called /var/tmp, and draws from stream called BIDS in the schema SALES on the default s-Server defined for this agent.|
# Location, date format, prefix, suffix
# Rotation settings = 1 hour
# Schema, name, and parameter signature of origin stream
ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)
|4.||Save the properties file in the /clienttools/EcdaAgent directory.|
|5.||Determine the output format you will use. The ECD agent defaults to using the file system, but you can also configure it to write to files over a network socket, an amqp feed, to and from a kafka server, to and from an mqseries server, or to and from a hadoop file system. A list of options for the --io property is available at the topic Extensible Common Data Agent Overview.|
|6.||Launch the agent with the --output property enabled and a --props property that references the properties file you just created:|
./commondataagent.sh --output --io file --props example.properties
If you have configured everything correctly, the agent should begin writing data to the file location, and rotating files according to the settings in MAX_BYTES_PER_FILE or MAX_FILENAME_DELTA.
When writing CSV files, the Extensible Common Data Adapter converts rows into character-separated output based on options supplied through the options sections of the CREATE FOREIGN STREAM statement. It converts streaming tuples into a character-separated file.
To write a CSV file, you need to give the Extensible Common Data Adapter the following information: