【Kettle】—— Kettle遍历执行SQL文件

更新日志：

• [2019-07-26] 修正步骤 3 中错误词 “添加” 为 “增加”
• [2019-07-19] 文章发布

说明：

• Kettle 版本
  • Version 8.2
• 本文地址
  • 《【Kettle】—— Kettle 遍历执行SQL文件》
  • https://blog.csdn.net/maixiaochai/article/details/96483519
• 参考文献：
  • 《Kettle作业步骤循环执行的实现教程》
  • 《ETL工具kettle实现循环》
• Create by MaiXiaochai
  CSDN: https://blog.csdn.net/maixiaochai
  GitHub: https://github.com/MaiXiaochai

---

1. 简述

• 最终效果图
  
• 需求背景
  需要将写好的 200 多个 sql 文件部署到Kettle，若一个个写 转换，复制粘贴执行SQL，
  工作量较大。而且不便于后期的维护工作（比如，新增了100个sql文件等等）。
  所以，就有了用Kettle遍历执行目录下所有sql文件的需求。
  
  

2. 测试用例准备

• 我们只谈技术，不谈秘密。这里用测试用例。
  这里用两个同步数据的任务，来代表遍历执行多个sql文件的过程，
  若只用一个任务，不具有代表性，很可能只用一个测试很成功，但是两个就频繁出错。
  我在完成这项工作的时候就遇到了这种情况。
  
  
• 有四张表，TEST_A、TEST_A2、TEST_B、TEST_B2，
  这四张表结构都一样，
  这样理解比较好一些——TEST_A 和 TEST_A2结构一样，TEST_B 和 TEST_B2 结构一样，
  表结构如下图
  
• 其中TEST_A 和 TEST_B 为 数据源表（有数据），
  TEST_A2 和 TEST_B2 为目标表（空表，没有数据），
  按照以下关系将源表数据 MERGE INTO到目标表：
  • TEST_A —> TEST_A2
  • TEST_B —> TEST_B2
    
    
• 源表数据：
  • TEST_A
    
  • TEST_B
    
• 目标表初始状态
  • TEST_A2
    
  • TEST_B2
    
• sql 脚本及内容
  • test_a.sql

    MERGE INTO TEST_A2 T1 USING ( SELECT * FROM TEST_A ) S1 ON ( T1.MY_ID = S1.MY_ID ) 
    WHEN MATCHED THEN
    UPDATE 
    	SET T1.MY_NAME = S1.MY_NAME 
    	WHEN NOT MATCHED THEN
    	INSERT ( MY_ID, MY_NAME )
    VALUES
    	( S1.MY_ID, S1.MY_NAME );
    commit;
    

  • test_b.sql

    MERGE INTO TEST_B2 T1 USING ( SELECT * FROM TEST_B ) S1 ON ( T1.MY_ID = S1.MY_ID ) 
    WHEN MATCHED THEN
    UPDATE 
    	SET T1.MY_NAME = S1.MY_NAME 
    	WHEN NOT MATCHED THEN
    	INSERT ( MY_ID, MY_NAME )
    VALUES
    	( S1.MY_ID, S1.MY_NAME );
    commit;
    

3. Kettle 流程编写

• 新建 “转换” test_get_names.ktr，用来获取所有SQL文件路径
  • get_merge_test_files
    该转换的作用是将所有.sql文件路径保存到结果（可被其他转换使用）
    • 插件位置：输入 - 获取文件名
      
    • 填写如下内容
      • 文件目录: sql文件所在的目录
      • 正则表达式:.*\.sql(匹配所有.sql 路径)
      • 然后点增加
      • 之后 已经选择的文件名称 会出现刚刚配置的规则，点击 预览记录，
        如果出正确的 SQL文件路径，说明配置正确。
        
  • 字段选择
    • 插件位置：转换 - 字段选择
    • 点击获取选择的字段，然后只保留filename字段
      
  • 复制记录到结果
    • 插件位置：作业 - 复制记录到结果
      
      
• 新建作业 public_execute_sql_file.kjb，用来执行SQL文件
  • SQL
    
    • 插件位置：脚本 - SQL
    • 数据库连接：自己配置好的数据库连接
    • 从文件得到的SQL：√
    • SQL文件名：${this_path}(这里是调用了全局变量，这个变量是其他转换或作业设置的)
      
      
• 新建作业 test
  

  • 获取merge文件名称并保存到结果

    • 双击然后添写 test_get_name.ktr转换的路径
      

  • merge_sql脚本数量判断与设置变量

    • 插件位置：脚本 - JavaScript
    • JavaScript 内容
      
      注意 SQL内容，注意检查拼写错误

      var preRows = previous_result.getRows();//获取结果中的文件路径
      if (preRows == null && (preRows.size() == 0)){ 
      //↑ 判断结果中是否有路径数据，若没有，返回false，转换执行报错。
      	false;
      }else{
      	parent_job.setVariable("merge_paths", preRows); 
      	//↑ 将结果中的路径给变量 merge_paths,[[sql_file1], [sql_file2], ..., [sql_filen]]
      	parent_job.setVariable("merge_size", preRows.size());//获取SQL路径数量
      	parent_job.setVariable("i", 0);//设置循环中的 i为 0
      	parent_job.setVariable("this_path", preRows.get(0).getString("filename", ""));
      	//↑ 赋值结果中的第一条结果的 filename 字段的值（也就是第一个SQL文件具体的路径）给this_path变量
      	true;
      }
      

  • 检测字段的值

    • 插件位置：条件 - 检测字段的值
      
    • 检验：变量
    • 变量名：${i}（循环中的 i，之前JavaScript 已经设置，初始值为 0）
    • 成功条件：如果值小于
    • 值：${merge_size}（sql文件路径总数，之前JavaScript 已经设置）
      
      

  • 作业

    • 插件位置：通用 - 作业
      • 双击打开，填写public_execute_sql_file.kjb路径
        

  • 循环 i++和 this_path 改变

    • 插件位置：脚本 - javaScript
      

    var list_tables = parent_job.getVariable("merge_paths").replace("[","").replace("]", "").split(",");
    /*↑ 这里获取merge_paths变量，类型为String，所以要处理一下，
    由 "[[sql_file1], [sql_file2]" 变为 ['sql_file1', 'sql_file2'] */
    var merge_size = new Number(parent_job.getVariable("merge_size"));
    //↑ 同样，sq文件路径数量的大小也是字符串，重新处理成数字
    var i = new Number(parent_job.getVariable("i")) + 1;
    // ↑ 同上，i 的值处理成 数字，并 +1
    if (i < merge_size){
    	parent_job.setVariable('this_path', list_tables[i].replace(" ", ""));
    	/* ↑ 这里是取下一个SQL文件路径，注意最后的 replace(" ", "")，
    	经过上面处理后的路径，最前面会有空格，这会导致 执行SQL文件的作业报“找不到文件”的错误，
    	我在这卡了一段时间，后来发现是路径前有空格的问题。
    	我的文件路径中没有类似"Program files"这种中间有空格的路径，所以，可以用replace，
    	若你的中间路径有空格，请用其它方法去掉路径开头的空格
    	*/
    }
    parent_job.setVariable("i", i);
    //↑ 设置新的 i 值给 i 变量
    true;
    

  • 成功

    • 插件位置：通用 - 成功

  • 注意各个步骤之间的逻辑关系，即，下个步骤是在上个步骤哪种 布尔结果下执行的

4. 运行

• 运行：点击 test 作业的运行按钮
• 结果
  • Kettle 结果
    
  • TEST_A2 结果
    
  • TEST_B2 结果
    
• 文章主体目的成功 √
  
  

5. 总体逻辑

• Python 伪代码描述 - 详细版

  preRows = get_sql_paths()
  merge_size = len(preRows)
  
  if (not preRows and merge == 0):
  	return False
  
  i = 0
  this_path = preRows[i]
  list_tables = str_to_list(preRows)
  
  for j in range(merge_size):
  	if i != 0:
  		this_path = list_tables[i]
  	job_public_execute_sql_file(this_path)
  	i ++
  

• Python 伪代码描述 - 简化版

  for i in list_tables:
  	job_execute_sql_file(i)
  

The end.