Sqoop: upsert export issue


#1

trying to do Sqoop export with UPSERT.

But its not doing update and insert . looks like its only appending the rows to the table.

below is the steps have done. Please tell me what is the issue?

created table in MYSQL:

mysql> create table KT_DailyRevenue ( Revenue float , Description varchar(50) , order_Date varchar(30) );
Query OK, 0 rows affected (0.24 sec)

exported data from hive: order_Date ‘2013-07%’ and ‘2013-08%’
38 rows exported.

[kirantadisetti@gw02 ~]$ sqoop export --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --export-dir /apps/hive/warehouse/kirtad.db/dailyrevenue_1 --table KT_DailyRevenue --columns order_date,revenue --input-fields-terminated-by “\001”

    Map-Reduce Framework
            Map input records=38
            Map output records=38
            Input split bytes=660
            Spilled Records=0
            Failed Shuffles=0
            Merged Map outputs=0
            GC time elapsed (ms)=181
            CPU time spent (ms)=4100
            Physical memory (bytes) snapshot=957784064
            Virtual memory (bytes) snapshot=14873522176
            Total committed heap usage (bytes)=879230976
    File Input Format Counters
            Bytes Read=0
    File Output Format Counters
            Bytes Written=0

18/10/13 21:30:58 INFO mapreduce.ExportJobBase: Transferred 4.4062 KB in 26.702 seconds (168.9763 bytes/sec)
18/10/13 21:30:58 INFO mapreduce.ExportJobBase: Exported 38 records.

mysql> select count() from KT_DailyRevenue;
±---------+
| count(
) |
±---------+
| 38 |
±---------+
1 row in set (0.00 sec)

deleted the rows from mysql table and updated the revenue to 0.

mysql> delete from KT_DailyRevenue where order_Date like ‘2013-08%’;
Query OK, 31 rows affected (0.05 sec)

mysql> update KT_DailyRevenue set Revenue=0;
Query OK, 7 rows affected (0.02 sec)
Rows matched: 7 Changed: 7 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from KT_DailyRevenue;
±--------±------------±----------------------+
| Revenue | Description | order_Date |
±--------±------------±----------------------+
| 0 | NULL | 2013-07-25 00:00:00.0 |
| 0 | NULL | 2013-07-26 00:00:00.0 |
| 0 | NULL | 2013-07-27 00:00:00.0 |
| 0 | NULL | 2013-07-28 00:00:00.0 |
| 0 | NULL | 2013-07-29 00:00:00.0 |
| 0 | NULL | 2013-07-30 00:00:00.0 |
| 0 | NULL | 2013-07-31 00:00:00.0 |
±--------±------------±----------------------+
7 rows in set (0.01 sec)

exported the hive data using UPSERT logic as below.

sqoop export
–connect jdbc:mysql://ms.itversity.com:3306/retail_export
–username retail_user
–password itversity
–export-dir /apps/hive/warehouse/kirtad.db/dailyrevenue_1
–table KT_DailyRevenue
–columns order_date,revenue
–update-key order_date
–update-mode allowinsert
–input-fields-terminated-by “\001”

    Map-Reduce Framework
            Map input records=38
            Map output records=38
            Input split bytes=660
            Spilled Records=0
            Failed Shuffles=0
            Merged Map outputs=0
            GC time elapsed (ms)=180
            CPU time spent (ms)=3420
            Physical memory (bytes) snapshot=939102208
            Virtual memory (bytes) snapshot=14858506240
            Total committed heap usage (bytes)=877133824
    File Input Format Counters
            Bytes Read=0
    File Output Format Counters
            Bytes Written=0

18/10/13 21:35:55 INFO mapreduce.ExportJobBase: Transferred 4.4062 KB in 24.4796 seconds (184.3171 bytes/sec)
18/10/13 21:35:55 INFO mapreduce.ExportJobBase: Exported 38 records.

result in MYSQL:

mysql> select * from KT_DailyRevenue;
±--------±------------±----------------------+
| Revenue | Description | order_Date |
±--------±------------±----------------------+
| 0 | NULL | 2013-07-25 00:00:00.0 |
| 0 | NULL | 2013-07-26 00:00:00.0 |
| 0 | NULL | 2013-07-27 00:00:00.0 |
| 0 | NULL | 2013-07-28 00:00:00.0 |
| 0 | NULL | 2013-07-29 00:00:00.0 |
| 0 | NULL | 2013-07-30 00:00:00.0 |
| 0 | NULL | 2013-07-31 00:00:00.0 |
| 68153.8 | NULL | 2013-07-25 00:00:00.0 |
| 136520 | NULL | 2013-07-26 00:00:00.0 |
| 101074 | NULL | 2013-07-27 00:00:00.0 |
| 87123.1 | NULL | 2013-07-28 00:00:00.0 |
| 137287 | NULL | 2013-07-29 00:00:00.0 |
| 102746 | NULL | 2013-07-30 00:00:00.0 |
| 131878 | NULL | 2013-07-31 00:00:00.0 |
| 129002 | NULL | 2013-08-01 00:00:00.0 |
| 109347 | NULL | 2013-08-02 00:00:00.0 |
| 95266.9 | NULL | 2013-08-03 00:00:00.0 |
| 90931 | NULL | 2013-08-04 00:00:00.0 |
| 75882.3 | NULL | 2013-08-05 00:00:00.0 |
| 120574 | NULL | 2013-08-06 00:00:00.0 |
| 103351 | NULL | 2013-08-07 00:00:00.0 |
| 76501.7 | NULL | 2013-08-08 00:00:00.0 |
| 62316.5 | NULL | 2013-08-09 00:00:00.0 |
| 129575 | NULL | 2013-08-10 00:00:00.0 |
| 71149.6 | NULL | 2013-08-11 00:00:00.0 |
| 121883 | NULL | 2013-08-12 00:00:00.0 |
| 99616.2 | NULL | 2013-08-23 00:00:00.0 |
| 128884 | NULL | 2013-08-24 00:00:00.0 |
| 98521.6 | NULL | 2013-08-25 00:00:00.0 |
| 88114.2 | NULL | 2013-08-26 00:00:00.0 |
| 91634.9 | NULL | 2013-08-27 00:00:00.0 |
| 55189.2 | NULL | 2013-08-28 00:00:00.0 |
| 99960.6 | NULL | 2013-08-29 00:00:00.0 |
| 57008.5 | NULL | 2013-08-30 00:00:00.0 |
| 75923.7 | NULL | 2013-08-31 00:00:00.0 |
| 39874.5 | NULL | 2013-08-13 00:00:00.0 |
| 103939 | NULL | 2013-08-14 00:00:00.0 |
| 103031 | NULL | 2013-08-15 00:00:00.0 |
| 69264.5 | NULL | 2013-08-16 00:00:00.0 |
| 127362 | NULL | 2013-08-17 00:00:00.0 |
| 106789 | NULL | 2013-08-18 00:00:00.0 |
| 50348.3 | NULL | 2013-08-19 00:00:00.0 |
| 87983.1 | NULL | 2013-08-20 00:00:00.0 |
| 64860.8 | NULL | 2013-08-21 00:00:00.0 |
| 94574.7 | NULL | 2013-08-22 00:00:00.0 |
±--------±------------±----------------------+
45 rows in set (0.00 sec)


#2

If i remove the - --update-mode allowinsert , then its updating the records for 2013-07.

What should I do to update the existing records and insert the new records at the same time ?

and one more question., If i define the order_Date in mysql ( target) as date, sqoop export is failing ?

How to type cast the data when export the data from hive to mysql ?


#3

Can some one please give me update ?