Code Structure for Implementing the Extensible Common Data Adapter

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Code Structure for Implementing the Extensible Common Data Adapter

Return to chapter overview

The Extensible Common Data Adapter (ECDA) is implemented like any other adapter. In deploying the Extensible Common Data Adapter, you identify two main differentiators:

1. A class name for a server object, which tells you what kind of  I/O system you are talking to. Currently, these are fileset.columnset, socket.columnset, mail.columnset, and amqp.columnset

2. A format type, which tells you what kind of file you are reading (SELECT) and/or writing to (INSERT). Currently, these are CSV, XML, FCLP, VCLP, W3C, FastRegex, MultiRow, and Custom. Custom lets you specify your own parser or formatter.

As with other adapters, implementing the Extensible Common Data Adapter has three steps:

An "installing" section. In the installing section, you create a foreign data wrapper which references the Extensible Common Data Adapter JAR. This makes the Extensible Common Data Adapter available to s-Server.
A "defining" section. In the defining section, you specify an I/O format for the data to be transmitted, by specifying one of four JARS.
A "calling" section. In this section, you initiate the data transfer using a CREATE FOREIGN STREAM or CREATE FOREIGN TABLE command. Once you create this stream, you can SELECT from it or INSERT into it. When you select, the Extensible Common Data Adapter grabs data from a specified location, such as a file or a socket,  For CSV, XML, and JSON, you can both select and insert. For the other formats, you can only select.

The Extensible Common Data Adapter reads or writes data once you run a SELECT or INSERT statement against the foreign stream or table defined in the calling section.

"Installing" section

To install the adapter, you create a foreign data wrapper along the following lines:

CREATE OR REPLACE FOREIGN DATA WRAPPER "FileWriterWrapper" LIBRARY 'class com.SQLstream.plugin.common.CommonDataWrapper' LANGUAGE JAVA

 

For more detail, see the topic CREATE FOREIGN DATA WRAPPER in the Streaming SQL Reference Guide.

Note: If you try to replace a JAR that is currently loading, you will receive a NullPointerException error. This is a limitation of the Sun Java Runtime Environment. More information about this limitation is available at http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6832972.

Defining Section

In the defining server section, you specify a native class depending on the I/O type for the adapter. The code below, for example, specifies an I/O type of local computer or network.

CREATE OR REPLACE SERVER "FileReaderServer"

FOREIGN DATA WRAPPER "FileReaderWrapper"

OPTIONS (classname 'com.SQLstream.plugin.common.FileSetColumnSet')

 

For more detail, see the topic CREATE SERVER in the Streaming SQL Reference Guide.

Type

Purpose

FileSetColumnSet

Reads to/ writes from file system or local network.

SocketColumnSet

Reads to/ writes from network socket connections.

MailColumnSet

Reads to/ writes from a mail server.

AmqpColumnSet

Reads to/ writes from AMQP message bus

Calling Section

In order to access data from an external source using the Extensible Common Data Adapter, you need to create a special kind of stream called a foreign stream. Options in the stream specify the file format, character separators, whether or not a header should be written, and so on, as well as any options specific to the format type. For example, XML options, which are described in the topic Extensible Common Data Adapter: Writing and Reading XML, include information on how to map column names to XML elements.

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 "FileReaderStream." It sets four options:

DIRECTORY, which is a path to the file to be read.
ENCODING, which determines character encoding for the file.
FORMAT_TYPE, which determines the kind of file to be read.
FILENAME_PATTERN, which determines the pattern of the file to read.

CREATE OR REPLACE SCHEMA "WebData";

SET SCHEMA '"WebData"';

 

CREATE OR REPLACE FOREIGN STREAM "FileReaderStream"

('"recNo" INTEGER,

"ts" TIMESTAMP NOT NULL,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER',)

SERVER "FileWriterServer"

OPTIONS

(DIRECTORY '/path/to/logfile',

FILENAME_PATTERN 'web*.log',

ENCODING 'UTF-8',

format_type 'VCLP');

 

To actually read from a file in /path/to/logfile, you need to write a SELECT statement along the following lines:

SELECT * FROM "FileReaderStream";

 

If you attempted to INSERT into this stream, the DDL will fail, because the stream is not configured for writing.

Options for All Formats

All of the options below are set as options in a foreign stream or foreign table.

Name

Description

Possible values

FORMAT_TYPE

CSV, XML, FCLP, VCLP, W3C, FastRegex, MultiRow, Custom


WRITE_HEADER

True or False


CHARACTER_ENCODING

UTF-8

Any Java Supported Encoding. Use encoding implemented by java.nio, see:

http://download.oracle.com/javase/6/docs/technotes/guides/intl/encoding.doc.html?toc=0

UTF-8

Any Java Supported Encoding. Use encoding implemented by java.nio, see: http://download.oracle.com/javase/6/docs/technotes/guides/intl/encoding.doc.html?toc=0

SEPARATOR

Defines the characters used to delimit the columns in your data. Any character in the list will be used as a delimiter, including a tab. For example, after specifying SEPARATOR ',;:', every comma, semicolon, and colon in a log record starts a new column.


PARSER_CLASSNAME

Name of classname for custom parsers. Only used when Custom is indicated for FORMAT_TYPE


SKIP_HEADER

Whether or not a formatter will write a header.


FORMATTER_CLASSNAME

Name of classname for custom formatters. Only used when Custom is indicated for FORMAT_TYPE


FORMATTER_INCLUDE_ROWTIME

True or False