-------------------------------------------------------------- -- [08:36] sink() diverted to this file, " ~/git/orch/log/MgmtReport/Automation_ETL+MGMT_LogFile_20141129_1136.logr " -------------------------------------------------------------- -- [08:36] sink() diverted to this file, " ~/git/orch/log/MgmtReport/Automation_ETL+MGMT_LogFile_20141129_1136.logr " -------- FRESH / RESET HAPPENED HERE -------- -- [08:36] Loading required package: XML Loading required package: DBI ----------- RUNNING GPU UPDATE ----------- -- [08:36] ..######....#######......######...######..####.########.##....##..######..######## .##....##..##.....##....##....##.##....##..##..##.......###...##.##....##.##...... .##........##.....##....##.......##........##..##.......####..##.##.......##...... .##...####.##.....##.....######..##........##..######...##.##.##.##.......######.. .##....##..##.....##..........##.##........##..##.......##..####.##.......##...... .##....##..##.....##....##....##.##....##..##..##.......##...###.##....##.##...... ..######....#######......######...######..####.########.##....##..######..######## 01000111.01101111...01010011.01100011.01101001.01100101.01101110.01100011.01100101 Will run the query against cluster #09 Running query [Began at 11:36 AM] --------------------------------------------------------------------------------------------------------------- SELECT storeid AS store, accountingperiodid AS periodid, transactiontypeid AS transac_typeid , SUM(sales) as paidunits , SUM(gross) as gross FROM production.fact_sales WHERE accountingperiodid >= (SELECT max(periodid) FROM bi.period_view WHERE perioddate <= sysdate - INTERVAL '24 MONTH') GROUP BY 1, 2, 3 --------------------------------------------------------------------------------------------------------------- Dim of results is 2,405 rows X 5 cols Total time to execute query was 6.355 seconds Key has been set Warning in eval(expr, envir, enclos) : There is 1 groups with $0 gross. Namely store transac_typeid date paidunits gross GPU 1: 1 6 2013-03-01 0 0 NA Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-07-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-06-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-05-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-04-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-03-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-02-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2014-01-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-12-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-11-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-10-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-09-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-08-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-07-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-06-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-05-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-04-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-03-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-02-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2013-01-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2012-12-01' Calculating GPU_Est_Fore & GPU_Est_MA for date == '2012-11-01' Running query [Began at 11:37 AM] --------------------------------------------------------------------------------------------------------------- SELECT storeid AS store, transactiontypeid AS transac_typeid, CASE WHEN download_activity_date between '2011-10-02' and '2011-11-05' THEN '2011-10-01' WHEN download_activity_date between '2011-11-06' and '2011-12-03' THEN '2011-11-01' WHEN download_activity_date between '2011-12-04' and '2011-12-31' THEN '2011-12-01' WHEN download_activity_date between '2012-01-01' and '2012-02-04' THEN '2012-01-01' WHEN download_activity_date between '2012-02-05' and '2012-03-03' THEN '2012-02-01' WHEN download_activity_date between '2012-03-04' and '2012-03-31' THEN '2012-03-01' WHEN download_activity_date between '2012-04-01' and '2012-05-05' THEN '2012-04-01' WHEN download_activity_date between '2012-05-06' and '2012-06-02' THEN '2012-05-01' WHEN download_activity_date between '2012-06-03' and '2012-06-30' THEN '2012-06-01' WHEN download_activity_date between '2012-07-01' and '2012-08-04' THEN '2012-07-01' WHEN download_activity_date between '2012-08-05' and '2012-09-01' THEN '2012-08-01' WHEN download_activity_date between '2012-09-02' and '2012-09-29' THEN '2012-09-01' WHEN download_activity_date between '2012-09-30' and '2012-11-03' THEN '2012-10-01' WHEN download_activity_date between '2012-11-04' and '2012-12-01' THEN '2012-11-01' WHEN download_activity_date between '2012-12-02' and '2012-12-29' THEN '2012-12-01' WHEN download_activity_date between '2012-12-30' and '2013-02-02' THEN '2013-01-01' WHEN download_activity_date between '2013-02-03' and '2013-03-02' THEN '2013-02-01' WHEN download_activity_date between '2013-03-03' and '2013-03-30' THEN '2013-03-01' WHEN download_activity_date between '2013-03-31' and '2013-05-04' THEN '2013-04-01' WHEN download_activity_date between '2013-05-05' and '2013-06-01' THEN '2013-05-01' WHEN download_activity_date between '2013-06-02' and '2013-06-29' THEN '2013-06-01' WHEN download_activity_date between '2013-06-30' and '2013-08-03' THEN '2013-07-01' WHEN download_activity_date between '2013-08-04' and '2013-08-31' THEN '2013-08-01' WHEN download_activity_date between '2013-09-01' and '2013-09-28' THEN '2013-09-01' WHEN download_activity_date between '2013-09-29' and '2013-11-02' THEN '2013-10-01' WHEN download_activity_date between '2013-11-03' and '2013-11-30' THEN '2013-11-01' WHEN download_activity_date between '2013-12-01' and '2013-12-28' THEN '2013-12-01' WHEN download_activity_date between '2013-12-29' and '2014-02-01' THEN '2014-01-01' WHEN download_activity_date between '2014-02-02' and '2014-03-01' THEN '2014-02-01' WHEN download_activity_date between '2014-03-02' and '2014-03-29' THEN '2014-03-01' WHEN download_activity_date between '2014-03-30' and '2014-05-03' THEN '2014-04-01' WHEN download_activity_date between '2014-05-04' and '2014-05-31' THEN '2014-05-01' WHEN download_activity_date between '2014-06-01' and '2014-06-28' THEN '2014-06-01' WHEN download_activity_date between '2014-06-29' and '2014-08-02' THEN '2014-07-01' WHEN download_activity_date between '2014-08-03' and '2014-08-30' THEN '2014-08-01' WHEN download_activity_date between '2014-08-31' and '2014-09-27' THEN '2014-09-01' WHEN download_activity_date between '2014-09-28' and '2014-11-01' THEN '2014-10-01' WHEN download_activity_date between '2014-11-02' and '2014-11-29' THEN '2014-11-01' WHEN download_activity_date between '2014-11-30' and '2014-12-27' THEN '2014-12-01' WHEN download_activity_date between '2014-12-28' and '2015-01-31' THEN '2015-01-01' WHEN download_activity_date between '2015-02-01' and '2015-02-28' THEN '2015-02-01' ELSE '' END AS date , max(download_activity_date) AS max_date, sum(paidunits) AS paidunits FROM fact_analytics WHERE (download_activity_date BETWEEN sysdate - INTERVAL '24 MONTH' AND sysdate) AND storeid = 1 -- ## I was previously removing NULL labelids, due to an issue matching to SC. But there should not be any NULLs in labelid -- AND NOT labelid is NULL GROUP BY 1, 2, 3 ORDER BY store --------------------------------------------------------------------------------------------------------------- Dim of results is 136 rows X 5 cols Total time to execute query was 12.917 seconds Running query [Began at 11:38 AM] --------------------------------------------------------------------------------------------------------------- SELECT storeid AS store, transactiontypeid AS transac_typeid, to_char(download_activity_date, 'YYYY-MM-01'), max(download_activity_date) AS max_date, sum(paidunits) AS paidunits FROM fact_analytics WHERE (download_activity_date BETWEEN sysdate - INTERVAL '24 MONTH' AND sysdate) AND NOT (storeid = 1) -- ## I was previously removing NULL labelids, due to an issue matching to SC. But there should not be any NULLs in labelid -- AND NOT labelid is NULL GROUP BY 1, 2, 3 ORDER BY store --------------------------------------------------------------------------------------------------------------- Dim of results is 535 rows X 5 cols Total time to execute query was 3.12 minutes -= Creating Backup of DT.MonthlyAnalytics =- **** The following store+transaction_type are missing (looking back to 2013-11-01) **** --------------------------------------------------------------------------------------------- storeid store_name transac_typeid transac_type_abbr transac_type missing_from 1: 1 iTunes 6 UA Upgraded Download Albums analytics 2: 1 iTunes 27 NR Non-interactive Radio Revenue analytics 3: 11 24/7 12 DV Video Download Purchase analytics 4: 11 24/7 19 DT Download Tracks analytics 5: 11 24/7 20 TD Tethered Downloads analytics 6: 11 24/7 23 DA Download Albums analytics 7: 153 MusicLoad 1 S Subscription Audio Streams analytics 8: 187 Amazon 28 CL Cloud Match Unit analytics 9: 286 Spotify 19 DT Download Tracks analytics 10: 424 rDio 19 DT Download Tracks analytics 11: 424 rDio 23 DA Download Albums analytics 12: 440 simfy GmbH 10 AS Ad Supported Stream analytics 13: 463 Google Play 12 DV Video Download Purchase analytics 14: 463 Google Play 16 VR Video Rental analytics 15: 497 Muve 1 S Subscription Audio Streams analytics 16: 497 Muve 4 DR Downloaded Ringtone analytics 17: 497 Muve 5 RB Ringback analytics 18: 453 YouTube 31 US Unmonetized Audio Streams accounting 19: 453 YouTube 32 UV Unmonetized Video Streams accounting 20: 453 YouTube 37 NA NA accounting 21: 453 YouTube 38 NA NA accounting storeid store_name transac_typeid transac_type_abbr transac_type missing_from updating ~/git/orch/src/chartio/_latestBackups.r with DT.MonthlyAnalytics+No SC Group - Prior to inserting imputed values ----------------------------------------------------------------- Some values of 'GPU_using' are still NA ----------------------------------------------------------------- date store transac_typeid max_date max_date_bygrp paidunits month_end month_start days_in_month 1: 2013-04-01 1 6 2013-05-04 2013-04-05 1 2013-05-04 2013-03-31 35 2: 2014-09-01 453 31 2014-09-30 2014-09-30 0 2014-09-30 2014-09-01 30 3: 2014-10-01 453 31 2014-10-31 2014-10-31 0 2014-10-31 2014-10-01 31 4: 2014-11-01 453 31 2014-11-23 2014-11-23 0 2014-11-30 2014-11-01 30 5: 2014-09-01 453 32 2014-09-30 2014-09-30 0 2014-09-30 2014-09-01 30 6: 2014-10-01 453 32 2014-10-31 2014-10-31 0 2014-10-31 2014-10-01 31 7: 2014-11-01 453 32 2014-11-23 2014-11-23 0 2014-11-30 2014-11-01 30 8: 2014-09-01 453 37 2014-09-30 2014-09-14 611705273 2014-09-30 2014-09-01 30 9: 2014-10-01 453 37 2014-10-31 2014-10-31 243832559 2014-10-31 2014-10-01 31 10: 2014-11-01 453 37 2014-11-23 2014-11-23 1164651226 2014-11-30 2014-11-01 30 11: 2014-09-01 453 38 2014-09-30 2014-09-14 173643147 2014-09-30 2014-09-01 30 12: 2014-10-01 453 38 2014-10-31 2014-10-31 71664672 2014-10-31 2014-10-01 31 13: 2014-11-01 453 38 2014-11-23 2014-11-23 306075613 2014-11-30 2014-11-01 30 14: 2014-10-01 496 1 2014-10-31 2014-10-31 0 2014-10-31 2014-10-01 31 15: 2014-11-01 496 1 2014-11-27 2014-11-27 0 2014-11-30 2014-11-01 30 16: 2014-10-01 496 19 2014-10-31 2014-10-31 253380 2014-10-31 2014-10-01 31 17: 2014-11-01 496 19 2014-11-27 2014-11-27 328755 2014-11-30 2014-11-01 30 18: 2014-10-01 496 23 2014-10-31 2014-10-31 111409 2014-10-31 2014-10-01 31 19: 2014-11-01 496 23 2014-11-27 2014-11-27 85199 2014-11-30 2014-11-01 30 days_present days_missing paidunits_expected_ratio paidunits_expected GPU_using GPU_is_estimate GPU_estimate 1: 35 0 1.00000 1 NA TRUE NA 2: 30 0 1.00000 0 NA TRUE NA 3: 31 0 1.00000 0 NA TRUE NA 4: 23 7 1.30435 0 NA TRUE NA 5: 30 0 1.00000 0 NA TRUE NA 6: 31 0 1.00000 0 NA TRUE NA 7: 23 7 1.30435 0 NA TRUE NA 8: 30 0 1.00000 611705273 NA TRUE NA 9: 31 0 1.00000 243832559 NA TRUE NA 10: 23 7 1.30435 1519110295 NA TRUE NA 11: 30 0 1.00000 173643147 NA TRUE NA 12: 31 0 1.00000 71664672 NA TRUE NA 13: 23 7 1.30435 399229060 NA TRUE NA 14: 31 0 1.00000 0 NA TRUE NA 15: 27 3 1.11111 0 NA TRUE NA 16: 31 0 1.00000 253380 NA TRUE NA 17: 27 3 1.11111 365283 NA TRUE NA 18: 31 0 1.00000 111409 NA TRUE NA 19: 27 3 1.11111 94666 NA TRUE NA NA_location_gpu_using 1: before 2: before 3: before 4: before 5: before 6: before 7: before 8: before 9: before 10: before 11: before 12: before 13: before 14: before 15: before 16: before 17: before 18: before 19: before ------------ The corresponding Transsaction Types are ------------ transac_typeid transac_typeid transac_type_abbr transac_type 1: 1 1 S Subscription Audio Streams 2: 6 6 UA Upgraded Download Albums 3: 19 19 DT Download Tracks 4: 23 23 DA Download Albums 5: 31 31 US Unmonetized Audio Streams 6: 32 32 UV Unmonetized Video Streams 7: 37 NA NA NA 8: 38 NA NA NA ----------------------------------------------------------------- updating ~/git/orch/src/chartio/_latestBackups.r with DT.MonthlyAnalytics+After_filling_GPU_using Uploading the DT.MonthlyAnalytics data to cluster #09 The following cols will be converted to integer: store transac_typeid days_in_month days_present days_missing The following cols will be treated as numeric: paidunits paidunits_expected GPU_using GPU_estimate paidunits_expected_ratio * Truncating Table "bi.GPU" -- [08:41] * Inserting into Table "bi.GPU" -- [08:41] Confirmed that bi.GPU has been written properly updating permissions for TABLE bi.gpu and orcdpipeline -= Restoring cluster.out from Backup (cluster.out.bak) =- Saving Image for [projName: chartio_ImageSave] [subProj: ] Saving with time stamp of 2014-11-29 08:41:32 Time it took to save the 3.82 MB image to 'chartio_ImageSave_20141129_1141.Rda' was 1.5 seconds. GPU Update image saved to '~/git/orch/ImageSaves/chartio/chartio_ImageSave_20141129_1141.Rda' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ------------------ git Pull Request ------------------ Executing git pull under user rsaporta Already up-to-date. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Reloading utils ... LOADED. Updating tables analytics AND accounting -- [08:41] Error in notifyIfError(expr = e, msg = sprintf("There was an error in running\n updateDeNormTable(%s)", : ERROR in ETL Occurred There was an error in running updateDeNormTable(analytics): <> 11: stop(msg.out) at notify.r#86 10: notifyIfError(expr = e, msg = sprintf("There was an error in running\n updateDeNormTable(%s)", tbl), subj = "ERROR in ETL Occurred", fail = TRUE) at MGMT_REPORT_FULL_AUTO.r#128 9: value[[3L]](cond) 8: tryCatchOne(expr, names, parentenv, handlers[[1L]]) 7: tryCatchList(expr, classes, parentenv, handlers) 6: tryCatch(expr = { for (tbl in tbls_to_update) { ret[[tbl]] <- updateDeNormTable(tbl = tbl, schema = "bi", delim = "\t", manifest = FALSE, gzip = FALSE, cluster.in = 9, cluster.out = 9, projName = "DeNormalizing", add_aggregated = FALSE, dontRename = FALSE, stamp = stamp, skip.select = skip.select.in_Denormalizing, verbose = TRUE) timeCheck[[sprintf("end.%s", tbl)]] <- Sys.time() } }, error = function(e) notifyIfError(expr = e, msg = sprintf("There was an error in running\n updateDeNormTable(%s)", tbl), subj = "ERROR in ETL Occurred", fail = TRUE)) at MGMT_REPORT_FULL_AUTO.r#106 5: eval(expr, envir, enclos) 4: eval(ei, envir) 3: withVisible(eval(ei, envir)) 2: source("~/git/orch/src/MGMT_REPORT_FULL_AUTO.r") 1: source("~/git/orch/src/MGMT_REPORT_FULL_AUTO.r") Turning off sink() -------------------------------------------------------------- -- [21:26]