Database connections ..
Create a reusable MySQL connection to the Steel Wheels sampledata database.
sampledata
What you’ll do
Validate the database is reachable (optional, using DBeaver)
Install a JDBC driver (only if PDI does not include it)
Create, test, share, and explore a PDI database connection
Prerequisites
PDI (Spoon) installed and working
A running sampledata database (Docker setup recommended)
Basic understanding of schemas, tables, and authentication
Estimated time: 15 minutes
Workshop files
Download the following file.
Keep the filename unchanged.
Save it in your workshop folder.
Use any of these options to open a new transformation tab:
Select File > New > Transformation
Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)
Ctrl+N
Cmd+N
DBeaver is optional. Use it to confirm the database is reachable before you touch PDI.
DBeaver CE ships with most drivers. That makes it a fast connectivity check.
Pentaho no longer ships a writable sample database user by default. Use the Docker sampledata setup for hands-on database workshops.
If you completed the Setup, you should have a MySQL Docker container. It should be exposed on port 3306 and include the sampledata database.
3306
Launch DBeaver and select MySQL.
Configure the connection:
Username: root
root
Password: password
password
You might need to download the supported driver version.
If the test fails, enable allowPublicKeyRetrieval.
allowPublicKeyRetrieval
Test the connection.
Expand Databases > sampledata > Tables.
Open a SQL window and run a test query:
Checkpoint: you can browse tables and run a query against CUSTOMERS.
CUSTOMERS
PDI does not ship all JDBC drivers. If your database type is missing, add the driver JAR.
Download the JDBC driver for your database.
Copy the driver JAR into your PDI install:
C:\Pentaho\design-tools\data-integration\lib\
~/Pentaho/design-tools/data-integration/lib/
Restart Spoon.
If your install uses lib/jdbc/, place the JAR there instead.
lib/jdbc/
Create the connection once. Reuse it in steps like Table input, Table output, and Database lookup.
In this lab, you connect to the Steel Wheels sampledata database (MySQL).
Define a database connection (MySQL)
Create a transformation.
In Spoon, select File > New > Database connection.
The Database connection dialog opens.
Enter the following details:
If you use a MariaDB driver newer than 2.7.x, you might see a fetch size error. If that happens, use the MySQL driver instead.
2.7.x
Connection name: MySQL: sampledata
MySQL: sampledata
Connection type: MySQL
Access: Native (JDBC)
Host name: localhost (or your Docker host IP)
localhost
Database name: sampledata
Username: pentaho_admin
pentaho_admin
Select Test.
Checkpoint: Spoon shows a success message.
Share Database Connection
Share the connection so other transformations can reuse it.
Click OK to save your entries and exit the Database Connection dialog box.
From within the View tab, right-click on the connection and select Share from the list that appears.
Shared connections show up for other users and projects. Use Explore to confirm schemas and tables.
Explore Database
Use Database Explorer to browse schemas, preview rows, and run SQL.
Click on the View tab, expand Database Connections.
Right-click MySQL:sampledata and choose Explore from the menu options:
Preview the first 100 rows of ..
Return the first 100 rows of the selected table.
Preview first .. rows of ..
Enter the number of rows to preview
Number of rows ..
Displays number of rows
Generate DDL
Displays DDL statement that creates table.
Generate DDL for other connection
Select connection to display DDL. Syntax is based on database engine.
Open SQL for ..
Edit SELECT statement
Truncate table
Deletees all the rows from selected table
In the Database Explorer window, expand Sampledata > Tables
Right-click the CUSTOMERS table and choose Preview first 100.
Examine the customer data.
Select View SQL.
Click Execute.
Checkpoint: you can preview CUSTOMERS and execute SQL in Database Explorer.
Last updated 5 months ago
Was this helpful?
select * from CUSTOMERS where COUNTRY = 'USA' and CITY = 'NYC';
