Yanting, Also, it seems like the schema you've provided is for an Oracle database. i.e. VARCHAR2 and NUMBER are datatypes specific to Oracle. Could you please use an oracle connection string and driver? i.e. oracle.jdbc.driver.OracleDriver and jdbc:oracle:thin:@host:port:SID.
-abe On Wed, Sep 4, 2013 at 7:32 PM, Mengwei Ding <[email protected]> wrote: > Hmm... would you mind showing us your most updated job configuration by > typing "show job --jid 3"? I just want to make sure that you provide the > partition column correctly. > > Also, I notice that the primary key for this table is "VARCHAR(23)" type, > this might be the problem. > > Mengwei > > > On Wed, Sep 4, 2013 at 10:23 PM, Yanting Chen <[email protected]>wrote: > >> Hi Mengwei >> >> I try to set primary key into partition column. But still the same error! >> >> >> On Thu, Sep 5, 2013 at 10:17 AM, Mengwei Ding <[email protected]>wrote: >> >>> Hi Yanting, >>> >>> It seems like you did not specify the 'partition column' for the job. >>> Generally, the primary key of the table is a good choice for 'parition >>> column'. >>> >>> You could use 'update job -jid 3' to update the job configuration. >>> >>> Mengwei >>> >>> >>> On Wed, Sep 4, 2013 at 9:37 PM, Yanting Chen <[email protected]>wrote: >>> >>>> Abraham, >>>> >>>> Thanks for you answer. I review my database. >>>> I think the database name is invoice and the schema name is public, >>>> just like the picture below. >>>> http://imgur.com/ns0iNLi >>>> >>>> So, I change the schema name with a new value, "public" >>>> Then, I run this job and get a different error. >>>> >>>> Status: FAILURE_ON_SUBMIT >>>> Creation date: 2013-09-05 09:30:44 CST >>>> Last update date: 2013-09-05 09:30:44 CST >>>> Exception: org.apache.sqoop.common.SqoopException: >>>> GENERIC_JDBC_CONNECTOR_0011:The type is not supported - 12 >>>> Stack trace: org.apache.sqoop.common.SqoopException: >>>> GENERIC_JDBC_CONNECTOR_0011:The type is not supported - 12 >>>> at >>>> org.apache.sqoop.connector.jdbc.GenericJdbcImportPartitioner.getPartitions(GenericJdbcImportPartitioner.java:87) >>>> at >>>> org.apache.sqoop.connector.jdbc.GenericJdbcImportPartitioner.getPartitions(GenericJdbcImportPartitioner.java:32) >>>> at >>>> org.apache.sqoop.job.mr.SqoopInputFormat.getSplits(SqoopInputFormat.java:71) >>>> at >>>> org.apache.hadoop.mapreduce.JobSubmitter.writeNewSplits(JobSubmitter.java:452) >>>> at >>>> org.apache.hadoop.mapreduce.JobSubmitter.writeSplits(JobSubmitter.java:469) >>>> at >>>> org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:366) >>>> at org.apache.hadoop.mapreduce.Job$11.run(Job.java:1269) >>>> at org.apache.hadoop.mapreduce.Job$11.run(Job.java:1266) >>>> 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:1408) >>>> at org.apache.hadoop.mapreduce.Job.submit(Job.java:1266) >>>> at >>>> org.apache.sqoop.submission.mapreduce.MapreduceSubmissionEngine.submit(MapreduceSubmissionEngine.java:265) >>>> at >>>> org.apache.sqoop.framework.FrameworkManager.submit(FrameworkManager.java:480) >>>> at >>>> org.apache.sqoop.handler.SubmissionRequestHandler.submissionSubmit(SubmissionRequestHandler.java:112) >>>> at >>>> org.apache.sqoop.handler.SubmissionRequestHandler.handleActionEvent(SubmissionRequestHandler.java:98) >>>> at >>>> org.apache.sqoop.handler.SubmissionRequestHandler.handleEvent(SubmissionRequestHandler.java:68) >>>> at >>>> org.apache.sqoop.server.v1.SubmissionServlet.handlePostRequest(SubmissionServlet.java:44) >>>> at >>>> org.apache.sqoop.server.SqoopProtocolServlet.doPost(SqoopProtocolServlet.java:63) >>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) >>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) >>>> at >>>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) >>>> at >>>> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) >>>> at >>>> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) >>>> at >>>> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) >>>> at >>>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) >>>> at >>>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) >>>> at >>>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) >>>> at >>>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) >>>> at >>>> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859) >>>> at >>>> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602) >>>> at >>>> org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) >>>> at java.lang.Thread.run(Thread.java:724) >>>> >>>> Also, I try to remove the schema name, but I get the same error as >>>> above. >>>> >>>> On Thu, Sep 5, 2013 at 6:11 AM, Abraham Elmahrek <[email protected]> >>>> wrote: >>>> >>>> Yanting, >>>>> >>>>> I'm sorry, I'm a bit confused. The database you are using here is >>>>> called "invoice". Do you also have a schema called "invoice"? Try removing >>>>> the "Schema name" from your job and see if that works for you. >>>>> >>>>> http://www.postgresql.org/docs/9.1/static/ddl-schemas.html >>>>> >>>>> -Abe >>>>> >>>>> >>>>> On Tue, Sep 3, 2013 at 6:34 PM, Yanting Chen <[email protected]> >>>>> wrote: >>>>> >>>>>> However, with Sqoop 1.4.4, all schema and table names are lower-case, >>>>>> and it is successful to make import. >>>>>> >>>>>> As a result, I am not sure if case sensitivity will be a problem in >>>>>> Sqoop 2. >>>>>> >>>>>> *This is the connection I created:* >>>>>> 1 connection(s) to show: >>>>>> Connection with id 1 and name ds_msg_log (Created 9/2/13 10:54 AM, >>>>>> Updated 9/3/13 6:35 PM) >>>>>> Using Connector id 1 >>>>>> Connection configuration >>>>>> JDBC Driver Class: org.postgresql.Driver >>>>>> JDBC Connection String: jdbc:postgresql://MY_IP:MY_PORT/invoice >>>>>> Username: hadoop >>>>>> Password: >>>>>> JDBC Connection Properties: >>>>>> Security related configuration options >>>>>> Max connections: 123 >>>>>> >>>>>> *This is the job I created:* >>>>>> Job with id 3 and name ds_msg_log (Created 9/2/13 11:37 AM, Updated >>>>>> 9/3/13 5:57 PM) >>>>>> Using Connection id 1 and Connector id 1 >>>>>> Database configuration >>>>>> Schema name: invoice >>>>>> Table name: ds_msg_log >>>>>> Table SQL statement: >>>>>> Table column names: >>>>>> Partition column name: >>>>>> Boundary query: >>>>>> Output configuration >>>>>> Storage type: HDFS >>>>>> Output format: TEXT_FILE >>>>>> Output directory: /user/root/ds_msg_log_sqoop2 >>>>>> Throttling resources >>>>>> Extractors: >>>>>> Loaders: >>>>>> >>>>>> *The command I use to start this job is:* >>>>>> submission start --jid 3 >>>>>> >>>>>> >>>>>> On Wed, Sep 4, 2013 at 9:24 AM, Abraham Elmahrek <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> Sorry, I missed that you are using PostGreSQL. I think case >>>>>>> sensitivity will be a problem with that database. >>>>>>> >>>>>>> >>>>>>> On Tue, Sep 3, 2013 at 6:23 PM, Abraham Elmahrek <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>>> What database are you using? Are table names or schema names case >>>>>>>> sensitive in your database? Sqoop quotes all names, so case sensitivity >>>>>>>> makes a difference here. >>>>>>>> >>>>>>>> Also, could you provide the corresponding Sqoop 1.4.4 command that >>>>>>>> worked for you? >>>>>>>> >>>>>>>> -Abe >>>>>>>> >>>>>>>> >>>>>>>> On Tue, Sep 3, 2013 at 6:06 PM, 陳彥廷(Yanting Chen) < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> I am pretty sure that the schema "invoice" exists because I >>>>>>>>> successfully imported data to this schema using Sqoop 1.4.4 >>>>>>>>> >>>>>>>>> Also, I already remove "*" when creating job. >>>>>>>>> >>>>>>>>> The following is the table in invoice schema: >>>>>>>>> TABLE DS_MSG_LOG >>>>>>>>> ( >>>>>>>>> MESSAGE_ID VARCHAR2(23) NOT NULL, >>>>>>>>> LOGIN_ID VARCHAR2(30), >>>>>>>>> ACPT_DTS TIMESTAMP(6), >>>>>>>>> DLVR_DTS TIMESTAMP(6), >>>>>>>>> SENDER_ID VARCHAR2(30), >>>>>>>>> SENDER_VAC_ID VARCHAR2(39), >>>>>>>>> RECEIVER_ID VARCHAR2(30), >>>>>>>>> RECEIVER_VAC_ID VARCHAR2(39), >>>>>>>>> STATUS VARCHAR2(1), >>>>>>>>> MESSAGE_TYPE VARCHAR2(8), >>>>>>>>> FLOW_TYPE VARCHAR2(5), >>>>>>>>> SERVICE_TYPE VARCHAR2(1), >>>>>>>>> SOURCE_FILE_NAME VARCHAR2(150), >>>>>>>>> ARCHIVE_FILE_NAME VARCHAR2(250), >>>>>>>>> ARCHIVE_CHAR_COUNT NUMBER, >>>>>>>>> DECRYPT_FILE_NAME VARCHAR2(250), >>>>>>>>> DECRYPT_CHAR_COUNT NUMBER, >>>>>>>>> RESP_FILE_NAME VARCHAR2(250), >>>>>>>>> RESP_CHAR_COUNT NUMBER, >>>>>>>>> RESP_FLAG VARCHAR2(1), >>>>>>>>> RTG_SEQ VARCHAR2(8), >>>>>>>>> RESENT_FLAG VARCHAR2(1) DEFAULT 'N', >>>>>>>>> TOTAL_INV_COUNT NUMBER, >>>>>>>>> CONSTRAINT PK_DS_MSG_LOG PRIMARY KEY (MESSAGE_ID) >>>>>>>>> ) >>>>>>>>> >>>>>>>>> >>>>>>>>> On Wed, Sep 4, 2013 at 1:46 AM, Abraham Elmahrek <[email protected] >>>>>>>>> > wrote: >>>>>>>>> >>>>>>>>>> Hey User, >>>>>>>>>> >>>>>>>>>> It looks like the schema "invoice" does not exist in your >>>>>>>>>> database. Could you please provide your database schema? Also, "*" is >>>>>>>>>> unnecessary when specifying "Table column names". If you leave it >>>>>>>>>> blank it >>>>>>>>>> will import all columns by default. >>>>>>>>>> >>>>>>>>>> -Abe >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Tue, Sep 3, 2013 at 3:03 AM, 陳彥廷(Yanting Chen) < >>>>>>>>>> [email protected]> wrote: >>>>>>>>>> >>>>>>>>>>> According to the official guide, >>>>>>>>>>> http://sqoop.apache.org/docs/1.99.2/Sqoop5MinutesDemo.html , I >>>>>>>>>>> successfully created a job. >>>>>>>>>>> >>>>>>>>>>> However, when I executed the command, submission start --jid 1, >>>>>>>>>>> I got this error message: "Exception has occurred during processing >>>>>>>>>>> command >>>>>>>>>>> Server has returned exception: Exception: java.lang.Throwable >>>>>>>>>>> Message: >>>>>>>>>>> GENERIC_JDBC_CONNECTOR_0002:Unable to execute the SQL statement" >>>>>>>>>>> >>>>>>>>>>> This is the information of my job. >>>>>>>>>>> >>>>>>>>>>> Database configuration >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Schema name: invoice >>>>>>>>>>> Table name: ds_msg_log >>>>>>>>>>> Table SQL statement: >>>>>>>>>>> Table column names: * >>>>>>>>>>> Partition column name: >>>>>>>>>>> Boundary query: >>>>>>>>>>> >>>>>>>>>>> Output configuration >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Storage type: HDFS >>>>>>>>>>> Output format: TEXT_FILE >>>>>>>>>>> Output directory: /user/root/ds_msg_log >>>>>>>>>>> >>>>>>>>>>> Throttling resources Extractors: Loaders: >>>>>>>>>>> >>>>>>>>>>> Since there is no information in the official guide talking >>>>>>>>>>> about how to set the values above, does any know anything wrong in >>>>>>>>>>> my job >>>>>>>>>>> setting? >>>>>>>>>>> >>>>>>>>>>> This is the log: Stack trace: at >>>>>>>>>>> org.apache.sqoop.connector.jdbc.GenericJdbcExecutor >>>>>>>>>>> (GenericJdbcExecutor.java:59) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer >>>>>>>>>>> (GenericJdbcImportInitializer.java:155) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer >>>>>>>>>>> (GenericJdbcImportInitializer.java:48) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer >>>>>>>>>>> (GenericJdbcImportInitializer.java:37) >>>>>>>>>>> at org.apache.sqoop.framework.FrameworkManager >>>>>>>>>>> (FrameworkManager.java:447) >>>>>>>>>>> at org.apache.sqoop.handler.SubmissionRequestHandler >>>>>>>>>>> (SubmissionRequestHandler.java:112) >>>>>>>>>>> at org.apache.sqoop.handler.SubmissionRequestHandler >>>>>>>>>>> (SubmissionRequestHandler.java:98) >>>>>>>>>>> at org.apache.sqoop.handler.SubmissionRequestHandler >>>>>>>>>>> (SubmissionRequestHandler.java:68) >>>>>>>>>>> at org.apache.sqoop.server.v1.SubmissionServlet >>>>>>>>>>> (SubmissionServlet.java:44) >>>>>>>>>>> at org.apache.sqoop.server.SqoopProtocolServlet >>>>>>>>>>> (SqoopProtocolServlet.java:63) >>>>>>>>>>> at javax.servlet.http.HttpServlet (HttpServlet.java:637) >>>>>>>>>>> at javax.servlet.http.HttpServlet (HttpServlet.java:717) >>>>>>>>>>> at org.apache.catalina.core.ApplicationFilterChain >>>>>>>>>>> (ApplicationFilterChain.java:290) >>>>>>>>>>> at org.apache.catalina.core.ApplicationFilterChain >>>>>>>>>>> (ApplicationFilterChain.java:206) >>>>>>>>>>> at org.apache.catalina.core.StandardWrapperValve >>>>>>>>>>> (StandardWrapperValve.java:233) >>>>>>>>>>> at org.apache.catalina.core.StandardContextValve >>>>>>>>>>> (StandardContextValve.java:191) >>>>>>>>>>> at org.apache.catalina.core.StandardHostValve >>>>>>>>>>> (StandardHostValve.java:127) >>>>>>>>>>> at org.apache.catalina.valves.ErrorReportValve >>>>>>>>>>> (ErrorReportValve.java:102) >>>>>>>>>>> at org.apache.catalina.core.StandardEngineValve >>>>>>>>>>> (StandardEngineValve.java:109) >>>>>>>>>>> at org.apache.catalina.connector.CoyoteAdapter >>>>>>>>>>> (CoyoteAdapter.java:293) >>>>>>>>>>> at org.apache.coyote.http11.Http11Processor >>>>>>>>>>> (Http11Processor.java:859) >>>>>>>>>>> at >>>>>>>>>>> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler >>>>>>>>>>> (Http11Protocol.java:602) >>>>>>>>>>> at org.apache.tomcat.util.net.JIoEndpoint$Worker >>>>>>>>>>> (JIoEndpoint.java:489) >>>>>>>>>>> at java.lang.Thread (Thread.java:724) >>>>>>>>>>> Caused by: Exception: java.lang.Throwable Message: ERROR: schema >>>>>>>>>>> "invoice" does not exist Position: 46 Stack trace: at >>>>>>>>>>> org.postgresql.core.v3.QueryExecutorImpl >>>>>>>>>>> (QueryExecutorImpl.java:2102) >>>>>>>>>>> at org.postgresql.core.v3.QueryExecutorImpl >>>>>>>>>>> (QueryExecutorImpl.java:1835) >>>>>>>>>>> at org.postgresql.core.v3.QueryExecutorImpl >>>>>>>>>>> (QueryExecutorImpl.java:257) >>>>>>>>>>> at org.postgresql.jdbc2.AbstractJdbc2Statement >>>>>>>>>>> (AbstractJdbc2Statement.java:500) >>>>>>>>>>> at org.postgresql.jdbc2.AbstractJdbc2Statement >>>>>>>>>>> (AbstractJdbc2Statement.java:374) >>>>>>>>>>> at org.postgresql.jdbc2.AbstractJdbc2Statement >>>>>>>>>>> (AbstractJdbc2Statement.java:254) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor >>>>>>>>>>> (GenericJdbcExecutor.java:56) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer >>>>>>>>>>> (GenericJdbcImportInitializer.java:155) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer >>>>>>>>>>> (GenericJdbcImportInitializer.java:48) >>>>>>>>>>> at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer >>>>>>>>>>> (GenericJdbcImportInitializer.java:37) >>>>>>>>>>> at org.apache.sqoop.framework.FrameworkManager >>>>>>>>>>> (FrameworkManager.java:447) >>>>>>>>>>> at org.apache.sqoop.handler.SubmissionRequestHandler >>>>>>>>>>> (SubmissionRequestHandler.java:112) >>>>>>>>>>> at org.apache.sqoop.handler.SubmissionRequestHandler >>>>>>>>>>> (SubmissionRequestHandler.java:98) >>>>>>>>>>> at org.apache.sqoop.handler.SubmissionRequestHandler >>>>>>>>>>> (SubmissionRequestHandler.java:68) >>>>>>>>>>> at org.apache.sqoop.server.v1.SubmissionServlet >>>>>>>>>>> (SubmissionServlet.java:44) >>>>>>>>>>> at org.apache.sqoop.server.SqoopProtocolServlet >>>>>>>>>>> (SqoopProtocolServlet.java:63) >>>>>>>>>>> at javax.servlet.http.HttpServlet (HttpServlet.java:637) >>>>>>>>>>> at javax.servlet.http.HttpServlet (HttpServlet.java:717) >>>>>>>>>>> at org.apache.catalina.core.ApplicationFilterChain >>>>>>>>>>> (ApplicationFilterChain.java:290) >>>>>>>>>>> at org.apache.catalina.core.ApplicationFilterChain >>>>>>>>>>> (ApplicationFilterChain.java:206) >>>>>>>>>>> at org.apache.catalina.core.StandardWrapperValve >>>>>>>>>>> (StandardWrapperValve.java:233) >>>>>>>>>>> at org.apache.catalina.core.StandardContextValve >>>>>>>>>>> (StandardContextValve.java:191) >>>>>>>>>>> at org.apache.catalina.core.StandardHostValve >>>>>>>>>>> (StandardHostValve.java:127) >>>>>>>>>>> at org.apache.catalina.valves.ErrorReportValve >>>>>>>>>>> (ErrorReportValve.java:102) >>>>>>>>>>> at org.apache.catalina.core.StandardEngineValve >>>>>>>>>>> (StandardEngineValve.java:109) >>>>>>>>>>> at org.apache.catalina.connector.CoyoteAdapter >>>>>>>>>>> (CoyoteAdapter.java:293) >>>>>>>>>>> at org.apache.coyote.http11.Http11Processor >>>>>>>>>>> (Http11Processor.java:859) >>>>>>>>>>> at >>>>>>>>>>> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler >>>>>>>>>>> (Http11Protocol.java:602) >>>>>>>>>>> at org.apache.tomcat.util.net.JIoEndpoint$Worker >>>>>>>>>>> (JIoEndpoint.java:489) >>>>>>>>>>> at java.lang.Thread (Thread.java:724) >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>> >> >
