Euclid mock production v1.7.17 correct
Jump to navigation
Jump to search
I estimate the correct morphological parameters for version 1.7.17, which contains several issues. The problems were reported in the Euclid redmine.
In order to correct them I use the notebook:
/nfs/pic.es/user/j/jcarrete/python_notebooks/Euclid_mock_pipeline_production-v.1.7.17_correct.ipynb
With the notebook I create several parquet files with the correct shape parameters.
Then I create an external parquet table in hive:
CREATE EXTERNAL TABLE `flagship_1_7_17_correct_pq`( `galaxy_id` bigint, `dominant_shape` bigint, `blanco_decam_i` double, `bulge_angle` double, `disk_angle` double, `median_major_axis` double, `scale_length` double, `bulge_fraction` double, `disk_length` double, `bulge_length` double, `ellipticity` double, `disk_axis_ratio` double, `bulge_axis_ratio` double, `halo_id` bigint) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 'hdfs://namenode01/user/jcarrete/data/euclid/flagship_1_7_17_correct/mock' ;
I create a table for CosmoHub, which is the JOIN between the 1.7.17_s version but replacing the shape parameters with the correct values of the table
CREATE TABLE `cosmohub.flagship_sc456_1_7_18_s`( `halo_id` bigint COMMENT 'unique halo id coming from the Flagship dark matter halo catalog', `galaxy_id` int COMMENT 'galaxy id, it is not a unique indentifier, the unique identifier is given by the halo_id together with the', `ra_gal` float COMMENT 'galaxy right ascension (degrees)', `dec_gal` float COMMENT 'galaxy declination (degrees)', `l_gal` float COMMENT 'galactic longitude (degrees)', `b_gal` float COMMENT 'galactic latitude (degrees)', `mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits', `ra_gal_mag` float COMMENT 'galaxy magnified right ascension (degree)', `dec_gal_mag` float COMMENT 'galaxy magnified declination (degree)', `kappa` float COMMENT 'convergence', `gamma1` float COMMENT 'shear', `gamma2` float COMMENT 'shear', `true_redshift_gal` float COMMENT 'true galaxy redshift', `observed_redshift_gal` float COMMENT 'galaxy observed redshift (including peculiar velocity)', `hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format', `abs_mag_r01_evolved` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h) including MICECATv2.0 magnitude evolution', `sdss_r01` float COMMENT 'observed magnitude in sdss r-band redshifted to z=0.1', `sed_cosmos` float COMMENT 'interpolated basic COSMOS SED [0,30]', `ext_curve_cosmos` float COMMENT 'COSMOS extinction curve [0-4]', `ebv_cosmos` float COMMENT 'interpolated extinction strength for COSMOS extinction curve', `log_ml_r01` float COMMENT 'logarithm of stellar_mass/luminosity ratio in the r01 SDSS band', `log_stellar_mass` float COMMENT 'logarithm of the stellar mass (Msolar/h)', `log_sfr` float COMMENT 'logarithm of star formation rate in (Msolar/h)/year', `logf_halpha_model1_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: includes extinction)', `logf_halpha_model3_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: includes extinction)', `logf_hbeta_model1_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: includes extinction)', `logf_o2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)', `logf_n2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)', `logf_o3_model1_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: includes extinction)', `logf_s2_model1_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: includes extinction)', `logf_hbeta_model3_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: includes extinction)', `logf_o2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)', `logf_n2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)', `logf_o3_model3_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: includes extinction)', `logf_s2_model3_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: includes extinction)', `blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)', `blanco_decam_r` float COMMENT 'observed flux in DES r-band (Euclid provided)', `blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)', `blanco_decam_z` float COMMENT 'observed flux in DES z-band (Euclid provided)', `cfht_megacam_r` float COMMENT 'observed flux in CFHT r-band (Euclid provided)', `cfht_megacam_u` float COMMENT 'observed flux in CFHT u-band (Euclid provided)', `euclid_nisp_h` float COMMENT 'observed flux in Euclid NISP-H band (Euclid provided)', `euclid_nisp_j` float COMMENT 'observed flux in Euclid NISP-J band (Euclid provided)', `euclid_nisp_y` float COMMENT 'observed flux in Euclid NISP-Y band (Euclid provided)', `euclid_vis` float COMMENT 'observed flux in Euclid VIS band (Euclid provided)', `gaia_bp` float COMMENT 'observed flux in Gaia-BP band (Euclid provided)', `gaia_g` float COMMENT 'observed flux in Gaia-G band (Euclid provided)', `gaia_rp` float COMMENT 'observed flux in Gaia-RP band (Euclid provided)', `jst_jpcam_g` float COMMENT 'observed flux in JST g-band (Euclid provided)', `kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)', `kids_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)', `kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)', `kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)', `lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)', `lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)', `lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)', `lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)', `lsst_z` float COMMENT 'observed flux in LSST z-band (Euclid provided)', `lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)', `pan_starrs_i` float COMMENT 'observed flux in Pan-STARRS i-band (Euclid provided)', `pan_starrs_z` float COMMENT 'observed flux in Pan-STARRS z-band (Euclid provided)', `subaru_hsc_z` float COMMENT 'observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)', `2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)', `2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)', `blanco_decam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES g-band (Euclid provided)', `blanco_decam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES r-band (Euclid provided)', `blanco_decam_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES i-band (Euclid provided)', `blanco_decam_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES z-band (Euclid provided)', `cfht_megacam_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT u-band (Euclid provided)', `cfht_megacam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT r-band (Euclid provided)', `euclid_nisp_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-H band (Euclid provided)', `euclid_nisp_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-J band (Euclid provided)', `euclid_nisp_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-Y band (Euclid provided)', `euclid_vis_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid VIS band (Euclid provided)', `gaia_bp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-BP band (Euclid provided)', `gaia_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-G band (Euclid provided)', `gaia_rp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-RP band (Euclid provided)', `jst_jpcam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in JST g-band (Euclid provided)', `kids_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS u-band (Euclid provided)', `kids_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS g-band (Euclid provided)', `kids_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS r-band (Euclid provided)', `kids_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS i-band (Euclid provided)', `lsst_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST u-band (Euclid provided)', `lsst_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST g-band (Euclid provided)', `lsst_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST r-band (Euclid provided)', `lsst_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST i-band (Euclid provided)', `lsst_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST z-band (Euclid provided)', `lsst_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST Y-band (Euclid provided)', `pan_starrs_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS i-band (Euclid provided)', `pan_starrs_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS z-band (Euclid provided)', `subaru_hsc_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `2mass_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS J-band (Euclid provided)', `2mass_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS H-band (Euclid provided)', `2mass_ks_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS Ks-band (Euclid provided)', `blanco_decam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES g-band (Euclid provided)', `blanco_decam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES r-band (Euclid provided)', `blanco_decam_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES i-band (Euclid provided)', `blanco_decam_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES z-band (Euclid provided)', `blanco_decam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES g-band (Euclid provided)', `blanco_decam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES r-band (Euclid provided)', `blanco_decam_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES i-band (Euclid provided)', `blanco_decam_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES z-band (Euclid provided)', `cfht_megacam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT r-band (Euclid provided)', `cfht_megacam_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT u-band (Euclid provided)', `cfht_megacam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT r-band (Euclid provided)', `cfht_megacam_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT u-band (Euclid provided)', `euclid_nisp_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-H band (Euclid provided)', `euclid_nisp_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-J band (Euclid provided)', `euclid_nisp_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-Y band (Euclid provided)', `euclid_vis_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid VIS band (Euclid provided)', `euclid_nisp_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-H band (Euclid provided)', `euclid_nisp_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-J band (Euclid provided)', `euclid_nisp_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-Y band (Euclid provided)', `euclid_vis_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid VIS band (Euclid provided)', `gaia_bp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-BP band (Euclid provided)', `gaia_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-G band (Euclid provided)', `gaia_rp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-RP band (Euclid provided)', `gaia_bp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-BP band (Euclid provided)', `gaia_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-G band (Euclid provided)', `gaia_rp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-RP band (Euclid provided)', `jst_jpcam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in JST g-band (Euclid provided)', `jst_jpcam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in JST g-band (Euclid provided)', `kids_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS u-band (Euclid provided)', `kids_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS g-band (Euclid provided)', `kids_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS r-band (Euclid provided)', `kids_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS i-band (Euclid provided)', `kids_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS u-band (Euclid provided)', `kids_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS g-band (Euclid provided)', `kids_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS r-band (Euclid provided)', `kids_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS i-band (Euclid provided)', `lsst_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST u-band (Euclid provided)', `lsst_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST g-band (Euclid provided)', `lsst_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST r-band (Euclid provided)', `lsst_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST i-band (Euclid provided)', `lsst_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST z-band (Euclid provided)', `lsst_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST Y-band (Euclid provided)', `lsst_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST u-band (Euclid provided)', `lsst_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST g-band (Euclid provided)', `lsst_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST r-band (Euclid provided)', `lsst_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST i-band (Euclid provided)', `lsst_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST z-band (Euclid provided)', `lsst_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST Y-band (Euclid provided)', `pan_starrs_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS i-band (Euclid provided)', `pan_starrs_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS z-band (Euclid provided)', `pan_starrs_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS i-band (Euclid provided)', `pan_starrs_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS z-band (Euclid provided)', `subaru_hsc_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `subaru_hsc_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `2mass_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS J-band (Euclid provided)', `2mass_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction 2MASS H-band (Euclid provided)', `2mass_ks_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS Ks-band (Euclid provided)', `2mass_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS J-band (Euclid provided)', `2mass_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction 2MASS H-band (Euclid provided)', `2mass_ks_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS Ks-band (Euclid provided)', `dominant_shape` int COMMENT 'flag to define whether the galaxy is BULGE-dominated (0) or DISK-dominated (1)', `bulge_fraction` float COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)', `bulge_length` float COMMENT 'major-axis half-light radius in arcsec', `disk_angle` float COMMENT 'position of the disk rotation axis (degrees) (assumption: bulge_angle = disk_angle). TU Galaxy stamps convention for OU-SIM: the standard is to set the position angle from North to East, with the major axis aligned in Declination', `disk_axis_ratio` float COMMENT 'disk projected axis ratio (b/a)', `disk_length` float COMMENT 'disk_length is equal 0 for bulge-dominated galaxies. For disk-dominated galaxies, the disk_length is the major-axis exponential scalelength in arcsec', `halo_lm` float COMMENT 'log10 of the FoF halo mass in Msum/h. This halo mass is computed as the particule mass multiplied by the number of particles of the FoF halo. At low masses it is interpolated to obtain a smooth distribution of halo masses', `halo_n_sats` int COMMENT 'number of satellite galaxies from HOD') CLUSTERED BY ( halo_id, galaxy_id) INTO 4096 BUCKETS ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' ;
This is the INSERT OVERWRITE commmand.
It was necessary to modify some memory parameter in the configuration to make it work:
set hive.tez.container.size=16384;
INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_18_s SELECT fs17.halo_id, fs17.galaxy_id, fs17.ra_gal, fs17.dec_gal, fs17.l_gal, fs17.b_gal, fs17.mw_extinction, fs17.ra_gal_mag, fs17.dec_gal_mag, fs17.kappa, fs17.gamma1, fs17.gamma2, fs17.true_redshift_gal, fs17.observed_redshift_gal, fs17.hpix_29_nest, fs17.abs_mag_r01_evolved, fs17.sdss_r01, fs17.sed_cosmos, fs17.ext_curve_cosmos, fs17.ebv_cosmos, fs17.log_ml_r01, fs17.log_stellar_mass, fs17.log_sfr, fs17.logf_halpha_model1_ext, fs17.logf_halpha_model3_ext, fs17.logf_hbeta_model1_ext, fs17.logf_o2_model1_ext, fs17.logf_n2_model1_ext, fs17.logf_o3_model1_ext, fs17.logf_s2_model1_ext, fs17.logf_hbeta_model3_ext, fs17.logf_o2_model3_ext, fs17.logf_n2_model3_ext, fs17.logf_o3_model3_ext, fs17.logf_s2_model3_ext, fs17.blanco_decam_g, fs17.blanco_decam_r, fs17.blanco_decam_i, fs17.blanco_decam_z, fs17.cfht_megacam_r, fs17.cfht_megacam_u, fs17.euclid_nisp_h, fs17.euclid_nisp_j, fs17.euclid_nisp_y, fs17.euclid_vis, fs17.gaia_bp, fs17.gaia_g, fs17.gaia_rp, fs17.jst_jpcam_g, fs17.kids_u, fs17.kids_g, fs17.kids_r, fs17.kids_i, fs17.lsst_u, fs17.lsst_g, fs17.lsst_r, fs17.lsst_i, fs17.lsst_z, fs17.lsst_y, fs17.pan_starrs_i, fs17.pan_starrs_z, fs17.subaru_hsc_z, fs17.2mass_j, fs17.2mass_h, fs17.2mass_ks, fs17.blanco_decam_g_odonnell_ext, fs17.blanco_decam_r_odonnell_ext, fs17.blanco_decam_i_odonnell_ext, fs17.blanco_decam_z_odonnell_ext, fs17.cfht_megacam_u_odonnell_ext, fs17.cfht_megacam_r_odonnell_ext, fs17.euclid_nisp_h_odonnell_ext, fs17.euclid_nisp_j_odonnell_ext, fs17.euclid_nisp_y_odonnell_ext, fs17.euclid_vis_odonnell_ext, fs17.gaia_bp_odonnell_ext, fs17.gaia_g_odonnell_ext, fs17.gaia_rp_odonnell_ext, fs17.jst_jpcam_g_odonnell_ext, fs17.kids_u_odonnell_ext, fs17.kids_g_odonnell_ext, fs17.kids_r_odonnell_ext, fs17.kids_i_odonnell_ext, fs17.lsst_u_odonnell_ext, fs17.lsst_g_odonnell_ext, fs17.lsst_r_odonnell_ext, fs17.lsst_i_odonnell_ext, fs17.lsst_z_odonnell_ext, fs17.lsst_y_odonnell_ext, fs17.pan_starrs_i_odonnell_ext, fs17.pan_starrs_z_odonnell_ext, fs17.subaru_hsc_z_odonnell_ext, fs17.2mass_j_odonnell_ext, fs17.2mass_h_odonnell_ext, fs17.2mass_ks_odonnell_ext, fs17.blanco_decam_g_el_model1_odonnell_ext, fs17.blanco_decam_r_el_model1_odonnell_ext, fs17.blanco_decam_i_el_model1_odonnell_ext, fs17.blanco_decam_z_el_model1_odonnell_ext, fs17.blanco_decam_g_el_model3_odonnell_ext, fs17.blanco_decam_r_el_model3_odonnell_ext, fs17.blanco_decam_i_el_model3_odonnell_ext, fs17.blanco_decam_z_el_model3_odonnell_ext, fs17.cfht_megacam_r_el_model1_odonnell_ext, fs17.cfht_megacam_u_el_model1_odonnell_ext, fs17.cfht_megacam_r_el_model3_odonnell_ext, fs17.cfht_megacam_u_el_model3_odonnell_ext, fs17.euclid_nisp_h_el_model1_odonnell_ext, fs17.euclid_nisp_j_el_model1_odonnell_ext, fs17.euclid_nisp_y_el_model1_odonnell_ext, fs17.euclid_vis_el_model1_odonnell_ext, fs17.euclid_nisp_h_el_model3_odonnell_ext, fs17.euclid_nisp_j_el_model3_odonnell_ext, fs17.euclid_nisp_y_el_model3_odonnell_ext, fs17.euclid_vis_el_model3_odonnell_ext, fs17.gaia_bp_el_model1_odonnell_ext, fs17.gaia_g_el_model1_odonnell_ext, fs17.gaia_rp_el_model1_odonnell_ext, fs17.gaia_bp_el_model3_odonnell_ext, fs17.gaia_g_el_model3_odonnell_ext, fs17.gaia_rp_el_model3_odonnell_ext, fs17.jst_jpcam_g_el_model1_odonnell_ext, fs17.jst_jpcam_g_el_model3_odonnell_ext, fs17.kids_u_el_model1_odonnell_ext, fs17.kids_g_el_model1_odonnell_ext, fs17.kids_r_el_model1_odonnell_ext, fs17.kids_i_el_model1_odonnell_ext, fs17.kids_u_el_model3_odonnell_ext, fs17.kids_g_el_model3_odonnell_ext, fs17.kids_r_el_model3_odonnell_ext, fs17.kids_i_el_model3_odonnell_ext, fs17.lsst_u_el_model1_odonnell_ext, fs17.lsst_g_el_model1_odonnell_ext, fs17.lsst_r_el_model1_odonnell_ext, fs17.lsst_i_el_model1_odonnell_ext, fs17.lsst_z_el_model1_odonnell_ext, fs17.lsst_y_el_model1_odonnell_ext, fs17.lsst_u_el_model3_odonnell_ext, fs17.lsst_g_el_model3_odonnell_ext, fs17.lsst_r_el_model3_odonnell_ext, fs17.lsst_i_el_model3_odonnell_ext, fs17.lsst_z_el_model3_odonnell_ext, fs17.lsst_y_el_model3_odonnell_ext, fs17.pan_starrs_i_el_model1_odonnell_ext, fs17.pan_starrs_z_el_model1_odonnell_ext, fs17.pan_starrs_i_el_model3_odonnell_ext, fs17.pan_starrs_z_el_model3_odonnell_ext, fs17.subaru_hsc_z_el_model1_odonnell_ext, fs17.subaru_hsc_z_el_model3_odonnell_ext, fs17.2mass_j_el_model1_odonnell_ext, fs17.2mass_h_el_model1_odonnell_ext, fs17.2mass_ks_el_model1_odonnell_ext, fs17.2mass_j_el_model3_odonnell_ext, fs17.2mass_h_el_model3_odonnell_ext, fs17.2mass_ks_el_model3_odonnell_ext, fs17.dominant_shape, shape.bulge_fraction, shape.bulge_length, shape.disk_angle, shape.disk_axis_ratio, shape.disk_length, fs17.halo_lm, fs17.halo_n_sats FROM cosmohub.flagship_sc456_1_7_17_s AS fs17 JOIN jcarrete.flagship_1_7_17_correct_pq as shape ON fs17.halo_id = shape.halo_id AND fs17.galaxy_id = shape.galaxy_id;
Then this is the table to create the FITS files:
ADD JAR hdfs:///apps/cosmohub/lib/recarray-serde-LATEST-with-dependencies.jar;
CREATE TABLE `cosmohub.flagship_sc456_1_7_18_jy_fits`( SOURCE_ID bigint, RA float, DEC float, RA_MAG float, DEC_MAG float, Z_OBS float, TU_MAG_R01_SDSS_ABS float, TU_MAG_R01_SDSS float, SED_TEMPLATE float, EXT_LAW smallint, EBV float, HALPHA_LOGFLAM_EXT float, HBETA_LOGFLAM_EXT float, O2_LOGFLAM_EXT float, O3_LOGFLAM_EXT float, N2_LOGFLAM_EXT float, S2_LOGFLAM_EXT float, BULGE_FRACTION float, BULGE_LENGTH float, DISK_LENGTH float, DISK_AXIS_RATIO float, DISK_ANGLE float, KAPPA float, GAMMA1 float, GAMMA2 float, AV float, TU_FNU_VIS float, TU_FNU_Y_NISP float, TU_FNU_J_NISP float, TU_FNU_H_NISP float, TU_FNU_G_DECAM float, TU_FNU_R_DECAM float, TU_FNU_I_DECAM float, TU_FNU_Z_DECAM float, TU_FNU_U_MEGACAM float, TU_FNU_R_MEGACAM float, TU_FNU_G_JPCAM float, TU_FNU_I_PANSTARRS float, TU_FNU_Z_PANSTARRS float, TU_FNU_Z_HSC float, TU_FNU_G_GAIA float, TU_FNU_BP_GAIA float, TU_FNU_RP_GAIA float, TU_FNU_U_LSST float, TU_FNU_G_LSST float, TU_FNU_R_LSST float, TU_FNU_I_LSST float, TU_FNU_Z_LSST float, TU_FNU_Y_LSST float, TU_FNU_U_KIDS float, TU_FNU_G_KIDS float, TU_FNU_R_KIDS float, TU_FNU_I_KIDS float, TU_FNU_J_2MASS float, TU_FNU_H_2MASS float, TU_FNU_KS_2MASS float ) PARTITIONED BY ( hpix_5_nest bigint ) ROW FORMAT SERDE 'es.pic.astro.hadoop.serde.RecArraySerDe' STORED AS INPUTFORMAT 'es.pic.astro.hadoop.io.BinaryOutputFormat' OUTPUTFORMAT 'es.pic.astro.hadoop.io.BinaryOutputFormat' ;
This is the INSERT OVERWRITE command.
Note that for a partiotioned table, the last field needs to be the partitioned field, which in this case is the hpix_5_nest:
INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_18_jy_fits PARTITION(hpix_5_nest)
SELECT
CAST( ((gal.halo_id * 10000) + gal.galaxy_id) AS bigint) AS SOURCE_ID,
CAST( gal.ra_gal AS float) AS RA,
CAST( gal.dec_gal AS float) AS DEC,
CAST( gal.ra_gal_mag AS float) AS RA_MAG,
CAST( gal.dec_gal_mag AS float) AS DEC_MAG,
CAST( gal.observed_redshift_gal AS float) AS Z_OBS,
CAST( gal.abs_mag_r01_evolved AS float) AS TU_MAG_R01_SDSS_ABS,
CAST( -2.5*log10(gal.sdss_r01) - 48.6 AS float) AS TU_MAG_R01_SDSS,
CAST( gal.sed_cosmos AS float) AS SED_TEMPLATE,
CAST( ROUND(gal.ext_curve_cosmos) AS smallint) AS EXT_LAW,
CAST( gal.ebv_cosmos AS float) AS EBV,
CAST( gal.logf_halpha_model3_ext AS float) AS HALPHA_LOGFLAM_EXT,
CAST( gal.logf_hbeta_model3_ext AS float) AS HBETA_LOGFLAM_EXT,
CAST( gal.logf_o2_model3_ext AS float) AS O2_LOGFLAM_EXT,
CAST( gal.logf_o3_model3_ext AS float) AS O3_LOGFLAM_EXT,
CAST( gal.logf_n2_model3_ext AS float) AS N2_LOGFLAM_EXT,
CAST( gal.logf_s2_model3_ext AS float) AS S2_LOGFLAM_EXT,
CAST( gal.bulge_fraction AS float) AS BULGE_FRACTION,
CAST( gal.bulge_length AS float) AS BULGE_LENGTH,
CAST( gal.disk_length AS float) AS DISK_LENGTH,
CAST( gal.disk_axis_ratio AS float) AS DISK_AXIS_RATIO,
CAST( gal.disk_angle AS float) AS DISK_ANGLE,
CAST( gal.kappa AS float) AS KAPPA,
CAST( gal.gamma1 AS float) AS GAMMA1,
CAST( gal.gamma2 AS float) AS GAMMA2,
CAST( gal.mw_extinction AS float) AS AV,
CAST( gal.euclid_vis_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_VIS,
CAST( gal.euclid_nisp_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_NISP,
CAST( gal.euclid_nisp_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_NISP,
CAST( gal.euclid_nisp_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_NISP,
CAST( gal.blanco_decam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_DECAM,
CAST( gal.blanco_decam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_DECAM,
CAST( gal.blanco_decam_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_DECAM,
CAST( gal.blanco_decam_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_DECAM,
CAST( gal.cfht_megacam_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_MEGACAM,
CAST( gal.cfht_megacam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_MEGACAM,
CAST( gal.jst_jpcam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_JPCAM,
CAST( gal.pan_starrs_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_PANSTARRS,
CAST( gal.pan_starrs_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_PANSTARRS,
CAST( gal.subaru_hsc_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_HSC,
CAST( gal.gaia_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_GAIA,
CAST( gal.gaia_bp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_BP_GAIA,
CAST( gal.gaia_rp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_RP_GAIA,
CAST( gal.lsst_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_LSST,
CAST( gal.lsst_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_LSST,
CAST( gal.lsst_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_LSST,
CAST( gal.lsst_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_LSST,
CAST( gal.lsst_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_LSST,
CAST( gal.lsst_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_LSST,
CAST( gal.kids_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_KIDS,
CAST( gal.kids_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_KIDS,
CAST( gal.kids_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_KIDS,
CAST( gal.kids_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_KIDS,
CAST( gal.2mass_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_2MASS,
CAST( gal.2mass_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_2MASS,
CAST( gal.2mass_ks_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_KS_2MASS,
CAST( SHIFTRIGHT(gal.hpix_29_nest, (29-5)*2) AS bigint) AS hpix_5_nest
FROM cosmohub.flagship_sc456_1_7_18_s AS gal
WHERE (gal.logf_halpha_model3_ext > -16 OR -2.5*log10(gal.euclid_nisp_h) - 48.6 < 26)
AND gal.disk_axis_ratio > 0.10865
;
Finally it is necessary to add the header for the FITS files and store them in the corresponding pnfs path.
The notebook is called: split in healpix pixels.ipynb, where it is necessary to config two paths:
path = '/apps/hive/warehouse/cosmohub.db/flagship_sc456_1_7_18_jy_fits/' pnfs_path = '/pnfs/pic.es/data/astro/euclid/disk/shared/SPV/GALAXIES/v31/
---
This is an example of the JOIN but filtering one of the healpix_id. This small example worked because less memory was used.
SELECT
CAST( ((gal.halo_id * 10000) + gal.galaxy_id) AS bigint) AS SOURCE_ID,
CAST( gal.ra_gal AS float) AS RA,
CAST( gal.dec_gal AS float) AS DEC,
CAST( gal.ra_gal_mag AS float) AS RA_MAG,
CAST( gal.dec_gal_mag AS float) AS DEC_MAG,
CAST( gal.observed_redshift_gal AS float) AS Z_OBS,
CAST( gal.abs_mag_r01_evolved AS float) AS TU_MAG_R01_SDSS_ABS,
CAST( -2.5*log10(gal.sdss_r01) - 48.6 AS float) AS TU_MAG_R01_SDSS,
CAST( gal.sed_cosmos AS float) AS SED_TEMPLATE,
CAST( ROUND(gal.ext_curve_cosmos) AS smallint) AS EXT_LAW,
CAST( gal.ebv_cosmos AS float) AS EBV,
CAST( gal.logf_halpha_model3_ext AS float) AS HALPHA_LOGFLAM_EXT,
CAST( gal.logf_hbeta_model3_ext AS float) AS HBETA_LOGFLAM_EXT,
CAST( gal.logf_o2_model3_ext AS float) AS O2_LOGFLAM_EXT,
CAST( gal.logf_o3_model3_ext AS float) AS O3_LOGFLAM_EXT,
CAST( gal.logf_n2_model3_ext AS float) AS N2_LOGFLAM_EXT,
CAST( gal.logf_s2_model3_ext AS float) AS S2_LOGFLAM_EXT,
CAST( shape.bulge_fraction AS float) AS BULGE_FRACTION,
CAST( shape.bulge_length AS float) AS BULGE_LENGTH,
CAST( shape.disk_length AS float) AS DISK_LENGTH,
CAST( shape.disk_axis_ratio AS float) AS DISK_AXIS_RATIO,
CAST( shape.disk_angle AS float) AS DISK_ANGLE,
CAST( gal.kappa AS float) AS KAPPA,
CAST( gal.gamma1 AS float) AS GAMMA1,
CAST( gal.gamma2 AS float) AS GAMMA2,
CAST( gal.mw_extinction AS float) AS AV,
CAST( gal.euclid_vis_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_VIS,
CAST( gal.euclid_nisp_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_NISP,
CAST( gal.euclid_nisp_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_NISP,
CAST( gal.euclid_nisp_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_NISP,
CAST( gal.blanco_decam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_DECAM,
CAST( gal.blanco_decam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_DECAM,
CAST( gal.blanco_decam_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_DECAM,
CAST( gal.blanco_decam_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_DECAM,
CAST( gal.cfht_megacam_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_MEGACAM,
CAST( gal.cfht_megacam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_MEGACAM,
CAST( gal.jst_jpcam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_JPCAM,
CAST( gal.pan_starrs_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_PANSTARRS,
CAST( gal.pan_starrs_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_PANSTARRS,
CAST( gal.subaru_hsc_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_HSC,
CAST( gal.gaia_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_GAIA,
CAST( gal.gaia_bp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_BP_GAIA,
CAST( gal.gaia_rp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_RP_GAIA,
CAST( gal.lsst_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_LSST,
CAST( gal.lsst_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_LSST,
CAST( gal.lsst_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_LSST,
CAST( gal.lsst_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_LSST,
CAST( gal.lsst_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_LSST,
CAST( gal.lsst_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_LSST,
CAST( gal.kids_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_KIDS,
CAST( gal.kids_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_KIDS,
CAST( gal.kids_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_KIDS,
CAST( gal.kids_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_KIDS,
CAST( gal.2mass_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_2MASS,
CAST( gal.2mass_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_2MASS,
CAST( gal.2mass_ks_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_KS_2MASS
FROM cosmohub.flagship_sc456_1_7_17_s AS gal
JOIN jcarrete.flagship_1_7_17_correct_pq as shape
ON gal.halo_id = shape.halo_id
AND gal.galaxy_id = shape.galaxy_id
WHERE (gal.logf_halpha_model3_ext > -16 OR gal.euclid_nisp_h < 26)
AND shape.disk_axis_ratio > 0.10865
AND SHIFTRIGHT(gal.hpix_29_nest, (29-5)*2) = 9129