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

From Public PIC Wiki
Jump to navigation Jump to search
 
Line 728: Line 728:
 
     JOIN cosmohub.flagship_lensing AS lens
 
     JOIN cosmohub.flagship_lensing AS lens
 
       ON gal.step = lens.step AND SHIFTRIGHT(gal.hpix_nest, (28-13)*2) = lens.hpix
 
       ON gal.step = lens.step AND SHIFTRIGHT(gal.hpix_nest, (28-13)*2) = lens.hpix
 +
    WHERE x_gal >= 0 AND y_gal >= 0 and z_gal >= 0
 
  ) AS mock
 
  ) AS mock

Latest revision as of 11:27, 2 July 2019

Description

Single octant with pipeline v1.8 (commit 4b08560) for Helsinki.

Input

First octant, taken from the table cosmohub.flagship_rockstar_octant1_c

Pipeline & config location

/software/astro/scripts/spark_notebook.sh -e mocks -c 21 -m 63G -o 54G

/hdfs/user/jcarrete/data/euclid/flagship_1_8_3/config

Mock output (parquet)

CREATE EXTERNAL TABLE tallada.flagship_1_8_3_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` BIGINT,
  `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_8_3/mock'
;

Final output

CREATE TABLE cosmohub.flagship_1_8_3_s (
  `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` BIGINT,
  `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,
  `random_index` DOUBLE,
  `halo_id` BIGINT,
  `galaxy_id` BIGINT,
  `ra_gal_mag` DOUBLE,
  `dec_gal_mag` DOUBLE,
  `kappa` FLOAT,
  `gamma1` FLOAT,
  `gamma2` FLOAT
)
PARTITIONED BY (
  step SMALLINT
)
CLUSTERED BY (
  halo_id,
  galaxy_id
)
INTO 128 BUCKETS
STORED AS ORC
;

Additional configuration to beeline: 

--hiveconf tez.am.resource.memory.mb=40000
--hiveconf tez.am.max.allowed.time-sec.for-read-error=600
--hiveconf tez.task.resource.mb=12000
--hiveconf hive.tez.container.size=12000

INSERT OVERWRITE TABLE cosmohub.flagship_1_8_3_s PARTITION(step)
SELECT 
  kind,
  luminosity_r01,
  halo_x,
  halo_y,
  halo_z,
  halo_vx,
  halo_vy,
  halo_vz,
  halo_r,
  true_redshift_halo,
  halo_lm,
  halo_n_sats,
  n_gals,
  abs_mag_r01,
  abs_mag_r01_evolved,
  luminosity_r01_evolved,
  gr_restframe,
  color_kind,
  x_gal,
  y_gal,
  z_gal,
  r_gal,
  true_redshift_gal,
  ra_gal,
  dec_gal,
  hpix_nest,
  vx_gal,
  vy_gal,
  vz_gal,
  vrad_gal,
  delta_r,
  observed_redshift_gal,
  sed_ke,
  gr_cosmos,
  sed_cosmos,
  ext_curve_cosmos,
  ebv_cosmos,
  is_within_cosmos,
  cosmos_distance,
  abs_mag_uv_dereddened,
  log_ml_r01,
  log_stellar_mass,
  metallicity,
  log_sfr,
  logf_halpha_ext,
  logf_halpha,
  halpha_scatter,
  loglum_halpha,
  loglum_halpha_ext,
  logf_dummy,
  z_dummy,
  logf_halpha_model1_ext,
  logf_halpha_model1,
  loglum_halpha_model1_ext,
  loglum_halpha_model1,
  logf_halpha_model3_ext,
  logf_halpha_model3,
  loglum_halpha_model3_ext,
  loglum_halpha_model3,
  logf_hbeta_model1_ext,
  logf_hbeta_model1,
  logf_o2_model1_ext,
  logf_o2_model1,
  logf_n2_model1_ext,
  logf_n2_model1,
  logf_o3_model1_ext,
  logf_o3_model1,
  logf_s2_model1_ext,
  logf_s2_model1,
  logf_hbeta_model3_ext,
  logf_hbeta_model3,
  logf_o2_model3_ext,
  logf_o2_model3,
  logf_n2_model3_ext,
  logf_n2_model3,
  logf_o3_model3_ext,
  logf_o3_model3,
  logf_s2_model3_ext,
  logf_s2_model3,
  2mass_h,
  2mass_j,
  2mass_ks,
  blanco_decam_g,
  blanco_decam_i,
  blanco_decam_r,
  blanco_decam_z,
  cfht_megacam_r,
  cfht_megacam_u,
  euclid_nisp_h,
  euclid_nisp_j,
  euclid_nisp_y,
  euclid_vis,
  gaia_bp,
  gaia_g,
  gaia_rp,
  jst_jpcam_g,
  kids_g,
  kids_i,
  kids_r,
  kids_u,
  lsst_g,
  lsst_i,
  lsst_r,
  lsst_u,
  lsst_y,
  lsst_z,
  pan_starrs_i,
  pan_starrs_z,
  sdss_r01,
  subaru_hsc_z,
  l_gal,
  b_gal,
  mw_extinction,
  2mass_h_odonnell_ext,
  2mass_j_odonnell_ext,
  2mass_ks_odonnell_ext,
  blanco_decam_g_odonnell_ext,
  blanco_decam_i_odonnell_ext,
  blanco_decam_r_odonnell_ext,
  blanco_decam_z_odonnell_ext,
  cfht_megacam_r_odonnell_ext,
  cfht_megacam_u_odonnell_ext,
  euclid_nisp_h_odonnell_ext,
  euclid_nisp_j_odonnell_ext,
  euclid_nisp_y_odonnell_ext,
  euclid_vis_odonnell_ext,
  gaia_bp_odonnell_ext,
  gaia_g_odonnell_ext,
  gaia_rp_odonnell_ext,
  jst_jpcam_g_odonnell_ext,
  kids_g_odonnell_ext,
  kids_i_odonnell_ext,
  kids_r_odonnell_ext,
  kids_u_odonnell_ext,
  lsst_g_odonnell_ext,
  lsst_i_odonnell_ext,
  lsst_r_odonnell_ext,
  lsst_u_odonnell_ext,
  lsst_y_odonnell_ext,
  lsst_z_odonnell_ext,
  pan_starrs_i_odonnell_ext,
  pan_starrs_z_odonnell_ext,
  sdss_r01_odonnell_ext,
  subaru_hsc_z_odonnell_ext,
  2mass_h_el_model1_odonnell_ext,
  2mass_h_el_model3_odonnell_ext,
  2mass_j_el_model1_odonnell_ext,
  2mass_j_el_model3_odonnell_ext,
  2mass_ks_el_model1_odonnell_ext,
  2mass_ks_el_model3_odonnell_ext,
  blanco_decam_g_el_model1_odonnell_ext,
  blanco_decam_g_el_model3_odonnell_ext,
  blanco_decam_i_el_model1_odonnell_ext,
  blanco_decam_i_el_model3_odonnell_ext,
  blanco_decam_r_el_model1_odonnell_ext,
  blanco_decam_r_el_model3_odonnell_ext,
  blanco_decam_z_el_model1_odonnell_ext,
  blanco_decam_z_el_model3_odonnell_ext,
  cfht_megacam_r_el_model1_odonnell_ext,
  cfht_megacam_r_el_model3_odonnell_ext,
  cfht_megacam_u_el_model1_odonnell_ext,
  cfht_megacam_u_el_model3_odonnell_ext,
  euclid_nisp_h_el_model1_odonnell_ext,
  euclid_nisp_h_el_model3_odonnell_ext,
  euclid_nisp_j_el_model1_odonnell_ext,
  euclid_nisp_j_el_model3_odonnell_ext,
  euclid_nisp_y_el_model1_odonnell_ext,
  euclid_nisp_y_el_model3_odonnell_ext,
  euclid_vis_el_model1_odonnell_ext,
  euclid_vis_el_model3_odonnell_ext,
  gaia_bp_el_model1_odonnell_ext,
  gaia_bp_el_model3_odonnell_ext,
  gaia_g_el_model1_odonnell_ext,
  gaia_g_el_model3_odonnell_ext,
  gaia_rp_el_model1_odonnell_ext,
  gaia_rp_el_model3_odonnell_ext,
  jst_jpcam_g_el_model1_odonnell_ext,
  jst_jpcam_g_el_model3_odonnell_ext,
  kids_g_el_model1_odonnell_ext,
  kids_g_el_model3_odonnell_ext,
  kids_i_el_model1_odonnell_ext,
  kids_i_el_model3_odonnell_ext,
  kids_r_el_model1_odonnell_ext,
  kids_r_el_model3_odonnell_ext,
  kids_u_el_model1_odonnell_ext,
  kids_u_el_model3_odonnell_ext,
  lsst_g_el_model1_odonnell_ext,
  lsst_g_el_model3_odonnell_ext,
  lsst_i_el_model1_odonnell_ext,
  lsst_i_el_model3_odonnell_ext,
  lsst_r_el_model1_odonnell_ext,
  lsst_r_el_model3_odonnell_ext,
  lsst_u_el_model1_odonnell_ext,
  lsst_u_el_model3_odonnell_ext,
  lsst_y_el_model1_odonnell_ext,
  lsst_y_el_model3_odonnell_ext,
  lsst_z_el_model1_odonnell_ext,
  lsst_z_el_model3_odonnell_ext,
  pan_starrs_i_el_model1_odonnell_ext,
  pan_starrs_i_el_model3_odonnell_ext,
  pan_starrs_z_el_model1_odonnell_ext,
  pan_starrs_z_el_model3_odonnell_ext,
  sdss_r01_el_model1_odonnell_ext,
  sdss_r01_el_model3_odonnell_ext,
  subaru_hsc_z_el_model1_odonnell_ext,
  subaru_hsc_z_el_model3_odonnell_ext,
  dominant_shape,
  bulge_angle,
  disk_angle,
  median_major_axis,
  scale_length,
  bulge_fraction,
  disk_length,
  bulge_length,
  ellipticity,
  disk_axis_ratio,
  bulge_axis_ratio,
  random_index,
  halo_id,
  galaxy_id,
  lensed.ra_mag,
  lensed.dec_mag,
  defl_0,
  shear_1,
  shear_2,
  step
FROM (
  SELECT
      gal.*,
      lens.defl_0, lens.shear_1, lens.shear_2,
      default.magnified_positions(
        CAST(gal.ra_gal AS DOUBLE),
        CAST(gal.dec_gal AS DOUBLE),
        CAST(lens.defl_1 AS DOUBLE),
        CAST(lens.defl_2 AS DOUBLE)
      ) AS lensed
    FROM tallada.flagship_1_8_3_p AS gal
    JOIN cosmohub.flagship_lensing AS lens
      ON gal.step = lens.step AND SHIFTRIGHT(gal.hpix_nest, (28-13)*2) = lens.hpix
    WHERE x_gal >= 0 AND y_gal >= 0 and z_gal >= 0
) AS mock
Retrieved from "http://pwiki.pic.es/index.php?title=Euclid_mock_production_v1.8.3&oldid=493"