Difference between revisions of "Euclid mock production v1.6.20"

From Public PIC Wiki
Jump to navigation Jump to search
(Created page with "== Description == Deep and NIR self calibration field. Area to cover would be: RA ~ [255, 285] Dec ~ [61, 72] == Input == Create temporal table with halo subset: CREATE...")
 
Line 324: Line 324:
 
   'es.pic.astro.hadoop.io.BinaryOutputFormat'
 
   'es.pic.astro.hadoop.io.BinaryOutputFormat'
 
  ;
 
  ;
−
+
 
−
  INSERT OVERWRITE TABLE cosmohub.spv_v15_02_fits PARTITION(hpix_5_nest)
+
  INSERT OVERWRITE TABLE tallada.flagship_1_6_20_s_fits PARTITION(hpix_5_nest)
−
SELECT
+
SELECT
−
    CAST( ((gal.halo_id * 10000) + gal.galaxy_id) AS bigint)   AS SOURCE_ID,
+
  CAST(((mock.halo_id * 10000) + mock.galaxy_id) AS bigint) AS SOURCE_ID,
−
    CAST( gal.ra_gal                             AS float)     AS RA,
+
  CAST(mock.ra_gal AS FLOAT) AS RA,
−
    CAST( gal.dec_gal                             AS float)     AS DEC,
+
  CAST(mock.dec_gal AS FLOAT) AS DEC,
−
    CAST( gal.observed_redshift_gal               AS float)     AS Z_OBS,
+
  CAST(mock.lensed.ra_mag AS FLOAT) AS RA_MAG,
−
    CAST( gal.abs_mag_r01_evolved                 AS float)     AS TU_MAG_R01_SDSS_ABS,
+
  CAST(mock.lensed.dec_mag AS FLOAT) AS DEC_MAG,
−
    CAST( -2.5*log10(gal.sdss_r01) - 48.6         AS float)     AS TU_MAG_R01_SDSS,
+
  CAST(mock.observed_redshift_gal AS FLOAT) AS Z_OBS,
−
    CAST( gal.sed_cosmos                         AS float)     AS SED_TEMPLATE,
+
  CAST(mock.abs_mag_r01_evolved AS FLOAT) AS TU_MAG_R01_SDSS_ABS,
−
    CAST( ROUND(gal.ext_curve_cosmos)             AS smallint) AS EXT_LAW,
+
  CAST(-2.5*log10(mock.sdss_r01_el_model3_odonnell_ext) - 48.6 AS FLOAT) AS TU_MAG_R01_SDSS,
−
    CAST( gal.ebv_cosmos                         AS float)     AS EBV,
+
  CAST(mock.sed_cosmos AS FLOAT) AS SED_TEMPLATE,
−
    CAST( gal.logf_halpha_model3_ext             AS float)     AS HALPHA_LOGFLAM_EXT,
+
  CAST(ROUND(mock.ext_curve_cosmos) AS smallint) AS EXT_LAW,
−
    CAST( gal.logf_hbeta_model3_ext               AS float)     AS HBETA_LOGFLAM_EXT,
+
  CAST(mock.ebv_cosmos AS FLOAT) AS EBV,
−
    CAST( gal.logf_o2_model3_ext                 AS float)     AS O2_LOGFLAM_EXT,
+
  CAST(mock.logf_halpha_model3_ext AS FLOAT) AS HALPHA_LOGFLAM_EXT,
−
    CAST( gal.logf_o3_model3_ext                 AS float)     AS O3_LOGFLAM_EXT,
+
  CAST(mock.logf_hbeta_model3_ext AS FLOAT) AS HBETA_LOGFLAM_EXT,
−
    CAST( gal.logf_n2_model3_ext                 AS float)     AS N2_LOGFLAM_EXT,
+
  CAST(mock.logf_o2_model3_ext AS FLOAT) AS O2_LOGFLAM_EXT,
−
    CAST( gal.logf_s2_model3_ext                 AS float)     AS S2_LOGFLAM_EXT,
+
  CAST(mock.logf_o3_model3_ext AS FLOAT) AS O3_LOGFLAM_EXT,
−
    CAST( gal.bulge_fraction                     AS float)     as BULGE_FRACTION,
+
  CAST(mock.logf_n2_model3_ext AS FLOAT) AS N2_LOGFLAM_EXT,
−
    CAST( gal.bulge_length                       AS float)     AS BULGE_LENGTH,
+
  CAST(mock.logf_s2_model3_ext AS FLOAT) AS S2_LOGFLAM_EXT,
−
    CAST( gal.disk_length                         AS float)     AS DISK_LENGTH,
+
  CAST(mock.bulge_fraction AS FLOAT) AS BULGE_FRACTION,
−
    CAST( gal.disk_axis_ratio                     AS float)     as DISK_AXIS_RATIO,
+
  CAST(mock.bulge_length AS FLOAT) AS BULGE_LENGTH,
−
    CAST( gal.disk_angle                         AS float)     AS DISK_ANGLE,
+
  CAST(mock.disk_length AS FLOAT) AS DISK_LENGTH,
−
    CAST( gal.ra_gal_mag                          AS float)     AS RA_MAG,
+
  CAST(mock.disk_axis_ratio AS FLOAT) AS DISK_AXIS_RATIO,
−
    CAST( gal.dec_gal_mag                        AS float)     AS DEC_MAG,
+
  CAST(mock.disk_angle AS FLOAT) AS DISK_ANGLE,
−
    CAST( gal.kappa                              AS float)     as KAPPA,
+
  CAST(mock.defl_0 AS FLOAT) AS KAPPA,
−
    CAST( gal.gamma1                              AS float)     AS GAMMA1,
+
  CAST(mock.shear_1 AS FLOAT) AS GAMMA1,
−
    CAST( gal.gamma2                              AS float)     AS GAMMA2,
+
  CAST(mock.shear_2 AS FLOAT) AS GAMMA2,
−
    CAST( gal.true_redshift_gal                  AS float)     AS true_redshift_gal,
+
  CAST(mock.mw_extinction AS FLOAT) AS AV,
−
    CAST( gal.logf_halpha_model1_ext              AS float)     AS logf_halpha_model1_ext,
+
  CAST(mock.euclid_vis_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_VIS,
−
    CAST( gal.logf_hbeta_model1_ext              AS float)     AS logf_hbeta_model1_ext,
+
  CAST(mock.euclid_nisp_y_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Y_NISP,
−
    CAST( gal.logf_n2_model1_ext                  AS float)     AS logf_n2_model1_ext,
+
  CAST(mock.euclid_nisp_j_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_J_NISP,
−
    CAST( gal.logf_o3_model1_ext                  AS float)     AS logf_o3_model1_ext,
+
  CAST(mock.euclid_nisp_h_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_H_NISP,
−
    CAST( gal.logf_o2_model1_ext                  AS float)     AS logf_o2_model1_ext,
+
  CAST(mock.blanco_decam_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_DECAM,
−
    CAST( gal.logf_s2_model1_ext                  AS float)     AS logf_s2_model1_ext,
+
  CAST(mock.blanco_decam_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_DECAM,
−
    CAST( gal.dominant_shape                      AS smallint) AS dominant_shape,
+
  CAST(mock.blanco_decam_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_DECAM,
−
    CAST( gal.halo_lm                            AS float)     AS halo_lm,
+
  CAST(mock.blanco_decam_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_DECAM,
−
    CAST( gal.halo_n_sats                        AS smallint) AS halo_n_sats,
+
  CAST(mock.cfht_megacam_u_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_U_MEGACAM,
−
    CAST( gal.log_ml_r01                          AS float)     AS log_ml_r01,
+
  CAST(mock.cfht_megacam_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_MEGACAM,
−
    CAST( gal.log_stellar_mass                    AS float)     AS log_stellar_mass,
+
  CAST(mock.jst_jpcam_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_JPCAM,
−
    CAST( gal.log_sfr                            AS float)     AS log_sfr,
+
  CAST(mock.pan_starrs_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_PANSTARRS,
−
    CAST( gal.euclid_vis                          AS float)     AS euclid_vis,
+
  CAST(mock.pan_starrs_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_PANSTARRS,
−
    CAST( gal.euclid_nisp_y                      AS float)     AS euclid_nisp_y,
+
  CAST(mock.subaru_hsc_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_HSC,
−
    CAST( gal.euclid_nisp_j                      AS float)     AS euclid_nisp_j,
+
  CAST(mock.gaia_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_GAIA,
−
    CAST( gal.euclid_nisp_h                      AS float)     AS euclid_nisp_h,
+
  CAST(mock.gaia_bp_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_BP_GAIA,
−
    CAST( SHIFTRIGHT(gal.hpix_29_nest, (29-5)*2)  AS bigint)   AS hpix_5_nest
+
  CAST(mock.gaia_rp_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_RP_GAIA,
−
FROM cosmohub.flagship_mock_1_6_15_s AS gal
+
  CAST(mock.lsst_u_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_U_LSST,
−
JOIN cosmohub.spv2_survey_mask_correct as mask
+
  CAST(mock.lsst_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_LSST,
−
ON mask.hpix_9_nest = SHIFTRIGHT(gal.hpix_29_nest, (29-9)*2)
+
  CAST(mock.lsst_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_LSST,
−
  AND mask.value = true
+
  CAST(mock.lsst_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_LSST,
−
WHERE (logf_halpha_model3_ext > -16 OR euclid_nisp_h < 26)
+
  CAST(mock.lsst_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_LSST,
−
;
+
  CAST(mock.lsst_y_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Y_LSST,
 +
  CAST(mock.kids_u_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_U_KIDS,
 +
  CAST(mock.kids_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_KIDS,
 +
  CAST(mock.kids_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_KIDS,
 +
  CAST(mock.kids_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_KIDS,
 +
  CAST(mock.2mass_j_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_J_2MASS,
 +
  CAST(mock.2mass_h_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_H_2MASS,
 +
  CAST(mock.2mass_ks_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_KS_2MASS,
 +
  SHIFTRIGHT(mock.hpix_nest, (29-5)*2) AS hpix_5_nest
 +
  FROM (
 +
  SELECT
 +
    gal.*,
 +
    lens.defl_0, lens.shear_1, lens.shear_2,
 +
    default.magnified_positions(gal.ra_gal, gal.dec_gal, lens.defl_1, lens.defl_2) AS lensed
 +
  FROM tallada.flagship_1_6_20_p AS gal
 +
  JOIN cosmohub.flagship_lensing AS lens
 +
    ON gal.step = lens.step AND SHIFTRIGHT(gal.hpix_nest, (28-13)*3) = lens.hpix
 +
) AS mock

Revision as of 09:52, 8 May 2019

Description

Deep and NIR self calibration field.

Area to cover would be: 

RA ~ [255, 285]
Dec ~ [61, 72]

Input

Create temporal table with halo subset: 

CREATE TABLE tallada.flagship_halos_1_6_20
STORED AS ORC
AS
SELECT id AS halo_id, num_p AS halo_num_p,
     x AS halo_x,   y AS halo_y,   z AS halo_z,
    vx AS halo_vx, vy AS halo_vy, vz AS halo_vz,
    SQRT(x*x + y*y + z*z) AS halo_r
FROM cosmohub.flagship_halos
WHERE pid=-1 
  AND PMOD((180.0/PI() * ATAN2(y,x)) + 360., 360.) BETWEEN 255 AND 285
  AND 90 - (180.0/PI() * ATAN2(SQRT(x*x+y*y),z)) BETWEEN 61 AND 72

Pipeline & config location

/hdfs/user/jcarrete/data/euclid/flagship_1_6_20/config

Mock output (parquet)

CREATE EXTERNAL TABLE tallada.flagship_1_6_20_p(
  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)
STORED AS PARQUET
LOCATION
  '/user/jcarrete/data/euclid/flagship_1_6_20/mock'
;

FITS output

Following https://euclid.roe.ac.uk/projects/sim_trueuniverse/wiki/SC456_TU_galaxies convention 

CREATE TABLE `tallada.flagship_1_6_20_s_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 tallada.flagship_1_6_20_s_fits PARTITION(hpix_5_nest)
SELECT
  CAST(((mock.halo_id * 10000) + mock.galaxy_id) AS bigint) AS SOURCE_ID,
  CAST(mock.ra_gal AS FLOAT) AS RA,
  CAST(mock.dec_gal AS FLOAT) AS DEC,
  CAST(mock.lensed.ra_mag AS FLOAT) AS RA_MAG,
  CAST(mock.lensed.dec_mag AS FLOAT) AS DEC_MAG,
  CAST(mock.observed_redshift_gal AS FLOAT) AS Z_OBS,
  CAST(mock.abs_mag_r01_evolved AS FLOAT) AS TU_MAG_R01_SDSS_ABS,
  CAST(-2.5*log10(mock.sdss_r01_el_model3_odonnell_ext) - 48.6 AS FLOAT) AS TU_MAG_R01_SDSS,
  CAST(mock.sed_cosmos AS FLOAT) AS SED_TEMPLATE,
  CAST(ROUND(mock.ext_curve_cosmos) AS smallint) AS EXT_LAW,
  CAST(mock.ebv_cosmos AS FLOAT) AS EBV,
  CAST(mock.logf_halpha_model3_ext AS FLOAT) AS HALPHA_LOGFLAM_EXT,
  CAST(mock.logf_hbeta_model3_ext AS FLOAT) AS HBETA_LOGFLAM_EXT,
  CAST(mock.logf_o2_model3_ext AS FLOAT) AS O2_LOGFLAM_EXT,
  CAST(mock.logf_o3_model3_ext AS FLOAT) AS O3_LOGFLAM_EXT,
  CAST(mock.logf_n2_model3_ext AS FLOAT) AS N2_LOGFLAM_EXT,
  CAST(mock.logf_s2_model3_ext AS FLOAT) AS S2_LOGFLAM_EXT,
  CAST(mock.bulge_fraction AS FLOAT) AS BULGE_FRACTION,
  CAST(mock.bulge_length AS FLOAT) AS BULGE_LENGTH,
  CAST(mock.disk_length AS FLOAT) AS DISK_LENGTH,
  CAST(mock.disk_axis_ratio AS FLOAT) AS DISK_AXIS_RATIO,
  CAST(mock.disk_angle AS FLOAT) AS DISK_ANGLE,
  CAST(mock.defl_0 AS FLOAT) AS KAPPA,
  CAST(mock.shear_1 AS FLOAT) AS GAMMA1,
  CAST(mock.shear_2 AS FLOAT) AS GAMMA2,
  CAST(mock.mw_extinction AS FLOAT) AS AV,
  CAST(mock.euclid_vis_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_VIS,
  CAST(mock.euclid_nisp_y_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Y_NISP,
  CAST(mock.euclid_nisp_j_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_J_NISP,
  CAST(mock.euclid_nisp_h_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_H_NISP,
  CAST(mock.blanco_decam_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_DECAM,
  CAST(mock.blanco_decam_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_DECAM,
  CAST(mock.blanco_decam_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_DECAM,
  CAST(mock.blanco_decam_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_DECAM,
  CAST(mock.cfht_megacam_u_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_U_MEGACAM,
  CAST(mock.cfht_megacam_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_MEGACAM,
  CAST(mock.jst_jpcam_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_JPCAM,
  CAST(mock.pan_starrs_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_PANSTARRS,
  CAST(mock.pan_starrs_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_PANSTARRS,
  CAST(mock.subaru_hsc_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_HSC,
  CAST(mock.gaia_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_GAIA,
  CAST(mock.gaia_bp_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_BP_GAIA,
  CAST(mock.gaia_rp_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_RP_GAIA,
  CAST(mock.lsst_u_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_U_LSST,
  CAST(mock.lsst_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_LSST,
  CAST(mock.lsst_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_LSST,
  CAST(mock.lsst_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_LSST,
  CAST(mock.lsst_z_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Z_LSST,
  CAST(mock.lsst_y_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_Y_LSST,
  CAST(mock.kids_u_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_U_KIDS,
  CAST(mock.kids_g_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_G_KIDS,
  CAST(mock.kids_r_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_R_KIDS,
  CAST(mock.kids_i_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_I_KIDS,
  CAST(mock.2mass_j_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_J_2MASS,
  CAST(mock.2mass_h_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_H_2MASS,
  CAST(mock.2mass_ks_el_model3_odonnell_ext AS FLOAT) AS TU_FNU_KS_2MASS,
  SHIFTRIGHT(mock.hpix_nest, (29-5)*2) AS hpix_5_nest
FROM (
  SELECT
    gal.*,
    lens.defl_0, lens.shear_1, lens.shear_2,
    default.magnified_positions(gal.ra_gal, gal.dec_gal, lens.defl_1, lens.defl_2) AS lensed
  FROM tallada.flagship_1_6_20_p AS gal
  JOIN cosmohub.flagship_lensing AS lens
    ON gal.step = lens.step AND SHIFTRIGHT(gal.hpix_nest, (28-13)*3) = lens.hpix
) AS mock
Retrieved from "http://pwiki.pic.es/index.php?title=Euclid_mock_production_v1.6.20&oldid=459"