Mysql--Pentaho how to export the data from MySQL and import into HANA Database

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.

 下载地址参照：（30天试用）http://www.pentaho.com/download

Before Start

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.

http://dev.mysql.com/downloads/connector/j/

 

2. Hana DB: ngdbc.jar

You could find this file in the directory of you SAP HANA Studio:
\%HANA Studio%\configuration\org.eclipse.osgi\bundles\2000\1\.cp\lib

 

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.

 

Create Inputs

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.

Create “Table Output” for HANA Database

1. Drag and drop the Table Output component.

2. Create a new database connection for HANA Database. Currently, there is no specific connection type for HDB, so we could use generic JDBC connection.

3. Test the connection configuration.

4. Set the Table Output configuration. We use the table “ORDER_INFO_KETTLE” for data destination.

 

Use “Select values” to map the data structure of Input and Output.

1. Drag and drop the Select Values component to the design area.

2. Link the Select Values component with the Stream Lookup.

3. Link the Select Values with Table Output.

4. Double click the Select Values component to open the property window. Click “Get fields to select” button to get the fields info from the Stream Lookup.

5. Click the “Edit Mapping” button to map the fields with the Table Output component.

6. Click “OK”.

7. Let the system to “Guess” the mapping.

8. Not smart enough.

9. We made the adjustment manually and click “OK” to close the property window.

 

Run the transformation

1. Click “Run” button in the design area.

2. The execution window pops up. Clicks the “launch” button directly.

3. After no time, the execution completed.

4. This is the result.

 

<<over>>

 

附录：Pentaho的一个视频地址

https://blogs.sap.com/2014/02/28/video-load-data-to-sap-hana-db-with-pentaho/

 

Here is a list of examples included in the original cookbook.

· Use Talend to Sync Data from MySQL to SAP HANA (Part I)

· Use Talend to Sync Data from MySQL to SAP HANA (Part II)

· Sync Data from My SQL to HANA DB with Pentaho (Part I)

· Sync Data from My SQL to HANA DB with Pentaho (Part II)

· Experiences with SAP HANA Geo-Spatial Features – Part 1

· Experiences with SAP HANA Geo-Spatial Features – Part 2

· Ad-hoc Analysis Comparison with MYSQL & SAP HANA One

· Ad-hoc Analysis Comparison with MYSQL & SAP HANA One - Continued

· Real-time sentiment rating of movies on SAP HANA One

· How we built a Movie Recommendation engine by leveraging R on SAP HANA