3.2.1   Catalog Query¶

(Based on the asynchronous flavor of the TAP interface.)

1. Caller submits an ADQL Query to the TAP service endpoint via HTTPS POST and receives a query ID to check for results.
2. Database service parses the query to determine the back end for the query, based on the tables selected, and translates the ADQL to the back end’s native query language.
3. Request is created and put on the work queue.
4. UWS worker dispatches the query and gathers results.
5. Worker massages data into the requested format and marks the request complete.
6. Caller uses the URL and ID to be redirected to the results file.

3.2.2   Catalog Metadata Query¶

Same as a normal catalog query, but the query uses the TAP_SCHEMA tables stored in the Oracle database.

3.2.3   Image Metadata Query¶

Same as a normal catalog query, but the query uses standard tables that contain image metadata stored in the Oracle database. An image metadata query can be a normal ADQL TAP query against the native LSST metadata, or against LSST’s CAOM2 data model tables. It can also be an ObsTAP query (i.e., ADQL against the basic table defined in the ObsCore standard), or it can be done via the simplified SIA protocol. In each case, the result is a VOTable with image metadata and corresponding access URLs.

3.2.4   Image Retrieval¶

1. Caller uses an Image Metadata Query to determine images they want to retrieve.
2. Caller makes another HTTPS GET to the URLs returned from the Image Metadata Query.
3. Image Service creates a ID, and puts the request on the work queue.
4. Image Service Worker picks up the request and uses the Butler to see if that file exists.
5. If the file does not exist, but is recreatable as a virtual data product from underlying data, the Image Service recreates that file by using the workflow engine to execute the appropriate Science Pipelines code.
6. Once the file exists, the file is put in the object store and the worker marks the request as complete.
7. Caller is redirected to the object store URL.

3.2.5   Image Cutouts¶

1. Caller uses an Image Metadata Query to determine datasets and particular images they might want cutouts of.
2. Caller makes a SODA request to the Image Service with parameters that determine positions and shapes of cutouts.
3. Image Service creates an ID and puts the request on the work queue.
4. Image Service Worker picks up the work and uses the Butler to gather and create image files it needs to process the request.
5. Worker uses the appropriate Science Pipelines code to create cutouts on those images.
6. Worker uploads result to object store and marks request as complete.
7. Caller uses the ID to check for results, and is redirected to the object store URL of the result.

4.4.1   QServ¶

QServ is our custom scalable database for distributed hosting of data release catalogs. QServ is based on top of MariaDB with customizations to support efficient queries against spatially organized data in spherical geometry. QServ has some special performance characteristics, but from the perspective of the DAX services, it means we mostly need to be compliant with its SQL variant and its geometry functions, and be able to transform ADQL into QServ SQL. QServ also has special functionality to do full table scans, allowing multiple queries to be run simultaneously (“shared scans”), support for fault-tolerance through maintaining redundant copies of the distributed data, and some special endpoints to allow for queries to run async with users able to retrieve the results later on.

Tables in QServ can either be “spatially sharded”, with their content distributed across its many database workers according to a tiling of the two-dimensional sky, or, for smaller tables, replicated across all workers. Tables of both types can be combined in JOINs, but spatially sharded tables can only be joined “locally” within shards, supporting the query of relationships between spatially nearby catalog entries.

4.4.2   No JOINs Across QServ and Oracle¶

While TAP will present the tables from QServ and Oracle as one large unified table space, we can’t allow for people to do SQL JOINS between them.

If we wanted to support this, it would be very complicated, so for now this is out of scope. If you need to do some joins, query each table with a different query and then JOIN it yourself by iterating through the data on the application side.

JOINs should be supported on all Oracle or all QServ tables though. Just JOINs between them will be disallowed.

In order to partially work around this restriction, certain tables, including for example key image and visit metadata tables, are expected to be made available in both database systems. This facilitates their use in JOINs in both contexts.

4.4.3   Authentication and Authorization¶

The primary released LSST data will not be world-public at the time of release (see LPM-261 for more details). In addition, scientists may have their own private datasets uploaded as well to do JOINS or other algorithmic analysis against. We need to be able to authorize each user to use the LSST DAC resources as well as protect their results and query history from someone else trying to scoop their research. Many IVOA standards come from the era of public astronomy data, so although there is some support for authentication and authorization in the standards (e.g., in the “Single-Sign-On Profile: Authentication Mechanisms” document), there may be some excitement here trying to add AAA to everything.

Since we are using UNIX groups and other very POSIX level permission schemes, we need to figure out how to respect these things in our Webservices, which aren’t always impersonating the user. For example, to get a result file, it’d be much easier to check the permissions rather than try to su to that user, and see if they still have access (which brings in things like ACLs, and UNIX group mechanics). Depending on the level of auth required, we might be able to restrict this to the creator of the query, rather than their group. Either way, this will have to be determined.

4.4.4   History Database¶

We want a history database of queries that can be looked through. The UWS spec defines that there is a way to get a list of jobs, both pending and finished, so that is one way of accomplishing this goal. Depending on how long we want to persist this data for, we might want to back up the queries, and index them in some other interesting way, probably through some other kind of ancillary service.

Query text should be protected by auth to only allow a user to see their own queries.

Retention of a history entry does not imply retention of the results of a query. We expect to retain query results for a relatively short time, both to facilitate users in obtaining results from long-running queries without setting an overly narrow window for them to respond to a notification of a completed query, and to enable workflows that begin in one LSP Aspect and continue in another. However, we expect to retain query texts and other records of the execution of a query for much longer, possibly unlimited, periods. The UWS standard on which the TAP protocol rests makes provision for this situation by defining an ARCHIVED state for queries post-execution in which the query results are no longer available but other information, including the query parameters, is retained.

The existence of the history database serves a number of purposes in the LSST Science Platform:

• It is useful in its own right for users to be able to understand the evolution of their data accesses.
• It provides one of the two means of transfer of a query workflow from one LSP Aspect to another; users can perform actions such as “show me the results of my last query”, or review recent queries, in order to find a query started in one Aspect in another Aspect.
• It provides support for the repetition and reproducibility of queries. Of particular interest for the continuously evolving Prompt data products, the information in the query history database enables a user to re-run a query either so as to reproduce its results as if at the time of the original query, or to re-run it afresh including newly released data.

4.4.5   Large Result Sets¶

Since LSST queries may take a long time to run, and have large results sets, we need to be able to cache large results sets (up to 5 GB of results per query) for a reasonable period of time so they can be retrieved. This may be on the other of a few days or a week, since some of the queries may be run overnight or over the weekend.

These results must also be protected so that only the user executing the query can retrieve the results. After the results are retrieved, that user can obviously do what they will with the results (such as share them). While there are data rights implications here, once the data is out of our control, it’s out of our control.

4.5.1   TAP-Compliant Interface¶

There are many ways to write a webservice these days, including many frameworks. We know what URIs we want to serve, /sync and /async, and that we want to serve results in XML. We need to really reference the TAP 1.1 spec for this part, implementing what we need to, such as parameters (LANG, QUERY, MAXREC) as well as wrapping the results in a VOTable format.

4.5.2   History Database¶

There are many data stores we could use for a history database. Many might even be tied to the execution of async jobs. For example, the distributed task framework celery uses RabbitMQ, Redis, MongoDB, to store results and execution status. This isn’t just used to query the history but to drive execution. These databases can also be queried directly by users, or we can add additional URIs to look through the history.

The UWS spec also mandates a way to list jobs, and get their results. This is fairly analogous to the history database functionality we want, as it lists the queries, their IDs, execution status, and result location. It may be useful to structure a more general query-history-query service in a way that returns the same basic data structures as the bare-bones UWS job list.

4.5.3   Determine the Backend¶

Many specs use the TAP and VOTable standards as a way of transmitting complex data. For example, the TAP_SCHEMA tables store the semantic metadata, and could be on a different backend than the catalog itself, which is hosted by QServ. Some user generated (Level 3) data might also be present in another database, such as Oracle or Postgres. There are also special tables for ObsTAP to look at image metadata.

The tricky part here is that if one database isn’t hosting all the tables, we need to inspect the query to determine what tables are being accessed, and then route the query to the appropriate backend. Different backends might also have different load characteristics, such as the number of running queries.

4.5.4   Query Rewriting¶

QServ doesn’t speak ADQL. Neither does Oracle. We need to take the ADQL query, inspect it, and rewrite it to work on the individual backends.

This may be to work around various quirks of different SQL variants and implementations (such as how keywords work, or the way of limiting results, or datatypes).

There are also some extensions to do very astronomical things, such as cone and other spatial searches, as well as dealing with different coordinate systems. Different back ends may have very different ways to implement these spherical geometry constructs.

4.5.5   Query Dispatch¶

Once we have the final query and we know where it’s going, we are ready to send the rewritten query to the backend and start getting the results. Since these results may be very large (GBs) or very small (0 or 1 rows), we need to be able to support both cases in a performant way.

For sync queries, the caller simply waits on the HTTP connection until the results are available. For async queries, since the caller will make another request, we need to ensure that these requests will always find the results, no matter how many TAP service copies we have. This means we can’t really store results locally on the TAP service disk (also this has the possibility of filling up the disk). It is better to have a central disk or shared place, so that results can be written there, and then picked up by anyone handling getting the results. This also helps with keeping results through upgrades and transient failures.

It’s also a good idea to separate out your front ends (things taking HTTP requests) from your back end workers (which dispatch to the database). This allows for a more even distribution of load across the workers, and keeping the load on the backends (which don’t scale as easily) in check.

As we gather these results, we need to put them also in the right format, which is VOTable. This may involve some coercing of data types to VOTable data types, rather than the original backend. Once the result is written and in the correct format, we can record that the query is finished and the results are available. The “Science Data Model” and its record of the intended data types of the catalog data may be of use in determining the correct VOTable data types to be used, rather than simply inferring them from the underlying database data types.

4.5.6   Centralized Result Store¶

After the user has completed their query, they will want their results, which may be large. They may be downloaded more than once, so we likely want to keep the results sets around for at least a few days, to prevent needing to rerun the same query on the database.

Because of the diversity of queries and their results sizes, and not being able to know the size of the results from the query, we need to be careful about local resources. If the results were stored on the TAP service nodes, we could easily fill up the local disk, which may be as few as 20 results for 100 GB. The fragmentation of splitting the load across multiple TAP service nodes might also be bad, since the sizes of the results might be uneven, filling up some nodes and leaving others empty. We want to store all these in a central place, preferably with URL access, so we can serve the results file directly off disk.

By having one place store the results, we eliminate the problem of the client needing to contact multiple servers to find the results, or the results not existing by the time the user checks for the results.

This could easily be an S3 like object store, or an NFS volume with Apache or another web front end checking for auth on top. Given that it is simply serving up static files, this part should be relatively easy.

4.5.7   Performance, Load, and Failure Characteristics¶

The performance characteristics of the database server should be fairly straightforward, at least compared to what it is built on top of and completely depends on.

The overhead of processing a request, parsing the query, putting it in ADQL, and dispatching it to the server should be very quick compared to running the query. This time should be fairly constant no matter what the query is.

Running a query is completely dependent on the query (which we don’t control) and the database (which we depend on, but don’t control). Things like the load on the shared database resources from other users and other queries can’t really be predicted.

The DAX Webservices can be good stewards of these shared resources. By having a work queue with a consistent maximum number of queries in flight, we can provide an orderly way to access a limited resource, without overloading it. There is usually a sweet spot in terms of performance, where you are fully using your resources, but not thrashing, that we will hopefully discover and tune our system accordingly.

The overhead of processing the response is certainly higher than that of the request. Having to take an up to 5 GB file and transmute it from database rows into a VOTable or other format can be costly. The latency involved in such large transfers is also not to be ignored. Given that we know we have a 5 GB limit on query responses, we can ensure that our portion of the processing of the results will generally have a fixed upper bound.

Because the database service doesn’t have much internal state, and has no important data to lose, the failure characteristics are straightforward. We might fail the request, and have to retry it, or lose a result. Since we cannot keep all results for all time, it’s inevitable that some results will be unavailable after a period, and tools will simply rerun the query. Transitive failures can be retried if desired, but not required.

5.4.1   Images we are serving¶

The standards mentioned previously can be used to host any particular image data, from any instrument. For LSST, we have two types of images we’d like to serve through these endpoints and queries:

1. PVIs - Processed Visit Images
2. Multiple sets of coadds - Created by Coadding PVIs.

Each of these will have images per band, and covering the LSST footprint. There are also multiple different sets of Coadds using different addition methods and selections of raw data.

5.4.2   PVI Retention and Virtual Products¶

Due to cost and space constraints, the current plan does not involve storing all the PVIs on disk. There is only a 30 day moving window of availability for these images while they are processed and can be easily read off disk.

After this 30 day window, additional work would need to happen to be able to recreate the PVI file, which could then be served to the caller. This work would involve having to read off tape (or hopefully, a disk) the raw image components, then use the workflow system to tell it to create the PVIs. While most of this logic is out of scope of this document, the important point is that this may take minutes and possibly even hours before an image can be served.

This is also true of other processing intensive operations, such as looking at different sets of coadds that might not always be on disk.

Because of these reasons, doing anything with images synchronously is probably a bad idea.

5.4.3   Authentication and Authorization¶

Users will have to be authenticated, and authorized (with data rights) to query these services and retrieve image data. This security model may be simpler to that of the TAP service, because people will likely not be uploading their own images to be served by the SIA, SODA, or ObsTAP interfaces. This means that there is generally a consistent level of protection needed that does not vary per user - everyone has the same access to all the image data, as all the image data is covered by LSST data rights rules.

That being said, ObsTAP does support a field called data_rights, which allows us to say that our dataset is either public, secure, or proprietary (ObsCore B.4.4). This will likely be one flag per data release, which will either be proprietary, then public after it is released.

5.4.4   History Database¶

While it is not mentioned in the requirements, we might want to extend the idea of the history database to encompass queries to the image service, such as ObsTAP, SODA, and SIA queries. Because of the authorization model outlined above, the results are less likely to need to be secured between users, allowing for caching and result reuse to be higher and easier to accomplish in a secure manner.

Either way, we will want to audit the access logs to this service, and attempt to determine usage patterns, to improve performance.

5.4.5   Large Result Sets¶

Because of the large size of the LSST data, including the images, we will want to ensure that queries are limited to a reasonable number of results, to not put undue load onto the system.

Since we have to support async queries to SODA, and because those jobs may take a while to run, it makes sense to use the same centralized results backend to store the data and provide URLs to objects in that backend.

5.4.6   Image Metadata¶

There will be a visit table that contains all the visits, and metadata about PVIs. This would be ideal if it’s in the ObsCoreDM format so it can directly be queried against using ObsTAP. Even if it’s not exactly in the same format, we’ll need to provide some kind of ObsTAP-compliant view of that data to allow for queries, since the metadata model has to be in a specific format to follow the standard.

We will also need tables that contain the metadata about all the coadds, so they can also be discovered, even though it’s not a visit at all, and therefore doesn’t belong in the visit table. We might have to virtually stitch these two tables (one containing PVI metadata, and one containing coadd metadata) together somehow to allow a unified interface for querying through one table.

This metadata also needs to exist for things that aren’t currently on disk, because they are virtual products. The fact that they exist in this database lets us know that they can be created, and at one time, were created. When someone queries these products, we need to create them on demand.

5.5.1   Querying Metadata and Image Discovery¶

Some of the implementation here gets to be shared with the Database Service, as ObsTAP is making sure that certain tables exist in a certain format and can be queried from our TAP service.

First, we need to ensure that we have the proper metadata, and it is available via the standards-compliant queries. Then we use the same TAP service described as above, using its sync or async endpoints to retrieve a VOTable containing image metadata. This image metadata contains URLs that can be used to access these images.

For SIA compatibility, we can run this on top of the current ObsTAP implementation because for each SIA query it can be mapped into an ObsTAP query, and the response is of the same format (VOTable). SIA only supports sync though, so it should only be for short queries. Again, the sync part is only relating to the query, but the images might also not be available for some time, even if there is an access_link provided in the response. This may break SIA clients.

5.5.2   Retrieving Images¶

Now we know what images exist, the types and formats of those images, and we have URLs to query them. Now we can either download PVIs or coadds, or do server side processing such as cutouts to receive a processed image via SODA on those PVIs or coadds.

Both of these types of requests can be served by one service, and that server uses the Butler as its backend for retrieving images and doing simple processing such as cutouts.

If the URL presented is not a SODA request, we can say that this is a request asking directly for a full image (either PVI or Coadd). We use the URL to map this back to a way that the Butler can retrieve the image using its known mappings. Once we find the file on disk (or network disk), we serve it up directly to the user. If the file doesn’t exist, we can create it using the workflow engine, but the image might not be available for some time. For direct GETs, we might need to use HTTP control to tell it to try again later, and that the image isn’t ready yet. Most of the standards assume images are all accessible in short order if they exist in the metadata.

If the URL is a SODA request, then we get to work. First, we process the query and pass the parameters to the Butler, which will find the images, stitch them together, and attempt the cutout. This may take time, because the PVIs or coadds virtually exist, be a request that covers a large space, or has multiple cutouts requested.

SODA allows for async operations though, so we know we can tell our caller to call again later to get their result no matter how long it takes.

Because the resulting files can be large, we can upload or copy them to shared storage in an object store, and have the image server redirect HTTP requests for finished work items to their URL in the object store. In this way, we can split up the workers and the servers and scale them up and down independently.

5.5.3   Performance, Load, and Failure Characteristics¶

For the image metadata portion of the system, these queries will be run against the TAP service, and have the same performance and load characteristics as noted in that section.

For the image retrieval and processing portion of the image service, we have it a bit easier. Much of the performance will be related to the speed of access to images, and if they already exist or are virtual products. For the files that exist, we will need to copy them off of a network share, which is a shared resource, which could be a bottleneck under heavy load.

Processing for creating virtual products will likely involve the workflow engine, and having to be queued and executed there. This is also a shared resource, so depending on load from other portions of the system, this could be slow and add latency to the end user.

For processing cutouts and doing mosaics, we will likely use the Butler and local CPU processing to create those products. This means we need to provision the CPU correctly - not too small so that big jobs take a long time, but not having a lot of unused resources on a worker. If we have workers that have too much CPU, we could always reduce the CPU requirements for each worker and have more workers to increase throughput.

Since the image service is just a proxy and processing layer on top of the existing data, there is no risk that the image server could destroy or lose data. The data is persisted at a lower level and the image server doesn’t require permissions to delete data. If the service goes down, the problem is that the data is inaccessible until it is restored. If the service itself doesn’t have to handle persistent storage (using an object store instead), then we don’t have to worry about persisting previous results between deploys.