CODEWITHSUNDEEP
mysqlhadoopsqoop
User12345
User12345

Reputation: 5480

sqoop import gives wrong result for a correct sql query

I using a query like below in MySQL . I am getting my desired result.

select TABLE_NAME,count(column_name) as no_of_columns from information_schema.columns where TABLE_SCHEMA = 'testing' and TABLE_NAME NOT REGEXP 'temp|bkup|RemoveMe|test' group by TABLE_NAME

When I use the same query in a sqoop import statement then the results are different.

The sqoop import statement is below.

sqoop import --connect jdbc:mysql://xxxxxx:3306/information_schema --username xxxxx --password-file /user/xxxxx/passwds/mysql.file --query "select TABLE_NAME,count(column_name) as no_of_columns from information_schema.columns where TABLE_SCHEMA = 'testing' and TABLE_NAME NOT REGEXP 'temp|bkup|RemoveMe|test' group by TABLE_NAME and \$CONDITIONS" -m 1 --target-dir /user/hive/warehouse/xxxx.db/testing_columns --outdir /home/xxxxx/logs/outdir

Why is this happening and what should I do in order to get desired result

Upvotes: 0

Views: 227

Answers (1)

danielsepulvedab
danielsepulvedab

Reputation: 674

The $CONDITIONS token must be in the WHERE clause:

sqoop import --connect jdbc:mysql://xxxxxx:3306/information_schema \
    --username xxxxx --password-file /user/xxxxx/passwds/mysql.file \
    --query "select TABLE_NAME,count(column_name) as no_of_columns \ 
               from information_schema.columns \
               where TABLE_SCHEMA = 'testing' \
                 and TABLE_NAME NOT REGEXP 'temp|bkup|RemoveMe|test' \ 
                 and \$CONDITIONS \
               group by TABLE_NAME" \
    -m 1 --target-dir /user/hive/warehouse/xxxx.db/testing_columns \
    --outdir /home/xxxxx/logs/outdir

Also consider that according to the Sqoop User Guide:

The facility of using free-form query in the current version of Sqoop is limited to simple queries where there are no ambiguous projections and no OR conditions in the WHERE clause. Use of complex queries such as queries that have sub-queries or joins leading to ambiguous projections can lead to unexpected results.

Upvotes: 1

Related Questions