简单使用kettle转换经销商信息表（excel数据、帆软报表执行、要注意的问题）

昨天把3个Excel文件用kettle做了转换，最后用计划任务（vbs脚本）调用kettle的kitchen.bat，执行作业。已经将帆软大屏的数据源进行了切换，报表各功能执行顺利。

1、这些Excel数据包括DMS中不存在的管理项目：日常车型分类、时间周期分类、经营目标。

使用帆软报表的文件数据集（Excel文件），有个问题是必需保证日期是yyyy-mm-dd才能与数据库数据集中的日期进行比较。现在改用Kettle将Excel转为数据表，则不存在这个问题（之前用Excel文件而不是建表，是考虑Excel用公式编辑数据的方便）。

这些Excel表起的作用是名称映射，DMS数据库中也有车型描述，但是和平时管理人员使用的名字有区别，比如，舒适版、标准版，我们平时统一叫“舒适/标准版”等等。这一需求也可以用SQL生成映射表（数据库数据集）。

时间名称表：周、月、季、年，用于生成趋势图，因此设计了起始、结束时间。名称表这些可以看成是维度表的另一版本。

2、后期工作规划

因为有了etl这个工具，原来的帆软报表数据源设计，协同etl设计，可以大幅调整优化。考虑投入，暂不优化。这一工作必须要以建立各类型、可扩展的维度表作为起步，并且能够容纳来自市场的数据、工信部公布的数据（集成爬虫所得数据）。这些数据集中在一个数仓，用Apache、Python构建Web服务，向本部门开放智能的查询、汇总查询功能。

3、etl遇到的问题

a) 服务器的时间不一致：

源服务器时间比目标服务器时间慢M分钟，如果etl的间隔比M大，那么会丢失一部分数据；如果比M小，那么除了首次，其他时间不能增加任何数据。后者示例：

此种情况etl对比 源库时间 > metadata时间 增量插入、更新，取到的数据永远是空。解决办法是将metadata的sync_time调小2分钟（目前时间相差不大）。

我通过对DMS源数据库SELECT NOW();，印证了上面的想法。DMS服务器比本地服务器慢了3分钟半；上面调小2分钟还是不够。

b) 源库发货单物理删除了部分发货明细：

即便是批准的发货单，也可能删除，因为DMS管理体系尚未到达成熟阶段。实际上多数企业，无论运营时间多久，似乎永远都在这个阶段。

DMS数据库既然真有物理删除的设计，那就真的无可奈何了。解决这一问题的办法我曾考虑：在午夜额外执行一次作业：删除所有目标表，如此可保证第二天的数据肯定正确，当天（白天）的数据可能会多那么一点。

但是更好使用“数据同步”这一转换，而不是“插入/更新”。一个逻辑漏洞在此出现，导致多花费了一个半小时的付出：既然使用所谓增量同步的办法，那么每次从源表抽取很少的数据与目标表同步，必然导致目标表的既有数据多数被删除。如果每次从目标表也抽取相同时间戳之后的数据，那么同步的范围仅仅限于这一小段时间。因此“增量同步”的概念就是错误的，只能是全量同步，增量插入/更新。因此，对于数据库（表）的开发人来说，务必设置记录的updated_time，并且设置valid字段表示数据的有效性（有效、过期/下架/作废、删除等）。| 后记：这基于入侵式、直连ETL，这些场景。2021-12-21

下面是当前设计各vbs脚本

REM 计划任务kettle_run调用 每天7:30开始执行，5分钟重复1次，12小时后结束

Dim WS              'Wscript.Shell
Dim FSO             '文件对象
Dim Kitchen         'kitchen.bat
Dim currdir         '本脚本路径
Dim kettle_job      '要运行的kettle job和日志
Dim log_time        '日志时间，用于创新新的log文件名称
Dim log_name        '新的log文件名称

Set WS = CreateObject("Wscript.Shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
Kitchen = "C:\pdi-ce-8.2.0.0-342\data-integration\kitchen.bat"
currdir = FSO.GetFile(Wscript.ScriptFullName).ParentFolder.Path
kettle_job = Kitchen & " /file " & currdir & "\job_dms2fr.kjb /level Basic /logfile " & currdir & "\logs\log.txt"
If WS.Run(kettle_job, 0, TRUE) = 0 Then
    '程序执行后，如果log.txt超过5M，将其修改为历史文件
    '但是 ketchen.bat 似乎什么也不返回，即便运行错误；所以Run始终返回0
    If FSO.GetFile(currdir & "\logs\log.txt").size > 5*1024*1024 Then
        log_time = now()
        log_name = right(year(log_time),2) & right("0" & month(log_time),2) & right("0" & day(log_time),2)
        log_name = log_name & right(hour(log_time),2) & right("0" & minute(log_time),2) & right("0" & second(log_time),2) & ".txt"
        FSO.MoveFile currdir & "\logs\log.txt", currdir & "\logs\" & log_name
    End If
End If

Set FSO = Nothing
Set WS = Nothing

WScript.Quit