Pentaho Data Integration PostgreSQL Bulk Loader Gotcha

The password you key in the connection won't work. It only works when authentication is disabled for the server Pentaho is running on.

Reference: http://wiki.pentaho.com/display/EAI/PostgreSQL+Bulk+Loader#PostgreSQLBulkLoader-Setupauthentication

Pentaho Data Integration: Unwinding JSON Array Elements From MongoDB

This can be done in the MongoDB Input step. However, it only supports one level of nesting.

To do that, use "arrayName[*]" as part of the path.

Pentaho: MySQL Bulk Load

In MySQL, bulk load operations i.e. "LOAD DATA" has much faster performance than INSERT. In Pentaho's Data Integration module, it is possible to directly perform a bulk load operation into MySQL, using the "MySQL Bulk Loader" Step (in the "Bulk loading" folder).

However, one "gotcha" with it (version 4.3) is that it will crash on an empty stream. You'll either have to find a way to handle it, or use it only in situation where that either doesn't matter or never happens, or hope they fix this in future versions.

Pentaho Data Integration 4.3 With MySQL

With version 4.3 of Pentaho Data Integration, the MySQL driver is not bundled because of "license compatibility issues". So if you need to connect to the MySQL database, you'll need to download the JDBC driver separately and copy it into the "libext/JDBC" folder.

Pentaho Data Types

Link: http://wiki.pentaho.com/display/EAI/User+Defined+Java+Expression

Pentaho Kettle User Defined Java Class: Creating Multiple Output Rows For Each Input Row

To do that, you'll need to clone the input row:

for(int i=1; i<=12; i++) {

Object[] clonedRow = getInputRowMeta().cloneRow(r);

   Object[] newRow = createOutputRow(clonedRow, clonedRow.length);

}

Pentaho Kettle Text Padding Issue

Pentaho Kettle will pad text strings that come from the DB up till the size specified in the DB column (e.g. 100 in the case of varchar(100). Trim instructions don't work in getting rid of the padding. The important part is removing the length attribute when creating the output, or click the "Minimal Width" button where available.