导致kettle脚本异常的sql：FIELDS TERMINATED BY ';'

一背景：

接手一个系统，包含定时任务JOB工程，其中一个重要模块依赖于kettle脚本。但是偏偏这个模块报错了，导致只能手工屏蔽该脚本，人工在两个库之间执行sql导数据。还都是半夜之后跑任务，十分折磨人。决心修复这个bug。

二分析：

TRUNCATE TABLE crm_custom_status_history;

LOAD DATA LOCAL INFILE '/opt/data/etl/crm/expData-crm_custom_status_history.txt' INTO TABLE crm_custom_status_history FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (
	id,
	custom_id,
	STATUS,
	type,
	reason,
	creator,
	creator_name,
	create_date
);

这是脚本sql.单独在数据库里面可以执行，但是放在job调用kettle脚本里就会报错，异常如下：

mportToCrm_custom_status_history - An error occurred executing this job entry : 
Couldn't execute SQL: LOAD DATA LOCAL INFILE '/opt/data/etl/crm/expData-crm_custom_status_history.txt' INTO TABLE crm_custom_status_history FIELDS TERMINATED BY '

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

INFO  31-07 14:50:48,846 - downloadStatusChangeHistory - Finished job entry [importToCrm_custom_status_history] (result=[false])
INFO  31-07 14:50:48,846 - downloadStatusChangeHistory - Finished job entry [将商家状态变更历史导入到dw] (result=[false])
INFO  31-07 14:50:48,846 - downloadStatusChangeHistory - Job execution finished

一开始以为是权限问题，但是日志提示是sql语法错误，要是sql单独执行报错还好调试，但是是脚本sql.单独在数据库里面可以执行，这就让人郁闷了，因为我对kettle不熟悉。

时间又紧急，怎么办呢？还是换个思路解决。

梳理下kettle脚本的意思，就是清空表然后根据文件导入数据。

这里顺便梳理下相关知识：

基本语法：

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]

into table tbl_name

[fields

[terminated by't']

[OPTIONALLY] enclosed by '']

[escaped by'\' ]]

[lines terminated by'n']

[ignore number lines]

[(col_name,   )]

LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。

为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用LOAD DATA INFILE，在服务器主机上你必须有file的权限。见6.5 由MySQL提供的权限。

1 如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令： 

load data low_priority infile "/home/mark/data sql" into table Orders;

这里我们没有使用，可以排除掉。

2如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。.

当在服务器主机上寻找文件时，服务器使用下列规则：

如果给出一个绝对路径名，服务器使用该路径名。 
如果给出一个有一个或多个前置部件的相对路径名，服务器相对服务器的数据目录搜索文件。

这里参照原来的sql可知，使用绝对路径，检查文件是否存在及权限也没有问题。在看sql报错的语句，也是在这之后的，那么这个点就pass了。

3、replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理。如果你指定 replace ，新行将代替有相同的唯一键值的现有行。如果你指定 ignore ，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。例如：

load data low_priority infile "/home/mark/data sql" replace into table Orders;

这里我们使用了ingore:忽略第一行

就是为了忽略第一行标题。这个也不是问题。而且这个对应sql没有意思出问题的‘‘’’，排除掉。

4分隔符：

terminated by 分隔符：意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符

terminated by 描述字段的分隔符，默认情况下是tab字符（\t） 
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

我们报错的sql是使用了的。也就是以分号“;“”分割。双引号”包括。换行是\n。

抱着试试的态度，我在脚本里面去掉这段sql。在执行job.不报错了。

那就是说明这里有问题，至于为什么在sql中可以执行，但是kettle脚本中不行，原因未知。

三修复：

问题定位了，那就是考虑如何修复了，我猜可能跟版本有关，或者某个bug导致。这个搜索不出来相关文章。

换个思路，既然是导数据的。那就去掉分隔符，吧之前脚本生成文件的kettle脚本修改下，用默认的tab分割（\t）.

导入的脚本也去掉分隔符，只是默认的load。修改后sql如下：

LOAD DATA LOCAL INFILE '/opt/data/etl/crm/expData-crm_custom_status_history.txt' INTO TABLE crm_custom_status_history  IGNORE 1 LINES (
	id,
	custom_id,
	STATUS,
	type,
	reason,
	creator,
	creator_name,
	create_date
);

在执行job，好了，调用kettle不报错了。可以再定时任务自动执行，再也不用人工半夜手动干预了。

总结：

遇到问题定位很关键，尤其是对线上紧急问题修复。

长期来看面对与团队不熟悉不受控的系统（也是引入第三方开源框架问题），要么熟悉这块把出现的问题修复掉，要么用其他的成熟方案来替代。

不然出问题搞不定，影响对于业务的支撑。