data-raw/clickhouse/compare_postgres_clickhouse.R

In hugheylab/pmparser: Create and Maintain a Relational Database of Data from PubMed/MEDLINE

library('pmparser')
library('data.table')
tables = c(names(pmparser:::getParsingTables('')), 'citation', 'citation_version')
conP =  pmparser:::connect('postgres', 'pmdbclick')
conC =  pmparser:::connect('clickhouse', 'pmdbclick')
for (table in tables) {
  q = glue('select * from {table}')
  dtP = as.data.table(DBI::dbReadTable(conP, table))
  for (colname in colnames(dtP)) {
    if (inherits(dtP[[colname]], 'Date')) {
      dtP[[colname]] = NULL
    }
  }
  data.table::setorder(dtP)

  dtC = as.data.table(DBI::dbReadTable(conC, table))
  for (colname in colnames(dtC)) {
    # if (inherits(dtC[[colname]], 'Date') || (colname == 'version' && table != 'pmid_status')) {
    if (inherits(dtC[[colname]], 'Date')) {
      dtC[[colname]] = NULL
    }
  }
  data.table::setorder(dtC)

  allEq = all.equal(dtP, dtC, check.attributes = FALSE)

  if (isTRUE(allEq)) {
    warning(glue('{table} is equal.\n'))
  } else{
    warning(glue('{table} is not equal: \n{allEq}\n'))
  }
}
warnings()
pmparser:::disconnect(conP)
pmparser:::disconnect(conC)