SQOOP Export Example : Hive to MySQL using SQOOP Export

// We are going to export ohm.cust_from_mysql (hive table ) into cust_exported (MySQL)

hive> select * from ohm.cust_from_mysql limit 5;

OK

1 Richard Hernandez XXXXXXXXX XXXXXXXXX 6303 Heather Plaza Brownsville TX 78521 

2 Mary Barrett XXXXXXXXX XXXXXXXXX 9526 Noble Embers Ridge Littleton CO 80126

3 Ann Smith XXXXXXXXX XXXXXXXXX 3422 Blue Pioneer Bend Caguas PR 00725

4 Mary Jones XXXXXXXXX XXXXXXXXX 8324 Little Common San Marcos CA 92069

5 Robert Hudson XXXXXXXXX XXXXXXXXX 10 Crystal River Mall Caguas PR 00725

hive> select count(1) from ohm.cust_from_mysql;

12435 -- Hive Table has 12435 Rows in it

// We must create target table in destination database before export 

Create an empty table in retail_db of MySQL

mysql> create table retail_db.cust_exported as select * from customers where 1 =0 

// Presently retail_db.cust_exported table has zero rows.

$ sqoop export \

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

-username root \

-password cloudera \

-table cust_exported \

-hcatalog-table cust_from_mysql \

-hcatalog-database ohm

Exported 12435 records.

mysql> select * from cust_exported  limit 5;

+-------------+----------------+----------------+----------------+-------------------+------------------------+---------------+----------------+------------------+

| customer_id | customer_fname | customer_lname | customer_email | customer_password | customer_street        | customer_city | customer_state | customer_zipcode |

+-------------+----------------+----------------+----------------+-------------------+------------------------+---------------+----------------+------------------+

|        9327 | Donna          | Smith          | XXXXXXXXX      | XXXXXXXXX         | 4114 Clear Nectar Isle | Caguas        | PR             | 00725            |

|           1 | Richard        | Hernandez      | XXXXXXXXX      | XXXXXXXXX         | 6303 Heather Plaza     | Brownsville   | TX             | 78521            |

|        9328 | Mary           | Perez          | XXXXXXXXX      | XXXXXXXXX         | 376 Golden Orchard     | Moreno Valley | CA             | 92553            |

|        9329 | Eugene         | Powell         | XXXXXXXXX      | XXXXXXXXX         | 2161 Burning Maze      | Metairie      | LA             | 70003            |

|        9330 | Mary           | Conley         | XXXXXXXXX      | XXXXXXXXX         | 3046 Broad Sky Dale    | Caguas        | PR             | 00725            |

+-------------+----------------+----------------+----------------+-------------------+------------------------+---------------+----------------+------------------+

mysql> select count(1) from retail_db.cust_exported;

+----------+

| count(1) |

+----------+

|    12435 |

+----------+