Writing to the File System

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Writing Data Out of s-Server > Writing to Other Destinations >

Writing to the File System

Previous pageReturn to chapter overviewNext page

To write to files over the file system, you use the Extensible Common Data Adapter (ECDA) or ECD Agent.

To write data, you first define a server object with connection information, including the directory and information on file rotation. Once you define this server object, you can write to the file system by referencing it. See the topic CREATE SERVER in the s-Server Streaming SQL Reference Guide for more details.

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.

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.

Note on Writing Output Files

If ORIGINAL_FILENAME is set then, then this is the name of the file being actively written to. When this file is rotated out (based on either MAX_BYTES_PER_FILE or MAX_TIME_DELTA_PER_FILE), it is given a filename of the form <prefix><date><suffix> using the last rowtime in the file.

If ORIGINAL_FILENAME isn't set, then the the file being actively written to is given the form <prefix><date><suffix> but using the first rowtime in the file set.

For example, the following options:

filename_prefix 'test-',

filename_date_format 'yyyy-MM-dd_HH:mm:ss',

filename_suffix '.csv',

 

produce rotating file names like the following:

test-2020-05-23_19:44:00.csv

 

Writing Over the File System

To write over the file system, you need to create a server object which references the data wrapper with type 'FILE'.

CREATE OR REPLACE SERVER "FileReaderServer" TYPE 'FILE'

FOREIGN DATA WRAPPER ECDA;

 

Note: ECD Adapter server definitions need to reference the ECD foreign data wrapper. You do so with the syntax FOREIGN DATA WRAPPER ECDA.

You then just need to designate a directory for the file in stream options:

CREATE OR REPLACE SCHEMA "FileWriterSchema"

SET SCHEMA 'FileWriterSchema';

 

CREATE OR REPLACE FOREIGN STREAM "FileWriterStream"

("recNo" INTEGER,

"ts" TIMESTAMP,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER)

--Columns for the new stream

SERVER "FileWriterServer"

OPTIONS

(directory 'myDirectory',

--directory for the file

formatter 'CSV',

filename_pattern 'myRecord.csv',

--regex for filename pattern to look for

character_encoding 'UTF-8',

skip_header 'true');

 

CREATE OR REPLACE SCHEMA "Pumps";

SET SCHEMA '"Pumps"';

 

CREATE OR REPLACE PUMP "writerPump" STOPPED AS

--We recommend creating pumps as stopped

--then using ALTER PUMP "Pumps"."writerPump" START to start it

INSERT INTO "FileWriterSchema"."FileWriterStream"

SELECT STREAM * FROM "MyStream";

--where "MyStream" is a currently existing stream

 

To start writing data, use the following code:

ALTER PUMP "Pumps"."writerPump" START;

 

Using the ECD Agent to Write Over the File System

The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.

# Location, date format, prefix, suffix

DIRECTORY=/var/tmp

FORMATTER=CSV

FILENAME_PATTERN=myRecord.csv

CHARACTER_ENCODING=UTF-8

FORMATTER_INCLUDE_ROWTIME=FALSE

# Schema, name, and parameter signature of origin stream

SCHEMA_NAME=MY_SCHEMA

TABLE_NAME=FILEREADER_STREAM

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

 

A properties file configured to write files might look like the following:

# Location, date format, prefix, suffix

DIRECTORY=/var/tmp

FORMATTER=CSV

FILENAME_DATE_FORMAT=YYYY_MM_DD_HH_MM_SS

FILENAME_PREFIX=MYPREFIX-

FILENAME_SUFFIX=.TXT

CHARACTER_ENCODING=UTF-8

FORMATTER_INCLUDE_ROWTIME=FALSE

  # Rotation settings = 1 hour

MAX_TIME_DELTA_PER_FILE=3600000

# Schema, name, and parameter signature of origin stream

SCHEMA_NAME=SALES

TABLE_NAME=BIDS

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

 

Format Type Options

Other options are specific to format type.