qry = """
SELECT COUNT(*) CNT , 'eloqua_contact' AS tbl
FROM reporting.eloqua_contact
UNION 
SELECT COUNT(*) CNT , 'eloqua_form' AS tbl
FROM reporting.eloqua_form
UNION 
SELECT COUNT(*) CNT , 'eloqua_form_submission' AS tbl
FROM reporting.eloqua_form_submission
UNION
SELECT COUNT(*) CNT , 'eloqua_system_preference_center' AS tbl
FROM reporting.eloqua_system_preference_center
UNION
SELECT COUNT(*) CNT , 'eloqua_system_sms_preference_center' AS tbl
FROM reporting.eloqua_system_sms_preference_center
UNION 
SELECT COUNT(*) CNT , 'eloqua_system_preference_center_toro_s_kids_club' AS tbl
FROM reporting.eloqua_system_preference_center_toro_s_kids_club
UNION
SELECT COUNT(*) CNT , 'eloqua_system_preference_center_historic' AS tbl
FROM reporting.eloqua_system_preference_center_historic
UNION
SELECT COUNT(*) CNT , 'eloqua_system_bi_prospect_scores' AS tbl
FROM reporting.eloqua_system_bi_prospect_scores

"""

# Get Pre Run Cnts
pre_run_df = spark.read.format("sqlserver")\
      .option("host", "sqls-houstontexans-db-01.database.windows.net")\
      .option("user", prodProperties['user']).option("password", prodProperties['password'])\
      .option("database","sqls-houstontexans")\
      .option("query", qry)\
      .option("fetchSize", 35000)\
      .load()
 
# write data from dbrix to azure
prod_dbrix_to_azure('eloqua.contact','reporting.eloqua_contact')
prod_dbrix_to_azure('eloqua.form','reporting.eloqua_form')
prod_dbrix_to_azure('eloqua.form_submission','reporting.eloqua_form_submission')
prod_dbrix_to_azure('eloqua.system_preference_center','reporting.eloqua_system_preference_center')
prod_dbrix_to_azure('eloqua.system_sms_preference_center','reporting.eloqua_system_sms_preference_center')
prod_dbrix_to_azure('eloqua.system_preference_center_toro_s_kids_club','reporting.eloqua_system_preference_center_toro_s_kids_club')
prod_dbrix_to_azure('eloqua.system_preference_center_historic','reporting.eloqua_system_preference_center_historic')
prod_dbrix_to_azure('eloqua.system_bi_prospect_scores','reporting.eloqua_system_bi_prospect_scores')

# Get Post Run Cnts
post_run_df = spark.read.format("sqlserver")\
      .option("host", "sqls-houstontexans-db-01.database.windows.net")\
      .option("user", prodProperties['user']).option("password", prodProperties['password'])\
      .option("database","sqls-houstontexans")\
      .option("query", qry)\
      .option("fetchSize", 35000)\
      .load()
      
# Compare Pre and Post Run Df 
pre_post_cnts_df = pre_run_df.join(post_run_df, on='tbl', how='inner').select(pre_run_df.tbl ,  pre_run_df.CNT.alias('pre_run_cnt'), post_run_df.CNT.alias('post_run_cnt'))
pre_post_cnts_df.display()