View source for MICE Redmagic v5.1

== Input Data ==

Provided by Martin Crocce at https://www.dropbox.com/s/ven77a149jvadga/MiceRedmagic.tar?dl=0

== Schema ==

Details from https://docs.google.com/document/d/1fkehCzkxWJcZyywjFjSGFuho80uanpzfpxvHJyyuIcE

 CREATE EXTERNAL TABLE jcarrete.mice_redmagic_v5_1_csv (
  id INT COMMENT 'unique_gal_id in MICE2',
  ra FLOAT COMMENT 'right ascension (degrees)',
  dec FLOAT COMMENT 'declination (degrees)',
  refmag FLOAT COMMENT 'z-band abs. magnitude, kcorrected to z=0.1 (for Lum)',
  refmag_err FLOAT COMMENT 'error on abs mag',
  lum FLOAT COMMENT 'z-band luminosity, fraction of L*',
  zredmagic FLOAT COMMENT 'redMaGiC photometric redshift',
  zredmagic_e FLOAT COMMENT 'approximate Gaussian error on photo-z',
  chisq FLOAT COMMENT 'chisq of fit to red sequence template at zredmagic',
  zspec FLOAT COMMENT 'z_cgal in MICE2',
  mag1 FLOAT COMMENT 'resampled SOF g-band mag (g_sof in MIDES Wide Field 6.1)',
  mag2 FLOAT COMMENT 'resampled SOF r-band mag (r_sof in MIDES Wide Field 6.1)',
  mag3 FLOAT COMMENT 'resampled SOF i-band mag (i_sof in MIDES Wide Field 6.1)',
  mag4 FLOAT COMMENT 'resampled SOF z-band mag (z_sof in MIDES Wide Field 6.1)',
  mag1_err FLOAT COMMENT 'g-band mag_err',
  mag2_err FLOAT COMMENT 'r-band mag_err',
  mag3_err FLOAT COMMENT 'i-band mag_err',
  mag4_err FLOAT COMMENT 'z-band mag_err',
  zredmagic_samp1 FLOAT COMMENT 'sample from the redmagic p(z)',
  zredmagic_samp2 FLOAT COMMENT 'sample from the redmagic p(z)',
  zredmagic_samp3 FLOAT COMMENT 'sample from the redmagic p(z)',
  zredmagic_samp4 FLOAT COMMENT 'sample from the redmagic p(z)'
 )
 PARTITIONED BY (
  magnified TINYINT COMMENT '0=unmagnified, 1=magnified',
  type TINYINT COMMENT '0=highdens, 1=higherlum, 2=highlum'
 )
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 LOCATION '/user/jcarrete/data/des/redmagic/'
 ;

 ALTER TABLE jcarrete.mice_redmagic_v5_1_csv ADD PARTITION(magnified='0', type='0');
 ALTER TABLE jcarrete.mice_redmagic_v5_1_csv ADD PARTITION(magnified='0', type='1');
 ALTER TABLE jcarrete.mice_redmagic_v5_1_csv ADD PARTITION(magnified='0', type='2');
 ALTER TABLE jcarrete.mice_redmagic_v5_1_csv ADD PARTITION(magnified='1', type='0');
 ALTER TABLE jcarrete.mice_redmagic_v5_1_csv ADD PARTITION(magnified='1', type='1');
 ALTER TABLE jcarrete.mice_redmagic_v5_1_csv ADD PARTITION(magnified='1', type='2');

Copio los files donde corresponde:

 hdfs dfs -cp /user/tallada/data/redmagic/magnified=0/type=0/* /user/jcarrete/data/des/redmagic/magnified=0/type=0/
 hdfs dfs -cp /user/tallada/data/redmagic/magnified=0/type=1/* /user/jcarrete/data/des/redmagic/magnified=0/type=1/
 hdfs dfs -cp /user/tallada/data/redmagic/magnified=0/type=2/* /user/jcarrete/data/des/redmagic/magnified=0/type=2/  

 hdfs dfs -cp /user/tallada/data/redmagic/magnified=1/type=0/* /user/jcarrete/data/des/redmagic/magnified=1/type=0/
 hdfs dfs -cp /user/tallada/data/redmagic/magnified=1/type=1/* /user/jcarrete/data/des/redmagic/magnified=1/type=1/
 hdfs dfs -cp /user/tallada/data/redmagic/magnified=1/type=2/* /user/jcarrete/data/des/redmagic/magnified=1/type=2/

Y ahora sí que funciona el SELECT!

 CREATE TABLE cosmohub.mice_redmagic_v5_1 (
  coadd_objects_id INT COMMENT 'unique_gal_id in MICE2',
  ra FLOAT COMMENT 'right ascension (degrees)',
  dec FLOAT COMMENT 'declination (degrees)',
  refmag FLOAT COMMENT 'z-band abs. magnitude, kcorrected to z=0.1 (for Lum)',
  refmag_err FLOAT COMMENT 'error on abs mag',
  lum FLOAT COMMENT 'z-band luminosity, fraction of L*',
  zredmagic FLOAT COMMENT 'redMaGiC photometric redshift',
  zredmagic_e FLOAT COMMENT 'approximate Gaussian error on photo-z',
  chisq FLOAT COMMENT 'chisq of fit to red sequence template at zredmagic',
  zspec FLOAT COMMENT 'z_cgal in MICE2',
  mag1 FLOAT COMMENT 'resampled SOF g-band mag (g_sof in MIDES Wide Field 6.1)',
  mag2 FLOAT COMMENT 'resampled SOF r-band mag (r_sof in MIDES Wide Field 6.1)',
  mag3 FLOAT COMMENT 'resampled SOF i-band mag (i_sof in MIDES Wide Field 6.1)',
  mag4 FLOAT COMMENT 'resampled SOF z-band mag (z_sof in MIDES Wide Field 6.1)',
  mag1_err FLOAT COMMENT 'g-band mag_err',
  mag2_err FLOAT COMMENT 'r-band mag_err',
  mag3_err FLOAT COMMENT 'i-band mag_err',
  mag4_err FLOAT COMMENT 'z-band mag_err',
  zredmagic_samp1 FLOAT COMMENT 'sample from the redmagic p(z)',
  zredmagic_samp2 FLOAT COMMENT 'sample from the redmagic p(z)',
  zredmagic_samp3 FLOAT COMMENT 'sample from the redmagic p(z)',
  zredmagic_samp4 FLOAT COMMENT 'sample from the redmagic p(z)'
 )
 PARTITIONED BY (
  magnified TINYINT COMMENT '0=unmagnified, 1=magnified',
  type TINYINT COMMENT '0=highdens, 1=higherlum, 2=highlum'
 )
 CLUSTERED BY (coadd_objects_id)
 SORTED BY (coadd_objects_id)
 INTO 4 BUCKETS
 STORED AS ORC
 ;


 INSERT OVERWRITE TABLE cosmohub.mice_redmagic_v5_1 PARTITION(magnified, type)
 SELECT *
 FROM jcarrete.mice_redmagic_v5_1_csv
 WHERE id IS NOT NULL
 ;