Database Connections
Database connections ..
Workshop - Database Connections
Database connectivity forms the foundation of enterprise data integration - from transactional systems and data warehouses to cloud databases and legacy applications. Organizations depend on reliable database connections to extract, transform, and load data across their entire technology ecosystem. Understanding how to configure, test, and manage database connections in PDI is essential for building robust data pipelines that connect diverse relational database systems.
In this hands-on workshop, you'll learn to establish and manage database connections using PDI's connection framework. Steel Wheels maintains data across multiple database platforms, and you'll configure connections to access their enterprise systems. You'll work with JDBC drivers, configure connection parameters, test connectivity, and explore database objects using the built-in Database Explorer. You'll also learn to share connections across transformations and jobs, ensuring consistency and reusability across your data integration projects.
What You'll Accomplish:
Install and configure JDBC drivers for database connectivity
Create database connections with proper authentication credentials
Configure connection parameters including host, port, database name, and access type
Test database connections before using them in transformations
Use the Database Explorer to navigate schemas, tables, and data
Execute SQL queries directly from the Database Explorer
Share database connections for reuse across multiple transformations
Understand connection types (Native JDBC) and when to use them
Preview data and validate table structures using explorer functions
Recognize best practices for managing connection credentials
By the end of this workshop, you'll have practical experience creating production-ready database connections and understand how to troubleshoot common connectivity issues. You'll develop confidence in working with JDBC drivers and connection strings, essential skills for any data integration developer. Rather than manually coding database access or relying on external connection tools, you'll build native PDI connections that integrate seamlessly with all transformation and job steps requiring database access.
Prerequisites: Understanding of basic database concepts (schemas, tables, authentication), familiarity with JDBC drivers; Pentaho Data Integration installed and configured; appropriate JDBC driver files downloaded for your target database
Estimated Time: 15 minutes
Create a new Transformation
Any one of these actions opens a new Transformation tab for you to begin designing your transformation.
By clicking File > New > Transformation
By using the CTRL-N hot key
DBeaver
Usually you start working with a database by creating a connection to it. The tool you will be using is DBeaver CE; which helps you quickly to create connections to various databases, as the drivers are shipped with the product.
Once you have a database connection you can search for database objects in the Database Navigator, or use the search tools to find specific objects, or compare databases and their contents. You can also edit data and import and export data, and you can create reports about the database and objects in it.
From Pentaho 9.5+ The DBA roles for the users SA & pentaho_admin have been removed for security reasons.
MySQL Database
If you have completed the Setup requirements, then you should have a MySQL Docker container, exposed on port:3306 with sampledata databse.
Launch DBeaver and Select: MySQL.
Configure the connection with the following properties:
Username: root
Password: password
You may need to download the supported version of the database driver.
Also enable: allowPublicKeyRetrieval
Test the connection.
Expand: databases > sampledata > Tables
Open a SQL window and run a test query.
Download JDBC Driver
PDI for obvious reasons doesn't ship with JDBC drivers. You will need to download the correct JDBC driver version compatible with your version of PDI.
Download the JDBC driver for your database.
Copy the driver into: C:\Pentaho\design-tools\data-integration\lib directory
Restart PDI to register the driver.
Pentaho Data Integration Connection
If you intend to work with a database, either reading, writing, looking up data, and so on, the first thing you must do is to create a connection to that database.
In this guided Lab, you will…
• Connect to Steel Wheels sampledata database (MySQL)
Define Database Connection (MySQL)
Create a Transformation.
From within Spoon, Select:
File > New > Database Connection The Database Connection dialog box appears.
Connection Name
Type name that uniquely identifies your new connection
Connection Type
Select the type of database to which you are connecting
Access
Select your method of access. Available access types depend on the connecting database type
Host Name
Type the name of the server that hosts the database to which you are connecting. Alternatively, you can specify the host by IP address.
Database Name
Enter the name of the database to which you are connecting. If you are using a ODBC connection, enter the Data Source Name (DSN) in this field.
Port Number
Enter the TCP/IP port number if it is different from the default.
User name
Type the user name used to connect to the database.
Password
Type the password used to connect to the database.
Enter the following details:
If you use a MariaDB driver above version 2.7.x then you will receive an error message on: 'fetch size' - use the MySQL driver connection instead.
Connection Name
MySQL: sampledata
Connection Type
MySQL
Host Name
localhost or IP address
Database Name
sampledata
User name
pentaho_admin
Password
password
Click Test.
A confirmation message displays if Spoon can establish a connection with the target database.
Share Database Connection
Sharing assets
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.
This shares the connection with your users. They will be able to select the shared connection. From within the View tab, click Explore to open the Database Explorer for an existing connection. This shows you the schemas and tables inside the connection.
Explore Database
The database explorer allows you to explore any configured database. When you open the database explorer, the first thing you see is a tree with the different objects of the database.
As soon as you select a database table, all buttons to the right side become available for you to explore that table. The following are the functions offered by the buttons at the right side of the database explorer:
Click on the View tab, expand Database Connections.
Right-click MySQL:sampledata and choose Explore from the menu options.
In the Database Explorer window, expand Sampledata > Tables
Right-click the CUSTOMERS table and choose Preview first 100 from the menu options
Examine the customer data.
Select ‘View SQL’. The simple SQL editor appears. Type this SQL:
Click Execute.
Last updated
Was this helpful?