stmt_trunc TRUNCATE TABLE public lenovo_clicks_stg print stmt_trunc lo

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
stmt_trunc = """TRUNCATE TABLE public.lenovo_clicks_stg"""
print(stmt_trunc)
logging.info(stmt_trunc)
logging.info("Truncating stg table")
redshift_cursor.execute(stmt_trunc)
redshift_conn.commit()
stmt_load = """INSERT INTO public.lenovo_clicks_stg
SELECT clk.zone_id, clk.deviceid AS did, clk.cre_date AS click_date, clk.click_id, clk.country_code
FROM clicks_fh clk
JOIN (SELECT zone_id, deviceid AS did, MIN(cre_date) AS min_click_date
FROM clicks_fh c
WHERE cre_date >= '{0}'
AND cre_date < '{1}'
GROUP BY 1,2
) agg ON clk.zone_id = agg.zone_id AND
clk.deviceid = agg.did AND
clk.cre_date = agg.min_click_date
LEFT JOIN public.lenovo_clicks lc ON lc.did = agg.did
WHERE pb_id IN ('3efcc480-1925-11e9-9022-1249e5ec4f79', '7efbb7c2-697c-11e9-9022-1249e5ec4f79')
AND lc.did IS NULL""".format(start_date.strftime('%Y-%m-%d'),end_date.strftime('%Y-%m-%d'))
print(stmt_load)
logging.info(stmt_load)
logging.info("Load new users")
redshift_cursor.execute(stmt_load)
redshift_conn.commit()
stmt_insert = """INSERT INTO public.lenovo_clicks
SELECT * FROM public.lenovo_clicks_stg"""
print(stmt_insert)
logging.info(stmt_insert)
logging.info("Insert new users")
redshift_cursor.execute(stmt_insert)
redshift_conn.commit()