EX: NO: 7 PRACTICE IMPORTING AND EXPORTING DATA FROM
DATE: DATABASES
AIM: To install Sqoop and execute basic commands of Hadoop ecosystem component Sqoop.
1. Sqoop – IMPORT Command:
Import command is used to importing a table from relational databases to HDFS. In our case,
we are going to import tables from MySQL databases to HDFS. As you can see in the below image,
we have employees table in the employees database which we will be importing into HDFS
The command for importing table is:
sqoop import --connect jdbc:mysql://localhost/employees --username edureka --table employ
As you can see in the below image, after executing this command Map tasks will be executed
at the back end. After the code is executed, you can check the Web UI of HDFS i.e.localhost:50070
where the data is imported.
sqoop import --connect jdbc:mysql://localhost/employees --username edureka –tab
Sqoop imports data in parallel from most database sources. -m property is used to specify the
number of mappers to be executed. Sqoop imports data in parallel from most database sources. You
can specify the number of map tasks (parallel processes) to use to perform the import by using the -
m or –num-mappers argument. Each of these arguments takes an integer value which corresponds to
the degree of parallelism to employ.
You can see in the below image, that the number of mapper task is 1. The number of files that are
created while importing MySQL tables is equal to the number of mapper created.
36
after executing this command Map tasks will be executed at the back end. The Web UI of HDFS i.e.
localhost:50070 where the data is imported.
The number of mapper tasks is 1. The number of files that are created while importing MySQL
tables is equal to the number of mapper created.
37
Sqoop – IMPORT Command with Where Clause
Import a subset of a table using the ‘where’ clause in Sqoop import tool. It executes the
corresponding SQL query in the respective database server and stores the result in a target directory
in HDFS. You can use the following command to import data with ‘where‘ clause:
sqoop import --connect jdbc:mysql://localhost/employees --username edureka --table
employees --m 3 --where "emp_no >
49000" --target-dir /Latest_Employees
Finally, Sqoop is used to transfer data from RDBMS (relational database management
system) like MySQL and Oracle to HDFS (Hadoop Distributed File System). Big Data Sqoop can
also be used to transform data in Hadoop MapReduce and then export it into RDBMS.
2. Exporting Data from a Database:
a. Using SQL Commands:
You can use SQL commands to export data from your database. Here's a general
example using the SELECT INTO OUTFILE statement:
-- Export data from a table to a CSV file
SELECT column1, column2
INTO OUTFILE '/path/to/exported_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM your_table;
b. Using Database Management Tools:
Most database management systems provide tools that allow you to export data to
various formats. For example, in MySQL, you can use the mysqldump command to export
the entire database or specific tables:
mysqldump -u username -p your_database > export.sql
38
3. Importing Data into a Database:
a. Using SQL Commands:
You can use SQL commands to import data into your database. The LOAD
DATA INFILE statement is commonly used for this purpose:
-- Import data from a CSV file into a table
LOAD DATA INFILE '/path/to/imported_data.csv
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
b. Using Database Management Tools:
Most database management systems provide tools for importing data. In
MySQL, you can use the mysql command to import data:
mysql -u username -p your_database < import.sql
RESULT:
Thus the importing and exporting of data between databases are executed successfully.
39
