High-z galaxies
Jump to navigation
Jump to search
Description of the process to generate the High-z galaxies catalog for SC8
Pau runs the pipeline and create a catalog in parquet:
Ojo porque en el hdfs tiene 1_10_4 y sin embargo en el 1_10_5
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/tallada/data/euclid/flagship_mock_1_10_4_high_z/0.pq -t jcarrete.flagship_mock_1_10_5_highz_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_5_highz_pq (
`type` STRING,
`z` DOUBLE,
`abs_muv` DOUBLE,
`sedname` STRING,
`sedname_int` BIGINT,
`rhalf_kpc` DOUBLE,
`rhalf_arcsec` DOUBLE,
`muv` DOUBLE,
`beta` DOUBLE,
`r` DOUBLE,
`hpix_9_nest` BIGINT,
`kind` BIGINT,
`step` BIGINT,
`unique_id` BIGINT,
`hpix_29_nest` BIGINT,
`ra` DOUBLE,
`dec` DOUBLE,
`l` DOUBLE,
`b` DOUBLE,
`mw_extinction` DOUBLE,
`blanco_decam_g_true` DOUBLE,
`blanco_decam_g` DOUBLE,
`blanco_decam_g_odonnell_ext` DOUBLE,
`blanco_decam_i_true` DOUBLE,
`blanco_decam_i` DOUBLE,
`blanco_decam_i_odonnell_ext` DOUBLE,
`blanco_decam_r_true` DOUBLE,
`blanco_decam_r` DOUBLE,
`blanco_decam_r_odonnell_ext` DOUBLE,
`blanco_decam_z_true` DOUBLE,
`blanco_decam_z` DOUBLE,
`blanco_decam_z_odonnell_ext` DOUBLE,
`subaru_hsc_z_true` DOUBLE,
`subaru_hsc_z` DOUBLE,
`subaru_hsc_z_odonnell_ext` DOUBLE,
`jst_jpcam_g_true` DOUBLE,
`jst_jpcam_g` DOUBLE,
`jst_jpcam_g_odonnell_ext` DOUBLE,
`kids_g_true` DOUBLE,
`kids_g` DOUBLE,
`kids_g_odonnell_ext` DOUBLE,
`kids_i_true` DOUBLE,
`kids_i` DOUBLE,
`kids_i_odonnell_ext` DOUBLE,
`kids_r_true` DOUBLE,
`kids_r` DOUBLE,
`kids_r_odonnell_ext` DOUBLE,
`kids_u_true` DOUBLE,
`kids_u` DOUBLE,
`kids_u_odonnell_ext` DOUBLE,
`lsst_g_true` DOUBLE,
`lsst_g` DOUBLE,
`lsst_g_odonnell_ext` DOUBLE,
`lsst_i_true` DOUBLE,
`lsst_i` DOUBLE,
`lsst_i_odonnell_ext` DOUBLE,
`lsst_r_true` DOUBLE,
`lsst_r` DOUBLE,
`lsst_r_odonnell_ext` DOUBLE,
`lsst_u_true` DOUBLE,
`lsst_u` DOUBLE,
`lsst_u_odonnell_ext` DOUBLE,
`lsst_y_true` DOUBLE,
`lsst_y` DOUBLE,
`lsst_y_odonnell_ext` DOUBLE,
`lsst_z_true` DOUBLE,
`lsst_z` DOUBLE,
`lsst_z_odonnell_ext` DOUBLE,
`cfht_megacam_r_true` DOUBLE,
`cfht_megacam_r` DOUBLE,
`cfht_megacam_r_odonnell_ext` DOUBLE,
`cfht_megacam_u_true` DOUBLE,
`cfht_megacam_u` DOUBLE,
`cfht_megacam_u_odonnell_ext` DOUBLE,
`pan_starrs_i_true` DOUBLE,
`pan_starrs_i` DOUBLE,
`pan_starrs_i_odonnell_ext` DOUBLE,
`pan_starrs_z_true` DOUBLE,
`pan_starrs_z` DOUBLE,
`pan_starrs_z_odonnell_ext` DOUBLE,
`2mass_h_true` DOUBLE,
`2mass_h` DOUBLE,
`2mass_h_odonnell_ext` DOUBLE,
`2mass_j_true` DOUBLE,
`2mass_j` DOUBLE,
`2mass_j_odonnell_ext` DOUBLE,
`2mass_ks_true` DOUBLE,
`2mass_ks` DOUBLE,
`2mass_ks_odonnell_ext` DOUBLE,
`sdss_r01_true` DOUBLE,
`sdss_r01` DOUBLE,
`sdss_r01_odonnell_ext` DOUBLE,
`gaia_bp_true` DOUBLE,
`gaia_bp` DOUBLE,
`gaia_bp_odonnell_ext` DOUBLE,
`gaia_g_true` DOUBLE,
`gaia_g` DOUBLE,
`gaia_g_odonnell_ext` DOUBLE,
`gaia_rp_true` DOUBLE,
`gaia_rp` DOUBLE,
`gaia_rp_odonnell_ext` DOUBLE,
`euclid_nisp_h_true` DOUBLE,
`euclid_nisp_h` DOUBLE,
`euclid_nisp_h_odonnell_ext` DOUBLE,
`euclid_nisp_j_true` DOUBLE,
`euclid_nisp_j` DOUBLE,
`euclid_nisp_j_odonnell_ext` DOUBLE,
`euclid_nisp_y_true` DOUBLE,
`euclid_nisp_y` DOUBLE,
`euclid_nisp_y_odonnell_ext` DOUBLE,
`euclid_vis_true` DOUBLE,
`euclid_vis` DOUBLE,
`euclid_vis_odonnell_ext` DOUBLE,
`id` BIGINT
)
STORED AS PARQUET
LOCATION '/user/tallada/data/euclid/flagship_mock_1_10_4_high_z'
;
Clusterizo:
CREATE TABLE cosmohub.flagship_mock_1_10_5_highz_c (
`unique_id` BIGINT COMMENT 'unique identifier',
`id` INT COMMENT 'ID',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`z` FLOAT,
`ra` FLOAT,
`dec` FLOAT,
`l` FLOAT,
`b` FLOAT,
`mw_extinction` FLOAT,
`abs_muv` FLOAT,
`sedname` STRING,
`sedname_int` BIGINT,
`rhalf_kpc` FLOAT,
`rhalf_arcsec` FLOAT,
`muv` FLOAT,
`beta` FLOAT,
`hpix_9_nest` INT,
`hpix_13_nest` INT,
`hpix_29_nest` BIGINT,
`blanco_decam_g_true` FLOAT,
`blanco_decam_g` FLOAT,
`blanco_decam_g_odonnell_ext` FLOAT,
`blanco_decam_i_true` FLOAT,
`blanco_decam_i` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`blanco_decam_r_true` FLOAT,
`blanco_decam_r` FLOAT,
`blanco_decam_r_odonnell_ext` FLOAT,
`blanco_decam_z_true` FLOAT,
`blanco_decam_z` FLOAT,
`blanco_decam_z_odonnell_ext` FLOAT,
`subaru_hsc_z_true` FLOAT,
`subaru_hsc_z` FLOAT,
`subaru_hsc_z_odonnell_ext` FLOAT,
`jst_jpcam_g_true` FLOAT,
`jst_jpcam_g` FLOAT,
`jst_jpcam_g_odonnell_ext` FLOAT,
`kids_g_true` FLOAT,
`kids_g` FLOAT,
`kids_g_odonnell_ext` FLOAT,
`kids_i_true` FLOAT,
`kids_i` FLOAT,
`kids_i_odonnell_ext` FLOAT,
`kids_r_true` FLOAT,
`kids_r` FLOAT,
`kids_r_odonnell_ext` FLOAT,
`kids_u_true` FLOAT,
`kids_u` FLOAT,
`kids_u_odonnell_ext` FLOAT,
`lsst_g_true` FLOAT,
`lsst_g` FLOAT,
`lsst_g_odonnell_ext` FLOAT,
`lsst_i_true` FLOAT,
`lsst_i` FLOAT,
`lsst_i_odonnell_ext` FLOAT,
`lsst_r_true` FLOAT,
`lsst_r` FLOAT,
`lsst_r_odonnell_ext` FLOAT,
`lsst_u_true` FLOAT,
`lsst_u` FLOAT,
`lsst_u_odonnell_ext` FLOAT,
`lsst_y_true` FLOAT,
`lsst_y` FLOAT,
`lsst_y_odonnell_ext` FLOAT,
`lsst_z_true` FLOAT,
`lsst_z` FLOAT,
`lsst_z_odonnell_ext` FLOAT,
`cfht_megacam_r_true` FLOAT,
`cfht_megacam_r` FLOAT,
`cfht_megacam_r_odonnell_ext` FLOAT,
`cfht_megacam_u_true` FLOAT,
`cfht_megacam_u` FLOAT,
`cfht_megacam_u_odonnell_ext` FLOAT,
`pan_starrs_i_true` FLOAT,
`pan_starrs_i` FLOAT,
`pan_starrs_i_odonnell_ext` FLOAT,
`pan_starrs_z_true` FLOAT,
`pan_starrs_z` FLOAT,
`pan_starrs_z_odonnell_ext` FLOAT,
`2mass_h_true` FLOAT,
`2mass_h` FLOAT,
`2mass_h_odonnell_ext` FLOAT,
`2mass_j_true` FLOAT,
`2mass_j` FLOAT,
`2mass_j_odonnell_ext` FLOAT,
`2mass_ks_true` FLOAT,
`2mass_ks` FLOAT,
`2mass_ks_odonnell_ext` FLOAT,
`sdss_r01_true` FLOAT,
`sdss_r01` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`gaia_bp_true` FLOAT,
`gaia_bp` FLOAT,
`gaia_bp_odonnell_ext` FLOAT,
`gaia_g_true` FLOAT,
`gaia_g` FLOAT,
`gaia_g_odonnell_ext` FLOAT,
`gaia_rp_true` FLOAT,
`gaia_rp` FLOAT,
`gaia_rp_odonnell_ext` FLOAT,
`euclid_nisp_h_true` FLOAT,
`euclid_nisp_h` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`euclid_nisp_j_true` FLOAT,
`euclid_nisp_j` FLOAT,
`euclid_nisp_j_odonnell_ext` FLOAT,
`euclid_nisp_y_true` FLOAT,
`euclid_nisp_y` FLOAT,
`euclid_nisp_y_odonnell_ext` FLOAT,
`euclid_vis_true` FLOAT,
`euclid_vis` FLOAT,
`euclid_vis_odonnell_ext` FLOAT,
`step` smallint COMMENT 'redshift step number'
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_5_highz_c SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, `l`, `b`, `mw_extinction`, `abs_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, SHIFTRIGHT(hpix_29_nest, (29-13)*2), `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `subaru_hsc_z_true`, `subaru_hsc_z`, `subaru_hsc_z_odonnell_ext`, `jst_jpcam_g_true`, `jst_jpcam_g`, `jst_jpcam_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfht_megacam_r_true`, `cfht_megacam_r`, `cfht_megacam_r_odonnell_ext`, `cfht_megacam_u_true`, `cfht_megacam_u`, `cfht_megacam_u_odonnell_ext`, `pan_starrs_i_true`, `pan_starrs_i`, `pan_starrs_i_odonnell_ext`, `pan_starrs_z_true`, `pan_starrs_z`, `pan_starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, `step` FROM jcarrete.flagship_mock_1_10_5_highz_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_5_highz_c COMPUTE STATISTICS FOR COLUMNS;
Añado el lensing:
CREATE TABLE cosmohub.flagship_mock_1_10_5_highz_s (
`unique_id` BIGINT COMMENT 'unique identifier',
`id` INT COMMENT 'ID',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`z` float COMMENT 'true galaxy redshift',
`ra` FLOAT COMMENT 'galaxy right ascension (degrees)',
`dec` FLOAT COMMENT 'galaxy declination (degrees)',
`ra_mag` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l` FLOAT COMMENT 'galactic longitude (degrees)',
`b` FLOAT COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`abs_muv` FLOAT COMMENT 'absolute magnitude at 1500 angstroms',
`sedname` STRING COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
`sedname_int` BIGINT COMMENT 'sedname as integer (from 0 to 75, from 0 to 37 without UV lines, from 38 to 75 with UV lines)',
`rhalf_kpc` FLOAT COMMENT 'half light radius (in physical kpc)',
`rhalf_arcsec` FLOAT COMMENT 'half light radius (in arcsec)',
`muv` FLOAT COMMENT 'for information only, observed magnitude at rest-frame 1500',
`beta` FLOAT COMMENT 'for information only, used to assign an SED',
`hpix_9_nest` int COMMENT 'healpix pixel id using order = 9 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`blanco_decam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'Interpolated observed flux in DES g-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'Interpolated observed flux in DES i-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'Interpolated observed flux in DES r-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'Interpolated observed flux in DES z-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES z-band (Euclid provided)',
`subaru_hsc_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'Interpolated observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`jst_jpcam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in JST g-band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'Interpolated observed flux in JST g-band (Euclid provided)',
`jst_jpcam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in JST g-band (Euclid provided)',
`kids_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS g-band (Euclid provided)',
`kids_g` float COMMENT 'Interpolated observed flux in KIDS g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS g-band (Euclid provided)',
`kids_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS i-band (Euclid provided)',
`kids_i` float COMMENT 'Interpolated observed flux in KIDS i-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS i-band (Euclid provided)',
`kids_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS r-band (Euclid provided)',
`kids_r` float COMMENT 'Interpolated observed flux in KIDS r-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS r-band (Euclid provided)',
`kids_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS u-band (Euclid provided)',
`kids_u` float COMMENT 'Interpolated observed flux in KIDS u-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST g-band (Euclid provided)',
`lsst_g` float COMMENT 'Interpolated observed flux in LSST g-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST g-band (Euclid provided)',
`lsst_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST i-band (Euclid provided)',
`lsst_i` float COMMENT 'Interpolated observed flux in LSST i-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST i-band (Euclid provided)',
`lsst_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST r-band (Euclid provided)',
`lsst_r` float COMMENT 'Interpolated observed flux in LSST r-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST r-band (Euclid provided)',
`lsst_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST u-band (Euclid provided)',
`lsst_u` float COMMENT 'Interpolated observed flux in LSST u-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST u-band (Euclid provided)',
`lsst_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST y-band (Euclid provided)',
`lsst_y` float COMMENT 'Interpolated observed flux in LSST y-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST y-band (Euclid provided)',
`lsst_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST z-band (Euclid provided)',
`lsst_z` float COMMENT 'Interpolated observed flux in LSST z-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST z-band (Euclid provided)',
`cfht_megacam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'Interpolated observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT u-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'Interpolated observed flux in CFHT u-band (Euclid provided)',
`cfht_megacam_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT u-band (Euclid provided)',
`pan_starrs_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'Interpolated observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'Interpolated observed flux in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS z-band (Euclid provided)',
`2mass_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS H-band (Euclid provided)',
`2mass_h` float COMMENT 'Interpolated observed flux in 2MASS H-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j` float COMMENT 'Interpolated observed flux in 2MASS J-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks` float COMMENT 'Interpolated observed flux in 2MASS Ks-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS Ks-band (Euclid provided)',
`sdss_r01_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01` float COMMENT 'Interpolated observed flux in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`gaia_bp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp` float COMMENT 'Interpolated observed flux in Gaia-BP band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-G band (Euclid provided)',
`gaia_g` float COMMENT 'Interpolated observed flux in Gaia-G band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp` float COMMENT 'Interpolated observed flux in Gaia-RP band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-RP band (Euclid provided)',
`euclid_nisp_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'Interpolated observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'Interpolated observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'Interpolated observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis` float COMMENT 'Interpolated observed flux in Euclid VIS band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid VIS band (Euclid provided)',
`step` smallint COMMENT 'redshift step number'
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
SET hive.auto.convert.join.noconditionaltask.size=1000;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_5_highz_s SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, mag_pos.ra_mag, mag_pos.dec_mag, kappa, gamma1, gamma2, defl1, defl2, `l`, `b`, `mw_extinction`, `abs_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `subaru_hsc_z_true`, `subaru_hsc_z`, `subaru_hsc_z_odonnell_ext`, `jst_jpcam_g_true`, `jst_jpcam_g`, `jst_jpcam_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfht_megacam_r_true`, `cfht_megacam_r`, `cfht_megacam_r_odonnell_ext`, `cfht_megacam_u_true`, `cfht_megacam_u`, `cfht_megacam_u_odonnell_ext`, `pan_starrs_i_true`, `pan_starrs_i`, `pan_starrs_i_odonnell_ext`, `pan_starrs_z_true`, `pan_starrs_z`, `pan_starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, `step` FROM (SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, udf.magnified_positions(CAST(ra AS DOUBLE), CAST(`dec` AS DOUBLE), CAST(l.defl1 AS DOUBLE), CAST(l.defl2 AS DOUBLE)) as mag_pos, l.kappa, l.gamma1, l.gamma2, l.defl1, l.defl2, `l`, `b`, `mw_extinction`, `abs_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, l.hpix_13_nest, `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `subaru_hsc_z_true`, `subaru_hsc_z`, `subaru_hsc_z_odonnell_ext`, `jst_jpcam_g_true`, `jst_jpcam_g`, `jst_jpcam_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfht_megacam_r_true`, `cfht_megacam_r`, `cfht_megacam_r_odonnell_ext`, `cfht_megacam_u_true`, `cfht_megacam_u`, `cfht_megacam_u_odonnell_ext`, `pan_starrs_i_true`, `pan_starrs_i`, `pan_starrs_i_odonnell_ext`, `pan_starrs_z_true`, `pan_starrs_z`, `pan_starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, l.step FROM cosmohub.flagship_mock_1_10_5_highz_c as m JOIN cosmohub.flagship_lensing2 as l ON m.step = l.step AND m.hpix_13_nest = l.hpix_13_nest) as t ;
ANALYZE TABLE cosmohub.flagship_mock_1_10_5_highz_s COMPUTE STATISTICS FOR COLUMNS;
Information in CosmoHub:
This is the first SC8 High-z galaxies release.
We have selected galaxies from [1](https://cosmohub.pic.es/catalogs/209) (copied information below) and located randomly in SC8 area.
We have assigned lensing parameters from last redshift step (86) since lensing and deflection maps are not yet available for higher redshift.
Therefore we have used those parameters to estimate the magnified positions and also magnified fluxes in the TU FITS files.
Note that **we provide fluxes instead of magnitudes**. In order **to get magnitudes** you can enter in the "Expert mode" and estimate the magnitude as follows:
m = -2.5 * log10(flux) - 48.6
And in order to estimate magnified magnitudes:
m_mag = m + 2.5 * log10((1-kappa)*(1-kappa) - gamma1 * gamma1 - gamma2 * gamma2)
---
- The catalog of LBG sources is based on the "Recipes" described in this [Euclid Redmine entry](https://euclid.roe.ac.uk/projects/puswg/wiki/Galaxies)**.
Important features to be noted:
1) The corresponding area is 16 deg2
2) Magnitude cut at H=27 (AB mag)
3) SEDs are provided with given UV continuum slopes
4) A standard cosmology with h=0.7, Om=0.3, Olambda=0.7 was used to convert kpc to arcsec and abs_muv (absolute magnitude at 1500 angstroms) to muv (observed magnitude at rest-frame 1500)
Fits file example in CosmoHub:
{
"sql": "SELECT CAST(unique_id AS bigint) AS SOURCE_ID, \nCAST(-1 AS bigint) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra AS double) AS RA, \nCAST(`dec` AS double) AS `DEC`, \nCAST(ra_mag AS double) AS RA_MAG, \nCAST(dec_mag AS double) AS DEC_MAG, \nCAST(z AS float) AS Z_OBS, \nCAST(abs_muv AS float) AS REF_MAG_ABS, \nCAST(abs_muv AS float) AS REF_MAG, \nCAST(1 AS float) AS BULGE_FRACTION, \nCAST(rhalf_arcsec AS float) AS BULGE_R50, \nCAST(rhalf_arcsec AS float) AS DISK_R50, \nCAST(1.5 AS float) AS BULGE_NSERSIC, \nCAST(-1 AS float) AS BULGE_AXIS_RATIO, \nCAST(-1 AS float) AS INCLINATION_ANGLE, \nCAST(-1 AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sedname_int AS float) AS SED_TEMPLATE, \nCAST(0 AS smallint) AS EXT_LAW, \nCAST(-1 AS float) AS EBV, \nCAST(-1 AS float) AS HALPHA_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS HBETA_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS O2_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS O3_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS N2_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS S2_LOGFLAM_EXT_MAG, \nCAST(mw_extinction AS float) AS AV,\nCAST(euclid_vis*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, \nCAST(euclid_nisp_y*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, \nCAST(euclid_nisp_j*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, \nCAST(euclid_nisp_h*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, \nCAST(blanco_decam_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, \nCAST(blanco_decam_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, \nCAST(blanco_decam_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, \nCAST(blanco_decam_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, \nCAST(cfht_megacam_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, \nCAST(cfht_megacam_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, \nCAST(jst_jpcam_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, \nCAST(pan_starrs_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, \nCAST(pan_starrs_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, \nCAST(subaru_hsc_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, \nCAST(gaia_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, \nCAST(gaia_bp*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, \nCAST(gaia_rp*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, \nCAST(lsst_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, \nCAST(lsst_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, \nCAST(lsst_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, \nCAST(lsst_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, \nCAST(lsst_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, \nCAST(lsst_y*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, \nCAST(kids_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, \nCAST(kids_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, \nCAST(kids_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, \nCAST(kids_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, \nCAST(2mass_j*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, \nCAST(2mass_h*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, \nCAST(2mass_ks*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG \nFROM cosmohub.flagship_mock_1_10_5_highz_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063"
}
I made the following choices:
- HALO_ID = -1 when kind = 2 or 3
- BULGE_FRACTION = 1 and then the disk information is not used!
- BULGE_NSERSIC = 1.5
The rest of the fields that are not included in the catalog but are necessary for the FITS file format = -1; [REF_MAG_ABS, REF_MAG] = (m1450 OR abs_muv) when kind = 2 and 3, respectively.
SELECT `unique_id`, `kind`, `ra`, `dec`, `ra_mag`, `dec_mag`, `kappa`, `gamma1`, `gamma2`, `defl1`, `defl2`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `step` FROM flagship_mock_1_10_5_highz_s LIMIT 2;
Aplicamos los cambios en la pipeline de high-z galaxies (.ipynb) que nos menciona Eric en el email y generamos un nuevo catálogo en formato parquet:
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_7_high_z/0.pq -t jcarrete.flagship_mock_1_10_7_highz_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_7_highz_pq (
`type` STRING,
`z` DOUBLE,
`abs_muv` DOUBLE,
`sedname` STRING,
`sedname_int` BIGINT,
`rhalf_kpc` DOUBLE,
`rhalf_arcsec` DOUBLE,
`muv` DOUBLE,
`beta` DOUBLE,
`r` DOUBLE,
`hpix_9_nest` BIGINT,
`kind` BIGINT,
`step` BIGINT,
`unique_id` BIGINT,
`hpix_29_nest` BIGINT,
`ra` DOUBLE,
`dec` DOUBLE,
`l` DOUBLE,
`b` DOUBLE,
`mw_extinction` DOUBLE,
`blanco_decam_g_true` DOUBLE,
`blanco_decam_g` DOUBLE,
`blanco_decam_g_odonnell_ext` DOUBLE,
`blanco_decam_i_true` DOUBLE,
`blanco_decam_i` DOUBLE,
`blanco_decam_i_odonnell_ext` DOUBLE,
`blanco_decam_r_true` DOUBLE,
`blanco_decam_r` DOUBLE,
`blanco_decam_r_odonnell_ext` DOUBLE,
`blanco_decam_z_true` DOUBLE,
`blanco_decam_z` DOUBLE,
`blanco_decam_z_odonnell_ext` DOUBLE,
`subaru_hsc_z_true` DOUBLE,
`subaru_hsc_z` DOUBLE,
`subaru_hsc_z_odonnell_ext` DOUBLE,
`jst_jpcam_g_true` DOUBLE,
`jst_jpcam_g` DOUBLE,
`jst_jpcam_g_odonnell_ext` DOUBLE,
`kids_g_true` DOUBLE,
`kids_g` DOUBLE,
`kids_g_odonnell_ext` DOUBLE,
`kids_i_true` DOUBLE,
`kids_i` DOUBLE,
`kids_i_odonnell_ext` DOUBLE,
`kids_r_true` DOUBLE,
`kids_r` DOUBLE,
`kids_r_odonnell_ext` DOUBLE,
`kids_u_true` DOUBLE,
`kids_u` DOUBLE,
`kids_u_odonnell_ext` DOUBLE,
`lsst_g_true` DOUBLE,
`lsst_g` DOUBLE,
`lsst_g_odonnell_ext` DOUBLE,
`lsst_i_true` DOUBLE,
`lsst_i` DOUBLE,
`lsst_i_odonnell_ext` DOUBLE,
`lsst_r_true` DOUBLE,
`lsst_r` DOUBLE,
`lsst_r_odonnell_ext` DOUBLE,
`lsst_u_true` DOUBLE,
`lsst_u` DOUBLE,
`lsst_u_odonnell_ext` DOUBLE,
`lsst_y_true` DOUBLE,
`lsst_y` DOUBLE,
`lsst_y_odonnell_ext` DOUBLE,
`lsst_z_true` DOUBLE,
`lsst_z` DOUBLE,
`lsst_z_odonnell_ext` DOUBLE,
`cfht_megacam_r_true` DOUBLE,
`cfht_megacam_r` DOUBLE,
`cfht_megacam_r_odonnell_ext` DOUBLE,
`cfht_megacam_u_true` DOUBLE,
`cfht_megacam_u` DOUBLE,
`cfht_megacam_u_odonnell_ext` DOUBLE,
`pan_starrs_i_true` DOUBLE,
`pan_starrs_i` DOUBLE,
`pan_starrs_i_odonnell_ext` DOUBLE,
`pan_starrs_z_true` DOUBLE,
`pan_starrs_z` DOUBLE,
`pan_starrs_z_odonnell_ext` DOUBLE,
`2mass_h_true` DOUBLE,
`2mass_h` DOUBLE,
`2mass_h_odonnell_ext` DOUBLE,
`2mass_j_true` DOUBLE,
`2mass_j` DOUBLE,
`2mass_j_odonnell_ext` DOUBLE,
`2mass_ks_true` DOUBLE,
`2mass_ks` DOUBLE,
`2mass_ks_odonnell_ext` DOUBLE,
`sdss_r01_true` DOUBLE,
`sdss_r01` DOUBLE,
`sdss_r01_odonnell_ext` DOUBLE,
`gaia_bp_true` DOUBLE,
`gaia_bp` DOUBLE,
`gaia_bp_odonnell_ext` DOUBLE,
`gaia_g_true` DOUBLE,
`gaia_g` DOUBLE,
`gaia_g_odonnell_ext` DOUBLE,
`gaia_rp_true` DOUBLE,
`gaia_rp` DOUBLE,
`gaia_rp_odonnell_ext` DOUBLE,
`euclid_nisp_h_true` DOUBLE,
`euclid_nisp_h` DOUBLE,
`euclid_nisp_h_odonnell_ext` DOUBLE,
`euclid_nisp_j_true` DOUBLE,
`euclid_nisp_j` DOUBLE,
`euclid_nisp_j_odonnell_ext` DOUBLE,
`euclid_nisp_y_true` DOUBLE,
`euclid_nisp_y` DOUBLE,
`euclid_nisp_y_odonnell_ext` DOUBLE,
`euclid_vis_true` DOUBLE,
`euclid_vis` DOUBLE,
`euclid_vis_odonnell_ext` DOUBLE,
`bulge_fraction` DOUBLE,
`bulge_nsersic` DOUBLE,
`bulge_ellipticity` DOUBLE,
`bulge_axis_ratio` DOUBLE,
`disk_angle` DOUBLE,
`id` BIGINT
)
STORED AS PARQUET
LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_7_high_z'
;
Clusterizo:
CREATE TABLE cosmohub.flagship_mock_1_10_7_highz_c (
`unique_id` BIGINT COMMENT 'unique identifier',
`id` INT COMMENT 'ID',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`z` FLOAT,
`ra` FLOAT,
`dec` FLOAT,
`l` FLOAT,
`b` FLOAT,
`mw_extinction` FLOAT,
`abs_muv` FLOAT,
`sedname` STRING,
`sedname_int` BIGINT,
`rhalf_kpc` FLOAT,
`rhalf_arcsec` FLOAT,
`muv` FLOAT,
`beta` FLOAT,
`hpix_9_nest` INT,
`hpix_13_nest` INT,
`hpix_29_nest` BIGINT,
`blanco_decam_g_true` FLOAT,
`blanco_decam_g` FLOAT,
`blanco_decam_g_odonnell_ext` FLOAT,
`blanco_decam_i_true` FLOAT,
`blanco_decam_i` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`blanco_decam_r_true` FLOAT,
`blanco_decam_r` FLOAT,
`blanco_decam_r_odonnell_ext` FLOAT,
`blanco_decam_z_true` FLOAT,
`blanco_decam_z` FLOAT,
`blanco_decam_z_odonnell_ext` FLOAT,
`subaru_hsc_z_true` FLOAT,
`subaru_hsc_z` FLOAT,
`subaru_hsc_z_odonnell_ext` FLOAT,
`jst_jpcam_g_true` FLOAT,
`jst_jpcam_g` FLOAT,
`jst_jpcam_g_odonnell_ext` FLOAT,
`kids_g_true` FLOAT,
`kids_g` FLOAT,
`kids_g_odonnell_ext` FLOAT,
`kids_i_true` FLOAT,
`kids_i` FLOAT,
`kids_i_odonnell_ext` FLOAT,
`kids_r_true` FLOAT,
`kids_r` FLOAT,
`kids_r_odonnell_ext` FLOAT,
`kids_u_true` FLOAT,
`kids_u` FLOAT,
`kids_u_odonnell_ext` FLOAT,
`lsst_g_true` FLOAT,
`lsst_g` FLOAT,
`lsst_g_odonnell_ext` FLOAT,
`lsst_i_true` FLOAT,
`lsst_i` FLOAT,
`lsst_i_odonnell_ext` FLOAT,
`lsst_r_true` FLOAT,
`lsst_r` FLOAT,
`lsst_r_odonnell_ext` FLOAT,
`lsst_u_true` FLOAT,
`lsst_u` FLOAT,
`lsst_u_odonnell_ext` FLOAT,
`lsst_y_true` FLOAT,
`lsst_y` FLOAT,
`lsst_y_odonnell_ext` FLOAT,
`lsst_z_true` FLOAT,
`lsst_z` FLOAT,
`lsst_z_odonnell_ext` FLOAT,
`cfht_megacam_r_true` FLOAT,
`cfht_megacam_r` FLOAT,
`cfht_megacam_r_odonnell_ext` FLOAT,
`cfht_megacam_u_true` FLOAT,
`cfht_megacam_u` FLOAT,
`cfht_megacam_u_odonnell_ext` FLOAT,
`pan_starrs_i_true` FLOAT,
`pan_starrs_i` FLOAT,
`pan_starrs_i_odonnell_ext` FLOAT,
`pan_starrs_z_true` FLOAT,
`pan_starrs_z` FLOAT,
`pan_starrs_z_odonnell_ext` FLOAT,
`2mass_h_true` FLOAT,
`2mass_h` FLOAT,
`2mass_h_odonnell_ext` FLOAT,
`2mass_j_true` FLOAT,
`2mass_j` FLOAT,
`2mass_j_odonnell_ext` FLOAT,
`2mass_ks_true` FLOAT,
`2mass_ks` FLOAT,
`2mass_ks_odonnell_ext` FLOAT,
`sdss_r01_true` FLOAT,
`sdss_r01` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`gaia_bp_true` FLOAT,
`gaia_bp` FLOAT,
`gaia_bp_odonnell_ext` FLOAT,
`gaia_g_true` FLOAT,
`gaia_g` FLOAT,
`gaia_g_odonnell_ext` FLOAT,
`gaia_rp_true` FLOAT,
`gaia_rp` FLOAT,
`gaia_rp_odonnell_ext` FLOAT,
`euclid_nisp_h_true` FLOAT,
`euclid_nisp_h` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`euclid_nisp_j_true` FLOAT,
`euclid_nisp_j` FLOAT,
`euclid_nisp_j_odonnell_ext` FLOAT,
`euclid_nisp_y_true` FLOAT,
`euclid_nisp_y` FLOAT,
`euclid_nisp_y_odonnell_ext` FLOAT,
`euclid_vis_true` FLOAT,
`euclid_vis` FLOAT,
`euclid_vis_odonnell_ext` FLOAT,
`bulge_fraction` FLOAT,
`bulge_nsersic` FLOAT,
`bulge_ellipticity` FLOAT,
`bulge_axis_ratio` FLOAT,
`disk_angle` FLOAT,
`step` smallint COMMENT 'redshift step number'
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_7_highz_c SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, `l`, `b`, `mw_extinction`, `abs_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, SHIFTRIGHT(hpix_29_nest, (29-13)*2), `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `subaru_hsc_z_true`, `subaru_hsc_z`, `subaru_hsc_z_odonnell_ext`, `jst_jpcam_g_true`, `jst_jpcam_g`, `jst_jpcam_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfht_megacam_r_true`, `cfht_megacam_r`, `cfht_megacam_r_odonnell_ext`, `cfht_megacam_u_true`, `cfht_megacam_u`, `cfht_megacam_u_odonnell_ext`, `pan_starrs_i_true`, `pan_starrs_i`, `pan_starrs_i_odonnell_ext`, `pan_starrs_z_true`, `pan_starrs_z`, `pan_starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, `bulge_fraction`, `bulge_nsersic`, `bulge_ellipticity`, `bulge_axis_ratio`, `disk_angle`, `step` FROM jcarrete.flagship_mock_1_10_7_highz_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_7_highz_c COMPUTE STATISTICS FOR COLUMNS;
Añado el lensing:
CREATE TABLE cosmohub.flagship_mock_1_10_7_highz_s (
`unique_id` BIGINT COMMENT 'unique identifier',
`id` INT COMMENT 'ID',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`z` float COMMENT 'true galaxy redshift',
`ra` FLOAT COMMENT 'galaxy right ascension (degrees)',
`dec` FLOAT COMMENT 'galaxy declination (degrees)',
`ra_mag` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l` FLOAT COMMENT 'galactic longitude (degrees)',
`b` FLOAT COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`abs_muv` FLOAT COMMENT 'absolute magnitude at 1500 angstroms',
`sedname` STRING COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
`sedname_int` BIGINT COMMENT 'sedname as integer (from 0 to 75, from 0 to 37 without UV lines, from 38 to 75 with UV lines)',
`rhalf_kpc` FLOAT COMMENT 'half light radius (in physical kpc)',
`rhalf_arcsec` FLOAT COMMENT 'half light radius (in arcsec)',
`muv` FLOAT COMMENT 'for information only, observed magnitude at rest-frame 1500',
`beta` FLOAT COMMENT 'for information only, used to assign an SED',
`hpix_9_nest` int COMMENT 'healpix pixel id using order = 9 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`blanco_decam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'Interpolated observed flux in DES g-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'Interpolated observed flux in DES i-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'Interpolated observed flux in DES r-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'Interpolated observed flux in DES z-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES z-band (Euclid provided)',
`subaru_hsc_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'Interpolated observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`jst_jpcam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in JST g-band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'Interpolated observed flux in JST g-band (Euclid provided)',
`jst_jpcam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in JST g-band (Euclid provided)',
`kids_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS g-band (Euclid provided)',
`kids_g` float COMMENT 'Interpolated observed flux in KIDS g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS g-band (Euclid provided)',
`kids_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS i-band (Euclid provided)',
`kids_i` float COMMENT 'Interpolated observed flux in KIDS i-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS i-band (Euclid provided)',
`kids_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS r-band (Euclid provided)',
`kids_r` float COMMENT 'Interpolated observed flux in KIDS r-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS r-band (Euclid provided)',
`kids_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS u-band (Euclid provided)',
`kids_u` float COMMENT 'Interpolated observed flux in KIDS u-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST g-band (Euclid provided)',
`lsst_g` float COMMENT 'Interpolated observed flux in LSST g-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST g-band (Euclid provided)',
`lsst_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST i-band (Euclid provided)',
`lsst_i` float COMMENT 'Interpolated observed flux in LSST i-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST i-band (Euclid provided)',
`lsst_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST r-band (Euclid provided)',
`lsst_r` float COMMENT 'Interpolated observed flux in LSST r-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST r-band (Euclid provided)',
`lsst_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST u-band (Euclid provided)',
`lsst_u` float COMMENT 'Interpolated observed flux in LSST u-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST u-band (Euclid provided)',
`lsst_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST y-band (Euclid provided)',
`lsst_y` float COMMENT 'Interpolated observed flux in LSST y-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST y-band (Euclid provided)',
`lsst_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST z-band (Euclid provided)',
`lsst_z` float COMMENT 'Interpolated observed flux in LSST z-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST z-band (Euclid provided)',
`cfht_megacam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'Interpolated observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT u-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'Interpolated observed flux in CFHT u-band (Euclid provided)',
`cfht_megacam_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT u-band (Euclid provided)',
`pan_starrs_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'Interpolated observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'Interpolated observed flux in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS z-band (Euclid provided)',
`2mass_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS H-band (Euclid provided)',
`2mass_h` float COMMENT 'Interpolated observed flux in 2MASS H-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j` float COMMENT 'Interpolated observed flux in 2MASS J-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks` float COMMENT 'Interpolated observed flux in 2MASS Ks-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS Ks-band (Euclid provided)',
`sdss_r01_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01` float COMMENT 'Interpolated observed flux in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`gaia_bp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp` float COMMENT 'Interpolated observed flux in Gaia-BP band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-G band (Euclid provided)',
`gaia_g` float COMMENT 'Interpolated observed flux in Gaia-G band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp` float COMMENT 'Interpolated observed flux in Gaia-RP band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-RP band (Euclid provided)',
`euclid_nisp_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'Interpolated observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'Interpolated observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'Interpolated observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis` float COMMENT 'Interpolated observed flux in Euclid VIS band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid VIS band (Euclid provided)',
`bulge_fraction` FLOAT COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)',
`bulge_nsersic` FLOAT COMMENT 'Sersic index of the bulge component',
`bulge_ellipticity` FLOAT COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)',
`bulge_axis_ratio` FLOAT COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`disk_angle` FLOAT COMMENT 'position of the disk rotation axis (degrees). 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',
`step` smallint COMMENT 'redshift step number'
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
SET hive.auto.convert.join.noconditionaltask.size=1000;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_7_highz_s SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, mag_pos.ra_mag, mag_pos.dec_mag, kappa, gamma1, gamma2, defl1, defl2, `l`, `b`, `mw_extinction`, `abs_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `subaru_hsc_z_true`, `subaru_hsc_z`, `subaru_hsc_z_odonnell_ext`, `jst_jpcam_g_true`, `jst_jpcam_g`, `jst_jpcam_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfht_megacam_r_true`, `cfht_megacam_r`, `cfht_megacam_r_odonnell_ext`, `cfht_megacam_u_true`, `cfht_megacam_u`, `cfht_megacam_u_odonnell_ext`, `pan_starrs_i_true`, `pan_starrs_i`, `pan_starrs_i_odonnell_ext`, `pan_starrs_z_true`, `pan_starrs_z`, `pan_starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, `bulge_fraction`, `bulge_nsersic`, `bulge_ellipticity`, `bulge_axis_ratio`, `disk_angle`, `step` FROM (SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, udf.magnified_positions(CAST(ra AS DOUBLE), CAST(`dec` AS DOUBLE), CAST(l.defl1 AS DOUBLE), CAST(l.defl2 AS DOUBLE)) as mag_pos, l.kappa, l.gamma1, l.gamma2, l.defl1, l.defl2, `l`, `b`, `mw_extinction`, `abs_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, l.hpix_13_nest, `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `subaru_hsc_z_true`, `subaru_hsc_z`, `subaru_hsc_z_odonnell_ext`, `jst_jpcam_g_true`, `jst_jpcam_g`, `jst_jpcam_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfht_megacam_r_true`, `cfht_megacam_r`, `cfht_megacam_r_odonnell_ext`, `cfht_megacam_u_true`, `cfht_megacam_u`, `cfht_megacam_u_odonnell_ext`, `pan_starrs_i_true`, `pan_starrs_i`, `pan_starrs_i_odonnell_ext`, `pan_starrs_z_true`, `pan_starrs_z`, `pan_starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, `bulge_fraction`, `bulge_nsersic`, `bulge_ellipticity`, `bulge_axis_ratio`, `disk_angle`, l.step FROM cosmohub.flagship_mock_1_10_7_highz_c as m JOIN cosmohub.flagship_lensing2 as l ON m.step = l.step AND m.hpix_13_nest = l.hpix_13_nest) as t;
ANALYZE TABLE cosmohub.flagship_mock_1_10_7_highz_s COMPUTE STATISTICS FOR COLUMNS;
I regenerate the catalog since we have to include some modifications:
- Tengo que generar los flujos de los filtros actualizados
- Cambiar el gamma1 por -gamma1. Esto estaba mal en la versión anterior!!!!
- Calcular el valor observado del filtro de referencia
I rename the fluxes:
cfht_megacam_u ---> cfis_u cfht_megacam_r ---> cfis_r jst_jpcam_g ---> jedis_g pan_starrs_i ---> pan-starrs_i subaru_hsc_z ---> wishes_z -- el pan_starrs_z.csv se cambia a pan-starrs_z.csv
SELECT unique_id, id, kind, z, ra, dec, ra_mag, dec_mag, kappa, gamma1, gamma2, defl1, defl2, l, b, mw_extinction, abs_muv, sedname, sedname_int, rhalf_kpc, rhalf_arcsec, muv, beta, hpix_9_nest, hpix_13_nest, hpix_29_nest, blanco_decam_g_true, blanco_decam_g, blanco_decam_g_odonnell_ext, blanco_decam_i_true, blanco_decam_i, blanco_decam_i_odonnell_ext, blanco_decam_r_true, blanco_decam_r, blanco_decam_r_odonnell_ext, blanco_decam_z_true, blanco_decam_z, blanco_decam_z_odonnell_ext, kids_g_true, kids_g, kids_g_odonnell_ext, kids_i_true, kids_i, kids_i_odonnell_ext, kids_r_true, kids_r, kids_r_odonnell_ext, kids_u_true, kids_u, kids_u_odonnell_ext, lsst_g_true, lsst_g, lsst_g_odonnell_ext, lsst_i_true, lsst_i, lsst_i_odonnell_ext, lsst_r_true, lsst_r, lsst_r_odonnell_ext, lsst_u_true, lsst_u, lsst_u_odonnell_ext, lsst_y_true, lsst_y, lsst_y_odonnell_ext, lsst_z_true, lsst_z, lsst_z_odonnell_ext, 2mass_h_true, 2mass_h, 2mass_h_odonnell_ext, 2mass_j_true, 2mass_j, 2mass_j_odonnell_ext, 2mass_ks_true, 2mass_ks, 2mass_ks_odonnell_ext, sdss_r01_true, sdss_r01, sdss_r01_odonnell_ext, gaia_bp_true, gaia_bp, gaia_bp_odonnell_ext, gaia_g_true, gaia_g, gaia_g_odonnell_ext, gaia_rp_true, gaia_rp, gaia_rp_odonnell_ext, euclid_nisp_h_true, euclid_nisp_h, euclid_nisp_h_odonnell_ext, euclid_nisp_j_true, euclid_nisp_j, euclid_nisp_j_odonnell_ext, euclid_nisp_y_true, euclid_nisp_y, euclid_nisp_y_odonnell_ext, euclid_vis_true, euclid_vis, euclid_vis_odonnell_ext, bulge_fraction, bulge_nsersic, bulge_ellipticity, bulge_axis_ratio, disk_angle, step FROM cosmohub.flagship_mock_1_10_7_highz_s
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_10_high_z/0.pq -t jcarrete.flagship_mock_1_10_10_high_z_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_10_high_z_pq ( `id` BIGINT, `kind` BIGINT, `z` DOUBLE, `ra` DOUBLE, `dec` DOUBLE, `ra_mag` DOUBLE, `dec_mag` DOUBLE, `kappa` DOUBLE, `gamma1` DOUBLE, `gamma2` DOUBLE, `defl1` DOUBLE, `defl2` DOUBLE, `l` DOUBLE, `b` DOUBLE, `mw_extinction` DOUBLE, `abs_muv` DOUBLE, `sedname` STRING, `sedname_int` BIGINT, `rhalf_kpc` DOUBLE, `rhalf_arcsec` DOUBLE, `muv` DOUBLE, `beta` DOUBLE, `hpix_9_nest` BIGINT, `hpix_13_nest` BIGINT, `hpix_29_nest` BIGINT, `blanco_decam_g_true` DOUBLE, `blanco_decam_g` DOUBLE, `blanco_decam_g_odonnell_ext` DOUBLE, `blanco_decam_i_true` DOUBLE, `blanco_decam_i` DOUBLE, `blanco_decam_i_odonnell_ext` DOUBLE, `blanco_decam_r_true` DOUBLE, `blanco_decam_r` DOUBLE, `blanco_decam_r_odonnell_ext` DOUBLE, `blanco_decam_z_true` DOUBLE, `blanco_decam_z` DOUBLE, `blanco_decam_z_odonnell_ext` DOUBLE, `kids_g_true` DOUBLE, `kids_g` DOUBLE, `kids_g_odonnell_ext` DOUBLE, `kids_i_true` DOUBLE, `kids_i` DOUBLE, `kids_i_odonnell_ext` DOUBLE, `kids_r_true` DOUBLE, `kids_r` DOUBLE, `kids_r_odonnell_ext` DOUBLE, `kids_u_true` DOUBLE, `kids_u` DOUBLE, `kids_u_odonnell_ext` DOUBLE, `lsst_g_true` DOUBLE, `lsst_g` DOUBLE, `lsst_g_odonnell_ext` DOUBLE, `lsst_i_true` DOUBLE, `lsst_i` DOUBLE, `lsst_i_odonnell_ext` DOUBLE, `lsst_r_true` DOUBLE, `lsst_r` DOUBLE, `lsst_r_odonnell_ext` DOUBLE, `lsst_u_true` DOUBLE, `lsst_u` DOUBLE, `lsst_u_odonnell_ext` DOUBLE, `lsst_y_true` DOUBLE, `lsst_y` DOUBLE, `lsst_y_odonnell_ext` DOUBLE, `lsst_z_true` DOUBLE, `lsst_z` DOUBLE, `lsst_z_odonnell_ext` DOUBLE, `2mass_h_true` DOUBLE, `2mass_h` DOUBLE, `2mass_h_odonnell_ext` DOUBLE, `2mass_j_true` DOUBLE, `2mass_j` DOUBLE, `2mass_j_odonnell_ext` DOUBLE, `2mass_ks_true` DOUBLE, `2mass_ks` DOUBLE, `2mass_ks_odonnell_ext` DOUBLE, `sdss_r01_true` DOUBLE, `sdss_r01` DOUBLE, `sdss_r01_odonnell_ext` DOUBLE, `gaia_bp_true` DOUBLE, `gaia_bp` DOUBLE, `gaia_bp_odonnell_ext` DOUBLE, `gaia_g_true` DOUBLE, `gaia_g` DOUBLE, `gaia_g_odonnell_ext` DOUBLE, `gaia_rp_true` DOUBLE, `gaia_rp` DOUBLE, `gaia_rp_odonnell_ext` DOUBLE, `euclid_nisp_h_true` DOUBLE, `euclid_nisp_h` DOUBLE, `euclid_nisp_h_odonnell_ext` DOUBLE, `euclid_nisp_j_true` DOUBLE, `euclid_nisp_j` DOUBLE, `euclid_nisp_j_odonnell_ext` DOUBLE, `euclid_nisp_y_true` DOUBLE, `euclid_nisp_y` DOUBLE, `euclid_nisp_y_odonnell_ext` DOUBLE, `euclid_vis_true` DOUBLE, `euclid_vis` DOUBLE, `euclid_vis_odonnell_ext` DOUBLE, `bulge_fraction` DOUBLE, `bulge_nsersic` DOUBLE, `bulge_ellipticity` DOUBLE, `bulge_axis_ratio` DOUBLE, `disk_angle` DOUBLE, `step` BIGINT, `lum_dist` DOUBLE, `app_muv` DOUBLE, `wishes_z_true` DOUBLE, `wishes_z` DOUBLE, `wishes_z_odonnell_ext` DOUBLE, `jedis_g_true` DOUBLE, `jedis_g` DOUBLE, `jedis_g_odonnell_ext` DOUBLE, `cfis_r_true` DOUBLE, `cfis_r` DOUBLE, `cfis_r_odonnell_ext` DOUBLE, `cfis_u_true` DOUBLE, `cfis_u` DOUBLE, `cfis_u_odonnell_ext` DOUBLE, `pan-starrs_i_true` DOUBLE, `pan-starrs_i` DOUBLE, `pan-starrs_i_odonnell_ext` DOUBLE, `pan-starrs_z_true` DOUBLE, `pan-starrs_z` DOUBLE, `pan-starrs_z_odonnell_ext` DOUBLE, `unique_id` BIGINT ) STORED AS PARQUET LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_10_high_z' ;
CREATE TABLE cosmohub.flagship_mock_1_10_10_highz_s (
`unique_id` BIGINT COMMENT 'unique identifier',
`id` INT COMMENT 'ID',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`z` float COMMENT 'true galaxy redshift',
`ra` FLOAT COMMENT 'galaxy right ascension (degrees)',
`dec` FLOAT COMMENT 'galaxy declination (degrees)',
`ra_mag` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l` FLOAT COMMENT 'galactic longitude (degrees)',
`b` FLOAT COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`abs_muv` FLOAT COMMENT 'absolute magnitude at 1500 angstroms',
`lum_dist` FLOAT COMMENT 'luminosity distance in pc',
`app_muv` FLOAT COMMENT 'apparent magnitude at 1450 angstroms (M1450)',
`sedname` STRING COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
`sedname_int` BIGINT COMMENT 'sedname as integer (from 0 to 75, from 0 to 37 without UV lines, from 38 to 75 with UV lines)',
`rhalf_kpc` FLOAT COMMENT 'half light radius (in physical kpc)',
`rhalf_arcsec` FLOAT COMMENT 'half light radius (in arcsec)',
`muv` FLOAT COMMENT 'for information only, observed magnitude at rest-frame 1500',
`beta` FLOAT COMMENT 'for information only, used to assign an SED',
`hpix_9_nest` int COMMENT 'healpix pixel id using order = 9 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`blanco_decam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'Interpolated observed flux in DES g-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'Interpolated observed flux in DES i-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'Interpolated observed flux in DES r-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'Interpolated observed flux in DES z-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES z-band (Euclid provided)',
`wishes_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`wishes_z` float COMMENT 'Interpolated observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`wishes_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`jedis_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in JST g-band (Euclid provided)',
`jedis_g` float COMMENT 'Interpolated observed flux in JST g-band (Euclid provided)',
`jedis_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in JST g-band (Euclid provided)',
`kids_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS g-band (Euclid provided)',
`kids_g` float COMMENT 'Interpolated observed flux in KIDS g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS g-band (Euclid provided)',
`kids_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS i-band (Euclid provided)',
`kids_i` float COMMENT 'Interpolated observed flux in KIDS i-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS i-band (Euclid provided)',
`kids_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS r-band (Euclid provided)',
`kids_r` float COMMENT 'Interpolated observed flux in KIDS r-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS r-band (Euclid provided)',
`kids_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS u-band (Euclid provided)',
`kids_u` float COMMENT 'Interpolated observed flux in KIDS u-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST g-band (Euclid provided)',
`lsst_g` float COMMENT 'Interpolated observed flux in LSST g-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST g-band (Euclid provided)',
`lsst_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST i-band (Euclid provided)',
`lsst_i` float COMMENT 'Interpolated observed flux in LSST i-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST i-band (Euclid provided)',
`lsst_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST r-band (Euclid provided)',
`lsst_r` float COMMENT 'Interpolated observed flux in LSST r-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST r-band (Euclid provided)',
`lsst_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST u-band (Euclid provided)',
`lsst_u` float COMMENT 'Interpolated observed flux in LSST u-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST u-band (Euclid provided)',
`lsst_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST y-band (Euclid provided)',
`lsst_y` float COMMENT 'Interpolated observed flux in LSST y-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST y-band (Euclid provided)',
`lsst_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST z-band (Euclid provided)',
`lsst_z` float COMMENT 'Interpolated observed flux in LSST z-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST z-band (Euclid provided)',
`cfis_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT r-band (Euclid provided)',
`cfis_r` float COMMENT 'Interpolated observed flux in CFHT r-band (Euclid provided)',
`cfis_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT r-band (Euclid provided)',
`cfis_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT u-band (Euclid provided)',
`cfis_u` float COMMENT 'Interpolated observed flux in CFHT u-band (Euclid provided)',
`cfis_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT u-band (Euclid provided)',
`pan-starrs_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan-starrs_i` float COMMENT 'Interpolated observed flux in Pan-STARRS i-band (Euclid provided)',
`pan-starrs_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan-starrs_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan-starrs_z` float COMMENT 'Interpolated observed flux in Pan-STARRS z-band (Euclid provided)',
`pan-starrs_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS z-band (Euclid provided)',
`2mass_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS H-band (Euclid provided)',
`2mass_h` float COMMENT 'Interpolated observed flux in 2MASS H-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j` float COMMENT 'Interpolated observed flux in 2MASS J-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks` float COMMENT 'Interpolated observed flux in 2MASS Ks-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS Ks-band (Euclid provided)',
`sdss_r01_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01` float COMMENT 'Interpolated observed flux in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`gaia_bp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp` float COMMENT 'Interpolated observed flux in Gaia-BP band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-G band (Euclid provided)',
`gaia_g` float COMMENT 'Interpolated observed flux in Gaia-G band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp` float COMMENT 'Interpolated observed flux in Gaia-RP band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-RP band (Euclid provided)',
`euclid_nisp_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'Interpolated observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'Interpolated observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'Interpolated observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis` float COMMENT 'Interpolated observed flux in Euclid VIS band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid VIS band (Euclid provided)',
`bulge_fraction` FLOAT COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)',
`bulge_nsersic` FLOAT COMMENT 'Sersic index of the bulge component',
`bulge_ellipticity` FLOAT COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)',
`bulge_axis_ratio` FLOAT COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`disk_angle` FLOAT COMMENT 'position of the disk rotation axis (degrees). 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',
`step` smallint COMMENT 'redshift step number'
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_10_highz_s SELECT `unique_id`, `id`, `kind`, `z`, `ra`, `dec`, `ra_mag`, `dec_mag`, `kappa`, `gamma1`, `gamma2`, `defl1`, `defl2`, `l`, `b`, `mw_extinction`, `abs_muv`, `lum_dist`, `app_muv`, `sedname`, `sedname_int`, `rhalf_kpc`, `rhalf_arcsec`, `muv`, `beta`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `blanco_decam_g_true`, `blanco_decam_g`, `blanco_decam_g_odonnell_ext`, `blanco_decam_i_true`, `blanco_decam_i`, `blanco_decam_i_odonnell_ext`, `blanco_decam_r_true`, `blanco_decam_r`, `blanco_decam_r_odonnell_ext`, `blanco_decam_z_true`, `blanco_decam_z`, `blanco_decam_z_odonnell_ext`, `wishes_z_true`, `wishes_z`, `wishes_z_odonnell_ext`, `jedis_g_true`, `jedis_g`, `jedis_g_odonnell_ext`, `kids_g_true`, `kids_g`, `kids_g_odonnell_ext`, `kids_i_true`, `kids_i`, `kids_i_odonnell_ext`, `kids_r_true`, `kids_r`, `kids_r_odonnell_ext`, `kids_u_true`, `kids_u`, `kids_u_odonnell_ext`, `lsst_g_true`, `lsst_g`, `lsst_g_odonnell_ext`, `lsst_i_true`, `lsst_i`, `lsst_i_odonnell_ext`, `lsst_r_true`, `lsst_r`, `lsst_r_odonnell_ext`, `lsst_u_true`, `lsst_u`, `lsst_u_odonnell_ext`, `lsst_y_true`, `lsst_y`, `lsst_y_odonnell_ext`, `lsst_z_true`, `lsst_z`, `lsst_z_odonnell_ext`, `cfis_r_true`, `cfis_r`, `cfis_r_odonnell_ext`, `cfis_u_true`, `cfis_u`, `cfis_u_odonnell_ext`, `pan-starrs_i_true`, `pan-starrs_i`, `pan-starrs_i_odonnell_ext`, `pan-starrs_z_true`, `pan-starrs_z`, `pan-starrs_z_odonnell_ext`, `2mass_h_true`, `2mass_h`, `2mass_h_odonnell_ext`, `2mass_j_true`, `2mass_j`, `2mass_j_odonnell_ext`, `2mass_ks_true`, `2mass_ks`, `2mass_ks_odonnell_ext`, `sdss_r01_true`, `sdss_r01`, `sdss_r01_odonnell_ext`, `gaia_bp_true`, `gaia_bp`, `gaia_bp_odonnell_ext`, `gaia_g_true`, `gaia_g`, `gaia_g_odonnell_ext`, `gaia_rp_true`, `gaia_rp`, `gaia_rp_odonnell_ext`, `euclid_nisp_h_true`, `euclid_nisp_h`, `euclid_nisp_h_odonnell_ext`, `euclid_nisp_j_true`, `euclid_nisp_j`, `euclid_nisp_j_odonnell_ext`, `euclid_nisp_y_true`, `euclid_nisp_y`, `euclid_nisp_y_odonnell_ext`, `euclid_vis_true`, `euclid_vis`, `euclid_vis_odonnell_ext`, `bulge_fraction`, `bulge_nsersic`, `bulge_ellipticity`, `bulge_axis_ratio`, `disk_angle`, `step` FROM jcarrete.flagship_mock_1_10_10_high_z_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_10_highz_s COMPUTE STATISTICS FOR COLUMNS;
## This is the first SC8 High-z galaxies release. Changes from previous version (1.10.5): * gamma1 flip sign is included in this version (missing in previous one; due to system reference transformation from IAU and Healpix) * update some filter transmissions * include REF_MAG We have selected galaxies from [2](https://cosmohub.pic.es/catalogs/209) (copied information below) and located randomly in SC8 area. The total simulated area is 1297 square degrees. We have assigned lensing parameters from last redshift step (86) since lensing and deflection maps are not yet available for higher redshift. Therefore we have used those parameters to estimate the magnified positions and also magnified fluxes in the TU FITS files. Note that **we provide fluxes instead of magnitudes**. In order **to get magnitudes** you can enter in the "Expert mode" and estimate the magnitude as follows: m = -2.5 * log10(flux) - 48.6 And in order to estimate magnified magnitudes: m_mag = m + 2.5 * log10((1-kappa)*(1-kappa) - gamma1 * gamma1 - gamma2 * gamma2) --- **The catalog of LBG sources is based on the "Recipes" described in this [Euclid Redmine entry](https://euclid.roe.ac.uk/projects/puswg/wiki/Galaxies)**. Important features to be noted: 1) The corresponding area is 16 deg2 2) Magnitude cut at H=27 (AB mag) 3) SEDs are provided with given UV continuum slopes 4) A standard cosmology with h=0.7, Om=0.3, Olambda=0.7 was used to convert kpc to arcsec and abs_muv (absolute magnitude at 1500 angstroms) to muv (observed magnitude at rest-frame 1500)
FITS file in CosmoHub:
{
"sql": "SELECT CAST(unique_id AS bigint) AS `SOURCE_ID`, \nCAST(-1 AS bigint) AS `HALO_ID`, \nCAST(kind AS smallint) AS `KIND`, \nCAST(ra AS double) AS `RA`, \nCAST(`dec` AS double) AS `DEC`, \nCAST(ra_mag AS double) AS `RA_MAG`, \nCAST(dec_mag AS double) AS `DEC_MAG`, \nCAST(z AS float) AS `Z_OBS`, \nCAST(abs_muv AS float) AS `REF_MAG_ABS`, \nCAST(app_muv AS float) AS `REF_MAG`, \nCAST(bulge_fraction AS float) AS `BULGE_FRACTION`, \nCAST(rhalf_arcsec AS float) AS `BULGE_R50`, \nCAST(rhalf_arcsec AS float) AS `DISK_R50`, \nCAST(bulge_nsersic AS float) AS `BULGE_NSERSIC`, \nCAST(bulge_axis_ratio AS float) AS `BULGE_AXIS_RATIO`, \nCAST(-1 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(sedname_int AS float) AS `SED_TEMPLATE`, \nCAST(0 AS smallint) AS `EXT_LAW`, \nCAST(-1 AS float) AS `EBV`, \nCAST(-1 AS float) AS `HALPHA_LOGFLAM_EXT_MAG`, \nCAST(-1 AS float) AS `HBETA_LOGFLAM_EXT_MAG`, \nCAST(-1 AS float) AS `O2_LOGFLAM_EXT_MAG`, \nCAST(-1 AS float) AS `O3_LOGFLAM_EXT_MAG`, \nCAST(-1 AS float) AS `N2_LOGFLAM_EXT_MAG`, \nCAST(-1 AS float) AS `S2_LOGFLAM_EXT_MAG`, \nCAST(mw_extinction AS float) AS `AV`,\nCAST(euclid_vis*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_VIS_MAG`, \nCAST(euclid_nisp_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_NISP_MAG`, \nCAST(euclid_nisp_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_NISP_MAG`, \nCAST(euclid_nisp_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_NISP_MAG`, \nCAST(blanco_decam_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_DECAM_MAG`, \nCAST(blanco_decam_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_DECAM_MAG`, \nCAST(blanco_decam_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_DECAM_MAG`, \nCAST(blanco_decam_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_DECAM_MAG`, \nCAST(cfis_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_MEGACAM_MAG`, \nCAST(cfis_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_MEGACAM_MAG`, \nCAST(jedis_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_JPCAM_MAG`, \nCAST(`pan-starrs_i`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_PANSTARRS_MAG`, \nCAST(`pan-starrs_z`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_PANSTARRS_MAG`, \nCAST(wishes_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_HSC_MAG`, \nCAST(gaia_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_GAIA_MAG`, \nCAST(gaia_bp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_BP_GAIA_MAG`, \nCAST(gaia_rp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_RP_GAIA_MAG`, \nCAST(lsst_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_LSST_MAG`, \nCAST(lsst_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_LSST_MAG`, \nCAST(lsst_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_LSST_MAG`, \nCAST(lsst_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_LSST_MAG`, \nCAST(lsst_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_LSST_MAG`, \nCAST(lsst_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_LSST_MAG`, \nCAST(kids_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_KIDS_MAG`, \nCAST(kids_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_KIDS_MAG`, \nCAST(kids_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_KIDS_MAG`, \nCAST(kids_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_KIDS_MAG`, \nCAST(2mass_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_2MASS_MAG`, \nCAST(2mass_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_2MASS_MAG`, \nCAST(2mass_ks*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_KS_2MASS_MAG` \nFROM cosmohub.flagship_mock_1_10_10_highz_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063"
}
FITS table:
CREATE TABLE jcarrete.flagship_mock_1_10_10_highz_s_magnified_fits(
SOURCE_ID bigint COMMENT 'from deserializer',
HALO_ID bigint COMMENT 'from deserializer',
KIND smallint COMMENT 'from deserializer',
RA double COMMENT 'from deserializer',
`DEC` double COMMENT 'from deserializer',
RA_MAG double COMMENT 'from deserializer',
DEC_MAG double 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',
BULGE_AXIS_RATIO 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_MAG float COMMENT 'from deserializer',
HBETA_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
O2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
O3_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
N2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
S2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
AV FLOAT COMMENT 'from deserializer',
TU_FNU_VIS_MAG float COMMENT 'from deserializer',
TU_FNU_Y_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_J_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_H_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_G_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_R_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_I_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_Z_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_U_MEGACAM_MAG float COMMENT 'from deserializer',
TU_FNU_R_MEGACAM_MAG float COMMENT 'from deserializer',
TU_FNU_G_JPCAM_MAG float COMMENT 'from deserializer',
TU_FNU_I_PANSTARRS_MAG float COMMENT 'from deserializer',
TU_FNU_Z_PANSTARRS_MAG float COMMENT 'from deserializer',
TU_FNU_Z_HSC_MAG float COMMENT 'from deserializer',
TU_FNU_G_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_BP_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_RP_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_U_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_G_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_R_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_I_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_Z_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_Y_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_U_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_G_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_R_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_I_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_J_2MASS_MAG float COMMENT 'from deserializer',
TU_FNU_H_2MASS_MAG float COMMENT 'from deserializer',
TU_FNU_KS_2MASS_MAG float COMMENT 'from deserializer'
)
PARTITIONED BY (
`hpix_5_nest` int)
ROW FORMAT SERDE
'es.pic.astro.hadoop.serde.RecArraySerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'es.pic.astro.hadoop.io.BinaryOutputFormat'
;
INSERT OVERWRITE TABLE jcarrete.flagship_mock_1_10_10_highz_s_magnified_fits SELECT CAST(unique_id AS bigint) AS `SOURCE_ID`, CAST(-1 AS bigint) AS `HALO_ID`, CAST(kind AS smallint) AS `KIND`, CAST(ra AS double) AS `RA`, CAST(`dec` AS double) AS `DEC`, CAST(ra_mag AS double) AS `RA_MAG`, CAST(dec_mag AS double) AS `DEC_MAG`, CAST(z AS float) AS `Z_OBS`, CAST(abs_muv AS float) AS `REF_MAG_ABS`, CAST(app_muv AS float) AS `REF_MAG`, CAST(bulge_fraction AS float) AS `BULGE_FRACTION`, CAST(rhalf_arcsec AS float) AS `BULGE_R50`, CAST(rhalf_arcsec AS float) AS `DISK_R50`, CAST(bulge_nsersic AS float) AS `BULGE_NSERSIC`, CAST(bulge_axis_ratio AS float) AS `BULGE_AXIS_RATIO`, CAST(-1 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(sedname_int AS float) AS `SED_TEMPLATE`, CAST(0 AS smallint) AS `EXT_LAW`, CAST(-1 AS float) AS `EBV`, CAST(-1 AS float) AS `HALPHA_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `HBETA_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `O2_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `O3_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `N2_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `S2_LOGFLAM_EXT_MAG`, CAST(mw_extinction AS float) AS `AV`, CAST(euclid_vis*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_VIS_MAG`, CAST(euclid_nisp_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_NISP_MAG`, CAST(euclid_nisp_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_NISP_MAG`, CAST(euclid_nisp_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_NISP_MAG`, CAST(blanco_decam_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_DECAM_MAG`, CAST(blanco_decam_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_DECAM_MAG`, CAST(blanco_decam_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_DECAM_MAG`, CAST(blanco_decam_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_DECAM_MAG`, CAST(cfis_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_MEGACAM_MAG`, CAST(cfis_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_MEGACAM_MAG`, CAST(jedis_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_JPCAM_MAG`, CAST(`pan-starrs_i`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_PANSTARRS_MAG`, CAST(`pan-starrs_z`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_PANSTARRS_MAG`, CAST(wishes_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_HSC_MAG`, CAST(gaia_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_GAIA_MAG`, CAST(gaia_bp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_BP_GAIA_MAG`, CAST(gaia_rp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_RP_GAIA_MAG`, CAST(lsst_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_LSST_MAG`, CAST(lsst_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_LSST_MAG`, CAST(lsst_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_LSST_MAG`, CAST(lsst_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_LSST_MAG`, CAST(lsst_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_LSST_MAG`, CAST(lsst_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_LSST_MAG`, CAST(kids_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_KIDS_MAG`, CAST(kids_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_KIDS_MAG`, CAST(kids_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_KIDS_MAG`, CAST(kids_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_KIDS_MAG`, CAST(2mass_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_2MASS_MAG`, CAST(2mass_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_2MASS_MAG`, CAST(2mass_ks*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_KS_2MASS_MAG`, CAST(SHIFTRIGHT(hpix_29_nest, (29-5)*2) as int) AS hpix_5_nest FROM cosmohub.flagship_mock_1_10_10_highz_s ;
Tenemos un problema con los flujos y volvemos a recalcularlos con la pipeline. Modifico las cosas necesarias. Básicamente lo que hacemos es coger el catálogo cosmohub.flagship_mock_1_10_10_highz_s, excepto los flujos, quitar los steps en la pipeline (high_z_galaxies_pipeline.py) que no necesito (ref_mag y gamma1 flip) y volverla a correr.
Y lo más importante: cuando selecciono el catálogo, calculo abs_muv - 5 * log10(h), para no tener que modificar la cosmología e incluso para no tener que cambiar el código en sí. Normalmente nosotros siempre damos todo en función de h!
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_13_high_z/0.pq -t jcarrete.flagship_mock_1_10_13_high_z_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_13_high_z_pq ( `id` BIGINT, `kind` BIGINT, `z` DOUBLE, `ra` DOUBLE, `dec` DOUBLE, `ra_mag` DOUBLE, `dec_mag` DOUBLE, `kappa` DOUBLE, `gamma1` DOUBLE, `gamma2` DOUBLE, `defl1` DOUBLE, `defl2` DOUBLE, `l` DOUBLE, `b` DOUBLE, `mw_extinction` DOUBLE, `abs_muv` DOUBLE, `lum_dist` DOUBLE, `app_muv` DOUBLE, `sedname` STRING, `sedname_int` BIGINT, `rhalf_kpc` DOUBLE, `rhalf_arcsec` DOUBLE, `muv` DOUBLE, `beta` DOUBLE, `hpix_9_nest` BIGINT, `hpix_13_nest` BIGINT, `hpix_29_nest` BIGINT, `bulge_fraction` DOUBLE, `bulge_nsersic` DOUBLE, `bulge_ellipticity` DOUBLE, `bulge_axis_ratio` DOUBLE, `disk_angle` DOUBLE, `step` BIGINT, `blanco_decam_g_true` DOUBLE, `blanco_decam_g` DOUBLE, `blanco_decam_g_odonnell_ext` DOUBLE, `blanco_decam_i_true` DOUBLE, `blanco_decam_i` DOUBLE, `blanco_decam_i_odonnell_ext` DOUBLE, `blanco_decam_r_true` DOUBLE, `blanco_decam_r` DOUBLE, `blanco_decam_r_odonnell_ext` DOUBLE, `blanco_decam_z_true` DOUBLE, `blanco_decam_z` DOUBLE, `blanco_decam_z_odonnell_ext` DOUBLE, `wishes_z_true` DOUBLE, `wishes_z` DOUBLE, `wishes_z_odonnell_ext` DOUBLE, `jedis_g_true` DOUBLE, `jedis_g` DOUBLE, `jedis_g_odonnell_ext` DOUBLE, `kids_g_true` DOUBLE, `kids_g` DOUBLE, `kids_g_odonnell_ext` DOUBLE, `kids_i_true` DOUBLE, `kids_i` DOUBLE, `kids_i_odonnell_ext` DOUBLE, `kids_r_true` DOUBLE, `kids_r` DOUBLE, `kids_r_odonnell_ext` DOUBLE, `kids_u_true` DOUBLE, `kids_u` DOUBLE, `kids_u_odonnell_ext` DOUBLE, `lsst_g_true` DOUBLE, `lsst_g` DOUBLE, `lsst_g_odonnell_ext` DOUBLE, `lsst_i_true` DOUBLE, `lsst_i` DOUBLE, `lsst_i_odonnell_ext` DOUBLE, `lsst_r_true` DOUBLE, `lsst_r` DOUBLE, `lsst_r_odonnell_ext` DOUBLE, `lsst_u_true` DOUBLE, `lsst_u` DOUBLE, `lsst_u_odonnell_ext` DOUBLE, `lsst_y_true` DOUBLE, `lsst_y` DOUBLE, `lsst_y_odonnell_ext` DOUBLE, `lsst_z_true` DOUBLE, `lsst_z` DOUBLE, `lsst_z_odonnell_ext` DOUBLE, `cfis_r_true` DOUBLE, `cfis_r` DOUBLE, `cfis_r_odonnell_ext` DOUBLE, `cfis_u_true` DOUBLE, `cfis_u` DOUBLE, `cfis_u_odonnell_ext` DOUBLE, `pan-starrs_i_true` DOUBLE, `pan-starrs_i` DOUBLE, `pan-starrs_i_odonnell_ext` DOUBLE, `pan-starrs_z_true` DOUBLE, `pan-starrs_z` DOUBLE, `pan-starrs_z_odonnell_ext` DOUBLE, `2mass_h_true` DOUBLE, `2mass_h` DOUBLE, `2mass_h_odonnell_ext` DOUBLE, `2mass_j_true` DOUBLE, `2mass_j` DOUBLE, `2mass_j_odonnell_ext` DOUBLE, `2mass_ks_true` DOUBLE, `2mass_ks` DOUBLE, `2mass_ks_odonnell_ext` DOUBLE, `sdss_r01_true` DOUBLE, `sdss_r01` DOUBLE, `sdss_r01_odonnell_ext` DOUBLE, `gaia_bp_true` DOUBLE, `gaia_bp` DOUBLE, `gaia_bp_odonnell_ext` DOUBLE, `gaia_g_true` DOUBLE, `gaia_g` DOUBLE, `gaia_g_odonnell_ext` DOUBLE, `gaia_rp_true` DOUBLE, `gaia_rp` DOUBLE, `gaia_rp_odonnell_ext` DOUBLE, `euclid_nisp_h_true` DOUBLE, `euclid_nisp_h` DOUBLE, `euclid_nisp_h_odonnell_ext` DOUBLE, `euclid_nisp_j_true` DOUBLE, `euclid_nisp_j` DOUBLE, `euclid_nisp_j_odonnell_ext` DOUBLE, `euclid_nisp_y_true` DOUBLE, `euclid_nisp_y` DOUBLE, `euclid_nisp_y_odonnell_ext` DOUBLE, `euclid_vis_true` DOUBLE, `euclid_vis` DOUBLE, `euclid_vis_odonnell_ext` DOUBLE, `unique_id` BIGINT ) STORED AS PARQUET LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_13_high_z' ;
CREATE TABLE cosmohub.flagship_mock_1_10_13_highz_s (
`unique_id` BIGINT COMMENT 'unique identifier',
`id` INT COMMENT 'ID',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`z` float COMMENT 'true galaxy redshift',
`ra` FLOAT COMMENT 'galaxy right ascension (degrees)',
`dec` FLOAT COMMENT 'galaxy declination (degrees)',
`ra_mag` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l` FLOAT COMMENT 'galactic longitude (degrees)',
`b` FLOAT COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`abs_muv` FLOAT COMMENT 'absolute magnitude at 1500 angstroms',
`lum_dist` FLOAT COMMENT 'luminosity distance in pc',
`app_muv` FLOAT COMMENT 'apparent magnitude at 1450 angstroms (M1450)',
`sedname` STRING COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
`sedname_int` BIGINT COMMENT 'sedname as integer (from 0 to 75, from 0 to 37 without UV lines, from 38 to 75 with UV lines)',
`rhalf_kpc` FLOAT COMMENT 'half light radius (in physical kpc)',
`rhalf_arcsec` FLOAT COMMENT 'half light radius (in arcsec)',
`muv` FLOAT COMMENT 'for information only, observed magnitude at rest-frame 1500',
`beta` FLOAT COMMENT 'for information only, used to assign an SED',
`hpix_9_nest` int COMMENT 'healpix pixel id using order = 9 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`blanco_decam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'Interpolated observed flux in DES g-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'Interpolated observed flux in DES i-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'Interpolated observed flux in DES r-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'Interpolated observed flux in DES z-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES z-band (Euclid provided)',
`wishes_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`wishes_z` float COMMENT 'Interpolated observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`wishes_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`jedis_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in JST g-band (Euclid provided)',
`jedis_g` float COMMENT 'Interpolated observed flux in JST g-band (Euclid provided)',
`jedis_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in JST g-band (Euclid provided)',
`kids_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS g-band (Euclid provided)',
`kids_g` float COMMENT 'Interpolated observed flux in KIDS g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS g-band (Euclid provided)',
`kids_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS i-band (Euclid provided)',
`kids_i` float COMMENT 'Interpolated observed flux in KIDS i-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS i-band (Euclid provided)',
`kids_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS r-band (Euclid provided)',
`kids_r` float COMMENT 'Interpolated observed flux in KIDS r-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS r-band (Euclid provided)',
`kids_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS u-band (Euclid provided)',
`kids_u` float COMMENT 'Interpolated observed flux in KIDS u-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST g-band (Euclid provided)',
`lsst_g` float COMMENT 'Interpolated observed flux in LSST g-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST g-band (Euclid provided)',
`lsst_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST i-band (Euclid provided)',
`lsst_i` float COMMENT 'Interpolated observed flux in LSST i-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST i-band (Euclid provided)',
`lsst_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST r-band (Euclid provided)',
`lsst_r` float COMMENT 'Interpolated observed flux in LSST r-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST r-band (Euclid provided)',
`lsst_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST u-band (Euclid provided)',
`lsst_u` float COMMENT 'Interpolated observed flux in LSST u-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST u-band (Euclid provided)',
`lsst_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST y-band (Euclid provided)',
`lsst_y` float COMMENT 'Interpolated observed flux in LSST y-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST y-band (Euclid provided)',
`lsst_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST z-band (Euclid provided)',
`lsst_z` float COMMENT 'Interpolated observed flux in LSST z-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST z-band (Euclid provided)',
`cfis_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT r-band (Euclid provided)',
`cfis_r` float COMMENT 'Interpolated observed flux in CFHT r-band (Euclid provided)',
`cfis_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT r-band (Euclid provided)',
`cfis_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT u-band (Euclid provided)',
`cfis_u` float COMMENT 'Interpolated observed flux in CFHT u-band (Euclid provided)',
`cfis_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT u-band (Euclid provided)',
`pan-starrs_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan-starrs_i` float COMMENT 'Interpolated observed flux in Pan-STARRS i-band (Euclid provided)',
`pan-starrs_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan-starrs_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan-starrs_z` float COMMENT 'Interpolated observed flux in Pan-STARRS z-band (Euclid provided)',
`pan-starrs_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS z-band (Euclid provided)',
`2mass_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS H-band (Euclid provided)',
`2mass_h` float COMMENT 'Interpolated observed flux in 2MASS H-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j` float COMMENT 'Interpolated observed flux in 2MASS J-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks` float COMMENT 'Interpolated observed flux in 2MASS Ks-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS Ks-band (Euclid provided)',
`sdss_r01_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01` float COMMENT 'Interpolated observed flux in sdss r-band redshifted to z=0.1 (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)',
`gaia_bp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp` float COMMENT 'Interpolated observed flux in Gaia-BP band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-G band (Euclid provided)',
`gaia_g` float COMMENT 'Interpolated observed flux in Gaia-G band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp` float COMMENT 'Interpolated observed flux in Gaia-RP band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-RP band (Euclid provided)',
`euclid_nisp_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'Interpolated observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'Interpolated observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'Interpolated observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis` float COMMENT 'Interpolated observed flux in Euclid VIS band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid VIS band (Euclid provided)',
`bulge_fraction` FLOAT COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)',
`bulge_nsersic` FLOAT COMMENT 'Sersic index of the bulge component',
`bulge_ellipticity` FLOAT COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)',
`bulge_axis_ratio` FLOAT COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`disk_angle` FLOAT COMMENT 'position of the disk rotation axis (degrees). 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',
`step` smallint COMMENT 'redshift step number'
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_13_highz_s SELECT unique_id, id, kind, z, ra, `dec`, ra_mag, dec_mag, kappa, gamma1, gamma2, defl1, defl2, l, b, mw_extinction, abs_muv, lum_dist, app_muv, sedname, sedname_int, rhalf_kpc, rhalf_arcsec, muv, beta, hpix_9_nest, hpix_13_nest, hpix_29_nest, blanco_decam_g_true, blanco_decam_g, blanco_decam_g_odonnell_ext, blanco_decam_i_true, blanco_decam_i, blanco_decam_i_odonnell_ext, blanco_decam_r_true, blanco_decam_r, blanco_decam_r_odonnell_ext, blanco_decam_z_true, blanco_decam_z, blanco_decam_z_odonnell_ext, wishes_z_true, wishes_z, wishes_z_odonnell_ext, jedis_g_true, jedis_g, jedis_g_odonnell_ext, kids_g_true, kids_g, kids_g_odonnell_ext, kids_i_true, kids_i, kids_i_odonnell_ext, kids_r_true, kids_r, kids_r_odonnell_ext, kids_u_true, kids_u, kids_u_odonnell_ext, lsst_g_true, lsst_g, lsst_g_odonnell_ext, lsst_i_true, lsst_i, lsst_i_odonnell_ext, lsst_r_true, lsst_r, lsst_r_odonnell_ext, lsst_u_true, lsst_u, lsst_u_odonnell_ext, lsst_y_true, lsst_y, lsst_y_odonnell_ext, lsst_z_true, lsst_z, lsst_z_odonnell_ext, cfis_r_true, cfis_r, cfis_r_odonnell_ext, cfis_u_true, cfis_u, cfis_u_odonnell_ext, `pan-starrs_i_true`, `pan-starrs_i`, `pan-starrs_i_odonnell_ext`, `pan-starrs_z_true`, `pan-starrs_z`, `pan-starrs_z_odonnell_ext`, 2mass_h_true, 2mass_h, 2mass_h_odonnell_ext, 2mass_j_true, 2mass_j, 2mass_j_odonnell_ext, 2mass_ks_true, 2mass_ks, 2mass_ks_odonnell_ext, sdss_r01_true, sdss_r01, sdss_r01_odonnell_ext, gaia_bp_true, gaia_bp, gaia_bp_odonnell_ext, gaia_g_true, gaia_g, gaia_g_odonnell_ext, gaia_rp_true, gaia_rp, gaia_rp_odonnell_ext, euclid_nisp_h_true, euclid_nisp_h, euclid_nisp_h_odonnell_ext, euclid_nisp_j_true, euclid_nisp_j, euclid_nisp_j_odonnell_ext, euclid_nisp_y_true, euclid_nisp_y, euclid_nisp_y_odonnell_ext, euclid_vis_true, euclid_vis, euclid_vis_odonnell_ext, bulge_fraction, bulge_nsersic, bulge_ellipticity, bulge_axis_ratio, disk_angle, step FROM jcarrete.flagship_mock_1_10_13_high_z_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_13_highz_s COMPUTE STATISTICS FOR COLUMNS;
CREATE TABLE jcarrete.flagship_mock_1_10_13_highz_s_magnified_fits(
SOURCE_ID bigint COMMENT 'from deserializer',
HALO_ID bigint COMMENT 'from deserializer',
KIND smallint COMMENT 'from deserializer',
RA double COMMENT 'from deserializer',
`DEC` double COMMENT 'from deserializer',
RA_MAG double COMMENT 'from deserializer',
DEC_MAG double 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',
BULGE_AXIS_RATIO 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_MAG float COMMENT 'from deserializer',
HBETA_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
O2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
O3_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
N2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
S2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
AV FLOAT COMMENT 'from deserializer',
TU_FNU_VIS_MAG float COMMENT 'from deserializer',
TU_FNU_Y_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_J_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_H_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_G_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_R_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_I_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_Z_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_U_MEGACAM_MAG float COMMENT 'from deserializer',
TU_FNU_R_MEGACAM_MAG float COMMENT 'from deserializer',
TU_FNU_G_JPCAM_MAG float COMMENT 'from deserializer',
TU_FNU_I_PANSTARRS_MAG float COMMENT 'from deserializer',
TU_FNU_Z_PANSTARRS_MAG float COMMENT 'from deserializer',
TU_FNU_Z_HSC_MAG float COMMENT 'from deserializer',
TU_FNU_G_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_BP_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_RP_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_U_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_G_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_R_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_I_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_Z_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_Y_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_U_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_G_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_R_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_I_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_J_2MASS_MAG float COMMENT 'from deserializer',
TU_FNU_H_2MASS_MAG float COMMENT 'from deserializer',
TU_FNU_KS_2MASS_MAG float COMMENT 'from deserializer'
)
PARTITIONED BY (
`hpix_5_nest` int)
ROW FORMAT SERDE
'es.pic.astro.hadoop.serde.RecArraySerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'es.pic.astro.hadoop.io.BinaryOutputFormat'
;
INSERT OVERWRITE TABLE jcarrete.flagship_mock_1_10_13_highz_s_magnified_fits SELECT CAST(unique_id AS bigint) AS `SOURCE_ID`, CAST(-1 AS bigint) AS `HALO_ID`, CAST(kind AS smallint) AS `KIND`, CAST(ra AS double) AS `RA`, CAST(`dec` AS double) AS `DEC`, CAST(ra_mag AS double) AS `RA_MAG`, CAST(dec_mag AS double) AS `DEC_MAG`, CAST(z AS float) AS `Z_OBS`, CAST(abs_muv AS float) AS `REF_MAG_ABS`, CAST(app_muv AS float) AS `REF_MAG`, CAST(bulge_fraction AS float) AS `BULGE_FRACTION`, CAST(rhalf_arcsec AS float) AS `BULGE_R50`, CAST(rhalf_arcsec AS float) AS `DISK_R50`, CAST(bulge_nsersic AS float) AS `BULGE_NSERSIC`, CAST(bulge_axis_ratio AS float) AS `BULGE_AXIS_RATIO`, CAST(-1 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(sedname_int AS float) AS `SED_TEMPLATE`, CAST(0 AS smallint) AS `EXT_LAW`, CAST(-1 AS float) AS `EBV`, CAST(-1 AS float) AS `HALPHA_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `HBETA_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `O2_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `O3_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `N2_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `S2_LOGFLAM_EXT_MAG`, CAST(mw_extinction AS float) AS `AV`, CAST(euclid_vis*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_VIS_MAG`, CAST(euclid_nisp_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_NISP_MAG`, CAST(euclid_nisp_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_NISP_MAG`, CAST(euclid_nisp_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_NISP_MAG`, CAST(blanco_decam_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_DECAM_MAG`, CAST(blanco_decam_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_DECAM_MAG`, CAST(blanco_decam_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_DECAM_MAG`, CAST(blanco_decam_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_DECAM_MAG`, CAST(cfis_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_MEGACAM_MAG`, CAST(cfis_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_MEGACAM_MAG`, CAST(jedis_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_JPCAM_MAG`, CAST(`pan-starrs_i`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_PANSTARRS_MAG`, CAST(`pan-starrs_z`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_PANSTARRS_MAG`, CAST(wishes_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_HSC_MAG`, CAST(gaia_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_GAIA_MAG`, CAST(gaia_bp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_BP_GAIA_MAG`, CAST(gaia_rp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_RP_GAIA_MAG`, CAST(lsst_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_LSST_MAG`, CAST(lsst_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_LSST_MAG`, CAST(lsst_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_LSST_MAG`, CAST(lsst_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_LSST_MAG`, CAST(lsst_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_LSST_MAG`, CAST(lsst_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_LSST_MAG`, CAST(kids_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_KIDS_MAG`, CAST(kids_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_KIDS_MAG`, CAST(kids_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_KIDS_MAG`, CAST(kids_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_KIDS_MAG`, CAST(2mass_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_2MASS_MAG`, CAST(2mass_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_2MASS_MAG`, CAST(2mass_ks*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_KS_2MASS_MAG`, CAST(SHIFTRIGHT(hpix_29_nest, (29-5)*2) as int) AS hpix_5_nest FROM cosmohub.flagship_mock_1_10_13_highz_s ;
Hay una cagada en la magnitud aparente. Teníamos que haber metido el factor - 5 * log10(h) también. Lo hago con una query. Creo una tabla con el mismo release:
CREATE TABLE cosmohub.flagship_mock_1_10_13_v1_1_highz_s ( `unique_id` BIGINT COMMENT 'unique identifier', `id` INT COMMENT 'ID', `kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT', `z` float COMMENT 'true galaxy redshift', `ra` FLOAT COMMENT 'galaxy right ascension (degrees)', `dec` FLOAT COMMENT 'galaxy declination (degrees)', `ra_mag` float COMMENT 'galaxy magnified right ascension (degree)', `dec_mag` float COMMENT 'galaxy magnified declination (degree)', `kappa` float COMMENT 'convergence', `gamma1` float COMMENT 'shear', `gamma2` float COMMENT 'shear', `defl1` float COMMENT 'deflection1', `defl2` float COMMENT 'deflection2', `l` FLOAT COMMENT 'galactic longitude (degrees)', `b` FLOAT COMMENT 'galactic latitude (degrees)', `mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits', `abs_muv` FLOAT COMMENT 'absolute magnitude at 1500 angstroms', `lum_dist` FLOAT COMMENT 'luminosity distance in pc', `app_muv` FLOAT COMMENT 'apparent magnitude at 1450 angstroms (M1450)', `sedname` STRING COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)', `sedname_int` BIGINT COMMENT 'sedname as integer (from 0 to 75, from 0 to 37 without UV lines, from 38 to 75 with UV lines)', `rhalf_kpc` FLOAT COMMENT 'half light radius (in physical kpc)', `rhalf_arcsec` FLOAT COMMENT 'half light radius (in arcsec)', `muv` FLOAT COMMENT 'for information only, observed magnitude at rest-frame 1500', `beta` FLOAT COMMENT 'for information only, used to assign an SED', `hpix_9_nest` int COMMENT 'healpix pixel id using order = 9 in NESTED format', `hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format', `hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format', `blanco_decam_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES g-band (Euclid provided)', `blanco_decam_g` float COMMENT 'Interpolated observed flux in DES g-band (Euclid provided)', `blanco_decam_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES g-band (Euclid provided)', `blanco_decam_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES i-band (Euclid provided)', `blanco_decam_i` float COMMENT 'Interpolated observed flux in DES i-band (Euclid provided)', `blanco_decam_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES i-band (Euclid provided)', `blanco_decam_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES r-band (Euclid provided)', `blanco_decam_r` float COMMENT 'Interpolated observed flux in DES r-band (Euclid provided)', `blanco_decam_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES r-band (Euclid provided)', `blanco_decam_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in DES z-band (Euclid provided)', `blanco_decam_z` float COMMENT 'Interpolated observed flux in DES z-band (Euclid provided)', `blanco_decam_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in DES z-band (Euclid provided)', `wishes_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `wishes_z` float COMMENT 'Interpolated observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `wishes_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)', `jedis_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in JST g-band (Euclid provided)', `jedis_g` float COMMENT 'Interpolated observed flux in JST g-band (Euclid provided)', `jedis_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in JST g-band (Euclid provided)', `kids_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS g-band (Euclid provided)', `kids_g` float COMMENT 'Interpolated observed flux in KIDS g-band (Euclid provided)', `kids_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS g-band (Euclid provided)', `kids_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS i-band (Euclid provided)', `kids_i` float COMMENT 'Interpolated observed flux in KIDS i-band (Euclid provided)', `kids_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS i-band (Euclid provided)', `kids_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS r-band (Euclid provided)', `kids_r` float COMMENT 'Interpolated observed flux in KIDS r-band (Euclid provided)', `kids_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS r-band (Euclid provided)', `kids_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in KIDS u-band (Euclid provided)', `kids_u` float COMMENT 'Interpolated observed flux in KIDS u-band (Euclid provided)', `kids_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in KIDS u-band (Euclid provided)', `lsst_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST g-band (Euclid provided)', `lsst_g` float COMMENT 'Interpolated observed flux in LSST g-band (Euclid provided)', `lsst_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST g-band (Euclid provided)', `lsst_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST i-band (Euclid provided)', `lsst_i` float COMMENT 'Interpolated observed flux in LSST i-band (Euclid provided)', `lsst_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST i-band (Euclid provided)', `lsst_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST r-band (Euclid provided)', `lsst_r` float COMMENT 'Interpolated observed flux in LSST r-band (Euclid provided)', `lsst_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST r-band (Euclid provided)', `lsst_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST u-band (Euclid provided)', `lsst_u` float COMMENT 'Interpolated observed flux in LSST u-band (Euclid provided)', `lsst_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST u-band (Euclid provided)', `lsst_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST y-band (Euclid provided)', `lsst_y` float COMMENT 'Interpolated observed flux in LSST y-band (Euclid provided)', `lsst_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST y-band (Euclid provided)', `lsst_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in LSST z-band (Euclid provided)', `lsst_z` float COMMENT 'Interpolated observed flux in LSST z-band (Euclid provided)', `lsst_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in LSST z-band (Euclid provided)', `cfis_r_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT r-band (Euclid provided)', `cfis_r` float COMMENT 'Interpolated observed flux in CFHT r-band (Euclid provided)', `cfis_r_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT r-band (Euclid provided)', `cfis_u_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in CFHT u-band (Euclid provided)', `cfis_u` float COMMENT 'Interpolated observed flux in CFHT u-band (Euclid provided)', `cfis_u_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in CFHT u-band (Euclid provided)', `pan-starrs_i_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS i-band (Euclid provided)', `pan-starrs_i` float COMMENT 'Interpolated observed flux in Pan-STARRS i-band (Euclid provided)', `pan-starrs_i_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS i-band (Euclid provided)', `pan-starrs_z_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Pan-STARRS z-band (Euclid provided)', `pan-starrs_z` float COMMENT 'Interpolated observed flux in Pan-STARRS z-band (Euclid provided)', `pan-starrs_z_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Pan-STARRS z-band (Euclid provided)', `2mass_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS H-band (Euclid provided)', `2mass_h` float COMMENT 'Interpolated observed flux in 2MASS H-band (Euclid provided)', `2mass_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS H-band (Euclid provided)', `2mass_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS J-band (Euclid provided)', `2mass_j` float COMMENT 'Interpolated observed flux in 2MASS J-band (Euclid provided)', `2mass_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS J-band (Euclid provided)', `2mass_ks_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in 2MASS Ks-band (Euclid provided)', `2mass_ks` float COMMENT 'Interpolated observed flux in 2MASS Ks-band (Euclid provided)', `2mass_ks_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in 2MASS Ks-band (Euclid provided)', `sdss_r01_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)', `sdss_r01` float COMMENT 'Interpolated observed flux in sdss r-band redshifted to z=0.1 (Euclid provided)', `sdss_r01_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in sdss r-band redshifted to z=0.1 (Euclid provided)', `gaia_bp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-BP band (Euclid provided)', `gaia_bp` float COMMENT 'Interpolated observed flux in Gaia-BP band (Euclid provided)', `gaia_bp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-BP band (Euclid provided)', `gaia_g_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-G band (Euclid provided)', `gaia_g` float COMMENT 'Interpolated observed flux in Gaia-G band (Euclid provided)', `gaia_g_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-G band (Euclid provided)', `gaia_rp_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Gaia-RP band (Euclid provided)', `gaia_rp` float COMMENT 'Interpolated observed flux in Gaia-RP band (Euclid provided)', `gaia_rp_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Gaia-RP band (Euclid provided)', `euclid_nisp_h_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-H band (Euclid provided)', `euclid_nisp_h` float COMMENT 'Interpolated observed flux in Euclid NISP-H band (Euclid provided)', `euclid_nisp_h_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-H band (Euclid provided)', `euclid_nisp_j_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-J band (Euclid provided)', `euclid_nisp_j` float COMMENT 'Interpolated observed flux in Euclid NISP-J band (Euclid provided)', `euclid_nisp_j_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-J band (Euclid provided)', `euclid_nisp_y_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid NISP-Y band (Euclid provided)', `euclid_nisp_y` float COMMENT 'Interpolated observed flux in Euclid NISP-Y band (Euclid provided)', `euclid_nisp_y_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid NISP-Y band (Euclid provided)', `euclid_vis_true` float COMMENT 'Integrated observed flux including (properly) MW extinction in Euclid VIS band (Euclid provided)', `euclid_vis` float COMMENT 'Interpolated observed flux in Euclid VIS band (Euclid provided)', `euclid_vis_odonnell_ext` float COMMENT 'Interpolated observed flux including MW extinction in Euclid VIS band (Euclid provided)', `bulge_fraction` FLOAT COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)', `bulge_nsersic` FLOAT COMMENT 'Sersic index of the bulge component', `bulge_ellipticity` FLOAT COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)', `bulge_axis_ratio` FLOAT COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)', `disk_angle` FLOAT COMMENT 'position of the disk rotation axis (degrees). 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', `step` smallint COMMENT 'redshift step number' ) CLUSTERED BY ( step, hpix_13_nest) SORTED BY ( step ASC, hpix_13_nest ASC) INTO 4096 BUCKETS STORED AS ORC ;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_13_v1_1_highz_s SELECT unique_id, id, kind, z, ra, `dec`, ra_mag, dec_mag, kappa, gamma1, gamma2, defl1, defl2, l, b, mw_extinction, abs_muv, lum_dist, app_muv - 5 * log10(0.67) as app_muv, sedname, sedname_int, rhalf_kpc, rhalf_arcsec, muv, beta, hpix_9_nest, hpix_13_nest, hpix_29_nest, blanco_decam_g_true, blanco_decam_g, blanco_decam_g_odonnell_ext, blanco_decam_i_true, blanco_decam_i, blanco_decam_i_odonnell_ext, blanco_decam_r_true, blanco_decam_r, blanco_decam_r_odonnell_ext, blanco_decam_z_true, blanco_decam_z, blanco_decam_z_odonnell_ext, wishes_z_true, wishes_z, wishes_z_odonnell_ext, jedis_g_true, jedis_g, jedis_g_odonnell_ext, kids_g_true, kids_g, kids_g_odonnell_ext, kids_i_true, kids_i, kids_i_odonnell_ext, kids_r_true, kids_r, kids_r_odonnell_ext, kids_u_true, kids_u, kids_u_odonnell_ext, lsst_g_true, lsst_g, lsst_g_odonnell_ext, lsst_i_true, lsst_i, lsst_i_odonnell_ext, lsst_r_true, lsst_r, lsst_r_odonnell_ext, lsst_u_true, lsst_u, lsst_u_odonnell_ext, lsst_y_true, lsst_y, lsst_y_odonnell_ext, lsst_z_true, lsst_z, lsst_z_odonnell_ext, cfis_r_true, cfis_r, cfis_r_odonnell_ext, cfis_u_true, cfis_u, cfis_u_odonnell_ext, `pan-starrs_i_true`, `pan-starrs_i`, `pan-starrs_i_odonnell_ext`, `pan-starrs_z_true`, `pan-starrs_z`, `pan-starrs_z_odonnell_ext`, 2mass_h_true, 2mass_h, 2mass_h_odonnell_ext, 2mass_j_true, 2mass_j, 2mass_j_odonnell_ext, 2mass_ks_true, 2mass_ks, 2mass_ks_odonnell_ext, sdss_r01_true, sdss_r01, sdss_r01_odonnell_ext, gaia_bp_true, gaia_bp, gaia_bp_odonnell_ext, gaia_g_true, gaia_g, gaia_g_odonnell_ext, gaia_rp_true, gaia_rp, gaia_rp_odonnell_ext, euclid_nisp_h_true, euclid_nisp_h, euclid_nisp_h_odonnell_ext, euclid_nisp_j_true, euclid_nisp_j, euclid_nisp_j_odonnell_ext, euclid_nisp_y_true, euclid_nisp_y, euclid_nisp_y_odonnell_ext, euclid_vis_true, euclid_vis, euclid_vis_odonnell_ext, bulge_fraction, bulge_nsersic, bulge_ellipticity, bulge_axis_ratio, disk_angle, step FROM cosmohub.flagship_mock_1_10_13_highz_s;
ANALYZE TABLE cosmohub.flagship_mock_1_10_13_v1_1_highz_s COMPUTE STATISTICS FOR COLUMNS;
CREATE TABLE jcarrete.flagship_mock_1_10_13_v1_1_highz_s_magnified_fits(
SOURCE_ID bigint COMMENT 'from deserializer',
HALO_ID bigint COMMENT 'from deserializer',
KIND smallint COMMENT 'from deserializer',
RA double COMMENT 'from deserializer',
`DEC` double COMMENT 'from deserializer',
RA_MAG double COMMENT 'from deserializer',
DEC_MAG double 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',
BULGE_AXIS_RATIO 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_MAG float COMMENT 'from deserializer',
HBETA_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
O2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
O3_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
N2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
S2_LOGFLAM_EXT_MAG float COMMENT 'from deserializer',
AV FLOAT COMMENT 'from deserializer',
TU_FNU_VIS_MAG float COMMENT 'from deserializer',
TU_FNU_Y_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_J_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_H_NISP_MAG float COMMENT 'from deserializer',
TU_FNU_G_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_R_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_I_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_Z_DECAM_MAG float COMMENT 'from deserializer',
TU_FNU_U_MEGACAM_MAG float COMMENT 'from deserializer',
TU_FNU_R_MEGACAM_MAG float COMMENT 'from deserializer',
TU_FNU_G_JPCAM_MAG float COMMENT 'from deserializer',
TU_FNU_I_PANSTARRS_MAG float COMMENT 'from deserializer',
TU_FNU_Z_PANSTARRS_MAG float COMMENT 'from deserializer',
TU_FNU_Z_HSC_MAG float COMMENT 'from deserializer',
TU_FNU_G_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_BP_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_RP_GAIA_MAG float COMMENT 'from deserializer',
TU_FNU_U_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_G_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_R_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_I_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_Z_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_Y_LSST_MAG float COMMENT 'from deserializer',
TU_FNU_U_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_G_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_R_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_I_KIDS_MAG float COMMENT 'from deserializer',
TU_FNU_J_2MASS_MAG float COMMENT 'from deserializer',
TU_FNU_H_2MASS_MAG float COMMENT 'from deserializer',
TU_FNU_KS_2MASS_MAG float COMMENT 'from deserializer'
)
PARTITIONED BY (
`hpix_5_nest` int)
ROW FORMAT SERDE
'es.pic.astro.hadoop.serde.RecArraySerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'es.pic.astro.hadoop.io.BinaryOutputFormat'
;
INSERT OVERWRITE TABLE jcarrete.flagship_mock_1_10_13_v1_1_highz_s_magnified_fits SELECT CAST(unique_id AS bigint) AS `SOURCE_ID`, CAST(-1 AS bigint) AS `HALO_ID`, CAST(kind AS smallint) AS `KIND`, CAST(ra AS double) AS `RA`, CAST(`dec` AS double) AS `DEC`, CAST(ra_mag AS double) AS `RA_MAG`, CAST(dec_mag AS double) AS `DEC_MAG`, CAST(z AS float) AS `Z_OBS`, CAST(abs_muv AS float) AS `REF_MAG_ABS`, CAST(app_muv AS float) AS `REF_MAG`, CAST(bulge_fraction AS float) AS `BULGE_FRACTION`, CAST(rhalf_arcsec AS float) AS `BULGE_R50`, CAST(rhalf_arcsec AS float) AS `DISK_R50`, CAST(bulge_nsersic AS float) AS `BULGE_NSERSIC`, CAST(bulge_axis_ratio AS float) AS `BULGE_AXIS_RATIO`, CAST(-1 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(sedname_int AS float) AS `SED_TEMPLATE`, CAST(0 AS smallint) AS `EXT_LAW`, CAST(-1 AS float) AS `EBV`, CAST(-1 AS float) AS `HALPHA_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `HBETA_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `O2_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `O3_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `N2_LOGFLAM_EXT_MAG`, CAST(-1 AS float) AS `S2_LOGFLAM_EXT_MAG`, CAST(mw_extinction AS float) AS `AV`, CAST(euclid_vis*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_VIS_MAG`, CAST(euclid_nisp_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_NISP_MAG`, CAST(euclid_nisp_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_NISP_MAG`, CAST(euclid_nisp_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_NISP_MAG`, CAST(blanco_decam_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_DECAM_MAG`, CAST(blanco_decam_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_DECAM_MAG`, CAST(blanco_decam_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_DECAM_MAG`, CAST(blanco_decam_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_DECAM_MAG`, CAST(cfis_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_MEGACAM_MAG`, CAST(cfis_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_MEGACAM_MAG`, CAST(jedis_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_JPCAM_MAG`, CAST(`pan-starrs_i`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_PANSTARRS_MAG`, CAST(`pan-starrs_z`*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_PANSTARRS_MAG`, CAST(wishes_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_HSC_MAG`, CAST(gaia_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_GAIA_MAG`, CAST(gaia_bp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_BP_GAIA_MAG`, CAST(gaia_rp*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_RP_GAIA_MAG`, CAST(lsst_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_LSST_MAG`, CAST(lsst_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_LSST_MAG`, CAST(lsst_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_LSST_MAG`, CAST(lsst_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_LSST_MAG`, CAST(lsst_z*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Z_LSST_MAG`, CAST(lsst_y*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_Y_LSST_MAG`, CAST(kids_u*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_U_KIDS_MAG`, CAST(kids_g*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_G_KIDS_MAG`, CAST(kids_r*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_R_KIDS_MAG`, CAST(kids_i*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_I_KIDS_MAG`, CAST(2mass_j*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_J_2MASS_MAG`, CAST(2mass_h*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_H_2MASS_MAG`, CAST(2mass_ks*1.e23*ABS(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS `TU_FNU_KS_2MASS_MAG`, CAST(SHIFTRIGHT(hpix_29_nest, (29-5)*2) as int) AS hpix_5_nest FROM cosmohub.flagship_mock_1_10_13_v1_1_highz_s ;