Now import data from mysql table departments_hive01 to this hive table. Please make sure that data should be visible using below hive command. Also, while importing if null value found for department_name column replace it with "" (empty string) and for id column with -999 select * from departments_hive;

Now import data from mysql table departments_hive01 to this hive table. Please make sure that data should be visible using below hive command. Also, while importing if null value found for department_name column replace it with "" (empty string) and for id column with -999 select * from departments_hive;

View Answer

Answer: Solution:

Step 1: Create hive table as below.

hive

show tables;

create table departments_hive01(department_id int, department_name string, avgsalary int);

Step 2: Create table in mysql db as well.

mysql -user=retail_dba -password=cloudera

use retail_db

CREATE TABLE IF NOT EXISTS departments_hive01(id int, department_name varchar(45), avg_salary int);

show tables;

step 3: Insert data in mysql table.

insert into departments_hive01 select a.*, null from departments a;

check data inserts

select’ from departments_hive01;

Now iserts null records as given in problem. insert into departments_hive01 values(777, "Not known", 1000); insert into departments_hive01 values(8888, null, 1000); insert into departments_hive01 values(666, null, 1100);

Step 4: Now import data in hive as per requirement.

sqoop import

-connect jdbc:mysql://quickstart:3306/retail_db

~username=retail_dba

–password=cloudera

-table departments_hive01

–hive-home /user/hive/warehouse

–hive-import

-hive-overwrite

-hive-table departments_hive0l

–fields-terminated-by ‘01’

–null-string M"

–null-non-strlng -999

-split-by id

-m 1

Step 5: Checkthe data in directory.

hdfs dfs -Is /user/hive/warehouse/departments_hive01

hdfs dfs -cat/user/hive/warehouse/departments_hive01/part"

Check data in hive table.

Select * from departments_hive01;