Export data to MySQL database from HDFS using Sqoop:
- First of all create table in mySql as it is mandatory to available in mySql db.
CREATE TABLE employee (id INT NOT NULL PRIMARY KEY,name VARCHAR(20),deg VARCHAR(20),salary INT,dept VARCHAR(10));
2. Now create a inputEmployee.txt file in local file system through vi Editor.
Like /home/cloudera/Export
Note: No space would be there after comma(,) saperator otherwise file will not export successfully.
1201,sumit,manager,50000,TP
1202,manisha,preader,50000,TP
1203,kalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
1206,satish p,grp des,20000,GR
3. Now place this input file into Hdfs file system by following command:
>hdfs dfs -put inputEmployee.txt /home/cloudera/exportResult/
4. Export this hdfs file into mysql through Sqoop:
>sqoop export --connect jdbc:mysql://localhost/test --username root --password cloudera --table employee --export-dir /home/cloudera/exportResult/
Note: No need to give input file name to export , only directory path is sufficient to fetch the file.
All files would be fetched from this directory one by one to export.
5. Check the records in the table:
>select * form employee;
More Sqoop command:
- List of databases getting from sqoop command:
sqoop list-databases --connect jdbc:mysql://localhost/ --username root -P
2. List of tables getting from sqoop command:
sqoop list-tables --connect jdbc:mysql://localhost/test --username root -P
3. We can also use following command for export:
sqoop export -m 1 –connect jdbc:MySQL://localhost/db1 –username sqoop –password root –table acad –export-dir /sqoop_msql/
To obtain a filtered map, we can use the following option:
–input-fields-terminated-by ‘/t’ –MySQL-delmiters
Where ‘/t’ denotes tab.
By default, Sqoop export creates INSERT statements. If the –update-key argument is stated, UPDATE statements will be created instead.
The -m argument sets the number of map jobs for reading the file splits from HDFS. Each mapper will have its own connection to the MySQL Server.
