MIDES Wide Field v6.0

Input data

Materialize partitioned bata by redshift bins

CREATE TABLE apocino.des_sample(
    coadd_object_id bigint,
    sof_cm_mag_corrected_g double,
    sof_cm_mag_corrected_r double,
    sof_cm_mag_corrected_i double,
    sof_cm_mag_corrected_z double,
    sof_cm_mag_err_g double,
    sof_cm_mag_err_r double,
    sof_cm_mag_err_i double,
    sof_cm_mag_err_z double
)
PARTITIONED BY (
    z float
)
CLUSTERED BY (
    coadd_object_id
)
INTO 1 BUCKETS
STORED AS PARQUET
;

INSERT OVERWRITE TABLE apocino.des_sample PARTITION(z=0)
SELECT
    coadd_object_id,
    sof_cm_mag_corrected_g, sof_cm_mag_corrected_r, sof_cm_mag_corrected_i, sof_cm_mag_corrected_z,
    sof_cm_mag_err_g, sof_cm_mag_err_r, sof_cm_mag_err_i, sof_cm_mag_err_z
FROM cosmohub.des_y3_gold_v2_2_c
WHERE extended_class_sof = 3
AND ra BETWEEN 20 AND 40 AND dec BETWEEN -28 AND -13
AND sof_cm_mag_corrected_g - sof_cm_mag_corrected_r BETWEEN -1 AND 3
AND sof_cm_mag_corrected_r - sof_cm_mag_corrected_i BETWEEN -1 AND 2.5
AND sof_cm_mag_corrected_i - sof_cm_mag_corrected_z BETWEEN -1 AND 2
AND sof_cm_mag_corrected_g < 30
AND sof_cm_mag_corrected_r < 30
AND sof_cm_mag_corrected_i < 30
AND sof_cm_mag_corrected_z < 30
AND dnf_zmean_sof != -9999
AND dnf_zmc_sof >= 0 AND dnf_zmc_sof < 0.1
;

INSERT OVERWRITE TABLE apocino.des_sample PARTITION(z=1.40)
SELECT
    coadd_object_id,
    sof_cm_mag_corrected_g, sof_cm_mag_corrected_r, sof_cm_mag_corrected_i, sof_cm_mag_corrected_z,
    sof_cm_mag_err_g, sof_cm_mag_err_r, sof_cm_mag_err_i, sof_cm_mag_err_z
FROM cosmohub.des_y3_gold_v2_2_c
WHERE extended_class_sof = 3
AND ra BETWEEN 20 AND 40 AND dec BETWEEN -28 AND -13
AND sof_cm_mag_corrected_g - sof_cm_mag_corrected_r BETWEEN -1 AND 3
AND sof_cm_mag_corrected_r - sof_cm_mag_corrected_i BETWEEN -1 AND 2.5
AND sof_cm_mag_corrected_i - sof_cm_mag_corrected_z BETWEEN -1 AND 2
AND sof_cm_mag_corrected_g < 30
AND sof_cm_mag_corrected_r < 30
AND sof_cm_mag_corrected_i < 30
AND sof_cm_mag_corrected_z < 30
AND dnf_zmean_sof != -9999
AND dnf_zmc_sof >= 1.40 AND dnf_zmc_sof < 1.42
;

INSERT OVERWRITE TABLE apocino.des_sample PARTITION(z)
SELECT
    coadd_object_id,
    sof_cm_mag_corrected_g, sof_cm_mag_corrected_r, sof_cm_mag_corrected_i, sof_cm_mag_corrected_z,
    sof_cm_mag_err_g, sof_cm_mag_err_r, sof_cm_mag_err_i, sof_cm_mag_err_z, 
    ROUND(dnf_zmc_sof, 2)
FROM cosmohub.des_y3_gold_v2_2_c
WHERE extended_class_sof = 3
AND ra BETWEEN 20 AND 40 AND dec BETWEEN -28 AND -13
AND sof_cm_mag_corrected_g - sof_cm_mag_corrected_r BETWEEN -1 AND 3
AND sof_cm_mag_corrected_r - sof_cm_mag_corrected_i BETWEEN -1 AND 2.5
AND sof_cm_mag_corrected_i - sof_cm_mag_corrected_z BETWEEN -1 AND 2
AND sof_cm_mag_corrected_g < 30
AND sof_cm_mag_corrected_r < 30
AND sof_cm_mag_corrected_i < 30
AND sof_cm_mag_corrected_z < 30
AND dnf_zmean_sof != -9999
AND dnf_zmc_sof >= 0.1 AND dnf_zmc_sof < 1.40
;