Skip to content

Instantly share code, notes, and snippets.

@mafernando

mafernando/gist:b6a4da5a750fb1f630acf6f4da21dbc4

Forked from bryhal/gist:4129042
Last active November 17, 2016 09:58
Show Gist options
  • Save mafernando/b6a4da5a750fb1f630acf6f4da21dbc4 to your computer and use it in GitHub Desktop.
Save mafernando/b6a4da5a750fb1f630acf6f4da21dbc4 to your computer and use it in GitHub Desktop.
Download ZIP
MYSQL: Generate Calendar Table
Raw
gistfile1.txt
;
DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
id INTEGER PRIMARY KEY, -- year*10000+month*100+day
date DATE NOT NULL,
year INTEGER NOT NULL,
month INTEGER NOT NULL, -- 1 to 12
day INTEGER NOT NULL, -- 1 to 31
quarter INTEGER NOT NULL, -- 1 to 4
week INTEGER NOT NULL, -- 1 to 52/53
day_name VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
month_name VARCHAR(9) NOT NULL, -- 'January', 'February'...
holiday_flag CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
weekend_flag CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
event VARCHAR(50),
UNIQUE td_ymd_idx (year,month,day),
UNIQUE td_dbdate_idx (date)
) Engine=InnoDB;
DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
DECLARE currentdate DATE;
SET currentdate = startdate;
WHILE currentdate < stopdate DO
INSERT INTO dates VALUES (
YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
currentdate,
YEAR(currentdate),
MONTH(currentdate),
DAY(currentdate),
QUARTER(currentdate),
WEEKOFYEAR(currentdate),
DATE_FORMAT(currentdate,'%W'),
DATE_FORMAT(currentdate,'%M'),
'f',
CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
NULL);
SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
END WHILE;
END
//
DELIMITER ;
TRUNCATE TABLE dates;
CALL fill_date_dimension('2000-01-01','2100-01-01');
OPTIMIZE TABLE dates;
SELECT * FROM dates ORDER BY id DESC;
@mafernando
Copy link
Author

mafernando commented Nov 3, 2016 •
edited 

#select * from etl_logs where location like '%client_stream%';		
		
#select * from etl_logs where location like '%malware_stream%';		
		
#select * from etl_logs where location like '%exploit_stream_mi%';		
		
-- update etl_logs set stream = 'client_stream' where location like '%client_stream%';		
		
-- select STR_TO_DATE('12/13z/10', '%d/%m/%Y') ;		
		
-- DROP VIEW v_etl_logs;		
-- DROP VIEW v_etl_vars;		
		
-- CREATE VIEW v_etl_logs AS 		
SELECT		
-- 	request_dt,	
	request_dt AS date,	
	request_dt - INTERVAL 1 WEEK AS prior_week,	
	#DAYOFMONTH(STR_TO_DATE(request_dt, '%m/%d/%Y')) AS day,	
	#MONTH(STR_TO_DATE(request_dt, '%m/%d/%Y')) AS month,	
	#YEAR(STR_TO_DATE(request_dt, '%m/%d/%Y')) AS year, 	
	rows, 	
	stream 	
FROM 		
	etl_logs	
WHERE 1		
	AND request_dt IS NOT NULL	
;		
		
-- CREATE VIEW v_etl_vars AS	
SELECT 		
	e.stream, 	
	e.date, 	
	e.prior_week, 	
	e.rows AS curr_rows,	
	_e.rows AS last_rows,	
	(e.rows - _e.rows) AS row_diff,	
	ROUND(((e.rows - _e.rows) / e.rows)*100, 2) AS var	
FROM		
	v_etl_logs e	
	LEFT JOIN 	
		v_etl_logs _e 
	ON 1	
		AND e.prior_week = _e.date
		AND e.stream = _e.stream;
		
select * from v_etl_vars order by stream, ;		
		
SELECT 		
	*,	
-- 	@curRank := @curRank + 1 AS rank 	
		  ( 
            CASE v.week_of_year 		
            WHEN @week_of_year 		
            THEN @day := @day + 1 		
            ELSE @day := 1 AND @week_of_year := v.week_of_year END		
          ) + 1 AS rank,		
          week_of_year		
FROM 		
	v_etl_vars v,	
-- 	(SELECT @curRank := 0) r	
(SELECT @day := 0, @week_of_year := '') r		
WHERE 1 		
	AND v.date >= (NOW() - INTERVAL 2 WEEK)	
ORDER BY		
	1 DESC	
;		
		
-- CREATE TABLE c_etl_vars AS
SELECT 		
	v.date as x,	
	( CASE v.week 	
	WHEN @weekOfYear 	
        THEN @curDay := @curDay + 1 		
        ELSE @curDay := 1 AND @weekOfYear := v.week END		
     ) AS y,		
     IF(v.curr_rows IS NULL, 0, v.curr_rows) as value, 		
     IF(v.var IS NULL, 0, v.var) as variance     		
FROM 		
	v_etl_vars v,	
	(SELECT @curDay := 0, @weekOfYear := '') r	
WHERE 1 		
	AND v.stream = 'client_stream'	
	AND v.date >= (NOW() - INTERVAL 2 MONTH)	
ORDER BY		
	v.week, v.date DESC	
;		
		
SELECT		
	*	
FROM		
	v_etl_vars	
;		
		
#NAME?		
#NAME?		
SELECT 		
	s.stream_idx as x,	
	( CASE s.stream 	
	WHEN @curStream 	
        THEN @curDay := @curDay + 1 		
        ELSE @curDay := 1 AND @curStream := s.stream END		
     ) AS y,		
     IF(v.curr_rows IS NULL, 0, v.curr_rows) as value, 		
     IF(v.var IS NULL, 0, v.var) as variance		
FROM		
	(	
	SELECT DATE(NOW()) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 1 DAY) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 2 DAY) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 3 DAY) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 4 DAY) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 5 DAY) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 6 DAY) AS date UNION	
	SELECT DATE(NOW() - INTERVAL 7 DAY) AS date 	
	) AS d	
	LEFT JOIN 	
	(	
		SELECT 'client_stream' AS stream, 0 as stream_idx
-- 		SELECT 'malware_stream' AS stream, 1 as stream_idx UNION
-- 		SELECT 'exploit_stream_mi' AS stream, 2 as stream_idx
	) AS s	
	ON 1	
	LEFT JOIN v_etl_vars v ON d.date = v.date and s.stream = v.stream,	
	(SELECT @curDay := 0, @curStream := '') r	
ORDER BY		
	s.stream_idx ASC, d.date DESC	
;		
		
-- 	LEFT OUTER JOIN v_etl_vars v ON d.date = v.date	
;		
		
SELECT DATE(NOW()) AS date UNION		
SELECT DATE(NOW() - INTERVAL 1 DAY) AS date UNION		
SELECT DATE(NOW() - INTERVAL 2 DAY) AS date UNION		
SELECT DATE(NOW() - INTERVAL 3 DAY) AS date UNION		
SELECT DATE(NOW() - INTERVAL 4 DAY) AS date UNION		
SELECT DATE(NOW() - INTERVAL 5 DAY) AS date UNION		
SELECT DATE(NOW() - INTERVAL 6 DAY) AS date UNION		
SELECT DATE(NOW() - INTERVAL 8 DAY) AS date 		
;		
		
SELECT NOW() AS day UNION		
SELECT NOW() - INTERVAL 1 DAY AS day UNION		
SELECT NOW() - INTERVAL 2 DAY AS day UNION		
SELECT NOW() - INTERVAL 3 DAY AS day UNION		
SELECT NOW() - INTERVAL 4 DAY AS day UNION		
SELECT NOW() - INTERVAL 5 DAY AS day UNION		
SELECT NOW() - INTERVAL 6 DAY AS day UNION		
SELECT NOW() - INTERVAL 8 DAY AS day 		
;		
		
SELECT DATE_FORMAT(NOW(), '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 2 DAY, '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 3 DAY, '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 4 DAY, '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 5 DAY, '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 6 DAY, '%W') AS day UNION		
SELECT DATE_FORMAT(NOW() - INTERVAL 8 DAY, '%W') AS day 		
		
;		
/* 	NOW() - INTERVAL 2 DAY	
	NOW() - INTERVAL 3 DAY	
	NOW() - INTERVAL 4 DAY	
	NOW() - INTERVAL 5 DAY	
	NOW() - INTERVAL 6 DAY	
	NOW() - INTERVAL 7 DAY */	
	
;

select * from etl_logs;	

SET @tmp = '2016-11-03';
SELECT @tmp;
SELECT 
	d.date
	,DATE_ADD(date, INTERVAL(1-DAYOFWEEK(date)) DAY) week_start
    ,DATE_ADD(date, INTERVAL(7-DAYOFWEEK(date)) DAY) week_end
	,d.* 
FROM 
	dates d
WHERE 1
	AND d.date BETWEEN DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY) AND DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY)
-- 	AND d.date BETWEEN DATE_ADD(@tmp, INTERVAL(1-DAYOFWEEK(@tmp)) DAY) AND DATE_ADD(@tmp, INTERVAL(7-DAYOFWEEK(@tmp)) DAY)
-- 	AND d.date >= NOW() - INTERVAL 1 WEEK;
ORDER BY 
	d.id DESC;

SELECT * FROM v_etl_vars;	

SELECT 
	d.date
	#,s.stream
	,v.*
FROM 
	dates d 
	#LEFT JOIN (SELECT DISTINCT stream FROM etl_logs) s ON 1
-- 	LEFT JOIN v_etl_vars v ON d.date = DATE_FORMAT(STR_TO_DATE(v.date, '%d/%m/%Y'), '%Y-%m-%d')
WHERE 1
	AND d.date BETWEEN DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY) AND DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY)
ORDER BY 
	d.id DESC;

select * from dates;

SET @tmp = '2016-11-03';
SELECT @tmp;

-- DROP TABLE portal_development.c_etl_vars;

-- DROP TABLE portal_development_backup.c_etl_vars;

-- CREATE TABLE portal_development.c_etl_vars LIKE portal_development_backup.c_etl_vars;

-- INSERT INTO portal_development.c_etl_vars SELECT * FROM portal_development_backup.c_etl_vars;

-- CREATE TABLE c_etl_vars AS 
	SELECT
	DATE_FORMAT(d.date, '%m/%d') AS date
-- 	d.date
-- 	,d.week
-- 	,s.stream
-- 	,d.date - INTERVAL 1 WEEK AS prior_week
	,IF(v.curr_rows, v.curr_rows, 0) AS value
	,IF(v.last_rows, v.last_rows, 0) AS old_value
	,IF(v.row_diff, v.row_diff, 0) AS diff
	,IF(v.var, v.var, 0) AS variance
-- 	,d.day_name
 	,(CASE d.week
		WHEN @curr_week THEN @idx
        ELSE ( (@idx := @idx + 1) AND (@curr_week := d.week)) #@idx := @idx + 1 		
     END) AS rank_col
	,(CASE d.day_name
		WHEN 'Monday' THEN 0
		WHEN 'Tuesday' THEN 1	
		WHEN 'Wednesday' THEN 2				
		WHEN 'Thursday' THEN 3						
		WHEN 'Friday' THEN 4						
		WHEN 'Saturday' THEN 5						
		WHEN 'Sunday' THEN 6
		ELSE -1
	END) AS y
	,GREATEST(@idx, 1) -1 as x
-- 	,@curr_week 	
 FROM
	dates d 
	LEFT JOIN (SELECT DISTINCT stream FROM etl_logs) s ON 1
	LEFT JOIN v_etl_vars v ON d.date = v.date AND s.stream = v.stream,
	(SELECT @idx := -1,  @curr_week := '') r	
WHERE 1
-- 	AND d.date BETWEEN DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY) AND DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY)
-- 	AND d.date BETWEEN DATE_ADD( CURDATE(), INTERVAL(1-DAYOFWEEK( CURDATE() )) DAY) AND DATE_ADD( CURDATE() , INTERVAL(7-DAYOFWEEK( CURDATE() )) DAY)
-- 	AND d.date >= (CURDATE() - INTERVAL 2 MONTH)
-- 	AND d.date >= DATE_ADD((CURDATE() - INTERVAL 7 WEEK), INTERVAL(1-DAYOFWEEK((CURDATE() - INTERVAL 7 WEEK))) DAY) # START OF WEEK AT DAY 2 MONTHS AGO
-- 	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY) # START OF WEEK AT DAY 2 MONTHS AGO
	#AND d.date = '2010-11-29'	
-- 	AND d.date >= DATE_ADD(@tmp, INTERVAL(1-DAYOFWEEK(@tmp)+1) DAY) - INTERVAL 28 WEEK
-- 	AND d.date <= DATE_ADD(@tmp, INTERVAL(8-DAYOFWEEK(@tmp)) DAY)
	AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 28 WEEK
	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
ORDER BY
	x,y
-- 	y, x
	#d.week asc, d.date asc
;	

select * from etl_logs where request_dt = '2016-10-30';


SELECT 
	v.stream
	,v.date
	,v.prior_week	
	,DATE_ADD((CURDATE() - INTERVAL 2 MONTH)
FROM 
	v_etl_vars v
;

SELECT 
	-- d.date
	-- ,DATE_ADD(date, INTERVAL(1-DAYOFWEEK(date)) DAY) week_start
    -- ,DATE_ADD(date, INTERVAL(7-DAYOFWEEK(date)) DAY) week_end
    DISTINCT CONCAT(DATE_FORMAT(DATE_ADD(date, INTERVAL(2-DAYOFWEEK(date)) DAY), '%m/%d'), '-', DATE_FORMAT(DATE_ADD(date, INTERVAL(8-DAYOFWEEK(date)) DAY), '%m/%d')) label
-- 	,d.* 
FROM 
	dates d
WHERE 1
-- 	AND d.date BETWEEN DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY) AND DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY)
-- 	AND d.date BETWEEN DATE_ADD(@tmp, INTERVAL(1-DAYOFWEEK(@tmp)) DAY) AND DATE_ADD(@tmp, INTERVAL(7-DAYOFWEEK(@tmp)) DAY)
-- 	AND d.date >= NOW() - INTERVAL 1 WEEK;
-- 	AND d.date > DATE_ADD((CURDATE() - INTERVAL 7 WEEK), INTERVAL(1-DAYOFWEEK((CURDATE() - INTERVAL 7 WEEK))) DAY) # START OF WEEK AT DAY 2 MONTHS AGO
-- 	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY) # START OF WEEK AT DAY 2 MONTHS AGO
	AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 28 WEEK
	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
;

insert into portal_development.etl_logs select * from portal_development_backup.etl_logs;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_etl_logs`
AS SELECT
   `etl_logs`.`request_dt` AS `date`,(`etl_logs`.`request_dt` - interval 1 week) AS `prior_week`,
   `etl_logs`.`rows` AS `rows`,
   `etl_logs`.`stream` AS `stream`
FROM `etl_logs` where (1 and (`etl_logs`.`request_dt` is not null));

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_etl_vars`
AS SELECT
   `e`.`stream` AS `stream`,
   `e`.`date` AS `date`,
   `e`.`prior_week` AS `prior_week`,
   `e`.`rows` AS `curr_rows`,
   `_e`.`rows` AS `last_rows`,(`e`.`rows` - `_e`.`rows`) AS `row_diff`,round((((`e`.`rows` - `_e`.`rows`) / `e`.`rows`) * 100),2) AS `var`
FROM (`v_etl_logs` `e` left join `v_etl_logs` `_e` on((1 and (`e`.`prior_week` = `_e`.`date`) and (`e`.`stream` = `_e`.`stream`))));

CREATE TABLE `etl_logs` (
  `request_dt` varchar(255) DEFAULT NULL,
  `rows` int(11) DEFAULT NULL,
  `files` int(11) DEFAULT NULL,
  `size` varchar(255) DEFAULT NULL,
  `byetes_cached` varchar(255) DEFAULT NULL,
  `cache_replication` varchar(255) DEFAULT NULL,
  `format` varchar(255) DEFAULT NULL,
  `incremental_stats` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `stream` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

;
DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        date                 	DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (date)

) Engine=InnoDB;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO dates VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE dates;

CALL fill_date_dimension('2000-01-01','2100-01-01');
OPTIMIZE TABLE dates;

SELECT * FROM dates ORDER BY id DESC;

##########################################################################################
;

CREATE TABLE `etl_logs` (
  `request_dt` varchar(255) DEFAULT NULL,
  `rows` int(11) DEFAULT NULL,
  `files` int(11) DEFAULT NULL,
  `size` varchar(255) DEFAULT NULL,
  `byetes_cached` varchar(255) DEFAULT NULL,
  `cache_replication` varchar(255) DEFAULT NULL,
  `format` varchar(255) DEFAULT NULL,
  `incremental_stats` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `stream` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

SET @tmp = '2016-11-03';
SELECT @tmp;

-- DROP TABLE c_etl_logs;

-- CREATE TABLE c_etl_logs AS 
SELECT
	 d.date AS raw_dt
	,DATE_FORMAT(d.date, '%m/%d/%y') AS date
	,IF(v.curr_rows, v.curr_rows, 0) AS value
	,IF(v.last_rows, v.last_rows, 0) AS old_value
	,IF(v.row_diff, v.row_diff, 0) AS diff
	,IF(v.var, v.var, 0) AS variance
 	,GREATEST( (CASE d.week WHEN @curr_week THEN @idx ELSE ( (@idx := @idx + 1) AND (@curr_week := d.week)) END), 1, @idx) - 1 AS x
	,(CASE d.day_name
		WHEN 'Monday' THEN 0
		WHEN 'Tuesday' THEN 1	
		WHEN 'Wednesday' THEN 2				
		WHEN 'Thursday' THEN 3						
		WHEN 'Friday' THEN 4						
		WHEN 'Saturday' THEN 5						
		WHEN 'Sunday' THEN 6
		ELSE -1
	END) AS y
FROM
	dates d LEFT JOIN (SELECT DISTINCT stream FROM etl_logs) s ON 1
	LEFT JOIN 
	(SELECT		
		e.stream 	
		,e.request_dt AS date	
		,e.request_dt - INTERVAL 1 WEEK AS last_date	
		,IF(e.rows, e.rows, 0) AS curr_rows
		,IF(_e.rows, _e.rows, 0) AS last_rows
		,IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0) AS row_diff
		,IF(_e.rows,  ROUND(((IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0)) / IF(_e.rows, _e.rows, 0)) * 100, 2), 0) AS var	
	FROM 		
		etl_logs e
		LEFT JOIN etl_logs _e ON (e.request_dt - INTERVAL 1 WEEK) = _e.request_dt AND e.stream = _e.stream
	WHERE 1		
		AND e.request_dt IS NOT NULL	
	) AS v ON d.date = v.date AND s.stream = v.stream,
		(SELECT @idx := -1,  @curr_week := '') r	
WHERE 1
-- 	AND d.date >= DATE_ADD(@tmp, INTERVAL(1-DAYOFWEEK(@tmp)+1) DAY) - INTERVAL 28 WEEK
-- 	AND d.date <= DATE_ADD(@tmp, INTERVAL(8-DAYOFWEEK(@tmp)) DAY)
	AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 56 WEEK
	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
-- 	AND d.date >= '2011-09-26'
ORDER BY
	x,y
;

-- DROP VIEW c_etl_labels;

-- CREATE VIEW c_etl_labels AS 
SELECT 
    DISTINCT CONCAT(DATE_FORMAT(DATE_ADD(date, INTERVAL(2-DAYOFWEEK(date)) DAY), '%m/%d'), '-', DATE_FORMAT(DATE_ADD(date, INTERVAL(8-DAYOFWEEK(date)) DAY), '%m/%d')) label
-- 	,d.* 
FROM 
	dates d
WHERE 1
-- 	AND d.date BETWEEN DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY) AND DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY)
-- 	AND d.date BETWEEN DATE_ADD(@tmp, INTERVAL(1-DAYOFWEEK(@tmp)) DAY) AND DATE_ADD(@tmp, INTERVAL(7-DAYOFWEEK(@tmp)) DAY)
-- 	AND d.date >= NOW() - INTERVAL 1 WEEK;
-- 	AND d.date > DATE_ADD((CURDATE() - INTERVAL 7 WEEK), INTERVAL(1-DAYOFWEEK((CURDATE() - INTERVAL 7 WEEK))) DAY) # START OF WEEK AT DAY 2 MONTHS AGO
-- 	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(7-DAYOFWEEK(CURDATE())) DAY) # START OF WEEK AT DAY 2 MONTHS AGO
	AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 56 WEEK
	AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
;

##########################################################################################

;
DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        date                 	DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (date)

) Engine=InnoDB;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO dates VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE dates;

CALL fill_date_dimension('2000-01-01','2100-01-01');
OPTIMIZE TABLE dates;

SELECT * FROM dates ORDER BY id DESC;

##########################################################################################
;

# CREATE etl_logs WITH ACTIVE RECORD

DROP PROCEDURE IF EXISTS load_c_etl_logs;
DELIMITER //
CREATE PROCEDURE load_c_etl_logs()
BEGIN
	DROP TABLE IF EXISTS c_etl_logs;
	CREATE TABLE `c_etl_logs` (
	  `stream` varchar(50) DEFAULT NULL,
	  `raw_dt` date NOT NULL,
	  `date` varchar(8) DEFAULT NULL,
	  `value` bigint(20) DEFAULT NULL,
	  `old_value` bigint(20) DEFAULT NULL,
	  `diff` bigint(20) DEFAULT NULL,
	  `variance` decimal(17,2) DEFAULT NULL,
	  `x` double DEFAULT NULL,
	  `y` int(2) NOT NULL DEFAULT '0',
	  `label` varchar(11) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
	INSERT INTO c_etl_logs
	SELECT
		 v.stream
		,d.date AS raw_dt
		,DATE_FORMAT(d.date, '%m/%d/%y') AS date
		,IF(v.curr_rows, v.curr_rows, 0) AS value
		,IF(v.last_rows, v.last_rows, 0) AS old_value
		,IF(v.row_diff, v.row_diff, 0) AS diff
		,IF(v.var, v.var, 0) AS variance
		,GREATEST( (CASE d.week WHEN @curr_week THEN @idx ELSE ( (@idx := @idx + 1) AND (@curr_week := d.week)) END), 1, @idx) - 1 AS x
		,(CASE d.day_name
			WHEN 'Monday' THEN 0
			WHEN 'Tuesday' THEN 1	
			WHEN 'Wednesday' THEN 2				
			WHEN 'Thursday' THEN 3						
			WHEN 'Friday' THEN 4						
			WHEN 'Saturday' THEN 5						
			WHEN 'Sunday' THEN 6
			ELSE -1
		END) AS y
		,CONCAT(DATE_FORMAT(DATE_ADD(d.date, INTERVAL(2-DAYOFWEEK(d.date)) DAY), '%m/%d'), '-', DATE_FORMAT(DATE_ADD(d.date, INTERVAL(8-DAYOFWEEK(d.date)) DAY), '%m/%d')) label
	FROM
		dates d LEFT JOIN (SELECT DISTINCT stream FROM etl_logs) s ON 1
		LEFT JOIN 
		(SELECT		
			e.stream 	
			,e.request_dt AS date	
			,e.request_dt - INTERVAL 1 WEEK AS last_date	
			,IF(e.rows, e.rows, 0) AS curr_rows
			,IF(_e.rows, _e.rows, 0) AS last_rows
			,IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0) AS row_diff
			,IF(_e.rows,  ROUND(((IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0)) / IF(_e.rows, _e.rows, 0)) * 100, 2), 0) AS var	
		FROM 		
			etl_logs e
			LEFT JOIN etl_logs _e ON (e.request_dt - INTERVAL 1 WEEK) = _e.request_dt AND e.stream = _e.stream
		WHERE 1		
			AND e.request_dt IS NOT NULL	
		) AS v ON d.date = v.date AND s.stream = v.stream,
		(SELECT @idx := -1,  @curr_week := '') r	
	WHERE 1
		AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 56 WEEK
		AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
	ORDER BY
		x,y
	;	
END
//
DELIMITER ;

TRUNCATE etl_logs;

SET @_stream = 'exploit_stream_mi';

-- LOAD DATA LOCAL INFILE '/Users/mfernando/Development/portal/db/load/exploit_stream_mi.csv' 
-- LOAD DATA LOCAL INFILE '/Users/mfernando/Development/portal/db/load/malware_stream.csv'
-- LOAD DATA LOCAL INFILE '/Users/mfernando/Development/portal/db/load/client_stream.csv' 
INTO TABLE etl_logs
FIELDS TERMINATED BY ','  
IGNORE 1 ROWS
(
--   `request_dt`
   @request_dt
  ,`rows`
  ,`files`
  ,`size`
  ,`bytes_cached`
  ,`cache_replication`
  ,`format` 
  ,`incremental_stats` 
  ,`location`
)
SET id = NULL
-- ,stream = 'expolit_stream_mi'
-- ,stream = 'malware_stream'
-- ,stream = 'client_stream'
,request_dt = IF(@request_dt REGEXP '.*-.*-', @request_dt, STR_TO_DATE(@request_dt, '%m/%d/%y'))
,created_at = CURRENT_TIMESTAMP
,updated_at = CURRENT_TIMESTAMP
;

SELECT '11/11/11' REGEXP '.*/.*/';

SELECT '2010-11-29' REGEXP '.*-.*-';

DELETE FROM etl_logs WHERE request_dt IS NULL OR request_dt LIKE 'T%' OR request_dt LIKE 'N%';

CALL load_c_etl_logs();

select distinct label from c_etl_logs;

select distinct stream from c_etl_logs where stream is not null; 

SELECT * FROM c_etl_logs;

CALL load_c_etl_logs();

DROP PROCEDURE IF EXISTS load_c_etl_logs;

DELIMITER //
CREATE PROCEDURE load_c_etl_logs() BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE _stream VARCHAR(255) DEFAULT "";
	DECLARE stream_cur CURSOR FOR SELECT DISTINCT stream FROM etl_logs WHERE stream IS NOT NULL;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	DROP TABLE IF EXISTS c_etl_logs;	
	CREATE TABLE `c_etl_logs` (
	  `stream` varchar(50) DEFAULT NULL,
	  `raw_dt` varchar(11) DEFAULT NULL,
	  `date` varchar(8) DEFAULT NULL,
	  `value` bigint(20) DEFAULT NULL,
	  `old_value` bigint(20) DEFAULT NULL,
	  `diff` bigint(20) DEFAULT NULL,
	  `variance` decimal(17,2) DEFAULT NULL,
	  `x` double DEFAULT NULL,
	  `y` int(2) NOT NULL DEFAULT '0',
	  `label` varchar(11) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
	OPEN stream_cur;
	read_loop: LOOP
		FETCH stream_cur INTO _stream;
    	IF done THEN
      		LEAVE read_loop;
      	ELSE

			INSERT INTO c_etl_logs
			SELECT
				 s.stream
				,d.date AS raw_dt
				,DATE_FORMAT(d.date, '%m/%d/%y') AS date
				,IF(v.curr_rows, v.curr_rows, 0) AS value
				,IF(v.last_rows, v.last_rows, 0) AS old_value
				,IF(v.row_diff, v.row_diff, 0) AS diff
				,IF(v.var, v.var, 0) AS variance
				,GREATEST( (CASE d.week WHEN @curr_week THEN @idx ELSE ( (@idx := @idx + 1) AND (@curr_week := d.week)) END), 1, @idx) - 1 AS x
				,(CASE d.day_name
					WHEN 'Monday' THEN 0
					WHEN 'Tuesday' THEN 1	
					WHEN 'Wednesday' THEN 2				
					WHEN 'Thursday' THEN 3						
					WHEN 'Friday' THEN 4						
					WHEN 'Saturday' THEN 5						
					WHEN 'Sunday' THEN 6
					ELSE -1
				END) AS y
				,CONCAT(DATE_FORMAT(DATE_ADD(d.date, INTERVAL(2-DAYOFWEEK(d.date)) DAY), '%m/%d'), '-', DATE_FORMAT(DATE_ADD(d.date, INTERVAL(8-DAYOFWEEK(d.date)) DAY), '%m/%d')) label
			FROM
				dates d LEFT JOIN (SELECT _stream AS stream) s ON 1
				LEFT JOIN 
				(SELECT		
					e.stream 	
					,e.request_dt AS date	
					,e.request_dt - INTERVAL 1 WEEK AS last_date	
					,IF(e.rows, e.rows, 0) AS curr_rows
					,IF(_e.rows, _e.rows, 0) AS last_rows
					,IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0) AS row_diff
					,IF(_e.rows,  ROUND(((IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0)) / IF(_e.rows, _e.rows, 0)) * 100, 2), 0) AS var	
				FROM 		
					etl_logs e
					LEFT JOIN etl_logs _e ON (e.request_dt - INTERVAL 1 WEEK) = _e.request_dt AND e.stream = _e.stream
				WHERE 1		
					AND e.request_dt IS NOT NULL) AS v ON d.date = v.date AND s.stream = v.stream,
				(SELECT @idx := -1,  @curr_week := '') r	
			WHERE 1
				AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 56 WEEK
				AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
			ORDER BY
				x,y; 
    	END IF;
  	END LOOP;
	CLOSE stream_cur;
	
END;
//
DELIMITER ;

				SELECT		
					e.stream 	
					
					,e.request_dt AS date	
					
					,e.request_dt - INTERVAL 1 WEEK AS last_date	

/* 					,STR_TO_DATE(e.request_dt, '%m/%d/%y') AS date	
					,STR_TO_DATE(e.request_dt, '%m/%d/%y') - INTERVAL 1 WEEK AS last_date	
 */
-- 					,IF(e.request_dt IS NULL, STR_TO_DATE(e.request_dt, '%m/%d/%y')) AS foo	

					,IF(e.rows, e.rows, 0) AS curr_rows
					,IF(_e.rows, _e.rows, 0) AS last_rows
					,IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0) AS row_diff
					,IF(_e.rows,  ROUND(((IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0)) / IF(_e.rows, _e.rows, 0)) * 100, 2), 0) AS var	
				FROM 		
					etl_logs e
					LEFT JOIN etl_logs _e ON (e.request_dt - INTERVAL 1 WEEK) = _e.request_dt AND e.stream = _e.stream
				WHERE 1		
					AND e.request_dt IS NOT NULL
				;
SELECT 
	e.*,
	d.*
FROM 
	etl_logs e LEFT JOIN dates d on e.request_dt = d.date
WHERE 1 
	AND e.id = 1 ;


SET @_stream = 'exploit_stream_mi';

			SELECT
				 s.stream
				,d.date AS raw_dt
				,DATE_FORMAT(d.date, '%m/%d/%y') AS date
				,IF(v.curr_rows, v.curr_rows, 0) AS value
				,IF(v.last_rows, v.last_rows, 0) AS old_value
				,IF(v.row_diff, v.row_diff, 0) AS diff
				,IF(v.var, v.var, 0) AS variance
				,GREATEST( (CASE d.week WHEN @curr_week THEN @idx ELSE ( (@idx := @idx + 1) AND (@curr_week := d.week)) END), 1, @idx) - 1 AS x
				,(CASE d.day_name
					WHEN 'Monday' THEN 0
					WHEN 'Tuesday' THEN 1	
					WHEN 'Wednesday' THEN 2				
					WHEN 'Thursday' THEN 3						
					WHEN 'Friday' THEN 4						
					WHEN 'Saturday' THEN 5						
					WHEN 'Sunday' THEN 6
					ELSE -1
				END) AS y
				,CONCAT(DATE_FORMAT(DATE_ADD(d.date, INTERVAL(2-DAYOFWEEK(d.date)) DAY), '%m/%d'), '-', DATE_FORMAT(DATE_ADD(d.date, INTERVAL(8-DAYOFWEEK(d.date)) DAY), '%m/%d')) label
			FROM
				dates d LEFT JOIN (SELECT @_stream AS stream) s ON 1
				LEFT JOIN 
				(SELECT		
					e.stream 	
					,e.request_dt AS date	
					,e.request_dt - INTERVAL 1 WEEK AS last_date	
					,IF(e.rows, e.rows, 0) AS curr_rows
					,IF(_e.rows, _e.rows, 0) AS last_rows
					,IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0) AS row_diff
					,IF(_e.rows,  ROUND(((IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0)) / IF(_e.rows, _e.rows, 0)) * 100, 2), 0) AS var	
				FROM 		
					etl_logs e
					LEFT JOIN etl_logs _e ON (e.request_dt - INTERVAL 1 WEEK) = _e.request_dt AND e.stream = _e.stream
				WHERE 1		
					AND e.request_dt IS NOT NULL) AS v ON d.date = v.date AND s.stream = v.stream,
				(SELECT @idx := -1,  @curr_week := '') r	
			WHERE 1
				AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 56 WEEK
				AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
			ORDER BY
				x,y; 
				
				select * from c_etl_logs;
				
				select * from etl_logs where request_dt = '2016-10-18';
				
##########################################################################################				
;

DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        date                 	DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (date)

) Engine=InnoDB;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO dates VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE dates;

CALL fill_date_dimension('2000-01-01','2100-01-01');

OPTIMIZE TABLE dates;

SELECT * FROM dates ORDER BY id DESC;
 
DROP PROCEDURE IF EXISTS load_c_etl_logs;

DELIMITER //
CREATE PROCEDURE load_c_etl_logs() BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE _stream VARCHAR(255) DEFAULT "";
	DECLARE stream_cur CURSOR FOR SELECT DISTINCT stream FROM etl_logs WHERE stream IS NOT NULL;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	DROP TABLE IF EXISTS c_etl_logs;	
	CREATE TABLE `c_etl_logs` (
	  `stream` varchar(50) DEFAULT NULL,
	  `raw_dt` varchar(11) DEFAULT NULL,
	  `date` varchar(8) DEFAULT NULL,
	  `value` bigint(20) DEFAULT NULL,
	  `old_value` bigint(20) DEFAULT NULL,
	  `diff` bigint(20) DEFAULT NULL,
	  `variance` decimal(17,2) DEFAULT NULL,
	  `x` double DEFAULT NULL,
	  `y` int(2) NOT NULL DEFAULT '0',
	  `label` varchar(11) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
	OPEN stream_cur;
	read_loop: LOOP
		FETCH stream_cur INTO _stream;
    	IF done THEN
      		LEAVE read_loop;
      	ELSE

			INSERT INTO c_etl_logs
			SELECT
				 s.stream
				,d.date AS raw_dt
				,DATE_FORMAT(d.date, '%m/%d/%y') AS date
				,IF(v.curr_rows, v.curr_rows, 0) AS value
				,IF(v.last_rows, v.last_rows, 0) AS old_value
				,IF(v.row_diff, v.row_diff, 0) AS diff
				,IF(v.var, v.var, 0) AS variance
				,GREATEST( (CASE d.week WHEN @curr_week THEN @idx ELSE ( (@idx := @idx + 1) AND (@curr_week := d.week)) END), 1, @idx) - 1 AS x
				,(CASE d.day_name
					WHEN 'Monday' THEN 0
					WHEN 'Tuesday' THEN 1	
					WHEN 'Wednesday' THEN 2				
					WHEN 'Thursday' THEN 3						
					WHEN 'Friday' THEN 4						
					WHEN 'Saturday' THEN 5						
					WHEN 'Sunday' THEN 6
					ELSE -1
				END) AS y
				,CONCAT(DATE_FORMAT(DATE_ADD(d.date, INTERVAL(2-DAYOFWEEK(d.date)) DAY), '%m/%d'), '-', DATE_FORMAT(DATE_ADD(d.date, INTERVAL(8-DAYOFWEEK(d.date)) DAY), '%m/%d')) label
			FROM
				dates d LEFT JOIN (SELECT _stream AS stream) s ON 1
				LEFT JOIN 
				(SELECT		
					e.stream 	
					,e.request_dt AS date	
					,e.request_dt - INTERVAL 1 WEEK AS last_date	
					,IF(e.rows, e.rows, 0) AS curr_rows
					,IF(_e.rows, _e.rows, 0) AS last_rows
					,IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0) AS row_diff
					,IF(_e.rows,  ROUND(((IF(e.rows, e.rows, 0) - IF(_e.rows, _e.rows, 0)) / IF(_e.rows, _e.rows, 0)) * 100, 2), 0) AS var	
				FROM 		
					etl_logs e
					LEFT JOIN etl_logs _e ON (e.request_dt - INTERVAL 1 WEEK) = _e.request_dt AND e.stream = _e.stream
				WHERE 1		
					AND e.request_dt IS NOT NULL) AS v ON d.date = v.date AND s.stream = v.stream,
				(SELECT @idx := -1,  @curr_week := '') r	
			WHERE 1
				AND d.date >= DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())+1) DAY) - INTERVAL 56 WEEK
				AND d.date <= DATE_ADD(CURDATE(), INTERVAL(8-DAYOFWEEK(CURDATE())) DAY)
			ORDER BY
				x,y; 
    	END IF;
  	END LOOP;
	CLOSE stream_cur;
	
END;
//
DELIMITER ;


TRUNCATE etl_logs;

-- LOAD DATA LOCAL INFILE '/Users/mfernando/Development/portal/db/load/exploit_stream_mi.csv' 
-- LOAD DATA LOCAL INFILE '/Users/mfernando/Development/portal/db/load/malware_stream.csv'
-- LOAD DATA LOCAL INFILE '/Users/mfernando/Development/portal/db/load/client_stream.csv' 
INTO TABLE etl_logs
FIELDS TERMINATED BY ','  
IGNORE 1 ROWS
(
   @request_dt
  ,`rows`
  ,`files`
  ,`size`
  ,`bytes_cached`
  ,`cache_replication`
  ,`format` 
  ,`incremental_stats` 
  ,`location`
)
SET id = NULL
-- ,stream = 'expolit_stream_mi'
-- ,stream = 'malware_stream'
-- ,stream = 'client_stream'
,request_dt = IF(@request_dt REGEXP '.*-.*-', @request_dt, STR_TO_DATE(@request_dt, '%m/%d/%y'))
,created_at = CURRENT_TIMESTAMP
,updated_at = CURRENT_TIMESTAMP
;

CALL load_c_etl_logs();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment