You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by Matthieu Labour <ma...@actionx.com> on 2012/11/03 00:18:44 UTC
Sqoop exporting error
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)
Re: Sqoop exporting error
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
You're welcome. I'm glad that Sqoop has started working for you!
Jarcec
On Fri, Nov 09, 2012 at 01:26:56PM -0500, Matthieu Labour wrote:
> Jarcec
> Thank you for your email and for giving me the idea. You are right. In that
> case the issue was that the value for device_os was too long
> Now it works
> So in brief changing the type of 'ts' from bigint to numeric solved the
> issue
> Thanks again
> Matthieu
>
> On Thu, Nov 8, 2012 at 12:11 PM, Jarek Jarcec Cecho <ja...@apache.org>wrote:
>
> > Hi Matthieu,
> > sorry for my late response.
> >
> > Thank you for doing the research what might help fixing the issue. Sqoop
> > is using prepared statement and therefore we're not explicitly escaping
> > data values. I believe that the '1351716176767' is just the way PostgreSQL
> > connector is displaying the error, not the actual problem. You can see much
> > more number based columns escaped with quotes even in your example.
> >
> > Would you mind sharing task log for this particular job? I would like to
> > see the getNextException() call that should be there.
> >
> > Jarcec
> >
> > On Wed, Nov 07, 2012 at 01:10:10PM -0500, Matthieu Labour wrote:
> > > Hi Jarcec
> > >
> > > I did the following
> > >
> > > I changed the type of 'ts' column to decimal. ALTER TABLE
> > > ml_ys_log_gmt_test ALTER ts TYPE decimal;
> > >
> > > Table "public.ml_ys_log_gmt_test"
> > > Column | Type | Modifiers
> > > | Storage | Description
> > >
> > ------------------------+--------------------------+---------------------------------+----------+-------------
> > > ts | numeric |
> > > | main |
> > >
> > >
> > > Then I run the following command
> > >
> > > ~/$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' --verbose
> > > --batch
> > >
> > >
> > > 12/11/07 17:25:32 INFO mapred.JobClient: Task Id :
> > > attempt_201211071722_0002_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', '1351716176767',
> > >
> > '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)
> > >
> > > I have attached ml_ys_log_gmt_test_with_map_column_java_ts_numeric.java
> > >
> > > Please note that ts = '1351716176767' in the INSERT clause. The value
> > seems
> > > right. But since the definition of the column is numeric, the quotes (')
> > > surrounding 1351716176767 should not be there.
> > >
> > > Thank you for your help!
> > >
> > > 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 | *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)
Re: Sqoop exporting error
Posted by Matthieu Labour <ma...@actionx.com>.
Jarcec
Thank you for your email and for giving me the idea. You are right. In that
case the issue was that the value for device_os was too long
Now it works
So in brief changing the type of 'ts' from bigint to numeric solved the
issue
Thanks again
Matthieu
On Thu, Nov 8, 2012 at 12:11 PM, Jarek Jarcec Cecho <ja...@apache.org>wrote:
> Hi Matthieu,
> sorry for my late response.
>
> Thank you for doing the research what might help fixing the issue. Sqoop
> is using prepared statement and therefore we're not explicitly escaping
> data values. I believe that the '1351716176767' is just the way PostgreSQL
> connector is displaying the error, not the actual problem. You can see much
> more number based columns escaped with quotes even in your example.
>
> Would you mind sharing task log for this particular job? I would like to
> see the getNextException() call that should be there.
>
> Jarcec
>
> On Wed, Nov 07, 2012 at 01:10:10PM -0500, Matthieu Labour wrote:
> > Hi Jarcec
> >
> > I did the following
> >
> > I changed the type of 'ts' column to decimal. ALTER TABLE
> > ml_ys_log_gmt_test ALTER ts TYPE decimal;
> >
> > Table "public.ml_ys_log_gmt_test"
> > Column | Type | Modifiers
> > | Storage | Description
> >
> ------------------------+--------------------------+---------------------------------+----------+-------------
> > ts | numeric |
> > | main |
> >
> >
> > Then I run the following command
> >
> > ~/$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' --verbose
> > --batch
> >
> >
> > 12/11/07 17:25:32 INFO mapred.JobClient: Task Id :
> > attempt_201211071722_0002_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', '1351716176767',
> >
> '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)
> >
> > I have attached ml_ys_log_gmt_test_with_map_column_java_ts_numeric.java
> >
> > Please note that ts = '1351716176767' in the INSERT clause. The value
> seems
> > right. But since the definition of the column is numeric, the quotes (')
> > surrounding 1351716176767 should not be there.
> >
> > Thank you for your help!
> >
> > 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 | *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)
Re: Sqoop exporting error
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
Hi Matthieu,
sorry for my late response.
Thank you for doing the research what might help fixing the issue. Sqoop is using prepared statement and therefore we're not explicitly escaping data values. I believe that the '1351716176767' is just the way PostgreSQL connector is displaying the error, not the actual problem. You can see much more number based columns escaped with quotes even in your example.
Would you mind sharing task log for this particular job? I would like to see the getNextException() call that should be there.
Jarcec
On Wed, Nov 07, 2012 at 01:10:10PM -0500, Matthieu Labour wrote:
> Hi Jarcec
>
> I did the following
>
> I changed the type of 'ts' column to decimal. ALTER TABLE
> ml_ys_log_gmt_test ALTER ts TYPE decimal;
>
> Table "public.ml_ys_log_gmt_test"
> Column | Type | Modifiers
> | Storage | Description
> ------------------------+--------------------------+---------------------------------+----------+-------------
> ts | numeric |
> | main |
>
>
> Then I run the following command
>
> ~/$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' --verbose
> --batch
>
>
> 12/11/07 17:25:32 INFO mapred.JobClient: Task Id :
> attempt_201211071722_0002_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', '1351716176767',
> '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)
>
> I have attached ml_ys_log_gmt_test_with_map_column_java_ts_numeric.java
>
> Please note that ts = '1351716176767' in the INSERT clause. The value seems
> right. But since the definition of the column is numeric, the quotes (')
> surrounding 1351716176767 should not be there.
>
> Thank you for your help!
>
> Matthieu
>
>
> On Tue, Nov 6, 2012 at 2:33 PM, Jarek Jarcec Cecho <ja...@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 | *Action**X* |
> 584 Broadway, Suite 1002 – NY, NY 10012
> 415-994-3480 (m)
Re: Sqoop exporting error
Posted by Matthieu Labour <ma...@actionx.com>.
Hi Jarcec
I did the following
I changed the type of 'ts' column to decimal. ALTER TABLE
ml_ys_log_gmt_test ALTER ts TYPE decimal;
Table "public.ml_ys_log_gmt_test"
Column | Type | Modifiers
| Storage | Description
------------------------+--------------------------+---------------------------------+----------+-------------
ts | numeric |
| main |
Then I run the following command
~/$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' --verbose
--batch
12/11/07 17:25:32 INFO mapred.JobClient: Task Id :
attempt_201211071722_0002_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', '1351716176767',
'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)
I have attached ml_ys_log_gmt_test_with_map_column_java_ts_numeric.java
Please note that ts = '1351716176767' in the INSERT clause. The value seems
right. But since the definition of the column is numeric, the quotes (')
surrounding 1351716176767 should not be there.
Thank you for your help!
Matthieu
On Tue, Nov 6, 2012 at 2:33 PM, Jarek Jarcec Cecho <ja...@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 | *Action**X* |
584 Broadway, Suite 1002 – NY, NY 10012
415-994-3480 (m)
Re: Sqoop exporting error
Posted by Matthieu Labour <ma...@actionx.com>.
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 <ja...@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 | *Action**X* |
584 Broadway, Suite 1002 – NY, NY 10012
415-994-3480 (m)
Re: Sqoop exporting error
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
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 <ja...@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)
> >
Re: Sqoop exporting error
Posted by Matthieu Labour <ma...@actionx.com>.
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 <ja...@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)
>
Re: Sqoop exporting error
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
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)