Jcarrete at 22:37, 25 February 2021

2021-02-25T22:37:17Z

Show changes

Jcarrete at 09:54, 2 February 2021

2021-02-02T09:54:47Z

Show changes

Jcarrete at 15:59, 18 January 2021

2021-01-18T15:59:15Z

Jcarrete at 16:26, 15 January 2021

2021-01-15T16:26:05Z

Show changes

Jcarrete: Created page with "Information in Euclid Redmine: https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/ CREATE TABLE full_H25cut_Hcol_v1_0_c( `id` bigint COMMENT 'ID', `type` strin..."

2020-06-30T22:06:39Z

Created page with "Information in Euclid Redmine: https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/ CREATE TABLE full_H25cut_Hcol_v1_0_c( `id` bigint COMMENT 'ID', `type` strin..."

New page

Information in Euclid Redmine:

https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/

CREATE TABLE full_H25cut_Hcol_v1_0_c(
`id` bigint COMMENT 'ID',
`type` string COMMENT 'type (QSO for all objects)',
`z` float COMMENT 'redshift (z). Redshifts are in the range [6-15]',
`m1450` float COMMENT 'absolute magnitude at 1450 angstroms (M1450)',
`template` string COMMENT 'spectral template name (among 21)',
`h` float COMMENT 'approximate H-band magnitude for information only. This was computed for applying the magnitude cut'
)
CLUSTERED BY (
id
)
SORTED BY (
id ASC
)
INTO 4 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;

INSERT OVERWRITE TABLE cosmohub.full_H25cut_Hcol_v1_0_c
SELECT `id`, `type`, `z`,`m1450`, `template`, `h`
FROM tallada.full_H25cut_Hcol;

ANALYZE TABLE cosmohub.full_H25cut_Hcol_v1_0_c COMPUTE STATISTICS FOR COLUMNS;

--------------

CREATE TABLE full_H25cut_Hcol_v1_1_c(
`id` bigint COMMENT 'ID',
`type` string COMMENT 'type (QSO for all objects)',
`z` float COMMENT 'redshift (z). Redshifts are in the range [6-15]',
`m1450` float COMMENT 'absolute magnitude at 1450 angstroms (M1450)',
`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'
)
CLUSTERED BY (
id
)
SORTED BY (
id ASC
)
INTO 4 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;

INSERT OVERWRITE TABLE cosmohub.full_H25cut_Hcol_v1_1_c
SELECT `id`, `type`, `z`,`m1450`, `template`,
CASE WHEN template = 'Banados_avg_Harris' THEN 0
WHEN template = 'Banados_avg_Selsing' THEN 1
WHEN template = 'Banados_avg_Vandenberk' THEN 2
WHEN template = 'Banados_hlya_Harris' THEN 3
WHEN template = 'Banados_hlya_Selsing' THEN 4
WHEN template = 'Banados_hlya_Vandenberk' THEN 5
WHEN template = 'Banados_llya_Harris' THEN 6
WHEN template = 'Banados_llya_Selsing' THEN 7
WHEN template = 'Banados_llya_Vandenberk' THEN 8
WHEN template = 'Hewett_l0_cb' THEN 9
WHEN template = 'Hewett_l0_cr' THEN 10
WHEN template = 'Hewett_l0_cs' THEN 11
WHEN template = 'Hewett_ld_cb' THEN 12
WHEN template = 'Hewett_ld_cr' THEN 13
WHEN template = 'Hewett_ld_cs' THEN 14
WHEN template = 'Hewett_lh_cb' THEN 15
WHEN template = 'Hewett_lh_cr' THEN 16
WHEN template = 'Hewett_lh_cs' THEN 17
WHEN template = 'Hewett_ls_cb' THEN 18
WHEN template = 'Hewett_ls_cr' THEN 19
WHEN template = 'Hewett_ls_cs' THEN 20
END as template_int,
`h`
FROM tallada.full_H25cut_Hcol;

ANALYZE TABLE cosmohub.full_H25cut_Hcol_v1_1_c COMPUTE STATISTICS FOR COLUMNS;

@@ Line 647: / Line 647: @@
+     {
-         "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 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"
+         "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"
+         }

High-z QSOs - Revision history

Jcarrete at 22:37, 25 February 2021

Jcarrete at 09:54, 2 February 2021

Jcarrete at 15:59, 18 January 2021

Jcarrete at 16:26, 15 January 2021

Jcarrete: Created page with "Information in Euclid Redmine: https://euclid.roe.ac.uk/projects/puswg/wiki/Quasars/ CREATE TABLE full_H25cut_Hcol_v1_0_c( `id` bigint COMMENT 'ID', `type` strin..."