Using the ECD Agent

Writing Data Remotely

To read data from remote locations, you need to configure and instantiate the Extensible Common Data agent. This agent is a standalone Java application that mediates data between SQLstream s-Server and external data sources.

The agent is supplied either as part of the distributed SQLstream product or as part of the ClientTools download from the SQLstream website (via SQLstream-VERSION-clienttools-linux.run or SQLstream-client-tools-VERSION-windows.exe).

The ECDA Agent uses the SQLstream JDBC driver to select from a stream in s-Server. You need to create a stream before implementing the agent.

The ECDA Agent reads data from the remote source and writes them to an existing stream in s-Server.

The agent itself is a wrapper (or “hoster”) for the ECDA (Extensible Common Data Adapter). See the topic Reading from Other Sources for more information on the ECD adapter. The agent is configured much like the adapter, except that you use command-line options or properties files instead of foreign stream options to configure the agent.

Requirements

The server hosting the ECDA Agent must have a Java Runtime Environment (or a JDK) installed.

Configuring and Using the ECDA Agent

To use the agent, you launch it from the command line, using a command along the following lines:

./commondataagent.sh --input --io file --props /var/sqlstream/etc/file_demo.properties --schemaName fileSource --tableName fileSourceStream

The agent can be launched from anywhere on the machine on which it is installed. The script require the following minimum command line arguments:

–input. Specififies that the agent reads from the server on which the agent resides. –io Determines the input/output format to be used by the agent. Options are file (which is the default), net (network), mail (mail server), amqp, mqseries, kafka, or hdfs. –schemaName, –tableName. These indicate the s-Server stream to be read from. You can configure these through the properties file. When specified on the command line, they override any setting in the properties file.

Configuring the EcdaAgent

You configure the rest of the EcdaAgent either by specifying a properties file or using command-line parameters. We recommend using the properties file option, though it may be useful at times to use base configuration information in the properties file and use the command line to change one or two options.

Using Command Line Options Versus Property File Options

You use Property File options to specify how the ECDA Agent communicates with a particular data source, such as a log file. The properties file configures the format type, directory, separator character, and so on. You can also configure stream information in the properties file.

However, you may wish to use the same agent to connect with another instance of s-Server. In this case, you can configure server connection and stream information from the command line, using the arguments defined above.

Running the Agent

To invoke the ECDA Agent:

  1. Declare or identify a destination stream in s-Server. For more information on creating a stream, see the topic CREATE STREAM in the Streaming SQL Reference Guide.
  2. Create a properties file with information about the data source and destination stream. Property file options are described below. The following example is for an agent that reads from a stream called fileSourceStream in a schema called fileSource and writes to an CSV file located in the directory /home/guavus/output:
# Location, date format, prefix, suffix
FORMATTER=CSV
CHARACTER_ENCODING=UTF-8
ROW_SEPARATOR=\000A
SEPARATOR=,
WRITE_HEADER=false
DIRECTORY=/home/guavus/output
ORIGINAL_FILENAME=stocks-output.csv
FILENAME_PREFIX=output-
FILENAME_SUFFIX=.csv
FILENAME_DATE_FORMAT=yyyy-MM-dd-HH:mm:ss
FILE_ROTATION_SIZE=20K
FORMATTER_INCLUDE_ROWTIME=true
# Schema, name, and parameter signature of destination stream
SCHEMA_NAME=stocks
TABLE_NAME=output_file
#columns
ROWTYPE=RECORDTYPE(TIMESTAMP order_time, INTEGER amount, VARCHAR(100) ticker)

  1. Run the commondataagent.sh script. The following example specifies that the agent is run as input, with a format type of kafka, using a properties file at /var/sqlstream/etc/kafka_demo.properties.
./commondataagent.sh --input --io kafka --props /var/sqlstream/etc/kafka_demo.properties --plugin /opt/sqlstream/5.1.0.14827/s-Server/plugin/kafka/kafka.jar

Command Line Arguments for ECDA Agent

Command Line Argument Definition
–input, –output –input specifies reader (inserting)–output specifies writer (selecting)One of these is required.
–io Input/output source. Defaults to file. Options arefile–reads/writes over the file systemnet–reads/writes over a networkamqp–reads/writes over amqpkafka–reads from/writes to kafkamqseries–reads from/writes to mqseriesmail–reads from/writes to mail serverhdfs–reads from/writes to Hadoop file system
–props Indicates properties file to be passed. Some properties, such as –rowType, –tableName, –schemaName can be also be configured through the command line.
–schemaName–tableName These indicate the s-Server stream to be read from. They override any setting in the properties file.
–rowType This is optional in many use cases, because the parser will automatically generate column names. This is always RecordType and takes as options column names for the stream. In writing column names, you enter the column type first, such as VARCHAR(2040) or INTEGER, then the column name exactly as it appears in the stream declaration. Column names have implicit quotation marks; that is, id in a ROWTYPE property is equivalent to “id” in a stream declaration.–ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at)
–uri, –user, –pass Connection information for the s-Server JDBC driver. You only need to specify these if you are connecting to an s-Server other then the one defined in in client-tools/default.conn.properties.
–formatter This also overrides any setting in the properties file. File format to be processed by agent.Possible values are CSV, XML, JSON, Custom
–plugin This is the location of the jar file which contains the appropriate ECDA plugin. For example, for the Kafka Agent, this argument would be set to a value like this:plugin/kafka/kafka.jar The resulting boot command for the Kafka Agent would then beclienttools/commondata/commondataagent.sh –input –io kafka –props \$propertyFile –plugin plugin/kafka/kafka.jar where *$propertyFile* is the name of the file containing the agent property files (as described below).

At minimum, you need to specify the agent as a reader or writer and a configuration option.

Properties File Configuration Options

The properties file determines most of the options for the agent (most of the options set as part of a foreign stream definition for the ECD adapter). Some options are general to all sources, and other options need to be specified for each source. See the tables below for more details.

To specify a properties file, enter the following:

--props example.properties

where example.properties is the name of your property file.You list property files using a text editor. Property names are case-sensitive.

By default, the EcdaAgent uses the connection info shared with all the other tools and agents in client-tools/default.conn.properties, and uses a default data format of file.

Writing Data Remotely

To write to files in remote locations, you need to configure and instantiate the Extensible Common Data agent. This agent is a standalone Java application that mediates data between SQLstream s-Server and external data sources.

The agent is supplied either as part of the distributed SQLstream product or as part of the ClientTools download from the SQLstream website (via SQLstream-5.2.0-clienttools-linux.run or SQLstream-client-tools-5.2.0-windows.exe).

The ECDA Agent uses the SQLstream JDBC driver to select from a stream in s-Server. You need to create a stream before implementing the agent.

The ECDA Agent reads rows from an existing stream in s-Server and writes them to a destination on a remote server

The agent itself is a wrapper (or “hoster”) for the ECDA (Extensible Common Data Adapter). See the topic Reading from Other Sources for more information on the ECD adapter. The agent is configured much like the adapter, except that you use command-line options or properties files instead of foreign stream options to configure the agent.

Requirements

The server hosting the ECDA Agent must have a Java Runtime Environment (or a JDK) installed.

Configuring and Using the ECDA Agent

To use the agent, you launch it from the command line, using a command along the following lines:

./commondataagent.sh --output --io file --props /var/sqlstream/etc/file_demo.properties --schemaName fileSource --tableName fileSourceStream

The agent can be launched from anywhere on the machine on which it is installed. The script require the following minimum command line arguments:

–output. Specififies that the agent reads from the server on which the agent resides.

–io Determines the input/output format to be used by the agent. Options are file (which is the default), net (network), mail (mail server), amqp, mqseries, kafka, or hdfs.

–schemaName, –tableName. These indicate the s-Server stream to be read from. You can configure these through the properties file. When specified on the command line, they override any setting in the properties file.

Configuring the EcdaAgent

You configure the rest of the EcdaAgent either by specifying a properties file or using command-line parameters. We recommend using the properties file option, though it may be useful at times to use base configuration information in the properties file and use the command line to change one or two options.

Using Command Line Options Versus Property File Options

You use Property File options to specify how the ECDA Agent communicates with a particular data source, such as a log file. The properties file configures the format type, directory, separator character, and so on. You can also configure stream information in the properties file.

However, you may wish to use the same agent to connect with another instance of s-Server. In this case, you can configure server connection and stream information from the command line, using the arguments defined above.

Running the Agent

To invoke the ECDA Agent:

  1. Declare or identify a source stream in s-Server. For more information on creating a stream, see the topic CREATE STREAM in the Streaming SQL Reference Guide.
  2. Create a properties file with information about the data source and source stream. Property file options are described below. The following example is for an agent that reads from a stream called fileSourceStream in a schema called fileSource and writes to an CSV file located in the directory /home/guavus/output:
# Location, date format, prefix, suffix
FORMATTER=CSV
CHARACTER_ENCODING=UTF-8
ROW_SEPARATOR=\000A
SEPARATOR=,
WRITE_HEADER=false
DIRECTORY=/home/guavus/output
ORIGINAL_FILENAME=stocks-output.csv
FILENAME_PREFIX=output-
FILENAME_SUFFIX=.csv
FILENAME_DATE_FORMAT=yyyy-MM-dd-HH:mm:ss
FILE_ROTATION_SIZE=20K
FORMATTER_INCLUDE_ROWTIME=true
# Schema, name, and parameter signature of origin stream
SCHEMA_NAME=stocks
TABLE_NAME=output_file
#columns
ROWTYPE=RECORDTYPE(TIMESTAMP order_time, INTEGER amount, VARCHAR(100) ticker)

  1. Run the commondataagent.sh script. The following example specifies that the agent is run as input, with a format type of kafka, using a properties file at /var/sqlstream/etc/kafka_demo.properties.
./commondataagent.sh --output --io kafka --props /var/sqlstream/etc/kafka_demo.properties --plugin /opt/sqlstream/5.1.0.14827/s-Server/plugin/kafka/kafka.jar

Command Line Arguments for ECDA Agent

Command Line Argument Definition
–input, –output –input specifies reader (inserting)–output specifies writer (selecting)One of these is required.
–io Input/output source. Defaults to file. Options arefile–reads/writes over the file systemnet–reads/writes over a networkamqp–reads/writes over amqpkafka–reads from/writes to kafkamqseries–reads from/writes to mqseriesmail–reads from/writes to mail serverhdfs–reads from/writes to Hadoop file system
–props Indicates properties file to be passed. Some properties, such as –rowType, –tableName, –schemaName can be also be configured through the command line.
–schemaName–tableName These indicate the s-Server stream to be read from. They override any setting in the properties file.
–rowType This is optional in many use cases, because the parser will automatically generate column names. This is always RecordType and takes as options column names for the stream. In writing column names, you enter the column type first, such as VARCHAR(2040) or INTEGER, then the column name exactly as it appears in the stream declaration. Column names have implicit quotation marks; that is, id in a ROWTYPE property is equivalent to “id” in a stream declaration.–ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at)
–uri, –user, –pass Connection information for the s-Server JDBC driver. You only need to specify these if you are connecting to an s-Server other then the one defined in in client-tools/default.conn.properties.
–formatter This also overrides any setting in the properties file. File format to be processed by agent.Possible values are CSV, XML, JSON, Custom
–plugin This is the location of the jar file which contains the appropriate ECDA plugin. For example, for the Kafka Agent, this argument would be set to a value like this:plugin/kafka/kafka.jar The resulting boot command for the Kafka Agent would then beclienttools/commondata/commondataagent.sh –input –io kafka –props \$propertyFile –plugin plugin/kafka/kafka.jar where *$propertyFile* is the name of the file containing the agent property files (as described below).

At minimum, you need to specify the agent as a reader or writer and a configuration option.

Properties File Configuration Options

The properties file determines most of the options for the agent (most of the options set as part of a foreign stream definition for the ECD adapter). Some options are general to all sources, and other options need to be specified for each source. See the tables below for more details.

To specify a properties file, enter the following:

--props example.properties

where example.properties is the name of your property file.You list property files using a text editor. Property names are case-sensitive.

By default, the EcdaAgent uses the connection info shared with all the other tools and agents in client-tools/default.conn.properties, and uses a default data format of file.