# Row Numbers for fact_analytics 20150721.r
# ~/git/orch/src/Looker/Row Numbers for fact_analytics 20150721.r

setScience("Snowflake_Scratch", subProj="row_index", create=FALSE, subl=FALSE, load=FALSE)
.us()

wh <- ifelse(sfIsWarehouseOn("LOOKER_WH_LARGE"), "LOOKER_WH_LARGE", "SCIENCE")
dbname <- getSnowflakeDB()
schema <- "production"
setSnowflake(wh=wh, dbname=dbname, schema=schema)

tbl <- "fact_analytics"

rowclause <- makeRowNumberClause(groupBy="dayid", order=c("dayid", "storeid", "labelid", "countryid", "uuid")) %>% 
              sprintf("(dayid * 1000000000) + %s", .)


makeQry(tbl=tbl, schema=schema, dbname=dbname, colsToPull=c(row_number=rowclause), aggFunc=NULL, limit=NULL) %>% as.character %>% cat("\n")

## ADD THE COLUMN
Qry.add_column <- "ALTER TABLE prod.production.fact_analytics DROP COLUMN row_number"
Qry.add_column <- "ALTER TABLE prod.production.fact_analytics ADD COLUMN row_number NUMERIC (17, 0)"
sfQry(Qry.add_column, wh=wh, dbname=dbname)

sfQry("SELECT dayid, storeid, row_number from prod.production.fact_analytics limit 3")

# SELECT   (dayid * 1000000000) + ROW_NUMBER() OVER (PARTITION BY dayid ORDER BY dayid, storeid, labelid, countryid, uuid) AS "row_number"  FROM  production.fact_analytics
ADD COLUMN ROW_NUMBER() OVER (PARTITION BY dayid ORDER BY dayid, storeid, labelid, countryid, uuid) AS row_number FROM  
Qry.populate_column <- "UPDATE prod.production.fact_analytics SET row_number = ((dayid * 1000000000) + ROW_NUMBER() OVER (PARTITION BY dayid ORDER BY dayid, storeid, labelid, countryid, uuid))"
Qry.populate_column <- "UPDATE prod.production.fact_analytics 
  SET row_number = (SELECT (dayid * 100000000000) + countryid * 100000000 + ROW_NUMBER() OVER (partition by dayid, countryid))"
sfQry(Qry.populate_column, wh=wh, dbname=dbname)

"SELECT dayid, countryid, labelid, artistid, (dayid * 100000000000) + countryid * 100000000 + ROW_NUMBER() OVER (partition by dayid, countryid order by  dayid, countryid, labelid, artistid) as row_number FROM (SELECT dayid, countryid, labelid, artistid from prod.production.fact_analytics limit 20000)" %>% sfQry -> DT.test; DT.test[, row_number2 := as.character(row_number)]; DT.test

DAYID  STORE  CTRY  LABELID      ROWS 
12345  12345   123   123456  12345678





SELECT dayid
FROM (SELECT dayid, countryid, from prod.production.fact_analytics limit 20000)

colsToPull <- c("DAYID", "STOREID", "countryid", "LABELID")
makeQry(tbl=tbl, colsToPull=colsToPull, key="colsToPull", colsToAgg=c(rows="*"), dateCol="dayid", minDate=6000) %>% sfQry -> DT.rows_per
DT.rows_per

WHERE dayid >= 6000

sfQry("SELECT dayid, displaydate from dev_engineering.production.dim_day where displaydate = '2015-06-01'")
sfQry("SELECT max(labelid) FROM dev_engineering.production.dim_label")




tbl.apple <- "staging_raw_apple_music"
tbl.spot <- c("spotify_streams_cropped_to_apple" = "staging_raw_spotify_v2")
schema     <- "production"
schema_out <- "AppleMusic"
cluster <- 7
wh <- ifelse(interactive(), getSnowflakeWH(), "CRON_JOBS_SMALL") %>% {ifelse(. == "", "LOOKER_WH_LARGE", .)}
dbname <- "prod"

stage_name <- "Dumps_For_AppleMusic_and_Spotify"
format_name <- "Ricks_Default_RedShift_Dump"

     

if (FALSE)
{
  ## The latter days, as of dayid = 6044, have about 70000000 (70 M) rows of data
  ## The can quickly reach (100 M), thus we should add dayid to 1000 M or 1 B
  ## 1000000000
    makeRowNumberClause(groupBy="dayid", order=c("dayid", "storeid", "labelid")) %>% 
    sprintf(
"   SELECT dayid * 1000000000 + %s, dayid, storeid, labelid
   from ((SELECT storeid, dayid, labelid from prod.production.fact_analytics WHERE dayid = 5342  limit 20) union (SELECT storeid, dayid, labelid from prod.production.fact_analytics WHERE dayid in (5341, 5343, 5344) limit 75))
   limit 230", .) %>% sfQry -> DT.test; print(DT.test, nrow=300)
}

makeRowNumberClause <- function(groupBy=NULL, orderBy=groupBy, useNames=TRUE, nm="row_number", AS_explicit=FALSE) {

  if (!missing(AS_explicit))
    stop ("'AS_explicit' not implemented -- did you mean to use 'nm' ?")


  if (!isTRUE(getOption("snowflake_inuse")))
    warning("It's possible that makeRowNumberClause() only works for Snowflake", .call=FALSE)

  if (isNULLorBlank(orderBy))
    stop ("order by must not be NULL or Blank")

  # quote_and_comma <- . %>% pasteQ(q='"', w="") %>% commaSep
  #  hmm... quoting seems to be a problem.  Dont quote for now
  quote_and_comma <- . %>% commaSep

  partition_clause <- ""
  if (!is.null(groupBy)) {
    if (useNames)
      groupBy %<>% colNamesFromVector()
    
    partition_clause <- groupBy %>% quote_and_comma %>% sprintf("PARTITION BY %s ", .)
  }

  if (useNames)
    orderBy %<>% colNamesFromVector()

  sprintf("ROW_NUMBER() OVER (%sORDER BY %s) AS \"%s\"", partition_clause, quote_and_comma(orderBy), nm)
  setNames(nm=nm, obj=sprintf("ROW_NUMBER() OVER (%sORDER BY %s)", partition_clause, quote_and_comma(orderBy)))
}

"SELECT  as row_numb, dayid, storeid
from prod.production.fact_analytics
limit 23
" %>% sfQry


day-country-label-store-release-track-uu