Lab 1: Sqoop, Flume and Oozie Hands-on
Solution 1:
-- Task 1: Open Terminal and Create a employee.csv file in current directory. $ sudo gedit employee.csv -- Task 2: Paste the file content in the newly created "employee.csv" file and save the file and exit. /* ---select from below--- roll_number,name,city,country,salary 001,Harshit,Mumbai,India,10000 002,John,Chennai,India,80000 003,Ram,Mumbai,India,14000 004,Sujit,Mumbai,India,12000 005,Deepali,Mumbai,India,24000 006,Faheda,Chennai,India,42000 007,Rahul,Hyderabad,India,88777 008,Avanthika,Banglore,India,100000 009,Sunny,Bnaglore,India,64000 ---Copy till above-- */ -- Task 3: Lab will not allow to read data from any directory as only single path is given for read/write the file. -- So need to copy the file from the current directory to executable directory. user1@kq9:/challenge$ sudo cp employee.csv /var/lib/mysql-files/ user1@kq9:/challenge$ sudo ls /var/lib/mysql-files/ -- employee.csv -- Task 4: Open MySql in the Terminal and provide the credentials given in "Desktop->Readme.txt" file. user1@kq9:/challenge$ mysql -u root -p -- Task 5: Create a new database in Mysql. mysql > create database temp; mysql > use temp; -- To check the allowed read/write directory, run the command: mysql> SHOW VARIABLES LIKE "secure_file_priv"; -- output: /var/lib/mysql-files/ -- Note: if the path is not showing same in your Lab Environment and if it is changed in you Lab Env,
then you have to change the path in Task 3 as well, otherwise few error might be occure.
And if it is showing same then no need to do anything. -- Task 6: Create a new table "emp" in Mysql where data will imported from the CSV file. mysql> create table emp(roll_number int ,name varchar(20) ,city varchar(20) ,country varchar(20) ,salary int ); -- Task 7: Import the data from the CSV file to the mysql Table 'emp': mysql> LOAD DATA INFILE '/var/lib/mysql-files/employee.csv' INTO TABLE emp FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; -- Task 8: After inserting the data in the table, we can confirm by running: mysql> select * from emp; --Task 9: Now, open a new Terminal and run the below command with a valid mysql credentials provided in the 'Desktop->Readme.txt' file: user1@kq9:/challenge$ sqoop import \ --connect jdbc:mysql://localhost/temp \ --username root \ --password labpass123 \ --table emp --m 1; user1@kq9:/challenge$ --Task 10: If last step run successfully without query termination due to any error, it means Lab completed successfully. -- You can click submit button (top-right hand side) and close the Lab. --In case you are getting any error, you can write below in the comment box.
