question Re: Data Ingestion from mysql to HDFS - simple import in Support Questions

Data Ingestion from mysql to HDFS - simple import

Eukrev — Sun, 03 Apr 2016 13:12:17 GMT

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.

Re: Data Ingestion from mysql to HDFS - simple import

Eukrev — Sun, 03 Apr 2016 20:12:49 GMT

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_sqoop_configuration.html)

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

Re: Data Ingestion from mysql to HDFS - simple import

Eukrev — Sun, 03 Apr 2016 20:35:14 GMT

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.

Re: Data Ingestion from mysql to HDFS - simple import

tmccuch — Sun, 03 Apr 2016 20:52:00 GMT

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

Re: Data Ingestion from mysql to HDFS - simple import

tmccuch — Sun, 03 Apr 2016 21:07:01 GMT

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.

Re: Data Ingestion from mysql to HDFS - simple import

rp4345 — Mon, 09 Apr 2018 18:43:11 GMT

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

sqoop.png