# screen -xRR ParentUPC stop(" Jan 22 2016 Most of this still works, however the logic is less than perfect Specifically, the biggest indicator should be catalogid I know use the view parent_upc_view and the function parent_upc_score() ") # source("~/git/orch/src/MasterUPC/MasterUPC.r") setScience("MasterUPC", create=FALSE, subl=FALSE) setGitBranchToSystem(); .g() cluster <- 4 wh <- getWH_by_interactive() dbname <- "prod" dict.manual_parent_upcs <- get_dict_manual_parent_upcs() ## This is needed for RPostgreSQL setMethod("dbIsValid", "PostgreSQLConnection", function(dbObj, ...) { isValid <- tryCatch({dbGetInfo(dbObj)}, error = function(e) NULL) !is.null(isValid) }) ## Get release metadata from Redshift since it will be more up to date setDBall(cluster=4) DT.releases <- runQry("SELECT * FROM PRODUCTION.DIM_RELEASE", cluster=4) setnames(DT.releases, "releasename", "release_name") setIDCols(DT.releases) ## hmm..... There are no rows where display_upc is different. Does this mean that releaseid does NOT lose any data, or does it ## mean that display_upc DID lose data in the R sqlExecute() command? setcolorderpt(DT.releases, start=c("releaseid", "display_upc")) stopifnot(!nrow(DT.releases[releaseid != removeText("^0+", display_upc)])) ## find duplicates by catalogid DT.releases[!is.na(catalogid), parent_by.catalogid := min(display_upc), by=list(catalogid, labelid)] ## SOME MANUALLY IDENTIFIED ITEMS TO NOT GROUP if (FALSE) ### NOT IMPLEMENTED not_group.release_name_cleaned <- c("DO NOT DISTRIBUTE") ## wrapper-function with selected arg values clean_text <- {. %>% clean_names_to_simple_alpha(whitespace=TRUE, inside_parens=TRUE, parens=TRUE, trim=TRUE, tolower=TRUE)} ## clean up release_name DT.releases[, release_name_cleaned := clean_text(release_name)] setcolorderpt(DT.releases, endCols=c("release_name", "release_name_cleaned")) ## Find parent id for display_upc DT.releases[, parent_by.release_name := min(display_upc), keyby=list(artistid, labelid, release_name_cleaned)] ## Identify which has multiple DT.releases[, has_multiple := parent_by.catalogid != parent_by.release_name | parent_by.catalogid != display_upc] ## Show rows where ONLY parent_by.catalogid is different DT.releases[parent_by.release_name == display_upc & parent_by.catalogid != display_upc] ## For those catalogids where the release_name is NOT the same, ## Check if artistID is the same; if NOT, then do NOT consider them the same DT.releases[, possibly_wrong_by.catalogid := parent_by.release_name == display_upc & parent_by.catalogid != display_upc] DT.releases[, parent_by.catalogid_tighter := parent_by.catalogid] DT.releases[(possibly_wrong_by.catalogid) , parent_by.catalogid_tighter := min(display_upc), by=list(catalogid, labelid, artistid)] ## SEE WHICH ROWS MIGHT STILL BE GETTING GROUPED TOGETHER, INCORRECTLY. if (FALSE) { tmp.tighters_to_check <- DT.releases[parent_by.release_name == display_upc & parent_by.catalogid_tighter != display_upc, unique(parent_by.catalogid_tighter)] for (i in seq_along(tmp.tighters_to_check)) print(DT.releases[parent_by.catalogid_tighter == tmp.tighters_to_check[[i]]]) } ## TODO: One option is to create a broader "PARENT UPC BROAD GROUP" ## and take out all \\d numbers from release_name_cleaned ## The parent upc group will be parent_by.release_name unless parent_by.catalogid_tighter is different DT.releases[, min_upc_in_group := parent_by.release_name] DT.releases[min_upc_in_group == display_upc, min_upc_in_group := parent_by.catalogid_tighter] message("This method might actually cause some issues: Specifically, for every min_upc_in_group there is at least one release where the equality will hold How do we know that we are not over-writing mid group and breaking it? Two things give us confidence: 1. we always take the MIN upc, so there would have to be a both a group-mismatch AND the mismatch would have to be smaller than any other upc 2. Even if this does break up some groups, it's not a terrible problem; Afterall, we are not sure that the group is correct in the first place and perhaps the catalogid is distinguishing between them Incidentally --- should we check for same release_name but different catalogid? ") DT.upc_parent_table <- DT.releases[, list(min_upc_in_group, original_upc=display_upc, is_deleted=deletions=='Y')] ## NOTE: What still remains is identifying which should be the actual "parent" ## Use lifetime sales to determine DT.lifetime_sales_by_release <- sfQry("SELECT releaseid as UPC , sum(gross) as lifetime_revenue FROM production.fact_sales group by 1 order by 1") setIDCols(DT.lifetime_sales_by_release) setkeyIfNot(DT.upc_parent_table, original_upc, organize=TRUE) setkeyIfNot(DT.lifetime_sales_by_release, upc, organize=TRUE) jesusForData(DT.lifetime_sales_by_release, DT.upc_parent_table, DT.releases) ## Add in revenue information, filling in NAs with 0 addColsFrom_(DT.upc_parent_table, DT.lifetime_sales_by_release, colsToBring="lifetime_revenue", joinCols.r="original_upc", joinCols.g="upc") DT.upc_parent_table[is.na(lifetime_revenue), lifetime_revenue := 0] ## Pick a parent upc, taking into account deletions ## Note that original_upc is key, hence taking [[1]] grabs the smallest DT.upc_parent_table[, parent_upc := if (all(is_deleted)) original_upc[lifetime_revenue == max(lifetime_revenue)][[1]] else original_upc[lifetime_revenue == max(lifetime_revenue[!is_deleted])][[1]] , by=min_upc_in_group] ## (most of the ties here for parent_upc will be when revenue is 0 or close to 0) message("Remember to check for 'e+11' rounding errors") setcolorderpt(DT.upc_parent_table, startCols=c("original_upc", "parent_upc"), endCols="lifetime_revenue") # { # if (any(grep("[[:alpha:]]", removeText("e\\+", DT.upc_parent_table$original_upc)))) # DT.upc_parent_table[, original_upc := as.character(original_upc)] # else # DT.upc_parent_table[, original_upc := as.numeric(original_upc)] # } setIDCols(DT.upc_parent_table, extract("upc", DT.upc_parent_table)) ## ------------- MANUAL DICT -------------------- ## ## Add in manual overrides setkeyIfNot(DT.upc_parent_table, original_upc) ## Two parts -- make sure no extra members; DT.upc_parent_table[unlist(dict.manual_parent_upcs), parent_upc := original_upc] for (.upc in names(dict.manual_parent_upcs)) DT.upc_parent_table[.(dict.manual_parent_upcs[[.upc]]), c("parent_upc", "min_upc_in_group") := list(.upc, .upc)] ## ------------- MANUAL DICT -------------------- ## ## ------------- ADD METADATA -------------------- ## metaCols <- c("release_name") metaCols.parent <- metaCols %>% setNames(nm=paste0("parent_", .)) metaCols.orig<- metaCols %>% setNames(nm=paste0("original_", .)) addColsFrom_(DT.upc_parent_table, DT.releases, joinCols.g="display_upc", joinCols.r="parent_upc", colsToBring=c(metaCols.parent, parent_release_name_cleaned="release_name_cleaned")) addColsFrom_(DT.upc_parent_table, DT.releases, joinCols.g="display_upc", joinCols.r="original_upc", colsToBring=metaCols.orig) setnames(DT.upc_parent_table, "is_deleted", "original_is_deleted") setnames(DT.upc_parent_table, "lifetime_revenue", "original_lifetime_revenue") ## Quote marks in strings mess up Snowflake imports. For now, remove them. ## TODO: Reach out to SF Support about this DT.upc_parent_table[, parent_release_name := gsub("\"", "", parent_release_name)] DT.upc_parent_table[, original_release_name := gsub("\"", "", original_release_name)] ## ------------- ADD METADATA -------------------- ## ## Verbose, just to inform the number of releases (under 1.5 M) message(sprintf("There are %10s releases and\nThere are %10s parent releases", formnumb(round=FALSE, DT.upc_parent_table[, length(unique(original_upc))]), formnumb(round=FALSE, DT.upc_parent_table[, length(unique(parent_upc))]))) ## ------------- EXPORT TO SNOWFLAKE -------------------- ## setSnowflake(wh=wh, dbname=dbname, start=TRUE) ingestIntoSQL(DT.upc_parent_table, append=FALSE, drop=TRUE, transient=TRUE, snowflake=TRUE, datetime_type='TIMESTAMP_NTZ', add.ingestDate=FALSE) ## Save Everything jesusForData() ## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## "Below is scrap work still worth saving; Namely I used it to give an idea of which columns could be used to help identify matching UPCs" ## CHECK POSSIBLE IDCOLS if (FALSE) { possibles <- sapply(DT.releases, is.idcol) %>% nwhich() %>% sort %>% setdiff(c("labelid", "artistid", "genreid", "releaseid", "release_name_cleaned")) ## Breakdowns for display by idCol cuts_for_counts <- c(-Inf, -1, 0, 1, 2, 3, 4, 5, 10, 50, 100, Inf) labels_for_counts <- c("< -1", -1, 0, 1, 2, 3, 4, "5-9", "10-49", "50-99", "100+") cls(22) for (idCol in possibles) { catn("~~~~~~~~~~~~ ", idCol, " ~~~~~~~~~~~~~~~~") print(ret <- DT.releases[, .N, keyby=idCol][N > 1][order(N, decreasing=TRUE)]) catn() nm=sprintf("%ss", topropper_keywords(removeText("id$", idCol))) ret[, 'Number Of' := cut_integers(N, cuts=cuts_for_counts)] print(ret[, setNames(list(.N), nm), keyby='Number Of']) catn("\n") } } # ?? DT.releases[catalogid == "2228659"] # ?? # ?? .myf(punc) # ?? # ?? # ?? SELECT 1, sum(rowcount) as FROM ( # ?? SELECT vendor_release_identifier, count(*) as rowcount # ?? FROM PRODUCTION.DIM_RELEASE # ?? WHERE NOT (vendor_release_identifier = 'NULL' OR vendor_release_identifier is NULL) # ?? GROUP BY 1 # ?? ) # ?? WHERE rowcount > 2 # ?? # ?? { # ?? " # ?? " %>% # ?? sfQry() # ?? } # ??