= IPP to PSPS interface: {{{ippToPsps}}} = [[PageOutline]] {{{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}}}. The various temporary copies of the batches are deleted once the ODM reports that they have been successfully loaded or merged. Some notes prepared for the PSPS operational readiness review can be found [wiki:ippToPspsOrr here]. = Batch types = The outputs of {{{ippToPsps}}} are referred to as 'batches', and are detailed below. ||'''Batch name''' || '''PSPS name''' || '''Description''' || '''IPP Source''' || || [wiki:ippToPsps_Initialization Initialization] || IN || metadata relating to other batches, eg filter ID, survey ID etc || generated from XML config || || [wiki:ippToPsps_Detections Detections] || P2 || single exposure detections || generated from one {{{smf}}} file per exposure plus associated DVO database || || [wiki:ippToPsps_Stack Stack] || ST || stack image detections || one FITS file generated per IPP {{{cmf}}}, which contains data for one filter on one skycell || || [wiki:ippToPsps_Object Object] || OB || tables detailing all objects || All from DVO FITS files || [[BR]] = Architecture and design = == Outline == 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. We also have to write far fewer lines of code. 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 it 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 [http://www.jython.org/ Jython], this is in part to take full advantage of the [http://www.star.bris.ac.uk/~mbt/stilts/ STILTS] package, which enables 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 the burden on us. Jython is simply a Java implementation of Python, a modern, high-level, object-oriented language enabling {{{ippToPsps}}} to be written in minimal 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 [http://www.ivoa.net/Documents/VOTable/20040811/REC-VOTable-1.1-20040811.html VOTable] files (for [http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/browser/trunk/ippToPsps/config/P2/tables.vot example]), which are a standard of the [http://www.ivoa.net/ IVOA]. Because VOTables are an XML format, they are both human and machine readable, expandable and self-describing. These VOTables are generated directly from the PSPS schema using a [http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/browser/trunk/ippToPsps/perl/pspsSchema2xml.pl script], so that any changes to the schema can be easily passed-along to {{{ippToPsps}}}. Another configuration is used to describe a particular 'loading campaign', complete with the names of databases, location of output files, datastore settings etc. This configuration is stored in the {{{ippToPsps}}} database. Details can be found [wiki:ippToPsps_Database#Theconfigtable here]. All {{{ippToPsps}}} programs require the name of a config at startup as the first argument. Changes to the config can be made at any time, and programs will update accordingly. Most fields are self-explanatory, others that are less obvious will be explained below in the ''Using the software'' section. == The {{{ippToPsps}}} database == A full description of the {{{ippToPsps}}} database is detailed [wiki:ippToPsps_Database 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 [http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/browser/trunk/ippToPsps/jython/dvoToMySQL.py 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. = {{{ippToPsps}}} programs = {{{ippToPsps}}} consists of numerous programs that work together to queue, process and monitor data as it passes from the IPP to PSPS. All programs can be run like this: {{{ ./run.sh prog.py someConfigName }}} Programs that require any extra arguments will prompt the user. All {{{ippToPsps}}} programs register with the {{{ippToPsps}}} database upon start-up, adding a row in the [wiki:ippToPsps_Database#Theclientstable {{{clients}}} table]. While running, all programs routinely check the {{{ippToPsps}}} database to check for changes in the config, or to see if they should be paused or stopped. All programs catch Ctrl-C for clean exiting, and all programs write a log to stdout as well as to file, the location of which is set in the [browser:trunk/ippToPsps/config/settings.xml settings file] == queue.py == Before any {{{loader.py}}} clients can do anything, you must queue something up. To do this run: {{{ ./run.sh queue.py someConfig }}} Data to be processed is divided up in a grid on the sky, using the RA/Dec and box-size settings in the config. The {{{plotter.py}}} program can generate a density plot of data still to be processed through {{{ippToPsps}}}, an example of which is shown below. [[Image(new3pi_P2_2012_0308_135938.png)]] The master list of items to be processed comes from the gpc1 {{{addRun}}} table, which lists all items currently merged into the DVO database we are using. From this list, {{{queue.py}}} subtracts items that have already been successfully loaded to the datastore already, as well as items that have routinely failed to process, and queues up the remainder. == loader.py == {{{loader.py}}} programs actually process the data and upload it to the datastore for PSPS. They look to the queue tables in the {{{ippToPsps}}} database for a list of items to process. They first attempt to secure a a 'stripe' of RA (from the [wiki:ippToPsps_Database#Thestripetable stripe table]) that is not being processed by any other {{{loader.py}}} clients. If all available stripes have already been reserved, then it chooses the stripe with the largest number of pending items. With a stripe chosen, {{{loader.py}}} gathers a list of box_ids (each stripe is sub-divided into boxes). It works its way through each box, processing all pending items. Because it is possible, and usual, to run multiple versions of {{{loader.py}}} (in order to speed up loading time), the method to begin processing a new batch is part of a critical section. This simply means that the 'batch' table in the {{{ippToPsps}}} database is locked by a client looking for a new item to process, then released afterwards, ensuring that two separate {{{loader.py}}} instances do not attempt to process the same item. == metrics.py == {{{metrics.py}}} is a program that collects useful statistics for a particular config and reports then to the screen, while also inserting data into the [wiki:Czartool czartool] database. The ippMonitor czartool page for ippToPsps displays much of this information, complete with time series plots showing the progress of processing. {{{metrics.py}}} takes a second argument which is a time (in hours). If this is provided the program will sleep then run again after that time. An example output from {{{metrics.py}}} is shown below. {{{ ./run.sh metrics.py someConfig }}} And will produce a response similar to this: {{{ ippToPsps loading summary Time now 2011-09-29 11:54:33 Loading epoch 2011-08-16 DVO label LAP.ThreePi.20110809 +----+------------------+---------------+-------------------+------------------+----------------+ |Type| batches per hour | last 24 hours | per day this week | total detections | last published | +----+------------------+---------------+-------------------+------------------+----------------+ | P2 | 0.0 | 283 | 329.4 | 1855730051 | 4.6 hours ago | | ST | 0.0 | 12936 | 4539.0 | 185807608 | 3.7 hours ago | +----+------------------+---------------+-------------------+------------------+----------------+ +----+-------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+ |Type| DVO | processed|loaded_to_datastore| loaded_to_ODM| merge_worthy| merged| deleted_datastore| deleted_dxlayer| deleted_local| | | | Pend Succ Fail | Pend Succ Fail | Pend Succ Fail | Pend Succ Fail | Pend Succ Fail | Pend Succ Fail | Pend Succ Fail | Pend Succ Fail | +----+-------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+ | P2 | 8140 | 8101 39 | 2 8099 | 2041 6049 9 | 6049 | 313 5736 | 5736 | 5736 | | | ST | 32309 | 31684 625 | 5 31679 |24617 7062 | 7062 | 7062 | | | | +----+-------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+ }}} == cleanup.py == == pollodm.py == == plotter.py == All programs accept at least one argument, which is the nam of a config currently in the {{{ippToPsps}}} database. = Using the software = == Loading == Loading data from IPP to PSPS means running an instance of the {{{load.py}}} program. It is possible, and usually necessary, to run multiple versions of {{{load.py}}}. The code has been designed so that the user can start as many clients as he or she likes, and they will run concurrently without causing any problems. Note, however, that clients on machines where the DVO database is not on a local disk will run more slowly due to the increased burden of accessing DVO over the network. Any number of clients can run concurrently on any number of hosts, so long as they have a local 'scratch' database set up properly, as detailed below. === Setting up a loading machine === Although this can be changed in the config, {{{ippToPsps}}} uses a 'scratch' MySQL database on {{{localhost}}} while processing. So, to to any loading, a local MySQL server should be up-and-running, with a database created as the {{{ipp}}} user for use by {{{ippToPsps}}}. The default name for this is {{{ipptopsps_scratch}}}. Because {{{ippToPsps}}} uses this local database as a scratch database, there are large number of inserts and deletions, meaning large log files. For this reason, it is important to configure MySQL to store its data on a local partition, and not {{{/var}}} as is the default. For example, on {{{ipp005}}}: {{{ /var/lib/mysql }}} was moved to: {{{ /export/ipp005.0/mysql }}} In addition, it is prudent to decrease the default MySQL log-retention period from 90 days to 1 day. This is done is in {{{ /etc/mysql/my.cnf }}} on the line {{{ expire_logs_days = 90 }}} You will need (if it is not done already) to create an {{{ipp}}} user. Connect to the local MySQL server as root then to the following. {{{ GRANT ALL PRIVILEGES ON *.* TO 'ipp'@'localhost' IDENTIFIED BY 'ipp' WITH GRANT OPTION; }}} Now disconnect then reconnect as {{{ipp}}} and create the scratch database. {{{ CREATE DATABASE ipptopsps_scratch; }}} Finally, you need to create the initialization tables. {{{ ./run.sh loader.py init }}} At the time of writing, scratch databases are set up on the following IPP hosts: {{{ ipp005 ipp006 ipp007 ipp033 ipp034 ipp045 }}} {{{ipp005}}} and {{{ipp045}}} in fact have ''three'' scratch databases each, so three clients can potentially be run on these machines. This is particularly useful on {{{ipp005}}} as this is where the DVO databases are currently stored, so clients running on the same machine have a much higher throughput. ({{{ipp045}}} used to host a backup 3pi DVO database.) === Running === Loading data means running the {{{load.py}}} program while passing it the relevant configuration file (see above). So, running the software is simply a case of editing a config and passing it as an argument to the program, like this: {{{ cd trunk/ippToPsps/jython ./run.sh loader.py someConfig }}} 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) === Creating and publishing an 'IN' batch === The same program is used to create IN batches, but requires one extra argument, like this: {{{ ./run.sh load.py init }}} This will produce an IN batch, publish it, then stop (the IN-batch MySQL tables will also be updateded in the local scratch database). === Epochs === Each config requires an entry in the {{{epoch}}} column. An ''epoch'' in the context of {{{ippToPsps}}} is the date that we count as ''the beginning''. If we loaded IPP data to PSPS once and only once, this would not be necessary: we would queue up all available exposures and stacks and simply load them. However, the early stages of the project have required multiple re-loads of data while the IPP perfects the science, necessitating that the same exposures and stacks are loaded more than once. Because {{{queue.py}}} queues available items by taking into account those that have already been published to PSPS, we have to give it an epoch date from which to accurately determine those exposure that have been loaded or not. Generally speaking, the epoch is reset every time we delete the main PSPS database. === Test mode === One field in the config, under the ''options'' section, is ''testMode''. This is a boolean, i.e. can be either 1 or 0. When switched on, each batch type will be run in it's own test mode. For detections, this means: * it will only process the first exposure that is queued * it will only process OTA 33 from this exposure * it will ignore certain missing values, which would otherwise fail the batch == Running {{{dvoToMySQL}}} == Like all other {{{ippToPsps}}} components, {{{dvoToMySQL}}} takes one argument which is a name of a config in the database. For example: {{{ ./run.sh ./dvoToMySQL.py configs/lap.xml }}} This will begin to import the DVO database pointed to in the config into the scratch MySQL database also detailed in the config. The program can be started and stopped, as it will 'remember' which DVO files it has read and imported. There is a second 'hidden' argument to {{{dvoToMySQL}}}, namely {{{reset}}}. This is dangerous, as this option will reset all the tables necessary to import a DVO database. If you have spent days importing a DVO database, then run {{{dvoToMySQL}}} using this option, everything wilol be lost. The user is prompted to ask whether they really want to proceed. == Deletion policy == When loading, three copies of the data exist: the original files on disk, the batches on the datastore and a third copy on the PSPS loading machine. The deletion policy at present is: * when a batch has been loaded to the ODM and has a status of 'merge worthy' '''or''' 'failed', then the copy on the datastore and the DXLayer's copy can be removed * when a batch has been successfully merged into the PSPS database, then final copy on local disk may be 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. This standard behavior can be changed by setting the appropriate values in the ''deletion'' section of the config. To actually perform the clean, run the {{{cleanup.py}}} program like this: {{{ ./run.sh cleanup.py configs/lap20110809.xml }}} = 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 = [http://datastore.ipp.ifa.hawaii.edu/PSPS_test Datastore test area for PSPS on Maui][[BR]] [http://datastore.ipp.ifa.hawaii.edu/PSPS_JHU Datastore test area for PSPS at JHU][[BR]]