SQOOP Import Problem and Solution #3

#Problem : 2

Connect to MySQL database using SQOOP,

import all customers whose street name contains "Plaza"

Ex:

Hazy Mountain Plaza

Tawny Fox Plaza

Data description:

A MySQL instance is running on the localhost node.

In that instance, you will find customers table that contains

customer's data.

Installation : localhost

Database name : retail_db

Table Name : Customers

User Name : root 

Password : cloudera

Output requirement:

Place the customers files in HDFS directory

"user/cloudera/problem1/customers/txtdata"

Save output in text format with fields 

separated by a "*" and lines should be terminated by pipe.

Select the columns : customer_id,

customer_fname,customer_lname,

customer_street_name.

$ sqoop import \

-connect "jdbc:mysql://localhost/retail_db" \

-username root \

-password cloudera \

-table customers \

-where "customer_street like '%Plaza%'" \

-target-dir /user/cloudera/problem1/customers/textdata \

-columns "customer_id,customer_fname,customer_lname,customer_street"  

dfs dfs -ls /user/cloudera/problem1/customers/textdata

Found 5 items

-rw-r--r--   1 cloudera cloudera          0 2020-08-03 08:40 /user/cloudera/problem1/customers/textdata/_SUCCESS

-rw-r--r--   1 cloudera cloudera        974 2020-08-03 08:40 /user/cloudera/problem1/customers/textdata/part-m-00000

-rw-r--r--   1 cloudera cloudera       1165 2020-08-03 08:40 /user/cloudera/problem1/customers/textdata/part-m-00001

-rw-r--r--   1 cloudera cloudera        986 2020-08-03 08:40 /user/cloudera/problem1/customers/textdata/part-m-00002

-rw-r--r--   1 cloudera cloudera       1028 2020-08-03 08:40 /user/cloudera/problem1/customers/textdata/part-m-00003

$ hdfs dfs -cat /user/cloudera/problem1/customers/textdata/part-m-00000 | tail

1793,Mary,Burke,141 Dewy Plaza

1821,Mary,Trevino,6520 Quaking Quail Plaza

1891,Mary,Mora,1804 Misty Plaza

2053,Ronald,Smith,1025 Colonial Plaza

2247,Alice,Werner,1557 Heather Leaf Plaza

2517,Jose,Kane,5202 Emerald Island Plaza

2545,Larry,Patel,1688 Middle Panda Plaza

2575,Mary,Carroll,4979 Honey Plaza

2831,Mary,Padilla,3810 Crystal Plaza

2875,Mary,Roberts,2205 Velvet Plaza