sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From vasanth kumar <rj.vasanthku...@gmail.com>
Subject Re: Both table name and SQL cannot be specified
Date Thu, 03 Apr 2014 06:32:46 GMT
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 <suhas.satish@gmail.com> 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 <centerqi@gmail.com> wrote:
>
>> Thank you for your enthusiasm
>> sqoop2 is a great work
>>
>>
>> 2013/12/31 Abraham Elmahrek <abe@cloudera.com>
>>
>>> 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 <centerqi@gmail.com> 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 <centerqi@gmail.com>
>>>>
>>>>> 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 胡齐忠 <huqizhong@koudai.com>
>>>>>
>>>>>>  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 <abe@cloudera.com>
>>>>>>
>>>>>>> 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 <centerqi@gmail.com>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*
>>>>>>>> --
>>>>>>>> centerqi@gmail.com
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> 口袋购物|胡齐忠|18001190059|huqizhong@koudai.com
>>>>>>
>>>>>> --
>>>>>> <huqizhong@koudai.com>centerqi@gmail.com|齐忠
>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> centerqi@gmail.com|齐忠
>>>>
>>>
>>>
>>
>>
>> --
>> centerqi@gmail.com|齐忠
>>
>
>


-- 
Regards
Vasanth kumar RJ

Mime
View raw message