Kettle Tutorial (1) Easy to get started, kettle simple to insert and update. Open Kettle

This article points to: Kettle to establish a database connection, using kettle for a simple full-amount comparison insert update: Kettle will automatically compare the user settings of the comparison field, if the target table does not exist in the field, the new insert record. If it exists, it is updated.

Kettle Introduction: Kettle is a foreign open source ETL tools, pure Java writing, can be run on Windows, Linux, UNIX, data extraction efficient and stable. Kettle Chinese name is the kettle, the project's main programmer, Matt, wants to put all kinds of data into a pot and then flow out in a specified format. Kettle This ETL toolset, which allows you to manage data from different databases by providing a graphical user environment to describe what you want to do, rather than what you want to do. There are two script files in Kettle, transformation and job,transformation complete the fundamental transformation of the data, and the job completes the control of the entire workflow. (quoted Baidu Encyclopedia)

1. Download and installation of kettle (the kettle version used in this article is 6.1.0.1-196)

Kettle's Latest: http://community.pentaho.com/projects/data-integration/

2, open the kettle.

First unzip the downloaded compressed package such as: Pdi-ce-6.1.0.1-196.zip

Then open the Spoon.bat:

　　

Please wait patiently for a little while after opening.

3, establish the conversion.

New reload in file.

The new conversion establishes a DB connection in the left main object tree to connect to the database. ：

　　

The process of establishing a database connection is similar to other database management software connection databases.

　Note: During a database link, an exception could be reported that a database connection could not be found. That is because you do not have the corresponding database link driver, please download the corresponding driver, put in the Kettle Lib folder.

4. Simple data table insert \ Update

(1) New Table Insert

Select "Core Object" in the left panel, select "Input-to-table input" in the core object and drag to the right panel with your mouse. ：

Double-click the table you dragged over to edit the table input.

Select the database connection and edit the SQL statement, and in this step you can click Preview to see if you are connected correctly.

(2) Output to table by inserting \ Update.

Select the core object in the left panel and select output, insert \ Update:

Edit Insert Update:

First: The table input connection inserts the update.

Select the table input, hold down the SHIFT key, and drag to insert the update.

Then: Double-click Insert Update to edit it.

Here basically, this conversion will be done, you can click to run to see the effect, to see if it is wrong, this need to save the first to run, you can freely save to any place.

5, the use of job control above the load exchange execution.

A job can be used to perform conversions on a regular or periodic basis, creating a new job. And drag from the left panel into start and convert.

　　

Double-click Start to edit, you can set the execution time and so on

Click to switch, you can set the need to perform the conversion tasks, such as can do the above we do the conversion, XXX.KTR

Last click to run.

　　Here, a simple kettle through the job scheduling, even if done.

Appendix: Test Data Sheet

1------------------------------2 DROP TABLE IF EXISTS ' student '; 3 CREATE TABLE ' Student ' (4   ' userId ' int (one) not NULL auto_increment, 5   ' userName ' varchar () DEFAULT NULL, 6
    ' userage ' int (one) default NULL, 7   ' timestamp ' timestamp NOT null default Current_timestamp on UPDATE current_timesta MP, 8   PRIMARY KEY (' userId ') 9) Engine=innodb auto_increment=6 DEFAULT charset=utf8;10 one---------------------------  -----table structure for student213------------------------------DROP table IF EXISTS ' student2 '; CREATE table ' Student2 ' (+   ' userId ' int (one) not NULL auto_increment,17   ' userName ' varchar ') DEFAULT null,18   ' userage ' Int (one) default null,19   ' timestamp ' timestamp not NULL default ' 0000-00-00 00:00:00 ' on UPDATE current_timestamp,20< C9/>primary KEY (' userId ') engine=innodb auto_increment=6 DEFAULT Charset=utf8;

The

Kettle Tutorial (1) is simple to get started, kettle simple to insert and update. Open Kettle