High-z QSOs

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;