High-z QSOs
Jump to navigation
Jump to search
Since I don't know how to do it, I use this old page which was "detached" or "invisible" to include all the information about the production of the High-z QSOs for SC8.
The info of the page is not removed since it is included in the "QSOs" page.
Para generar el catálogo de QSOs lo que hacemos es utilizar una pipeline especial:
qso_pipeline.ipynb
Se la ha currado Pau casi entera.
Lo que hacemos es seleccionar un número de QSOs del total que tenemos en el catálogo, ya que se corresponde con un área de 15000. El footprint que tenemos para el SC8 es 1296.92. Cuando hacemos la query a la tabla de los QSOs seleccionamos un porcentaje 1296.92/15000 aleatoriamente. Además, le asignamos un valor aleatoriamente a cada QSO entre 1 y el número de píxeles que tiene la máscara para posicionarlo en el cielo.
sql = """
SELECT m.*, f.hpix_9_nest
FROM
(
SELECT m.*, RAND() as r
FROM cosmohub.full_h25cut_hcol_v1_1_c AS m
WHERE rand() <= (1296.92/15000)
) AS m
JOIN cosmohub.sc8_footprint_nest_nside512_2_c AS f
ON f.row_number = CAST((m.r * 98896) + 1 AS INT)
"""
Para crear el unique_id busco el máximo del SOURCE_ID y me voy a un orden superior.
# The current maximum value of SOURCE_ID when creating FITS files is: # SELECT MAX(CAST(((halo_id * 10000) + galaxy_id) AS bigint)) as max_source_id FROM flagship_mock_1_10_3_s_sc8_c; # 9081710654390000 # QSOs starts at 20000000000000000
Once created, I create the parquet table:
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_4_qso/0.pq -t jcarrete.flagship_mock_1_10_4_qso_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_4_qso_pq (
`type` STRING,
`z` DOUBLE,
`m1450` DOUBLE,
`template` STRING,
`template_int` BIGINT,
`h` 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` DOUBLE,
`blanco_decam_i` DOUBLE,
`blanco_decam_r` DOUBLE,
`blanco_decam_z` DOUBLE,
`subaru_hsc_z` DOUBLE,
`jst_jpcam_g` DOUBLE,
`kids_g` DOUBLE,
`kids_i` DOUBLE,
`kids_r` DOUBLE,
`kids_u` DOUBLE,
`lsst_g` DOUBLE,
`lsst_i` DOUBLE,
`lsst_r` DOUBLE,
`lsst_u` DOUBLE,
`lsst_y` DOUBLE,
`lsst_z` DOUBLE,
`cfht_megacam_r` DOUBLE,
`cfht_megacam_u` DOUBLE,
`pan_starrs_i` DOUBLE,
`pan_starrs_z` DOUBLE,
`2mass_h` DOUBLE,
`2mass_j` DOUBLE,
`2mass_ks` DOUBLE,
`sdss_r01` DOUBLE,
`gaia_bp` DOUBLE,
`gaia_g` DOUBLE,
`gaia_rp` DOUBLE,
`euclid_nisp_h` DOUBLE,
`euclid_nisp_j` DOUBLE,
`euclid_nisp_y` DOUBLE,
`euclid_vis` DOUBLE,
`id` BIGINT
)
STORED AS PARQUET
LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_4_qso'
;
CREATE TABLE jcarrete.flagship_mock_1_10_4_qso_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',
`ra` FLOAT COMMENT 'galaxy right ascension (degrees)',
`dec` FLOAT COMMENT 'galaxy declination (degrees)',
`l` FLOAT COMMENT 'galactic longitude (degrees)',
`b` FLOAT COMMENT 'galactic latitude (degrees)',
`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',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`z` float COMMENT 'true galaxy redshift',
`template` STRING COMMENT 'spectral template name (among 21)',
`template_int` INT COMMENT 'spectral template as integer (from 0 to 20)',
`h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut',
`r` FLOAT COMMENT 'random number between 0 and 1',
`m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)',
`2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)',
`2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)',
`2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'observed flux in DES r-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'observed flux in DES z-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'observed flux in CFHT u-band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_vis` float COMMENT 'observed flux in Euclid VIS band (Euclid provided)',
`gaia_bp` float COMMENT 'observed flux in Gaia-BP band (Euclid provided)',
`gaia_g` float COMMENT 'observed flux in Gaia-G band (Euclid provided)',
`gaia_rp` float COMMENT 'observed flux in Gaia-RP band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'observed flux in JST g-band (Euclid provided)',
`kids_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)',
`kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)',
`kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)',
`kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)',
`lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)',
`lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)',
`lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)',
`lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)',
`lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)',
`lsst_z` float COMMENT 'observed flux in LSST z-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'observed flux in Pan-STARRS z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`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 jcarrete.flagship_mock_1_10_4_qso_c SELECT unique_id, id, kind, ra, `dec`, l, b, hpix_9_nest, SHIFTRIGHT(hpix_29_nest, (29-13)*2), hpix_29_nest, mw_extinction, z, template, template_int, h, r, m1450, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, step FROM jcarrete.flagship_mock_1_10_4_qso_pq;
Y ahora hago el JOIN con el lensing:
CREATE TABLE jcarrete.flagship_mock_1_10_4_qso_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',
`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)',
`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',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`z` float COMMENT 'true galaxy redshift',
`template` STRING COMMENT 'spectral template name (among 21)',
`template_int` INT COMMENT 'spectral template as integer (from 0 to 20)',
`h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut',
`r` FLOAT COMMENT 'random number between 0 and 1',
`m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)',
`2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)',
`2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)',
`2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'observed flux in DES r-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'observed flux in DES z-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'observed flux in CFHT u-band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_vis` float COMMENT 'observed flux in Euclid VIS band (Euclid provided)',
`gaia_bp` float COMMENT 'observed flux in Gaia-BP band (Euclid provided)',
`gaia_g` float COMMENT 'observed flux in Gaia-G band (Euclid provided)',
`gaia_rp` float COMMENT 'observed flux in Gaia-RP band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'observed flux in JST g-band (Euclid provided)',
`kids_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)',
`kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)',
`kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)',
`kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)',
`lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)',
`lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)',
`lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)',
`lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)',
`lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)',
`lsst_z` float COMMENT 'observed flux in LSST z-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'observed flux in Pan-STARRS z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`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 jcarrete.flagship_mock_1_10_4_qso_s SELECT unique_id, id, kind, ra, `dec`, mag_pos.ra_mag, mag_pos.dec_mag, kappa, gamma1, gamma2, defl1, defl2, l, b, hpix_9_nest, hpix_13_nest, hpix_29_nest, mw_extinction, z, template, template_int, h, r, m1450, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, step FROM (SELECT unique_id, id, kind, 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, hpix_9_nest, l.hpix_13_nest, hpix_29_nest, mw_extinction, z, template, template_int, h, r, m1450, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, l.step FROM jcarrete.flagship_mock_1_10_4_qso_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 ;
Le cambio el esquema!
ALTER TABLE jcarrete.flagship_mock_1_10_4_qso_s RENAME TO cosmohub.flagship_mock_1_10_4_qso_s;
Hago un FITS file con una sola galaxia!
{
"sql": "SELECT CAST(unique_id AS bigint) AS SOURCE_ID, \nCAST(-1 AS float) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra AS float) AS RA, \nCAST(`dec` AS float) AS `DEC`, \nCAST(ra_mag AS float) AS RA_MAG, \nCAST(dec_mag AS float) AS DEC_MAG, \nCAST(z AS float) AS Z_OBS, \nCAST(m1450 AS float) AS REF_MAG_ABS, \nCAST(m1450 AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(-1 AS float) AS BULGE_R50, \nCAST(-1 AS float) AS DISK_R50, \nCAST(-1 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(template_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_4_qso_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2554"
}
CosmoHub information:
This is the first SC8 QSOs release.
We have randomly selected QSO from [1](https://cosmohub.pic.es/catalogs/205) (copied information below) and located also 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)
---
Catalogue of high-z QSOs
Full information about the catalog can be found in the following Redmine url:
[2](https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/)
We (Rhys Barnett) generated a high-z QSO catalogue from Jiang et al. (2016) QSO luminosity function at z = 6 and assuming Wang et al. (2019) redshift evolution (k=-0.72).
Important features to be noted:
1. The corresponding area is 15,000 deg2 2. We applied an approximate magnitude cut at H=25 (computed with the Euclid H-filter bandpass) 3. We applied an overpopulation by a factor 1000. This overpopulation factor is motivated by the need to perform meaningful statistical tests of completeness in the highest redshift bins where the number of objects is obviously the lowest 4. There is a set of 21 QSO spectral templates in total. Each object is assigned a template with a uniform random distribution.
Para pasar el .ipynb a .py hay que hacerlo con "Pair notebook with percent Script".
Para correrlo:
/software/astro/scripts/spark_notebook.sh -e mocks -s -- path_to_script.py
Then we run it massively. This is the second try:
Pau usa la pipeline para generar un mock que contiene ya todos los filtros. Calcula los flujos:
- blanco_decam_g_true: incluyen la milky way extinction también calculada true
- blanco_decam_g: interpolado sin MW extinction
- blanco_decam_g_odonnell_ext: interpolado tanto el flujo como la extinción.
Esta es la tabla que ha creado Pau: cosmohub.flagship_mock_1_10_4_qso_pq
Lo paso a clustered:
CREATE TABLE cosmohub.flagship_mock_1_10_4_qso_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',
`ra` FLOAT COMMENT 'galaxy right ascension (degrees)',
`dec` FLOAT COMMENT 'galaxy declination (degrees)',
`l` FLOAT COMMENT 'galactic longitude (degrees)',
`b` FLOAT COMMENT 'galactic latitude (degrees)',
`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',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`z` float COMMENT 'true galaxy redshift',
`template` STRING COMMENT 'spectral template name (among 21)',
`template_int` INT COMMENT 'spectral template as integer (from 0 to 20)',
`h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut',
`r` FLOAT COMMENT 'random number between 0 and 1',
`m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)',
`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_4_qso_c SELECT `unique_id`, `id`, `kind`, `ra`, `dec`, `l`, `b`, `hpix_9_nest`, SHIFTRIGHT(hpix_29_nest, (29-13)*2), `hpix_29_nest`, `mw_extinction`, `z`, `template`, `template_int`, `h`, `r`, `m1450`, `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 cosmohub.flagship_mock_1_10_4_qso_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_4_qso_c COMPUTE STATISTICS FOR COLUMNS;
Añado el lensing:
CREATE TABLE cosmohub.flagship_mock_1_10_4_qso_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',
`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)',
`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',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`z` float COMMENT 'true galaxy redshift',
`template` STRING COMMENT 'spectral template name (among 21)',
`template_int` INT COMMENT 'spectral template as integer (from 0 to 20)',
`h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut',
`r` FLOAT COMMENT 'random number between 0 and 1',
`m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)',
`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_4_qso_s SELECT `unique_id`, `id`, `kind`, `ra`, `dec`, mag_pos.ra_mag, mag_pos.dec_mag, kappa, gamma1, gamma2, defl1, defl2, `l`, `b`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `mw_extinction`, `z`, `template`, `template_int`, `h`, `r`, `m1450`, `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`, `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`, `hpix_9_nest`, l.hpix_13_nest, `hpix_29_nest`, `mw_extinction`, `z`, `template`, `template_int`, `h`, `r`, `m1450`, `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_4_qso_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_4_qso_s COMPUTE STATISTICS FOR COLUMNS;
SELECT COUNT(*) FROM cosmohub.flagship_mock_1_10_4_qso_s; 1520395
A more simple example:
select COUNT(m.hpix_13_nest) from cosmohub.flagship_mock_1_10_4_qso_c as m join cosmohub.flagship_lensing2 as l on l.step = m.step and l.hpix_13_nest = m.hpix_13_nest;
Info en CosmoHub:
This is the first SC8 QSOs release.
We have randomly selected QSO from [3](https://cosmohub.pic.es/catalogs/205) (copied information below) and located also 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)
---
Catalogue of high-z QSOs
Full information about the catalog can be found in the following Redmine url:
[4](https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/)
We (Rhys Barnett) generated a high-z QSO catalogue from Jiang et al. (2016) QSO luminosity function at z = 6 and assuming Wang et al. (2019) redshift evolution (k=-0.72).
Important features to be noted:
1. The corresponding area is 15,000 deg2 2. We applied an approximate magnitude cut at H=25 (computed with the Euclid H-filter bandpass) 3. We applied an overpopulation by a factor 1000. This overpopulation factor is motivated by the need to perform meaningful statistical tests of completeness in the highest redshift bins where the number of objects is obviously the lowest 4. There is a set of 21 QSO spectral templates in total. Each object is assigned a template with a uniform random distribution.
FITS files:
{
"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(m1450 AS float) AS REF_MAG_ABS, \nCAST(m1450 AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(-1 AS float) AS BULGE_R50, \nCAST(-1 AS float) AS DISK_R50, \nCAST(-1 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(template_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_4_qso_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063"
}
Genero un nuevo release de QSOs:
Modifico el notebook: qso_pipeline.ipynb
- 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
sql = """
SELECT unique_id, id, kind, ra, dec,
ra_mag, dec_mag, kappa, gamma1, gamma2, defl1, defl2, l, b,
hpix_9_nest, hpix_13_nest, hpix_29_nest, mw_extinction, z, template, template_int, h, r, m1450,
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, step
FROM cosmohub.flagship_mock_1_10_4_qso_s
"""
Una vez generado el catálogo, genero la tabla externa en parquet:
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_9_qso/0.pq -t jcarrete.flagship_mock_1_10_9_qso_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_9_qso_pq ( `id` BIGINT, `kind` BIGINT, `ra` DOUBLE, `dec` DOUBLE, `ra_mag` DOUBLE, `dec_mag` DOUBLE, `kappa` DOUBLE, `gamma1` DOUBLE, `gamma2` DOUBLE, `defl1` DOUBLE, `defl2` DOUBLE, `l` DOUBLE, `b` DOUBLE, `hpix_9_nest` BIGINT, `hpix_13_nest` BIGINT, `hpix_29_nest` BIGINT, `mw_extinction` DOUBLE, `z` DOUBLE, `template` STRING, `template_int` BIGINT, `h` DOUBLE, `r` DOUBLE, `m1450` 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, `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, `step` BIGINT, `lum_dist` DOUBLE, `app_m1450` 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_9_qso' ;
CREATE TABLE cosmohub.flagship_mock_1_10_9_qso_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', `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)', `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', `mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits', `z` float COMMENT 'true galaxy redshift', `template` STRING COMMENT 'spectral template name (among 21)', `template_int` INT COMMENT 'spectral template as integer (from 0 to 20)', `h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut', `r` FLOAT COMMENT 'random number between 0 and 1', `m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)', `lum_dist` FLOAT COMMENT 'luminosity distance in pc', `app_m1450` FLOAT COMMENT 'apparent magnitude at 1450 angstroms (M1450)', `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)', `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_9_qso_s SELECT `unique_id`, `id`, `kind`, `ra`, `dec`, `ra_mag`, `dec_mag`, `kappa`, `gamma1`, `gamma2`, `defl1`, `defl2`, `l`, `b`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `mw_extinction`, `z`, `template`, `template_int`, `h`, `r`, `m1450`, `lum_dist`, `app_m1450`, `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`, `step` FROM jcarrete.flagship_mock_1_10_9_qso_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_9_qso_s COMPUTE STATISTICS FOR COLUMNS;
Info in CosmoHub:
## This is the first SC8 QSOs release. Changes from previous version (1.10.4): * 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 randomly selected QSO from [5](https://cosmohub.pic.es/catalogs/205) (copied information below) and located also 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) ## Catalogue of high-z QSOs Full information about the catalog can be found in the following Redmine url: [6](https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/) We (Rhys Barnett) generated a high-z QSO catalogue from Jiang et al. (2016) QSO luminosity function at z = 6 and assuming Wang et al. (2019) redshift evolution (k=-0.72). Important features to be noted: 1. The corresponding area is 15,000 deg2 2. We applied an approximate magnitude cut at H=25 (computed with the Euclid H-filter bandpass) 3. We applied an overpopulation by a factor 1000. This overpopulation factor is motivated by the need to perform meaningful statistical tests of completeness in the highest redshift bins where the number of objects is obviously the lowest 4. There is a set of 21 QSO spectral templates in total. Each object is assigned a template with a uniform random distribution.
{
"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(m1450 AS float) AS REF_MAG_ABS, \nCAST(app_m1450 AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(-1 AS float) AS BULGE_R50, \nCAST(-1 AS float) AS DISK_R50, \nCAST(-1 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(template_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_9_qso_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063"
}
FITS table:
CREATE TABLE jcarrete.flagship_mock_1_10_9_qso_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_9_qso_s_magnified_fits PARTITION(hpix_5_nest) 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(m1450 AS float) AS `REF_MAG_ABS`, CAST(app_m1450 AS float) AS `REF_MAG`, CAST(-1 AS float) AS `BULGE_FRACTION`, CAST(-1 AS float) AS `BULGE_R50`, CAST(-1 AS float) AS `DISK_R50`, CAST(-1 AS float) AS `BULGE_NSERSIC`, CAST(-1 AS float) AS `BULGE_AXIS_RATIO`, CAST(-1 AS float) AS `INCLINATION_ANGLE`, CAST(-1 AS float) AS `DISK_ANGLE`, CAST(kappa AS float) AS `KAPPA`, CAST(gamma1 AS float) AS `GAMMA1`, CAST(gamma2 AS float) AS `GAMMA2`, CAST(template_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_9_qso_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_9_qso_s, excepto los flujos, quitar los steps en la pipeline que no necesito (ref_mag y gamma1 flip) y volverla a correr.
Y lo más importante: cuando selecciono el catálogo, calculo m1450 - 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!
Lo calculo con el qso_pipeline.py notebook.
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_12_qso/0.pq -t jcarrete.flagship_mock_1_10_12_qso_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_12_qso_pq ( `id` BIGINT, `kind` BIGINT, `ra` DOUBLE, `dec` DOUBLE, `ra_mag` DOUBLE, `dec_mag` DOUBLE, `kappa` DOUBLE, `gamma1` DOUBLE, `gamma2` DOUBLE, `defl1` DOUBLE, `defl2` DOUBLE, `l` DOUBLE, `b` DOUBLE, `hpix_9_nest` BIGINT, `hpix_13_nest` BIGINT, `hpix_29_nest` BIGINT, `mw_extinction` DOUBLE, `z` DOUBLE, `template` STRING, `template_int` BIGINT, `h` DOUBLE, `r` DOUBLE, `m1450` DOUBLE, `lum_dist` DOUBLE, `app_m1450` 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_12_qso' ;
CREATE TABLE cosmohub.flagship_mock_1_10_12_qso_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', `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)', `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', `mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits', `z` float COMMENT 'true galaxy redshift', `template` STRING COMMENT 'spectral template name (among 21)', `template_int` INT COMMENT 'spectral template as integer (from 0 to 20)', `h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut', `r` FLOAT COMMENT 'random number between 0 and 1', `m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)', `lum_dist` FLOAT COMMENT 'luminosity distance in pc', `app_m1450` FLOAT COMMENT 'apparent magnitude at 1450 angstroms (M1450)', `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)', `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_12_qso_s SELECT `unique_id`, `id`, `kind`, `ra`, `dec`, `ra_mag`, `dec_mag`, `kappa`, `gamma1`, `gamma2`, `defl1`, `defl2`, `l`, `b`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `mw_extinction`, `z`, `template`, `template_int`, `h`, `r`, `m1450`, `lum_dist`, `app_m1450`, `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`, `step` FROM jcarrete.flagship_mock_1_10_12_qso_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_12_qso_s COMPUTE STATISTICS FOR COLUMNS;
CREATE TABLE jcarrete.flagship_mock_1_10_12_qso_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_12_qso_s_magnified_fits PARTITION(hpix_5_nest) 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(m1450 AS float) AS `REF_MAG_ABS`, CAST(app_m1450 AS float) AS `REF_MAG`, CAST(-1 AS float) AS `BULGE_FRACTION`, CAST(-1 AS float) AS `BULGE_R50`, CAST(-1 AS float) AS `DISK_R50`, CAST(-1 AS float) AS `BULGE_NSERSIC`, CAST(-1 AS float) AS `BULGE_AXIS_RATIO`, CAST(-1 AS float) AS `INCLINATION_ANGLE`, CAST(-1 AS float) AS `DISK_ANGLE`, CAST(kappa AS float) AS `KAPPA`, CAST(gamma1 AS float) AS `GAMMA1`, CAST(gamma2 AS float) AS `GAMMA2`, CAST(template_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_12_qso_s ;
Y finalmente tengo que añadir los header. En este caso añadimos las unidades y el nombre de la extensión.
Hay un error en la magnitud aparente. Teníamos que haber metido el factor - 5 * log10(h) también. En este caso lo hago con una query. No vuelvo a generar el catálogo con el notebook! Creo una tabla con el mismo release:
CREATE TABLE cosmohub.flagship_mock_1_10_12_v1_1_qso_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', `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)', `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', `mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits', `z` float COMMENT 'true galaxy redshift', `template` STRING COMMENT 'spectral template name (among 21)', `template_int` INT COMMENT 'spectral template as integer (from 0 to 20)', `h` FLOAT COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut', `r` FLOAT COMMENT 'random number between 0 and 1', `m1450` FLOAT COMMENT 'absolute magnitude at 1450 angstroms (M1450)', `lum_dist` FLOAT COMMENT 'luminosity distance in pc', `app_m1450` FLOAT COMMENT 'apparent magnitude at 1450 angstroms (M1450)', `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)', `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_12_v1_1_qso_s SELECT `unique_id`, `id`, `kind`, `ra`, `dec`, `ra_mag`, `dec_mag`, `kappa`, `gamma1`, `gamma2`, `defl1`, `defl2`, `l`, `b`, `hpix_9_nest`, `hpix_13_nest`, `hpix_29_nest`, `mw_extinction`, `z`, `template`, `template_int`, `h`, `r`, `m1450`, `lum_dist`, `app_m1450` - 5 * log10(0.67) as `app_m1450`, `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`, `step` FROM cosmohub.flagship_mock_1_10_12_qso_s;
ANALYZE TABLE cosmohub.flagship_mock_1_10_12_v1_1_qso_s COMPUTE STATISTICS FOR COLUMNS;
CREATE TABLE jcarrete.flagship_mock_1_10_12_v1_1_qso_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_12_v1_1_qso_s_magnified_fits PARTITION(hpix_5_nest) 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(m1450 AS float) AS `REF_MAG_ABS`, CAST(app_m1450 AS float) AS `REF_MAG`, CAST(-1 AS float) AS `BULGE_FRACTION`, CAST(-1 AS float) AS `BULGE_R50`, CAST(-1 AS float) AS `DISK_R50`, CAST(-1 AS float) AS `BULGE_NSERSIC`, CAST(-1 AS float) AS `BULGE_AXIS_RATIO`, CAST(-1 AS float) AS `INCLINATION_ANGLE`, CAST(-1 AS float) AS `DISK_ANGLE`, CAST(kappa AS float) AS `KAPPA`, CAST(gamma1 AS float) AS `GAMMA1`, CAST(gamma2 AS float) AS `GAMMA2`, CAST(template_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_12_v1_1_qso_s ;
Ahora le vuelvo a añadir los header pero lo meto en el mismo release que antes.