Kettle derivative Delete and insert update data _20161130

Here are 3 tables only the time dimension of different days-week, now need to add yesterday data daily into these 3 tables

Because there will be returns in the business, and so on, it is necessary to first remove the original part of the data and then from that point in time to update.

Days need to delete the first 7 days of data, weeks need to delete the first 2 weeks of data, the month need to delete data before 2 months to ensure accurate data

The Execute SQL Script control is provided in kettle, so let's first delete the original data and take the first SQL script execution as an example.

1. Delete the data for the first 7 days of the table in the day dimension

DELETE   from WHERE Online date >=date_add (current_date-7Day)

2, table data inserted table input step where condition before 7 days that time date is the starting point

Sql:

SELECTD.id,a. The city, a. Online date, c. Commodity classification level, c. Classification of commodities class two, category three, commodity classification, product id,c. Product Name, B. Total quantity of items, order Amount from (    SELECTCity, date (online date) asonline date, Product ID from' a010_dynamic_ products 'WHEREOnline date>=Date_add (current_date, INTERVAL-7  Day) andOnline date<current_date    GROUP  byCity, date (online date), Product ID) asa Left JOIN (    SELECTCity, date (order date) asOrder date, Product ID,SUM(Total quantity of items) asTotal quantity of the product,SUMAmount asOrder Amount from' A003_order 'WHEREOrder Date>=Date_add (current_date, INTERVAL-7  Day) andOrder Date<current_date  andAmount>0     GROUP  byCity, date (order date), Product ID) asB onA. City=B. Cities andA. Product ID=B. Product ID andA. Online date=B. Order Date Left JOIN' A002_ Products ' asC onA. Product ID=c. Product ID Left JOIN' A000_city ' asD onA. City=D. City

Script settings:

3, the table output step.

Do not tick the Crop table option tick to delete all the table data to import only the first 7 days of data

Kettle derivative Delete and insert update data _20161130