View source for DESY3 MICE2 source catalog

== FIRST ITERATION ==

There is a notebook donde convierto los FITS files en parquet files:

DES_Y3_MICE2_source_catalog_IA_mocks

    CREATE EXTERNAL TABLE jcarrete.scattZ_sof_mice_Feb3rd_maxmag22_5_pq (
    `unique_gal_id` BIGINT,
    `z_cgal` DOUBLE,
    `ra_gal` DOUBLE,
    `dec_gal` DOUBLE,
    `g_sof` DOUBLE,
    `r_sof` DOUBLE,
    `i_sof` DOUBLE,
    `z_sof` DOUBLE,
    `z_dnf_mean_sof` FLOAT,
    `z_dnf_mc_sof` FLOAT,
    `des_asahi_full_g_true` FLOAT,
    `des_asahi_full_r_true` FLOAT,
    `des_asahi_full_i_true` FLOAT,
    `des_asahi_full_z_true` FLOAT,
    `kappa` FLOAT,
    `gamma1` FLOAT,
    `gamma2` FLOAT,
    `eps1` DOUBLE,
    `eps2` DOUBLE
    )
    STORED AS PARQUET
    LOCATION '/user/jcarrete/data/mice/des_y3_mice2_source_catalog_IA_mocks'
    ;



    CREATE TABLE cosmohub.scattZ_sof_mice_Feb3rd_maxmag22_5_c (
   `unique_gal_id` BIGINT,
    `z_cgal` DOUBLE,
    `ra_gal` DOUBLE,
    `dec_gal` DOUBLE,
    `g_sof` DOUBLE,
    `r_sof` DOUBLE,
    `i_sof` DOUBLE,
    `z_sof` DOUBLE,
    `z_dnf_mean_sof` FLOAT,
    `z_dnf_mc_sof` FLOAT,
    `des_asahi_full_g_true` FLOAT,
    `des_asahi_full_r_true` FLOAT,
    `des_asahi_full_i_true` FLOAT,
    `des_asahi_full_z_true` FLOAT,
    `kappa` FLOAT,
    `gamma1` FLOAT,
    `gamma2` FLOAT,
    `eps1` DOUBLE,
    `eps2` DOUBLE
    )
    CLUSTERED BY (
    unique_gal_id) 
    INTO 8 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.scattZ_sof_mice_Feb3rd_maxmag22_5_c
    SELECT *
    FROM  jcarrete.scattZ_sof_mice_Feb3rd_maxmag22_5_pq;

    ANALYZE TABLE cosmohub.scattZ_sof_mice_Feb3rd_maxmag22_5_c COMPUTE STATISTICS;

    ANALYZE TABLE cosmohub.scattZ_sof_mice_Feb3rd_maxmag22_5_c COMPUTE STATISTICS FOR COLUMNS;


== SECOND ITERATION ==


It is necessary to add partitions to the table to be able to filter by "tomographic source bin" (which are in the different files).

I create another parquet table with partitions.

I also create and copy inside the corresponding directories in the hdfs.

Then I create the ORC and overwrite it.

There is also a google doc written by M.Crocce and Lucas Secco with the info of the catalog:

https://docs.google.com/document/d/1PieK-sds2aUpGQMjmwREtXmquwr6EWe2TnOSQ12vRWA/edit

These are the commands:

    CREATE EXTERNAL TABLE `jcarrete.scattz_sof_mice_feb3rd_maxmag22_5_partitioned_pq`(
  `unique_gal_id` bigint, 
  `z_cgal` double, 
  `ra_gal` double, 
  `dec_gal` double, 
  `g_sof` double, 
  `r_sof` double, 
  `i_sof` double, 
  `z_sof` double, 
  `z_dnf_mean_sof` float, 
  `z_dnf_mc_sof` float, 
  `des_asahi_full_g_true` float, 
  `des_asahi_full_r_true` float, 
  `des_asahi_full_i_true` float, 
  `des_asahi_full_z_true` float, 
  `kappa` float, 
  `gamma1` float, 
  `gamma2` float, 
  `eps1` double, 
  `eps2` double)
    PARTITIONED BY (tomographic_bin int)
    ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
    STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
    OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
    LOCATION
  'hdfs://namenode01/user/jcarrete/data/mice/des_y3_mice2_source_catalog_IA_mocks'
    ;

    msck repair table scattz_sof_mice_feb3rd_maxmag22_5_partitioned_pq;

Tienen que tener permisos los directorios!!!
Si no, no funciona y no lee los directorios!
NO funciona como tabla externa básicamente!


    CREATE TABLE `scattz_sof_mice_feb3rd_maxmag22_5_part_c`(
  `unique_gal_id` bigint COMMENT 'unique galaxy id', 
  `z_cgal` double COMMENT 'galaxy true redshift', 
  `ra_gal` double COMMENT 'galaxy right ascension (degrees)', 
  `dec_gal` double COMMENT 'galaxy declination (degrees)', 
  `g_sof` double COMMENT 'observed g-magnitude resampled from DES Y3Gold2 sof g magnitude', 
  `r_sof` double COMMENT 'observed g-magnitude resampled from DES Y3Gold2 sof r magnitude', 
  `i_sof` double COMMENT 'observed g-magnitude resampled from DES Y3Gold2 sof i magnitude', 
  `z_sof` double COMMENT 'observed g-magnitude resampled from DES Y3Gold2 sof z magnitude', 
  `z_dnf_mean_sof` float COMMENT 'Photo-z from DNF. Z mean statistic. Dnf run using sof resampled magnitudes', 
  `z_dnf_mc_sof` float COMMENT 'Photo-z from DNF. Z Monte-Carlo statistic. Dnf run using sof resampled magnitudes', 
  `des_asahi_full_g_true` float COMMENT 'observed magnitude in des asahi full g-band', 
  `des_asahi_full_r_true` float COMMENT 'observed magnitude in des asahi full r-band', 
  `des_asahi_full_i_true` float COMMENT 'observed magnitude in des asahi full i-band', 
  `des_asahi_full_z_true` float COMMENT 'observed magnitude in des asahi full z-band', 
  `kappa` float COMMENT 'convergence', 
  `gamma1` float COMMENT 'shear', 
  `gamma2` float COMMENT 'shear', 
  `eps1` double COMMENT 'intrinsic shape', 
  `eps2` double COMMENT 'intrinsic shape'
    )
    PARTITIONED BY (tomographic_bin int COMMENT 'tomographic bin')
    CLUSTERED BY ( 
  unique_gal_id) 
    INTO 16 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.scattz_sof_mice_feb3rd_maxmag22_5_part_c partition(tomographic_bin) select * from jcarrete.scattz_sof_mice_feb3rd_maxmag22_5_partitioned_pq;


    ANALYZE TABLE cosmohub.scattz_sof_mice_feb3rd_maxmag22_5_part_c PARTITION(tomographic_bin) COMPUTE STATISTICS;

    ANALYZE TABLE cosmohub.scattz_sof_mice_feb3rd_maxmag22_5_part_c COMPUTE STATISTICS FOR COLUMNS;