Euclid mock production v1.7.17 deep correct

From Public PIC Wiki
Revision as of 15:13, 19 December 2019 by Jcarrete (talk | contribs) (Created page with "Esta es la tabla externa que contiene los datos de los FITS del DEEP SELF calibration field pero en csv. <pre> CREATE EXTERNAL TABLE `jcarrete.flagship_1_6_20_csv`( SOURCE_...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Esta es la tabla externa que contiene los datos de los FITS del DEEP SELF calibration field pero en csv. 

CREATE EXTERNAL TABLE `jcarrete.flagship_1_6_20_csv`(
  SOURCE_ID bigint,
  RA float,
  DEC float,
  RA_MAG float,
  DEC_MAG float,
  Z_OBS float,
  TU_MAG_R01_SDSS_ABS float,
  TU_MAG_R01_SDSS float,
  SED_TEMPLATE float,
  EXT_LAW smallint,
  EBV float,
  HALPHA_LOGFLAM_EXT float,
  HBETA_LOGFLAM_EXT float,
  O2_LOGFLAM_EXT float,
  O3_LOGFLAM_EXT float,
  N2_LOGFLAM_EXT float,
  S2_LOGFLAM_EXT float,
  BULGE_FRACTION float,
  BULGE_LENGTH float,
  DISK_LENGTH float,
  DISK_AXIS_RATIO float,
  DISK_ANGLE float,
  KAPPA float,
  GAMMA1 float,
  GAMMA2 float,
  AV float,
  TU_FNU_VIS float,
  TU_FNU_Y_NISP float,
  TU_FNU_J_NISP float,
  TU_FNU_H_NISP float,
  TU_FNU_G_DECAM float,
  TU_FNU_R_DECAM float,
  TU_FNU_I_DECAM float,
  TU_FNU_Z_DECAM float,
  TU_FNU_U_MEGACAM float,
  TU_FNU_R_MEGACAM float,
  TU_FNU_G_JPCAM float,
  TU_FNU_I_PANSTARRS float,
  TU_FNU_Z_PANSTARRS float,
  TU_FNU_Z_HSC float,
  TU_FNU_G_GAIA float,
  TU_FNU_BP_GAIA float,
  TU_FNU_RP_GAIA float,
  TU_FNU_U_LSST float,
  TU_FNU_G_LSST float,
  TU_FNU_R_LSST float,
  TU_FNU_I_LSST float,
  TU_FNU_Z_LSST float,
  TU_FNU_Y_LSST float,
  TU_FNU_U_KIDS float,
  TU_FNU_G_KIDS float,
  TU_FNU_R_KIDS float,
  TU_FNU_I_KIDS float,
  TU_FNU_J_2MASS float,
  TU_FNU_H_2MASS float,
  TU_FNU_KS_2MASS float
)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'=',', 
  'serialization.format'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://namenode01/user/jcarrete/data/euclid/deep_calib_fits_to_csv'


Esta tabla es la misma que tallada.flagship_1_6_20_p pero con los flujos correctos! 

CREATE EXTERNAL TABLE `jcarrete.flagship_SC456_1_7_17_deep_from_1_6_20`(
  `kind` bigint, 
  `luminosity_r01` double, 
  `halo_x` double, 
  `halo_y` double, 
  `halo_z` double, 
  `halo_vx` double, 
  `halo_vy` double, 
  `halo_vz` double, 
  `halo_r` double, 
  `true_redshift_halo` double, 
  `halo_lm` double, 
  `halo_n_sats` bigint, 
  `n_gals` bigint, 
  `abs_mag_r01` double, 
  `abs_mag_r01_evolved` double, 
  `luminosity_r01_evolved` double, 
  `gr_restframe` double, 
  `color_kind` int, 
  `x_gal` double, 
  `y_gal` double, 
  `z_gal` double, 
  `r_gal` double, 
  `true_redshift_gal` double, 
  `ra_gal` double, 
  `dec_gal` double, 
  `hpix_nest` bigint, 
  `vx_gal` double, 
  `vy_gal` double, 
  `vz_gal` double, 
  `vrad_gal` double, 
  `delta_r` double, 
  `observed_redshift_gal` double, 
  `sed_ke` double, 
  `gr_cosmos` double, 
  `sed_cosmos` double, 
  `ext_curve_cosmos` double, 
  `ebv_cosmos` double, 
  `is_within_cosmos` boolean, 
  `cosmos_distance` double, 
  `abs_mag_uv_dereddened` double, 
  `log_ml_r01` double, 
  `log_stellar_mass` double, 
  `metallicity` double, 
  `log_sfr` double, 
  `logf_halpha_ext` double, 
  `logf_halpha` double, 
  `halpha_scatter` double, 
  `loglum_halpha` double, 
  `loglum_halpha_ext` double, 
  `logf_dummy` double, 
  `z_dummy` double, 
  `logf_halpha_model1_ext` double, 
  `logf_halpha_model1` double, 
  `loglum_halpha_model1_ext` double, 
  `loglum_halpha_model1` double, 
  `logf_halpha_model3_ext` double, 
  `logf_halpha_model3` double, 
  `loglum_halpha_model3_ext` double, 
  `loglum_halpha_model3` double, 
  `logf_hbeta_model1_ext` double, 
  `logf_hbeta_model1` double, 
  `logf_o2_model1_ext` double, 
  `logf_o2_model1` double, 
  `logf_n2_model1_ext` double, 
  `logf_n2_model1` double, 
  `logf_o3_model1_ext` double, 
  `logf_o3_model1` double, 
  `logf_s2_model1_ext` double, 
  `logf_s2_model1` double, 
  `logf_hbeta_model3_ext` double, 
  `logf_hbeta_model3` double, 
  `logf_o2_model3_ext` double, 
  `logf_o2_model3` double, 
  `logf_n2_model3_ext` double, 
  `logf_n2_model3` double, 
  `logf_o3_model3_ext` double, 
  `logf_o3_model3` double, 
  `logf_s2_model3_ext` double, 
  `logf_s2_model3` double, 
  `2mass_h` double, 
  `2mass_j` double, 
  `2mass_ks` double, 
  `blanco_decam_g` double, 
  `blanco_decam_i` double, 
  `blanco_decam_r` double, 
  `blanco_decam_z` double, 
  `cfht_megacam_r` double, 
  `cfht_megacam_u` double, 
  `euclid_nisp_h` double, 
  `euclid_nisp_j` double, 
  `euclid_nisp_y` double, 
  `euclid_vis` double, 
  `gaia_bp` double, 
  `gaia_g` double, 
  `gaia_rp` double, 
  `jst_jpcam_g` double, 
  `kids_g` double, 
  `kids_i` double, 
  `kids_r` double, 
  `kids_u` double, 
  `lsst_g` double, 
  `lsst_i` double, 
  `lsst_r` double, 
  `lsst_u` double, 
  `lsst_y` double, 
  `lsst_z` double, 
  `pan_starrs_i` double, 
  `pan_starrs_z` double, 
  `sdss_r01` double, 
  `subaru_hsc_z` double, 
  `l_gal` double, 
  `b_gal` double, 
  `mw_extinction` double, 
  `2mass_h_odonnell_ext` double, 
  `2mass_j_odonnell_ext` double, 
  `2mass_ks_odonnell_ext` double, 
  `blanco_decam_g_odonnell_ext` double, 
  `blanco_decam_i_odonnell_ext` double, 
  `blanco_decam_r_odonnell_ext` double, 
  `blanco_decam_z_odonnell_ext` double, 
  `cfht_megacam_r_odonnell_ext` double, 
  `cfht_megacam_u_odonnell_ext` double, 
  `euclid_nisp_h_odonnell_ext` double, 
  `euclid_nisp_j_odonnell_ext` double, 
  `euclid_nisp_y_odonnell_ext` double, 
  `euclid_vis_odonnell_ext` double, 
  `gaia_bp_odonnell_ext` double, 
  `gaia_g_odonnell_ext` double, 
  `gaia_rp_odonnell_ext` double, 
  `jst_jpcam_g_odonnell_ext` double, 
  `kids_g_odonnell_ext` double, 
  `kids_i_odonnell_ext` double, 
  `kids_r_odonnell_ext` double, 
  `kids_u_odonnell_ext` double, 
  `lsst_g_odonnell_ext` double, 
  `lsst_i_odonnell_ext` double, 
  `lsst_r_odonnell_ext` double, 
  `lsst_u_odonnell_ext` double, 
  `lsst_y_odonnell_ext` double, 
  `lsst_z_odonnell_ext` double, 
  `pan_starrs_i_odonnell_ext` double, 
  `pan_starrs_z_odonnell_ext` double, 
  `sdss_r01_odonnell_ext` double, 
  `subaru_hsc_z_odonnell_ext` double, 
  `2mass_h_el_model1_odonnell_ext` double, 
  `2mass_h_el_model3_odonnell_ext` double, 
  `2mass_j_el_model1_odonnell_ext` double, 
  `2mass_j_el_model3_odonnell_ext` double, 
  `2mass_ks_el_model1_odonnell_ext` double, 
  `2mass_ks_el_model3_odonnell_ext` double, 
  `blanco_decam_g_el_model1_odonnell_ext` double, 
  `blanco_decam_g_el_model3_odonnell_ext` double, 
  `blanco_decam_i_el_model1_odonnell_ext` double, 
  `blanco_decam_i_el_model3_odonnell_ext` double, 
  `blanco_decam_r_el_model1_odonnell_ext` double, 
  `blanco_decam_r_el_model3_odonnell_ext` double, 
  `blanco_decam_z_el_model1_odonnell_ext` double, 
  `blanco_decam_z_el_model3_odonnell_ext` double, 
  `cfht_megacam_r_el_model1_odonnell_ext` double, 
  `cfht_megacam_r_el_model3_odonnell_ext` double, 
  `cfht_megacam_u_el_model1_odonnell_ext` double, 
  `cfht_megacam_u_el_model3_odonnell_ext` double, 
  `euclid_nisp_h_el_model1_odonnell_ext` double, 
  `euclid_nisp_h_el_model3_odonnell_ext` double, 
  `euclid_nisp_j_el_model1_odonnell_ext` double, 
  `euclid_nisp_j_el_model3_odonnell_ext` double, 
  `euclid_nisp_y_el_model1_odonnell_ext` double, 
  `euclid_nisp_y_el_model3_odonnell_ext` double, 
  `euclid_vis_el_model1_odonnell_ext` double, 
  `euclid_vis_el_model3_odonnell_ext` double, 
  `gaia_bp_el_model1_odonnell_ext` double, 
  `gaia_bp_el_model3_odonnell_ext` double, 
  `gaia_g_el_model1_odonnell_ext` double, 
  `gaia_g_el_model3_odonnell_ext` double, 
  `gaia_rp_el_model1_odonnell_ext` double, 
  `gaia_rp_el_model3_odonnell_ext` double, 
  `jst_jpcam_g_el_model1_odonnell_ext` double, 
  `jst_jpcam_g_el_model3_odonnell_ext` double, 
  `kids_g_el_model1_odonnell_ext` double, 
  `kids_g_el_model3_odonnell_ext` double, 
  `kids_i_el_model1_odonnell_ext` double, 
  `kids_i_el_model3_odonnell_ext` double, 
  `kids_r_el_model1_odonnell_ext` double, 
  `kids_r_el_model3_odonnell_ext` double, 
  `kids_u_el_model1_odonnell_ext` double, 
  `kids_u_el_model3_odonnell_ext` double, 
  `lsst_g_el_model1_odonnell_ext` double, 
  `lsst_g_el_model3_odonnell_ext` double, 
  `lsst_i_el_model1_odonnell_ext` double, 
  `lsst_i_el_model3_odonnell_ext` double, 
  `lsst_r_el_model1_odonnell_ext` double, 
  `lsst_r_el_model3_odonnell_ext` double, 
  `lsst_u_el_model1_odonnell_ext` double, 
  `lsst_u_el_model3_odonnell_ext` double, 
  `lsst_y_el_model1_odonnell_ext` double, 
  `lsst_y_el_model3_odonnell_ext` double, 
  `lsst_z_el_model1_odonnell_ext` double, 
  `lsst_z_el_model3_odonnell_ext` double, 
  `pan_starrs_i_el_model1_odonnell_ext` double, 
  `pan_starrs_i_el_model3_odonnell_ext` double, 
  `pan_starrs_z_el_model1_odonnell_ext` double, 
  `pan_starrs_z_el_model3_odonnell_ext` double, 
  `sdss_r01_el_model1_odonnell_ext` double, 
  `sdss_r01_el_model3_odonnell_ext` double, 
  `subaru_hsc_z_el_model1_odonnell_ext` double, 
  `subaru_hsc_z_el_model3_odonnell_ext` double, 
  `dominant_shape` bigint, 
  `bulge_angle` double, 
  `disk_angle` double, 
  `median_major_axis` double, 
  `scale_length` double, 
  `bulge_fraction` double, 
  `disk_length` double, 
  `bulge_length` double, 
  `ellipticity` double, 
  `disk_axis_ratio` double, 
  `bulge_axis_ratio` double, 
  `step` bigint, 
  `random_index` double, 
  `halo_id` bigint, 
  `galaxy_id` bigint)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://namenode01/user/jcarrete/data/euclid/flagship_SC456_1_7_17_deep_from_1_6_20/mock/'
;

Ahora tengo que hacer el JOIN con los csv que he creado a partir de los FITS: 

  /user/jcarrete/data/euclid/deep_calib_fits_to_csv/



   ADD JAR hdfs:///apps/cosmohub/lib/recarray-serde-LATEST-with-dependencies.jar;

CREATE TABLE `cosmohub.flagship_1_6_20_s_v2_join_fits`(
  SOURCE_ID bigint,
  RA float,
  DEC float,
  RA_MAG float,
  DEC_MAG float,
  Z_OBS float,
  TU_MAG_R01_SDSS_ABS float,
  TU_MAG_R01_SDSS float,
  SED_TEMPLATE float,
  EXT_LAW smallint,
  EBV float,
  HALPHA_LOGFLAM_EXT float,
  HBETA_LOGFLAM_EXT float,
  O2_LOGFLAM_EXT float,
  O3_LOGFLAM_EXT float,
  N2_LOGFLAM_EXT float,
  S2_LOGFLAM_EXT float,
  BULGE_FRACTION float,
  BULGE_LENGTH float,
  DISK_LENGTH float,
  DISK_AXIS_RATIO float,
  DISK_ANGLE float,
  KAPPA float,
  GAMMA1 float,
  GAMMA2 float,
  AV float,
  TU_FNU_VIS float,
  TU_FNU_Y_NISP float,
  TU_FNU_J_NISP float,
  TU_FNU_H_NISP float,
  TU_FNU_G_DECAM float,
  TU_FNU_R_DECAM float,
  TU_FNU_I_DECAM float,
  TU_FNU_Z_DECAM float,
  TU_FNU_U_MEGACAM float,
  TU_FNU_R_MEGACAM float,
  TU_FNU_G_JPCAM float,
  TU_FNU_I_PANSTARRS float,
  TU_FNU_Z_PANSTARRS float,
  TU_FNU_Z_HSC float,
  TU_FNU_G_GAIA float,
  TU_FNU_BP_GAIA float,
  TU_FNU_RP_GAIA float,
  TU_FNU_U_LSST float,
  TU_FNU_G_LSST float,
  TU_FNU_R_LSST float,
  TU_FNU_I_LSST float,
  TU_FNU_Z_LSST float,
  TU_FNU_Y_LSST float,
  TU_FNU_U_KIDS float,
  TU_FNU_G_KIDS float,
  TU_FNU_R_KIDS float,
  TU_FNU_I_KIDS float,
  TU_FNU_J_2MASS float,
  TU_FNU_H_2MASS float,
  TU_FNU_KS_2MASS float
)
PARTITIONED BY (
  hpix_5_nest bigint
)
ROW FORMAT SERDE
  'es.pic.astro.hadoop.serde.RecArraySerDe'
STORED AS INPUTFORMAT
  'es.pic.astro.hadoop.io.BinaryOutputFormat'
OUTPUTFORMAT
  'es.pic.astro.hadoop.io.BinaryOutputFormat'
;



INSERT OVERWRITE TABLE cosmohub.flagship_1_6_20_s_v2_join_fits PARTITION(hpix_5_nest)
SELECT fits.`SOURCE_ID`, 
fits.`RA`, 
fits.`DEC`, 
fits.`RA_MAG`, 
fits.`DEC_MAG`, 
fits.`Z_OBS`,   
fits.`TU_MAG_R01_SDSS_ABS`,
CAST(-2.5*log10(flux.`sdss_r01_el_model3_odonnell_ext`) - 48.6 AS FLOAT) AS `TU_MAG_R01_SDSS`,
fits.`SED_TEMPLATE`, 
fits.`EXT_LAW`, 
fits.`EBV`,   
fits.`HALPHA_LOGFLAM_EXT`,
fits.`HBETA_LOGFLAM_EXT`,
fits.`O2_LOGFLAM_EXT`,
fits.`O3_LOGFLAM_EXT`,
fits.`N2_LOGFLAM_EXT`,
fits.`S2_LOGFLAM_EXT`,
fits.`BULGE_FRACTION`,
fits.`BULGE_LENGTH`,
fits.`DISK_LENGTH`,
fits.`DISK_AXIS_RATIO`,
fits.`DISK_ANGLE`,
fits.`KAPPA`,
fits.`GAMMA1`,
fits.`GAMMA2`,
fits.`AV`,
CAST(flux.euclid_vis_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_VIS`,
CAST(flux.euclid_nisp_y_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Y_NISP`,
CAST(flux.euclid_nisp_j_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_J_NISP`,
CAST(flux.euclid_nisp_h_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_H_NISP`,
CAST(flux.blanco_decam_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_DECAM`,
CAST(flux.blanco_decam_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_DECAM`,
CAST(flux.blanco_decam_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_DECAM`,
CAST(flux.blanco_decam_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_DECAM`,
CAST(flux.cfht_megacam_u_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_U_MEGACAM`,
CAST(flux.cfht_megacam_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_MEGACAM`,
CAST(flux.jst_jpcam_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_JPCAM`,
CAST(flux.pan_starrs_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_PANSTARRS`,
CAST(flux.pan_starrs_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_PANSTARRS`,
CAST(flux.subaru_hsc_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_HSC`,
CAST(flux.gaia_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_GAIA`,
CAST(flux.gaia_bp_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_BP_GAIA`,
CAST(flux.gaia_rp_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_RP_GAIA`,
CAST(flux.lsst_u_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_U_LSST`,
CAST(flux.lsst_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_LSST`,
CAST(flux.lsst_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_LSST`,
CAST(flux.lsst_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_LSST`,
CAST(flux.lsst_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_LSST`,
CAST(flux.lsst_y_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Y_LSST`,
CAST(flux.kids_u_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_U_KIDS`,
CAST(flux.kids_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_KIDS`,
CAST(flux.kids_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_KIDS`,
CAST(flux.kids_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_KIDS`,
CAST(flux.2mass_j_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_J_2MASS`,
CAST(flux.2mass_h_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_H_2MASS`,
CAST(flux.2mass_ks_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_KS_2MASS`,
SHIFTRIGHT(flux.hpix_nest, (29-5)*2) AS hpix_5_nest
FROM jcarrete.flagship_1_6_20_csv as fits
JOIN jcarrete.flagship_SC456_1_7_17_deep_from_1_6_20 as flux
ON fits.`SOURCE_ID` = CAST(((flux.halo_id * 10000) + flux.galaxy_id) AS bigint)
;

AHORA HAY QUE AÑADIRLE EL HEADER Y CREAR LOS FITS usando el notebook: split in healpix pixels.ipynb


CALCULAMOS LAS SHAPES CORRECTAS DEL DEEP CALIBRATION

Utilizo el mismo notebook con el que calculamos las shapes correctas: Euclid_mock_pipeline_production-v.1.7.17_correct.ipynb

COJO COMO INPUT el siguiente catálogo: jcarrete.flagship_SC456_1_7_17_deep_from_1_6_20

Y he creado la tabla en parquet:

que apunta a los datos que se han creado en formato parquet aquí: 

   /user/jcarrete/data/euclid/flagship_1_7_17_deep_correct/mock

Y genero la tabla FITS 

CREATE TABLE `cosmohub.flagship_SC456_1_7_17_deep_correct_from_1_6_20_jy_fits`(
SOURCE_ID bigint,
RA float,
DEC float,
RA_MAG float,
DEC_MAG float,
Z_OBS float,
TU_MAG_R01_SDSS_ABS float,
TU_MAG_R01_SDSS float,
SED_TEMPLATE float,
EXT_LAW smallint,
EBV float,
HALPHA_LOGFLAM_EXT float,
HBETA_LOGFLAM_EXT float,
O2_LOGFLAM_EXT float,
O3_LOGFLAM_EXT float,
N2_LOGFLAM_EXT float,
S2_LOGFLAM_EXT float,
BULGE_FRACTION float,
BULGE_LENGTH float,
DISK_LENGTH float,
DISK_AXIS_RATIO float,
DISK_ANGLE float,
KAPPA float,
GAMMA1 float,
GAMMA2 float,
AV float,
TU_FNU_VIS float,
TU_FNU_Y_NISP float,
TU_FNU_J_NISP float,
TU_FNU_H_NISP float,
TU_FNU_G_DECAM float,
TU_FNU_R_DECAM float,
TU_FNU_I_DECAM float,
TU_FNU_Z_DECAM float,
TU_FNU_U_MEGACAM float,
TU_FNU_R_MEGACAM float,
TU_FNU_G_JPCAM float,
TU_FNU_I_PANSTARRS float,
TU_FNU_Z_PANSTARRS float,
TU_FNU_Z_HSC float,
TU_FNU_G_GAIA float,
TU_FNU_BP_GAIA float,
TU_FNU_RP_GAIA float,
TU_FNU_U_LSST float,
TU_FNU_G_LSST float,
TU_FNU_R_LSST float,
TU_FNU_I_LSST float,
TU_FNU_Z_LSST float,
TU_FNU_Y_LSST float,
TU_FNU_U_KIDS float,
TU_FNU_G_KIDS float,
TU_FNU_R_KIDS float,
TU_FNU_I_KIDS float,
TU_FNU_J_2MASS float,
TU_FNU_H_2MASS float,
TU_FNU_KS_2MASS float
)
PARTITIONED BY (
   hpix_5_nest bigint
)
ROW FORMAT SERDE
  'es.pic.astro.hadoop.serde.RecArraySerDe'
STORED AS INPUTFORMAT
  'es.pic.astro.hadoop.io.BinaryOutputFormat'
OUTPUTFORMAT
  'es.pic.astro.hadoop.io.BinaryOutputFormat'
;

JOIN DE ESTA: 

   jcarrete.flagship_1_6_20_csv

CON ESTA: 

   jcarrete.flagship_SC456_1_7_17_deep_from_1_6_20

Y CON LAS SHAPES BUENAS: 

   jcarrete.flagship_1_7_17_deep_correct_pq

INSERT OVERWRITE TABLE cosmohub.flagship_SC456_1_7_17_deep_correct_from_1_6_20_jy_fits PARTITION(hpix_5_nest)
SELECT fits.`SOURCE_ID`, 
fits.`RA`, 
fits.`DEC`, 
fits.`RA_MAG`, 
fits.`DEC_MAG`, 
fits.`Z_OBS`,   
fits.`TU_MAG_R01_SDSS_ABS`,
CAST(-2.5*log10(flux.`sdss_r01_el_model3_odonnell_ext`) - 48.6 AS FLOAT) AS `TU_MAG_R01_SDSS`,
fits.`SED_TEMPLATE`, 
fits.`EXT_LAW`, 
fits.`EBV`,   
fits.`HALPHA_LOGFLAM_EXT`,
fits.`HBETA_LOGFLAM_EXT`,
fits.`O2_LOGFLAM_EXT`,
fits.`O3_LOGFLAM_EXT`,
fits.`N2_LOGFLAM_EXT`,
fits.`S2_LOGFLAM_EXT`,
shape.`BULGE_FRACTION`,
shape.`BULGE_LENGTH`,
shape.`DISK_LENGTH`,
shape.`DISK_AXIS_RATIO`,
shape.`DISK_ANGLE`,
fits.`KAPPA`,
fits.`GAMMA1`,
fits.`GAMMA2`,
fits.`AV`,
CAST(flux.euclid_vis_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_VIS`,
CAST(flux.euclid_nisp_y_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Y_NISP`,
CAST(flux.euclid_nisp_j_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_J_NISP`,
CAST(flux.euclid_nisp_h_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_H_NISP`,
CAST(flux.blanco_decam_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_DECAM`,
CAST(flux.blanco_decam_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_DECAM`,
CAST(flux.blanco_decam_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_DECAM`,
CAST(flux.blanco_decam_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_DECAM`,
CAST(flux.cfht_megacam_u_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_U_MEGACAM`,
CAST(flux.cfht_megacam_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_MEGACAM`,
CAST(flux.jst_jpcam_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_JPCAM`,
CAST(flux.pan_starrs_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_PANSTARRS`,
CAST(flux.pan_starrs_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_PANSTARRS`,
CAST(flux.subaru_hsc_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_HSC`,
CAST(flux.gaia_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_GAIA`,
CAST(flux.gaia_bp_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_BP_GAIA`,
CAST(flux.gaia_rp_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_RP_GAIA`,
CAST(flux.lsst_u_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_U_LSST`,
CAST(flux.lsst_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_LSST`,
CAST(flux.lsst_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_LSST`,
CAST(flux.lsst_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_LSST`,
CAST(flux.lsst_z_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Z_LSST`,
CAST(flux.lsst_y_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_Y_LSST`,
CAST(flux.kids_u_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_U_KIDS`,
CAST(flux.kids_g_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_G_KIDS`,
CAST(flux.kids_r_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_R_KIDS`,
CAST(flux.kids_i_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_I_KIDS`,
CAST(flux.2mass_j_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_J_2MASS`,
CAST(flux.2mass_h_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_H_2MASS`,
CAST(flux.2mass_ks_el_model3_odonnell_ext * 1e23 AS FLOAT) AS `TU_FNU_KS_2MASS`,
SHIFTRIGHT(flux.hpix_nest, (29-5)*2) AS hpix_5_nest
FROM jcarrete.flagship_1_6_20_csv as fits
JOIN jcarrete.flagship_SC456_1_7_17_deep_from_1_6_20 as flux
ON fits.`SOURCE_ID` = CAST(((flux.halo_id * 10000) + flux.galaxy_id) AS bigint)
JOIN jcarrete.flagship_1_7_17_deep_correct_pq as shape
ON fits.`SOURCE_ID` = CAST(((shape.halo_id * 10000) + shape.galaxy_id) AS bigint)
WHERE (flux.logf_halpha_model3_ext > -16 OR -2.5*log10(flux.euclid_nisp_h) - 48.6 < 26)
AND shape.disk_axis_ratio > 0.10865
;
Retrieved from "http://pwiki.pic.es/index.php?title=Euclid_mock_production_v1.7.17_deep_correct&oldid=584"