CREATE SERVER

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > CREATE statements >

CREATE SERVER

Previous pageReturn to chapter overviewNext page

In order to access a foreign data source, you first need to create a server object. A server object contains connection information that a foreign-data wrapper uses to access the external data source.

CREATE SERVER creates an instance of a plugin with these options. Server options required depend on the data wrapper used. Most foreign data sources use one of three data wrappers:

ECDA. The Extensible Common Data Adapter, which lets you read and write data from s-Server to and from a variety of sources, including the file system, network sockets, AMQP, Kafka, and IBM MQ. Options.

SYS_JDBC. A plugin implementing SQL/MED access to any foreign DBMS for which a JDBC driver is available. Options.

WebFeed. A plugin that lets you read a web-based data feed, such as an RSS or Atom feed. It tails this feed and parses results into a columned stream, using the xpath syntax. Options.

To read from or write to a foreign data source, you use the Extensible Common Data Adapter. For more information on the code structure for writing to sources using this adapter, see the topic Reading from Other Sources in the Integration Guide.

For example, the following code defines an Extensible Common Data Adapter server of type "file", for reading over the file system:

CREATE OR REPLACE SERVER "FileReaderServer" TYPE 'FILE'

FOREIGN DATA WRAPPER ECDA;

 

Syntax

create_server

Note: All foreign servers can take JndiParams as a parameter, naming a JndiParams property file located in $SQLSTREAM_HOME/plugin/jndi. Any property in that JndiParams property file will override any option specified in the server options. Servers whose streams use options from the server options as defaults can also have their properties come from the JndiParams property file. Properties of the form <stream name>.property will override the corresponding stream option. If the stream has specified the option JndiPrefix, then that prefix is used instead of <stream name>.

Server Definition Options for Extensible Common Data Adapter

All server declarations for the Extensible Common Data Adapter must declare a type. Type parameter accepts the following options.

Option

I/O Type

file

Reading and writing over the file system. See the topics Reading from the File System and Writing to the File System for more details.

net

Configured for a socket. Reads or writes data streamed from a client program using TCP or UDP. See the topics Reading from Network Sockets and Writing to Network Sockets for more details.

amqp

Reads to and writes to AMQP message bus. See the topics Reading from AMQP and Writing to AMQP for more details

kafka

Allows s-Server to exchange data with Kafka clusters. See the topics Reading from Kafka and Writing to Kafka for more details.

kinesis

Allows s-Server to exchange data with Kinesis streams. See the topics Reading from Kinesis and Writing to Kinesis for more details.

websocket

Allows s-Server to read and write data over web sockets. See the topics Reading from Websockets and Writing to Websockets for more details.

http

Allows s-Server to read and write data over http. See the topics Reading over HTTP and Writing over HTTP for more details.

mongodb

Allows s-Server to write to MongoDB. See the topic Writing to MongoDB for more details.

snowflake

Allows s-Server to write to Snowflake warehouses. See the topic Writing to Snowflake for more details.

IBM MQ

Reads to and writes from a data system using IBM MQ messaging. See the topics Reading from IBM MQ and Writing to IBM MQ for more details

hive

Writes to the Hadoop file system. See Using the ECDAgent with Hadoop for more details.

Server Definition Options for RDBMS Databases

The table below lists options for reading from and writing to RDBMS databases. See the topics Reading Data from RDBMS Sources and Writing Data to RBDMS Destinations in the Integration Guide for more details.

Name

Description

DRIVER

Class name of the JDBC Driver for the remote database. Fully-qualified name of the JDBC driver class to load. This must be available on the classpath.

URI

JDBC URI

USER_NAME

Remote database user.

PASSWORD

Remote database password

DIALECT

Type of database, generally auto detected. Valid values are: Oracle, POSTGRES, MYSQL, TERADATA, SQL SERVER

TERADATA_QUERY_BAND

Optional, and for Teradata connections only. Teradata query bands "tag" the query. For more information, see

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/End_Logging-Syntax.027.143.html

This is run before the actual query. The submitted SQL looks like the following:

SET QUERY BAND = 'ApplicationName=SQLstream_s-Server;Version=<ourversion>;<value from TERADATA_QUERY_BAND option>' FOR SESSION

queryCol

Name of the column to use for a highwater mark, such as ROWTIME. No default

pollingInterval

In milliseconds, how often to sleep when now new rows are available. Default is 1000.

txInterval

How many rows to wait before reading a row, in order to ensure that no rows are missed as a result of rollbacks or dirty reads. If queryCol is ROWTIME, you should consider setting txInterval to 1000. Larger values add latency to the query, but make the stream more consistent (safer) in the face of crashes and multiple inserters to the remote database's table. Smaller values add less latency, may result in a missed row, in the remote chance of a dirty read.

Options for Web Feed Server

The table below lists server options for Web Feed sources. See the topic Reading Data from Web Feeds in the Integration Guide for more details.

Option

Description

Required

Default

URL

Base URL for web feed.

Y

 

MIN_SLEEP_INTERVAL

Minimum sleep interval between fetches, in milliseconds.

N

60000 (one minute)

MAX_SLEEP_INTERVAL

Maximum sleep interval between fetches, in milliseconds.

N

3600000 (one hour)

SLEEP_MULTIPLICAND

Sleep interval is multiplied by this value each time the result is unchanged. If this is set to <1, this allows for exponential backoff, in order to space out repeat transmissions of same data.

Y

2

FEED_FORMAT

Specifies type of web feed to be tailed. For this release, options are "RSS" and "Atom".

Y

 

UNIQUE_ID

XPath to data which makes an entry unique.

 

 

DATE_FORMAT

Format for converting datetime fields into SQL timestamps.

 

For RSS, "EEE, dd MMM yyyy HH:mm:ss z"

For Atom, "yyyy-MM-dd'T'HH:mm:ssz"

DEFAULT_NS_PREFIX

Prefix for elements in default namespace.

 

 

Example

Here is a Extensible Common Data Adapter example that uses no options:

CREATE OR REPLACE SERVER "FileReaderServer" TYPE 'FILE'

FOREIGN DATA WRAPPER ECDA

OPTIONS

(directory 'myDirectory',

//directory for the file

format_type 'CSV',

filename_pattern 'myRecord.csv',

//regex for filename pattern to look for

character_encoding 'UTF-8',

skip_header 'true');

 

Note: ECD Adapter server definitions need to reference the ECD foreign data wrapper. You can do so with the syntax FOREIGN DATA WRAPPER ECDA.

The following code block defines a JDBC connection for a MySQL database.

  CREATE OR REPLACE SERVER mysql_reader

  FOREIGN DATA WRAPPER SYS_JDBC

  OPTIONS (

      DRIVER_CLASS 'com.mysql.jdbc.Driver',

      URL 'jdbc:mysql://localhost:3306/sample',

      USER_NAME 'sqlstream',

      PASSWORD 'sqlstream'

      );