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
;