Reading from the File System

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Reading Data into s-Server > Reading from Other Sources  >

Reading from the File System

Previous pageReturn to chapter overviewNext page

You can read files from any accessible file system location using the Extensible Common Data Adapter (ECDA) or ECD Agent.

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.

To read from the file system, you need to create a server object which references the data wrapper ECDA and is of 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 can do so with the syntax FOREIGN DATA WRAPPER ECDA.

Unlike server objects, all foreign streams need to be created in a schema. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named "FileReaderStream."

CREATE OR REPLACE SCHEMA "FileSource";

SET SCHEMA '"FileSource"';

 

CREATE OR REPLACE FOREIGN STREAM "FileReaderStream"

("recNo" INTEGER,

"ts" TIMESTAMP,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER)

--Columns for the new stream

SERVER "FileReaderServer"

OPTIONS

(directory 'myDirectory',

--directory for the file

parser 'CSV',

filename_pattern 'myRecord\.csv',

--regex for filename pattern to look for

character_encoding 'UTF-8',

skip_header 'true');

 

Using the ECD Agent to Read from 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

PARSER=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

PARSER=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)