MIDES Wide Field v5.0

These are the commands to ingest version 5 from Andrea Pocino

CREATE EXTERNAL TABLE `jcarrete.mice2_remap_photometry_sof_desy3_gold2_v5_0_csv`(
  `unique_gal_id` bigint COMMENT 'unique galaxy id', 
  `ra_gal` double COMMENT 'galaxy right ascension (degrees) from MICECATv2.0', 
  `dec_gal` double COMMENT 'galaxy declination (degrees) from MICECATv2.0', 
   kappa double COMMENT '',
   lmhalo double COMMENT '',
   flag_central double COMMENT '',
  `z_cgal` double COMMENT 'galaxy true redshift from MICECATv2.0', 
  `g_sof` double COMMENT 'observed g-magnitude resampled from DES Y3Gold2 sof g magnitude', 
  `r_sof` double COMMENT 'observed r-magnitude resampled from DES Y3Gold2 sof r magnitude', 
  `i_sof` double COMMENT 'observed i-magnitude resampled from DES Y3Gold2 sof i magnitude', 
  `z_sof` double COMMENT 'observed z-magnitude resampled from DES Y3Gold2 sof z magnitude', 
  `g_sof_err` double COMMENT 'error of the resampled g-magnitude', 
  `r_sof_err` double COMMENT 'error of the resampled r-magnitude', 
  `i_sof_err` double COMMENT 'error of the resampled i-magnitude', 
  `z_sof_err` double COMMENT 'error of the resampled z-magnitude', 
   g_sof_magnified double COMMENT '',
   r_sof_magnified double COMMENT '',
   i_sof_magnified double COMMENT '',
   z_sof_magnified double COMMENT '',
  `z_dnf_mean_sof` double COMMENT 'Photo-z from DNF. Z mean statistic. Dnf run using sof resampled magnitudes', 
  `z_dnf_mean_sof_err` double COMMENT 'Photo-z error given by DNF. Dnf run using sof resampled magnitudes', 
  `z_dnf_mc_sof` double COMMENT 'Photo-z from DNF. Z Monte-Carlo statistic. Dnf run using sof resampled magnitudes')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'=',', 
  'serialization.format'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://namenode01/user/jcarrete/data/des/MICE2_resampled/apocino/v5.0/'
;

CREATE EXTERNAL TABLE `cosmohub.mice2_remap_photometry_sof_desy3_gold2_v5_0`(
  `unique_gal_id` bigint COMMENT 'unique galaxy id', 
  `ra_gal` float COMMENT 'galaxy right ascension (degrees) from MICECATv2.0', 
  `dec_gal` float COMMENT 'galaxy declination (degrees) from MICECATv2.0',
  `z_cgal` float COMMENT 'galaxy true redshift from MICECATv2.0', 
  `g_sof` float COMMENT 'observed g-magnitude resampled from DES Y3Gold2 sof g magnitude', 
  `r_sof` float COMMENT 'observed r-magnitude resampled from DES Y3Gold2 sof r magnitude', 
  `i_sof` float COMMENT 'observed i-magnitude resampled from DES Y3Gold2 sof i magnitude', 
  `z_sof` float COMMENT 'observed z-magnitude resampled from DES Y3Gold2 sof z magnitude', 
  `g_sof_err` float COMMENT 'error of the resampled g-magnitude', 
  `r_sof_err` float COMMENT 'error of the resampled r-magnitude', 
  `i_sof_err` float COMMENT 'error of the resampled i-magnitude', 
  `z_sof_err` float COMMENT 'error of the resampled z-magnitude', 
   g_sof_magnified float COMMENT 'magnified resampled sof g magnitude',
   r_sof_magnified float COMMENT 'magnified resampled sof r magnitude',
   i_sof_magnified float COMMENT 'magnified resampled sof i magnitude',
   z_sof_magnified float COMMENT 'magnified resampled sof z magnitude',
  `z_dnf_mean_sof` float COMMENT 'Photo-z from DNF. Z mean statistic. Dnf run using sof resampled magnitudes', 
  `z_dnf_mean_sof_err` float COMMENT 'Photo-z error given by DNF. 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'
)
CLUSTERED BY ( 
  unique_gal_id) 
INTO 64 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.mice2_remap_photometry_sof_desy3_gold2_v5_0
SELECT `unique_gal_id`, `ra_gal`, `dec_gal`, `z_cgal`, `g_sof`, `r_sof`, `i_sof`, `z_sof`, `g_sof_err`, `r_sof_err`, `i_sof_err`, `z_sof_err`, g_sof_magnified, r_sof_magnified, i_sof_magnified,   z_sof_magnified, `z_dnf_mean_sof`, `z_dnf_mean_sof_err`, `z_dnf_mc_sof`
FROM jcarrete.mice2_remap_photometry_sof_desy3_gold2_v5_0_csv
WHERE unique_gal_id IS NOT NULL;