sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suhas Satish <suhas.sat...@gmail.com>
Subject Re: Both table name and SQL cannot be specified
Date Wed, 02 Apr 2014 19:53:58 GMT
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|齐忠
>

Mime
View raw message