VIPERS

Raw data

Downloaded by Jorge at

/nfs/astro/jcarrete/sandbox/VIPERS/
├── photometry
│   ├── VIPERS_W1_PHOT_PDR2.fits
│   └── VIPERS_W4_PHOT_PDR2.fits
└── spectroscopy
    ├── VIPERS_W1_SPECTRO_PDR2.fits
    └── VIPERS_W4_SPECTRO_PDR2.fits

Conversion to parquet

files =  [
    '/nfs/astro/jcarrete/sandbox/VIPERS/photometry/VIPERS_W1_PHOT_PDR2.fits',
    '/nfs/astro/jcarrete/sandbox/VIPERS/photometry/VIPERS_W4_PHOT_PDR2.fits',
    '/nfs/astro/jcarrete/sandbox/VIPERS/spectroscopy/VIPERS_W1_SPECTRO_PDR2.fits',
    '/nfs/astro/jcarrete/sandbox/VIPERS/spectroscopy/VIPERS_W4_SPECTRO_PDR2.fits',
]

output_path = '/user/tallada/data/'

for f in files:
    df = spark.read.format("fits").option("hdu", 1).load(
        "file://" + f
    )
    df.write.parquet(os.path.join(output_path, os.path.basename(f)))

External tables

CREATE EXTERNAL TABLE tallada.vipers_phot_w1 (
    `id_IAU` STRING,
    `num` STRING,
    `id_T07` INT,
    `alpha` DOUBLE,
    `delta` DOUBLE,
    `selmag` FLOAT,
    `errselmag` FLOAT,
    `u` FLOAT,
    `g` FLOAT,
    `r` FLOAT,
    `i` FLOAT,
    `z` FLOAT,
    `erru` FLOAT,
    `errg` FLOAT,
    `errr` FLOAT,
    `erri` FLOAT,
    `errz` FLOAT,
    `u_T07` FLOAT,
    `g_T07` FLOAT,
    `r_T07` FLOAT,
    `i_T07` FLOAT,
    `iy_T07` FLOAT,
    `z_T07` FLOAT,
    `erru_T07` FLOAT,
    `errg_T07` FLOAT,
    `errr_T07` FLOAT,
    `erri_T07` FLOAT,
    `erriy_T07` FLOAT,
    `errz_T07` FLOAT,
    `K_video` FLOAT,
    `Ks` FLOAT,
    `FUV` FLOAT,
    `NUV` FLOAT,
    `errK_video` FLOAT,
    `errKs` FLOAT,
    `errFUV` FLOAT,
    `errNUV` FLOAT,
    `DeltaUG` FLOAT,
    `DeltaGR` FLOAT,
    `DeltaRI` FLOAT,
    `ebv` FLOAT,
    `r2` FLOAT,
    `r2_T07` FLOAT,
    `classFlag` INT,
    `photoMask` INT,
    `spectroMask` INT
)
STORED AS PARQUET
LOCATION '/user/tallada/data/VIPERS_W1_PHOT_PDR2.fits'
;

CREATE EXTERNAL TABLE tallada.vipers_phot_w4 (
    `id_IAU` STRING,
    `num` STRING,
    `id_T07` INT,
    `alpha` DOUBLE,
    `delta` DOUBLE,
    `selmag` FLOAT,
    `errselmag` FLOAT,
    `u` FLOAT,
    `g` FLOAT,
    `r` FLOAT,
    `i` FLOAT,
    `z` FLOAT,
    `erru` FLOAT,
    `errg` FLOAT,
    `errr` FLOAT,
    `erri` FLOAT,
    `errz` FLOAT,
    `u_T07` FLOAT,
    `g_T07` FLOAT,
    `r_T07` FLOAT,
    `i_T07` FLOAT,
    `iy_T07` FLOAT,
    `z_T07` FLOAT,
    `erru_T07` FLOAT,
    `errg_T07` FLOAT,
    `errr_T07` FLOAT,
    `erri_T07` FLOAT,
    `erriy_T07` FLOAT,
    `errz_T07` FLOAT,
    `Ks` FLOAT,
    `FUV` FLOAT,
    `NUV` FLOAT,
    `errKs` FLOAT,
    `errFUV` FLOAT,
    `errNUV` FLOAT,
    `DeltaUG` FLOAT,
    `DeltaGR` FLOAT,
    `DeltaRI` FLOAT,
    `ebv` FLOAT,
    `r2` FLOAT,
    `r2_T07` FLOAT,
    `classFlag` INT,
    `photoMask` INT,
    `spectroMask` INT
)
STORED AS PARQUET
LOCATION '/user/tallada/data/VIPERS_W4_PHOT_PDR2.fits'
;

CREATE EXTERNAL TABLE tallada.vipers_spec_w1 (
    `id_IAU` STRING,
    `num` STRING,
    `alpha` DOUBLE,
    `delta` DOUBLE,
    `selmag` FLOAT,
    `errselmag` FLOAT,
    `pointing` STRING,
    `quadrant` INT,
    `zspec` FLOAT,
    `zflg` FLOAT,
    `norm` FLOAT,
    `epoch` INT,
    `photoMask` INT,
    `tsr` FLOAT,
    `ssr` FLOAT,
    `classFlag` INT
)
STORED AS PARQUET
LOCATION '/user/tallada/data/VIPERS_W1_SPECTRO_PDR2.fits'
;

CREATE EXTERNAL TABLE tallada.vipers_spec_w4 (
    `id_IAU` STRING,
    `num` STRING,
    `alpha` DOUBLE,
    `delta` DOUBLE,
    `selmag` FLOAT,
    `errselmag` FLOAT,
    `pointing` STRING,
    `quadrant` INT,
    `zspec` FLOAT,
    `zflg` FLOAT,
    `norm` FLOAT,
    `epoch` INT,
    `photoMask` INT,
    `tsr` FLOAT,
    `ssr` FLOAT,
    `classFlag` INT
)
STORED AS PARQUET
LOCATION '/user/tallada/data/VIPERS_W4_SPECTRO_PDR2.fits'
;

Internal tables

CREATE TABLE cosmohub.vipers_photometry_pdr2 (
    `id_IAU` STRING,
    `num` INT,
    `id_T07` INT,
    `alpha` DOUBLE,
    `delta` DOUBLE,
    `selmag` FLOAT,
    `errselmag` FLOAT,
    `u` FLOAT,
    `g` FLOAT,
    `r` FLOAT,
    `i` FLOAT,
    `z` FLOAT,
    `erru` FLOAT,
    `errg` FLOAT,
    `errr` FLOAT,
    `erri` FLOAT,
    `errz` FLOAT,
    `u_T07` FLOAT,
    `g_T07` FLOAT,
    `r_T07` FLOAT,
    `i_T07` FLOAT,
    `iy_T07` FLOAT,
    `z_T07` FLOAT,
    `erru_T07` FLOAT,
    `errg_T07` FLOAT,
    `errr_T07` FLOAT,
    `erri_T07` FLOAT,
    `erriy_T07` FLOAT,
    `errz_T07` FLOAT,
    `K_video` FLOAT,
    `Ks` FLOAT,
    `FUV` FLOAT,
    `NUV` FLOAT,
    `errK_video` FLOAT,
    `errKs` FLOAT,
    `errFUV` FLOAT,
    `errNUV` FLOAT,
    `DeltaUG` FLOAT,
    `DeltaGR` FLOAT,
    `DeltaRI` FLOAT,
    `ebv` FLOAT,
    `r2` FLOAT,
    `r2_T07` FLOAT,
    `classFlag` INT,
    `photoMask` INT,
    `spectroMask` INT
)
CLUSTERED BY (num)
SORTED BY (num ASC)
INTO 2 BUCKETS
STORED AS ORC
;

CREATE TABLE cosmohub.vipers_spectroscopy_pdr2 (
    `id_IAU` STRING,
    `num` INT,
    `alpha` DOUBLE,
    `delta` DOUBLE,
    `selmag` FLOAT,
    `errselmag` FLOAT,
    `pointing` STRING,
    `quadrant` INT,
    `zspec` FLOAT,
    `zflg` FLOAT,
    `norm` FLOAT,
    `epoch` INT,
    `photoMask` INT,
    `tsr` FLOAT,
    `ssr` FLOAT,
    `classFlag` INT
)
CLUSTERED BY (num)
SORTED BY (num ASC)
INTO 2 BUCKETS
STORED AS ORC
;

Insert data

INSERT OVERWRITE TABLE cosmohub.vipers_photometry_pdr2
SELECT
id_iau, CAST(num AS INT), id_t07, alpha, delta, selmag, errselmag, u, g, r, i, z, erru, errg, errr, erri, errz, u_t07, g_t07, r_t07, i_t07, iy_t07, z_t07, erru_t07, errg_t07, errr_t07, erri_t07, erriy_t07, errz_t07, k_video, ks, fuv, nuv, errk_video, errks, errfuv, errnuv, deltaug, deltagr, deltari, ebv, r2, r2_t07, classflag, photomask, spectromask
FROM tallada.vipers_phot_w1
UNION ALL
SELECT 
id_iau, CAST(num AS INT), id_t07, alpha, delta, selmag, errselmag, u, g, r, i, z, erru, errg, errr, erri, errz, u_t07, g_t07, r_t07, i_t07, iy_t07, z_t07, erru_t07, errg_t07, errr_t07, erri_t07, erriy_t07, errz_t07, NULL, ks, fuv, nuv, NULL, errks, errfuv, errnuv, deltaug, deltagr, deltari, ebv, r2, r2_t07, classflag, photomask, spectromask
FROM tallada.vipers_phot_w4
;

INSERT OVERWRITE TABLE cosmohub.vipers_spectroscopy_pdr2
SELECT
id_iau, CAST(num AS INT), alpha, delta, selmag, errselmag, pointing, quadrant, zspec, zflg, norm, epoch, photomask, tsr, ssr, classflag
FROM tallada.vipers_spec_w1
UNION ALL
SELECT
id_iau, CAST(num AS INT), alpha, delta, selmag, errselmag, pointing, quadrant, zspec, zflg, norm, epoch, photomask, tsr, ssr, classflag
FROM tallada.vipers_spec_w4
;

ANALYZE TABLE cosmohub.vipers_photometry_pdr2 COMPUTE STATISTICS FOR COLUMNS;
ANALYZE TABLE cosmohub.vipers_spectroscopy_pdr2 COMPUTE STATISTICS FOR COLUMNS;

Comments

ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE id_IAU id_IAU STRING COMMENT 'VIPERS object name, according to IAU standards';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE num num INT COMMENT 'Internal id number';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE id_T07 id_T07 INT COMMENT 'T07 object ID';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE alpha alpha DOUBLE COMMENT 'J2000 Right Ascension';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE delta delta DOUBLE COMMENT 'J2000 Declination';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE selmag selmag FLOAT COMMENT 'i_AB selection magnitude';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errselmag errselmag FLOAT COMMENT 'i_AB selection magnitude error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE u u FLOAT COMMENT 'CFHTLS T0005 u mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE g g FLOAT COMMENT 'CFHTLS T0005 g mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE r r FLOAT COMMENT 'CFHTLS T0005 r mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE i i FLOAT COMMENT 'CFHTLS T0005 i mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE z z FLOAT COMMENT 'CFHTLS T0005 z mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE erru erru FLOAT COMMENT 'CFHTLS T0005 u mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errg errg FLOAT COMMENT 'CFHTLS T0005 g mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errr errr FLOAT COMMENT 'CFHTLS T0005 r mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE erri erri FLOAT COMMENT 'CFHTLS T0005 i mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errz errz FLOAT COMMENT 'CFHTLS T0005 z mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE u_T07 u_T07 FLOAT COMMENT 'CFHTLS T0007 u mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE g_T07 g_T07 FLOAT COMMENT 'CFHTLS T0007 g mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE r_T07 r_T07 FLOAT COMMENT 'CFHTLS T0007 r mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE i_T07 i_T07 FLOAT COMMENT 'CFHTLS T0007 i mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE iy_T07 iy_T07 FLOAT COMMENT 'CFHTLS T0007 iy mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE z_T07 z_T07 FLOAT COMMENT 'CFHTLS T0007 z mag corrected for galactic extinction';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE erru_T07 erru_T07 FLOAT COMMENT 'CFHTLS T0007 u mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errg_T07 errg_T07 FLOAT COMMENT 'CFHTLS T0007 g mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errr_T07 errr_T07 FLOAT COMMENT 'CFHTLS T0007 r mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE erri_T07 erri_T07 FLOAT COMMENT 'CFHTLS T0007 i mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE erriy_T07 erriy_T07 FLOAT COMMENT 'CFHTLS T0007 iy mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errz_T07 errz_T07 FLOAT COMMENT 'CFHTLS T0007 z mag error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE K_video K_video FLOAT COMMENT 'VIDEO K magnitude';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE Ks Ks FLOAT COMMENT 'WIRCam Ks magnitude';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE FUV FUV FLOAT COMMENT 'GALEX FUV magnitude';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE NUV NUV FLOAT COMMENT 'GALEX NUV magnitude';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errK_video errK_video FLOAT COMMENT 'VIDEO K magnitude error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errKs errKs FLOAT COMMENT 'WIRCam Ks magnitude error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errFUV errFUV FLOAT COMMENT 'GALEX FUV magnitude error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE errNUV errNUV FLOAT COMMENT 'GALEX NUV magnitude error';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE DeltaUG DeltaUG FLOAT COMMENT 'U-G tile color offset';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE DeltaGR DeltaGR FLOAT COMMENT 'G-R tile color offset';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE DeltaRI DeltaRI FLOAT COMMENT 'R-I tile color offset';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE ebv ebv FLOAT COMMENT 'Extinction factor E(B-V)';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE r2 r2 FLOAT COMMENT 'CFHTLS T0005 half light radius (pixels)';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE r2_T07 r2_T07 FLOAT COMMENT 'CFHTLS T0007 half light radius (pixels)';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE classFlag classFlag INT COMMENT 'Selection flag';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE photoMask photoMask INT COMMENT 'Photometric mask flag: 1 if the object is inside';
ALTER TABLE cosmohub.vipers_photometry_pdr2 CHANGE spectroMask spectroMask INT COMMENT 'Spectroscopic mask flag: 1 if the object is inside';

ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE id_IAU id_IAU STRING COMMENT 'VIPERS object name, according to IAU standards';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE num num INT COMMENT 'Internal id number';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE alpha alpha DOUBLE COMMENT 'J2000 Right Ascension';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE delta delta DOUBLE COMMENT 'J2000 Declination';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE selmag selmag FLOAT COMMENT 'i_AB selection magnitude';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE errselmag errselmag FLOAT COMMENT 'i_AB selection magnitude error';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE pointing pointing STRING COMMENT 'VIPERS pointing';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE quadrant quadrant INT COMMENT 'VIMOS quadrant';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE zspec zspec FLOAT COMMENT 'Spectroscopic redshift';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE zflg zflg FLOAT COMMENT 'Spectroscopic redshift quality flag';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE norm norm FLOAT COMMENT 'Normalization factor';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE epoch epoch INT COMMENT 'Observing epoch';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE photoMask photoMask INT COMMENT 'Flag indicating whether the object falls within the photometric mask';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE tsr tsr FLOAT COMMENT 'The Target Sampling Rate';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE ssr ssr FLOAT COMMENT 'The Spectroscopic Success Rate';
ALTER TABLE cosmohub.vipers_spectroscopy_pdr2 CHANGE classFlag classFlag INT COMMENT 'Selection flag';