Y6zmhx9j

1Details
2Schema
3
4CREATE TABLE IF NOT EXISTS development.kra_adobe_anon_users
5(
6  `year` YEAR NOT NULL, 
7  `month` TINYINT UNSIGNED NOT NULL, 
8  account_number_name VARCHAR(150) NOT NULL, 
9  active_anonymous_visitors MEDIUMINT UNSIGNED NOT NULL, 
10  PRIMARY KEY (`year`, `month`, account_number_name) 
11) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
12Initial load
13
14LOAD DATA LOCAL INFILE '~/sync/adobe/kra_adobe_anon-users_2016-2019_1.csv' 
15IGNORE INTO TABLE development.kra_adobe_anon_users 
16CHARACTER SET UTF8 
17FIELDS 
18  TERMINATED BY ',' 
19  OPTIONALLY ENCLOSED BY '"' 
20LINES TERMINATED BY '\n' 
21IGNORE 1 LINES;
22# 48690
23Monthly feed insert
24Make staging table
25
26DROP TABLE IF EXISTS development.kra_stageload_adobe_anon_users;
27
28CREATE TABLE IF NOT EXISTS development.kra_stageload_adobe_anon_users
29LIKE development.kra_adobe_anon_users;
30Load into staging
31
32# 2 files already loaded; latest as example
33# sync/adobe/kra_adobe_anon-users_2019_2.csv
34# sync/adobe/kra_adobe_anon-users_2019_3.csv
35
36LOAD DATA LOCAL INFILE ~/sync/adobe/kra_adobe_anon-users_2019_3.csv' 
37INTO TABLE development.kra_stageload_adobe_anon_users 
38CHARACTER SET UTF8 
39FIELDS 
40  TERMINATED BY ',' 
41  OPTIONALLY ENCLOSED BY '"' 
42LINES TERMINATED BY '\n' 
43IGNORE 1 LINES;
44
45SELECT COUNT(*) FROM development.kra_stageload_adobe_anon_users;
46# 1249 for 2019 02
47# 1281 for 2019 03
48Insert into base table
49
50INSERT INTO development.kra_adobe_anon_users
51SELECT tload.* FROM development.kra_stageload_adobe_anon_users AS tload
52LEFT JOIN development.kra_adobe_anon_users AS tbase
53USING(`year`, `month`)
54WHERE 
55  tbase.`year` IS NULL AND 
56  tbase.`month` IS NULL;
57  
58# check; should be pre-load count + count of subset to load
59SELECT COUNT(*) FROM development.kra_adobe_anon_users;
60# 51220
61Drop staging if all good
62
63DROP TABLE IF EXISTS development.kra_stageload_adobe_anon_users;