Euclid mock production v1.9.3
Jump to navigation
Jump to search
Catalog is created using the new platform through jupyter lab and Spark
It is necessary to use kerberos certificates to run the jupyter lab with Spark
There is a google doc with some documentation about environmental variables and how to run the jupyter from data.astro
Also, some improvements in the tracking has been donde: we store in git the notebooks we use and we tag them with the catalog version.
Once the catalog is created in hdfs in parquet format, we create the external table using the script created for it.
In this particular version, we take the 1.9.2 catalog, that already contains the lensing properties and we only estimate again the shape parameters. There was a bug in one of the parameters (bulge_fraction). We use the shape_pipeline.ipynb notebook.
The external table:
CREATE EXTERNAL TABLE `flagship_mock_1_9_3_p`( `kind` bigint, `luminosity_r01` double, `halo_x` double, `halo_y` double, `halo_z` double, `halo_vx` double, `halo_vy` double, `halo_vz` double, `halo_r` double, `true_redshift_halo` double, `halo_lm` double, `halo_n_sats` bigint, `n_gals` bigint, `abs_mag_r01` double, `abs_mag_r01_evolved` double, `luminosity_r01_evolved` double, `gr_restframe` double, `color_kind` bigint, `x_gal` double, `y_gal` double, `z_gal` double, `r_gal` double, `true_redshift_gal` double, `ra_gal` double, `dec_gal` double, `ra_mag_gal` double, `dec_mag_gal` double, `kappa` double, `gamma1` double, `gamma2` double, `hpix_29_nest` bigint, `hpix_13_nest` bigint, `vx_gal` double, `vy_gal` double, `vz_gal` double, `vrad_gal` double, `delta_r` double, `observed_redshift_gal` double, `sed_ke` double, `gr_cosmos` double, `sed_cosmos` double, `ext_curve_cosmos` bigint, `ebv_cosmos` double, `is_within_cosmos` boolean, `cosmos_distance` double, `abs_mag_uv_dereddened` double, `log_ml_r01` double, `log_stellar_mass` double, `metallicity` double, `log_sfr` double, `logf_halpha_ext` double, `logf_halpha` double, `halpha_scatter` double, `loglum_halpha` double, `loglum_halpha_ext` double, `logf_dummy` double, `z_dummy` double, `logf_halpha_model1_ext` double, `logf_halpha_model1` double, `loglum_halpha_model1_ext` double, `loglum_halpha_model1` double, `logf_halpha_model3_ext` double, `logf_halpha_model3` double, `loglum_halpha_model3_ext` double, `loglum_halpha_model3` double, `logf_hbeta_model1_ext` double, `logf_hbeta_model1` double, `logf_o2_model1_ext` double, `logf_o2_model1` double, `logf_n2_model1_ext` double, `logf_n2_model1` double, `logf_o3_model1_ext` double, `logf_o3_model1` double, `logf_s2_model1_ext` double, `logf_s2_model1` double, `logf_hbeta_model3_ext` double, `logf_hbeta_model3` double, `logf_o2_model3_ext` double, `logf_o2_model3` double, `logf_n2_model3_ext` double, `logf_n2_model3` double, `logf_o3_model3_ext` double, `logf_o3_model3` double, `logf_s2_model3_ext` double, `logf_s2_model3` double, `blanco_decam_g` double, `blanco_decam_i` double, `blanco_decam_r` double, `blanco_decam_z` double, `gaia_bp` double, `gaia_g` double, `gaia_rp` double, `subaru_hsc_z` double, `jst_jpcam_g` double, `kids_g` double, `kids_i` double, `kids_r` double, `kids_u` double, `lsst_g` double, `lsst_i` double, `lsst_r` double, `lsst_u` double, `lsst_y` double, `lsst_z` double, `cfht_megacam_r` double, `cfht_megacam_u` double, `pan_starrs_i` double, `pan_starrs_z` double, `euclid_nisp_h` double, `euclid_nisp_j` double, `euclid_nisp_y` double, `euclid_vis` double, `2mass_h` double, `2mass_j` double, `2mass_ks` double, `sdss_r01` double, `l_gal` double, `b_gal` double, `mw_extinction` double, `2mass_h_odonnell_ext` double, `2mass_j_odonnell_ext` double, `2mass_ks_odonnell_ext` double, `blanco_decam_g_odonnell_ext` double, `blanco_decam_i_odonnell_ext` double, `blanco_decam_r_odonnell_ext` double, `blanco_decam_z_odonnell_ext` double, `cfht_megacam_r_odonnell_ext` double, `cfht_megacam_u_odonnell_ext` double, `euclid_nisp_h_odonnell_ext` double, `euclid_nisp_j_odonnell_ext` double, `euclid_nisp_y_odonnell_ext` double, `euclid_vis_odonnell_ext` double, `gaia_bp_odonnell_ext` double, `gaia_g_odonnell_ext` double, `gaia_rp_odonnell_ext` double, `jst_jpcam_g_odonnell_ext` double, `kids_g_odonnell_ext` double, `kids_i_odonnell_ext` double, `kids_r_odonnell_ext` double, `kids_u_odonnell_ext` double, `lsst_g_odonnell_ext` double, `lsst_i_odonnell_ext` double, `lsst_r_odonnell_ext` double, `lsst_u_odonnell_ext` double, `lsst_y_odonnell_ext` double, `lsst_z_odonnell_ext` double, `pan_starrs_i_odonnell_ext` double, `pan_starrs_z_odonnell_ext` double, `sdss_r01_odonnell_ext` double, `subaru_hsc_z_odonnell_ext` double, `blanco_decam_g_el_model1_odonnell_ext` double, `blanco_decam_g_el_model3_odonnell_ext` double, `blanco_decam_i_el_model1_odonnell_ext` double, `blanco_decam_i_el_model3_odonnell_ext` double, `blanco_decam_r_el_model1_odonnell_ext` double, `blanco_decam_r_el_model3_odonnell_ext` double, `blanco_decam_z_el_model1_odonnell_ext` double, `blanco_decam_z_el_model3_odonnell_ext` double, `gaia_bp_el_model1_odonnell_ext` double, `gaia_bp_el_model3_odonnell_ext` double, `gaia_g_el_model1_odonnell_ext` double, `gaia_g_el_model3_odonnell_ext` double, `gaia_rp_el_model1_odonnell_ext` double, `gaia_rp_el_model3_odonnell_ext` double, `subaru_hsc_z_el_model1_odonnell_ext` double, `subaru_hsc_z_el_model3_odonnell_ext` double, `jst_jpcam_g_el_model1_odonnell_ext` double, `jst_jpcam_g_el_model3_odonnell_ext` double, `kids_g_el_model1_odonnell_ext` double, `kids_g_el_model3_odonnell_ext` double, `kids_i_el_model1_odonnell_ext` double, `kids_i_el_model3_odonnell_ext` double, `kids_r_el_model1_odonnell_ext` double, `kids_r_el_model3_odonnell_ext` double, `kids_u_el_model1_odonnell_ext` double, `kids_u_el_model3_odonnell_ext` double, `lsst_g_el_model1_odonnell_ext` double, `lsst_g_el_model3_odonnell_ext` double, `lsst_i_el_model1_odonnell_ext` double, `lsst_i_el_model3_odonnell_ext` double, `lsst_r_el_model1_odonnell_ext` double, `lsst_r_el_model3_odonnell_ext` double, `lsst_u_el_model1_odonnell_ext` double, `lsst_u_el_model3_odonnell_ext` double, `lsst_y_el_model1_odonnell_ext` double, `lsst_y_el_model3_odonnell_ext` double, `lsst_z_el_model1_odonnell_ext` double, `lsst_z_el_model3_odonnell_ext` double, `cfht_megacam_r_el_model1_odonnell_ext` double, `cfht_megacam_r_el_model3_odonnell_ext` double, `cfht_megacam_u_el_model1_odonnell_ext` double, `cfht_megacam_u_el_model3_odonnell_ext` double, `pan_starrs_i_el_model1_odonnell_ext` double, `pan_starrs_i_el_model3_odonnell_ext` double, `pan_starrs_z_el_model1_odonnell_ext` double, `pan_starrs_z_el_model3_odonnell_ext` double, `euclid_nisp_h_el_model1_odonnell_ext` double, `euclid_nisp_h_el_model3_odonnell_ext` double, `euclid_nisp_j_el_model1_odonnell_ext` double, `euclid_nisp_j_el_model3_odonnell_ext` double, `euclid_nisp_y_el_model1_odonnell_ext` double, `euclid_nisp_y_el_model3_odonnell_ext` double, `euclid_vis_el_model1_odonnell_ext` double, `euclid_vis_el_model3_odonnell_ext` double, `2mass_h_el_model1_odonnell_ext` double, `2mass_h_el_model3_odonnell_ext` double, `2mass_j_el_model1_odonnell_ext` double, `2mass_j_el_model3_odonnell_ext` double, `2mass_ks_el_model1_odonnell_ext` double, `2mass_ks_el_model3_odonnell_ext` double, `sdss_r01_el_model1_odonnell_ext` double, `sdss_r01_el_model3_odonnell_ext` double, `random_index` double, `step` bigint, `dominant_shape` bigint, `bulge_angle` double, `disk_angle` double, `median_major_axis` double, `scale_length` double, `bulge_fraction` double, `disk_scalelength` double, `disk_nsersic` double, `disk_r50` double, `bulge_r50` double, `bulge_nsersic` double, `disk_ellipticity` double, `bulge_ellipticity` double, `disk_axis_ratio` double, `bulge_axis_ratio` double, `disk_inclination_angle` double, `bulge_inclination_angle` double, `halo_id` bigint, `galaxy_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://AntNest/user/jcarrete/data/euclid/flagship_mock_1_9_3' TBLPROPERTIES ( 'bucketing_version'='2', 'discover.partitions'='true', 'transient_lastDdlTime'='1595241495');
I make the effort to order the fields, to convert data types and add comments to all fields in the clustered table:
I create the ORC table:
CREATE TABLE `cosmohub.flagship_mock_1_9_3_s`( `halo_id` bigint COMMENT 'unique halo id coming from the Flagship dark matter halo catalog', `galaxy_id` int COMMENT 'combined with the unique_halo_id they uniquely identify each galaxy', `kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT', `random_index` float COMMENT 'random number [0 - 1) for subsampling', `ra_gal` float COMMENT 'galaxy right ascension (degrees)', `dec_gal` float COMMENT 'galaxy declination (degrees)', `ra_mag_gal` float COMMENT 'galaxy magnified right ascension (degree)', `dec_mag_gal` float COMMENT 'galaxy magnified declination (degree)', `kappa` float COMMENT 'convergence', `gamma1` float COMMENT 'shear', `gamma2` float COMMENT 'shear', `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', `x_gal` float COMMENT 'galaxy comoving x coordinate (Mpc/h)', `y_gal` float COMMENT 'galaxy comoving y coordinate (Mpc/h)', `z_gal` float COMMENT 'galaxy comoving z coordinate (Mpc/h)', `r_gal` float COMMENT 'galaxy comoving distance (Mpc/h)', `true_redshift_gal` float COMMENT 'true galaxy redshift', `observed_redshift_gal` float COMMENT 'observed galaxy redshift (including peculiar velocity)', `hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format', `vx_gal` float COMMENT 'physical (peculiar) velocity in x-component (km/s)', `vy_gal` float COMMENT 'physical (peculiar) velocity in y-component (km/s)', `vz_gal` float COMMENT 'physical (peculiar) velocity in z-component (km/s)', `vrad_gal` float COMMENT , `delta_r` float COMMENT , `abs_mag_r01` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h)', `luminosity_r01` float COMMENT 'luminosity in the 0.1-r-band', `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 flux in sdss r-band redshifted to z=0.1', `abs_mag_uv_dereddened` float COMMENT 'absolute magnitude ultraviolet', `gr_restframe` float COMMENT '(g-r) rest-frame at z=0', `color_kind` tinyint COMMENT 'galaxy color kind: 0=red sequence 1=green valley 2=blue cloud', `sed_ke` float COMMENT 'K-correction for each SED', `gr_cosmos` float COMMENT 'interpolated COSMOS g-r color', `sed_cosmos` float COMMENT 'interpolated basic COSMOS SED [0 30]', `is_within_cosmos` boolean COMMENT , `cosmos_distance` float COMMENT , `ext_curve_cosmos` tinyint 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 (Msun/h)', `metallicity` float COMMENT 'oxygen metallicity in units 12+logO/H', `log_sfr` float COMMENT 'logarithm of star formation rate in (Msun/h)/year', `logf_halpha` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: DOES NOT include extinction)', `logf_halpha_ext` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: includes extinction)', `halpha_scatter` float COMMENT , `loglum_halpha` float COMMENT 'DEPRECATED logarithm of Halpha luminosity (erg/s/h^2) using empirical relation with UV magnitude (DOES NOT include extinction)', `loglum_halpha_ext` float COMMENT , `logf_dummy` float COMMENT , `z_dummy` float COMMENT , `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_model1` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: DOES NOT include extinction)', `loglum_halpha_model1_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (includes extinction)', `loglum_halpha_model1` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (DOES NOT include 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_halpha_model3` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: DOES NOT include extinction)', `loglum_halpha_model3_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (includes extinction)', `loglum_halpha_model3` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (DOES NOT include extinction)', `logf_hbeta_model1_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: includes extinction)', `logf_hbeta_model1` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: DOES NOT include 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_o2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include 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_n2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include 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_o3_model1` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: DOES NOT include 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_s2_model1` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)', `logf_hbeta_model3_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: includes extinction)', `logf_hbeta_model3` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: DOES NOT include 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_o2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include 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_n2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include 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_o3_model3` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)', `logf_s2_model3_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: includes extinction)', `logf_s2_model3` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)', `2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)', `2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)', `2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)', `blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)', `blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)', `blanco_decam_r` float COMMENT 'observed flux in DES r-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_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)', `kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)', `kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)', `kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)', `lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)', `lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)', `lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)', `lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)', `lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)', `lsst_z` float COMMENT 'observed flux in LSST z-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_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS H-band (Euclid provided)', `2mass_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS J-band (Euclid provided)', `2mass_ks_odonnell_ext` float COMMENT 'observed flux with MW extinction 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_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES i-band (Euclid provided)', `blanco_decam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES r-band (Euclid provided)', `blanco_decam_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES z-band (Euclid provided)', `cfht_megacam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT r-band (Euclid provided)', `cfht_megacam_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT u-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_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS g-band (Euclid provided)', `kids_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS i-band (Euclid provided)', `kids_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS r-band (Euclid provided)', `kids_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS u-band (Euclid provided)', `lsst_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST g-band (Euclid provided)', `lsst_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST i-band (Euclid provided)', `lsst_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST r-band (Euclid provided)', `lsst_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST u-band (Euclid provided)', `lsst_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST Y-band (Euclid provided)', `lsst_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST z-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)', `sdss_r01_odonnell_ext` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1 with MW extinction', `subaru_hsc_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in HYPER SUPRIME CAMERA SUBARU z-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_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_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_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_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_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)', `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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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)', `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_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_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_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)', `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_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_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_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_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_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_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_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_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_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)', `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_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)', `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_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_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_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)', `sdss_r01_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in sdss r-band redshifted to z=0.1', `sdss_r01_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in sdss r-band redshifted to z=0.1', `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)', `dominant_shape` tinyint COMMENT 'flag to define whether the galaxy is BULGE-dominated (0) or DISK-dominated (1)', `bulge_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_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', `median_major_axis` float COMMENT 'median semi-major axis exponential scalength. Profile defined as: I = I_o x exp (-radius/scalelength)', `scale_length` float COMMENT 'disc and bulge scalelength prior', `bulge_fraction` float COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)', `disk_scalelength` float COMMENT 'scalength of the disk, profile defined as: I = I_o x exp (-radius/disk_scalelength)', `disk_nsersic` float COMMENT 'Sersic index of the disk component', `disk_r50` float COMMENT 'half light radius of the disk. For an exponential profile (or Sersec profile with index n=1), disk_r50 = disk_scalelength * 1.678', `bulge_r50` float COMMENT 'half light radius of the bulge', `bulge_nsersic` float COMMENT 'Sersic index of the bulge component', `disk_ellipticity` float COMMENT 'ellipticity of the disk defined as disk_ellipticity = (1 - disk_axis_ratio) / (1 + disk_axis_ratio)', `bulge_ellipticity` float COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)', `disk_axis_ratio` float COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)', `bulge_axis_ratio` float COMMENT 'bulge projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)', `disk_inclination_angle` float COMMENT , `bulge_inclination_angle` float COMMENT , `halo_x` float COMMENT 'halo comoving x coordinate (Mpc/h)', `halo_y` float COMMENT 'halo comoving y coordinate (Mpc/h)', `halo_z` float COMMENT 'halo comoving z coordinate (Mpc/h)', `halo_vx` float COMMENT 'halo x-centre velocity (km/h)', `halo_vy` float COMMENT 'halo y-centre velocity (km/h)', `halo_vz` float COMMENT 'halo z-centre velocity (km/h)', `halo_r` float COMMENT 'halo comoving distance (Mpc/h)', `true_redshift_halo` float COMMENT 'true redshift of the host halo', `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', `n_gals` int COMMENT 'DEPRECATED', `luminosity_r01_evolved` float COMMENT , `hpix_13_nest` bigint COMMENT ) PARTITIONED BY ( `step` smallint COMMENT 'step number in the Flagship lensing maps') CLUSTERED BY ( hpix_13_nest) SORTED BY ( hpix_13_nest ASC) INTO 128 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' LOCATION 'hdfs://AntNest/warehouse/tablespace/managed/hive/cosmohub.db/flagship_mock_1_9_3_s' TBLPROPERTIES ( 'bucketing_version'='2', 'transient_lastDdlTime'='1595347172');
And I INSERT OVERWRITE:
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_9_3_s PARTITION(step) SELECT halo_id, galaxy_id, kind, random_index, ra_gal, dec_gal, ra_mag_gal, dec_mag_gal, kappa, gamma1, gamma2, l_gal, b_gal, mw_extinction, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, observed_redshift_gal, hpix_29_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, abs_mag_r01, luminosity_r01, abs_mag_r01_evolved, sdss_r01, abs_mag_uv_dereddened, gr_restframe, color_kind, sed_ke, gr_cosmos, sed_cosmos, is_within_cosmos, cosmos_distance, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha, logf_halpha_ext, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_h_odonnell_ext, 2mass_j_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_r_odonnell_ext, cfht_megacam_u_odonnell_ext, euclid_nisp_h_odonnell_ext, euclid_nisp_j_odonnell_ext, euclid_nisp_y_odonnell_ext, euclid_vis_odonnell_ext, gaia_bp_odonnell_ext, gaia_g_odonnell_ext, gaia_rp_odonnell_ext, jst_jpcam_g_odonnell_ext, kids_g_odonnell_ext, kids_i_odonnell_ext, kids_r_odonnell_ext, kids_u_odonnell_ext, lsst_g_odonnell_ext, lsst_i_odonnell_ext, lsst_r_odonnell_ext, lsst_u_odonnell_ext, lsst_y_odonnell_ext, lsst_z_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, sdss_r01_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_i_el_model3_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_y_el_model3_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_z_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, dominant_shape, bulge_angle, disk_angle, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, disk_inclination_angle, bulge_inclination_angle, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, luminosity_r01_evolved, hpix_13_nest, step FROM jcarrete.flagship_mock_1_9_3_p;
ANALYZE TABLE cosmohub.flagship_mock_1_9_3_s COMPUTE STATISTICS FOR COLUMNS;
Then I include the FITS file format as Value-Added-Data:
{
"sql": "SELECT CAST(((halo_id * 10000) + galaxy_id) AS bigint) AS SOURCE_ID, \nCAST(ra_gal AS float) AS RA, \nCAST(dec_gal AS float) AS `DEC`, \nCAST(ra_mag_gal AS float) AS RA_MAG, \nCAST(dec_mag_gal AS float) AS DEC_MAG, \nCAST(observed_redshift_gal AS float) AS Z_OBS, \nCAST(abs_mag_r01_evolved AS float) AS REF_MAG_ABS, \nCAST(-2.5*log10(sdss_r01) - 48.6 AS float) AS REF_MAG, \nCAST(bulge_fraction AS float) AS BULGE_FRACTION, \nCAST(bulge_r50 AS float) AS BULGE_R50, \nCAST(disk_r50 AS float) AS DISK_R50, \nCAST(bulge_nsersic AS float) AS BULGE_NSERSIC, \nCAST(((1-bulge_fraction)*disk_inclination_angle + bulge_fraction*bulge_inclination_angle)*180/3.1415927 AS float) AS INCLINATION_ANGLE, \nCAST(disk_angle AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sed_cosmos AS float) AS SED_TEMPLATE, \nCAST(ROUND(ext_curve_cosmos) AS smallint) AS EXT_LAW, \nCAST(ebv_cosmos AS float) AS EBV, \nCAST(logf_halpha_model3_ext AS float) AS HALPHA_LOGFLAM_EXT, \nCAST(logf_hbeta_model3_ext AS float) AS HBETA_LOGFLAM_EXT, \nCAST(logf_o2_model3_ext AS float) AS O2_LOGFLAM_EXT, \nCAST(logf_o3_model3_ext AS float) AS O3_LOGFLAM_EXT, \nCAST(logf_n2_model3_ext AS float) AS N2_LOGFLAM_EXT, \nCAST(logf_s2_model3_ext AS float) AS S2_LOGFLAM_EXT, \nCAST(mw_extinction AS float) AS AV, \nCAST(euclid_vis_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_VIS, \nCAST(euclid_nisp_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_NISP, \nCAST(euclid_nisp_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_NISP, \nCAST(euclid_nisp_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_NISP, \nCAST(blanco_decam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_DECAM, \nCAST(blanco_decam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_DECAM, \nCAST(blanco_decam_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_DECAM, \nCAST(blanco_decam_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_DECAM, \nCAST(cfht_megacam_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_MEGACAM, \nCAST(cfht_megacam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_MEGACAM, \nCAST(jst_jpcam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_JPCAM, \nCAST(pan_starrs_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_PANSTARRS, \nCAST(pan_starrs_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_PANSTARRS, \nCAST(subaru_hsc_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_HSC, \nCAST(gaia_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_GAIA, \nCAST(gaia_bp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_BP_GAIA, \nCAST(gaia_rp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_RP_GAIA, \nCAST(lsst_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_LSST, \nCAST(lsst_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_LSST, \nCAST(lsst_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_LSST, \nCAST(lsst_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_LSST, \nCAST(lsst_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_LSST, \nCAST(lsst_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_LSST, \nCAST(kids_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_KIDS, \nCAST(kids_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_KIDS, \nCAST(kids_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_KIDS, \nCAST(kids_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_KIDS, \nCAST(2mass_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_2MASS, \nCAST(2mass_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_2MASS, \nCAST(2mass_ks_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_KS_2MASS, \nCAST( SHIFTRIGHT(hpix_29_nest, (29-5)*2) AS bigint) AS hpix_5_nest \nFROM cosmohub.flagship_mock_1_9_3_s \nWHERE (logf_halpha_model3_ext > -16 OR -2.5*log10(euclid_nisp_h) - 48.6 < 26) \nAND disk_axis_ratio > 0.10865 \nAND SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 4522"
}
To create all FITS files:
CREATE TABLE `jcarrete.flagship_mock_1_9_3_fits`( `source_id` bigint COMMENT 'from deserializer', `ra` float COMMENT 'from deserializer', `dec` float COMMENT 'from deserializer', `ra_mag` float COMMENT 'from deserializer', `dec_mag` float COMMENT 'from deserializer', `z_obs` float COMMENT 'from deserializer', `ref_mag_abs` float COMMENT 'from deserializer', `ref_mag` float COMMENT 'from deserializer', `bulge_fraction` float COMMENT 'from deserializer', `bulge_r50` float COMMENT 'from deserializer', `disk_r50` float COMMENT 'from deserializer', `bulge_nsersic` float COMMENT 'from deserializer', `inclination_angle` float COMMENT 'from deserializer', `disk_angle` float COMMENT 'from deserializer', `kappa` float COMMENT 'from deserializer', `gamma1` float COMMENT 'from deserializer', `gamma2` float COMMENT 'from deserializer', `sed_template` float COMMENT 'from deserializer', `ext_law` smallint COMMENT 'from deserializer', `ebv` float COMMENT 'from deserializer', `halpha_logflam_ext` float COMMENT 'from deserializer', `hbeta_logflam_ext` float COMMENT 'from deserializer', `o2_logflam_ext` float COMMENT 'from deserializer', `o3_logflam_ext` float COMMENT 'from deserializer', `n2_logflam_ext` float COMMENT 'from deserializer', `s2_logflam_ext` float COMMENT 'from deserializer', `av` float COMMENT 'from deserializer', `tu_fnu_vis` float COMMENT 'from deserializer', `tu_fnu_y_nisp` float COMMENT 'from deserializer', `tu_fnu_j_nisp` float COMMENT 'from deserializer', `tu_fnu_h_nisp` float COMMENT 'from deserializer', `tu_fnu_g_decam` float COMMENT 'from deserializer', `tu_fnu_r_decam` float COMMENT 'from deserializer', `tu_fnu_i_decam` float COMMENT 'from deserializer', `tu_fnu_z_decam` float COMMENT 'from deserializer', `tu_fnu_u_megacam` float COMMENT 'from deserializer', `tu_fnu_r_megacam` float COMMENT 'from deserializer', `tu_fnu_g_jpcam` float COMMENT 'from deserializer', `tu_fnu_i_panstarrs` float COMMENT 'from deserializer', `tu_fnu_z_panstarrs` float COMMENT 'from deserializer', `tu_fnu_z_hsc` float COMMENT 'from deserializer', `tu_fnu_g_gaia` float COMMENT 'from deserializer', `tu_fnu_bp_gaia` float COMMENT 'from deserializer', `tu_fnu_rp_gaia` float COMMENT 'from deserializer', `tu_fnu_u_lsst` float COMMENT 'from deserializer', `tu_fnu_g_lsst` float COMMENT 'from deserializer', `tu_fnu_r_lsst` float COMMENT 'from deserializer', `tu_fnu_i_lsst` float COMMENT 'from deserializer', `tu_fnu_z_lsst` float COMMENT 'from deserializer', `tu_fnu_y_lsst` float COMMENT 'from deserializer', `tu_fnu_u_kids` float COMMENT 'from deserializer', `tu_fnu_g_kids` float COMMENT 'from deserializer', `tu_fnu_r_kids` float COMMENT 'from deserializer', `tu_fnu_i_kids` float COMMENT 'from deserializer', `tu_fnu_j_2mass` float COMMENT 'from deserializer', `tu_fnu_h_2mass` float COMMENT 'from deserializer', `tu_fnu_ks_2mass` float COMMENT 'from deserializer') PARTITIONED BY ( `hpix_5_nest` bigint) ROW FORMAT SERDE 'es.pic.astro.hadoop.serde.RecArraySerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'es.pic.astro.hadoop.io.BinaryOutputFormat' ;
Note that there is a small change in the FORMAT of the table.
Before we used the following:
)
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'
;
We don't know why it does not work in the current version.
Then, the INSERT OVERWRITE COMMAND:
INSERT OVERWRITE TABLE flagship_mock_1_9_3_fits PARTITION(hpix_5_nest) SELECT CAST(((halo_id * 10000) + galaxy_id) AS bigint) AS SOURCE_ID, CAST(ra_gal AS float) AS RA, CAST(dec_gal AS float) AS `DEC`, CAST(ra_mag_gal AS float) AS RA_MAG, CAST(dec_mag_gal AS float) AS DEC_MAG, CAST(observed_redshift_gal AS float) AS Z_OBS, CAST(abs_mag_r01_evolved AS float) AS REF_MAG_ABS, CAST(-2.5*log10(sdss_r01) - 48.6 AS float) AS REF_MAG, CAST(bulge_fraction AS float) AS BULGE_FRACTION, CAST(bulge_r50 AS float) AS BULGE_R50, CAST(disk_r50 AS float) AS DISK_R50, CAST(bulge_nsersic AS float) AS BULGE_NSERSIC, CAST(((1-bulge_fraction)*disk_inclination_angle + bulge_fraction*bulge_inclination_angle)*180/3.1415927 AS float) AS INCLINATION_ANGLE, CAST(disk_angle AS float) AS DISK_ANGLE, CAST(kappa AS float) AS KAPPA, CAST(gamma1 AS float) AS GAMMA1, CAST(gamma2 AS float) AS GAMMA2, CAST( sed_cosmos AS float) AS SED_TEMPLATE, CAST(ROUND(ext_curve_cosmos) AS smallint) AS EXT_LAW, CAST(ebv_cosmos AS float) AS EBV, CAST(logf_halpha_model3_ext AS float) AS HALPHA_LOGFLAM_EXT, CAST(logf_hbeta_model3_ext AS float) AS HBETA_LOGFLAM_EXT, CAST(logf_o2_model3_ext AS float) AS O2_LOGFLAM_EXT, CAST(logf_o3_model3_ext AS float) AS O3_LOGFLAM_EXT, CAST(logf_n2_model3_ext AS float) AS N2_LOGFLAM_EXT, CAST(logf_s2_model3_ext AS float) AS S2_LOGFLAM_EXT, CAST(mw_extinction AS float) AS AV, CAST(euclid_vis_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_VIS, CAST(euclid_nisp_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_NISP, CAST(euclid_nisp_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_NISP, CAST(euclid_nisp_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_NISP, CAST(blanco_decam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_DECAM, CAST(blanco_decam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_DECAM, CAST(blanco_decam_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_DECAM, CAST(blanco_decam_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_DECAM, CAST(cfht_megacam_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_MEGACAM, CAST(cfht_megacam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_MEGACAM, CAST(jst_jpcam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_JPCAM, CAST(pan_starrs_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_PANSTARRS, CAST(pan_starrs_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_PANSTARRS, CAST(subaru_hsc_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_HSC, CAST(gaia_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_GAIA, CAST(gaia_bp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_BP_GAIA, CAST(gaia_rp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_RP_GAIA, CAST(lsst_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_LSST, CAST(lsst_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_LSST, CAST(lsst_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_LSST, CAST(lsst_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_LSST, CAST(lsst_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_LSST, CAST(lsst_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_LSST, CAST(kids_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_KIDS, CAST(kids_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_KIDS, CAST(kids_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_KIDS, CAST(kids_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_KIDS, CAST(2mass_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_2MASS, CAST(2mass_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_2MASS, CAST(2mass_ks_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_KS_2MASS, CAST( SHIFTRIGHT(hpix_29_nest, (29-5)*2) AS bigint) AS hpix_5_nest FROM cosmohub.flagship_mock_1_9_3_s WHERE (logf_halpha_model3_ext > -16 OR -2.5*log10(euclid_nisp_h) - 48.6 < 26) AND disk_axis_ratio > 0.10865 ;
And finally we add the proper header and store the FITS files in the webdavdoor using the corresponding notebook. It is tag in the git repository as tag: v1.9.3_FITS.