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;