Difference between revisions of "Euclid mock production v1.7.17"
Jump to navigation
Jump to search
(Created page with "From data.astro: ./create_external_table_pepino.sh '/user/jcarrete/data/euclid/flagship_SC456_1_7_17/mock/' flagship_SC456_1_7_17_p CREATE TABLE `cosmohub.flagship_SC456_1_7...") |
|||
| Line 3: | Line 3: | ||
./create_external_table_pepino.sh '/user/jcarrete/data/euclid/flagship_SC456_1_7_17/mock/' flagship_SC456_1_7_17_p | ./create_external_table_pepino.sh '/user/jcarrete/data/euclid/flagship_SC456_1_7_17/mock/' flagship_SC456_1_7_17_p | ||
| − | CREATE TABLE `cosmohub.flagship_SC456_1_7_17_s`( | + | CREATE TABLE `cosmohub.flagship_SC456_1_7_17_s`( |
`halo_id` bigint COMMENT 'unique halo id coming from the Flagship dark matter halo catalog', | `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', | `galaxy_id` int COMMENT 'galaxy id, it is not a unique indentifier, the unique identifier is given by the halo_id together with the', | ||
| Line 167: | Line 167: | ||
`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_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' | `halo_n_sats` int COMMENT 'number of satellite galaxies from HOD' | ||
| − | ) | + | ) |
| − | CLUSTERED BY ( | + | CLUSTERED BY ( |
halo_id, | halo_id, | ||
galaxy_id) | galaxy_id) | ||
| − | INTO 4096 BUCKETS | + | INTO 4096 BUCKETS |
| − | ROW FORMAT SERDE | + | ROW FORMAT SERDE |
'org.apache.hadoop.hive.ql.io.orc.OrcSerde' | 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' | ||
| − | STORED AS INPUTFORMAT | + | STORED AS INPUTFORMAT |
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' | 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' | ||
| − | OUTPUTFORMAT | + | OUTPUTFORMAT |
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' | 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' | ||
; | ; | ||
| Line 182: | Line 182: | ||
# From parquet to ORC: | # From parquet to ORC: | ||
| − | INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_17_s | + | INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_17_s |
| − | SELECT halo_id, galaxy_id, ra_gal, dec_gal, l_gal, b_gal, mw_extinction, ra_gal_mag, dec_gal_mag, kappa, gamma1, gamma2, true_redshift_gal, observed_redshift_gal, hpix_29_nest, abs_mag_r01_evolved, sdss_r01, sed_cosmos, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, log_sfr, logf_halpha_model1_ext, logf_halpha_model3_ext, logf_hbeta_model1_ext, logf_o2_model1_ext, logf_n2_model1_ext, logf_o3_model1_ext, logf_s2_model1_ext, logf_hbeta_model3_ext, logf_o2_model3_ext, logf_n2_model3_ext, logf_o3_model3_ext, logf_s2_model3_ext, blanco_decam_g, blanco_decam_r, blanco_decam_i, 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_u, kids_g, kids_r, kids_i, lsst_u, lsst_g, lsst_r, lsst_i, lsst_z, lsst_y, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_j, 2mass_h, 2mass_ks, blanco_decam_g_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_u_odonnell_ext, cfht_megacam_r_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_u_odonnell_ext, kids_g_odonnell_ext, kids_r_odonnell_ext, kids_i_odonnell_ext, lsst_u_odonnell_ext, lsst_g_odonnell_ext, lsst_r_odonnell_ext, lsst_i_odonnell_ext, lsst_z_odonnell_ext, lsst_y_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_j_odonnell_ext, 2mass_h_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_i_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_z_el_model3_odonnell_ext, lsst_y_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, dominant_shape, bulge_fraction, bulge_length, disk_angle, disk_axis_ratio, disk_length, halo_lm, halo_n_sats | + | SELECT halo_id, galaxy_id, ra_gal, dec_gal, l_gal, b_gal, mw_extinction, ra_gal_mag, dec_gal_mag, kappa, gamma1, gamma2, true_redshift_gal, observed_redshift_gal, hpix_29_nest, abs_mag_r01_evolved, sdss_r01, sed_cosmos, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, log_sfr, logf_halpha_model1_ext, logf_halpha_model3_ext, logf_hbeta_model1_ext, logf_o2_model1_ext, logf_n2_model1_ext, logf_o3_model1_ext, logf_s2_model1_ext, logf_hbeta_model3_ext, logf_o2_model3_ext, logf_n2_model3_ext, logf_o3_model3_ext, logf_s2_model3_ext, blanco_decam_g, blanco_decam_r, blanco_decam_i, 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_u, kids_g, kids_r, kids_i, lsst_u, lsst_g, lsst_r, lsst_i, lsst_z, lsst_y, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_j, 2mass_h, 2mass_ks, blanco_decam_g_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_u_odonnell_ext, cfht_megacam_r_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_u_odonnell_ext, kids_g_odonnell_ext, kids_r_odonnell_ext, kids_i_odonnell_ext, lsst_u_odonnell_ext, lsst_g_odonnell_ext, lsst_r_odonnell_ext, lsst_i_odonnell_ext, lsst_z_odonnell_ext, lsst_y_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_j_odonnell_ext, 2mass_h_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_i_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_z_el_model3_odonnell_ext, lsst_y_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, dominant_shape, bulge_fraction, bulge_length, disk_angle, disk_axis_ratio, disk_length, halo_lm, halo_n_sats |
| − | FROM jcarrete.flagship_mock_sc456_1_7_17_p | + | FROM jcarrete.flagship_mock_sc456_1_7_17_p |
| − | ; | + | ; |
# ADD catalog to CosmoHub: | # ADD catalog to CosmoHub: | ||
| Line 262: | Line 262: | ||
For further information please contact Pablo Fosalba (fosalba@ieec.uab.es; ICE, IEEC-CSIC) or Romain Teyssier (romain.teyssier@uzh.ch; UZH/ICS). | For further information please contact Pablo Fosalba (fosalba@ieec.uab.es; ICE, IEEC-CSIC) or Romain Teyssier (romain.teyssier@uzh.ch; UZH/ICS). | ||
| + | # Creating FITS files: | ||
| − | ADD JAR hdfs:///apps/cosmohub/lib/recarray-serde-LATEST-with-dependencies.jar; | + | ADD JAR hdfs:///apps/cosmohub/lib/recarray-serde-LATEST-with-dependencies.jar; |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | CREATE TABLE `cosmohub.flagship_sc456_1_7_17_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' | ||
| + | ; | ||
| − | + | Writing FITS files: | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_17_jy_fits PARTITION(hpix_5_nest) | + | INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_17_jy_fits PARTITION(hpix_5_nest) |
| − | SELECT | + | SELECT |
CAST( ((gal.halo_id * 10000) + gal.galaxy_id) AS bigint) AS SOURCE_ID, | CAST( ((gal.halo_id * 10000) + gal.galaxy_id) AS bigint) AS SOURCE_ID, | ||
CAST( gal.ra_gal AS float) AS RA, | CAST( gal.ra_gal AS float) AS RA, | ||
| Line 469: | Line 397: | ||
CAST( gal.2mass_ks_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_KS_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 | CAST( SHIFTRIGHT(gal.hpix_29_nest, (29-5)*2) AS bigint) AS hpix_5_nest | ||
| − | FROM cosmohub.flagship_sc456_1_7_17_s AS gal | + | FROM cosmohub.flagship_sc456_1_7_17_s AS gal |
| − | WHERE (gal.logf_halpha_model3_ext > -16 OR gal.euclid_nisp_h < 26) | + | WHERE (gal.logf_halpha_model3_ext > -16 OR gal.euclid_nisp_h < 26) |
| − | AND gal.disk_axis_ratio > 0.10865 | + | AND gal.disk_axis_ratio > 0.10865 |
| − | ; | + | ; |
| + | |||
| + | Estimating some statistics: | ||
| − | ANALYZE TABLE cosmohub.flagship_sc456_1_7_17_s COMPUTE STATISTICS FOR COLUMNS; | + | ANALYZE TABLE cosmohub.flagship_sc456_1_7_17_s COMPUTE STATISTICS FOR COLUMNS; |
Latest revision as of 11:05, 12 July 2019
From data.astro:
./create_external_table_pepino.sh '/user/jcarrete/data/euclid/flagship_SC456_1_7_17/mock/' flagship_SC456_1_7_17_p
CREATE TABLE `cosmohub.flagship_SC456_1_7_17_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'
- From parquet to ORC:
INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_17_s SELECT halo_id, galaxy_id, ra_gal, dec_gal, l_gal, b_gal, mw_extinction, ra_gal_mag, dec_gal_mag, kappa, gamma1, gamma2, true_redshift_gal, observed_redshift_gal, hpix_29_nest, abs_mag_r01_evolved, sdss_r01, sed_cosmos, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, log_sfr, logf_halpha_model1_ext, logf_halpha_model3_ext, logf_hbeta_model1_ext, logf_o2_model1_ext, logf_n2_model1_ext, logf_o3_model1_ext, logf_s2_model1_ext, logf_hbeta_model3_ext, logf_o2_model3_ext, logf_n2_model3_ext, logf_o3_model3_ext, logf_s2_model3_ext, blanco_decam_g, blanco_decam_r, blanco_decam_i, 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_u, kids_g, kids_r, kids_i, lsst_u, lsst_g, lsst_r, lsst_i, lsst_z, lsst_y, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_j, 2mass_h, 2mass_ks, blanco_decam_g_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_u_odonnell_ext, cfht_megacam_r_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_u_odonnell_ext, kids_g_odonnell_ext, kids_r_odonnell_ext, kids_i_odonnell_ext, lsst_u_odonnell_ext, lsst_g_odonnell_ext, lsst_r_odonnell_ext, lsst_i_odonnell_ext, lsst_z_odonnell_ext, lsst_y_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_j_odonnell_ext, 2mass_h_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_i_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_z_el_model3_odonnell_ext, lsst_y_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, dominant_shape, bulge_fraction, bulge_length, disk_angle, disk_axis_ratio, disk_length, halo_lm, halo_n_sats FROM jcarrete.flagship_mock_sc456_1_7_17_p ;
- ADD catalog to CosmoHub:
Flagship Galaxy mock
1.7.17
Euclid Flagship galaxy mock (SC456 pointings)
- Euclid Flagship galaxy mock
Version 1.7.17: Changes from 1.7.13: Discrepancies between SimSpectra and SciPIC algorithms solved. This catalog is based on the previous catalog Euclid Flagship galaxy mock 1.6.18, which used the Euclid footprint (WIDE area). The catalog has been created to cover the area for the scientific challenge 456 (SC456). Pointings are defined here: https://euclid.roe.ac.uk/projects/ec_sgs_challenges/wiki/SC456_FoV The catalog contains the same fields than the metioned catalog (WIDE) but includes the following new fields: all the different observed fluxes, l_gal, b_gal and mw_extinction. Filter list as well as the fields in the catalog for the SC456 are defined here: https://euclid.roe.ac.uk/issues/7759 The catalog contains 566233578 (~566M) galaxies up to z=2.3 with a cut at magnitude H<26 or Hα flux log(f_H_α) < -16 ergs/s/cm2- We provide fluxes instead of magnitudes. In order to get magnitudes you can enter in the Expert mode and estimate the magnitude: -2.5 * log10(flux) - 48.6
The following figure shows the different pointings for SC456, in particular the number counts per pixel (2000x1000) in RA, Dec_norm (Dec_norm = sin(dec_gal*pi()/180)).
![alt text][Flagship_SC456_area]
[Flagship_SC456_area]: https://www.dropbox.com/s/2k8fmsqxiiir1be/Euclid_Flagship_v1.7.9_SC456.png?raw=1
The cosmological parameters are:
- Ωm=0.319 - Ωb=0.049 - ΩΛ=0.681 - σ8 = 0.83 - ns=0.96 - h=0.67
and the particle mass is mp~2.398 x 109 Msun/h.
The galaxy light-cone catalog contains 2.6 B galaxies over 5000 deg2 and it extends up to z=2.3 with a cut at magnitude H<26 or Hα flux log(f_H_α) < -16 ergs/s/cm2.
```Note that the H_α flux variables you should use are the following ones:```
```logf_halpha_model1, logf_halpha_model1_ext, loglum_halpha_model1, loglum_halpha_model1_ext```
```logf_halpha_model3, logf_halpha_model3_ext, loglum_halpha_model3, loglum_halpha_model3_ext```
```The rest of the H_α fields are not properly fit to observations and therefore you should not use them.```
The catalog includes:
- Spectroscopic and photometric information - Lensing properties - Shape parameters
The mock galaxy catalog has been generated at PIC using the SciPIC pipeline on top of a Big Data platform based on Apache Hadoop. It is calibrated using local observational constraints:
- The local luminosity function (Blanton et al. 2003 and Blanton et al. 2005 for the faintest galaxies) - The galaxy clustering as a function of luminosity and colour (Zehavi et al. 2011) - The color-magnitude diagram (NYU DR7 catalog)
A short summary of the recipes used for the construction of this catalog (see References for details):
- galaxies are assigned to halos following Halo Occupation Distribution (HOD), discriminating between central and satellite galaxies - galaxy luminosities are assigned using Halo Abundance Matching (HAM), constrained by the observed local luminosity function - (g-r) restframe galaxy colors are assigned using HOD, discriminating between central and satellite galaxies, constrained by the observed color-magnitude diagram - evolutionary corrections are applied following evolutionary spectral synthesis model libraries (PEGASE) - Spectral Energy Distributions (SEDs) with dust extinction are assigned by sampling the COSMOS galaxy catalog, where galaxies are matched to observed ones using redshift, absolute magnitude and color - stellar properties and emission line fluxes are assigned using empirical relations - H_α emission line fluxes are re-calibrated to match the model of Pozzetti et al. 2016 (arXiv:1603.01453) - morphological parameters are assigned using recipes from Miller et al. 2013 (arXiv:1210.8201). Some basic checks with previous simulations and a brief description of the followed approach can be found in the following link <https://www.dropbox.com/s/unj7pk3zajss2ec/Galaxy_shapes_DC3.pdf?dl=0>. - lensing properties and magnified positions due to gravitational lensing effects are computed within the Born approximation using projected mass density maps (in HEALPIX format) generated from the particle light-cone of the Flagship simulation
For further information please contact Pablo Fosalba (fosalba@ieec.uab.es; ICE, IEEC-CSIC) or Romain Teyssier (romain.teyssier@uzh.ch; UZH/ICS).
- Creating FITS files:
ADD JAR hdfs:///apps/cosmohub/lib/recarray-serde-LATEST-with-dependencies.jar;
CREATE TABLE `cosmohub.flagship_sc456_1_7_17_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'
;
Writing FITS files:
INSERT OVERWRITE TABLE cosmohub.flagship_sc456_1_7_17_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_17_s AS gal WHERE (gal.logf_halpha_model3_ext > -16 OR gal.euclid_nisp_h < 26) AND gal.disk_axis_ratio > 0.10865 ;
Estimating some statistics:
ANALYZE TABLE cosmohub.flagship_sc456_1_7_17_s COMPUTE STATISTICS FOR COLUMNS;