## looker_usage.r

setScience("WarehouseUsageForSnowflake", create=FALSE)
lib(reshape2)

# f.in.warehouse <- ingest.p("warehouse_usage_ORCHARD_20160630091846_1467292726992.txt")
# f.in.storage   <- ingest.p("storage_usage_ORCHARD_20160630092051_1467292851036.txt")
# DT.warehouse <- fread(f.in.warehouse) %>% cleanColNamesForSQL_
# DT.storage   <- fread(f.in.storage) %>% cleanColNamesForSQL_

files.storage   <- extractIngestFiles("storage",   ext="txt")
files.warehouse <- extractIngestFiles("warehouse", ext="txt")

DT.storage <- lapply(files.storage, fread) %>% rbindlist %>% cleanColNamesForSQL_ %>% unique(by=NULL)
DT.storage[, date := as.Date(date)]
setkeyIfNot(DT.storage, date)
DT.storage[, date_diff := as.numeric(diffNA(date, padTop=FALSE))]


## Confirm all dates are present
stopifnot(0 == nrow(DT.storage[(date_diff != 1)]))

## Create neatly formatted bytes columns
cols_bytes <- extract("bytes", DT.storage)
DT.storage[, removeText("_bytes", cols_bytes) := lapply(.SD, formatBytes), .SDcols=cols_bytes]


## WAREHOUSE USAGE
DT.warehouse <- lapply(files.warehouse, fread) %>% rbindlist %>% cleanColNamesForSQL_ %>% unique(by=NULL)
DT.warehouse[, date := as.Date(date)]
setkeyIfNot(DT.warehouse, date, warehouse)
DT.warehouse[, date_diff := as.numeric(diffNA(date, padTop=FALSE)), by=warehouse]

## Drop any data after today (zeros are generally included through until end of month)
DT.warehouse <- DT.warehouse[date <= today()]

## This wouldn't necessarilly not give any errors, because a warehouse may not be used for a while
DT.warehouse[(date_diff != 1)]

## If we remove the warehouse information, all dates should be present
## Confirm that no dates are missing
stopifnot(DT.warehouse[, .N, keyby=date][, ][, all(as.numeric(diff(date)) == 1, na.rm=TRUE)])

### -------------------------------------------------- ##

### LOAD IN ESTIMATES;  These files were outputed by the script at 
###     ~/git/orch/src/PricingModelForDB/Snowflake Estimates - Isolate into standalone tables.r
###
DT.snowflake_estimated_storage   <- extractIngestFiles("storage",   sub="SnowflakeEstimateTables", ext="tsv") %>% fread
DT.snowflake_estimated_warehouse <- extractIngestFiles("warehouse", sub="SnowflakeEstimateTables", ext="tsv") %>% fread

DT.snowflake_estimated_storage[, month := as.Date(month)]
DT.snowflake_estimated_warehouse[, month := as.Date(month)]

setkey(DT.snowflake_estimated_storage,   month)
setkey(DT.snowflake_estimated_warehouse, month, purpose)

## VIEW
DT.warehouse[, sum(credits), by=warehouse][order(V1)]
DT.snowflake_estimated_warehouse[, sum(credits), by=purpose]

## ADD IN PURPOSE
  ## set the purpose
  DT.warehouse[, purpose := dict.warehouse_purpose[warehouse]]
  ## Check for unknown purposes
  if (any (is.na(DT.warehouse$purpose))) {
    warning("There are warehouses whose purpose is unknown: \n\t", pasteC(DT.warehouse[is.na(purpose), unique(warehouse)], C="\n\t"))
    ## Assign them purpose
    DT.warehouse[is.na(purpose), purpose := sprintf("unknown (%s)", warehouse)]
  }

## Aggregate by month
{
  catn("\n\n----=[  Aggregating DT.warehouse_compare  ]=-----")
    DT.warehouse_compare <- aggregateMonthly(DT.warehouse, dateCol="date", by=c("date", "purpose"), colsToAgg=c("credits"),     aggFunc="sum")
  catn("\n\n----=[  Aggregating DT.storage_compare    ]=-----")
    DT.storage_compare   <- aggregateMonthly(DT.storage,   dateCol="date", by=c("date"),            colsToAgg=c("total_bytes"), aggFunc="mean")
}
## Convert to TB
DT.storage_compare[, TB_used := formatBytes(total_bytes, min="TB") %>% removeText("\\s+TB", .) %>% as.numeric]
## There should not be any NAs
stopifnot(DT.storage_compare[, !is.na(TB_used)])

## Add in Storage Comparisons
addColsFrom_(DT.receiving=DT.storage_compare, DT.snowflake_estimated_storage, colsToBring=c("TB_budgeted"="TB_total_f"), joinCols="month")
DT.storage_compare[, overage := TB_used - TB_budgeted]

## Add in Warehouse Comparisons
addColsFrom_(DT.receiving=DT.warehouse_compare, DT.snowflake_estimated_warehouse, colsToBring=c("credits_budgeted"="credits_f"), joinCols=c("month", "purpose"))

## MELT THE DATA FOR GRAPHING
{
  DT.warehouse_compare.melted <- melt.data.table(DT.warehouse_compare, id.vars=c("month", "purpose"), variable.name="budget_vs_actual", value.name="credits")
  DT.warehouse_compare.melted[budget_vs_actual == "credits",          budget_vs_actual := "Actual"]
  DT.warehouse_compare.melted[budget_vs_actual == "credits_budgeted", budget_vs_actual := "Budgeted"]
  DT.warehouse_compare.melted <- DT.warehouse_compare.melted[budget_vs_actual != "overage"]
}

## Add some columns for the report
DT.warehouse_compare[, Credits_Over := credits - credits_budgeted]
DT.warehouse_compare[, Percent_Over := Credits_Over / credits_budgeted]
addColsFrom_(DT.warehouse_compare, DT.giving=DT.warehouse[, lunique(date), keyby=list(month=monthFloor(date), purpose)], colsToBring=c("days_of_data"="V1"), joinCols=c("month", "purpose"))
DT.warehouse_compare

## EXPORT THE DATA
f.warehouse_compare <- writeDT(DT.warehouse_compare)
f.storage_compare <- writeDT(DT.storage_compare)


## ACTUAL CREDIT USAGE
P.actual_credit_usage <- ggLinegraph(DT.warehouse_compare.melted, x="month", y="credits", color="purpose", linetype="budget_vs_actual", shape="budget_vs_actual", title="Actual Credit Usage by Purpose")
f.actual_credit_usage <- ggsave.out(P.actual_credit_usage)

DT.warehouse_by_week <- DT.warehouse[date >= "2016-01-01", list(credits=sum(credits)), by=list(week=as.Date("2015-12-31") + (7*(week(date)-1)), warehouse, purpose)]
DT.warehouse_by_week <- DT.warehouse_by_week[week != max(week)]
## Only look at the warehouse avging over 100 credits per week in the last 100 days
warehouses_concerning <- DT.warehouse_by_week[(week >= (today() - 100)), mean(credits), by=warehouse][V1 > 100, warehouse]
ggLinegraph(DT.warehouse_by_week[(warehouse %in% warehouses_concerning)], x="week", y="credits", color="warehouse", title="2016 Actual Credit Usage by Warehouse", smooth=FALSE)

ggLinegraph(DT.warehouse[(warehouse %in% warehouses_concerning)], x="date", y="credits", color="warehouse", title="2016 Actual Credit Usage by Warehouse", smooth=FALSE)

## My team
P.actual_credit_usage_datalytics <- ggLinegraph(DT.warehouse[year(date) == 2016][purpose == "analytics"], x="date", y="credits", color="warehouse", title=subtext("Data Analytics", "2016 Actual Credit Usage by Warehouse"), smooth=TRUE, dotsOn=FALSE)
f.actual_credit_usage_datalytics <- ggsave.out(P.actual_credit_usage_datalytics)




## PLOT FILES
f.warehouse_compare
f.storage_compare
f.actual_credit_usage
f.actual_credit_usage_datalytics

## Next steps, copy the TSV files into Excel
if (FALSE) {
  .o(f.warehouse_compare)
  .o(f.storage_compare)
  .o(f.actual_credit_usage)
  .o(f.actual_credit_usage_datalytics)
}


# .o(f.actual_credit_usage)
# .o(f.actual_credit_usage_datalytics)
# 
# .o(ingestDir)