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 <[email protected]>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 <[email protected] > >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 | *Action**X* | 584 Broadway, Suite 1002 – NY, NY 10012 415-994-3480 (m)
