Data Ingestion from mysql to HDFS - simple import

[#1]

Hi,

I am using the sandbox to practice Sqoop. From the SSH shell, I entered to mysql prompt and have created a table customerInfo under the database test.

Now, I have from the command prompt I typed the following command: sqoop import \ --connect jdbc:mysql://localhost/test \ --username root \ --password xxxx \ --table customerInfo --m 1

and got the error : access denied for user 'root'@'localhost'. 1) Could you reply which password should I use? I tried with hadoop. I am using the mysql that comes along with the sandbox.

2) Where can I see the port configuration as mentioned in: https://alexeikh.wordpress.com/2012/05/03/using-sqoop-for-moving-data-between-hadoop-and-sql-server/

Thank you.

[#2]

Sqoop-env file looks like below:(Should this be changed as per https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.4/bk_installing_manually_book/content/set_up_...

export HADOOP_HOME=${HADOOP_HOME:-{{hadoop_home}}} export HBASE_HOME=${HBASE_HOME:-{{hbase_home}}} export HIVE_HOME=${HIVE_HOME:-{{hive_home}}} export ZOOCFGDIR=${ZOOCFGDIR:-/etc/zookeeper/conf} export SQOOP_USER_CLASSPATH="`ls ${HIVE_HOME}/lib/libthrift-*.jar 2> /dev/null`:${SQOOP_USER_CLASSPATH}" export SQOOP_CONF_DIR="/usr/hdp/current/sqoop-server/conf" ----> I added this line

[#3]

Finally the below step has helped:(taken from the previous questions)

hdfs dfs -chown -R root:hdfs /user/root 

sqoop import --connect jdbc:mysql://sandbox.hortonworks.com:3306/test --username root --table customerInfo --driver com.mysql.jdbc.Driver --m 1

It is working fine. But, where to find the hostname "sandbox.hortonworks.com:3306" that should be used.

Thank you.

[#4]

MySQL on the sandbox has no password for the root login by default. To set up a root password for the first time, use the mysqladmin command at the shell prompt as follows:

$ mysqladmin -u root password newpass

[#5]

@Tom McCuch my root is denied access to mysql. what should i do to transfer data from sqoop to hdfs

sqoop.png

[#6 · Accepted solution]

run hostname -f from the command line on the sandbox vm, and you should see the following:

[root@sandbox ~]# hostname -f 
sandbox.hortonworks.com

So, assuming you are running sqoop from the same sandbox vm, this fqdn should resolve properly. You can cat the contents of /etc/hosts/ to verify:

[root@sandbox ~]# cat /etc/hosts
# File is generated from /usr/lib/hue/tools/start_scripts/gen_hosts.sh
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 	localhost.localdomain localhost
192.168.59.103 	sandbox.hortonworks.com sandbox ambari.hortonworks.com

If you are running the sqoop command from another host, then you need to make sure the second entry in the sandbox /etc/hosts file is in the /etc/hosts file on that host as well.

Before running the sqoop command, you can test that you can connect to mysql using that hostname from the command line:

[root@sandbox ~]# mysql -h sandbox.hortonworks.com -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

If that command works, then the jdbc connection with the same hostname, username, and password will work.