Euclid mock production v1.9.2
Jump to navigation
Jump to search
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 scalength. Profile defined as: I = I_o x exp (-radius/scalelength)', `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 'scalength of the disk, profile defined as: I = I_o x exp (-radius/disk_scalelength)', `disk_nsersic` double COMMENT 'Sersic index of the disk component', `disk_r50` double COMMENT 'half light radius of the disk. For an exponential profile (or Sersec profile with index n=1), disk_r50 = disk_scalelength * 1.678', `bulge_r50` double COMMENT 'half light radius of the bulge', `bulge_nsersic` double COMMENT 'Sersic index of the bulge component', `disk_ellipticity` double COMMENT 'ellipticity of the disk defined as disk_ellipticity = (1 - disk_axis_ratio) / (1 + disk_axis_ratio)', `bulge_ellipticity` double COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)', `disk_axis_ratio` double COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)', `bulge_axis_ratio` double COMMENT 'bulge projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)', `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;