This is the accepted version of the journal article: Tallada, Pau; Carretero, Jorge; Casals, Jordi; [et al.]. «CosmoHub : Interactive exploration and distribution of astronomical data on Hadoop». Astronomy and computing, Vol. 32 (July 2020), art. 100391. DOI 10.1016/j.ascom.2020.100391 This version is available at https://ddd.uab.cat/record/256252 under the terms of the license CosmoHub: Interactive exploration and distribution of astronomical data on Hadoop P. Talladaa,1,∗, J. Carreterob,1,∗∗, J. Casalsa,1 , C. Acosta-Silvab,1 , S. Serranoc,d , M. Caubeta,1 , F. J. Castanderc,d , E. Césarf , M. Croccec,d , M. Delfinob,1 , M. Eriksenb,1 , P. Fosalbac,d , E. Gaztañagac,d , G. Merinob,1 , C. Neissnerb,1 , N. Tonelloe a Centro de Investigaciones Energéticas, Medioambientales y Tecnológicas (CIEMAT), Avenida Complutense 40, 28040 Madrid, Spain b Institut de Fı́sica d’Altes Energies (IFAE), The Barcelona Institute of Science and Technology, Campus UAB, 08193 Bellaterra (Barcelona), Spain c Institute of Space Sciences (ICE, CSIC), Campus UAB, Carrer de Can Magrans, s/n, 08193 Bellaterra (Barcelona), Spain d Institut d’Estudis Espacials de Catalunya (IEEC), 08034 Barcelona, Spain e Barcelona Supercomputing Center (BSC), C/ Jordi Girona 29, 08034 Barcelona, Spain f Universitat Autònoma de Barcelona (UAB), 08193 Bellaterra (Barcelona), Spain Abstract We present CosmoHub (https://cosmohub.pic.es), a web application based on Hadoop to perform interactive exploration and distribution of massive cosmological datasets. Recent Cosmology seeks to unveil the nature of both dark matter and dark energy mapping the large-scale structure of the Universe, through the analysis of massive amounts of astronomical data, progressively increasing during the last (and future) decades with the digitization and automation of the experimental techniques. CosmoHub, hosted and developed at the Port d’Informació Cientı́fica (PIC), provides support to a worldwide community of scientists, without requiring the end user to know any Structured Query Language (SQL). It is serving data of several large inter- national collaborations such as the Euclid space mission, the Dark Energy Survey (DES), the Physics of the Accelerating Universe Survey (PAUS) and the Marenostrum Institut de Ciències de l’Espai (MICE) numerical simulations. While originally developed as a PostgreSQL relational database web frontend, this work describes the current version of CosmoHub, built on top of Apache Hive, which facilitates scalable reading, writing and managing huge datasets. As CosmoHub’s datasets are seldomly modified, Hive it is a better fit. Over 60 TiB of catalogued information and 50 × 109 astronomical objects can be interactively explored using an integrated visualization tool which includes 1D histogram and 2D heatmap plots. In our current implementation, online exploration of datasets of 109 objects can be done in a timescale of tens of seconds. Users can also download customized subsets of data in standard formats generated in few minutes. Keywords: Apache Hadoop, Apache Hive, Data exploration, Data distribution, FITS, ASDF 1. Introduction A substantial part of the success of a scientific project can be measured by the impact its results have on the scientific com- Experimental astronomy has entered in recent years into a munity. Also, having powerful tools to facilitate exploration new data regime, mainly due to the construction and develop- and distribution of data is key to boost their usage. With open ment of ground —and space— based sky surveys2 in the whole science principles in mind, cosmology surveys are developing electromagnetic spectrum, from gamma rays and X-rays, ul- different solutions to share and distribute their data, including traviolet, optical, and infrared to radio bands. This trend will analysis tools. increase with the next generation of projects, for example: (i) One of the most successful and innovative galaxy surveys is the future 3.2 GigaPixel LSST camera (LSST Dark Energy Sci- the Sloan Digital Sky Survey (SDSS) (York et al., 2000). The ence Collaboration, 2012) will take images every 30 seconds enormous success of this project is due to —besides the quality and the data rate will be about 15 terabytes per night3 , (ii) the of the data— the fact that its results are fully public and easily complete Euclid survey (Laureijs et al., 2012) represents hun- accessible5 . They have put great effort into facilitating scientific dreds of thousands images and several tens of petabytes of data; exploitation by any user, regardless of their technical expertise the final Euclid source catalog will contain about 1010 entries4 . (see Szalay et al. (2002) and Raddick et al. (2017)). Web interfaces are a common tool to provide access to cos- ∗ Main technical author mological datasets. For instance Cosmosim6 provides results ∗∗ Main scientific author from cosmological simulations performed within different projects. Email address: tallada@pic.es (P. Tallada) 1 also at Port d’Informació Cientı́fica (PIC), Campus UAB, C. Albareda s/n, The Millennium Run Observatory7 (Overzier et al., 2013) is a 08193 Bellaterra (Barcelona), Spain 2 See http://www.astro.ljmu.ac.uk/~ikb/research/galaxy- 5 https://www.sdss.org/dr15/ redshift-surveys.html for a non-complete list of galaxy surveys 3 https://www.lsst.org/about/dm 6 https://www.cosmosim.org/ 4 https://www.euclid-ec.org/ 7 http://galformod.mpa-garching.mpg.de/mrobs/ Preprint submitted to Astronomy and Computing March 11, 2022 theoretical virtual observatory which uses virtual telescopes to 2.1. Objectives observe semi-analytic galaxy formation simulations. The The- Most of the CosmoHub’s objectives originated from our oretical Astrophysical Observatory8 (Bernyk et al., 2016) also experience in designing and developing the PAU Survey data gives access to multiple popular simulated datasets but, in addi- management (PAUdm) (Tonello et al., 2019), and from the in- tion, the datasets can be funneled through higher-level modules teractions of the PAU Survey project with other peer projects to build custom mock galaxy catalogues and images. such as MICE (Fosalba et al. (2015), Crocce et al. (2015), Fos- Most recent surveys have also created dedicated portals to alba et al. (2014), Carretero et al. (2014) and Hoffmann et al. manage access to their data releases. For example, the Dark En- (2014)) and DES. The following list defines the set of key re- ergy Survey (The Dark Energy Survey Collaboration, 2005) has quirements for CosmoHub. produced the DES Science Portal (Gschwend et al., 2018). Fu- ture surveys like LSST are putting a tremendous effort into de- • Centralized data distribution signing adequate tools to access and analyze the massive amounts Having a unique point of data distribution enables hav- of data they will generate (Jurić et al., 2017). ing a single, authoritative version of the data, reducing We started developing CosmoHub in 2013, a web appli- the risk of duplicated, corrupted or deprecated replicas. cation for the interactive exploration and distribution of mas- A unique entry point also facilitates the enforcement of sive cosmological datasets. With its intuitive user interface, access controls and policies. users with no Structured Query Language (SQL, Chamberlin and Boyce (1974)) knowledge could visualize and download Note that, relying on a common platform does not im- customized subsets of the data. This first version used Post- ply having a single point of failure. This platform can be greSQL to handle the data, following a similar approach as configured in a high-availability setup where service is the one adopted by other projects (SDSS uses Microsoft SQL not disrupted by eventual failures of its individual com- Server, while DES uses an Oracle Database). A few years later, ponents (see Section 3). we started struggling with performance issues due to the in- • Easy to use creasing amounts of data we were managing, and we decided to revisit our design choices. Usability is also a key for the success of this kind of plat- This article describes CosmoHub as released in late 2016, form. The easier it is to use, the more users it will engage powered by Apache Hive, a data warehouse solution based on and, therefore, the data published on it will reach a wider Apache Hadoop which facilitates reading, writing and manag- audience, increasing their impact on the scientific com- ing large datasets. First described in Carretero et al. (2017), munity. CosmoHub is one of the earliest implementations of a stor- Interfaces should be clean and simple enough such that age and computing platform for cosmological datasets based on any user may use the service without prior training. In Apache Hive. Some other works have also used other Apache detail, the following two issues should be addressed: Hadoop components to deal with massive datasets, e.g. Apache Spark in Plaszczynski et al. (2019). – No Structured Query Language (SQL) knowledge In the version described in this paper, over 30 TiB of cat- must be required alogued information and 50 × 109 astronomical objects from a In a data distribution service, SQL is the most com- dozen different projects can be interactively explored using an mon interface for interacting with the data. SQL is integrated visualization tool which includes 1D histogram and a declarative language that provides a set of con- 2D heatmap plots. Interactive visualization of datasets of thou- structs to select, project, filter and retrieve subsets sands of millions of objects can be done in less than a minute, of information from a database. As an industry stan- and customized subsets can be generated in a timescale of min- dard, most (if not all) vendors of data warehouses utes. offer a SQL interface to interact with their own ser- This paper is structured as follows: section 2 describes the vices. main objectives of CosmoHub, our solution design and the evo- Exposing an SQL interface is problematic for at lution from the early prototypes, section 3 shows in detail the least two reasons: First, while SQL knowledge is current implementation: the Hadoop platform, the backend and common in technical circles, many scientific users the frontend. Section 4 presents some results and use cases and are unfamiliar with SQL. And second, SQL is an finally section 5 summarizes and concludes. industry standard, but has different vendor imple- mentations that deviate from official specifications. 2. CosmoHub These differences originate from adding complemen- tary features or because their implementation pre- This section presents an overview of CosmoHub objectives, dates the official specification. This means that even the technical implementation, and the historical development users with training on data warehouses might en- which led to the current form. counter problems because the SQL they know does not match the exact flavour used in a given solution. 8 https://tao.asvo.org.au/tao/ 2 – ”Common” file formats group acl user The astronomical community has grown used to a standard set of formats for data interchange. In par- ticular, the approach of the Virtual Observatory (VO) led by the International Virtual Observatory Alliance group_catalog query (IVOA9 ) is of most importance. Consequentially, specific tools for managing, processing and visual- izing data stored in these formats have been devel- oped and are widely used. Therefore, we must sup- catalog port those formats to enable our users to keep using the tools they already master and make the interop- erability with them as straightforward as possible. Figure 1: CosmoHub’s data model, which stores information about its catalogs, groups, users and access control lists (acl), among other metadata. This usability objective should not only be considered for end users, but also for system administrators. They that manage and/or produce large amounts of catalogued should be able to deploy and keep the service operational, data. Most of these data end up released to the public avoiding that eventual hiccups pose a threat to service and, if a replica is available in CosmoHub, any registered availability. user may access, explore and download it. • Custom datasets Furthermore, these projects usually have private datasets One of the main challenges of managing large datasets is for internal processes, such as release validation or cali- to be able to efficiently generate small customized subsets bration, which are only available to project members. fitting the scientists’ needs. • Metadata database Allowing the generation and download of custom subsets CosmoHub provides access to a collection of catalogs or enables users to minimize data storage and transfer costs. astronomical datasets hosted in our data warehouse. Each At the same time, processing costs are also reduced as the catalog is defined by a name, a short description and a selection and filtering part is offloaded onto the service, summary of its characteristics. Each catalog is mapped which has plenty of resources optimized for this task. to a single table in our data warehouse containing a set of As a downside, the service must cope with the additional columns, which are described by its data type and a short storage required for all (potentially overlapping) subsets description. that the users have requested, including some means to Each catalog belongs to one or several projects or re- eventually expire or purge them. search groups. Users can access all data in CosmoHub • Quick exploration associated to the groups they are member of. Users re- quest access to the groups when registering in the service. The ability to create custom datasets is not useful if the Each group has a set of users with special privileges who user does not know the exact criteria to specify them or are in charge of validating and granting the correspond- is not confident enough on the properties of the resulting ing membership requests. Access to specific groups or subset. Having some functionality for the user to inter- projects can be updated at any time through a web inter- actively explore and preview the results of subset gener- face. Users receive an email notification whenever their ation is very helpful. privileges change. A catalog may also be public and, as This quick exploration tool can be offered in different such, not require any specific membership to access it. ways, for example row sampling or simple visualizations Figure 1 shows the relational data model of all this meta- such as scatter plots and histograms. Once the users are data. In particular, in addition to storing the information certain that the subset matches their expectations, they about catalogs, groups and users, it also includes the re- can proceed with the download, as needed. lationships between them such as project membership or group administration privileges. 2.2. Solution design In this section we describe the main aspects of the design of • Web interface CosmoHub. CosmoHub is designed as a web application. This solu- tion only needs a web browser, a requirement that usually • Target audience comes preinstalled on any computer. Moreover, this also Prospective users of CosmoHub are the thousands of sci- allows to reuse all the user experience of the web seman- entists around the globe collaborating in astronomical projects tics and graphical metaphors that most users are already used to. 9 http://ivoa.net • Guided process 3 In order to facilitate the usage of the platform to those custom subset. For performance reasons they are limited users without SQL knowledge, the custom subset build- to less than 10k uniform bins, which is by far enough for ing interface has been designed as a guided series of steps most applications. that can be followed very easily even by the most inexpe- rienced user. • Batch custom subsets The web interface guides users through a sequence of When users finish exploring a subset, they can select a steps, allowing them to select catalogs they are interested download format and request its generation and delivery. in, then the columns they need, adding filtering criteria Among the formats, special attention must be put in sup- (if needed) and choosing the download format. porting Flexible Image Transport System (FITS) (Wells and Greisen, 1979), which is one of the most popular data They can also plot and preview the dataset they are build- formats in astronomy. ing with the integrated plotting tool, also implemented with intuitive and easy to use web forms to configure each The custom subset is built in the background by the un- type of plot (see section 3.4 for details). derlying database engine. When the custom subset is ready and stored, an email will be sent to the user with • SQL expert mode a link that they must follow in order to start the down- CosmoHub also offers the possibility to unleash the full load. power and capabilities of SQL. The ”expert” mode al- • Security lows to write an SQL query directly and passes it to the underlying database for its execution. This feature al- CosmoHub hosts private datasets and also serves as the lows more experienced users to define additional com- authoritative catalog repository for a number or projects. puted columns using standard functions and operators, As such, it is of utmost importance to ensure the integrity specify arbitrary groupings or even perform joins. The of any stored dataset and to prevent any unauthorized ac- latter is very interesting because it allows matching and cesses to them. All SQL queries, including those coming combining data from several different catalogs. from the ”expert” mode, are screened by the API. In this process, each SQL sentence is parsed by Hive and the list Furthermore, these capabilities can be extended by im- of catalogs to be accessed is retrieved. Then, each catalog plementing additional user defined functions (UDFs) through is checked against the user’s privileges stored in Cosmo- a set of JAR files. For now, users cannot add their own Hub’s metadata (see Figure 1). If a user tries to access UDFs for security reasons and are instead directed to a catalog without the required permissions, the action is contact us if they have a specific need. The list of im- logged and an error is returned. plemented UDFs, which is linked in the ”expert” mode, is available in the Help section10 and includes functions Furthermore, in order to better ensure the integrity of the for dealing with the HEALPix11 (Górski et al., 2005) pix- datasets, all sentences that are not pure SELECTs are fil- elization scheme. tered at the API level too. • (Simple) visualization 2.3. Early prototypes Users may get a quick insight on the data they are select- CosmoHub history can be traced back to the beginnings of ing by using the four integrated visualizations that Cos- the Physics of the Accelerating Universe Survey (PAUS) project moHub offers: in 2013. At that time, PAUS had started to produce its first simulated data that needed to be distributed to its collaborators. – Table overview shows 20 rows of the subset. In order to facilitate the distribution, a pilot web interface called – Scatter plot, to visualize trends and relations be- ”PAUdm Simulations Portal” was commissioned and integrated tween different columns. It also supports plotting into the official PAUdm operations web. This prototype offered different series of data, but is limited to plot only access to the PAUdm database hosted in a PostgreSQL server. 10k points, so users only see partial results/behaviour The amount of data stored in the prototype grew substan- of the full subset. Therefore, the plot may not be tially. Most of it came from external data used in PAUS pipelines, representative of the subset as a whole. such as SDSS star catalogs and MICE mock galaxy catalogs. In time, MICE started ingesting more of its own data in the same – 1D histograms and database, in order to be able to use the web interface. Through – 2D heatmaps, both with automatic hints on column several iterations we ended up implementing some dataset ex- names, and bin ranges. ploration features. From the beginning, CosmoHub was designed in a way that 1D histograms and 2D heatmaps are aggregated plots im- no specific technical knowledge was required in order to exploit plemented on the backend. They get the full picture of the its functionalities. In particular, users were already able to for- mulate queries without any SQL knowledge through a guided 10 https://cosmohub.pic.es/help process. Also, users were able to directly download Value- 11 http://healpix.sourceforge.net/ Added-Data (prebuilt catalogs or other information needed to 4 analyze the data, such as filter curves or dust maps), they could fundamental to achieve our objectives. Therefore, we researched visualize general data trends using simple plots and, of course, and tested several alternatives that we thought could be use- download custom subsets which were created asynchronously ful. There are multiple solutions in the market to handle large in the computing facilities at PIC. structured datasets in a manner that is scalable and has good After a year or so, the performance of the database server performance, such as NoSQL (or non-relational) databases and —designed specifically to host only PAUS data— started to suf- distributed relational databases. We only took into considera- fer. The amount of data hosted kept growing, mainly due to cat- tion the open source alternatives due to technical and economic alogs ingested from external projects, the storage space became reasons. tight and response times degraded. We knew from the beginning that one of the key require- In this situation, the first affected feature were the inter- ments of CosmoHub would be the ability to hold multiple large active plots, which at that time were limited to 10k rows and datasets, and to be able to analyze, compare and crossmatch queries taking less than 2 minutes to complete. With the in- them. The particular architecture of NoSQL solutions such as creasing size of the catalogs, most queries did not fulfill the HBase12 , Cassandra13 , MongoDB14 or Redis15 ) normally does response time requirements even using custom indexes. not allow for efficient joins between datasets. Furthermore, We ended up migrating to another instance of PostgreSQL each of them implement a different language for interacting database in a separate server, with much more storage space and with the data, with partial SQL support, at best. For these rea- similar processing power. In that way, we mitigated two prob- sons, we decided to discard the NoSQL solutions. lems: the limited storage available and the competition of com- For the distributed relational databases, we studied two ap- puting resources with the main PAUdm processing pipelines. proaches. First, we tested clustered implementations of tradi- Nevertheless, the problem of solving the long response times tional databases, such as Postgres-XL and Greenplum. These was hard to tackle. In our experience, traditional relational solutions rely on sharding and replicating the datasets onto mul- databases such as PostgreSQL can deal with huge datasets, as tiple nodes in a computer cluster. Queries are then split and long as you deal with them in small chunks. But when the re- routed to the proper nodes, which execute them assisted by a quested data is above a certain threshold, the PostgreSQL query central coordinator. Due to the reliance on partitions and in- optimizer does not use indexes as it is not efficient anymore. dexes, this kind of solutions are optimal for scaling out large That is the main reason why most of the queries ended up per- datasets. forming a sequential scan of the entire table, resulting in re- These kind of solutions are mostly engineered to the CRUD sponse times that can go from hours to even days. Last but (Create, Retrieve, Update and Delete) paradigm they have in- not least, modifying the schema and removing large amounts of herited, where each operation usually involves a small subset rows were extremely inefficient operations. of the total number of rows. In contrast, in the typical Cos- For these reasons we realized that PostgreSQL was not the moHub workflow, datasets are ingested and deleted always as right tool for CosmoHub’s data workflow and we explored dif- a whole, never updated, and usually retrieved in large subsets, ferent possibilities (see section 3.1) to solve the problem. In the or as aggregations of large subsets. In addition to the critical end we decided to use Apache Hive, a data warehouse based on differences in data workflow design, we found that it was not Hadoop. straightforward to implement or integrate new data formats on these solutions. 3. CosmoHub implementation Next, we tested solutions based on the Hadoop platform, such as Apache Hive (Thusoo et al., 2009) and Apache Im- This section introduces the implementation details of Cos- pala (Bittorf et al., 2015). Hive is an open-source data ware- moHub’s main components. First, we discuss the configura- house which has gained a lot of momentum since 2013, mostly tion of the Hadoop platform that hosts the data and supports thanks to the Hortonworks16 Stinger17 and Stinger.next18 ini- the processes that implement the available services, e.g. inter- tiatives. Impala is a massively parallel processing (MPP) SQL active plots and custom catalogs. Then, we describe how input query engine for data stored in Hadoop, and its most important catalogs are ingested and stored, how SQL queries are treated, contributor is Cloudera19 . which are the output formats available to users, and how con- When evaluating these alternatives, we found that Impala tents are generated. Next, we introduce the design and imple- timings were inconsistent, and in some cases, the results were mentation using Python of the Representational State Transfer incorrect. More up to date correctness studies have replicated (REST) API, which implements the available operations to the users, hiding the particular details of the underlying Hadoop 12 https://hbase.apache.org platform. Finally, we describe the implementation of the web 13 https://cassandra.apache.org/ interface and workflows that allow users to interact with Cos- 14 https://www.mongodb.com/ moHub. 15 https://redis.io 16 https://hortonworks.com/ 3.1. Hadoop Platform 17 https://es.hortonworks.com/blog/100x-faster-hive/ 18 https://es.hortonworks.com/blog/stinger-next- Assessing alternatives enterprise-sql-hadoop-scale-apache-hive/ From the experience gained from prototypes, we knew that 19 https://www.cloudera.com/ choosing the right data storage and processing platform was 5 Administration & Management (Ambari) Data warehouse Scripting Batch (Hive) (Pig) (MapReduce) NoSQL Database In-memory distributed (Sqoop / REST / ODBC) Workflow & Scheduling processing framework (HBase) Data Integration Orchestration Engine (Spark) Coordination (ZooKeeper) (Tez) (Oozie) Resource negotiator (YARN) Distributed storage (HDFS) Figure 2: Hadoop layered ecosystem, showing how multiple components stack on top of others to provide a broad set of features and services. In grey, the components used for CosmoHub, the same findings20 21 22 . Furthermore, the administration tools Resources are managed using scheduling queues, where each from the Cloudera Hadoop distribution were not free, compared user and group can get a fair share of resources as per configu- to the Hortonworks open source ones. Consequently, we de- ration. cided on a solution based on Apache Hive on top of Horton- The keystone of the Hadoop platform, the Hadoop Distributed works due to its stability, extensibility, comprehensive docu- File System (HDFS) (Shvachko et al., 2010) lays at the base of mentation and availability of free administration tools. the stack. HDFS is a high performance distributed filesystem that makes use of the storage of the nodes in a Hadoop cluster, Software stack merging it into a single name-space for increased capacity and Apache Hive is one of the multiple components in the Hadoop performance. Concisely, it works by splitting the files in fixed- ecosystem. Figure 2 displays a typical Hadoop architecture size blocks and then replicating those blocks between the avail- showing several of those components layered in a stack. Cos- able nodes in the cluster. This architecture allows very good moHub heavily relies on several of these components, specially resilience and scalability. on Apache Hive, which is a data warehouse software that facil- Additional components take care of security, user authenti- itates reading, writing, and managing large structured datasets cation and authorization, as well as the administration and con- located in distributed storage. From the administrator’s point of figuration of the different components. The market offers sev- view, migrating from the previous setup based on PostgreSQL eral distributions that include most of those components in the to Apache Hive is easy as both have the same interface (SQL) form of self-contained packages that facilitate an easy instal- with the data. lation and configuration and, in addition, commercial support. Apache Hive sits on top of Apache Tez (Saha et al., 2015), In our case, all these layers are provided by the Hortonworks an application framework which allows the execution of com- Data Platform (HDP) software solution. The currently installed plex directed-acyclic-graphs (DAG) of tasks for processing data. version is HDP 2.6.5. The scaffolding provided by Tez allows the orchestration and optimization of Hive tasks, even at runtime, boosting its perfor- Hardware structure mance. Figure 3 shows the hardware architecture of the Hadoop The computing needs of every Hadoop platform are deliv- platform used for CosmoHub. The current cluster is composed ered by Yet Another Resource Negotiator (YARN) (Vavilapalli of 16 compute nodes and 3 head nodes. The 16 compute nodes et al., 2013). YARN enables the execution of arbitrary tasks on are grouped in 4 dual-twin servers. Each node is equipped with top of containers executed in cluster nodes. Their resources are two 14-core CPUs, 128 GiB of RAM, two 6 TB SATA disks delimited and isolated, as to not interfere or starve each other. and a single 960 GB SSD disk. The SATA drives are config- ured with a small RAID-1 partition to host the OS, and the rest of space is used for HDFS storage (without RAID). The SSD 20 https://mr3.postech.ac.kr/blog/2018/10/30/performance- drive is devoted to cache intermediate files in order to boost evaluation-0.4/ 21 https://mr3.postech.ac.kr/blog/2019/03/22/performance- the efficiency of shuffle operations (Kambatla and Chen, 2014), evaluation-0.6/ such as CosmoHub joins. The compute nodes are located at our 22 https://mr3.postech.ac.kr/blog/2019/06/26/correctness- innovative oil-submersion cooling facility, which is very power hivemr3-presto-impala/ 6 External disk enclosure External disk enclosure Headnodes x32 x32 10 GbE 10 GbE 1 GbE 1 GbE 1 GbE 10 GbE Network switch Network switch 10 GbE 10 GbE 1 GbE x16 CosmoHub Hadoop node Hadoop node Figure 3: Hadoop cluster architecture, showing the configuration of the external disks and their logical links, the physically separated network for the nodes and external disk enclosures and the connection with the core network along with the headnodes and CosmoHub server. efficient (Acı́n et al., 2015). in some kind of ASCII23 format, like CSV. The main problem Because of the dense form factor used for the compute nodes, with this kind of formats is that they are not able to describe the there is not much space for expansion. Thus, in order to expand corresponding data types in a native way. In some cases, the the storage capacity, we provisioned two external disk enclo- data files are augmented with additional headers or they come sures, with 36 SATA disks of 2 TB. Each compute node mounts with attached documentation of the types and meaning of the 4 of those disks using iSCSI, two from each enclosure, account- different columns in the dataset. However, alternative binary ing for 32 of the 36 drives in each enclosure. The remaining 4 formats —such as FITS— are also common, and lately others drives are left as spare. The iSCSI disks appear as if they were such as Hierarchical Data Format 5 (HDF5) (Folk et al., 2011) local disks and are used for HDFS storage. have been gaining traction. A decisive advantage of these bi- All in all, the compute nodes sum 448 cores, 2 TiB of RAM, nary formats is that they can store the machine representation 192 TiB of local SATA storage space and 128 TiB of external of the values and they carry along detailed metadata, including iSCSI storage space. After taking into account the CPU and the description of data types and columns. Therefore, they are RAM reserved for Hadoop services in the compute nodes, and preferred in order to preserve as much information as possible the overhead that replicas introduce in HDFS storage, the ac- when ingesting a dataset into CosmoHub. tual resources available for processing are approximately ∼400 cores, 1.8 TiB RAM, ∼60 TiB of local SATA storage and ∼40 Raw data TiB of external iSCSI storage space. The original upstream data, in whatever raw format is pro- Each compute node and disk enclosure has a 10 GbE link vided, is copied into HDFS and then converted into a native to the same network switch. This switch is kept separated from Hive format that is suitable for efficient query processing. Op- the rest of the network infrastructure so that traffic peaks or sat- timized Row Columnar (ORC24 ) and Parquet25 are two con- uration do not affect other critical services at PIC. An additional tending formats in this area. 10 GbE link connects this switch to the rest of the PIC network, Features like columnar-based structure, push-down predi- where the head nodes and the CosmoHub application server are cate (PPD) capabilities, column statistics or bloom filters are located. very useful for query efficiency, as they enable to skip entire The head nodes are equipped with two 8-core CPUs, 32 GiB sections of rows that do not contain data of interest. While of RAM and 2 TB of local storage. They are configured in a both formats are very similar and support nearly the same set high-availability setup. In case of a failure, the service running of features, we decided to use ORC because it is the one that in another head node is able to promote itself to master and keep is recommended by Hortonworks, the chosen Hadoop platform the service functioning. distribution for CosmoHub. 3.2. Data workflow 23 https://tools.ietf.org/html/rfc20 CosmoHub hosts multiple datasets of different projects, ori- 24 https://orc.apache.org/specification/ORCv1/ gins, cardinalities and complexities, and all of them are stored 25 https://parquet.apache.org/ on HDFS. Most of the time, incoming raw data is published 7 Although Hive already performs very well just using the Data formats features described above, we tried to reduce even more the ex- CSV is a very well known ASCII format, broadly supported ecution times combining them with other capabilities. For in- among all kinds of programming languages, toolkits and spe- stance, even though joins are not an officially supported feature, cialized software. However, it is also very bulky. Compres- we use a combination of partitions and buckets to try to improve sion can greatly reduce its footprint, but not all compression its efficiency. For object catalogs, entries are usually bucketed codecs allow merging different files or streams into a single by id to enable efficient equi-joins on tables storing different one. Hadoop supports different compression codecs, includ- sets of properties (i.e. fluxes and photo-z). For non-object cata- ing GZip29 30 , BZip2 and XZ31 . GZip is fast, provides a de- logs, such as lensing maps, data is partitioned by redshift shell cent compression factor, but does not support the concatena- and then bucketed by HEALPix to facilitate joins based on sky tion of gzip-compressed streams. BZip2 and XZ both provide location. Hive had also limited support26 for single-table in- great compression factors at an increased computational cost, dexes that we deemed unnecessary for CosmoHub’s purposes. and both support the concatenation of its compressed streams. We decided to use BZip2 for its stability and broad support Interactive exploration compared to XZ. It is worth noticing that during our initial tests, Queries intended for visualization purposes should finish we also found some issues32 in the Hadoop XZ library. in a few seconds to satisfy the interactivity requirement. As FITS is an stable and mature format that has become the de- transferring large amounts of data to the browser for plotting is facto standard for astronomy data interchange. First defined in unfeasible, we use histograms and heatmaps to visualize large 1981, it has undergone several revisions (latest is v4.0 released datasets. CosmoHub rewrites the queries that feed the histogram in August 2018). However, it still carries on several limitations and heatmap plots in order to pre-aggregate the results and de- (see Thomas et al. (2015)) originating from the ancient hard- liver to the browser only the data points to be plotted. This ware it was designed for. minimizes network traffic and lessens the load on the client. In order to implement FITS as a download format in Cos- Furthermore, in order to speed up even more the execution moHub, the main limitation for us is its lack of streaming sup- of interactive queries, two resources queues have been set up in port. Without entering into much detail, FITS files are divided YARN. One queue is for batch tasks such as generating custom in headers and data sections. In our particular case, the FITS catalogs or other non-CosmoHub related jobs, while the other files used for storing tabular data define the schema of each is devoted solely to the execution of interactive queries. This row in the header, along with the number of rows. The row last queue has absolute priority over the resources and can even length depends on the schema and is equal for all rows. Both preempt resources from the other queue if needed in order to the header and data sections are null-padded to a multiple of ensure the fastest execution time possible. 2880 bytes and saved to disk. The problem is that the number We cannot make any guarantees regarding the response times, of rows that must be stored in the header is not determined until as the nature of the interaction between the users and Cosmo- the query has finished. By then, the output has already been Hub is very diverse and our resources are limited. Nevertheless, generated and HDFS does not allow random-access writes to a the results (see section 4.1) show that, in nearly all cases, the in- file, only appends. teractivity requirement is achieved. To circumvent this problem, we developed a custom Hive output format33 that stores just the data section (without padding). Custom catalogs We can compute the number of generated rows as the result of Custom catalogs are generated from an SQL statement where dividing the total size of the output by the row length once all the output is written concurrently as a set of files, each one of outputs have been written, given that the row length is fixed. them created by a different cluster node. WebHCat27 is used to Once we know the number of rows, streaming the results back orchestrate the execution of this SQL statement. In the previous to the user is easy. We generate the header on-the-fly from the version of CosmoHub, the catalogs were offered as a single file query metadata stored in the database, we pad it as a multiple download. In order to have a smooth transition, we wanted to of 2880 bytes and we serve it to the browser, appending after it keep providing the same download interface. all the output files the query has produced. At the end, we pad Therefore, the download formats that we could support would also the last output. The resulting downloaded file is a perfectly need to have a fast and simple way to merge the set of indi- compliant FITS file. vidual files into a single stream. Taking into account this re- ASDF is also supported as download format in CosmoHub. strictions, CosmoHub implements three different download for- It has been designed as the successor of FITS, claiming to re- mats: BZip2-compressed Comma-Separated Values (CSV28 ), move most of its problems. ASDF files are divided in several FITS and Advanced Scientific Data Format (ASDF) (Greenfield sections but, for our purposes, we are only interested in the tree et al., 2015). and the data sections. The schema is defined in the tree section 26 Indexes 29 https://tools.ietf.org/html/rfc1951 are no longer available in latest versions (v3.0 and later) 27 https://cwiki.apache.org/confluence/display/Hive/ 30 https://tools.ietf.org/html/rfc1952 31 https://tukaani.org/xz/ WebHCat 28 https://tools.ietf.org/html/rfc4180 32 https://github.com/yongtang/hadoop-xz/issues/9 33 https://github.com/ptallada/recarrayserde 8 and is serialized as a YAML Ain’t Markup Language (YAML) developed for the Python DB-API interface for Hive (PyHive) (Ben-Kiki et al., 2009) document. The rows are stored as binary in order to extract the progress of an ongoing query. This in- blocks in the data section. formation is relayed using a websocket38 connection, which en- In order to facilitate interoperability between FITS and ASDF, ables bidirectional communication between the browser and the the block binary format is compatible with FITS. This fact made backend. Through this channel, users receive periodic progress it very easy to add ASDF support in CosmoHub. As the rows updates about a query and can also request its cancellation. are already written in FITS format, we just need to generate the CosmoHub is deployed in three different instances corre- ASDF header and append all the outputs without any padding. sponding to the production, pre-production and test environ- ments. Each one of them runs on separate identical virtual ma- 3.3. Python backend chines, with 4 cores, 4 GiB of RAM and 10 GiB of storage CosmoHub allows users to process large datasets to obtain each. Only the production instance is accessible to the outside additional information. In order to decouple the results of those through https://cosmohub.pic.es. interactions from the way they are presented, all operations are The main software components used for building the back- carried out through a set of API calls. In particular, all actions end stack are Flask39 as the Python Web Server Gateway In- available to the user are implemented in an API that follows the terface (WSGI40 ) framework, Flask-RESTful41 as the REST REST (Fielding, 2000) paradigm. Operations are grouped in framework and gevent42 as the coroutine networking library. several endpoints depending on the data model entity they act This stack runs on top of uWSGI43 behind an NGINX44 proxy. on (see Figure 1). Database access is proxied through an Object For the data access layer, SQLAlchemy45 is used as the Relational Mapper (ORM) layer in order not to tie the imple- ORM component and the combination of psycopg46 and psycogreen47 mentation of each action to the data model specifics, enabling are used as the PostgreSQL driver and coroutine adapter library, data model evolution. All actions return JSON34 responses, respectively. Finally, astropy48 is used to implement FITS as a which are consumed by the web frontend. A complete list of download format and the ASDF49 python library to implement endpoints and their description is available in Table 1. ASDF format. Most catalogs in CosmoHub belong to a single project al- though, in some special cases, they can be associated with sev- 3.4. Web frontend eral projects. Only users which are members of those projects The web interface’s main objective is to enable the user to are able to access their corresponding data. In order to prevent access all of CosmoHub’s features which, as described in the unauthorized uses of CosmoHub, all requests are authenticated previous section, are available through a set of REST endpoints. and the user privileges are checked against the database. The Usability is a strong requirement, as the interface should be API accepts two authentication methods, basic and token. intuitive enough so that any user can interact with it with no With HTTP Basic authentication35 , each request must in- prior training. Special care should be taken to follow and ex- clude a username and password combination. This information ploit characteristic web semantics —such as forms, hyperlinks is looked up in the user database and, if no match is found, the or scrolling, among others— to aid the user at every step. In request is denied. The main inconvenience with this mecha- the end, designing a clean and simple interface, preferably self- nism is that each request requires a round-trip to the database. explanatory, is key to permitting open science. In order to soften the load on the database, a JSON Web Token36 CosmoHub’s frontend has been developed using modern, (JWT) is attached to every response. This token contains signed widely-supported, community technologies, such as AngularJS50 , information about the authenticated user and, when supplied on Bootstrap51 , WebSockets52 , Plot.ly53 and Wordpress54 . future requests, it allows the backend to verify the identity of AngularJS is a Javascript framework, developed and main- the user without any database involvement. tained by Google55 , that extends HTML to implement Model- Regarding the interactive exploration feature of CosmoHub, View-Controller capabilities into web browsers, making user as Hive queries are potentially executed on all nodes in the 38 https://tools.ietf.org/html/rfc6455 Hadoop platform, a full table scan usually takes about a minute. 39 http://flask.pocoo.org/ Combined with sampling, results can be obtained even faster. 40 https://www.python.org/dev/peps/pep-3333/ This performance allowed us to implement interactive explo- 41 https://flask-restful.readthedocs.io ration of large datasets using histograms and heatmaps. As also 42 http://www.gevent.org/ 43 https://uwsgi-docs.readthedocs.io mentioned in 3.2, data for these plots is pre-aggregated on the 44 https://www.nginx.com/ Hive side using a specially constructed query and only the data 45 https://www.sqlalchemy.org/ points to be plotted are sent to the browser. 46 http://initd.org/psycopg/ Additionally, in order to provide some feedback to the user 47 https://bitbucket.org/dvarrazzo/psycogreen during the execution of interactive queries, an extension37 was 48 https://www.astropy.org/ 49 https://asdf-standard.readthedocs.io 50 https://angularjs.org/ 51 https://getbootstrap.com/ 34 https://tools.ietf.org/html/rfc7159 52 https://tools.ietf.org/html/rfc6455 35 https://tools.ietf.org/html/rfc7617 53 https://plot.ly/ 36 https://tools.ietf.org/html/rfc7519 54 https://wordpress.com/ 37 https://github.com/dropbox/PyHive/pull/136 55 https://google.com 9 URL Method Description GET Retrieve current user profile PATCH Update profile data (i.e. email, password) /user POST Register a new user DELETE Remove a user account /groups GET Retrieve the list of groups GET Retrieve the list of users and their memberships /acls PATCH Modify a user’s membership /catalogs GET Retrieve the list of catalogs accessible to the current user /catalogs/{id} GET Retrieve detailed information of a catalog /catalogs/syntax GET Perform an SQL syntax check /downloads/datasets/{id}/readme GET Download the README file for a dataset /downloads/files/{id}/readme GET Download the README file for a value-added file /downloads/files/{id}/contents GET Download the contents of a value-added file /downloads/queries/{id}/results GET Download the output of a custom catalog GET Retrieve the list of custom catalogs for the current user /queries POST Request the generation of a custom catalog /queries/{id}/cancel POST Abort the generation of a custom catalog /queries/{id}/done GET Callback to notify the completion of a custom catalog /contact POST Send a message to the CosmoHub Team Table 1: List of REST API endpoints, grouped by entity. For each one, its URL pattern, the HTTP method and a brief description is shown. interactions dynamic, faster and more fluid. It has a large col- Finally, WordPress is one of the most used content man- lection of official and third-party plugins and is specially de- agement systems (CMS). Although it is mostly associated with signed to interact with API based applications, such as Cos- blogs, CosmoHub uses it as a backend for editing the content moHub. Being open-source, well maintained and with a broad of dynamic sections, such as the news feed. community of users and developers are key aspects for choos- When a user visits CosmoHub (https://cosmohub.pic. ing it as the base of the frontend. es), it is presented with the initial page shown in Figure 4. This Bootstrap is an open-source HTML, JavaScript and Cas- front page describes its goals, showcases its main features and cading Style Sheets (CSS) library created and maintained by holds references to the rest of public contents: about page, news Twitter56 for responsive web design. It is one of the most used feed, terms of use and a link to the Twitter profile. All these styling frameworks in the entire web development community, sections can be accessed without authentication. providing users with clear and coherent interfaces. It comes with a handy and easy to configure column-based layout, plus User management some predefined style elements. These features are extensively Authentication is required in order to access CosmoHub’s used in CosmoHub. main features. For this, users have to enter their credentials in WebSockets is a technology for bi-directional communica- the login form. If they forgot their credentials, they can enter tion between web browsers and web servers. This technique their email address in the reset password form. A link will be involves upgrading a stateless HTTP request into a persistent sent to that address that allows them to set up a new password. TCP connection that can be subsequently used to transfer in- If they do not have an account, they can fill in a registra- formation from both parties. Some features such as real-time tion form. A captcha58 protects this form from automated/spam progress monitoring require websockets to work properly. registration attempts. Upon completion, users receive an email Plot.ly is an open-source plotting library based on the widely with a link they must open to confirm ownership of the email used D3.js57 web visualization framework. It greatly simplifies address provided and, immediately afterwards, they are granted the programming needed to implement all sorts of charts and access to the public catalogs. Access to private data from addi- dashboards, such as those used for interactive exploration. tional projects has to be manually validated by project adminis- 56 https://twitter.com 58 https://www.google.com/recaptcha 57 https://d3js.org/ 10 trators. Requests are usually processed within the day, using a specific interface (not shown here). Finally, users can access their profile page which displays their personal details such as name, email address and their project membership. They can update this information, includ- ing their password, or request the removal of their account. All personal data from registered users is stored and processed fol- lowing GDPR regulations. Interactive exploration Just after logging in, users are presented with the catalog selection page. As shown in Figure 5, each catalog appears listed with a name, a short description, the origin (observed or simulated data) and the date it was uploaded to CosmoHub. The headers on the top of each column allow to sort the list for each field, while the top search box allows user to restrict the listing to only those catalogs Selecting any entry brings them to the catalog page (Figures 6 and 7), where they can build their own custom catalogs or subsets for interactive exploration and/or download. In order to guide them, the subset construction process is divided in a series of steps, which can be traversed through scrolling or using the navigation bar fixed at the top. The first piece of information a user encounters is a com- plete description of the catalog, usually provided by the catalog owner. Just below, an optional section called ”Value Added Data” contains links and documentation to additional data that complements this catalog and that may be useful to analyze it, such as filter curves or extinction maps. Users can use a predefined dataset (Step 0) or create a new one from scratch (Step 1). Predefined datasets are curated op- tions with specific purposes, although users can modify them to suit their needs. For each dataset listed in Step 0, its name, ver- sion, description, type and number of rows is shown. There are two types of predefined datasets: basic datasets use the guided interface to configure the subset, while expert datasets resort to setting up directly the SQL statement in expert mode. In order to build a custom catalog from scratch, users start choosing the set of columns they need from Step 1. The search box on the top right allows users to filter the columns display looking for partial matches on names and comments. Steps 2 and 3 represent two methods available to users for restricting the number of rows contained in their custom cata- log. On the one hand, with row sampling (Step 2), Hive can be configured to only read a fraction of the files that store the catalog’s data. In an attempt to deliver statistically unbiased subsets, rows are divided in those files not following any ac- tual property —such as position, mass, luminosity, etc.— but a pseudo-random value, usually a surrogate key. In addition, users may specify in Step 3 any arbitrary criteria to further re- strict the resulting rows. A filtering criteria consists of a col- umn, an operator and a constant value. If multiple criteria are specified its effects are combined, thus only rows fulfilling all Figure 4: Initial page, showcasing the main projects and features. Note that criteria are returned. some content has been edited for presentation purposes. Step 4 displays the corresponding SQL statement constructed from the options selected in the previous steps. If the guided 11 Figure 5: Catalog selection page, showing a subset of current public catalogs. interface capabilities are not enough, or users are proficient us- Custom catalogs ing SQL, the Expert mode can be enabled by clicking a button. Whenever users are satisfied with the properties of their cus- From that point on, the guided interface will be disabled and tom catalog, they can request it to be generated and stored in the SQL sentence can only be modified by manually editing it a specific format to be later downloaded. Step 6 shows the in the text area. supported formats: CSV with BZip2 compression, FITS and Once the custom catalog has been defined, users can inter- ASDF. Finally, after selecting the desired download format, actively explore its properties using any of the 4 visualization users have to read and accept the corresponding data usage and tools in Step 5. (i) The table preview shows the first 20 rows in citation guidelines in Step 7. Once requested, the custom cat- the subset and it is mainly used to have a glance at the results. alog is assigned a unique identifier, so that users may track its (ii) The scatter plot allows to display the relationship between progress several properties. However, as it cannot aggregate data, it is When a custom catalog is completed, users receive an email limited to ten thousand points. (iii) 1D histograms can be gen- directing them to the Activity page (see Figure 10) in order to erated from any column using a configurable number of uni- download it. Users may also use this page to follow the progress formly sized bins. The upper and lower bound of the bins are of their custom catalog requests. Finished catalogs are kept automatically hinted based on the column statistics stored by a minimum of 30 days and are eventually deleted to maintain Hive. An example is shown in Figure 8. (iv) 2D heatmaps, as enough free storage space. scatter plots, display the relation between two properties using rectangular bins. As with 1D histograms, bin ranges are filled 4. Results in from column statistics. Also, the metric can be selected be- tween COUNT, AVG, MAX or MIN, in order to display the num- In this section we present some of the most relevant re- ber of rows, average, maximum or minimum value for each bin, sults and successful use cases of the version of CosmoHub as respectively. Figure 9 shows an example. described in this article, which was commissioned in October After filling in the required fields, pressing the Play button 2016. The results are separated into two sections, the first one will start the process to generate the visualization. The status contains a quantitative analysis of the volume, timing and per- is presented using a progress bar with two different stages. In formance of the service as a whole, while the second section static color, the amount of work that has been completed, and in contains specific scientific applications where CosmoHub is be- a moving pattern the amount that is being executed right now. ing used. When the progress bar becomes all filled and static, the results are downloaded and displayed on screen. All plots can have 4.1. Quantitative analysis customized display options such as axis scaling (linear or loga- Since this version opened for public use, CosmoHub has rithmic), axis direction (increasing or decreasing) or switching been constantly growing in all relevant metrics, such as num- to a cumulative plot. Also, visualizations can be zoomed in and ber of users, number of catalogs and volume of published data out, exported as a Portable Network Graphics (PNG) image or (see Figure 11). Note that the information in this figure only downloaded as a CSV file for additional processing. accounts for data published through CosmoHub, excluding cus- tom catalogs. 12 Figure 6: Catalog page upper half, showing catalog’s description, valued added data and steps 0 to 3. 13 Figure 7: Catalog page bottom half, showing steps 4 to 7. 14 Catalog Rows Fields Size ALHAMBRA — S/G classified 422 K 7 10.4 MiB — photometric redshifts 441 K 113 149.5 MiB CFHTLens 29 M 129 7.5 GiB COSMOS 2015 1.2 M 537 1.6 GiB Gaia DR1 1,143 M 62 174.9 GiB Gaia DR2 1,693 M 98 486.5 GiB KiDS DR4 100 M 306 89.6 GiB MICECAT 1 205 M 91 59.9 GiB MICECAT 2 500 M 122 211.1 GiB PAUS-COSMOS EDR 6.5 K 126 2.8 MiB PAU-MillGas Lightcone 7.4 M 34 9.0 GiB Zest 132 K 71 20.9 MiB Figure 8: 1D histogram, displaying the number of galaxies in MICECAT2 with Table 2: List of public catalogs in CosmoHub. true redshift between 0.4 and 0.6, grouped by absolute magnitude in a hundred uniformly sized bins. times stable. Figure 13 provides information about the distribution of re- sponse times by plotting the completion ration for both interac- tive queries and custom catalogs. The completion ration is de- fined as the fraction of queries completed after a given elapsed time. Note that the orchestration of the different tasks on the cluster nodes has a minimum overhead of about 10-12 seconds. Only queries that can be answered directly from statistics, such as selecting the number of rows or the maximum value of a column without filters, return in a shorter time. About 66.9% of all interactive queries finish in less than 30 seconds, while 96.8% of them finish in less than 2 minutes. These response times enable users to interactive explore any dataset, without worrying about its volume or the complexity of the query. On the other hand, for custom catalogs, 71.0% are produced in less than 3 minutes, while 97.4% of them finish in 30 minutes. Although not as relevant as for interactive queries, Figure 9: 2D heatmap, displaying a color-magnitude diagram of MICECAT2. keeping a low response time for the generation of custom cata- logs is also important to keep a good user experience. Since late 2016, more than 600 new users have opened an Figure 14 shows the evolution of active users over time. account, and more than 4,000 custom catalogs and nearly 10,000 Dates of particular scientific events are overlayed. A clear cor- interactive queries have been delivered. Data growth has been relation can be seen, particularly with the Euclid Consortium limited by the available storage space. By the end of 2019, our meeting held yearly every June, where interest on newly re- current expectations are to double the amount of published data. leased data boosts user activity. A peak in activity can also be At the same time, performance in terms of response time seen coinciding with Gaia Data Release 2 (DR259 ). The catalog has been stable. Figure 12 shows that the average response time was mirrored in CosmoHub in less than 12h and we were able for the execution of interactive queries and the generation of to release it almost simultaneously to the official announcement custom catalogs has barely increased over time. Statistical fluc- at the Europen Space Astronomy Centre (ESAC60 ). tuations are due to resource contention with concurrent queries and scheduling overheads, among others. 4.2. Scientific applications It is worth noticing that, since its commissioning, the archi- CosmoHub supports multiple international cosmology projects, tecture and configuration of the Hadoop platform has seen sev- the most relevant in terms of users and data volume being Eu- eral reorganizations, although the only resource that has been clid 61 . Euclid simulations require the production of extremely increased is the storage capacity. Therefore, by improving and large datasets. In their Flagship simulation62 (paper in prepara- tuning the platform, we have been able to cope with the growth tion), with an estimated final size of 20 TiB and nearly 30 × 109 in users and data volume and to keep the response time stable. Nevertheless, we do not expect to be able to squeeze much more 59 https://www.cosmos.esa.int/web/gaia/dr2 60 http://www.esa.int/About_Us/ESAC performance of the actual setup so, in the future, additional re- 61 http://sci.esa.int/euclid/ sources will be required in order to reduce or keep the response 62 https://www.euclid-ec.org/?page_id=4133 15 Figure 10: Activity page, used to follow custom catalog creation progress and to download them when they are ready. 40T 1.0 Data volume 70 60 0.8 30T completion ratio 50 0.6 20T 40 30 0.4 10T 20 0.2 Custom catalogs Catalogs 10 0.0 Interactive queries 0T 0 2017 2018 2019 10s 1m 5m 1h 1d Figure 11: Evolution over time of the number of available catalogs and size of Figure 13: Completion ratio as a factor of processing time for batch catalogs published data. and interactive queries. 350 Euclid Consortium meeting 15m Custom catalogs 300 Gaia DR2 Interactive queries 250 processing time active users 10m 200 150 5m 100 50 0m 0 2017 2018 2019 2017 2018 2019 Figure 14: Active users over time, with relevant milestones highlighted. Upper Figure 12: Processing time in minutes (monthly average) for batch catalogs and and lower bounds correspond to active users within a window of 28 days and 7 interactive queries. Shaded area shows one sigma deviation. days respectively, while middle line is for a 14 days window. 16 objects, they are using as input the largest dark-matter halo cat- and the Euclid simulation group have selected CosmoHub as alog up to date, with 5.5 TiB and 40 × 109 objects. CosmoHub their primary data distribution service, which was also one of is currently providing access to the entire halo catalog and to our objectives. several productions of mock galaxy catalogs (these account for The decision to delegate CosmoHub’s data processing to about half of the data stored in CosmoHub). The Euclid cos- Hadoop and Hive (see section 3.1) has proven to be a good mological simulations validation team makes heavy use of the choice. The reliability and high performance exhibited by this exploration capabilities of CosmoHub in order to validate data data warehouse solution, combined with the usability and pow- prior to its release to the full collaboration. Then, Euclid sci- erful features implemented in CosmoHub’s interface, have en- entists use CosmoHub to download customized subsets of the abled to steadily support a growing number of users and projects. data for further analysis and processing. Also, the great scalability of the platform has allowed to keep At the time of this writing, up to 24 publications have ac- response times low at all times (see section 4.1), in spite of the knowledged CosmoHub contribution to their results. Projects constant increase in data volume. In the end, the results pre- such as PAU Survey63 and MICE64 use CosmoHub as the offi- sented support the fact that CosmoHub is providing a useful cial and primary channel for the distribution of their data (Erik- service to the scientific community with a high quality of ser- sen et al. (2019) and Cabayol et al. (2018)). Other projects vice, as proven by the use of CosmoHub by some of the most such as DES65 and Gaia66 have a replica of their most important relevant projects in cosmology (see section 4.2). releases, and several publications have made use of them (see When CosmoHub entered into service in late 2016, it was Serenelli, Aldo et al. (2019) and Sevilla-Noarbe et al. (2018)). the first project to apply Hadoop to the analysis and distribu- The subset of public catalogs in CosmoHub (as of October 2019) tion of large cosmological datasets. Over these years we have is shown in Table 2. learnt a lot from both our own experience and user’s feedback. Finally, other applications not based solely on the distribu- In fact, we are already working on the next iteration of Cos- tion of data are also present, such as the one from the DES clus- moHub which will include a lot of improvements based on this tering science working group. They made intensive use of the experience: exploration capabilities to define and test many different arbi- (i) Regarding the Hadoop platform, upgrade it to the lat- trary galaxy subsamples to estimate the Baryon Acoustic Oscil- est HDP release. The most exciting new features include the lations (BAO) feature in the galaxy distribution (Crocce et al., possibility to reduce replica overhead using erasure coding, the 2019). Some particularly useful applications are shown in Ap- ability to access a read-only view of externally provided storage pendix A. Lastly, some projects have also cited CosmoHub as and the implementation of materialized views in Hive to speed a state-of-the-art reference to their own data publication proce- up join queries. dures, such as Heitmann et al. (2019) and Nelson et al. (2019). (ii) From CosmoHub application’s perspective, add the abil- ity for users to upload their own catalogs and to publish and share them with other users, extend and optimize the visualiza- 5. Conclusions and future work tion tools performance, and improve the general responsiveness This paper presents CosmoHub, our vision to enable the of the user interface. We also want to implement some VO pro- interactive exploration and distribution of large cosmological tocols such as the Table Access Protocol (TAP) to enable pro- datasets on top of Hadoop. The paper describes the main fea- grammatic access to launch SQL queries and retrieve results, tures and capabilities of CosmoHub from the user’s point of and to integrate with Jupyter Notebooks to be able to create ar- view but, more importantly, it also details all the research and bitrary plots or perform custom analysis. There are plans to decisions made regarding its design and implementation. provide guided analysis tasks to compute the 2-point galaxy Regarding the design (explained in section 2.2), it is fo- correlation function, to generate mock galaxy catalogs using cused on satisfying a set of needs (enumerated in section 2.1) the Halo Occupation Distribution (HOD) model or to compute gathered from the scientific community, while the experience the galaxy photometric redshift, among others. gained through the early prototypes (described in section 2.3) With all this future work under way, we are prepared to keep helped pave the way to achieve its current success. In partic- pushing forward and to help put in place the next generation ular, the easy to use requirement has been met by implement- of services for managing large volumes of structured scientific ing CosmoHub as a web application, with a guided process to data. remove any SQL knowledge dependency, and the support for common data formats such as CSV and FITS. Also, the abil- Acknowledgements ity to produce visualizations to get insight over thousands of millions of rows in just a few seconds fulfills the interactive ex- CosmoHub has been partially funded through projects of ploration requirement. Several projects such as PAUS, MICE the Spanish national program “Programa Estatal de I+D+i” of the Spanish government. The support of the ERDF fund is gratefully acknowledged. 63 https://www.pausurvey.org/ 64 http://maia.ice.cat/mice/ We are also deeply grateful to all PIC’s staff, both for the 65 https://www.darkenergysurvey.org/ encouragement from the management and for the tremendous 66 http://sci.esa.int/gaia/ help and support we get from our IT colleagues. 17 Finally, our most sincere thanks to CosmoHub’s community Gschwend, J., Rossel, A.C., Ogando, R.L.C., Neto, A.F., Maia, M.A.G., da of users, for their invaluable feedback and advice. Costa, L.N., Lima, M., Pellegrini, P., Campisano, R., Singulani, C., Adean, C., Benoist, C., Aguena, M., Carrasco Kind, M., Davis, T.M., de Vicente, J., Hartley, W.G., Hoyle, B., Palmese, A., Sadeh, I., Abbott, T.M.C., Ab- References dalla, F.B., Allam, S., Annis, J., Asorey, J., Brooks, D., Calcino, J., Car- ollo, D., Castander, F.J., D’Andrea, C.B., Desai, S., Evrard, A.E., Fosalba, Acı́n, V., Delfino, M., Herbera, A., Hernández, J., 2015. Free cooling on the P., Frieman, J., Garcı́a-Bellido, J., Glazebrook, K., Gerdes, D.W., Gruendl, Mediterranean shore: Energy efficiency upgrades at PIC. J. Phys. Conf. Ser. R.A., Gutierrez, G., Hinton, S., Hollowood, D.L., Honscheid, K., Hoor- 664, 052009. mann, J.K., James, D.J., Kuehn, K., Kuropatkin, N., Lahav, O., Lewis, G., Ben-Kiki, O., Evans, C., Ingerson, B., 2009. YAML aint markup language Lidman, C., Lin, H., Macaulay, E., Marshall, J., Melchior, P., Miquel, R., (YAML) version 1.2. YAML.org . Möller, A., Plazas, A.A., Sánchez, E., Santiago, B., Scarpine, V., Schindler, Bernyk, M., Croton, D.J., Tonini, C., Hodkinson, L., Hassan, A.H., Garel, T., R.H., Sevilla-Noarbe, I., Smith, M., Sobreira, F., Sommer, N.E., Suchyta, E., Duffy, A.R., Mutch, S.J., Poole, G.B., Hegarty, S., 2016. The Theoretical Swanson, M.E.C., Tarle, G., Tucker, B.E., Tucker, D.L., Uddin, S., Walker, Astrophysical Observatory: Cloud-based Mock Galaxy Catalogs. ApJS 223, A.R., 2018. DES science portal: Computing photometric redshifts. Astron- 9. 1403.5270. omy and Computing 25, 58–80. 1708.05643. Bittorf, M., Bobrovytsky, T., Erickson, C., Hecht, M.G.D., Kuff, M., Leblang, Heitmann, K., Uram, T.D., Finkel, H., Frontiere, N., Habib, S., Pope, D.K.A., Robinson, N., Rus, D.R.S., Wanderman, J., Yoder, M.M., 2015. A., Rangel, E., Hollowed, J., Korytov, D., Larsen, P., 2019. HACC Impala: A modern, open-source sql engine for hadoop, in: Proceedings of Cosmological Simulations: First Data Release. arXiv e-prints , the 7th Biennial Conference on Innovative Data Systems Research. arXiv:1904.119661904.11966. Cabayol, L., Sevilla-Noarbe, I., Fernández, E., Carretero, J., Eriksen, M., Ser- Hoffmann, K., Bel, J., Gaztanaga, E., Crocce, M., Fosalba, P., Castander, F., rano, S., Alarcón, A., Amara, A., Casas, R., Castander, F.J., deVicente, J., 2014. Measuring the growth of matter fluctuations with third-order galaxy Folger, M., Garcı́a-Bellido, J., Gaztanaga, E., Hoekstra, H., Miquel, R., correlations. MNRAS . Padilla, C., Sánchez, E., Stothert, L., Tallada, P., Tortorelli, L., 2018. The Jurić, M., Ciardi, D., Dubois-Felsmann, G., 2017. LSST Science Platform PAU survey: stargalaxy classification with multi narrow-band data. MNRAS Vision Document. LSST Document LSE-319 https://lse-319.lsst.io . 483, 529–539. 1806.08545. Kambatla, K., Chen, Y., 2014. The truth about mapreduce performance on Carretero, J., Castander, F.J., Gaztaaga, E., Crocce, M., Fosalba, P., 2014. ssds, in: Proceedings of the 28th USENIX Conference on Large Installa- An algorithm to build mock galaxy catalogues using MICE simulations. tion System Administration, USENIX Association, Berkeley, CA, USA. pp. MNRAS 447, 646–670. http://oup.prod.sis.lan/mnras/article- 109–117. pdf/447/1/646/4911622/stu2402.pdf. Laureijs, R., Gondoin, P., Duvet, L., Saavedra Criado, G., Hoar, J., Amiaux, Carretero, J., Tallada, P., Casals, J., Caubet, M., Castander, F., Blot, L., Alarcn, J., Auguères, J.L., Cole, R., Cropper, M., Ealet, A., Ferruit, P., Escudero A., Serrano, S., Fosalba, P., Acosta-Silva, C., Tonello, N., Torradeflot, F., Sanz, I., Jahnke, K., Kohley, R., Maciaszek, T., Mellier, Y., Oosterbroek, Eriksen, M., Neissner, C., Delfino, M., 2017. Cosmohub and scipic: Mas- T., Pasian, F., Sauvage, M., Scaramella, R., Sirianni, M., Valenziano, L., sive cosmological data analysis, distribution and generation using a big data 2012. Euclid: ESA’s mission to map the geometry of the dark universe, in: platform, p. 488. Space Telescopes and Instrumentation 2012: Optical, Infrared, and Millime- Chamberlin, D.D., Boyce, R.F., 1974. SEQUEL: A structured english query ter Wave, p. 84420T. language, in: ACM SIGMOD, p. 249264. LSST Dark Energy Science Collaboration, 2012. Large Synoptic Survey Tele- Crocce, M., Castander, F.J., Gaztaaga, E., Fosalba, P., Carretero, J., 2015. scope: Dark Energy Science Collaboration. arXiv e-prints 1211.0310. The MICE Grand Challenge lightcone simulation II. Halo and galaxy cata- Nelson, D., Springel, V., Pillepich, A., Rodriguez-Gomez, V., Torrey, P., Genel, logues. MNRAS 453, 1513–1530. http://oup.prod.sis.lan/mnras/ S., Vogelsberger, M., Pakmor, R., Marinacci, F., Weinberger, R., 2019. The article-pdf/453/2/1513/3940111/stv1708.pdf. IllustrisTNG simulations: public data release. Computational Astrophysics Crocce, M., Ross, A.J., Sevilla-Noarbe, I., Gaztanaga, E., Elvin-Poole, J., and Cosmology 6, 2. 1812.05609. Avila, S., Alarcon, A., Chan, K.C., Banik, N., Carretero, J., 2019. Dark Overzier, R., Lemson, G., Angulo, R.E., Bertin, E., Blaizot, J., Henriques, Energy Survey year 1 results: galaxy sample for BAO measurement. MN- B.M.B., Marleau, G.D., White, S.D.M., 2013. The Millennium Run Ob- RAS 482, 2807–2822. 1712.06211. servatory: first light. MNRAS 428, 778–803. 1206.6923. Eriksen, M., Alarcon, A., Gaztanaga, E., Amara, A., Cabayol, L., Carretero, Plaszczynski, S., Peloton, J., Arnault, C., Campagne, J.E., 2019. Analysing J., Castander, F.J., Crocce, M., Delfino, M., DeVicente, J., Fernandez, E., billion-objects catalogue interactively: ApacheSpark for physicists. Astron- Fosalba, P., Garcia-Bellido, J., Hildebrandt, H., Hoekstra, H., Joachimi, B., omy and Computing 28, 100305. 1807.03078. Norberg, P., Miquel, R., Padilla, C., Refregier, A., Sanchez, E., Serrano, Raddick, J., Souter, B., Lemson, G., Taghizadeh-Popp, M., 2017. SciServer: S., Sevilla-Noarbe, I., Tallada, P., Tonello, N., Tortorelli, L., 2019. The An Online Collaborative Environment for Big Data in Research and Educa- PAU Survey: early demonstration of photometric redshift performance in tion, in: American Astronomical Society Meeting Abstracts #229, p. 236.15. the COSMOS field. MNRAS 484, 4200–4215. http://oup.prod.sis. Saha, B., Shah, H., Seth, S., Vijayaraghavan, G., Murthy, A., Curino, C., 2015. lan/mnras/article-pdf/484/3/4200/27747421/stz204.pdf. Apache tez: A unifying framework for modeling and building data process- Fielding, R.T., 2000. Architectural Styles and the Design of Network-based ing applications, in: Proceedings of the 2015 ACM SIGMOD International Software Architectures. Ph.D. thesis. AAI9980887. Conference on Management of Data, ACM, New York, NY, USA. pp. 1357– Folk, M., Heber, G., Koziol, Q., Pourmal, E., Robinson, D., 2011. An overview 1369. of the hdf5 technology suite and its applications, in: Proceedings of the Serenelli, Aldo, Rohrmann, René D., Fukugita, Masataka, 2019. Nature of EDBT/ICDT 2011 Workshop on Array Databases, ACM. pp. 36–47. blackbody stars. A&A 623, A177. Fosalba, P., Crocce, M., Gaztaaga, E., Castander, F.J., 2015. The MICE Sevilla-Noarbe, I., Hoyle, B., Marchã, M.J., Soumagnac, M.T., Bechtol, K., grand challenge lightcone simulation I. Dark matter clustering. MN- Drlica-Wagner, A., Abdalla, F., Aleksić, J., Avestruz, C., Balbinot, E., 2018. RAS 448, 2987–3000. http://oup.prod.sis.lan/mnras/article- Star-galaxy classification in the Dark Energy Survey Y1 data set. MNRAS pdf/448/4/2987/2814755/stv138.pdf. 481, 5451–5469. 1805.02427. Fosalba, P., Gaztaaga, E., Castander, F.J., Crocce, M., 2014. The MICE Grand Shvachko, K., Kuang, H., Radia, S., Chansler, R., 2010. The hadoop distributed Challenge light-cone simulation III. Galaxy lensing mocks from all-sky file system, in: 2010 IEEE 26th Symposium on Mass Storage Systems and lensing maps. MNRAS 447, 1319–1332. http://oup.prod.sis.lan/ Technologies (MSST), pp. 1–10. mnras/article-pdf/447/2/1319/8093572/stu2464.pdf. Szalay, A.S., Gray, J., Thakar, A.R., Kunszt, P.Z., Malik, T., Raddick, J., Górski, K.M., Hivon, E., Banday, A.J., Wand elt, B.D., Hansen, F.K., Reinecke, Stoughton, C., vandenBerg, J., 2002. The SDSS SkyServer: Public Access M., Bartelmann, M., 2005. HEALPix: A Framework for High-Resolution to the Sloan Digital Sky Server Data. arXiv e-prints cs/0202013. Discretization and Fast Analysis of Data Distributed on the Sphere. ApJ The Dark Energy Survey Collaboration, 2005. The Dark Energy Survey. arXiv 622, 759–771. astro-ph/0409513. e-prints astro-ph/0510346. Greenfield, P., Droettboom, M., Bray, E., 2015. Asdf: A new data format for Thomas, B., Jenness, T., Economou, F., Greenfield, P., Hirst, P., Berry, D., Bray, astronomy. Astronomy and Computing 12, 240–251. E., Gray, N., Muna, D., Turner, J., de Val-Borro, M., Santander-Vela, J., 18 Shupe, D., Good, J., Berriman, G., Kitaeff, S., Fay, J., Laurino, O., Alexov, A., Landry, W., Masters, J., Brazier, A., Schaaf, R., Edwards, K., Redman, R., Marsh, T., Streicher, O., Norris, P., Pascual, S., Davie, M., Droettboom, M., Robitaille, T., Campana, R., Hagen, A., Hartogh, P., Klaes, D., Craig, M., Homeier, D., 2015. Learning from fits: Limitations in use in modern astronomical research. Astronomy and Computing 12, 133 – 145. Thusoo, A., Sarma, J.S., Jain, N., Shao, Z., Chakka, P., Anthony, S., Liu, H., Wyckoff, P., Murthy, R., 2009. Hive: A warehousing solution over a map- reduce framework. Proc. VLDB Endow. 2, 1626–1629. Tonello, N., Tallada, P., Serrano, S., Carretero, J., Eriksen, M., Folger, M., Neissner, C., Sevilla-Noarbe, I., Castander, F., Delfino, M., Vicente, J.D., Fernandez, E., Garcia-Bellido, J., Gaztanaga, E., Padilla, C., Sanchez, E., Tortorelli, L., 2019. The pau survey: Operation and orchestration of multi- band survey data. Astronomy and Computing 27, 171 – 188. Vavilapalli, V.K., Murthy, A.C., Douglas, C., Agarwal, S., Konar, M., Evans, R., Graves, T., Lowe, J., Shah, H., Seth, S., Saha, B., Curino, C., O’Malley, O., Radia, S., Reed, B., Baldeschwieler, E., 2013. Apache hadoop yarn: Yet another resource negotiator, in: Proceedings of the 4th Annual Symposium on Cloud Computing, ACM, New York, NY, USA. pp. 5:1–5:16. Wells, D.C., Greisen, E.W., 1979. Fits-a flexible image transport system, in: Image Processing in Astronomy, p. 445. York, D.G., Adelman, J., Anderson, Jr., J.E., Anderson, S.F., Annis, J., Bah- call, N.A., Bakken, J.A., Barkhouser, R., Bastian, S., Berman, E., Boroski, W.N., Bracker, S., Briegel, C., Briggs, J.W., Brinkmann, J., Brunner, R., Burles, S., Carey, L., Carr, M.A., Castander, F.J., Chen, B., Colestock, P.L., Connolly, A.J., Crocker, J.H., Csabai, I., Czarapata, P.C., Davis, J.E., Doi, M., Dombeck, T., Eisenstein, D., Ellman, N., Elms, B.R., Evans, M.L., Fan, X., Federwitz, G.R., Fiscelli, L., Friedman, S., Frieman, J.A., Fukugita, M., Gillespie, B., Gunn, J.E., Gurbani, V.K., de Haas, E., Haldeman, M., Harris, F.H., Hayes, J., Heckman, T.M., Hennessy, G.S., Hindsley, R.B., Holm, S., Holmgren, D.J., Huang, C.h., Hull, C., Husby, D., Ichikawa, S.I., Ichikawa, T., Ivezić, Ž., Kent, S., Kim, R.S.J., Kinney, E., Klaene, M., Kleinman, A.N., Kleinman, S., Knapp, G.R., Korienek, J., Kron, R.G., Kunszt, P.Z., Lamb, D.Q., Lee, B., Leger, R.F., Limmongkol, S., Lindenmeyer, C., Long, D.C., Loomis, C., Loveday, J., Lucinio, R., Lupton, R.H., MacKinnon, B., Mannery, E.J., Mantsch, P.M., Margon, B., McGehee, P., McKay, T.A., Meiksin, A., Merelli, A., Monet, D.G., Munn, J.A., Narayanan, V.K., Nash, T., Neilsen, E., Neswold, R., Newberg, H.J., Nichol, R.C., Nicinski, T., Non- ino, M., Okada, N., Okamura, S., Ostriker, J.P., Owen, R., Pauls, A.G., Peo- ples, J., Peterson, R.L., Petravick, D., Pier, J.R., Pope, A., Pordes, R., Pros- apio, A., Rechenmacher, R., Quinn, T.R., Richards, G.T., Richmond, M.W., Rivetta, C.H., Rockosi, C.M., Ruthmansdorfer, K., Sandford, D., Schlegel, D.J., Schneider, D.P., Sekiguchi, M., Sergey, G., Shimasaku, K., Siegmund, W.A., Smee, S., Smith, J.A., Snedden, S., Stone, R., Stoughton, C., Strauss, M.A., Stubbs, C., SubbaRao, M., Szalay, A.S., Szapudi, I., Szokoly, G.P., Thakar, A.R., Tremonti, C., Tucker, D.L., Uomoto, A., Vanden Berk, D., Vo- geley, M.S., Waddell, P., Wang, S.i., Watanabe, M., Weinberg, D.H., Yanny, B., Yasuda, N., SDSS Collaboration, 2000. The Sloan Digital Sky Survey: Technical Summary. aj 120, 1579–1587. astro-ph/0006396. 19 Appendix A. Particularly useful applications This appendix describes in detail several representative use cases that make use of the custom catalog generation capabilities in CosmoHub. Each application includes the full SQL statement that was used, along with the time it took to complete. The timings measured in this section, unlike the results in section 4, were performed having exclusive use of the entire Hadoop platform. MICECAT1 clustering sample In this application, we want to generate a subset of MICECAT1 in order to compute the projected 2-point correlation function on it. Thus, we selected the right ascension, the declination and the comoving distance columns, and we filtered on a redshift shell (with z between 0.3 and 0.4) and also on a magnitude range (with absolute magnitude on the r band between -22 and -21). The creation of this custom catalog takes only 14 seconds and generates a CSV.BZ2 file of 4.99 MiB containing 492,210 rows. SELECT ‘ra‘, ‘dec‘, ‘d_c‘ FROM micecat_v1 WHERE ‘z‘ > 0.3 AND ‘z‘ < 0.4 AND ‘abs_mag_r‘ < -21 AND ‘abs_mag_r‘ > -22 DES Y1A1 BAO main sample Another interesting application was the generation of the BAO sample for DES. This sample, described in section 3 of Crocce et al. (2019), is a subset of DES Y1 data that, according to the article, represents “red galaxies with a good compromise of photo-z accuracy and number density, optimal for the BAO measurement”. The query below implements the criteria shown in Table 1 of the paper. The fast response times of CosmoHub were particularly useful to interactively refine the parameters of the sample, which is now available also as a predefined dataset within one of the DES private catalogs. An interactive query to visualize the number of objects as a function of the photometric redshift takes 31 seconds. Exporting the sample into a CSV.BZ2 file of 55.6 MiB containing 2.7 million objects takes 39 seconds. SELECT coadd_objects_id, ra, dec, mean_z_bpz_hiz, z_mc_bpz_hiz, t_b_hiz, odds_hiz FROM des_y1 WHERE (mag_auto_i > 17.5) AND (mag_auto_i < 22) AND (mag_auto_i < 19.0 + 3.0*mean_z_bpz_hiz) AND (ra < 15 or ra > 290 or dec < -35) AND (flags_badregion <= 3 and flags_gold = 0) AND (spread_model_i + (5.0/3.0)*spreaderr_model_i > 0.007) AND ((mag_auto_i - mag_auto_z) + 2.0*(mag_auto_r - mag_auto_i) > 1.7) AND ((mag_auto_g - mag_auto_r) BETWEEN -1. and 3.) AND ((mag_auto_r - mag_auto_i) BETWEEN -1. and 2.5) AND ((mag_auto_i - mag_auto_z) BETWEEN -1. and 2.) GAIA DR2 pseudo Healpix map The custom catalog feature can also be used in conjunction with the FITS format to create HEALPiX maps. For instance, in this application the following query was used to create a partial map with explicit indexing67 estimating the average of the Standard error of parallax (Angle[mas]) for each pixel. The pixel identifier is taken from the hpix 12 nest column. The generation of this custom catalog produces a FITS file of 156 million rows and 1.74 GiB in size in 65 seconds. SELECT ‘_hpix_12_nest‘, AVG(parallax_error) FROM gaia_dr2 GROUP BY ‘_hpix_12_nest‘ 67 See https://healpix.sourceforge.io/data/examples/healpix_fits_specs.pdf for more information about how HEALPiX data is stored as FITS. 20 Euclid True Universe FITS file CosmoHub stores and distributes a large amount of data for the Euclid Cosmological Simulations Working Group (CSWG) which is then used as input for different image simulator pipelines. This application uses a complex SQL statement to generate an individual input from the catalogs stored in CosmoHub. The resulting FITS file has the correct format, the proper field names and the correct units. This allows Euclid scientists to easily test their codes on a smaller scale, while at the same time enabling them to iterate and provide feedback much faster. In this particular example, the FITS file generated contains 1823344 objects, occupies 406.9 MiB and was produced in 22s. SELECT CAST(((gal.halo_id * 10000) + gal.galaxy_id) AS bigint) AS SOURCE_ID, CAST(gal.ra_gal AS float) AS RA, CAST(gal.dec_gal AS float) AS DEC, CAST(gal.ra_gal_mag AS float) AS RA_MAG, CAST(gal.dec_gal_mag AS float) AS DEC_MAG, CAST(gal.observed_redshift_gal AS float) AS Z_OBS, CAST(gal.abs_mag_r01_evolved AS float) AS TU_MAG_R01_SDSS_ABS, CAST(-2.5*log10(gal.sdss_r01) - 48.6 AS float) AS TU_MAG_R01_SDSS, CAST(gal.sed_cosmos AS float) AS SED_TEMPLATE, CAST(ROUND(gal.ext_curve_cosmos) AS smallint) AS EXT_LAW, CAST(gal.ebv_cosmos AS float) AS EBV, CAST(gal.logf_halpha_model3_ext AS float) AS HALPHA_LOGFLAM_EXT, CAST(gal.logf_hbeta_model3_ext AS float) AS HBETA_LOGFLAM_EXT, CAST(gal.logf_o2_model3_ext AS float) AS O2_LOGFLAM_EXT, CAST(gal.logf_o3_model3_ext AS float) AS O3_LOGFLAM_EXT, CAST(gal.logf_n2_model3_ext AS float) AS N2_LOGFLAM_EXT, CAST(gal.logf_s2_model3_ext AS float) AS S2_LOGFLAM_EXT, CAST(gal.bulge_fraction AS float) AS BULGE_FRACTION, CAST(gal.bulge_length AS float) AS BULGE_LENGTH, CAST(gal.disk_length AS float) AS DISK_LENGTH, CAST(gal.disk_axis_ratio AS float) AS DISK_AXIS_RATIO, CAST(gal.disk_angle AS float) AS DISK_ANGLE, CAST(gal.kappa AS float) AS KAPPA, CAST(gal.gamma1 AS float) AS GAMMA1, CAST(gal.gamma2 AS float) AS GAMMA2, CAST(gal.mw_extinction AS float) AS AV, CAST(gal.euclid_vis_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_VIS, CAST(gal.euclid_nisp_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_NISP, CAST(gal.euclid_nisp_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_NISP, CAST(gal.euclid_nisp_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_NISP, CAST(gal.blanco_decam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_DECAM, CAST(gal.blanco_decam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_DECAM, CAST(gal.blanco_decam_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_DECAM, CAST(gal.blanco_decam_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_DECAM, CAST(gal.cfht_megacam_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_MEGACAM, CAST(gal.cfht_megacam_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_MEGACAM, CAST(gal.jst_jpcam_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_JPCAM, CAST(gal.pan_starrs_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_PANSTARRS, CAST(gal.pan_starrs_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_PANSTARRS, CAST(gal.subaru_hsc_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_HSC, CAST(gal.gaia_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_GAIA, CAST(gal.gaia_bp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_BP_GAIA, CAST(gal.gaia_rp_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_RP_GAIA, CAST(gal.lsst_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_LSST, CAST(gal.lsst_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_LSST, CAST(gal.lsst_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_LSST, CAST(gal.lsst_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_LSST, CAST(gal.lsst_z_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Z_LSST, CAST(gal.lsst_y_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_Y_LSST, 21 CAST(gal.kids_u_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_U_KIDS, CAST(gal.kids_g_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_G_KIDS, CAST(gal.kids_r_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_R_KIDS, CAST(gal.kids_i_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_I_KIDS, CAST(gal.2mass_j_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_J_2MASS, CAST(gal.2mass_h_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_H_2MASS, CAST(gal.2mass_ks_el_model3_odonnell_ext*1.e23 AS float) AS TU_FNU_KS_2MASS, CAST(SHIFTRIGHT(gal.hpix_29_nest, (29-5)*2) AS bigint) AS hpix_5_nest FROM cosmohub.flagship_mock_sc456 AS gal WHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 7155 22