Jcarrete: Created page with "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 Coa..."

2020-09-14T15:33:57Z

Created page with "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 Coa..."

New page

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;

PAUdm forced aperture coadd - Revision history

Jcarrete: Created page with "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 Coa..."