### EVENTUALLY, THIS SHOULD PULL THE DATA FRESH FROM REPORTS AR ## For now, just use the flat files that dave previously pulled ## screen -xRR carveouts if (FALSE) source("~/git/orch/src/DeNormalizing/Additional Tables/carve_outs.r") RUN_QRY <- TRUE LOOKML <- TRUE setScience(proj="DeNormalizing", subProj="AdditionalTables") setGitBranchToSystem(); .g() date_last_modified <- as.Date("2015-10-28") wh <- "cron_jobs_small" wh <- "SCIENCE" dbname <- "prod" schema <- "carveouts" # tbl.label <- "label_level" # tbl.release <- "release_level" ## About 8 minutes if (TRUE) { ## Pull out all the relevant SQL files from the ingest folder f.in.sql <- extractIngestFiles(subFolder="carveouts", ext="sql") ## ignore the "single qry" files f.in.sql %<>% {.[!grepl("\\[Single Qry with Joins\\]", .)]} setDBall("mysql") ll_DTs <- emptylist(f.in.sql) for (nm in names(f.in.sql)) { catn("Running query for '", nm, "'", sep="") f.in <- f.in.sql[nm] tmp_DT.carveouts <- runQry(sqlFileToQry(f.in, clear=TRUE)) ## Check for duplicates and clear if (anyDuplicated(tmp_DT.carveouts, by=NULL)) { warning ("There are duplicates for: ", nm) print(tmp_DT.carveouts[duplicated(tmp_DT.carveouts)]) tmp_DT.carveouts <- unique(tmp_DT.carveouts, by=NULL) } ll_DTs[[nm]] <- copy(tmp_DT.carveouts) rm(tmp_DT.carveouts) gc() } sfWarehouseOn(wh=wh, size="L") setSnowflake(wh=wh, dbname=dbname) for (nm in names(f.in.sql)) { tbl <- removeText("\\.sql$", nm) %>% gsub("\\s+(\\-\\s*)?", "_", .) %>% tolower %>% gsub("level_", "level__", .) ingestIntoSQL(DT=ll_DTs[[nm]], tbl=tbl, schema=schema, dbname=dbname, wh=wh) } } # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : f.in # old Dec 2015 # : .............. LEFT OFF HERE ........ was going to iterate over each file. Have to iterate over six files # old Dec 2015 # : # s.t({ # old Dec 2015 # : tmp_DT.carveouts <- runQry(sqlFileToQry(f.in, clear=TRUE)) # old Dec 2015 # : # }, title="query carveouts, label level") # old Dec 2015 # : anyDuplicated(tmp_DT.carveouts[labelid != 6481], by=NULL) # old Dec 2015 # : DT.carveouts <- unique(tmp_DT.carveouts, by=NULL) # old Dec 2015 # : if (nrow(DT.carveouts) != nrow(tmp_DT.carveouts)) # old Dec 2015 # : warning("There were duplicate rows in tmp_DT.carveouts --- figure out why") # old Dec 2015 # : # old Dec 2015 # : } # old Dec 2015 # : # old Dec 2015 # : setSnowflake(wh=wh, dbname=dbname, start=TRUE) # old Dec 2015 # : ingestIntoSQL(DT=DT.carveouts, schema=schema, tbl=tbl.label, dbname=dbname, wh=wh) # old Dec 2015 # : tbl.label # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : f.ins <- extractIngestFiles("carve") # old Dec 2015 # : # old Dec 2015 # : if (length(f.ins) > 2) # old Dec 2015 # : stop ("Rick: You forgot to fix the script to check for largest file by date -- is that a function") # old Dec 2015 # : # old Dec 2015 # : if (RUN_QRY) DT.stores <- sfQry("SELECT storeid, store_name from bi.store_view order by 1") # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : f.label_levels <- extract("label", f.ins) # old Dec 2015 # : tmp_DT.label_level_carveouts <- fread(f.label_levels, na.strings=c("(null)", "null"), colClasses=c("integer", "integer", "character")) # old Dec 2015 # : setnames(tmp_DT.label_level_carveouts, "vendor_id", "labelid") # old Dec 2015 # : setnames(tmp_DT.label_level_carveouts, "customer_master_master_id", "storeid") # old Dec 2015 # : tmp_DT.label_level_carveouts[, storeid := as.integer(storeid)] # old Dec 2015 # : labs_odd <- tmp_DT.label_level_carveouts[is.na(storeid) & is.na(country_code), labelid] # old Dec 2015 # : addColsFrom_(tmp_DT.label_level_carveouts, DT.stores, joinCols="storeid") # old Dec 2015 # : setnames(tmp_DT.label_level_carveouts, "storeid", "label_level_carved_out_storeid") # old Dec 2015 # : setnames(tmp_DT.label_level_carveouts, "store_name", "label_level_carved_out_store_name") # old Dec 2015 # : setnames(tmp_DT.label_level_carveouts, "country_code", "label_level_carved_out_country_code") # old Dec 2015 # : # old Dec 2015 # : f.release_levels <- extract("release", f.ins) # old Dec 2015 # : tmp_DT.release_level_carveouts <- fread(f.release_levels, na.strings=c("(null)"), colClasses=c("integer", "integer", "character")) # old Dec 2015 # : setnames(tmp_DT.release_level_carveouts, "customer_master_master_id", "storeid") # old Dec 2015 # : setnames(tmp_DT.release_level_carveouts, "upc", "releaseid") # old Dec 2015 # : tmp_DT.release_level_carveouts[, storeid := as.integer(storeid)] # old Dec 2015 # : releaseids_odd <- tmp_DT.release_level_carveouts[is.na(storeid) & is.na(country_code), releaseid] # old Dec 2015 # : addColsFrom_(tmp_DT.release_level_carveouts, DT.stores, joinCols="storeid") # old Dec 2015 # : setnames(tmp_DT.release_level_carveouts, "storeid", "release_level_carved_out_storeid") # old Dec 2015 # : setnames(tmp_DT.release_level_carveouts, "store_name", "release_level_carved_out_store_name") # old Dec 2015 # : setnames(tmp_DT.release_level_carveouts, "country_code", "release_level_carved_out_country_code") # old Dec 2015 # : # old Dec 2015 # : if (length(releaseids_odd)) { # old Dec 2015 # : warning("There are ", length(releaseids_odd), " releaseids with neither store nor country carveout, why are they on the list?") # old Dec 2015 # : } # old Dec 2015 # : if (length(labs_odd)) { # old Dec 2015 # : warning("There are ", length(labs_odd), " labels with neither store nor country carveout, why are they on the list?") # old Dec 2015 # : } # old Dec 2015 # : # old Dec 2015 # : sfCreateSchema("carveouts", dbname=dbname, wh=wh) # old Dec 2015 # : ingestIntoSQL(DT=tmp_DT.label_level_carveouts, schema="carveouts", tbl="label_level", dbname=dbname, wh=wh) # old Dec 2015 # : ingestIntoSQL(DT=tmp_DT.release_level_carveouts, schema="carveouts", tbl="release_level", dbname=dbname, wh=wh) # old Dec 2015 # : # old Dec 2015 # : nrow(tmp_DT.label_level_carveouts) # old Dec 2015 # : as.character(nrow(tmp_DT.release_level_carveouts)) # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : ## Pull all labelids for each releaseid and merge to tmp_DT.release_level_carveouts # old Dec 2015 # : if (RUN_QRY) DT.release_to_label <- sfQry("SELECT releaseid as releaseid, labelid from production.dim_release order by releaseid") # old Dec 2015 # : addColsFrom_(tmp_DT.release_level_carveouts, DT.release_to_label, joinCols="releaseid") # old Dec 2015 # : # old Dec 2015 # : ## Pull all label ids # old Dec 2015 # : if (RUN_QRY) DT.all_labels <- sfQry("SELECT labelid from production.dim_label order by labelid") # old Dec 2015 # : # old Dec 2015 # : ## merge with # old Dec 2015 # : setkeyIfNot(DT.all_labels, labelid, verbose=FALSE, organize=TRUE) # old Dec 2015 # : setkeyIfNot(tmp_DT.label_level_carveouts, labelid, verbose=FALSE, organize=TRUE) # old Dec 2015 # : setkeyIfNot(tmp_DT.release_level_carveouts, labelid, verbose=FALSE, organize=TRUE) # old Dec 2015 # : # old Dec 2015 # : # tmp_DT.label_level_carveouts <- tmp_DT.label_level_carveouts[DT.all_labels, allow=TRUE] # old Dec 2015 # : # tmp_DT.label_level_carveouts[tmp_DT.release_level_carveouts, # old Dec 2015 # : # `:=`(label_has_some_releases_with_store_carveouts = any(!is.na(release_level_carved_out_storeid)) # old Dec 2015 # : # , label_has_some_releases_with_country_carveouts = any(!is.na(release_level_carved_out_country_code)) # old Dec 2015 # : # ), allow=TRUE] # old Dec 2015 # : # # old Dec 2015 # : # tmp_DT.label_level_carveouts[, .N, by=list(labelid, label_has_some_releases_with_country_carveouts, label_has_some_releases_with_store_carveouts)] # old Dec 2015 # : # DT.label_and_release_level_carveouts[!is.na(labelid), .N, by=list(labelid, label_has_some_releases_with_country_carveouts, label_has_some_releases_with_store_carveouts)] # old Dec 2015 # : # # old Dec 2015 # : # labs_lr <- DT.label_and_release_level_carveouts[(label_has_some_releases_with_store_carveouts), .N, by=labelid][order(N, decreasing=TRUE)]$labelid # old Dec 2015 # : # labs_l <- tmp_DT.label_level_carveouts[(label_has_some_releases_with_store_carveouts), .N, by=labelid][order(N, decreasing=TRUE)]$labelid # old Dec 2015 # : # # old Dec 2015 # : # allSetDiff(labs_lr, labs_l) # old Dec 2015 # : # # old Dec 2015 # : # tmp_DT.label_level_carveouts[labelid == 7123] # old Dec 2015 # : # DT.label_and_release_level_carveouts[labelid == 7123] # old Dec 2015 # : # unique(DT.label_and_release_level_carveouts[labelid == 7123], by=c("labelid", "releaseid", "release_level_carved_out_storeid")) # old Dec 2015 # : # unique(tmp_DT.release_level_carveouts[labelid == 7123], by=c("labelid", "releaseid", "release_level_carved_out_storeid")) # old Dec 2015 # : # unique(DT.label_and_release_level_carveouts[labelid == 7123], by="releaseid") # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : # ---------------------------------------------------------------------- # # old Dec 2015 # : # old Dec 2015 # : setkeyIfNot(DT.all_labels, labelid, verbose=FALSE, organize=TRUE) # old Dec 2015 # : setkeyIfNot(tmp_DT.label_level_carveouts, labelid, verbose=FALSE, organize=TRUE) # old Dec 2015 # : setkeyIfNot(tmp_DT.release_level_carveouts, labelid, verbose=FALSE, organize=TRUE) # old Dec 2015 # : # old Dec 2015 # : ## Merge the three tables # old Dec 2015 # : DT.label_and_release_level_carveouts <- { # old Dec 2015 # : DT.all_labels %>% # old Dec 2015 # : merge(tmp_DT.label_level_carveouts, by="labelid", all=TRUE, allow=TRUE) %>% # old Dec 2015 # : merge(tmp_DT.release_level_carveouts, by="labelid", all=TRUE, allow=TRUE) # old Dec 2015 # : } # old Dec 2015 # : # old Dec 2015 # : ## Add in label info, regarding whether it has releases with special carveouts # old Dec 2015 # : DT.label_and_release_level_carveouts[, label_has_some_releases_with_store_carveouts := any(!is.na(release_level_carved_out_storeid)), by=labelid] # old Dec 2015 # : DT.label_and_release_level_carveouts[, label_has_some_releases_with_country_carveouts := any(!is.na(release_level_carved_out_country_code)), by=labelid] # old Dec 2015 # : ## ------------ # old Dec 2015 # : DT.label_and_release_level_carveouts[, label_has_some_store_carveouts := any(!is.na(label_level_carved_out_storeid)), by=labelid] # old Dec 2015 # : DT.label_and_release_level_carveouts[, label_has_some_country_carveouts := any(!is.na(label_level_carved_out_country_code)), by=labelid] # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : ## ------------ # old Dec 2015 # : DT.label_and_release_level_carveouts[, release_has_some_store_carveouts := any(!is.na(release_level_carved_out_storeid)), by=releaseid] # old Dec 2015 # : DT.label_and_release_level_carveouts[, release_has_some_country_carveouts := any(!is.na(release_level_carved_out_country_code)), by=releaseid] # old Dec 2015 # : # old Dec 2015 # : ## In order for this to be used accurately, we would need to merge in ALL releases for each label (or at least for each label that has some carveout). Otherwise, positives will only show for those releases already on this list. # old Dec 2015 # : ## # old Dec 2015 # : # DT.label_and_release_level_carveouts[, release_has_some_label_level_store_carveouts := any(!is.na(label_level_carved_out_storeid)), by=releaseid] # old Dec 2015 # : # DT.label_and_release_level_carveouts[, release_has_some_label_level_country_carveouts := any(!is.na(label_level_carved_out_country_code)), by=releaseid] # old Dec 2015 # : # old Dec 2015 # : labCols <- extract("label", DT.label_and_release_level_carveouts) %>% {.[!grepl("^release_has", .)]} # old Dec 2015 # : relCols <- extract("release|releaseid", DT.label_and_release_level_carveouts) %>% {.[!grepl("^label_has", .)]} # old Dec 2015 # : # old Dec 2015 # : ## Different ways to confirm that all of the columns are included # old Dec 2015 # : stopifnot(!length(intersect(labCols, relCols))) # old Dec 2015 # : stopifnot(!length(setdiff(names(DT.label_and_release_level_carveouts), c(labCols, relCols)))) # old Dec 2015 # : stopifnot(identical(sort(names(DT.label_and_release_level_carveouts)), sort(unname(c(labCols, relCols))))) # old Dec 2015 # : # old Dec 2015 # : ## ISOLATE DT.label_level_carveouts # old Dec 2015 # : DT.label_level_carveouts <- DT.label_and_release_level_carveouts[!is.na(labelid), .N, keyby=labCols][, N := NULL] # old Dec 2015 # : ## Confirm that the "has_..." cols are unique by # old Dec 2015 # : has_cols <- extract("has", DT.label_level_carveouts) # old Dec 2015 # : stopifnot(DT.label_level_carveouts[, lapply(.SD, function(x) length(unique(x)) == 1), by=labelid, .SDcols=has_cols][, lapply(.SD, all), .SDcols=has_cols] %>% unlist) # old Dec 2015 # : # old Dec 2015 # : ## ISOLATE DT.release_level_carveouts # old Dec 2015 # : DT.release_level_carveouts <- DT.label_and_release_level_carveouts[!is.na(releaseid), .N, keyby=relCols][, N := NULL] # old Dec 2015 # : ## Confirm that the "has_..." cols are unique by # old Dec 2015 # : has_cols <- extract("has", DT.release_level_carveouts) # old Dec 2015 # : stopifnot(DT.release_level_carveouts[, lapply(.SD, function(x) length(unique(x)) == 1), by=releaseid, .SDcols=has_cols][, lapply(.SD, all), .SDcols=has_cols] %>% unlist) # old Dec 2015 # : # old Dec 2015 # : ## DROP ANY ROWS WHERE THERE ARE NO CARVE OUTS # old Dec 2015 # : ## This relates to the warning earlier, in that they should not be in this list to begin with # old Dec 2015 # : DT.release_level_carveouts <- DT.release_level_carveouts[(release_has_some_store_carveouts | release_has_some_country_carveouts)] # old Dec 2015 # : DT.label_level_carveouts <- DT.label_level_carveouts[(label_has_some_store_carveouts | label_has_some_country_carveouts) | (label_has_some_releases_with_store_carveouts | label_has_some_releases_with_country_carveouts)] # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : ingestIntoSQL(DT=DT.label_level_carveouts, tbl=tbl.label, schema=schema, dbname=dbname, wh=wh, add.ingestDate=TRUE, datetime_type='TIMESTAMP_TZ', drop=TRUE, snowflake=TRUE) # old Dec 2015 # : ingestIntoSQL(DT=DT.release_level_carveouts, tbl=tbl.release, schema=schema, dbname=dbname, wh=wh, add.ingestDate=TRUE, datetime_type='TIMESTAMP_TZ', drop=TRUE, snowflake=TRUE) # old Dec 2015 # : # old Dec 2015 # : sfShowTables(schema=schema, dbname=dbname) # old Dec 2015 # : # old Dec 2015 # : if (LOOKML) # old Dec 2015 # : { # old Dec 2015 # : cls(25) # old Dec 2015 # : sourceSupportFns(proj="Looker") # old Dec 2015 # : create_lookml_model_from_schema(dbname=dbname, schema=schema) # old Dec 2015 # : } # old Dec 2015 # : # old Dec 2015 # : # old Dec 2015 # : if (FALSE) # old Dec 2015 # : { # old Dec 2015 # : setScienceIfNot(proj='misc') # old Dec 2015 # : sourceSupportFns(proj='Looker') # old Dec 2015 # : bringAndCopyLookerFiles(f='~rsaporta/git/orch/out/DeNormalizing/auto_generated_lookmls/carveouts') # old Dec 2015 # : # old Dec 2015 # : .o('/Applications/Tower 2.0.app') # old Dec 2015 # : } # old Dec 2015 # : # old Dec 2015 # :