Adapter for log4j

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Appendix A: Legacy Adapters and Agents >

Adapter for log4j

Previous pageReturn to chapter overviewNext page

Note: This topic describes a legacy adapter. See the topics Reading from Other Sources and Writing to Other Destinations for current solutions.

A Java application using the Apache log4j API can use the SQLstream Adapter for log4j to push data seamlessly into the SQLstream system by configuring SQLstream to receive data from an external log4j appender. An appender is the log4j term for an output destination. The many supported appenders can output messages to log files, the console, email servers, GUI components, remote socket servers, JMS, Microsoft Windows NT Event Loggers, remote UNIX syslog daemons, and JDBC connections.

HOWEVER, please note that the SQLstream Adapter for log4j is currently a beta implementation based on the log4j 1.3alpha8 release, which is no longer supported or developed by the Apache project. (This documentation is also beta.)

Because there are known issues with that Apache code branch, you may want to reconsider its suitability for your application.

One feasible alternative would be to use the application's log4j implementation to write data to a log file,

and then use the SQLstream Log File Adapter to inject the data into the SQLstream s-Server.

What is log4j?

Apache log4j is a versatile open source Java platform for logging that includes easy-to-configure logging levels, output types, and formats. The next section provides a short introduction to the log4j concepts used throughout this document. For a more exhaustive description, please refer to


In log4j, every log message is logged by a Logger (also known as a Category for earlier versions of log4j). The application developer decides what messages should be logged by which logger. Normally, developers create a logger for each class or file, but other methodologies are permissible. One key benefit of log4j is the ability to dynamically choose which logger or set of loggers (e.g., all loggers in a specific package) to activate at any given time.

Logging Levels

A standard log4j message belongs to one of the following log levels (or priorities) in decreasing level of verbosity:

Trace (since log4j 1.2.12)






The logging level is configurable for each logger and can be done via the log4j configuration file or programmatically.


Each logger can be configured to use one or more appenders or data sinks. For example, it is possible to have an email alert sent out at the same time an SMS message is sent whenever a log message of level error or higher is issued. The activating level is referred to as the Threshold and is configurable.


Receivers are the opposite of appenders and were introduced in log4j 1.3. Receivers are used to bring logging events into the system. log4j 1.3 includes the following receivers:

Type of Receiver

Events Received by this Receiver Type


Events from SocketAppender


Events from SocketHubAppender


Events from UDPAppender


Events from JMSAppender


Events from XMLSocketAppender


Events by parsing and tailing log files


A View of a log message (think of the Model View Controller design pattern) can be specified through the choice of a Layout. A Layout can be an HTML formatted message, a simple text string, or any other output appropriate for a logging application. Layouts permit customization, e.g., date and time configured to fit the localized settings.


Filter chains can be used to decide which message to be logged. The filter rules can be customized. log4j ships with string match expression rules in order to filter messages by the message content and/or level.

Converting Log Messages to Stream Data

Following is a description of how a log4j message integrates with SQLstream. log4j messages fit nicely into the SQLstream model due to their inherent asynchronous nature. Each log message is converted into a row in a stream, where each column in the row represents information about a log message. The following information can be extracted from a standard log4j log message:

Logger name (category)

The log message - can be in binary format represented by a Java object

Log level (priority) of the logging event

Date and time

Classname of the caller issuing the logging request

*Filename of the caller issuing the logging request (See the topic Dynamic Logger in this guide for more details)

*Line number where the logging request was issued (See the topic Configuration Parameters in this guide for more details)

*Method name where the logging request was issued (See the topic Configuration Parameters in this guide for more details)

Number of milliseconds elapsed since the start of the application

Name of the thread generating the logging event

The NDC (nested diagnostic context) associated with the thread generating the logging event

The MDC (mapped diagnostic context) associated with the thread generating the logging event

Java exception information

(*) Items marked with an asterisk in the above list constitute extremely time-consuming operations that should only be used if performance is not an issue.

Each log4j message becomes a row placed into a stream. The values that actually get inserted are configurable and extensible.

Each logger available to the adapter is exposed to the control node as a stream. Loggers can later be mapped into one stream by a FULL JOIN.

Modes of Operation

There are two modes for integrating log4j loggers into the SQLstream system, static and dynamic.

Static Logger Creation

In static mode, streams are manually created and configured for each logger intended to be exposed to the SQLstream system.

A customized third party JDBC Appender is available at It is open source and distributed under the Apache License (log4j under v1.1 and JDBC appender under v2.0), supporting J2SDK 1.3, 1.4 and 1.5 as well as Log4j 1.2.9 and current development. The latter JDBC appender only requires the SQLstream JDBC driver.

Example: Using a Static Logger

In this example, we use the third party JDBCAppender to insert into an existing stream in the SQLstream system.

Create a native stream (or table) for incoming log4j messages:

CREATE SCHEMA log4j_jdbc;

CREATE STREAM log4j_jdbc.log1


   LOGGER varchar(20),

   LEVEL varchar(10),

   MESSAGE varchar(80)



Configure the application's log4j framework to use a JDBCAppender that is configured to insert into the stream.

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="">

   <appender name="JDBC" class="org.apache.log4j.jdbcplus.JDBCAppender">

       <param name="Connector"

              value="com.sqlstream.plugin.log4j.Log4JJdbcConnectionHandler" />

              <param name="Url" value="jdbc:sqlstream:sdp://localhost" />

       <param name="Dbclass" value="com.sqlstream.jdbc.Driver" />

       <param name="Username" value="sa" />

       <param name="Password" value="" />

       <param name="Sql"

              value="INSERT INTO log4j_jdbc.log1 VALUES('@CAT@', '@PRIO@', '@MSG@')" />



       <level value="debug" />

       <appender-ref ref="JDBC" />




After the application starts logging to the JDBCAppender, the log messages will be inserted into the stream and can be queried.

-- listen to stream

SELECT STREAM * FROM log4j_jdbc.log1;


Similarly, the SQLstream system can be configured to work with the other JDBC appenders that log4j offers.

Dynamic Logger Creation

In dynamic mode, a log4j adapter queries the available loggers in the log4j framework and makes them available in a virtual foreign schema. These streams are FOREIGN STREAMS as opposed to the normal streams used with Static Logger Creation. See the topic CREATE FOREIGN STREAM in the s-Server Streaming SQL Reference Guide fore more details.

The core of this approach is as follows:

A log4j appender is responsible for reading all necessary configuration parameters and for the interface to and from the log4j framework.

The log4j adapter implements the SQL/MED interface and exposes the available loggers to the SQLstream system through a virtual SQL/MED schema. The log4j adapter makes use of a log4j receiver to receive log4j events from the application. After receiving a log event, the log4j adapter uses an internal appender to push the log event onto the appropriate stream.

In order to activate the adapter, the user will need to manually enter a series of DDL statements to create the foreign data wrapper, foreign data server, and foreign data streams. See the example section for statement examples and the log4j Adapter Configuration Parameters section for statement options.

The log4j adapter allows input into SQLstream s-Server through the creation of a "source" data stream, and output into a log4j logger through the creation of a "sink" data stream. The CREATE FOREIGN STREAM statement is used to create these foreign data streams, and the statement is validated by the adapter to check if the column names and types are valid.

Each log4j data server will correspond to a different log4j repository, with the configuration specified by a log4j configuration file (an XML or properties file). For input into SQLstream, the appropriate log4j receivers need to be configured. For SQLstream to log using log4j, either for error logs or for log4j sink streams, the appropriate loggers and appenders will also need to be configured.

By using the IMPORT FOREIGN SCHEMA construct, foreign streams will be automatically created and added to a local schema based on the virtual schema. A foreign stream 'source' will be created for each logger in the repository.

Users who have specialized needs can use MDC fields to extend the available columns in a stream, such as to add to each log message the current CPU load or memory availability.

Example: Using a Dynamic Logger

To get a better feel for what is going on with the dynamic logger, a step-by-step walkthrough describing how an application starts up and exposes itself to the SQLstream system is presented below.

1.Start the application. Before doing so, you or an administrator needs to have configured the application correctly to log to an appender with a corresponding receiver. For instance, the application can lo to a SocketAppender.

2.To use the log4j adapter, issue the following DDL statements:

-- install the adapter jar


   LIBRARY 'class com.sqlstream.plugin.log4j'


-- instantiate a foreign server

CREATE SERVER Log4jAgentDoubleOhSeven


   OPTIONS (CONFIG 'log4j.xml',


       ERROR_LOGGER 'sqlstream.errors');


3.Make sure the log4j.xml file should contain a corresponding receiver for the appender used by the application. The Adapter uses the configuration in the file log4j.xml to configure the log4j repository in the server JVM.

For instance, if the application is using a SocketAppender, the Adapter should be configured to use a SocketReceiver. When the Adapter finishes loading and initializing itself, it will expose the available loggers in a virtual schema. Each logger of a received logging event is exposed as a corresponding foreign source stream.

4.To start reading and streaming data, create log4j foreign streams explicitly using code along the following lines:

--- create a schema to hold all loggers on Log4jAgentDoubleOhSeven


-- define streams

CREATE FOREIGN STREAM loggers.loggerName1


       LOGGER varchar(...),

       CLASSNAME varchar(...),



   SERVER Log4jAgentDoubleOhSeven




       LOGGER varchar(...),

       LEVEL varchar(...),

       MESSAGE varchar(...)


   SERVER Log4jAgentDoubleOhSeven


-- listen to stream with log levels fatal only


-- send log message to log4j by inserting into stream

INSERT INTO loggers.sink

   VALUES ('SQLstream', 'fine', 'log message');


5.You can query loggers directly through the virtual schema, or you can import them into a local schema.

-- query the virtual schema directly (assuming there is logger called mylog)

SELECT * FROM Log4JAgentDoubleOhSeven.logs.mylog;

-- import foreign streams into a local schema

CREATE SCHEMA importedLoggers;


    INTO importedLoggers;


Changing the log4j Appender's configuration parameters can now be done remotely by issuing an appropriate ALTER FOREIGN [SERVER | STREAM] statement.

Whenever a new log message is raised over a certain priority in the log4j framework, it will propagate through SQLstream's log4j Appender to the Adapter which will stream into the SQLstream system through the SQL/MED interface.

Error Handling

The following errors can occur while using the SQLstream log4j Adapter:

Connection failure. Incorrect connection parameters supplied. An error is reported to the user.

Stream name already exists. If multiple instances of an application are running on the same machine, a situation can arise where a default stream name already exists. An error is reported to the user.

Unexpected error while pushing messages. The statement is correctly formed, but for some reason the statement does not execute due to errors outside the adapter. A possible cause is a general system failure. An error is reported to the user.

Bandwidth not sufficient. When the appender receives more log messages than it has bandwidth to send out, some sort of buffering is necessary. An error is reported to the user.

Access Restriction. Whenever the adapter tries to do an unprivileged operation, a security exception is thrown. An error is reported to the user.