Quick tip: Using Pentaho Data Integration (PDI) with SingleStoreDB
Tags: singlestoredb, pentaho, etl
Published: 2022-11-02 14:44:30

## Abstract
Continuing our use and evaluation of open-source and commercial tools with SingleStoreDB, we'll focus on Pentaho Data Integration (PDI) in this short article. We'll use the Community Edition (CE) of PDI, and work through the [PDI Tutorial](https://help.hitachivantara.com/Documentation/Pentaho/9.0/Setup/Pentaho_Data_Integration_(PDI)_tutorial) to demonstrate ETL with SingleStoreDB as our database system.

The project files used in this article are available on [GitHub](https://github.com/VeryFatBoy/pdi-demo).

## Introduction
This article used a Virtual Machine running Ubuntu 20.04.3 with OpenJDK 1.8.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **PDI Demo Group** as our Workspace Group Name and **pdi-demo** as our Workspace Name. We'll make a note of our **password** and **host** name. Finally, we'll create a new database using the **SQL Editor**:

```sql
CREATE DATABASE pdi_demo;
```

## Download and unpack PDI
PDI can be [downloaded](https://sourceforge.net/projects/pentaho/files/latest/download) from SourceForge. The version used when writing this article was `pdi-ce-9.3.0.0-428`. The zip file was saved in the `Downloads` folder and unpacked to a `data-integration` folder.

From the `data-integration` folder, we'll run:

```shell
./spoon.sh
```

Several warning and error messages may appear; we'll need to fix these before the software runs correctly.

### 1. no libwebkitgtk-1.0 detected
This can be fixed by editing the following file:

```shell
sudo vim /etc/apt/sources.list
```

and adding the following to the end of the file and saving the file:

```
deb http://cz.archive.ubuntu.com/ubuntu bionic main universe
```

Next, we'll run the following commands:

```shell
sudo apt update

sudo apt install libwebkitgtk-1.0-0
```

### 2. Failed to load module "canberra-gtk-module"

This can be fixed by installing the following:

```shell
sudo apt install libcanberra-gtk-module libcanberra-gtk3-module
```

### 3. Assistive Technology not found: org.GNOME.Accessibility.AtkWrapper

This can be fixed by editing the following file:

```shell
sudo vim /etc/java-8-openjdk/accessibility.properties
```

and commenting out the next line and saving the file:

```
assistive_technologies=org.GNOME.Accessibility.AtkWrapper
```

## Download MySQL connector
We need to obtain `mysql-connector-java-5.1.49` from the [MySQL archives](https://downloads.mysql.com/archives/c-j/). This is a PDI requirement. Unpacking the downloaded archive file, we'll find two jar files:

```
mysql-connector-java-5.1.49-bin.jar

mysql-connector-java-5.1.49.jar
```

We'll copy these two files to `data-integration/lib`.

## Launch PDI
From the `data-integration` folder, we'll again run:

```shell
./spoon.sh
```

This time, the software should start correctly.

## PDI Tutorial
The [PDI tutorial](https://help.hitachivantara.com/Documentation/Pentaho/9.0/Setup/Pentaho_Data_Integration_(PDI)_tutorial) works through an example that reads data from CSV files, creates data pipelines, performs data cleanup, and eventually writes the data to a database system. The project files can be downloaded from [GitHub](https://github.com/VeryFatBoy/pdi-demo) and opened in PDI to save time.

We'll need to adjust our project files with the full paths to the CSV files:

```
data-integration/samples/transformations/files/sales_data.csv

data-integration/samples/transformations/files/Zipssortedbycitystate.csv
```

and configure the database settings, as follows:

- **Connection name:** SingleStoreDB
- **Connection type:** MySQL
- **Access:** Native (JDBC)
- **Host Name:** &lt;host&gt;
- **Database Name:** pdi_demo
- **Port Number:** 3306
- **Username:** admin
- **Password:** &lt;password&gt;

We'll replace the `<host>` and `<password>` with the values from our SingleStoreDB Cloud account.

We'll make any other file path adjustments, as required.

When ready, we'll run the Transformation pipeline using **Action &gt; Run**. The result should be as shown in Figure 1.

![Figure 1. Transformation.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r9172bl0difkznyc0tht.png)

<center>Figure 1. Transformation.</center>

Similarly, we'll run the Job pipeline using **Action &gt; Run**. The result should be as shown in Figure 2.

![Figure 2. Job.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3y1advgf1b2oczsvwp50.png)

<center>Figure 2. Job.</center>

We can check our `SALES_DATA` table in SingleStoreDB. It is quite a wide table with 2823 rows.

## Summary

In this short article, we have successfully configured and run PDI using SingleStoreDB Cloud as our backend database system. Our data pipelines have cleaned the CSV data and written the final output to SingleStoreDB, demonstrating a complete ETL example.