# Database Connections

{% hint style="warning" %}

#### Workshop - Database connections

Create a reusable MySQL connection to the Steel Wheels `sampledata` database.

**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
{% endhint %}

{% embed url="<https://www.loom.com/share/3ec2d5123814460d92085851c18daaee?hideEmbedTopBar=true&hide_owner=true&hide_share=true&hide_title=true>" %}
Database Connection
{% endembed %}

***

{% hint style="info" %}
**Workshop files**

Download the following file.

Keep the filename unchanged.

Save it in your workshop folder.
{% endhint %}

{% file src="/files/3ILjuwjLO9JaUpUsPHpq" %}

***

{% hint style="info" %}

### Create a new transformation

Use any of these options to open a new transformation tab:

* Select **File** > **New** > **Transformation**
* Use `Ctrl+N` (Windows/Linux) or `Cmd+N` (macOS)
  {% endhint %}

***

{% tabs %}
{% tab title="1. DBeaver" %}
{% hint style="info" %}

#### **DBeaver**

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.
{% endhint %}

{% hint style="warning" %}
Pentaho no longer ships a writable sample database user by default.\
Use the Docker `sampledata` setup for hands-on database workshops.
{% endhint %}

{% tabs %}
{% tab title="MySQL" %}
{% hint style="info" %}

#### **MySQL Database**

If you completed the [Setup](broken://spaces/ZpCSy6Skj215f4oWypdc/pages/nyuNuY53XaIkQsM4MkZX), you should have a MySQL Docker container.\
It should be exposed on port `3306` and include the `sampledata` database.
{% endhint %}

1. Launch DBeaver and select **MySQL**.

<figure><img src="/files/f04HxAgnFLcJJfT7g1Kc" alt=""><figcaption><p>MySQL</p></figcaption></figure>

2. Configure the connection:

* **Username:** `root`
* **Password:** `password`

<figure><img src="/files/YlrpU0j9USa8MQwptL9F" alt=""><figcaption><p>Configure &#x26; Test MySQL connection - sampledata</p></figcaption></figure>

{% hint style="warning" %}
You might need to download the supported driver version.

If the test fails, enable `allowPublicKeyRetrieval`.
{% endhint %}

<figure><img src="/files/3nu7BbPVJSa69szd9xkW" alt=""><figcaption><p>Enable: allowPublicKeyRetrieval</p></figcaption></figure>

3. Test the connection.

<figure><img src="/files/zi3B824EcsskmFjDo7Am" alt=""><figcaption><p>Test connection</p></figcaption></figure>

4. Expand **Databases** > **sampledata** > **Tables**.

<figure><img src="/files/IpfBGicoVihojG7VvsCo" alt=""><figcaption><p>Customer Data</p></figcaption></figure>

5. Open a SQL window and run a test query:

```sql
select * from CUSTOMERS
where COUNTRY = 'USA' and CITY = 'NYC';
```

<figure><img src="/files/5B25msWIcQHRygRLGOTv" alt=""><figcaption><p>Sql query - NYC Customers</p></figcaption></figure>

{% hint style="success" %}
Checkpoint: you can browse tables and run a query against `CUSTOMERS`.
{% endhint %}
{% endtab %}
{% endtabs %}
{% endtab %}

{% tab title="2. JDBC Driver" %}
{% hint style="info" %}

#### **Download JDBC Driver**

PDI does not ship all JDBC drivers.\
If your database type is missing, add the driver JAR.
{% endhint %}

{% embed url="<https://docs.pentaho.com/install/jdbc-drivers-reference>" %}

1. Download the JDBC driver for your database.

{% embed url="<https://dbschema.com/databases.html>" %}

2. Copy the driver JAR into your PDI install:

{% tabs %}
{% tab title="Windows" %}
`C:\Pentaho\design-tools\data-integration\lib\`
{% endtab %}

{% tab title="macOS / Linux" %}
`~/Pentaho/design-tools/data-integration/lib/`
{% endtab %}
{% endtabs %}

3. Restart Spoon.

{% hint style="info" %}
If your install uses `lib/jdbc/`, place the JAR there instead.
{% endhint %}
{% endtab %}

{% tab title="3. Data Integration" %}
{% hint style="info" %}

#### **Pentaho Data Integration Connection**

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).
{% endhint %}

**Define a database connection (MySQL)**

1. Create a transformation.
2. In Spoon, select **File** > **New** > **Database connection**.

The **Database connection** dialog opens.

<figure><img src="/files/nfLYAjgnNcRbRwjO96lX" alt=""><figcaption></figcaption></figure>

3. Enter the following details:

{% hint style="danger" %}
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.
{% endhint %}

* **Connection name:** `MySQL: sampledata`
* **Connection type:** **MySQL**
* **Access:** **Native (JDBC)**
* **Host name:** `localhost` (or your Docker host IP)
* **Database name:** `sampledata`
* **Username:** `pentaho_admin`
* **Password:** `password`

<figure><img src="/files/QyouGLeHqt30IXT0SXd8" alt=""><figcaption><p>MySQL - sampledata</p></figcaption></figure>

4. Select **Test**.

{% hint style="info" %}
Checkpoint: Spoon shows a success message.
{% endhint %}

{% tabs %}
{% tab title="1. Share Connection" %}
{% hint style="info" %}
**Share Database Connection**

Share the connection so other transformations can reuse it.
{% endhint %}

1. Click OK to save your entries and exit the Database Connection dialog box.
2. From within the View tab, right-click on the connection and select Share from the list that appears.

<figure><img src="/files/McZz5Iv5N64RmHHYT76Z" alt=""><figcaption><p>Share database connection</p></figcaption></figure>

{% hint style="info" %}
Shared connections show up for other users and projects.\
Use **Explore** to confirm schemas and tables.
{% endhint %}
{% endtab %}

{% tab title="2. Explore Database" %}
{% hint style="info" %}
**Explore Database**

Use **Database Explorer** to browse schemas, preview rows, and run SQL.
{% endhint %}

1. Click on the View tab, expand Database Connections.
2. Right-click MySQL:sampledata and choose Explore from the menu options:

|                                   | Action                                                                |
| --------------------------------- | --------------------------------------------------------------------- |
| 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                             |

3. In the Database Explorer window, expand Sampledata > Tables

<figure><img src="/files/oOLOxwr8SdpjHhn62BG8" alt="" width="375"><figcaption><p>Database Explorer - sampledata</p></figcaption></figure>

4. Right-click the `CUSTOMERS` table and choose **Preview first 100**.
5. Examine the customer data.
6. Select **View SQL**.

<figure><img src="/files/1z3WddQJPibQc3251vHf" alt="" width="375"><figcaption><p>SQL</p></figcaption></figure>

7. Click Execute.

<figure><img src="/files/6qB4vJCoFdwQd8x7OxBi" alt=""><figcaption><p>Execute SQL statement</p></figcaption></figure>

{% hint style="success" %}
Checkpoint: you can preview `CUSTOMERS` and execute SQL in Database Explorer.
{% endhint %}
{% endtab %}
{% endtabs %}
{% endtab %}
{% endtabs %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.pentaho.com/pentaho-data-integration/data-integration/data-sources/databases/cruid/database-connections.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.