PAUdm forced aperture coadd

Here are the instructions to update the paudm_forced_aperture_coadd table in CosmoHub.

We write this entry after an email from Santi Serrano (subject: "Fixed ZP and MEMBA Coadds now in PAUdm" date: 10/09/2020)

He announced the following different MEMBA productions in PAUdm:

• 955 - COSMOS
• 956 - W1
• 957 - W2
• 958 - W3
• 959 - W4

Being @ui.pic.es:

I create them in:

   /cephfs/pic.es/astro/scratch/jcarrete/sandbox/PAU/PAU_MEMBA

   psql -U postgres -W -h db.pau.pic.es dm -c "COPY (SELECT * FROM forced_aperture_coadd WHERE production_id = 955) TO stdout DELIMITER ',' CSV" | pv  > forced_aperture_coadd_production_id_955.csv
   psql -U postgres -W -h db.pau.pic.es dm -c "COPY (SELECT * FROM forced_aperture_coadd WHERE production_id = 956) TO stdout DELIMITER ',' CSV" | pv  > forced_aperture_coadd_production_id_956.csv
   psql -U postgres -W -h db.pau.pic.es dm -c "COPY (SELECT * FROM forced_aperture_coadd WHERE production_id = 957) TO stdout DELIMITER ',' CSV" | pv  > forced_aperture_coadd_production_id_957.csv
   psql -U postgres -W -h db.pau.pic.es dm -c "COPY (SELECT * FROM forced_aperture_coadd WHERE production_id = 958) TO stdout DELIMITER ',' CSV" | pv  > forced_aperture_coadd_production_id_958.csv
   psql -U postgres -W -h db.pau.pic.es dm -c "COPY (SELECT * FROM forced_aperture_coadd WHERE production_id = 959) TO stdout DELIMITER ',' CSV" | pv  > forced_aperture_coadd_production_id_959.csv

Then, having the kerberos certificate of hive from beeline. CREATE PARTITIONS:

   ALTER TABLE jcarrete.paudm_forced_aperture_coadd_csv ADD IF NOT EXISTS PARTITION(production_id=955);
   ALTER TABLE jcarrete.paudm_forced_aperture_coadd_csv ADD IF NOT EXISTS PARTITION(production_id=956);
   ALTER TABLE jcarrete.paudm_forced_aperture_coadd_csv ADD IF NOT EXISTS PARTITION(production_id=957);
   ALTER TABLE jcarrete.paudm_forced_aperture_coadd_csv ADD IF NOT EXISTS PARTITION(production_id=958);
   ALTER TABLE jcarrete.paudm_forced_aperture_coadd_csv ADD IF NOT EXISTS PARTITION(production_id=959);

Copy files into the corresponding hdfs directories: From data.astro:

   hdfs dfs -put /cephfs/pic.es/astro/scratch/jcarrete/sandbox/PAU/PAU_forced_aperture_coadd/forced_aperture_coadd_production_id_955.csv /user/jcarrete/data/paudm/forced_aperture_coadd/production_id\=955/forced_aperture_coadd_production_id_955.csv

   hdfs dfs -put /cephfs/pic.es/astro/scratch/jcarrete/sandbox/PAU/PAU_forced_aperture_coadd/forced_aperture_coadd_production_id_956.csv /user/jcarrete/data/paudm/forced_aperture_coadd/production_id\=956/forced_aperture_coadd_production_id_956.csv

   hdfs dfs -put /cephfs/pic.es/astro/scratch/jcarrete/sandbox/PAU/PAU_forced_aperture_coadd/forced_aperture_coadd_production_id_957.csv /user/jcarrete/data/paudm/forced_aperture_coadd/production_id\=957/forced_aperture_coadd_production_id_957.csv

   hdfs dfs -put /cephfs/pic.es/astro/scratch/jcarrete/sandbox/PAU/PAU_forced_aperture_coadd/forced_aperture_coadd_production_id_958.csv /user/jcarrete/data/paudm/forced_aperture_coadd/production_id\=958/forced_aperture_coadd_production_id_958.csv

   hdfs dfs -put /cephfs/pic.es/astro/scratch/jcarrete/sandbox/PAU/PAU_forced_aperture_coadd/forced_aperture_coadd_production_id_959.csv /user/jcarrete/data/paudm/forced_aperture_coadd/production_id\=959/forced_aperture_coadd_production_id_959.csv

From beeline:

   INSERT OVERWRITE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION (production_id=955)
   SELECT `ref_id`, `band`, `flux`, `flux_error`, `chi2`, `n_coadd`, `run`
   FROM jcarrete.paudm_forced_aperture_coadd_csv
   WHERE ref_id IS NOT NULL 
   AND production_id = 955;

   INSERT OVERWRITE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION (production_id=956)
   SELECT `ref_id`, `band`, `flux`, `flux_error`, `chi2`, `n_coadd`, `run`
   FROM jcarrete.paudm_forced_aperture_coadd_csv
   WHERE ref_id IS NOT NULL 
   AND production_id = 956;

   INSERT OVERWRITE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION (production_id=957)
   SELECT `ref_id`, `band`, `flux`, `flux_error`, `chi2`, `n_coadd`, `run`
   FROM jcarrete.paudm_forced_aperture_coadd_csv
   WHERE ref_id IS NOT NULL 
   AND production_id = 957;

   INSERT OVERWRITE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION (production_id=958)
   SELECT `ref_id`, `band`, `flux`, `flux_error`, `chi2`, `n_coadd`, `run`
   FROM jcarrete.paudm_forced_aperture_coadd_csv
   WHERE ref_id IS NOT NULL 
   AND production_id = 958;

   INSERT OVERWRITE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION (production_id=959)
   SELECT `ref_id`, `band`, `flux`, `flux_error`, `chi2`, `n_coadd`, `run`
   FROM jcarrete.paudm_forced_aperture_coadd_csv
   WHERE ref_id IS NOT NULL 
   AND production_id = 959;

Estimating statistics for the different partitions:

   ANALYZE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION(production_id=955) COMPUTE STATISTICS FOR COLUMNS;
   ANALYZE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION(production_id=956) COMPUTE STATISTICS FOR COLUMNS;
   ANALYZE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION(production_id=957) COMPUTE STATISTICS FOR COLUMNS;
   ANALYZE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION(production_id=958) COMPUTE STATISTICS FOR COLUMNS;
   ANALYZE TABLE cosmohub.paudm_forced_aperture_coadd PARTITION(production_id=959) COMPUTE STATISTICS FOR COLUMNS;