kettle之牛刀小试

需求描述：

客户端有一个程序定时(15分钟间隔)的采集外网数据，不过需要将该数据保存到线上数据库的表中。但是由于客户端程序在本地，无法直接访问线上数据库。需要解决将该数据，写入到线上数据库的问题。

 

csv的格式如下：

1    1    1h

2    2    2h

....底下省略

 

表的结构跟csv的字段一样的。

id    hour_id hour_desc

1     1              1h

2     2              2h

 

方案：

1，本地的客户端程序，将采集到的外网数据先存在本地的tsa_dim_hour表中。

2，然后再本地起一个定时任务，将本地tsa_dim_hour表的数据生成csv文件。然后将该文件上传到ftp服务器。

3，在线上由kettle定时的从ftp上取该文件,将从ftp下的文件下载下来

4，然后将csv的数据更新到表中。因为csv的数据，由应用每隔15分钟向其增量插入数据。所以我拿到整个csv文件时，需要采用insert-update的方式。

 

步骤如下：

1，客户端将采集到的外网数据插入本地表的步骤省略。

2，本地将相关表的数据导出成csv格式。

在D盘目录下有两个文件：

文件dumpCallcenterData.bat内容如下：

@cd d:/
del test.csv
echo "删除数据文件"
mysql -uroot -proot -D tsa < test.sql

   

 

文件test.sql内容如下：

use tsa;
select * from tsa_dim_hour
into outfile 'd:/test.csv'    
fields terminated by ',' optionally enclosed by '"' escaped by '"'    
lines terminated by '\r\n'; 

 

 在本地起一个windows定时任务，每隔15分钟就将该表的数据导出成test.csv的文件。并将其上传到ftp服务器上。

 

3，将csv的文本数据同步到表中，我采用的是kettle的方案。

 

这个是相关的job，到时候上线的话，会设置间隔执行的时间。

图一：

以下为Get a file with FTPS的内容，这里需要注意的是remote directory可以不指定，不然会报Return Value: 550 Description: Failed to change directory. 的错误。因为我的ftp账号没有相关权限吧。

 

图二：

图三：图一中的transformation

这里需要注意的是，由于csv的数据没有表头，所以需要考虑如何将csv的列数据，与表中的字段一一对应上。一般可以利用获取字段，在我们的这种情况下，就会取得csv中的第一行的数据作为字段名称。所以这里就手动输入相关名称就可以了。

由于excel中的date_id，starttime,endtime为date,datetime,datetime类型，需要在后面的format上指定转换格式。因此分别指定如下：yyyy-MM-dd,yyyy-MM-dd HH:mi:ss,yyyy-MM-dd HH:mi:ss，按照此种方案，发现date_id会报错，错误如下： 

Error in step, asking everyone to stop because of:

Date<binary-string> : Unable to compare with value [Date]

date_id String : couldn't convert string [2011/07/03 00:00:00.000] to a date using format [yyyy-MM-dd]
Unparseable date: "2011/07/03 00:00:00.000"

 

解决方案为：

Since you enabled the "lazy conversion" option in the "CSV Input" step, the data is only convert when needed. In this case, in the Dimension step.
Uncheck the "lazy conversion" option to see the error happen in the "CSV Input" step.

 

图四：insert-update