Skip to main content
Pentaho Documentation

Loading Your Data into a Relational Database

Overview

Explains how to load your data into an RDBMS.

After completing Filter Records with Missing Postal Codes, you are ready to take all records exiting the Filter rows step where the POSTALCODE was not null (the true condition), and load them into a database table.

  1. Under the Design tab, expand the contents of the Output node.
  2. Click and drag a Table Output step into your transformation. Create a hop between the Filter Missing Zips and Table Output steps. In the dialog that appears, select Result is TRUE. File:/pdi_table_output_step_true.png
  3. Double-click the Table Output step to open its edit properties dialog box.
  4. Rename your Table Output Step to Write to Database.
  5. Click New next to the Connection field. You must create a connection to the database. The Database Connection dialog box appears.
  6. Provide the settings for connecting to the database.
    Connection Name Sample Data
    Connection Type: H2
    Host Name localhost
    Database Name sampledata
    Port Number 9092
    User Name sa
    Password blank/no password
  7. Click Test to make sure your entries are correct. A success message appears. Click OK.
    Note: If you get an error when testing your connection, ensure that you have provided the correct settings information as described in the table and that the sample database is running. See Starting the Data Integration Server for information about how to start the Data Integration Servers.
  8. Click OK, to exit the Database Connections window.
  9. Type SALES_DATA in the Target Table text field.
  10. Since this table does not exist in the target database, you will need use the software to generate the Data Definition Language (DDL) to create the table and execute it. DDLs are the SQL commands that define the different structures in a database such as CREATE TABLE.
    1. In the Table Output window, enable the Truncate Table property.
    2. Click the SQL button at the bottom of the Table output dialog box to generate the DDL for creating your target table.
    3. The Simple SQL editor window appears with the SQL statements needed to create the table.
    4. Click Execute to execute the SQL statement.  
    5. The Results of the SQL statements window appears.  Examine the results, then click OK to close the Results of the SQL statements window.  
    6. Click Close in the Simple SQL editor window to close it.
    7. Click OK to close the Table output window.
  11. Save your transformation.