Pentaho Data Integration allows you to define connections to multiple databases provided by multiple database vendors(MySQL, Oracle, Postgres, and many more). Pentaho Data Integration ships with the most suitable JDBC drivers forsupported databases and its primary interface to databases is through JDBC. Vendors write a driver that matches the JDBC specification and Pentaho Data Integration uses the driver. Unless you require extensive debugging or have other needs, you won’t ever need to write your own database driver.
Connections that are available for use with a transformation or job are listed under the Database Connection node in the explorer View in Spoon
There are several ways to define a new database connection:
In Spoon, go to File -> New -> Database Connection.
then under View, right-click Database connections and choose New.
then under View, right-click Database connections and choose New Connection Wizard.
When you define a database connection, the connection information (user name, password, port number, and so on)is stored in the Pentaho Enterprise Repository and is available to other users when they connect to the repository. If you are not using the Pentaho Enterprise Repository, the database connection information is stored in the XML file associated with a transformation or job.
Adding a JDBC Driver
Before you can connect to a data source in any Pentaho server or client tool, you must first install the appropriate database driver. Your database administrator, Chief Intelligence Officer, or IT manager should be able to provide you with the proper driver JAR. If not, you can download a JDBC driver JAR file from your database vendor or driver developer’s Web site. Once you have the JAR, follow the instructions below to copy it to the driver directories for all of the Business Analytics components that need to connect to this data source. See the Compatibility Matrix: Supported Components in any of the Installation guide for current version numbers.
Installing JDBC drivers
Copy the driver JAR file to the following directories, depending on which servers and client tools you are using(Dashboard Designer, ad hoc reporting, and Analyzer are all part of the BA Server):
For the DI Server: before copying a new JDBC driver, ensure that there is not a different version of the
same JAR in the destination directory. If there is, you must remove the old JAR to avoid version conflicts.
BA Server: /pentaho/server/biserver-ee/tomcat/lib/
Enterprise Console: /pentaho/server/enterprise-console/jdbc/
Data Integration Server: /pentaho/server/data-integration-server/tomcat/webapps/pentaho-di/
WEB-INF/lib/
Data Integration client: /pentaho/design-tools/data-integration/libext/JDBC/
Report Designer: /pentaho/design-tools/report-designer/lib/jdbc/
Schema Workbench: /pentaho/design-tools/schema-workbench/drivers/
Aggregation Designer: /pentaho/design-tools/agg-designer/drivers/
Metadata Editor: /pentaho/design-tools/metadata-editor/libext/JDBC/
Restarting
Once the driver JAR is in place, you must restart the server or client tool that you added it to.
Connecting to a Microsoft SQL Server using Integrated Authentication
The JDBC driver supports Type 2 integrated authentication on Windows operating systems through the integrated Security connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to all the directories to which you copied the JDBC files. The sqljdbc_auth.dll files are installed in the following location: \sqljdbc_\\auth\
Conclusion
Here we learnt about the jdbc connection with pentaho
for more info you can click here
