Extensible Common Data Adapter: Writing CSV files
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