Actually the schema comes from Oracle. However, I try to modify it to let it fit into PostgreSQL database. So, now the database I use is PostgreSQL.
On Thu, Sep 5, 2013 at 10:33 AM, Abraham Elmahrek <[email protected]> wrote: > 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) >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>> >>> >> >
