CREATE SERVER

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, and Kafka. Options.
  • SYS_JDBC. A plugin implementing SQL/MED access to any foreign DBMS for which a JDBC driver is available. 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 Integrating Guavus SQLstream with Other Systems.

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

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 .property will override the corresponding stream option. If the stream has specified the option JndiPrefix, then that prefix is used instead of .

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.
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 Integrating Guavus SQLstream with Other Systems 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=;’ 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.
JNDI_WRITEBACK True/False. Defaults to “false”. When you specify the JNDI_WRITEBACK option as “true” in the DDL or in the.properties file, s-Server writes back consolidated options (including options inherited from the parent as well as from the properties file) to the same properties file under $SQLSTREAM_HOME/plugin/jndi. This lets you configure the JNDI file using DDL.

Example

Here is an example that configures a connection to a file server. See Writing to the File System for more information.

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'
);