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 tallada.mice_redmagic_v5_1_csv (
  id INT COMMENT 'unique_gal_id in MICE2',
  ra 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/tallada/data/redmagic/'
;

CREATE TABLE cosmohub.mice_redmagic_v5_1 (
  id INT COMMENT 'unique_gal_id in MICE2',
  ra 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 (id)
SORTED BY (id)
INTO 4 BUCKETS
STORED AS ORC
;

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