IPP to PSPS interface: ippToPsps

ippToPsps is the interface between IPP and PSPS. At the highest level, its job is to create FITS files, generated from a multitude of IPP data-sources, and then publish them to a datastore in the form of batches. On the PSPS side, the DXLayer polls the datastore, collects batches when they become available, then converts the contents to csv files before sending them on to SQL Server loader software, which merges them into the relevant PSPS database.

The binary tables in the FITS files generated by ippToPsps match the PSPS database schema perfectly, the consequence being that any alterations to the PSPS database schema will only affect ippToPsps code, and not the DXLayer. A certain amount of data validation is performed by ippToPsps before publication, with more validation occurring at the loading and merge stages on the PSPS side.

Simultaneously to loading data, ippToPsps polls PSPS to inquire after the status of the batches it has loaded. Batches that failed can then be reloaded. The status of every batch is maintained in a MySQL database local to ippToPsps.

Batch types

The outputs of ippToPsps are referred to as 'batches', and are detailed below.

Batch name	PSPS name	Description	IPP Source
Initialization	IN	metadata relating to other batches, eg filter ID, survey ID etc	generated from XML config
Detections	P2	single exposure detections	generated from one smf file per exposure plus associated DVO database
Stack	ST	stack image detections	one FITS file generated per IPP cmf, which contains data for one filter on one skycell
Difference	?	difference image detections	generated from one cmf file per skycell per exposure

Architecture and design

Outline

Essentially, the job of ippToPsps is to take a collection of IPP tables and convert them to tables suitable for ingestion to PSPS. Some mappings between IPP and PSPS are direct (eg IPP exposure ID = PSPS frame ID), while many require a format conversion, or can only be derived from multiple IPP fields; some require mining info from other IPP sources, such as DVO databases or the IPP MySQL database, gpc1.

Languages and tools used

The tools chosen for ippToPsps are those considered to be the most effective to tackle the task at hand. As a result, ippToPsps is not consistant with the rest of the IPP code-base (which is, predominately, Perl and C), but since its role is essentially outside the IPP, this, to me, is not an issue.

MySQL

Because we are dealing with table data that requires some intensive manipulation, it follows that a relational database be used. Relational databases are highly optimized to provide extremely fast query times, especially when indexing in incorporated. Thus we gain speed over the more obvious route of read-a-table-from-FITS-into-an-array-then-loop-through-each-value etc.

An added bonus is that, by using keys that enforce uniqueness in a given column (or columns), we protect ourselves against the risk of duplicates making into PSPS.

ippToPsps uses two MySQL databases, a scratch database, used to import tables and manipulate then before discarding them, and the ippToPsps database, which keeps track of which batches have been processed, published to PSPS etc.

Jython and STILTS

ippToPsps is written in ​Jython, this is in part to take full advantage of the ​STILTS package, which enables very fast and efficient processing of astronomical catalog data tables. Since it supports FITS, VOTable, and SQL, it is a perfect fit for this project. It is also software maintained elsewhere, reducing this burden on us.

Jython is simply a Java implementation of Python, a modern, high-level and object-oriented language enabling ippToPsps to be written in a minimal number of lines of code, helping it be both more readable and maintainable.

High-level design

Each batch type produced by ippToPsps (detection, stack, init etc) has its own class, all of which inherit from the Batch base-class, which handles features common to all batches, such as creating and opening output FITS files, connecting to the GPC1 database, connecting with the DVO database etc. This keeps duplicated code at a minimum. The Batch class is an abstract class, i.e. it should not, and cannot, be instantiated.

ippToPsps works like this:

• reads all relevant FITS tables from a given smf or cmf into temporary tables in a 'scratch' MySQL database
• creates empty MySQL tables for PSPS output (also in the 'scratch' database). These tables match the shape of the final PSPS database tables exactly.
• copies all relevant columns from the temporary IPP tables into the PSPS tables, discarding duplicates where necessary
• accesses the DVO database and creates temporary MySQL tables containing all the detections for this smf or cmf
• updates the PSPS tables with the IDs from the DVO MySQL tables
• replaces any NULL values with -999, as required by the PSPS loader
• exports the PSPS tables to a FITS file
• publishes FITS file, complete with a batch 'manifest' file, to the datastore

The reading and export of FITS tables is done using STILTS. For import, we can specify which columns we wish to import from the IPP smf and cmf files (we don't need everything).

Configuration

Due to the potential for changes in both input and output for ippToPsps, rather than hard-coding table descriptions, the code is heavily configurable. As such, the tables descriptions are stored as ​VOTable files (for ​example), which are a standard of the ​IVOA. Because VOTables are an XML format, they both human and machine readable, expandable and self-describing. These VOTables are generated directly from the PSPS schema using a ​script, so that any changes to the schema can be easily passed-along to ippToPsps.

Additionally, a more general configuration file is used to detail the names of databases, location of output files, datastore settings etc. The path to one of these configuration files is the sole argument to running the code (see below). An example can be seen ​here

dvoToMySQL

For the special case where we have a 'locked' DVO database, i.e. one that is complete and will not grow any further, we can improve ippToPsps processing time by first generating MySQL tables that include all detections from DVO rather than just the detections-per-exposure (or stack) as above. For this we can use the dvoToMySQL tool, found [here]

It may take a matter of days to convert a relatively small DVO database to MySQL, however, querying the MySQL database is hugely faster than accessing DVO directly, especially for regions of sky with a high density of detections, such as the medium deep fields. (This was seen when loading MD4 prior to the Boston meeting in May 2011. DVO access per exposure was 40 minutes, whereas, once imported to MySQL, query time was roughly 30 seconds.)

Image ID confusion

We access DVO via a combination of 'source ID' and 'image ID'. Both numbers come from the smf file. However, IMAGE_ID in the smf does not correspond to IMAGE_ID in DVO, instead, it maps to EXTERN_ID in the Images.dat file at the top-level of a given DVO database. The IMAGE_ID column of the same table maps instead to IMAGE_ID in the various 'cpm' (measurements) files contained within the subdirectories of the same DVO database.

Using the software

ippToPsps is configured for use entirely by a config file (see above). So, running the software is simply a case of editing a config and passing it as the sole argument to the program, like this:

cd trunk/ippToPsps/jython
./run.sh ipptopsps.py someConfig.xml

The ./run.sh prefix above is necessary to invoke the correct Java virtual machine, while including the relevant jar files in the CLASSPATH (all included in the trunk/ippToPsps/Jars subdir)

Deletion policy

When loading, two copies of the data exist: the batches on the datastore and the original files on disk. The deletion policy at present is:

• when a batch has been loaded to the ODM and has a status of 'merge worthy', then the copy on the datastore is removed
• when a batch has been successfully merged into the PSPS database, the copy on local disk is deleted

The logic for this is that errors may occur during the merge phase and it is useful to have local copies of offending batches for debugging purposes.

Unit testing

Unit tests exist for all batch types in the unitTests folder. These tests confirm that data integrity is maintained after code changes have been made. All batch tests can be run like this

cd unitTests
./runAllTests.sh

Or, individual test like this, for detections:

cd unitTests/detectionTest
./run.sh

All test create batches in a 'test' mode, meaning, a current date will not be used and, in the case of detections, only chip 33 (instead of all 60) will be processed. The resultant FITS files are then compared (using diff) with FITS files known to be correct. The user is informed if they are identical or if they differ.

Recovery system design

Currently, the IPP to PSPS interface is a 'one-way' system. Batches are created by ippToPsps and posted on an IPP instance of the datastore. These batches are collected by the DXLayer on the PSPS side and sent on to the ODM. The IPP urgently requires some feedback from PSPS to determine which batches have succeeded and which have failed (and why they failed). With this information, data can be either deleted or regenerated accordingly. This is important simply because, with such large data volumes, we cannot afford the high levels of redundancy currently in place. At present, for a given batch, the following copies exist within the pipeline:

• a copy exists on the IPP cluster after generation by ippToPsps program
• a copy exists on the IPP datastore after publication by ippToPsps
• the DXLayer retains a copy after it has sent the csv version to the ODM
• the DXLayer also keeps a copy of these (larger) csv files

We therefore need to quickly implement the basic framework of a feedback loop so that the IPP can quickly learn if a given batch has been successfully merged into the PSPS database or not. This will enable it to safely delete the data files and remove the copy from the datastore. This will also form the basis for a more comprehensive recovery system, to be developed at a future date.

Previous design

                                              .............................
                                             .   ___________               .
                                             .  |           |              .
         ---------------------------------------| datastore |              .
        |                                    .  |___________|              .
 .......|...........................         .       /|\                   .
.  ____\|/_____       ___________   .        .    ____|____        _____   .
. |           |      |           |  .        .   |         |      |     |  .
. | ippToPsps |----->| datastore |-------------->| DXLayer |<---->| ODM |  .
. |___________|      |___________|  .        .   |_________|      |_____|  .
.                                   .        .                             .
 ...................................         ..............................   
                
               IPP                                       PSPS                         
                                         

Previously, Conrad and I had discussed a design whereby a second datastore instance would be utilized, this time on the PSPS cluster. The DXLayer would act as the 'middle-man', polling the ODM for updates on loading progress, then posting the results on the PSPS datastore for the IPP to consume. Polling this, ippToPsps could acquire a list of batches it knows are safe to be discarded. Simultaneously, the DXLayer could delete its copies of the same redundant data.

The update placed on the PSPS datastore could take the form of an XML file. At first this would simply detail those files it is safe to delete, but could evolve into a more complex recovery report, i.e. which batches failed, and what is required to be done by the IPP.

New design

                                                                                                      
         ------------------------------------------------------------        
        |                                                            |     
........|...........................          .......................|.....                 
.  ____\|/_____       ___________   .        .    _________        __|__   .
. |           |      |           |  .        .   |         |      |     |  .
. | ippToPsps |----->| datastore |-------------->| DXLayer |----->| ODM |  .
. |___________|      |___________|  .        .   |_________|      |_____|  .
.                                   .        .                             .
 ...................................         ..............................   
                
               IPP                                       PSPS                         
                                         

Instead of creating a new datastore instance within PSPS and using the DXLayer as communication layer between the ODM and the IPP, we propose that the DXLayer forms no part of the feedback system. It should be simplified such that it only facilitates loading, i.e. polling the IPP datastore for new data, converting it to csv files then sending these on to the ODM. Instead, to complete the circle, the ippToPsps code will poll the ODM directly, bypassing the DXLayer altogether. This also forms the basis of a full recovery system as, at a later date, ippToPsps can be coded to respond intelligently to the myriad of errors that may occur within the ODM. The DXLayer need know nothing of the how or why a certain batch is being submitted by the IPP, it should just grab it, convert it and pass it along to the ODM.

This design would therefore mean simplifying a major PSPS component, the DXLayer, but rather than waste the code already written, it would be taken and used within ippToPsps (for example, the ODM polling scripts). We would simply be shifting responsibility over from PSPS to IPP. Over parts could be dropped completely. For example, since ippToPsps will (soon) keep a record of all the jobs and corresponding exposure IDs in the IPP database, it is unnecessary for this information to be duplicated by the DXLayer, which currently has its own local database for this information.

The question remains of what should be done with the copies of the data currently retained by the DXLayer? The options are that it can either be deleted automatically after a defined amount of time, or the IPP can send a list of batches it is safe to delete through the datastore, or perhaps the DXLayer should not retain files at all. Since it can quickly and easily acquire data from the IPP datastore anyway, it is probably unnecessary for it to hold any copies.

Advantages over previous design

• no need for second datastore (not a big overhead, but it would require additional systems administration in an already complicated system)
• no need to define new XML standard that incorporates the whole array of recovery options
• no need for the DXLayer to poll the ODM
• no need for the DXLayer to have a database to log the batches (already done on the IPP side)
• no need for the DXLayer to keep data at all?

links

​Datastore test area for PSPS on Maui
​Datastore test area for PSPS at JHU
current loading summary