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 Developer Edition of PDI, and work through the PDI Tutorial to demonstrate ETL with SingleStoreDB as our database system.
The project files used in this article are available on GitHub.
Introduction
This article used a Virtual Machine running Ubuntu 22.04.2 with OpenJDK 11.
Create a SingleStoreDB Cloud account
A previous article 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:
CREATE DATABASE IF NOT EXISTS pdi_demo;
Download and unpack PDI
We'll download the Developer Edition edition of PDI. The version used when writing this article was pdi-ce-10.2.0.0-222. The zip file was unpacked to a data-integration folder.
From the data-integration folder, we'll run:
./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:
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:
sudo apt update
If there is an error similar to this:
The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 3B4FE6ACC0B21F32
We need to run the following:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 3B4FE6ACC0B21F32
Then the following commands should be successful:
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:
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:
sudo vim /etc/java-11-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. 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:
./spoon.sh
This time, the software should start correctly.
PDI Tutorial
The 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 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: <host>
- Database Name: pdi_demo
- Port Number: 3306
- Username: admin
- Password: <password>
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 > Run. The result should be as shown in Figure 1.
Similarly, we'll run the Job pipeline using Action > Run. The result should be as shown in Figure 2.
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.
Top comments (0)