Hadoop environment has made our work simpler and we can now insert and work with data effortlessly and quickly.
How about inserting table in Hadoop which already exists in MySQL?
In this blog we will discuss the following:
- Installing MySQL
- Creating a table and inserting values
- Installing Sqoop
- Fetching the table within MySQL using Sqoop
Prerequisite
There’s only one prerequisite:
Running instance of Hadoop in any Linux OS.
Installing MySQL
Steps to install MySQL are as follows:
- Open terminal and follow the command below:
|
1 |
sudo yum install mysql-server |
figure:1
2. If it asks for permission, enter yes.
figure:2
3. Let the download complete.
figure:3
4. Once the download is completed, we will start MySQL services using the command:
|
1 |
sudo service mysql start |
figure:4
5.Enter the mysql shell as root user with the following command:
|
1 |
mysql -u root |
figure:5
Congratulations!!! MySQL is installed in your Linux machine.
Creating a Table and Inserting Values
- Create a database to work with. The syntax is as follows:
|
1 2 3 |
Create database <database name>; use <database name>; |
- To create table and insert values inside table, follow the syntax shown below:
|
1 2 3 4 5 |
create table <table name>(column name1, column name 2); insert into <table name> values(column1 value1, column2 value1); insert into <table name> values(column1 value2, column2 value2); |
figure:6
These commands are compulsory for a local user to have the privileges.
|
1 2 3 4 5 6 7 |
grant all on *.* to '<username>@<hostname>' with grant option; flush privilages; commit; exit; |
figure:7
Installing Sqoop
Steps to install Sqoop are as follows:
- Download Sqoop (1.99.6) from the link:
https://drive.google.com/open?id=0B1QaXx7tpw3SM1ZJUzdJemJjdDQ
- Download the highlighted tar file. Extract it and place it in a secure location.
Note: Location should not be altered.
figure:8
3. Set HOME and PATH inside .bashrc file.
Also run the following command in the terminal for the changes to be affected.
|
1 |
source .bashrc |
4.Search for the mysql-connector-java-5.1.21 in Google. Then download and save it within sqoop/lib directory.
figure:9
5. Come to the terminal to operate Sqoop.
6.Make the changes in .bashrc file with export path for Sqoop home. The path as shown in image below.
figure:10
7. This command will confirm that sqoop is recognized by system.
|
1 |
which sqoop |
figure:11
Congratulations!!! Sqoop is now installed and running in your system.
Fetching the Table Within MySQL Using Sqoop
- The following command will fetch the table created inside MySQL named company:
figure:12
2. To see the result, use the following command which will allow access to HDFS.
figure:13
Hope this blog was useful in giving you an insight on Integrating MySQL with Sqoop.
Leave a Reply