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 isml_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 alsoml_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)