Hi All,
I am facing some issues while importing data from multiple tables of Oracle
and Postgres.
Whenever I start the job, it results to error after going in the running
state. Can anyone help me in this.
PFA the job and error details.
Many thanks.
Best regards,
Amanpreet Singh
sqoop:000> show job --jid 307
1 job(s) to show:
Job with id 307 and name pgjob (Enabled: true, Created by root at 5/29/17 10:25
PM, Updated by root at 5/29/17 11:49 PM)
Using link id 699 and Connector id 4
From database configuration
Schema name:
Table name:
Table SQL statement: select
dg.testing.newdata.id,dg.testing.newdata.creditcard,dg.testing.testdata.phone
from dg.testing.newdata,dg.testing.testdata where ${CONDITIONS}
Table column names:
Partition column name: id
Null value allowed for the partition column:
Boundary query:
Throttling resources
Extractors: 1
Loaders: 1
ToJob configuration
Override null value:
Null value:
Output format: TEXT_FILE
Compression format: NONE
Custom compression format:
Output directory: /sqoop
2017-05-29 23:50:07,013 WARN [main]
org.apache.hadoop.metrics2.impl.MetricsConfig: Cannot locate configuration:
tried hadoop-metrics2-maptask.properties,hadoop-metrics2.properties
2017-05-29 23:50:07,275 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Scheduled snapshot period at
10 second(s).
2017-05-29 23:50:07,275 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system
started
2017-05-29 23:50:07,328 INFO [main] org.apache.hadoop.mapred.YarnChild:
Executing with tokens:
2017-05-29 23:50:07,329 INFO [main] org.apache.hadoop.mapred.YarnChild: Kind:
mapreduce.job, Service: job_1494932549864_0004, Ident:
(org.apache.hadoop.mapreduce.security.token.JobTokenIdentifier@4351786b)
2017-05-29 23:50:07,695 INFO [main] org.apache.hadoop.mapred.YarnChild:
Sleeping for 0ms before retrying again. Got null now.
2017-05-29 23:50:08,571 INFO [main] org.apache.hadoop.mapred.YarnChild:
mapreduce.cluster.local.dir for child:
/var/lib/hadoop-yarn/cache/yarn/nm-local-dir/usercache/sqoop2/appcache/application_1494932549864_0004
2017-05-29 23:50:10,448 INFO [main]
org.apache.hadoop.conf.Configuration.deprecation: session.id is deprecated.
Instead, use dfs.metrics.session-id
2017-05-29 23:50:11,478 INFO [main] org.apache.hadoop.mapred.Task: Using
ResourceCalculatorProcessTree : [ ]
2017-05-29 23:50:12,010 INFO [main] org.apache.hadoop.mapred.MapTask:
Processing split: org.apache.sqoop.job.mr.SqoopSplit@79c8173a
2017-05-29 23:50:12,747 INFO [main] org.apache.hadoop.mapred.MapTask: (EQUATOR)
0 kvi 26214396(104857584)
2017-05-29 23:50:12,747 INFO [main] org.apache.hadoop.mapred.MapTask:
mapreduce.task.io.sort.mb: 100
2017-05-29 23:50:12,747 INFO [main] org.apache.hadoop.mapred.MapTask: soft
limit at 83886080
2017-05-29 23:50:12,747 INFO [main] org.apache.hadoop.mapred.MapTask: bufstart
= 0; bufvoid = 104857600
2017-05-29 23:50:12,747 INFO [main] org.apache.hadoop.mapred.MapTask: kvstart =
26214396; length = 6553600
2017-05-29 23:50:12,773 INFO [main] org.apache.hadoop.mapred.MapTask: Map
output collector class = org.apache.hadoop.mapred.MapTask$MapOutputBuffer
2017-05-29 23:50:13,019 INFO [main] org.apache.sqoop.job.mr.SqoopMapper:
Starting progress service
2017-05-29 23:50:13,025 INFO [main] org.apache.sqoop.job.mr.SqoopMapper:
Running extractor class org.apache.sqoop.connector.jdbc.GenericJdbcExtractor
2017-05-29 23:50:13,342 INFO [main]
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor: Using query: select
dg.testing.newdata.id,dg.testing.newdata.creditcard,dg.testing.testdata.phone
from dg.testing.newdata,dg.testing.testdata where 1 <= id AND id <= 10
2017-05-29 23:50:13,402 ERROR [main]
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor: Caught SQLException:
org.postgresql.util.PSQLException: ERROR: column reference "id" is ambiguous
Position: 141
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeQuery(GenericJdbcExecutor.java:67)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:58)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:38)
at org.apache.sqoop.job.mr.SqoopMapper.run(SqoopMapper.java:95)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
2017-05-29 23:50:13,430 INFO [main] org.apache.sqoop.job.mr.SqoopMapper:
Stopping progress service
2017-05-29 23:50:13,432 INFO [main] org.apache.hadoop.mapred.MapTask: Starting
flush of map output
2017-05-29 23:50:13,518 WARN [main] org.apache.hadoop.mapred.YarnChild:
Exception running child : org.apache.sqoop.common.SqoopException:
MAPRED_EXEC_0017:Error occurs during extractor run
at org.apache.sqoop.job.mr.SqoopMapper.run(SqoopMapper.java:99)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: org.apache.sqoop.common.SqoopException:
GENERIC_JDBC_CONNECTOR_0002:Unable to execute the SQL statement
at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeQuery(GenericJdbcExecutor.java:71)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:58)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:38)
at org.apache.sqoop.job.mr.SqoopMapper.run(SqoopMapper.java:95)
... 7 more
Caused by: org.postgresql.util.PSQLException: ERROR: column reference "id" is
ambiguous
Position: 141
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeQuery(GenericJdbcExecutor.java:67)
... 10 more
2017-05-29 23:50:13,557 INFO [main] org.apache.hadoop.mapred.Task: Runnning
cleanup for the task
2017-05-29 23:50:13,672 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Stopping MapTask metrics
system...
2017-05-29 23:50:13,672 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system
stopped.
2017-05-29 23:50:13,673 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system
shutdown complete.sqoop:000> show job --jid 309
1 job(s) to show:
Job with id 309 and name oraclemultiple (Enabled: true, Created by root at
5/31/17 12:18 AM, Updated by root at 5/31/17 2:12 AM)
Using link id 700 and Connector id 4
From database configuration
Schema name:
Table name:
Table SQL statement: select
UDHAM.FIRSTNAME_G_XDGM.PKID,UDHAM.FIRSTNAME_G_XDGM.FNAME,UDHAM.LASTNAME_XDGM.LASTNAME
from UDHAM.FIRSTNAME_G_XDGM, UDHAM.LASTNAME_XDGM where ${CONDITIONS}
Table column names:
Partition column name: PKID
Null value allowed for the partition column:
Boundary query:
Throttling resources
Extractors: 1
Loaders: 1
ToJob configuration
Override null value:
Null value:
Output format: TEXT_FILE
Compression format: NONE
Custom compression format:
Output directory: /sqoop
2017-05-31 02:13:58,086 WARN [main]
org.apache.hadoop.metrics2.impl.MetricsConfig: Cannot locate configuration:
tried hadoop-metrics2-maptask.properties,hadoop-metrics2.properties
2017-05-31 02:13:58,471 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Scheduled snapshot period at
10 second(s).
2017-05-31 02:13:58,471 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system
started
2017-05-31 02:13:58,563 INFO [main] org.apache.hadoop.mapred.YarnChild:
Executing with tokens:
2017-05-31 02:13:58,566 INFO [main] org.apache.hadoop.mapred.YarnChild: Kind:
mapreduce.job, Service: job_1494932549864_0006, Ident:
(org.apache.hadoop.mapreduce.security.token.JobTokenIdentifier@3f9e244f)
2017-05-31 02:13:59,068 INFO [main] org.apache.hadoop.mapred.YarnChild:
Sleeping for 0ms before retrying again. Got null now.
2017-05-31 02:13:59,877 INFO [main] org.apache.hadoop.mapred.YarnChild:
mapreduce.cluster.local.dir for child:
/var/lib/hadoop-yarn/cache/yarn/nm-local-dir/usercache/sqoop2/appcache/application_1494932549864_0006
2017-05-31 02:14:01,808 INFO [main]
org.apache.hadoop.conf.Configuration.deprecation: session.id is deprecated.
Instead, use dfs.metrics.session-id
2017-05-31 02:14:03,181 INFO [main] org.apache.hadoop.mapred.Task: Using
ResourceCalculatorProcessTree : [ ]
2017-05-31 02:14:03,971 INFO [main] org.apache.hadoop.mapred.MapTask:
Processing split: org.apache.sqoop.job.mr.SqoopSplit@13926fbf
2017-05-31 02:14:04,413 INFO [main] org.apache.hadoop.mapred.MapTask: (EQUATOR)
0 kvi 26214396(104857584)
2017-05-31 02:14:04,414 INFO [main] org.apache.hadoop.mapred.MapTask:
mapreduce.task.io.sort.mb: 100
2017-05-31 02:14:04,414 INFO [main] org.apache.hadoop.mapred.MapTask: soft
limit at 83886080
2017-05-31 02:14:04,414 INFO [main] org.apache.hadoop.mapred.MapTask: bufstart
= 0; bufvoid = 104857600
2017-05-31 02:14:04,414 INFO [main] org.apache.hadoop.mapred.MapTask: kvstart =
26214396; length = 6553600
2017-05-31 02:14:04,431 INFO [main] org.apache.hadoop.mapred.MapTask: Map
output collector class = org.apache.hadoop.mapred.MapTask$MapOutputBuffer
2017-05-31 02:14:04,557 INFO [main] org.apache.sqoop.job.mr.SqoopMapper:
Starting progress service
2017-05-31 02:14:04,561 INFO [main] org.apache.sqoop.job.mr.SqoopMapper:
Running extractor class org.apache.sqoop.connector.jdbc.GenericJdbcExtractor
2017-05-31 02:14:05,394 INFO [main]
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor: Using query: select
UDHAM.FIRSTNAME_G_XDGM.PKID,UDHAM.FIRSTNAME_G_XDGM.FNAME,UDHAM.LASTNAME_XDGM.LASTNAME
from UDHAM.FIRSTNAME_G_XDGM, UDHAM.LASTNAME_XDGM where 1 <= PKID AND PKID <=
3774
2017-05-31 02:14:05,701 ERROR [main]
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor: Caught SQLException:
java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at
oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
at
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeQuery(GenericJdbcExecutor.java:67)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:58)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:38)
at org.apache.sqoop.job.mr.SqoopMapper.run(SqoopMapper.java:95)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
2017-05-31 02:14:05,751 INFO [main] org.apache.sqoop.job.mr.SqoopMapper:
Stopping progress service
2017-05-31 02:14:05,754 INFO [main] org.apache.hadoop.mapred.MapTask: Starting
flush of map output
2017-05-31 02:14:05,925 WARN [main] org.apache.hadoop.mapred.YarnChild:
Exception running child : org.apache.sqoop.common.SqoopException:
MAPRED_EXEC_0017:Error occurs during extractor run
at org.apache.sqoop.job.mr.SqoopMapper.run(SqoopMapper.java:99)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: org.apache.sqoop.common.SqoopException:
GENERIC_JDBC_CONNECTOR_0002:Unable to execute the SQL statement
at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeQuery(GenericJdbcExecutor.java:71)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:58)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExtractor.extract(GenericJdbcExtractor.java:38)
at org.apache.sqoop.job.mr.SqoopMapper.run(SqoopMapper.java:95)
... 7 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously
defined
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at
oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
at
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeQuery(GenericJdbcExecutor.java:67)
... 10 more
2017-05-31 02:14:05,955 INFO [main] org.apache.hadoop.mapred.Task: Runnning
cleanup for the task
2017-05-31 02:14:06,063 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Stopping MapTask metrics
system...
2017-05-31 02:14:06,064 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system
stopped.
2017-05-31 02:14:06,066 INFO [main]
org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system
shutdown complete.
