Hi Suhas, change your query to:
*SELECT MIN(SQOOP_SUBQUERY_ALIAS.name), MAX(SQOOP_SUBQUERY_ALIAS.name) FROM (select count(name) name from voter where 1 = 1) *SQOOP_SUBQUERY_ALIAS Subquery returns only one value, so min and max will be same value. On Thu, Apr 3, 2014 at 1:23 AM, Suhas Satish <[email protected]> wrote: > Hi there, > I am having trouble with sqoop2 cli in getting a successful job > completion. After following this thread, this is where I am now stuck. Why > is sqoop wrapping around my simple sql construct with > *SQOOP_SUBQUERY_ALIAS.name* ? How do I get rid of it? > > I tried looking for a working example, but wasn't able to find one. > > > *New connection was successfully created with validation status FINE and > persistent id 2* > create job --xid 1 --type import > *New job was successfully created with validation status FINE and > persistent id 6* > > start job --jid 6 > Exception has occurred during processing command > Exception: org.apache.sqoop.common.SqoopException Message: > CLIENT_0001:Server has returned exception > > 2014-04-02 12:44:20,044 DEBUG jdbc.GenericJdbcImportInitializer > [org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer.configurePartitionProperties(GenericJdbcImportInitializer.java:206)] > *Using minMaxQuery: SELECT MIN(SQOOP_SUBQUERY_ALIAS.name), > MAX(SQOOP_SUBQUERY_ALIAS.name) FROM (select count(name) from voter where 1 > = 1) *SQOOP_SUBQUERY_ALIAS > 2014-04-02 12:44:20,046 ERROR server.SqoopProtocolServlet > [org.apache.sqoop.server.SqoopProtocolServlet.doPost(SqoopProtocolServlet.java:68)] > Exception in POST http://localhost:8080/sqoop/v1/submission/action/6 > > *Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: > Unknown column 'SQOOP_SUBQUERY_ALIAS.name' in 'field list'* > > > > This is my job creation configuration in sqoop CLI - > > ------------------------------------------------------------------------------------------------------ > Schema name: > Table name: > Table SQL statement: select count(name) from voter where ${CONDITIONS} > Table column names: name > Partition column name: name > Nulls in partition column: yes > Boundary query: > Output directory: /user/root/sqoop2 > > ------------------------------------------------------------------------------------------------------ > > Here's my mysql database table schema- > > show columns from voter; > +---------------+-------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +---------------+-------------+------+-----+---------+-------+ > | rownum | int(11) | YES | | NULL | | > | name | varchar(50) | YES | | NULL | | > | age | tinyint(4) | YES | | NULL | | > | registration | varchar(15) | YES | | NULL | | > | contributions | float | YES | | NULL | | > | voterzone | smallint(6) | YES | | NULL | | > +---------------+-------------+------+-----+---------+-------+ > > > > > Thanks, > Suhas. > > > On Mon, Dec 30, 2013 at 6:30 PM, centerqi hu <[email protected]> wrote: > >> Thank you for your enthusiasm >> sqoop2 is a great work >> >> >> 2013/12/31 Abraham Elmahrek <[email protected]> >> >>> Glad to hear things are working for you. To briefly answer your >>> question... Normally sqoop will try to use the column with the primary key >>> to split by. free-form queries can take on many shapes and forms (including >>> joins) and can span across multiple tables. As such, Sqoop delegates the >>> responsibility of choosing a column to split by to the user since it cannot >>> know which table has the correct column to partition the data. >>> >>> >>> On Mon, Dec 30, 2013 at 5:30 PM, centerqi hu <[email protected]> wrote: >>> >>>> I found a solution, but one thing does not understand why we must add >>>> the input partition data >>>> >>>> "Caused by: Exception: java.lang.Throwable Message: >>>> GENERIC_JDBC_CONNECTOR_0005: No column is found to partition data" >>>> >>>> https://issues.apache.org/jira/browse/SQOOP-1242 >>>> >>>> https://reviews.apache.org/r/15765/diff/ >>>> >>>> >>>> <https://reviews.apache.org/r/15765/diff/> >>>> >>>> >>>> >>>> 2013/12/31 centerqi hu <[email protected]> >>>> >>>>> Thank you very much for your help. >>>>> When I remove the value entered for "Table name",job creation is a >>>>> success. >>>>> >>>>> However, I get the following error >>>>> >>>>> Exception has occurred during processing command >>>>> >>>>> Exception: org.apache.sqoop.common.SqoopException >>>>> Message:CLIENT_0001:Server >>>>> has returned exception >>>>> >>>>> See server logs, logs inside without any error message, I do not >>>>> understand what this error message means >>>>> >>>>> >>>>> >>>>> Schema name: >>>>> >>>>> Table name: >>>>> >>>>> Table SQL statement: select name from theme where ${CONDITIONS} >>>>> >>>>> Table column names: name >>>>> >>>>> Partition column name: >>>>> >>>>> Nulls in partition column: >>>>> >>>>> Boundary query: >>>>> >>>>> >>>>> Output configuration >>>>> >>>>> >>>>> Storage type: >>>>> >>>>> 0 : HDFS >>>>> >>>>> Choose: 0 >>>>> >>>>> Output format: >>>>> >>>>> 0 : TEXT_FILE >>>>> >>>>> 1 : SEQUENCE_FILE >>>>> >>>>> Choose: 0 >>>>> >>>>> Compression format: >>>>> >>>>> 0 : NONE >>>>> >>>>> 1 : DEFAULT >>>>> >>>>> 2 : DEFLATE >>>>> >>>>> 3 : GZIP >>>>> >>>>> 4 : BZIP2 >>>>> >>>>> 5 : LZO >>>>> >>>>> 6 : LZ4 >>>>> >>>>> 7 : SNAPPY >>>>> >>>>> Choose: 0 >>>>> >>>>> Output directory: /user/rd/tmp/udc/sqoop2/theme_1 >>>>> >>>>> >>>>> Throttling resources >>>>> >>>>> >>>>> Extractors: >>>>> >>>>> Loaders: >>>>> >>>>> New job was successfully created with validation status FINE and >>>>> persistent id 2 >>>>> >>>>> >>>>> 2013/12/31 胡齐忠 <[email protected]> >>>>> >>>>>> Thank you very much for your help. >>>>>> When I remove the value entered for "Table name",job creation is a >>>>>> success. >>>>>> >>>>>> However, I get the following error >>>>>> >>>>>> Exception has occurred during processing command >>>>>> >>>>>> Exception: org.apache.sqoop.common.SqoopException Message: >>>>>> CLIENT_0001:Server >>>>>> has returned exception >>>>>> >>>>>> See server logs, logs inside without any error message, I do not >>>>>> understand what this error message means >>>>>> >>>>>> >>>>>> >>>>>> Schema name: >>>>>> >>>>>> Table name: >>>>>> >>>>>> Table SQL statement: select name from theme where ${CONDITIONS} >>>>>> >>>>>> Table column names: name >>>>>> >>>>>> Partition column name: >>>>>> >>>>>> Nulls in partition column: >>>>>> >>>>>> Boundary query: >>>>>> >>>>>> >>>>>> Output configuration >>>>>> >>>>>> >>>>>> Storage type: >>>>>> >>>>>> 0 : HDFS >>>>>> >>>>>> Choose: 0 >>>>>> >>>>>> Output format: >>>>>> >>>>>> 0 : TEXT_FILE >>>>>> >>>>>> 1 : SEQUENCE_FILE >>>>>> >>>>>> Choose: 0 >>>>>> >>>>>> Compression format: >>>>>> >>>>>> 0 : NONE >>>>>> >>>>>> 1 : DEFAULT >>>>>> >>>>>> 2 : DEFLATE >>>>>> >>>>>> 3 : GZIP >>>>>> >>>>>> 4 : BZIP2 >>>>>> >>>>>> 5 : LZO >>>>>> >>>>>> 6 : LZ4 >>>>>> >>>>>> 7 : SNAPPY >>>>>> >>>>>> Choose: 0 >>>>>> >>>>>> Output directory: /user/rd/tmp/udc/sqoop2/theme_1 >>>>>> >>>>>> >>>>>> Throttling resources >>>>>> >>>>>> >>>>>> Extractors: >>>>>> >>>>>> Loaders: >>>>>> >>>>>> New job was successfully created with validation status FINE and >>>>>> persistent id 2 >>>>>> >>>>>> >>>>>> 2013/12/31 Abraham Elmahrek <[email protected]> >>>>>> >>>>>>> Hey There, >>>>>>> >>>>>>> You should be able to remove the value entered for "Table name". if >>>>>>> it's an empty string, sqoop will validate your query. >>>>>>> >>>>>>> -Abe >>>>>>> >>>>>>> >>>>>>> On Mon, Dec 30, 2013 at 3:06 AM, centerqi hu <[email protected]>wrote: >>>>>>> >>>>>>>> hi all >>>>>>>> >>>>>>>> My sqoop verion is 1.99.3 >>>>>>>> >>>>>>>> I can certainly run the following sql >>>>>>>> >>>>>>>> "SELECT * FROM theme" >>>>>>>> >>>>>>>> However, >>>>>>>> >>>>>>>> When I run Sql "SELECT name from theme WHERE ${CONDITIONS} " get >>>>>>>> the errors:Error message: Both table name and SQL cannot be >>>>>>>> specified >>>>>>>> >>>>>>>> Schema name: >>>>>>>> >>>>>>>> Table name: theme >>>>>>>> >>>>>>>> Table SQL statement: SELECT name from theme WHERE ${CONDITIONS} >>>>>>>> >>>>>>>> Table column names: name >>>>>>>> >>>>>>>> Partition column name: >>>>>>>> >>>>>>>> Nulls in partition column: >>>>>>>> >>>>>>>> Boundary query: >>>>>>>> >>>>>>>> >>>>>>>> Output configuration >>>>>>>> >>>>>>>> >>>>>>>> Storage type: >>>>>>>> >>>>>>>> 0 : HDFS >>>>>>>> >>>>>>>> Choose: 0 >>>>>>>> >>>>>>>> Output format: >>>>>>>> >>>>>>>> 0 : TEXT_FILE >>>>>>>> >>>>>>>> 1 : SEQUENCE_FILE >>>>>>>> >>>>>>>> Choose: 0 >>>>>>>> >>>>>>>> Compression format: >>>>>>>> >>>>>>>> 0 : NONE >>>>>>>> >>>>>>>> 1 : DEFAULT >>>>>>>> >>>>>>>> 2 : DEFLATE >>>>>>>> >>>>>>>> 3 : GZIP >>>>>>>> >>>>>>>> 4 : BZIP2 >>>>>>>> >>>>>>>> 5 : LZO >>>>>>>> >>>>>>>> 6 : LZ4 >>>>>>>> >>>>>>>> 7 : SNAPPY >>>>>>>> >>>>>>>> Choose: 0 >>>>>>>> >>>>>>>> Output directory: /user/rd/tmp/udc/sqoop2/theme_1 >>>>>>>> >>>>>>>> >>>>>>>> Throttling resources >>>>>>>> >>>>>>>> >>>>>>>> Extractors: 1 >>>>>>>> >>>>>>>> Loaders: 1 >>>>>>>> >>>>>>>> >>>>>>>> There are issues with entered data, please revise your input: >>>>>>>> >>>>>>>> Name: theme >>>>>>>> >>>>>>>> >>>>>>>> Database configuration >>>>>>>> >>>>>>>> Error message: Both table name and SQL cannot be specified >>>>>>>> >>>>>>>> This is my reference >>>>>>>> >>>>>>>> *https://cwiki.apache.org/confluence/display/SQOOP/Sqoop2+Quickstart >>>>>>>> <https://cwiki.apache.org/confluence/display/SQOOP/Sqoop2+Quickstart>* >>>>>>>> >>>>>>>> >>>>>>>> *http://sqoop.apache.org/docs/1.99.3/ClientAPI.html >>>>>>>> <http://sqoop.apache.org/docs/1.99.3/ClientAPI.html>* >>>>>>>> >>>>>>>> *thx* >>>>>>>> -- >>>>>>>> [email protected] >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> 口袋购物|胡齐忠|18001190059|[email protected] >>>>>> >>>>>> -- >>>>>> <[email protected]>[email protected]|齐忠 >>>>>> >>>>> >>>> >>>> >>>> -- >>>> [email protected]|齐忠 >>>> >>> >>> >> >> >> -- >> [email protected]|齐忠 >> > > -- Regards Vasanth kumar RJ
