# table_row_counts.r

setScience(proj="DeNormalizing", subProj="rowcounts")

wh<-getWH_already_on(default="CRON_JOBS_SMALL")
dbname <- "prod"
setSnowflake(wh=wh, dbname=dbname)

## Grab the views
DT.views <- runQry("show views in prod.*")
DT.views[, kind := "view"]

DT.tbls <- runQry("show tables in prod.*")

## Combine 
DT.tbls <- rbind(DT.tbls, DT.views, use.names=TRUE, fill=TRUE)

## Add string for when last checked
DT.tbls[, meta_last_checked := now()]

dict.nms <- c(schema="schema_name", dbname="database_name", tbl="name", row_count="rows")
setnamesByDict(DT.tbls, dict.nms, warn_for_new_return=FALSE)


string_cols <- sapply(DT.tbls, is.character) %>% nwhich %>% setdiff("comment") %>% c("cluster_by") %>% unique

DT.tbls[, c(string_cols) := lapply(.SD, tolower), .SDcols=string_cols]
DT.tbls[, bytes_string := as.character(formatBytes(bytes))]

## for filtering
DT.tbls[, schema_tbl := paste(schema, tbl, sep=".")]

setcolorderpt(DT.tbls, startCols=c("schema_tbl", "dbname", "schema", "tbl", "kind", "cluster_by"), endCols=c("created_on", "meta_last_checked", "row_count", "bytes", "bytes_string"))

ingestIntoSQL(DT=DT.tbls, tbl="table_row_counts", schema="bi", dbname=dbname, datetime_type='TIMESTAMP_NTZ', drop=TRUE)