In response to customer requests we created a new component that writes data into Infobright software, a very popular column-oriented relational database. The CloverETL’s just released version 2.9 offers this new InfobrightDataWriter component.
Infobright is a highly compressed column-oriented database, based on MySql engine. In this database data are stored column-by-column instead of more typically row-by-row. There are many advantages of column-orientation, including the ability to do more efficient data compression and allowing compression to be optimized for each particular data type. The higher efficiency can be achieved because each column stores a single data type as opposed to rows that typically contain several data type. However the main purpose of Infobright software is to deliver a scalable data warehouse database optimized for analytic queries.
Our new InfobrightDataWriter component allows to easily load data into Infobright database directly from CloverETL transformations. It can be used both with Infobright Community Edition (ICE) as well as with Infobright Enterprise Edition (IEE). With IEE it is possible to use fast data loading in binary format. In binary format particular rows aren’t separated by any special character and there are no values’ delimiters or qualifiers either.
The connector uses named pipe for data transfer. In Linux systems it is system pipe, on Windows you need native library placed on the Java library path. The library is part of infobright-core-vX_X package that can be downloaded from Infobright site (Download Contributed Software).
For remote load it is necessary to start Infobright remote load agent on the server where Infobright is running (part of infobright-core-v3).
The above graph loads data from selected input fields (“Clover fields” attribute) to database table
test (“Database table” attribute) on remote server. It uses fast binary method of data loading (“Data format” attribute). Data loaded into database can be sent to output port. Input metadata has free format but the output one has to meet certain conditions:
- comma as a field delimiter
- system new line (
'rn'for Windows) as a record delimiter
- date/time formatting: yyyy-MM-dd HH:mm:ss (can be date only, time only or both)
As we load data to the remote server, the Infobright agent must run on the server. To start the agent we need to call:
java -jar infobright-core-3.0-remote.jar -p 6666 -l all
The port the agent is listening to must be the same as the agent port set on the component (“Remote agent port” attribute).
Full description of the component can be found on CloverETL’s wiki pages (InfobrightDataWriter component).