Extensible Common Data Adapter: Writing CSV files

<< Click to Display Table of Contents >>

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

Extensible Common Data Adapter: Writing CSV files

Previous pageReturn to chapter overviewNext page

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.

Note: For performance reasons, most formatting should be done in native SQL and passed into ECDA, not pushed into ECDA formatters.

To write a CSV file, you need to give the Extensible Common Data Adapter the following information:

A destination directory for the file.
A formatter of CSV
A character encoding type for the file.
How often the file will rotate in terms of bytes or time.

Example Code

To write to CSV files, you need to set up a server object which references one of the I/O systems.

CREATE OR REPLACE SERVER "FileWriterServer" TYPE 'FILE'

FOREIGN DATA WRAPPER ECDA;

 

Finally, you create a foreign stream which references the server object. Like all streams, foreign streams must be created within a schema. The example below creates and sets a schema called "WebData," and creates a foreign stream called "FileWriterStream." To write data over other input/output systems, such as IBM MQ or AMQP, you would need to specify options for these formats. See Writing to Other Destinations for more details.

CREATE OR REPLACE SCHEMA "WebData";

SET SCHEMA '"WebData"';

 

CREATE OR REPLACE FOREIGN STREAM "FileWriterStream"

("STR1" VARCHAR(32))

SERVER "FileWriterServer" OPTIONS

(directory 'path/to/myfile',

formatter 'CSV',

filename_date_format 'yyyy-MM-dd-HH:mm:ss',

filename_prefix 'test-',

filename_suffix '.csv',

character_encoding 'US-ASCII',

formatter_include_rowtime 'false',

max_bytes_per_file '1073741824')

 

To actually write to a file in path/to/myfile, you need to write a pump containing an INSERT statement along the following lines:

CREATE OR REPLACE PUMP "writerPump" STARTED AS

INSERT INTO "FileWriterStream"

SELECT STREAM "MyStream";

--where "MyStream" is a currently existing stream