Jcarrete at 19:29, 24 February 2020

2020-02-24T19:29:47Z

Tallada: Created page with "== 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/1..."

2020-01-24T14:49:02Z

Created page with "== 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/1..."

New page

== 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
;

@@ Line 7: / Line 7: @@
 Details from https://docs.google.com/document/d/1fkehCzkxWJcZyywjFjSGFuho80uanpzfpxvHJyyuIcE
-  CREATE EXTERNAL TABLE tallada.mice_redmagic_v5_1_csv (
+  CREATE EXTERNAL TABLE jcarrete.mice_redmagic_v5_1_csv (
-   id INT COMMENT 'unique_gal_id in MICE2',
+  id INT COMMENT 'unique_gal_id in MICE2',
-   ra FLOAT COMMENT 'declination (degrees)',
+  ra FLOAT COMMENT 'right ascension (degrees)',
-   refmag FLOAT COMMENT 'z-band abs. magnitude, kcorrected to z=0.1 (for Lum)',
+  dec FLOAT COMMENT 'declination (degrees)',
-   refmag_err FLOAT COMMENT 'error on abs mag',
+  refmag FLOAT COMMENT 'z-band abs. magnitude, kcorrected to z=0.1 (for Lum)',
-   lum FLOAT COMMENT 'z-band luminosity, fraction of L*',
+  refmag_err FLOAT COMMENT 'error on abs mag',
-   zredmagic FLOAT COMMENT 'redMaGiC photometric redshift',
+  lum FLOAT COMMENT 'z-band luminosity, fraction of L*',
-   zredmagic_e FLOAT COMMENT 'approximate Gaussian error on photo-z',
+  zredmagic FLOAT COMMENT 'redMaGiC photometric redshift',
-   chisq FLOAT COMMENT 'chisq of fit to red sequence template at zredmagic',
+  zredmagic_e FLOAT COMMENT 'approximate Gaussian error on photo-z',
-   zspec FLOAT COMMENT 'z_cgal in MICE2',
+  chisq FLOAT COMMENT 'chisq of fit to red sequence template at zredmagic',
-   mag1 FLOAT COMMENT 'resampled SOF g-band mag (g_sof in MIDES Wide Field 6.1)',
+  zspec FLOAT COMMENT 'z_cgal in MICE2',
-   mag2 FLOAT COMMENT 'resampled SOF r-band mag (r_sof in MIDES Wide Field 6.1)',
+  mag1 FLOAT COMMENT 'resampled SOF g-band mag (g_sof in MIDES Wide Field 6.1)',
-   mag3 FLOAT COMMENT 'resampled SOF i-band mag (i_sof in MIDES Wide Field 6.1)',
+  mag2 FLOAT COMMENT 'resampled SOF r-band mag (r_sof in MIDES Wide Field 6.1)',
-   mag4 FLOAT COMMENT 'resampled SOF z-band mag (z_sof in MIDES Wide Field 6.1)',
+  mag3 FLOAT COMMENT 'resampled SOF i-band mag (i_sof in MIDES Wide Field 6.1)',
-   mag1_err FLOAT COMMENT 'g-band mag_err',
+  mag4 FLOAT COMMENT 'resampled SOF z-band mag (z_sof in MIDES Wide Field 6.1)',
-   mag2_err FLOAT COMMENT 'r-band mag_err',
+  mag1_err FLOAT COMMENT 'g-band mag_err',
-   mag3_err FLOAT COMMENT 'i-band mag_err',
+  mag2_err FLOAT COMMENT 'r-band mag_err',
-   mag4_err FLOAT COMMENT 'z-band mag_err',
+  mag3_err FLOAT COMMENT 'i-band mag_err',
-   zredmagic_samp1 FLOAT COMMENT 'sample from the redmagic p(z)',
+  mag4_err FLOAT COMMENT 'z-band mag_err',
-   zredmagic_samp2 FLOAT COMMENT 'sample from the redmagic p(z)',
+  zredmagic_samp1 FLOAT COMMENT 'sample from the redmagic p(z)',
-   zredmagic_samp3 FLOAT COMMENT 'sample from the redmagic p(z)',
+  zredmagic_samp2 FLOAT COMMENT 'sample from the redmagic p(z)',
-   zredmagic_samp4 FLOAT COMMENT 'sample from the redmagic p(z)'
+  zredmagic_samp3 FLOAT COMMENT 'sample from the redmagic p(z)',
+  )
   PARTITIONED BY (
-   magnified TINYINT COMMENT '0=unmagnified, 1=magnified',
+  magnified TINYINT COMMENT '0=unmagnified, 1=magnified',
-   type TINYINT COMMENT '0=highdens, 1=higherlum, 2=highlum'
+  type TINYINT COMMENT '0=highdens, 1=higherlum, 2=highlum'
+  )
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ','
-  LOCATION '/user/tallada/data/redmagic/'
+  LOCATION '/user/jcarrete/data/des/redmagic/'
+  ;
   CREATE TABLE cosmohub.mice_redmagic_v5_1 (
-   id INT COMMENT 'unique_gal_id in MICE2',
+  coadd_objects_id INT COMMENT 'unique_gal_id in MICE2',
-   ra FLOAT COMMENT 'declination (degrees)',
+  ra FLOAT COMMENT 'right ascension (degrees)',
-   refmag FLOAT COMMENT 'z-band abs. magnitude, kcorrected to z=0.1 (for Lum)',
+  dec FLOAT COMMENT 'declination (degrees)',
-   refmag_err FLOAT COMMENT 'error on abs mag',
+  refmag FLOAT COMMENT 'z-band abs. magnitude, kcorrected to z=0.1 (for Lum)',
-   lum FLOAT COMMENT 'z-band luminosity, fraction of L*',
+  refmag_err FLOAT COMMENT 'error on abs mag',
-   zredmagic FLOAT COMMENT 'redMaGiC photometric redshift',
+  lum FLOAT COMMENT 'z-band luminosity, fraction of L*',
-   zredmagic_e FLOAT COMMENT 'approximate Gaussian error on photo-z',
+  zredmagic FLOAT COMMENT 'redMaGiC photometric redshift',
-   chisq FLOAT COMMENT 'chisq of fit to red sequence template at zredmagic',
+  zredmagic_e FLOAT COMMENT 'approximate Gaussian error on photo-z',
-   zspec FLOAT COMMENT 'z_cgal in MICE2',
+  chisq FLOAT COMMENT 'chisq of fit to red sequence template at zredmagic',
-   mag1 FLOAT COMMENT 'resampled SOF g-band mag (g_sof in MIDES Wide Field 6.1)',
+  zspec FLOAT COMMENT 'z_cgal in MICE2',
-   mag2 FLOAT COMMENT 'resampled SOF r-band mag (r_sof in MIDES Wide Field 6.1)',
+  mag1 FLOAT COMMENT 'resampled SOF g-band mag (g_sof in MIDES Wide Field 6.1)',
-   mag3 FLOAT COMMENT 'resampled SOF i-band mag (i_sof in MIDES Wide Field 6.1)',
+  mag2 FLOAT COMMENT 'resampled SOF r-band mag (r_sof in MIDES Wide Field 6.1)',
-   mag4 FLOAT COMMENT 'resampled SOF z-band mag (z_sof in MIDES Wide Field 6.1)',
+  mag3 FLOAT COMMENT 'resampled SOF i-band mag (i_sof in MIDES Wide Field 6.1)',
-   mag1_err FLOAT COMMENT 'g-band mag_err',
+  mag4 FLOAT COMMENT 'resampled SOF z-band mag (z_sof in MIDES Wide Field 6.1)',
-   mag2_err FLOAT COMMENT 'r-band mag_err',
+  mag1_err FLOAT COMMENT 'g-band mag_err',
-   mag3_err FLOAT COMMENT 'i-band mag_err',
+  mag2_err FLOAT COMMENT 'r-band mag_err',
-   mag4_err FLOAT COMMENT 'z-band mag_err',
+  mag3_err FLOAT COMMENT 'i-band mag_err',
-   zredmagic_samp1 FLOAT COMMENT 'sample from the redmagic p(z)',
+  mag4_err FLOAT COMMENT 'z-band mag_err',
-   zredmagic_samp2 FLOAT COMMENT 'sample from the redmagic p(z)',
+  zredmagic_samp1 FLOAT COMMENT 'sample from the redmagic p(z)',
-   zredmagic_samp3 FLOAT COMMENT 'sample from the redmagic p(z)',
+  zredmagic_samp2 FLOAT COMMENT 'sample from the redmagic p(z)',
-   zredmagic_samp4 FLOAT COMMENT 'sample from the redmagic p(z)'
+  zredmagic_samp3 FLOAT COMMENT 'sample from the redmagic p(z)',
+  )
   PARTITIONED BY (
-   magnified TINYINT COMMENT '0=unmagnified, 1=magnified',
+  magnified TINYINT COMMENT '0=unmagnified, 1=magnified',
-   type TINYINT COMMENT '0=highdens, 1=higherlum, 2=highlum'
+  type TINYINT COMMENT '0=highdens, 1=higherlum, 2=highlum'
+  )
-  CLUSTERED BY (id)
+  CLUSTERED BY (coadd_objects_id)
-  SORTED BY (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 tallada.mice_redmagic_v5_1_csv
+  FROM jcarrete.mice_redmagic_v5_1_csv
   WHERE id IS NOT NULL
+  ;

MICE Redmagic v5.1 - Revision history

Jcarrete at 19:29, 24 February 2020

Tallada: Created page with "== 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/1..."