Transfrom user_daily_activities_count to Drpt
select
date(actiontime),
userid,
planid,
logtype,
count(1)
from singtel_drptetl.ott_activitieslog al
where
actiontime>="2013-01-01"
and actiontime<"2013/08/21"
group by userid, planid, logtype
order by userid, planid, logtype
2013/08/21 13:25:42 - Checks if files exist -
ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 13:25:42 - Checks if files exist -
ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
Column 'UserID' cannot be null
解决办法:太难添加where条件 UserID>0
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error converting line
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Couldn't parse field [Number(15)] with value [dcf3d66bd49ba884e9474ef5 ], format [#.#] on data row [1126406].
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected conversion error while converting value [ClientVersion String(15)] to a Number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5 "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.textfileinput.TextFileInput.convertLineToRow(TextFileInput.java:844)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.textfileinput.TextFileInput.processRow(TextFileInput.java:1149)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.lang.Thread.run(Unknown Source)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Couldn't parse field [Number(15)] with value [dcf3d66bd49ba884e9474ef5 ], format [#.#] on data row [1126406].
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected conversion error while converting value [ClientVersion String(15)] to a Number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5 "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.textfileinput.TextFileInput.convertLineToRow(TextFileInput.java:731)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 3 more
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleValueException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected conversion error while converting value [ClientVersion String(15)] to a Number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5 "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1451)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertData(ValueMeta.java:3070)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertDataFromString(ValueMeta.java:3267)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.textfileinput.TextFileInput.convertLineToRow(TextFileInput.java:691)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 3 more
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleValueException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5 "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertStringToNumber(ValueMeta.java:708)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1404)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 6 more
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: java.text.ParseException: Unparseable number: "dcf3d66bd49ba884e9474ef5 "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.text.NumberFormat.parse(Unknown Source)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertStringToNumber(ValueMeta.java:704)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 7 more
2013/08/21 16:38:02 - RepMysql - Connection to database closed!
2013/08/21 16:38:02 - SRC_Loginhistory - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 错误被检测到!
2013/08/21 16:38:02 - import to DRPTSRC_Loginhistory.0 - 完成处理 (I=0, O=1119233, R=1119233, W=1119232, U=0, E=0
2013/08/21 16:38:02 - SRC_Loginhistory - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 错误被检测到!
2013/08/21 16:38:02 - 文本文件输入.0 - 完成处理 (I=1126407, O=0, R=0, W=1126405, U=0, E=1
2013/08/21 16:38:02 - SRC_Loginhistory - SRC_Loginhistory
2013/08/21 16:38:02 - SRC_Loginhistory - SRC_Loginhistory
Exact的时候选UTF-8.避免乱码
或者 有回车换行符 下面的sql语句去除换行符
SELECT
[LoginHistoryID]
,[CreateDate]
,[MemberID]
,[LoginDate]
,REPLACE( [ClientVersion] ,CHAR(10),'') AS ClientVersion
,REPLACE( [HandsetCode],CHAR(10),'') AS HandsetCode
,REPLACE( [Model] ,CHAR(10),'') AS Model
,REPLACE( [DeviceType],CHAR(10),'') AS DeviceType
FROM SingTelReportDB.[dbo].[LoginHistory]
WHERE CONVERT(varchar(10),CreateDate,23) >= ${Exact_StartDate}
AND CONVERT(varchar(10),CreateDate,23) < ${Exact_EndDate}