Database Connections
Database connections ..
Workshop - Database Connections
After reviewing the Datamart we're going to create 2 connections:
Connect to sampledata DB with a DB Management Tool
Connect to sampledata with Data Integration
From Pentaho 9.5+ The DBA roles for the users SA & pentaho_admin have been removed for security reasons.
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.
x
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.
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.
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.
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?