Jcarrete: /* Version 1.0 */

2020-07-23T16:37:08Z

Version 1.0

Jcarrete: Created page with "== Version 1.0 == Catalog provided by Rebecca and Pascal: https://euclid.roe.ac.uk/projects/puswg/wiki/Galaxies It is a ".txt" file. I remove the header and copy it into hd..."

2020-07-23T16:36:42Z

Created page with "== Version 1.0 == Catalog provided by Rebecca and Pascal: https://euclid.roe.ac.uk/projects/puswg/wiki/Galaxies It is a ".txt" file. I remove the header and copy it into hd..."

New page

== Version 1.0 ==

Catalog provided by Rebecca and Pascal: https://euclid.roe.ac.uk/projects/puswg/wiki/Galaxies

It is a ".txt" file.

I remove the header and copy it into hdfs:

tail -n+2 /cephfs/pic.es/astro/scratch/jcarrete/sandbox/Euclid/High_redshift_galaxies/Euclid_SC8_simcat_LBGs_DPL_16sqdegs_v1.txt | hdfs dfs -copyFromLocal - /user/jcarrete/data/euclid/high_z_galaxies/v1.0/Euclid_SC8_simcat_LBGs_DPL_16sqdegs_v1.csv

I create the external table:

CREATE EXTERNAL TABLE jcarrete.euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_0(
id bigint COMMENT 'unique galaxy identifier',
`type` string COMMENT 'type (LBG for all objects)',
z float COMMENT 'Redshift (in the range [6-12])',
abs_muv float COMMENT 'absolute magnitude at 1500 angstroms',
sedname string COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
rhalf_kpc float COMMENT 'half light radius (in physical kpc)',
rhalf_arcsec float COMMENT 'half light radius (in arcsec)',
muv float COMMENT 'for information only, observed magnitude at rest-frame 1500',
beta float COMMENT 'for information only, used to assign an SED'
)
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://AntNest/user/jcarrete/data/euclid/high_z_galaxies/v1.0'
;

I create the ORC table:

CREATE TABLE euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_0_c(
id bigint COMMENT 'unique galaxy identifier',
`type` string COMMENT 'type (LBG for all objects)',
z float COMMENT 'Redshift (in the range [6-12])',
abs_muv float COMMENT 'absolute magnitude at 1500 angstroms',
sedname string COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
rhalf_kpc float COMMENT 'half light radius (in physical kpc)',
rhalf_arcsec float COMMENT 'half light radius (in arcsec)',
muv float COMMENT 'for information only, observed magnitude at rest-frame 1500',
beta float COMMENT 'for information only, used to assign an SED'
)
CLUSTERED BY (
id)
SORTED BY (
id ASC)
INTO 4 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'
;

Then I covert the data:

INSERT OVERWRITE TABLE cosmohub. euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_0_c
SELECT * FROM jcarrete.euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_0
WHERE id IS NOT NULL;

I decide to add a new column in order to have the sedname as a integer values rather than as a string value:

CREATE TABLE euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_1_c(
id bigint COMMENT 'unique galaxy identifier',
`type` string COMMENT 'type (LBG for all objects)',
z float COMMENT 'Redshift (in the range [6-12])',
abs_muv float COMMENT 'absolute magnitude at 1500 angstroms',
sedname string COMMENT 'Spectral Energy Distribution name (among 76, 38 without UV lines, 38 with UV lines, corresponding to a given UV slope)',
sedname_int int COMMENT 'sedname as integer (from 0 to 75, from 0 to 37 without UV lines, from 38 to 75 with UV lines)',
rhalf_kpc float COMMENT 'half light radius (in physical kpc)',
rhalf_arcsec float COMMENT 'half light radius (in arcsec)',
muv float COMMENT 'for information only, observed magnitude at rest-frame 1500',
beta float COMMENT 'for information only, used to assign an SED'
)
CLUSTERED BY (
id)
SORTED BY (
id ASC)
INTO 4 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'
;

This is the command to convert sedname into sedname_int:

INSERT OVERWRITE TABLE cosmohub.euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_1_c
SELECT id, `type`,z, abs_muv, sedname,
CASE WHEN sedname = 'beta_1.25.dat' THEN 0
WHEN sedname = 'beta_1.30.dat' THEN 1
WHEN sedname = 'beta_1.35.dat' THEN 2
WHEN sedname = 'beta_1.40.dat' THEN 3
WHEN sedname = 'beta_1.45.dat' THEN 4
WHEN sedname = 'beta_1.50.dat' THEN 5
WHEN sedname = 'beta_1.55.dat' THEN 6
WHEN sedname = 'beta_1.60.dat' THEN 7
WHEN sedname = 'beta_1.65.dat' THEN 8
WHEN sedname = 'beta_1.70.dat' THEN 9
WHEN sedname = 'beta_1.75.dat' THEN 10
WHEN sedname = 'beta_1.80.dat' THEN 11
WHEN sedname = 'beta_1.85.dat' THEN 12
WHEN sedname = 'beta_1.90.dat' THEN 13
WHEN sedname = 'beta_1.95.dat' THEN 14
WHEN sedname = 'beta_2.00.dat' THEN 15
WHEN sedname = 'beta_2.05.dat' THEN 16
WHEN sedname = 'beta_2.10.dat' THEN 17
WHEN sedname = 'beta_2.15.dat' THEN 18
WHEN sedname = 'beta_2.20.dat' THEN 19
WHEN sedname = 'beta_2.25.dat' THEN 20
WHEN sedname = 'beta_2.30.dat' THEN 21
WHEN sedname = 'beta_2.35.dat' THEN 22
WHEN sedname = 'beta_2.40.dat' THEN 23
WHEN sedname = 'beta_2.45.dat' THEN 24
WHEN sedname = 'beta_2.50.dat' THEN 25
WHEN sedname = 'beta_2.55.dat' THEN 26
WHEN sedname = 'beta_2.60.dat' THEN 27
WHEN sedname = 'beta_2.65.dat' THEN 28
WHEN sedname = 'beta_2.70.dat' THEN 29
WHEN sedname = 'beta_2.75.dat' THEN 30
WHEN sedname = 'beta_2.80.dat' THEN 31
WHEN sedname = 'beta_2.85.dat' THEN 32
WHEN sedname = 'beta_2.90.dat' THEN 33
WHEN sedname = 'beta_2.95.dat' THEN 34
WHEN sedname = 'beta_3.00.dat' THEN 35
WHEN sedname = 'beta_3.05.dat' THEN 36
WHEN sedname = 'beta_3.10.dat' THEN 37
WHEN sedname = 'beta_1.25_wUVLines.dat' THEN 38
WHEN sedname = 'beta_1.30_wUVLines.dat' THEN 39
WHEN sedname = 'beta_1.35_wUVLines.dat' THEN 40
WHEN sedname = 'beta_1.40_wUVLines.dat' THEN 41
WHEN sedname = 'beta_1.45_wUVLines.dat' THEN 42
WHEN sedname = 'beta_1.50_wUVLines.dat' THEN 43
WHEN sedname = 'beta_1.55_wUVLines.dat' THEN 44
WHEN sedname = 'beta_1.60_wUVLines.dat' THEN 45
WHEN sedname = 'beta_1.65_wUVLines.dat' THEN 46
WHEN sedname = 'beta_1.70_wUVLines.dat' THEN 47
WHEN sedname = 'beta_1.75_wUVLines.dat' THEN 48
WHEN sedname = 'beta_1.80_wUVLines.dat' THEN 49
WHEN sedname = 'beta_1.85_wUVLines.dat' THEN 50
WHEN sedname = 'beta_1.90_wUVLines.dat' THEN 51
WHEN sedname = 'beta_1.95_wUVLines.dat' THEN 52
WHEN sedname = 'beta_2.00_wUVLines.dat' THEN 53
WHEN sedname = 'beta_2.05_wUVLines.dat' THEN 54
WHEN sedname = 'beta_2.10_wUVLines.dat' THEN 55
WHEN sedname = 'beta_2.15_wUVLines.dat' THEN 56
WHEN sedname = 'beta_2.20_wUVLines.dat' THEN 57
WHEN sedname = 'beta_2.25_wUVLines.dat' THEN 58
WHEN sedname = 'beta_2.30_wUVLines.dat' THEN 59
WHEN sedname = 'beta_2.35_wUVLines.dat' THEN 60
WHEN sedname = 'beta_2.40_wUVLines.dat' THEN 61
WHEN sedname = 'beta_2.45_wUVLines.dat' THEN 62
WHEN sedname = 'beta_2.50_wUVLines.dat' THEN 63
WHEN sedname = 'beta_2.55_wUVLines.dat' THEN 64
WHEN sedname = 'beta_2.60_wUVLines.dat' THEN 65
WHEN sedname = 'beta_2.65_wUVLines.dat' THEN 66
WHEN sedname = 'beta_2.70_wUVLines.dat' THEN 67
WHEN sedname = 'beta_2.75_wUVLines.dat' THEN 68
WHEN sedname = 'beta_2.80_wUVLines.dat' THEN 69
WHEN sedname = 'beta_2.85_wUVLines.dat' THEN 70
WHEN sedname = 'beta_2.90_wUVLines.dat' THEN 71
WHEN sedname = 'beta_2.95_wUVLines.dat' THEN 72
WHEN sedname = 'beta_3.00_wUVLines.dat' THEN 73
WHEN sedname = 'beta_3.05_wUVLines.dat' THEN 74
WHEN sedname = 'beta_3.10_wUVLines.dat' THEN 75
END as sedname_int,
rhalf_kpc, rhalf_arcsec, muv, beta
FROM jcarrete.euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_0
WHERE id IS NOT NULL;

ANALYZE TABLE cosmohub.euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_1_c COMPUTE STATISTICS FOR COLUMNS;

Then I create the catalog in CosmoHub.

@@ Line 66: / Line 66: @@
      SELECT * FROM jcarrete.euclid_sc8_simcat_lbgs_dpl_16_sqdegs_v1_0
      WHERE id IS NOT NULL;
 I decide to add a new column in order to have the sedname as a integer values rather than as a string value:

High redshift galaxies - Revision history

Jcarrete: /* Version 1.0 */

Jcarrete: Created page with "== Version 1.0 == Catalog provided by Rebecca and Pascal: https://euclid.roe.ac.uk/projects/puswg/wiki/Galaxies It is a ".txt" file. I remove the header and copy it into hd..."