# CarveOuts2016 - dump AR tables to Snowflake.r

##  Joining the tables in MySQL is too costly.  
##  Moving the tables to Snowflake to the join in there


setScience("CarveOuts", subProj="AR_Tables")

wh <- "Science"
dbname <- "prod"
schema <- "CO_StagingTables_from_AR"

## RUN ON SNOWFLAKE
setSnowflake(wh=wh, dbname=dbname)
sfCreateSchema(schema, dbname, comment="Select-column dump from AR. The CO Tables cannot be built directly from AR because too compute-intesnive")

## RUN ON MYSQL -- AR
 tbls <- data.table(structure(list(schema = c("art_relations", "art_relations", "art_relations","art_relations", "art_relations", "art_relations", "art_relations","art_relations", "art_relations", "art_relations", "art_relations","art_relations", "art_relations", "art_relations", "art_relations","art_relations", "art_relations", "art_relations", "art_relations","art_relations", "art_relations", "art_relations", "art_relations","art_relations", "art_relations", "art_relations", "art_relations","art_relations", "art_relations"), table_name = c("releases","releases", "releases", "releases", "subaccount", "subaccount","artist_info", "artist_info", "distribution_type", "customer_master_master","release_dms_master_restriction", "release_dms_master_restriction","release_dms_master_restriction", "subaccount_dms_master_restriction","subaccount_dms_master_restriction", "vw_active_vendor_contract","vw_active_vendor_contract", "vendor", "vendor_contract", "vendor_contract","vendor_contract", "vendor_dms_master_restriction", "vendor_dms_master_restriction","vendor_dms_master_restriction", "country", "release_territory_restriction","release_territory_restriction", "subaccount_territory_restriction","subaccount_territory_restriction"), alias = c("r", "r", "r","r", "s", "s", "ai", "ai", "dt", "cmm", "rdmr", "rdmr", "rdmr","sdmr", "sdmr", "vw", "vw", "v", "vc", "vc", "vc", "vdmr", "vdmr","vdmr", "c", "rtr", "rtr", "str", "str"), column_name = c("upc","release_id", "artist_id", "subaccount_id", "subaccount_id","vendor_id", "artist_id", "vendor_id", "id", "customer_master_master_id","distribution_type_id", "upc", "customer_master_master_id", "subaccount_id","customer_master_master_id", "vendor_id", "vendor_contract_id","vendor_id", "vendor_id", "id", "territory_carve_out", "distribution_type_id","vendor_contract_id", "customer_master_master_id", "id", "upc","country_id", "subaccount_id", "country_id"), primary_key = c(TRUE,FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, FALSE,FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE,FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE)), .Names = c("schema","table_name", "alias", "column_name", "primary_key"), class = c("data.table","data.frame"), row.names = c(NA, -29L)))

# for (tbl in unique(tbls$table_name)) {

setSnowflake(wh=wh, dbname=dbname)
tbls_already_done <- sfShowTables(dbname=dbname, schema=schema, tbl=tbl, justnames=TRUE) %>% tolower

## For your own viewing, these are the tables that remain to be done
catnn("These are the tables that remain to be migrated: ")
setdiff(unique(tbls$table_name), tbls_already_done)

for (tbl in rev(unique(tbls$table_name))) {

    if (tolower(tbl) %in% tolower(tbls_already_done))
      next

    gc()
    ## PULL THE DATA FROM MYSQL (reportsAR)
    setDBall("MySQL")
    DT <- tbls[tbl == table_name, makeQry(colsToPull=column_name, key=column_name[primary_key=='Y'], tbl=unique(table_name), limit=NULL)] %>% runQry

    gc()
    ## INSERT THE DATA INTO SNOWFLAKE
    setSnowflake(wh=wh, dbname=dbname)
    success <- try({
        ingestIntoSQL(DT=DT, tbl=tbl, schema=schema, dbname=dbname, datetime_type='TIMESTAMP_TZ', dont_take_copy_of_DT=TRUE, snowflake_inuse=TRUE)
    })
    if (isErr(success)) {
      gc()
      f.out <- writeDT(DT, base.file.name=tbl)
      gc()
      sfPopulateTable(tbl=tbl, schema=schema, dbname=dbname, file_name=f.out, format_name="RICKS_TSV_HEADER")
      rm(f.out)
    }

    rm(DT)
}


formnumb(round=0, sfShowTables(schema=schema, dbname=dbname, row=TRUE))[]



DT.release_dms_master_restriction <- runQry("SELECT min(restriction_id) as mn, max(restriction_id) as mx, count(*) as rows from release_dms_master_restriction")


```
  SHOW TABLES IN prod.CO_StagingTables_from_AR
 ---------------------------------------------------------------------------------------------------
 created_on                              name        rows     bytes
 2016-05-27                       ARTIST_INFO   1,241,039   5.28 MB
 2016-05-31                           COUNTRY         243   4.00 KB
 2016-05-27            CUSTOMER_MASTER_MASTER       1,174   4.00 KB
 2016-05-27                 DISTRIBUTION_TYPE          14   4.00 KB
 2016-05-27                          RELEASES   1,642,754  24.44 MB
 2016-05-31    RELEASE_DMS_MASTER_RESTRICTION 154,032,070 505.31 MB
 2016-05-31     RELEASE_TERRITORY_RESTRICTION  38,528,986 118.26 MB
 2016-05-27                        SUBACCOUNT      46,997 148.00 KB
 2016-05-31 SUBACCOUNT_DMS_MASTER_RESTRICTION      15,943  48.00 KB
 2016-05-31  SUBACCOUNT_TERRITORY_RESTRICTION       2,910   8.00 KB
 2016-05-31                            VENDOR      24,459  48.00 KB
 2016-05-31                   VENDOR_CONTRACT      42,916 240.00 KB
 2016-05-31     VENDOR_DMS_MASTER_RESTRICTION   3,738,234  12.53 MB
 2016-05-31         VW_ACTIVE_VENDOR_CONTRACT      11,836  48.00 KB
```