Formatting Data as CSV
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.
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"
SERVER "FileWriterServer" OPTIONS
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