| Index: trunk/wikistats/analytics/analytics_refresh_from_csv.txt |
| — | — | @@ -1,101 +1,6 @@ |
| 2 | | -
|
| 3 | 2 | USE `analytics` ;
|
| 4 | 3 |
|
| 5 | | -CREATE TABLE `comscore` (
|
| 6 | | - `date` date NOT NULL,
|
| 7 | | - `country_code` varchar (3),
|
| 8 | | - `region_code` varchar (3),
|
| 9 | | - `web_property` varchar (20),
|
| 10 | | - `project_code` varchar (10),
|
| 11 | | - `reach` decimal (4,1) DEFAULT NULL,
|
| 12 | | - `visitors` decimal (15) DEFAULT NULL,
|
| 13 | | - PRIMARY KEY (date,country_code,region_code,project_code,web_property),
|
| 14 | | - KEY (`country_code`)
|
| 15 | | -) ;
|
| 16 | | -
|
| 17 | | -CREATE TABLE `comscore_regions` (
|
| 18 | | - `region_code` varchar (2),
|
| 19 | | - `report_language` varchar (10),
|
| 20 | | - `region_name` varchar (18),
|
| 21 | | - PRIMARY KEY (report_language,region_code)
|
| 22 | | -) ;
|
| 23 | | -
|
| 24 | | -CREATE TABLE `wikistats` (
|
| 25 | | - `date` date NOT NULL,
|
| 26 | | - `project_code` varchar (10),
|
| 27 | | - `language_code` varchar (15),
|
| 28 | | - `editors_all_time` int (10) DEFAULT NULL,
|
| 29 | | - `editors_new` int (7) DEFAULT NULL,
|
| 30 | | - `editors_ge_5` int (7) DEFAULT NULL,
|
| 31 | | - `editors_ge_25` int (7) DEFAULT NULL,
|
| 32 | | - `editors_ge_100` int (7) DEFAULT NULL,
|
| 33 | | - `articles` int (12) DEFAULT NULL,
|
| 34 | | - `articles_new_per_day` int (9) DEFAULT NULL,
|
| 35 | | - `articles_over_bytes_500` int (12) DEFAULT NULL,
|
| 36 | | - `articles_over_bytes_2000` int (12) DEFAULT NULL,
|
| 37 | | - `edits_per_article` decimal (9,1) DEFAULT NULL,
|
| 38 | | - `bytes_per_article` decimal (9,1) DEFAULT NULL,
|
| 39 | | - `edits` int (12) DEFAULT NULL,
|
| 40 | | - `size_in_bytes` int (15) DEFAULT NULL,
|
| 41 | | - `size_in_words` int (15) DEFAULT NULL,
|
| 42 | | - `links_internal` int (15) DEFAULT NULL,
|
| 43 | | - `links_interwiki` int (15) DEFAULT NULL,
|
| 44 | | - `links_image` int (15) DEFAULT NULL,
|
| 45 | | - `links_external` int (15) DEFAULT NULL,
|
| 46 | | - `redirects` int (15) DEFAULT NULL,
|
| 47 | | - PRIMARY KEY (date,project_code,language_code)
|
| 48 | | -) ;
|
| 49 | | -
|
| 50 | | -CREATE TABLE `page_views` (
|
| 51 | | - `date` date NOT NULL,
|
| 52 | | - `project_code` char (2),
|
| 53 | | - `language_code` char (15),
|
| 54 | | - `views_non_mobile_raw` bigint (15),
|
| 55 | | - `views_mobile_raw` bigint (15),
|
| 56 | | - `views_non_mobile_normalized` bigint (15),
|
| 57 | | - `views_mobile_normalized` bigint (15),
|
| 58 | | - `views_raw` bigint (15),
|
| 59 | | - `views_normalized` bigint (15),
|
| 60 | | - PRIMARY KEY (date,project_code,language_code)
|
| 61 | | -) ;
|
| 62 | | -
|
| 63 | | -CREATE TABLE `language_names` (
|
| 64 | | - `report_language` varchar (15),
|
| 65 | | - `language_code` varchar (15),
|
| 66 | | - `language_name` varchar (50),
|
| 67 | | - PRIMARY KEY (report_language,language_code)
|
| 68 | | -) ;
|
| 69 | | -
|
| 70 | | -CREATE TABLE `binaries` (
|
| 71 | | - `date` date NOT NULL,
|
| 72 | | - `project_code` char (2),
|
| 73 | | - `language_code` char (15),
|
| 74 | | - `extension` varchar (10),
|
| 75 | | - `binaries` bigint (15),
|
| 76 | | - PRIMARY KEY (date,project_code,language_code,extension)
|
| 77 | | -) ;
|
| 78 | | -
|
| 79 | | -CREATE TABLE `offline` (
|
| 80 | | - `date` date NOT NULL,
|
| 81 | | - `readers` bigint (12),
|
| 82 | | - PRIMARY KEY (date,readers)
|
| 83 | | -) ;
|
| 84 | | -
|
| 85 | | - |
| 86 | | -
|
| 87 | | - |
| 88 | | -TRUNCATE TABLE 'analytics_in_comscore.csv' ;
|
| | 4 | +TRUNCATE TABLE comscore ;
|
| 89 | 5 | LOAD DATA LOCAL INFILE 'analytics_in_comscore.csv'
|
| 90 | 6 | INTO TABLE comscore
|
| 91 | 7 | FIELDS TERMINATED BY ','
|
| — | — | @@ -103,14 +8,14 @@ |
| 104 | 9 | (@date,country_code,region_code,web_property,project_code,reach,visitors)
|
| 105 | 10 | SET date = last_day (str_to_date(concat (@date,'-01'),'%Y-%m-%d')) ;
|
| 106 | 11 |
|
| 107 | | -TRUNCATE TABLE 'analytics_in_comscore_regions.csv' ; |
| | 12 | +TRUNCATE TABLE comscore_regions ; |
| 108 | 13 | LOAD DATA LOCAL INFILE 'analytics_in_comscore_regions.csv'
|
| 109 | 14 | INTO TABLE comscore_regions
|
| 110 | 15 | FIELDS TERMINATED BY ','
|
| 111 | 16 | OPTIONALLY ENCLOSED BY '"'
|
| 112 | 17 | (report_language,region_code,region_name) ;
|
| 113 | 18 |
|
| 114 | | -TRUNCATE TABLE 'analytics_in_wikistats.csv' ; |
| | 19 | +TRUNCATE TABLE wikistats ; |
| 115 | 20 | LOAD DATA LOCAL INFILE 'analytics_in_wikistats.csv'
|
| 116 | 21 | INTO TABLE wikistats
|
| 117 | 22 | FIELDS TERMINATED BY ','
|
| — | — | @@ -118,7 +23,7 @@ |
| 119 | 24 | (project_code,language_code,@date,editors_all_time,editors_new,editors_ge_5,editors_ge_25,editors_ge_100,articles,articles_new_per_day,articles_over_bytes_500,articles_over_bytes_2000,edits_per_article,bytes_per_article,edits,size_in_bytes,size_in_words,links_internal,links_interwiki,links_image,links_external,redirects)
|
| 120 | 25 | SET date = last_day (str_to_date(concat (@date,'-01'),'%Y-%m-%d')) ;
|
| 121 | 26 |
|
| 122 | | -TRUNCATE TABLE 'analytics_in_page_views.csv' ; |
| | 27 | +TRUNCATE TABLE page_views ; |
| 123 | 28 | LOAD DATA LOCAL INFILE 'analytics_in_page_views.csv'
|
| 124 | 29 | INTO TABLE page_views
|
| 125 | 30 | FIELDS TERMINATED BY ','
|
| — | — | @@ -126,15 +31,14 @@ |
| 127 | 32 | (project_code,language_code,@date,views_non_mobile_raw,views_mobile_raw,views_non_mobile_normalized,views_mobile_normalized,views_raw,views_normalized)
|
| 128 | 33 | SET date = last_day (str_to_date(concat (@date,'-01'),'%Y-%m-%d')) ;
|
| 129 | 34 |
|
| 130 | | -
|
| 131 | | -TRUNCATE TABLE 'analytics_in_language_names.csv' ; |
| | 35 | +TRUNCATE TABLE language_names ; |
| 132 | 36 | LOAD DATA LOCAL INFILE 'analytics_in_language_names.csv'
|
| 133 | 37 | INTO TABLE language_names
|
| 134 | 38 | FIELDS TERMINATED BY ','
|
| 135 | 39 | OPTIONALLY ENCLOSED BY '"'
|
| 136 | 40 | (report_language,language_code,language_name) ;
|
| 137 | 41 |
|
| 138 | | -TRUNCATE TABLE 'analytics_in_binaries.csv' ; |
| | 42 | +TRUNCATE TABLE binaries ; |
| 139 | 43 | LOAD DATA LOCAL INFILE 'analytics_in_binaries.csv'
|
| 140 | 44 | INTO TABLE binaries
|
| 141 | 45 | FIELDS TERMINATED BY ','
|
| — | — | @@ -142,4 +46,10 @@ |
| 143 | 47 | (project_code,language_code,@date,extension,binaries)
|
| 144 | 48 | SET date = last_day (str_to_date(concat (@date,'-01'),'%Y-%m-%d')) ;
|
| 145 | 49 |
|
| 146 | | -TRUNCATE TABLE 'analytics_in_offline.csv' ; |
| | 50 | +TRUNCATE TABLE offline ; |
| | 51 | +LOAD DATA LOCAL INFILE 'analytics_in_offline.csv'
|
| | 52 | + INTO TABLE offline
|
| | 53 | + FIELDS TERMINATED BY ','
|
| | 54 | + OPTIONALLY ENCLOSED BY '"'
|
| | 55 | + (@date, readers)
|
| | 56 | + SET date = last_day (str_to_date(concat (@date,'-01'),'%Y-%m-%d')) ;
|