"Kettle" 4, SQL Server to SQL Server data Conversion extraction Instance

1. System Version Information

System:windows flagship Service Pack1

Kettle version:6.1.0.1-196

JDK version:1.8.0_72

2. Connect to the database

This instance uses global variables when connecting to the database.

2.1 Create a new transformation: After spoon startup, click CTRL + N to create a new transformation

　　

2.2 In the new conversion interface, right click on the DB connection, the system will pop up the "Database Connection" interface.

　　

In a Windows system environment, you can get the contents of a variable with ${}.

　　

　Description

Connection Name: Configure the data source using the name. ( required)

Host Name: The database host IP address, shown here using a local IP (localhost) address or with 127.0.0.1. ( required)

Database name: DB instance name. ( required)

Port number: The database uses the port number. MS SQL Server default 1433,oracle default 1521. (required)

User name: Log in to the database user. (required)

Password: The login database corresponds to the user's password. (required)

2.3 After the connection is complete, you can see the completed database connection under the DB connection. Right-click Share to generate a Sharp.xml file and configure global variables in the Kettle.properties file.

　　

The generated Sharp.xml file

　　

Part of the Sharp.xml file.

　　

Kettle.properties part of the content.

　　

3. Data Extraction Conversion

Under Core Objects , you can display all the components that can be used in the transformation. Find the table input in "input", double click to appear in the design page, under " output ", find the table output, drag the space into the blank, then click the Shift key, the left button to connect the relationship between the two.

　　

A. Table input: Double-click the table input control, the system will pop up the table input space details.

　　

　　Description

Database connection: The connection under the DB connection, here you can select

SQL: Write query source data SQL script that can be obtained by "Get SQL query statement ..."

Replace the variable in the SQL statement: If you use a variable in the query condition, you need to tick

Insert data from step: Default

Execute each line: default

Record quantity limit: If 0, no data is previewed

Preview: If you can't determine if the SQL statement is working correctly , you can see if the SQL statement is correct by previewing .

B. Table output: Main option

　　

　　Description

Database connection: A connection data source under a DB connection, where you can select

Target table: Data table to be inserted for selecting data

Number of commit records: number of commits per insert

Crop table: If selected, the contents of the target table will be emptied each time and then inserted

Specify Database fields: Table input fields and table output fields you need to select a mapping relationship

Table output: A database field where you can select a mapping relationship for a field.

　　

　　Description

Get field: Automatically gets the mapping of the field

Input field Mapping: the mapping of fields needs to be maintained manually

SQL: Click on SQL will have the option to execute: I have not clicked

Once the table input and the table output are connected, you can click on the triangle in the upper left corner to view the execution log below.

　　

　　

　　

　　Description

The log shows the number of records, reads, writes, inputs, outputs, updates, rejections, errors, activations, times, and speeds performed by each step. You can determine whether the conversion needs to be optimized by analyzing the current conversion run efficiency here.

Summarize:

1 . When you run the transformation, the current transition is in a concurrency state, and if more than one table is entered, it will run concurrently.

2, in the design of the conversion is best a data flow, do not have a conversion of multiple data streams, if there are multiple data flow there is a possibility of a lock table problem.

　

　　

"Kettle" 4, SQL Server to SQL Server data Conversion extraction Instance