Sync Data from MySQL to HANA DB with Pentaho (Part I)
This document is for the introduction of the basic function of Pentaho and how to export the data from MySQL and import into HANA Database. Here is Part I.
You need to copy the jar files of database connector for both MySQL and HANA DB to the library folder of Pantaho before we start this ETL tool. Here is the list:
1. MySQL: mysql-connector-java-5.1.28-bin.jar
You may download the file from MySQL official website.
2. Hana DB: ngdbc.jar
You could find this file in the directory of you SAP HANA Studio:
3. Target folder: \%pentaho%\design-tools\data-integration\lib
Open Pentaho and create a transformation
1. Open “Data Integration”.
2. Click “Cancel”.
3. Now the “Spoon” is open.
4. Create a new Transformation.
5. Now you could drag and drop the components from Design panel.
1. Drag and Drop “Table input” component to the design panel.
2. Double click the icon on the design panel and the property window pops up. Click the “New” button to create a new database connection.
3. We first create a MySQL connection. Click the “Test” button to verify the configuration.
4. It seems to work.
5. Go back to the Table Input property window and Click the “Get SQL select statement…” button.
6. Choose the “customers” table.
7. Click “OK” and you may choose whether include the field’s name in the SELECT statement.
8. Click “OK” button and the Table Input for “customers” is ready.
9. We create another Table Input for “orders” table in MySQL.
Use “Stream Lookup” for fields-mapping
1. Drag and drop “Stream Lookup” component from the Design Panel.
2. Hold down the “Shift” key and draw a line between the Table Input and Stream Lookup.
3. Double click the Steam Lookup and set the configuration. This is for the mapping of Customer Name. Click “OK” button to finish the configuration.