You are viewing a plain text version of this content. The canonical link for it is here.
Posted to mapreduce-user@hadoop.apache.org by Kumar Jayapal <kj...@gmail.com> on 2015/07/28 01:17:25 UTC
Sqoop export date error
Hi ,
I am trying to export data from hdfs to oracle Its getting error out at
date column.
sqoop export
-Dmapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom"
--connect "jdbc:oracle:thin:@lorsastest.kjp.com:1521/testus" --username
usertest -P --table "TEST_BI.DATE_DIMENSION" --export-dir
"/user/hive/warehouse/sandbox.db/date_dimension_text"
--input-fields-terminated-by '\001' --input-null-string '\\N'
--input-null-non-string '\\N'
I get exception
15/07/27 23:01:00 INFO mapreduce.Job: Task Id :
attempt_1437406596370_5980_m_000001_1, Status : FAILED
Error: java.io.IOException: Can't export data, please check failed map task
logs
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1642)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163)
Caused by: java.lang.RuntimeException: Can't parse input data: '1977-05-12'
at
TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1892)
at TEST_BI_DATE_DIMENSION.parse(TEST_BI_DATE_DIMENSION.java:1645)
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
Caused by: java.lang.IllegalArgumentException: Timestamp format must be
yyyy-mm-dd hh:mm:ss[.fffffffff]
at java.sql.Timestamp.valueOf(Timestamp.java:202)
at
TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1699)
... 12 more
Thanks
Jay
Re: Sqoop export date error
Posted by Laurent H <la...@gmail.com>.
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd
hh:mm:ss : check date format in your data (there can be a difference
between timestamp in hive and in your DB)
--
Laurent HATIER - Consultant Big Data & Business Intelligence chez CapGemini
fr.linkedin.com/pub/laurent-hatier/25/36b/a86/
<http://fr.linkedin.com/pub/laurent-h/25/36b/a86/>
2015-07-28 1:17 GMT+02:00 Kumar Jayapal <kj...@gmail.com>:
>
> Hi ,
>
> I am trying to export data from hdfs to oracle Its getting error out at
> date column.
>
> sqoop export
> -Dmapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom"
> --connect "jdbc:oracle:thin:@lorsastest.kjp.com:1521/testus" --username
> usertest -P --table "TEST_BI.DATE_DIMENSION" --export-dir
> "/user/hive/warehouse/sandbox.db/date_dimension_text"
> --input-fields-terminated-by '\001' --input-null-string '\\N'
> --input-null-non-string '\\N'
>
>
> I get exception
>
>
> 15/07/27 23:01:00 INFO mapreduce.Job: Task Id :
> attempt_1437406596370_5980_m_000001_1, Status : FAILED
> Error: java.io.IOException: Can't export data, please check failed map
> task logs
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:415)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1642)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163)
> Caused by: java.lang.RuntimeException: Can't parse input data: '1977-05-12'
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1892)
> at TEST_BI_DATE_DIMENSION.parse(TEST_BI_DATE_DIMENSION.java:1645)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
> ... 10 more
> Caused by: java.lang.IllegalArgumentException: Timestamp format must be
> yyyy-mm-dd hh:mm:ss[.fffffffff]
> at java.sql.Timestamp.valueOf(Timestamp.java:202)
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1699)
> ... 12 more
>
>
>
> Thanks
> Jay
>
Re: Sqoop export date error
Posted by Laurent H <la...@gmail.com>.
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd
hh:mm:ss : check date format in your data (there can be a difference
between timestamp in hive and in your DB)
--
Laurent HATIER - Consultant Big Data & Business Intelligence chez CapGemini
fr.linkedin.com/pub/laurent-hatier/25/36b/a86/
<http://fr.linkedin.com/pub/laurent-h/25/36b/a86/>
2015-07-28 1:17 GMT+02:00 Kumar Jayapal <kj...@gmail.com>:
>
> Hi ,
>
> I am trying to export data from hdfs to oracle Its getting error out at
> date column.
>
> sqoop export
> -Dmapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom"
> --connect "jdbc:oracle:thin:@lorsastest.kjp.com:1521/testus" --username
> usertest -P --table "TEST_BI.DATE_DIMENSION" --export-dir
> "/user/hive/warehouse/sandbox.db/date_dimension_text"
> --input-fields-terminated-by '\001' --input-null-string '\\N'
> --input-null-non-string '\\N'
>
>
> I get exception
>
>
> 15/07/27 23:01:00 INFO mapreduce.Job: Task Id :
> attempt_1437406596370_5980_m_000001_1, Status : FAILED
> Error: java.io.IOException: Can't export data, please check failed map
> task logs
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:415)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1642)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163)
> Caused by: java.lang.RuntimeException: Can't parse input data: '1977-05-12'
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1892)
> at TEST_BI_DATE_DIMENSION.parse(TEST_BI_DATE_DIMENSION.java:1645)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
> ... 10 more
> Caused by: java.lang.IllegalArgumentException: Timestamp format must be
> yyyy-mm-dd hh:mm:ss[.fffffffff]
> at java.sql.Timestamp.valueOf(Timestamp.java:202)
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1699)
> ... 12 more
>
>
>
> Thanks
> Jay
>
Re: Sqoop export date error
Posted by Abraham Elmahrek <ab...@cloudera.com>.
Hey there,
it seems like you're loading into Oracle TIMESTAMP type with a normal date
(no time information). I see a couple of solutions:
1. Transorm the data before loading with Sqoop so that it fits the type.
2. Change your database table to use DATE instead of TIMESTAMP.
You might also be able to use an updateable view.
Also, check out
http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_dates_and_times for
details on how to handle oracle.
-Abe
On Mon, Jul 27, 2015 at 4:17 PM, Kumar Jayapal <kj...@gmail.com> wrote:
>
> Hi ,
>
> I am trying to export data from hdfs to oracle Its getting error out at
> date column.
>
> sqoop export
> -Dmapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom"
> --connect "jdbc:oracle:thin:@lorsastest.kjp.com:1521/testus" --username
> usertest -P --table "TEST_BI.DATE_DIMENSION" --export-dir
> "/user/hive/warehouse/sandbox.db/date_dimension_text"
> --input-fields-terminated-by '\001' --input-null-string '\\N'
> --input-null-non-string '\\N'
>
>
> I get exception
>
>
> 15/07/27 23:01:00 INFO mapreduce.Job: Task Id :
> attempt_1437406596370_5980_m_000001_1, Status : FAILED
> Error: java.io.IOException: Can't export data, please check failed map
> task logs
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:415)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1642)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163)
> Caused by: java.lang.RuntimeException: Can't parse input data: '1977-05-12'
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1892)
> at TEST_BI_DATE_DIMENSION.parse(TEST_BI_DATE_DIMENSION.java:1645)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
> ... 10 more
> Caused by: java.lang.IllegalArgumentException: Timestamp format must be
> yyyy-mm-dd hh:mm:ss[.fffffffff]
> at java.sql.Timestamp.valueOf(Timestamp.java:202)
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1699)
> ... 12 more
>
>
>
> Thanks
> Jay
>
Re: Sqoop export date error
Posted by Laurent H <la...@gmail.com>.
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd
hh:mm:ss : check date format in your data (there can be a difference
between timestamp in hive and in your DB)
--
Laurent HATIER - Consultant Big Data & Business Intelligence chez CapGemini
fr.linkedin.com/pub/laurent-hatier/25/36b/a86/
<http://fr.linkedin.com/pub/laurent-h/25/36b/a86/>
2015-07-28 1:17 GMT+02:00 Kumar Jayapal <kj...@gmail.com>:
>
> Hi ,
>
> I am trying to export data from hdfs to oracle Its getting error out at
> date column.
>
> sqoop export
> -Dmapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom"
> --connect "jdbc:oracle:thin:@lorsastest.kjp.com:1521/testus" --username
> usertest -P --table "TEST_BI.DATE_DIMENSION" --export-dir
> "/user/hive/warehouse/sandbox.db/date_dimension_text"
> --input-fields-terminated-by '\001' --input-null-string '\\N'
> --input-null-non-string '\\N'
>
>
> I get exception
>
>
> 15/07/27 23:01:00 INFO mapreduce.Job: Task Id :
> attempt_1437406596370_5980_m_000001_1, Status : FAILED
> Error: java.io.IOException: Can't export data, please check failed map
> task logs
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:415)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1642)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163)
> Caused by: java.lang.RuntimeException: Can't parse input data: '1977-05-12'
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1892)
> at TEST_BI_DATE_DIMENSION.parse(TEST_BI_DATE_DIMENSION.java:1645)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
> ... 10 more
> Caused by: java.lang.IllegalArgumentException: Timestamp format must be
> yyyy-mm-dd hh:mm:ss[.fffffffff]
> at java.sql.Timestamp.valueOf(Timestamp.java:202)
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1699)
> ... 12 more
>
>
>
> Thanks
> Jay
>
Re: Sqoop export date error
Posted by Laurent H <la...@gmail.com>.
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd
hh:mm:ss : check date format in your data (there can be a difference
between timestamp in hive and in your DB)
--
Laurent HATIER - Consultant Big Data & Business Intelligence chez CapGemini
fr.linkedin.com/pub/laurent-hatier/25/36b/a86/
<http://fr.linkedin.com/pub/laurent-h/25/36b/a86/>
2015-07-28 1:17 GMT+02:00 Kumar Jayapal <kj...@gmail.com>:
>
> Hi ,
>
> I am trying to export data from hdfs to oracle Its getting error out at
> date column.
>
> sqoop export
> -Dmapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom"
> --connect "jdbc:oracle:thin:@lorsastest.kjp.com:1521/testus" --username
> usertest -P --table "TEST_BI.DATE_DIMENSION" --export-dir
> "/user/hive/warehouse/sandbox.db/date_dimension_text"
> --input-fields-terminated-by '\001' --input-null-string '\\N'
> --input-null-non-string '\\N'
>
>
> I get exception
>
>
> 15/07/27 23:01:00 INFO mapreduce.Job: Task Id :
> attempt_1437406596370_5980_m_000001_1, Status : FAILED
> Error: java.io.IOException: Can't export data, please check failed map
> task logs
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:415)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1642)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163)
> Caused by: java.lang.RuntimeException: Can't parse input data: '1977-05-12'
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1892)
> at TEST_BI_DATE_DIMENSION.parse(TEST_BI_DATE_DIMENSION.java:1645)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
> ... 10 more
> Caused by: java.lang.IllegalArgumentException: Timestamp format must be
> yyyy-mm-dd hh:mm:ss[.fffffffff]
> at java.sql.Timestamp.valueOf(Timestamp.java:202)
> at
> TEST_BI_DATE_DIMENSION.__loadFromFields(TEST_BI_DATE_DIMENSION.java:1699)
> ... 12 more
>
>
>
> Thanks
> Jay
>