Euclid mock production v1.9.2

   CREATE EXTERNAL TABLE `flagship_1_9_2`(
 `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, 
 `blanco_decam_g` double, 
 `blanco_decam_i` double, 
 `blanco_decam_r` double, 
 `blanco_decam_z` double, 
 `gaia_bp` double, 
 `gaia_g` double, 
 `gaia_rp` double, 
 `subaru_hsc_z` 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, 
 `cfht_megacam_r` double, 
 `cfht_megacam_u` double, 
 `pan_starrs_i` double, 
 `pan_starrs_z` double, 
 `euclid_nisp_h` double, 
 `euclid_nisp_j` double, 
 `euclid_nisp_y` double, 
 `euclid_vis` double, 
 `2mass_h` double, 
 `2mass_j` double, 
 `2mass_ks` double, 
 `sdss_r01` 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, 
 `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, 
 `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, 
 `subaru_hsc_z_el_model1_odonnell_ext` double, 
 `subaru_hsc_z_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, 
 `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, 
 `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, 
 `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, 
 `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, 
 `sdss_r01_el_model1_odonnell_ext` double, 
 `sdss_r01_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_scalelength` double, 
 `disk_nsersic` double, 
 `disk_r50` double, 
 `bulge_r50` double, 
 `bulge_nsersic` double, 
 `disk_ellipticity` double, 
 `bulge_ellipticity` double, 
 `disk_axis_ratio` double, 
 `bulge_axis_ratio` double, 
 `disk_inclination_angle` double, 
 `bulge_inclination_angle` 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'
   ;

   CREATE TABLE flagship_mock_1_9_2_c(
 `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, 
 `blanco_decam_g` double, 
 `blanco_decam_i` double, 
 `blanco_decam_r` double, 
 `blanco_decam_z` double, 
 `gaia_bp` double, 
 `gaia_g` double, 
 `gaia_rp` double, 
 `subaru_hsc_z` 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, 
 `cfht_megacam_r` double, 
 `cfht_megacam_u` double, 
 `pan_starrs_i` double, 
 `pan_starrs_z` double, 
 `euclid_nisp_h` double, 
 `euclid_nisp_j` double, 
 `euclid_nisp_y` double, 
 `euclid_vis` double, 
 `2mass_h` double, 
 `2mass_j` double, 
 `2mass_ks` double, 
 `sdss_r01` 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, 
 `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, 
 `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, 
 `subaru_hsc_z_el_model1_odonnell_ext` double, 
 `subaru_hsc_z_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, 
 `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, 
 `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, 
 `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, 
 `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, 
 `sdss_r01_el_model1_odonnell_ext` double, 
 `sdss_r01_el_model3_odonnell_ext` double, 
 `dominant_shape` bigint COMMENT 'flag to define whether the galaxy is BULGE-dominated (0) or DISK-dominated (1)',  
 `bulge_angle` double COMMENT 'position of the disk rotation axis (degrees) (assumption: bulge_angle = disk_angle). TU Galaxy stamps convention for OU-SIM: the standard is to set the position angle from North to East, with the major axis aligned in Declination', 
 `disk_angle` double COMMENT 'position of the disk rotation axis (degrees) (assumption: bulge_angle = disk_angle). TU Galaxy stamps convention for OU-SIM: the standard is to set the position angle from North to East, with the major axis aligned in Declination', 
 `median_major_axis` double COMMENT 'median semi-major axis exponential scalelength (arcsec), rd in Miller et al. paper', 
 `scale_length` double COMMENT 'disc and bulge scalelength prior', 
 `bulge_fraction` double COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)', 
 `disk_scalelength` double COMMENT 'disk_length', 
 `disk_nsersic` double COMMENT '1', 
 `disk_r50` double COMMENT 'disk_r50 = disk_length * 1.678', 
 `bulge_r50` double COMMENT 'bulge_r50 = bulge_length (r50 or r_eff)', 
 `bulge_nsersic` double COMMENT 'From 1 to 4', 
 `disk_ellipticity` double COMMENT 'disk axis ratio = (1.-disk_ellipticity)/(1.+ disk_ellipticity)', 
 `bulge_ellipticity` double COMMENT 'bulge axis ratio = (1.-bulge_ellipticity)/(1.+ bulge_ellipticity)', 
 `disk_axis_ratio` double COMMENT 'disk projected axis ratio (b/a)', 
 `bulge_axis_ratio` double COMMENT 'bulge projected axis ratio (b/a)',
 `disk_inclination_angle` double COMMENT , 
 `bulge_inclination_angle` double COMMENT , 
 `step` bigint, 
 `random_index` double, 
 `halo_id` bigint, 
 `galaxy_id` bigint
   )
   CLUSTERED BY ( 
   halo_id, 
   galaxy_id) 
   INTO 128 BUCKETS
   ROW FORMAT SERDE 
   'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
   STORED AS INPUTFORMAT 
   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
   OUTPUTFORMAT 
   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
   ;

   INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_9_2_c
   SELECT * FROM tallada.flagship_1_9_2;

   ANALYZE TABLE cosmohub.flagship_mock_1_9_2_c COMPUTE STATISTICS FOR COLUMNS;