Jarcec
Please find below the versions
sqoop: sqoop-1.4.2.bin__hadoop-1.0.0.tar.gz
jdbc: postgresql-9.2-1000.jdbc4.jar
postgresql:PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
hadoop: Hadoop 1.0.3 (Amazon Distribution)
Cheers
Matthieu

On Tue, Nov 6, 2012 at 2:33 PM, Jarek Jarcec Cecho <jarcec@apache.org> wrote:
Hi Matthieu,
thank you very much for your outputs. I've took a look and I'm really not sure what is wrong. It seems that from strange reason sqoop submits for ts column value '[B@ba6c13' that seems to me an object address. But code that you've sent is correctly serializing long, so I'm really not sure what might be an issue here.

What versions are you using? (Sqoop, Hadoop, Postgre SQL JDBC Driver)?

Jarcec

On Tue, Nov 06, 2012 at 01:17:00PM -0500, Matthieu Labour wrote:
> Hi Jarcec
>
> I apologize for not getting back sooner to you. I live in NYC and life has
> been hectic here with the hurricane. Thank you for taking the time to look
> in my issue. Please find below the information requested.
>
>
> 1. Here is the command being executed followed by the error produced.
> Attached is ml_ys_log_gmt_test.java
>
> ~/$SQOOP_ROOT/bin/sqoop export --connect jdbc:postgresql://
> ec2-XX-XX-XXX-XX.compute-1.amazonaws.com:5662/dfg2tulf7263ut --username
> ufjb0gfs1n5kut --password xxxxxxxxx --table ml_ys_log_gmt_test
> --export-dir $HADOOP_INPUT/$LOGS_HOME/dt=$(date +%Y-%m-%d)
> --input-fields-terminated-by='\t' --lines-terminated-by='\n' --verbose
> --batch
>
> 12/11/06 17:35:33 INFO mapred.JobClient: Task Id :
> attempt_201211061658_0014_m_000000_0, Status : FAILED
> java.io.IOException: java.sql.BatchUpdateException: Batch entry 71 INSERT
> INTO ml_ys_log_gmt_test (date, ts, environment, resource, network,
> advertiser, campaign, creative, cost, click, flavor, ui_element_id,
> ui_element_type, event, charge_id, charge_type, charge_vertical,
> charge_payer, charge_amount, model, imageformatsupport, inputmodesupport,
> scriptsupport, vendor, stylesheetsupport, markupsupport, displaywidth,
> cookiesupport, displayheight, version, inputdevices, displaycolordepth,
> device_os, device_os_version, mobile_browser, mobile_browser_version,
> is_tablet, dual_orientation, marketing_name) VALUES ('2012-10-31
> 20:42:56.767000 +00:00:00', '[B@ba6c13',
> 'remote-addr=10.84.101.45&user-agent=LG-CT810%2FV10x+NetFront%2Fv3.5+Profile%2FMIDP-2.0+Configuration%2FCLDC-1.1',
> 'hxCA', 'MLNL', '1006', '10014', '1410', NULL, 'mdkP', '10014', '', '',
> 'click', '', '', '', '', NULL, 'CT810', '', '', '', 'LG', '', '', '400',
> '', '240', '', 'stylus', '', 'Windows Mobile OS', '6.1', 'Microsoft Mobile
> Explorer', '7.11', 'false', 'false', '') was aborted. Call
> getNextException to see the cause.
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:220)
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
> at
> org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:646)
> at
> org.apache.hadoop.mapreduce.TaskInputOutputContext.write(TaskInputOutputContext.java:80)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:78)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:36)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:182)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:771)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:375)
> at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:396)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1132)
> at org.apache.hadoop.mapred.Child.main(Child.java:249)
>
>
> 2. Here is the command being executed followed by the error produced.
> Attached is ml_ys_log_gmt_test_with_map_column_java_ts_long.java
>
> ~/$SQOOP_ROOT/bin/sqoop export --connect jdbc:postgresql://
> ec2-XX-XX-XXX-XX.compute-1.amazonaws.com:5662/dfg2tulf7263ut --username
> ufjb0gfs1n5kut --password xxxxxxx --table ml_ys_log_gmt_test --export-dir
> $HADOOP_INPUT/$LOGS_HOME/dt=$(date +%Y-%m-%d)
> --input-fields-terminated-by='\t' --lines-terminated-by='\n'
> --map-column-java ts=Long --verbose --batch
>
>
> 12/11/06 17:57:17 INFO mapred.JobClient: Task Id :
> attempt_201211061658_0016_m_000000_0, Status : FAILED
> java.io.IOException: java.sql.BatchUpdateException: Batch entry 71 INSERT
> INTO ml_ys_log_gmt_test (date, ts, environment, resource, network,
> advertiser, campaign, creative, cost, click, flavor, ui_element_id,
> ui_element_type, event, charge_id, charge_type, charge_vertical,
> charge_payer, charge_amount, model, imageformatsupport, inputmodesupport,
> scriptsupport, vendor, stylesheetsupport, markupsupport, displaywidth,
> cookiesupport, displayheight, version, inputdevices, displaycolordepth,
> device_os, device_os_version, mobile_browser, mobile_browser_version,
> is_tablet, dual_orientation, marketing_name) VALUES ('2012-10-31
> 20:42:56.767000 +00:00:00', '[B@1d709a5',
> 'remote-addr=10.84.101.45&user-agent=LG-CT810%2FV10x+NetFront%2Fv3.5+Profile%2FMIDP-2.0+Configuration%2FCLDC-1.1',
> 'hxCA', 'MLNL', '1006', '10014', '1410', NULL, 'mdkP', '10014', '', '',
> 'click', '', '', '', '', NULL, 'CT810', '', '', '', 'LG', '', '', '400',
> '', '240', '', 'stylus', '', 'Windows Mobile OS', '6.1', 'Microsoft Mobile
> Explorer', '7.11', 'false', 'false', '') was aborted. Call
> getNextException to see the cause.
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:220)
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
> at
> org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:646)
> at
> org.apache.hadoop.mapreduce.TaskInputOutputContext.write(TaskInputOutputContext.java:80)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:78)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:36)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:182)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:771)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:375)
> at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:396)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1132)
> at org.apache.hadoop.mapred.Child.main(Child.java:249)
>
> 3. I executed the following command. Attached is
> also ml_ys_log_gmt_test_with_map_column_java_ts_string.java
>
> Please note that the error seems OK as the column's type is bigint. The
> value being passed for 'ts' in the insert statement looks good to me.
>
> ~/$SQOOP_ROOT/bin/sqoop export --connect jdbc:postgresql://
> ec2-XX-XX-XXX-XX.compute-1.amazonaws.com:5662/dfg2tulf7263ut --username
> ufjb0gfs1n5kut --password xxxxxx --table ml_ys_log_gmt_test --export-dir
> $HADOOP_INPUT/$LOGS_HOME/dt=$(date +%Y-%m-%d)
> --input-fields-terminated-by='\t' --lines-terminated-by='\n'
> --map-column-java ts=String --verbose --batch
>
>
> 12/11/06 18:08:22 INFO mapred.JobClient: Task Id :
> attempt_201211061658_0018_m_000000_0, Status : FAILED
> java.io.IOException: java.sql.BatchUpdateException: Batch entry 0 INSERT
> INTO ml_ys_log_gmt_test (date, ts, environment, resource, network,
> advertiser, campaign, creative, cost, click, flavor, ui_element_id,
> ui_element_type, event, charge_id, charge_type, charge_vertical,
> charge_payer, charge_amount, model, imageformatsupport, inputmodesupport,
> scriptsupport, vendor, stylesheetsupport, markupsupport, displaywidth,
> cookiesupport, displayheight, version, inputdevices, displaycolordepth,
> device_os, device_os_version, mobile_browser, mobile_browser_version,
> is_tablet, dual_orientation, marketing_name) VALUES ('2012-10-31
> 06:44:02.631000 +00:00:00', '1351665842631',
> 'remote-addr=10.86.79.48&user-agent=Mozilla%2F5.0+%28iPod%3B+CPU+iPhone+OS+6_0+like+Mac+OS+X%29+AppleWebKit%2F536.26+%28KHTML%2C+like+Gecko%29+Version%2F6.0+Mobile%2F10A403+Safari%2F8536.25',
> 'hBdV', 'APPR', '1021', '10038', '3800', NULL, 'mbVr', '2000002', '', '',
> 'click', '', '', '', '', NULL, 'iPod Touch', '', '', '', 'Apple', '', '',
> '320', '', '480', '', 'touchscreen', '', 'iPhone OS', '1.0', 'Safari', '',
> 'false', 'true', '') was aborted. Call getNextException to see the cause.
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:220)
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
> at
> org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:646)
> at
> org.apache.hadoop.mapreduce.TaskInputOutputContext.write(TaskInputOutputContext.java:80)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:78)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:36)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:182)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:771)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:375)
> at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:396)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1132)
> at org.apache.hadoop.mapred.Child.main(Child.java:249)
>
>
>
>
> On Fri, Nov 2, 2012 at 8:19 PM, Jarek Jarcec Cecho <jarcec@apache.org>wrote:
>
> > Hi Matthieu,
> > would you mind sharing with us writable class that Sqoop generates for
> > your table? I'm expecting that this file will be named "ml_ys_log_gmt.java"
> > and it will be available in working directory where you're executing sqoop.
> >
> > You might try utilizing parameter type mapping argument --map-column-java
> > ts=Long or ts=String to see if it helps.
> >
> > Jarcec
> >
> > On Fri, Nov 02, 2012 at 07:18:44PM -0400, Matthieu Labour wrote:
> > > Hi
> > >
> > > Sometimes sqoop craps out. I am looking for any tips or help. Thank you
> > > very much !
> > >
> > > The following command runs
> > >
> > > ~/$SQOOP_ROOT/bin/sqoop export --connect jdbc:postgresql://
> > > ec2-XX-XX-XXX-XX.compute-1.amazonaws.com:5662/xxxxxxxxx --username
> > xxxxxxxx
> > > --password xxxxxxxx --table ml_ys_log_gmt --export-dir
> > > $HADOOP_INPUT/$LOGS_HOME/dt=$(date +%Y-%m-%d)
> > > --input-fields-terminated-by='\t' --lines-terminated-by='\n' --verbose
> > > --batch
> > >
> > > on the following line
> > >
> > > 2012-10-27 23:44:25.699 1351381465699
> > >
> > remote-addr=10.119.81.125&user-agent=SAMSUNG-SGH-I637%2FUCIJ2+Profile%2FMIDP-2.1+Configuration%2FCLDC-1.1+Mozilla%2F4.0+%28compatible%3B+MSIE+6.0%3B+Windows+CE%3B+IEMobile+8.12%3B+MSIEMobile+6.0%29
> > > hxCA MLNL 1006 10014 1410 kUQN 10014
> > > click SGH-i637
> > > Samsung 320
> > >
> > > I get the error below.
> > >
> > > The insert craps out on the second column 'ts' in INSERT INTO
> > ml_ys_log_gmt
> > > (date, ts
> > >
> > > The value passed is '[B@6127da' when it should be 1351381465699 (second
> > > field in the tab delimited line above)
> > >
> > > Here is the description in the table
> > > ts | bigint |
> > > | plain |
> > >
> > >
> > > 12/10/28 06:57:20 INFO mapred.JobClient: Task Id :
> > > attempt_201210280647_0004_m_000000_0, Status : FAILED
> > > java.io.IOException: java.sql.BatchUpdateException: Batch entry 59 INSERT
> > > INTO ml_ys_log_gmt (date, ts, environment, resource, network, advertiser,
> > > campaign, creative, cost, click, flavor, ui_element_id, ui_element_type,
> > > event, charge_id, charge_type, charge_vertical, charge_payer,
> > > charge_amount, model, imageformatsupport, inputmodesupport,
> > scriptsupport,
> > > vendor, stylesheetsupport, markupsupport, displaywidth, cookiesupport,
> > > displayheight, version, inputdevices, displaycolordepth, device_os,
> > > device_os_version, mobile_browser, mobile_browser_version, is_tablet,
> > > dual_orientation, marketing_name) VALUES ('2012-10-27 23:44:25.699000
> > > +00:00:00', '[B@6127da',
> > >
> > 'remote-addr=10.119.81.125&user-agent=SAMSUNG-SGH-I637%2FUCIJ2+Profile%2FMIDP-2.1+Configuration%2FCLDC-1.1+Mozilla%2F4.0+%28compatible%3B+MSIE+6.0%3B+Windows+CE%3B+IEMobile+8.12%3B+MSIEMobile+6.0%29',
> > > 'hxCA', 'MLNL', '1006', '10014', '1410', NULL, 'kUQN', '10014', '', '',
> > > 'click', '', '', '', '', NULL, 'SGH-i637', '', '', '', 'Samsung', '', '',
> > > '320', '', '240', '', '', '', 'Windows Mobile OS', '5.0', 'Microsoft
> > Mobile
> > > Explorer', '8.12', 'false', 'false', 'BlackJack') was aborted. Call
> > > getNextException to see the cause.
> > > at
> > >
> > org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:220)
> > > at
> > >
> > org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
> > > at
> > >
> > org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:646)
> > > at
> > >
> > org.apache.hadoop.mapreduce.TaskInputOutputContext.write(TaskInputOutputContext.java:80)
> > > at
> > org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:78)
> > > at
> > org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:36)
> > > at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)
> > > at
> > >
> > org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:182)
> > > at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:771)
> > > at org.apache.hadoop.mapred.MapTask.run(MapTask.java:375)
> > > at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
> > > at java.security.AccessController.doPrivileged(Native Method)
> > > at javax.security.auth.Subject.doAs(Subject.java:396)
> > > at
> > >
> > org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1132)
> > > at org.apache.hadoop.mapred.Child.main(Child.java:249)
> > > Caused by: java.sql.BatchUpdateException: Batch entry 59 INSERT INTO
> > > ml_ys_log_gmt (date, ts, environment, resource, network, advertiser,
> > > campaign, creative, cost, click, flavor, ui_element_id, ui_element_type,
> > > event, charge_id, charge_type, charge_vertical, charge_payer,
> > > charge_amount, model, imageformatsupport, inputmodesupport,
> > scriptsupport,
> > > vendor, stylesheetsupport, markupsupport, displaywidth, cookiesupport,
> > > displayheight, version, inputdevices, displaycolordepth, device_os,
> > > device_os_version, mobile_browser, mobile_browser_version, is_tablet,
> > > dual_orientation, marketing_name) VALUES ('2012-10-27 23:44:25.699000
> > > +00:00:00', '[B@6127da',
> > >
> > 'remote-addr=10.119.81.125&user-agent=SAMSUNG-SGH-I637%2FUCIJ2+Profile%2FMIDP-2.1+Configuration%2FCLDC-1.1+Mozilla%2F4.0+%28compatible%3B+MSIE+6.0%3B+Windows+CE%3B+IEMobile+8.12%3B+MSIEMobile+6.0%29',
> > > 'hxCA', 'MLNL', '1006', '10014', '1410', NULL, 'kUQN', '10014', '', '',
> > > 'click', '', '', '', '', NULL, 'SGH-i637', '', '', '', 'Samsung', '', '',
> > > '320', '', '240', '', '', '', 'Windows Mobile OS', '5.0', 'Microsoft
> > Mobile
> > > Explorer', '8.12', 'false', 'false', 'BlackJack') was aborted. Call
> > > getNextException to see the cause.
> > > at
> > >
> > org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746)
> > > at
> > >
> > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887)
> > > at
> > >
> > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
> > > at
> > >
> > org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893)
> > > at
> > >
> > org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:228)
> > >
> > >
> > > --
> > > Matthieu Labour, Engineering | *Action**X* |
> > > 584 Broadway, Suite 1002 NY, NY 10012
> > > 415-994-3480 (m)
> >







--
Matthieu Labour, Engineering |ActionX|
584 Broadway, Suite 1002 NY, NY 10012
415-994-3480(m)