job
1.job日志
job日志有三种:
作业日志表:整体的记录
作业项日志表:每个项目的日志
日志通道日志表:
第一次配置,因为数据库中还没有这个表,需要手动点击sql创建表,然后执行sql。
日志模式:oracle等数据库种有模式概念,mysql不需要填写
记录时间间隔:运行的时候多久写一次,在作业日志表中对应:logdate时间每10秒写一次
日志超时时间:过期删除?
在内存中保存的日志行数:
2.job内转换日志(这个没有打印出来)
trans
1.trans日志
转换:启动、结束时间、运行状态(正在运行,完成)
步骤:运行的步骤信息
运行:
日志通道:
Metrics:运行具体的步骤(度量)
==========================kettle日志====================
检验分流案例:
kettle文件:job文件:
trans文件:
具体内容,表输入为2张表join操作:
数据检验为对合并的数据做一个过滤操作,如为空,数值等,并定义错误处理(右键->错误处理):
js代码为合并错误的记录行以“|”拼接成一个字段,存入一张大表,大表记录数据来源:
待存入的库表结构:(实际项目中肯定得知道从哪台机器,哪个库表来的数据)
CREATE TABLE `p_error` ( `result` VARCHAR(256) NULL DEFAULT NULL, `error_num` VARCHAR(64) NULL DEFAULT NULL, `error_desc` VARCHAR(64) NULL DEFAULT NULL, `error_column` VARCHAR(64) NULL DEFAULT NULL, `error_code` VARCHAR(64) NULL DEFAULT NULL ) |
输出效果:
检验不通过分流输出:
kettle执行 情况:job、trans均正常执行。
日志记录输出情况:
作业:(有颜色的表示可以使用的字段)
作业日志表:(作业执行具体情况,执行状态、启动时间、当前日志记录时间、结束时间)
ID_JOB记录当前执行批次,后面所有表有关批次,全是该字段索引。
ID_JOB | 0 |
CHANNEL_ID | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 |
JOBNAME | 读取 |
STATUS | end |
LINES_READ | 0 |
LINES_WRITTEN | 0 |
LINES_UPDATED | 0 |
LINES_INPUT | 0 |
LINES_OUTPUT | 0 |
LINES_REJECTED | 0 |
ERRORS | 0 |
STARTDATE | 1/1/1900 07:00:00 |
ENDDATE | 6/14/2018 17:13:54 |
LOGDATE | 6/14/2018 17:13:55 |
DEPDATE | 6/14/2018 17:13:54 |
REPLAYDATE | 6/14/2018 17:13:54 |
LOG_FIELD |
log_field根据配置的时间间隔实时刷新,内容和kettle底部展示一致:
2018/06/14 17:13:54 - Spoon - 正在开始任务... 2018/06/14 17:13:54 - 读取 - 开始执行任务 2018/06/14 17:13:54 - 读取 - 开始项[转换] 2018/06/14 17:13:54 - 转换 - Loading transformation from XML file [D:\etl_project\错误处理.ktr] 2018/06/14 17:13:54 - 转换 - Using run configuration [Pentaho local] 2018/06/14 17:13:54 - 转换 - Using legacy execution engine 2018/06/14 17:13:54 - 错误处理 - 为了转换解除补丁开始 [错误处理] 2018/06/14 17:13:54 - 正确记录输出.0 - Connected to database [local_zj_ctcc] (commit=1000) 2018/06/14 17:13:54 - 过滤记录输出 2.0 - Connected to database [local_zj_ctcc] (commit=1000) 2018/06/14 17:13:54 - 过滤记录输出.0 - Connected to database [local_zj_ctcc] (commit=1000) 2018/06/14 17:13:54 - 表输入.0 - Finished reading query, closing connection. 2018/06/14 17:13:54 - JavaScript代码.0 - Optimization level set to 9. 2018/06/14 17:13:54 - 表输入.0 - 完成处理 (I=17, O=0, R=0, W=17, U=0, E=0) 2018/06/14 17:13:55 - 数据检验.0 - 完成处理 (I=0, O=0, R=17, W=12, U=0, E=5) 2018/06/14 17:13:55 - JavaScript代码.0 - 完成处理 (I=0, O=0, R=5, W=5, U=0, E=0) 2018/06/14 17:13:55 - 正确记录输出.0 - 完成处理 (I=0, O=12, R=12, W=12, U=0, E=0) 2018/06/14 17:13:55 - 过滤记录输出.0 - 完成处理 (I=0, O=5, R=5, W=5, U=0, E=0) 2018/06/14 17:13:55 - 读取 - 开始项[成功] 2018/06/14 17:13:55 - 读取 - 完成作业项[成功] (结果=[true]) 2018/06/14 17:13:55 - 读取 - 完成作业项[转换] (结果=[true]) 2018/06/14 17:13:55 - 读取 - 任务执行完毕 2018/06/14 17:13:55 - Spoon - 任务已经结束. END |
作业项日志表:
ID_BATCH | CHANNEL_ID | LOG_DATE | TRANSNAME | STEPNAME | LINES_READ | LINES_WRITTEN | LINES_UPDATED | LINES_INPUT | LINES_OUTPUT | LINES_REJECTED | ERRORS | RESULT | NR_RESULT_ROWS | NR_RESULT_FILES | |
0 | 155652e6-de7d-4ccc-93d6-738b46498211 | 6/14/2018 17:13:55 | 读取 | START | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | |
0 | 58c148e4-68a8-4276-a291-3bb004a97a8e | 6/14/2018 17:13:55 | 读取 | 转换 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | |
0 | eb553cb2-5db9-4987-9cf6-3089f6813e10 | 6/14/2018 17:13:55 | 读取 | 成功 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | |
0 | b94f8743-7831-4bae-841c-ea871d5a8c86 | 6/14/2018 17:13:55 | 读取 | DUMMY | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
日志通道日志表:
ID_BATCH | CHANNEL_ID | LOG_DATE | LOGGING_OBJECT_TYPE | OBJECT_NAME | OBJECT_COPY | REPOSITORY_DIRECTORY | FILENAME | OBJECT_ID | OBJECT_REVISION | PARENT_CHANNEL_ID | ROOT_CHANNEL_ID | ||
0 | 754bfd54-3a2e-4c65-867c-bb194fc4ebf4 | 6/14/2018 17:13:55 | DATABASE | local_kettle_log | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | c3ed94e2-c2ad-44b7-8e2e-6aadbe297f33 | 6/14/2018 17:13:55 | JOBENTRY | START | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | c88e0e5a-8dab-44ff-bbaa-2eefe2fb4dad | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | d577d451-2c6e-4fe5-b044-3f6db889cfeb | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | d577d451-2c6e-4fe5-b044-3f6db889cfeb | 6/14/2018 17:13:55 | STEP | 表输入 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | 3f5df8a8-525d-4fda-9656-47ec84eabad4 | 6/14/2018 17:13:55 | STEP | 数据检验 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | c1416a32-a719-4e49-b4ad-7405483a8c7e | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | d2e88ac3-2af0-4e97-b142-32e4bd75dadc | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | d2e88ac3-2af0-4e97-b142-32e4bd75dadc | 6/14/2018 17:13:55 | STEP | 正确记录输出 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | 60af517e-5f27-4c7a-96f9-c47fcb889ce7 | 6/14/2018 17:13:55 | STEP | JavaScript代码 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | bed78c9a-4a9c-4862-9b34-3ad3d5eb3fde | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | 89cb570a-1e3a-4f6e-b909-30d7bb31fbe6 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | 89cb570a-1e3a-4f6e-b909-30d7bb31fbe6 | 6/14/2018 17:13:55 | STEP | 过滤记录输出 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | c75912a5-d9dd-4adf-ae22-b69edf1ddbfb | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | 6a38328a-af09-49ac-b9e8-5adc5346e821 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | 6a38328a-af09-49ac-b9e8-5adc5346e821 | 6/14/2018 17:13:55 | STEP | 过滤记录输出 2 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | 1b40927e-a18a-459b-a7d3-009195b4ed3a | 6/14/2018 17:13:55 | DATABASE | local_kettle_log | 99922df2-8f34-4865-bdb9-6d571895eae4 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 6/14/2018 17:13:55 | TRANS | 错误处理 | / | D:\etl_project\错误处理.ktr | 3b5dff4f-0a52-41de-aac6-e5427f3058c7 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||
0 | 3b5dff4f-0a52-41de-aac6-e5427f3058c7 | 6/14/2018 17:13:55 | JOBENTRY | 转换 | D:\etl_project\错误处理.ktr | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | ||||||
0 | 8be87f8a-b464-4a1e-8722-3def49883517 | 6/14/2018 17:13:55 | JOBENTRY | 成功 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | |||||||
0 | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 | 6/14/2018 17:13:55 | JOB | 读取 | / | D:\etl_project\日志测试_错误处理.kjb | 2b97ef82-9ef0-4312-b563-2fa67ec55a21 |
转换:
转换:
ID_BATCH | 0 |
CHANNEL_ID | 99922df2-8f34-4865-bdb9-6d571895eae4 |
TRANSNAME | 错误处理 |
STATUS | end |
LINES_READ | 0 |
LINES_WRITTEN | 0 |
LINES_UPDATED | 0 |
LINES_INPUT | 0 |
LINES_OUTPUT | 0 |
LINES_REJECTED | 0 |
ERRORS | 0 |
STARTDATE | 1/1/1900 07:00:00 |
ENDDATE | 6/14/2018 17:13:54 |
LOGDATE | 6/14/2018 17:13:55 |
DEPDATE | 6/14/2018 17:13:54 |
REPLAYDATE | 6/14/2018 17:13:54 |
LOG_FIELD |
LOG_FIELD字段内容:
2018/06/14 17:13:54 - Spoon - 正在开始任务... 2018/06/14 17:13:54 - 错误处理 - 为了转换解除补丁开始 [错误处理] 2018/06/14 17:13:54 - 正确记录输出.0 - Connected to database [local_zj_ctcc] (commit=1000) 2018/06/14 17:13:54 - 过滤记录输出 2.0 - Connected to database [local_zj_ctcc] (commit=1000) 2018/06/14 17:13:54 - 过滤记录输出.0 - Connected to database [local_zj_ctcc] (commit=1000) 2018/06/14 17:13:54 - 表输入.0 - Finished reading query, closing connection. 2018/06/14 17:13:54 - JavaScript代码.0 - Optimization level set to 9. 2018/06/14 17:13:54 - 表输入.0 - 完成处理 (I=17, O=0, R=0, W=17, U=0, E=0) 2018/06/14 17:13:55 - 数据检验.0 - 完成处理 (I=0, O=0, R=17, W=12, U=0, E=5) 2018/06/14 17:13:55 - JavaScript代码.0 - 完成处理 (I=0, O=0, R=5, W=5, U=0, E=0) 2018/06/14 17:13:55 - 正确记录输出.0 - 完成处理 (I=0, O=12, R=12, W=12, U=0, E=0) 2018/06/14 17:13:55 - 过滤记录输出.0 - 完成处理 (I=0, O=5, R=5, W=5, U=0, E=0) END |
步骤:
ID_BATCH | CHANNEL_ID | LOG_DATE | TRANSNAME | STEPNAME | STEP_COPY | LINES_READ | LINES_WRITTEN | LINES_UPDATED | LINES_INPUT | LINES_OUTPUT | LINES_REJECTED | ERRORS |
0 | d577d451-2c6e-4fe5-b044-3f6db889cfeb | 6/14/2018 17:13:55 | 错误处理 | 表输入 | 0 | 0 | 17 | 0 | 17 | 0 | 0 | 0 |
0 | 3f5df8a8-525d-4fda-9656-47ec84eabad4 | 6/14/2018 17:13:55 | 错误处理 | 数据检验 | 0 | 17 | 12 | 0 | 0 | 0 | 5 | 0 |
0 | d2e88ac3-2af0-4e97-b142-32e4bd75dadc | 6/14/2018 17:13:55 | 错误处理 | 正确记录输出 | 0 | 12 | 12 | 0 | 0 | 12 | 0 | 0 |
0 | 60af517e-5f27-4c7a-96f9-c47fcb889ce7 | 6/14/2018 17:13:55 | 错误处理 | JavaScript代码 | 0 | 5 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 89cb570a-1e3a-4f6e-b909-30d7bb31fbe6 | 6/14/2018 17:13:55 | 错误处理 | 过滤记录输出 | 0 | 5 | 5 | 0 | 0 | 5 | 0 | 0 |
0 | 6a38328a-af09-49ac-b9e8-5adc5346e821 | 6/14/2018 17:13:55 | 错误处理 | 过滤记录输出 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
运行:状态有end和stop2种,其中stop为出错后执行的状态,其中有字段errors显示错误个数。
日志通道:
ID_BATCH | CHANNEL_ID | LOG_DATE | LOGGING_OBJECT_TYPE | OBJECT_NAME | OBJECT_COPY | REPOSITORY_DIRECTORY | FILENAME | OBJECT_ID | OBJECT_REVISION | PARENT_CHANNEL_ID | ROOT_CHANNEL_ID |
0 | c88e0e5a-8dab-44ff-bbaa-2eefe2fb4dad | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | d577d451-2c6e-4fe5-b044-3f6db889cfeb | 99922df2-8f34-4865-bdb9-6d571895eae4 | |||||
0 | d577d451-2c6e-4fe5-b044-3f6db889cfeb | 6/14/2018 17:13:55 | STEP | 表输入 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | ||||
0 | 3f5df8a8-525d-4fda-9656-47ec84eabad4 | 6/14/2018 17:13:55 | STEP | 数据检验 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | ||||
0 | c1416a32-a719-4e49-b4ad-7405483a8c7e | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | d2e88ac3-2af0-4e97-b142-32e4bd75dadc | 99922df2-8f34-4865-bdb9-6d571895eae4 | |||||
0 | d2e88ac3-2af0-4e97-b142-32e4bd75dadc | 6/14/2018 17:13:55 | STEP | 正确记录输出 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | ||||
0 | 60af517e-5f27-4c7a-96f9-c47fcb889ce7 | 6/14/2018 17:13:55 | STEP | JavaScript代码 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | ||||
0 | bed78c9a-4a9c-4862-9b34-3ad3d5eb3fde | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | 89cb570a-1e3a-4f6e-b909-30d7bb31fbe6 | 99922df2-8f34-4865-bdb9-6d571895eae4 | |||||
0 | 89cb570a-1e3a-4f6e-b909-30d7bb31fbe6 | 6/14/2018 17:13:55 | STEP | 过滤记录输出 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | ||||
0 | c75912a5-d9dd-4adf-ae22-b69edf1ddbfb | 6/14/2018 17:13:55 | DATABASE | local_zj_ctcc | 6a38328a-af09-49ac-b9e8-5adc5346e821 | 99922df2-8f34-4865-bdb9-6d571895eae4 | |||||
0 | 6a38328a-af09-49ac-b9e8-5adc5346e821 | 6/14/2018 17:13:55 | STEP | 过滤记录输出 2 | 0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | ||||
0 | 1b40927e-a18a-459b-a7d3-009195b4ed3a | 6/14/2018 17:13:55 | DATABASE | local_kettle_log | 99922df2-8f34-4865-bdb9-6d571895eae4 | 99922df2-8f34-4865-bdb9-6d571895eae4 | |||||
0 | 99922df2-8f34-4865-bdb9-6d571895eae4 | 6/14/2018 17:13:55 | TRANS | 错误处理 | / | D:\etl_project\错误处理.ktr | 3b5dff4f-0a52-41de-aac6-e5427f3058c7 | 99922df2-8f34-4865-bdb9-6d571895eae4 |
Metrics:空,无数据