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"







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


thx
--
centerqi@gmail.com




--



--
centerqi@gmail.com|




--
centerqi@gmail.com|