How the Kettle tool operates using JavaScript

The Kettle tool is an open source ETL (Extract, Transform, Load) tool that can help data engineers perform data extraction, transformation, loading and other tasks. Kettle not only provides a visual interface, but also uses JavaScript scripts to customize the ETL processing process. Therefore, this article will introduce how the Kettle tool operates using JavaScript.

1. Introduction to Kettle tool

Kettle is a Java-based ETL tool that supports multiple data sources and target data, including relational databases, files, NoSQL databases, etc., and has the following features Features:

1. Visual interface: Users can complete operations such as adding data sources, defining target data, and constructing and executing E-T-L tasks through the interface.
2. Support big data: Kettle uses some memory-efficient technologies to achieve excellent performance when processing large amounts of data or high concurrency.
3. Data quality verification: Kettle has data quality verification and supervision functions, and can conduct large-scale data verification to ensure the timeliness and correctness of the data.

2. How to operate the JavaScript script of the Kettle tool

To operate the JavaScript script in the Kettle tool, you need to follow the following steps:

1. Open the Kettle tool, Create a new transformation or job.
2. Right-click the conversion or job and select "Edit" to enter the editing state.
3. In the editing state, select the step where you need to add JavaScript script, right-click and select "Edit Step".
4. In the pop-up window, select the "Business Intelligence" tab and then select "JavaScript".
5. Just enter the JavaScript script in this window. In the script, the Kettle wizard will provide developers with some common variables and methods, which can be directly called or assigned to simplify operations for developers.

3. Use JavaScript scripts to complete data ETL operations

Kettle's JavaScript script is powerful and can be used to implement complex data ETL processing operations. Below we will introduce how to use JavaScript scripts to complete data ETL operations from three aspects: "data extraction", "data conversion" and "data loading".

1. Data extraction

When implementing data extraction in Kettle, you can use JavaScript scripts combined with the "Table Input" step to complete. The specific steps are as follows:

1) First, create a new transformation, add the "Table Input" step, and connect it to another step;

2) In the editing window of the "Table Input" step , select the "SQL statement query" option and enter the required SQL statement in the text box below;

3) Select the "Business Intelligence" tab, then select "JavaScript" and write JavaScript in the script editing box Script;

4) Use variables and methods in the script as follows:

var row = getRow();
if(row) {
  //在这里输入需要抽取的字段名和数据类型
  var name = row.get("name");
  var age = row.getInteger("age");
  
  //在这里实现数据转换
  age = age * 2;
  
  //在这里输出结果
  var newRow = createRowCopy(row);
  newRow.setValue("new_age", age);
  putRow(newRow);  
} else {
  //表格输入到此结束，结束结果保存到日志中，并返回null终止此步骤。
  logBasic("表格输入完成");
  null;
}

2. Data conversion

When implementing data conversion in Kettle , which can be done using JavaScript scripts combined with "Java Script" or "JDBC" steps. The specific steps are as follows:

1) Create a new transformation and add a "Java Script" or "JDBC" step in it to connect to other steps;

2) Open "Java Script" or " JDBC" step, define the data source and target data in the "Parameters" tab.

3) Select the "Business Intelligence" tab, then select "JavaScript" and write a JavaScript script in the script editing box;

4) Use variables and methods in the script to achieve data conversion , as shown below:

//获取连接
var con = getJDBCConnectionByName("dbConnection");

//查询数据
var rs = con.prepareStatement("SELECT * FROM customer").executeQuery();

//添加查询结果到输出
while(rs.next()) {
  var id = rs.getLong("id");
  var name = rs.getString("name");
  
  //在这里实现数据转换
  var transformedName = name.toUpperCase();   
  
  //在这里输出结果
  var newRow = createRowCopy(row);
  newRow.setValue("id", id);
  newRow.setValue("name", transformedName);
  putRow(newRow);  
}

//关闭连接
rs.close();
con.close();

3. Data loading

When implementing data loading in Kettle, you can use JavaScript scripts to combine the "Table Output" step and "Insert/Update" steps to complete. The specific steps are as follows:

1) Create a new transformation and add the "Table Output" step and the "Insert/Update" step to connect to other steps;

2) Open the "Table Output" step ” step, define the data source information in the “Table Output” tab.

3) Select the "Business Intelligence" tab, then select "JavaScript" and write a JavaScript script in the script editing box;

4) Use variables and methods in the script to load data , as shown below:

//往输出中添加数据
var newRow = getDataRow();
newRow.setValue("name", "马化腾");
newRow.setValue("sex", "男");
newRow.setValue("age", 48);
addRowToOutput(newRow);

//往目标表添加数据
var row = getRow();
if(row) {
  //抽取需要的变量，形式如该脚本实例
  
  //查询表中是否已存在此行数据
  var sql = "SELECT * FROM customer WHERE id='" + id + "'";
  var rs = dbConnection.executeQuery(sql);

  if(rs.next()) {
     //如果存在，就执行更新操作
     var updateSql = "UPDATE customer SET name=?,age=? WHERE id=?";
     var pstmt = dbConnection.getConnection().prepareStatement(updateSql);
     pstmt.setString(1, transformedName);
     pstmt.setInt(2, age);
     pstmt.setLong(3, id);
     pstmt.executeUpdate();
     pstmt.close();
  } else {
     //如果不存在，执行插入操作
     var insertSql = "INSERT INTO customer(id, name, age) VALUES (?, ?, ?)";
     var pstmt = dbConnection.getConnection().prepareStatement(insertSql);
     pstmt.setLong(1, id);
     pstmt.setString(2, transformedName);
     pstmt.setInt(3, age);
     pstmt.executeUpdate();
     pstmt.close();
  }
} else {
  //表格输入到此结束，结束结果保存到日志中。
  logBasic("表格输出完成");
  null;
}

Summary

Kettle tool’s JavaScript script can bring extremely flexible and powerful ETL processing capabilities to developers, and can help developers quickly extract and convert data and loading tasks. In actual work, developers only need to write JavaScript scripts suitable for specific business data processing needs, and then they can efficiently complete the corresponding data ETL work.

The above is the detailed content of How the Kettle tool operates using JavaScript. For more information, please follow other related articles on the PHP Chinese website!