2

I found a lot of people with the similar error but no tips are working for my problem.

My command line:

sqoop import --connect jdbc:mysql://localhost/databaseY --username=root -P --table tableX --target-dir /user/ec2-user/databaseY/tableX --as-textfile --fields-terminated-by "\t"

The error

16/08/19 11:25:51 INFO mapreduce.Job: Job job_1471608424445_0028 running in uber mode : false
16/08/19 11:25:51 INFO mapreduce.Job:  map 0% reduce 0%
16/08/19 11:25:58 INFO mapreduce.Job:  map 25% reduce 0%
16/08/19 11:26:04 INFO mapreduce.Job:  map 50% reduce 0%
16/08/19 11:26:06 INFO mapreduce.Job: Task Id :     attempt_1471608424445_0028_m_000000_0, Status : FAILED
Error: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

How it is possible that the map start and stop after due to this error?

It looks like I have all the right needed as this two command line are working:

sqoop list-databases --connect jdbc:mysql://localhost --username root -P

And

On mysql with root account I can do

select * from databaseY.tableX

---EDIT---

This command line is working:

sqoop import --connect jdbc:mysql://localhost/databaseY --username root --password PASSWORD --query "select * from databaseY.tableX where number = 1474 AND \$CONDITIONS" --target-dir /tmp/ok --as-textfile --direct --split-by number

but this one no:

sqoop import --connect jdbc:mysql://localhost/databaseY --username root --password PASSWORD --query "select * from databaseY.tableX where 1 = 1 AND \$CONDITIONS" --target-dir /tmp/ok --as-textfile --direct --split-by number

Then, I realized that if I use -m 1 my sqoop import is working. Only with -m 1

Does it mean I have a bad cluster configuration? Why my job is working only with one map task?

----- SOLUTION -----

It was simply a problem of the IP address. I changed localhost by the IP address and it is working fine now.

Selverine
  • 57
  • 2
  • 8

4 Answers4

2

That's how Sqoop works. See the official doc.

If a table does not have a primary key defined and the --split-by is not provided, then import will fail unless the number of mappers is explicitly set to one with the --num-mappers 1 option or the --autoreset-to-one-mapper option is used. The option --autoreset-to-one-mapper is typically used with the import-all-tables tool to automatically handle tables without a primary key in a schema.

kentaro
  • 1,249
  • 2
  • 13
  • 19
0

The username and password here specifies the user and password for the database you are trying connect. The username and password here is not for mysql login user. So use the username and password for the user specified for the database you are trying to connect.

KayV
  • 12,987
  • 11
  • 98
  • 148
0

Late but not least, I think I am going to give the answer part here in this thread

similar issue I were facing with sqoop in importing the table data from host windows machine to virtual box guest ubuntu os, just because of my windows host machine installed mysql server username doesn't have sufficient privileges for granting connection and schema over remote request.

So, now not talking much on issue I am illustrating the issue I were facing -

vm4learning@vm4learning:~/Installations/sqoop-1.4.4$ bin/sqoop import --connect jdbc:mysql://192.168.56.1/india --table india_most_populated_cities --target-dir /user/vm4learning/remotedir/ --username root --password password -m 1
no main manifest attribute, in /home/vm4learning/Installations/hbase-0.94.14/lib/coprocessor.jar
find: paths must precede expression: sqoop-test-1.4.4.jar
Usage: find [-H] [-L] [-P] [-Olevel] [-D help|tree|search|stat|rates|opt|exec] [path...] [expression]
Warning: $HADOOP_HOME is deprecated.

17/12/06 11:01:10 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/12/06 11:01:11 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/12/06 11:01:11 INFO tool.CodeGenTool: Beginning code generation
17/12/06 11:01:13 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Access denied for user 'root'@'Administrator' (using password: YES)
java.sql.SQLException: Access denied for user 'root'@'Administrator' (using password: YES)

Then, I came to my windows host machine, run ipconfig from cmd and first get the ipaddress of the host machine and in linux(ubuntu) it is "ifconfig":

Ethernet adapter VirtualBox Host-Only Network:

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : fe80::54d4:4f16:4bdb:885%18
   IPv4 Address. . . . . . . . . . . : 192.168.56.1
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :

So here my ip address is 192.168.56.1 in windows host machine and then I login into mysql through command in windows host and run below said scripts for granting the privilege -

mysql -u root -p Enter password: ********

mysql> GRANT ALL ON . TO root@'192.168.56.1' IDENTIFIED BY 'password'; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql> exit

that's it, and then I go the guest ubuntu machine inside virtual box (by doing this my windows were connected with wifi internet) and re-run the same above said sqoop command and that imports my desired table data to the cluster.

I would like to share the lines I got after successfully script run -

vm4learning@vm4learning:~/Installations/sqoop-1.4.4$ bin/sqoop import --connect jdbc:mysql://192.168.56.1/india --table india_most_populated_cities --target-dir /user/vm4learning/remotedir/ --username root --password password -m 1
no main manifest attribute, in /home/vm4learning/Installations/hbase-0.94.14/lib/coprocessor.jar
find: paths must precede expression: sqoop-test-1.4.4.jar
Usage: find [-H] [-L] [-P] [-Olevel] [-D help|tree|search|stat|rates|opt|exec] [path...] [expression]
Warning: $HADOOP_HOME is deprecated.

17/12/06 17:00:55 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/12/06 17:00:55 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/12/06 17:00:55 INFO tool.CodeGenTool: Beginning code generation
17/12/06 17:00:58 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `india_most_populated_cities` AS t LIMIT 1
17/12/06 17:00:58 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `india_most_populated_cities` AS t LIMIT 1
17/12/06 17:00:58 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/vm4learning/Installations/hadoop-1.2.1
Note: /tmp/sqoop-vm4learning/compile/fc8e526de8f7a74171941455a22f573f/india_most_populated_cities.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/12/06 17:01:02 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-vm4learning/compile/fc8e526de8f7a74171941455a22f573f/india_most_populated_cities.jar
17/12/06 17:01:02 WARN manager.MySQLManager: It looks like you are importing from mysql.
17/12/06 17:01:02 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
17/12/06 17:01:02 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
17/12/06 17:01:02 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
17/12/06 17:01:02 INFO mapreduce.ImportJobBase: Beginning import of india_most_populated_cities
17/12/06 17:01:06 INFO mapred.JobClient: Running job: job_201712061003_0002
17/12/06 17:01:07 INFO mapred.JobClient:  map 0% reduce 0%
17/12/06 17:01:29 INFO mapred.JobClient:  map 100% reduce 0%
17/12/06 17:01:36 INFO mapred.JobClient: Job complete: job_201712061003_0002
17/12/06 17:01:37 INFO mapred.JobClient: Counters: 18
17/12/06 17:01:37 INFO mapred.JobClient:   Job Counters 
17/12/06 17:01:37 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=24525
17/12/06 17:01:37 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
17/12/06 17:01:37 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
17/12/06 17:01:37 INFO mapred.JobClient:     Launched map tasks=1
17/12/06 17:01:37 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
17/12/06 17:01:37 INFO mapred.JobClient:   File Output Format Counters 
17/12/06 17:01:37 INFO mapred.JobClient:     Bytes Written=10406
17/12/06 17:01:37 INFO mapred.JobClient:   FileSystemCounters
17/12/06 17:01:37 INFO mapred.JobClient:     HDFS_BYTES_READ=87
17/12/06 17:01:37 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=80423
17/12/06 17:01:37 INFO mapred.JobClient:     HDFS_BYTES_WRITTEN=10406
17/12/06 17:01:37 INFO mapred.JobClient:   File Input Format Counters 
17/12/06 17:01:37 INFO mapred.JobClient:     Bytes Read=0
17/12/06 17:01:37 INFO mapred.JobClient:   Map-Reduce Framework
17/12/06 17:01:37 INFO mapred.JobClient:     Map input records=271
17/12/06 17:01:37 INFO mapred.JobClient:     Physical memory (bytes) snapshot=81043456
17/12/06 17:01:37 INFO mapred.JobClient:     Spilled Records=0
17/12/06 17:01:37 INFO mapred.JobClient:     CPU time spent (ms)=2530
17/12/06 17:01:37 INFO mapred.JobClient:     Total committed heap usage (bytes)=49807360
17/12/06 17:01:37 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=973082624
17/12/06 17:01:37 INFO mapred.JobClient:     Map output records=271
17/12/06 17:01:37 INFO mapred.JobClient:     SPLIT_RAW_BYTES=87
17/12/06 17:01:37 INFO mapreduce.ImportJobBase: Transferred 10.1621 KB in 33.9572 seconds (306.4444 bytes/sec)
17/12/06 17:01:37 INFO mapreduce.ImportJobBase: Retrieved 271 records.

Hope it will help you and also bring my post the answer of this thread, thanks.

ArifMustafa
  • 4,617
  • 5
  • 40
  • 48
0

Follow this two

GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY '%password%' WITH GRANT OPTION;

Even after u face an error try this

https://stackoverflow.com/a/70723186/6236959

Shubham Kumar Gupta
  • 638
  • 8
  • 28