*A**nnual Report for*

*Large-Scale On-Demand Cross-Matching with Open SkyQuery*

A.R. Thakar (PI), Maria Nieto-Santisteban, Jordan Raddick

**November 13, 2023**

# Introduction

The primary mission of the National Virtual Observatory (NVO) is to make
distributed digital archives accessible and interoperable so that
astronomers can maximize their potential for scientific discovery by
cross-matching multi-wavelength data between multiple archives on the
fly. This project aims to meet this goal by developing an advanced and
versatile all-sky cross-matching engine for large distributed
astronomical datasets by bringing together and leveraging the results of
two previously funded AISR projects -- NAG5-10742 which produced
SkyQuery.net and later on Open SkyQuery \[1\], a limited distributed
query and cross-matching facility for modest-sized cross-matches; and
NAG5-12092, which resulted in the development of a spatial Zone-based
partitioning scheme that allows a large database to be partitioned
across a cluster of nodes so as to enable high-speed parallel data
access and analysis.

In order to create an advanced all-sky cross-matching engine for large
distributed astronomical catalogs, we are extending the present SkyNode
and Open SkyQuery functionalities in the following ways:

1.  Incorporate parallel data access with Zone partitioning into
    individual SkyNodes

```{=html}
<!-- -->
```
1.  Transport data between SkyNodes and the portal using the
    mega-streaming transport protocol developed by Grossman et al.

2.  Replace the current synchronous query execution with an asynchronous
    system that is compatible with emerging IVOA standards.

In the first year, we have made considerable progress in the first task
(parallel distributed data access) and some progress in task #3
(asynchronous query execution), as described below.

# A 10-TB Partitioned Dataset

The *Zones* algorithm \[2\] is a very efficient way to partition a
spatial dataset into slices based on declination zones so that
individual slices can be hosted on different servers for parallel data
access. There are actually two parts to the partitioning process -- data
loading and data access. The data must first be distributed and loaded
into the individual slices, and secondly there must be a way to access
the distributed data in parallel while still presenting a unified
logical view to the user.

Using the Zones partitioning scheme and the Microsoft SQL Server
database management system (DBMS), we have successfully partitioned a
simulated 10 TB dataset that we created as a prototype for the
Pan-STARRS PS1 data archive into 4 slices and run distributed parallel
queries on it, as we describe below. This is the first step in the
process of developing a large-scale cross-match engine.

By "partitioned dataset" we mean a database that is distributed across a
cluster of data nodes, however we use the term "slices" to describe the
partitions on the data nodes to distinguish them from partitioned tables
on a single server. Hence *partitions* refer to partitioned *tables*
used to [scale up]{.underline} on a single server, whereas *slices*
refer to partitioning of the data across a
![](media/image1.png){width="3.107638888888889in"
height="3.5034722222222223in"}cluster in order to [scale
out]{.underline}. Typically the slices are independent databases on each
of the cluster nodes, and they are logically viewed as a single database
for user queries. We use SQL Server's *linked server* and *distributed
partitioned view* (DPV) technologies to access the distributed slices of
a given dataset. The distinction between partitioned tables and
distributed partitioned views is illustrated in Figure 1. Partitioned
tables may be used to scale up on a single server by partitioning the
table data across multiple filegroups on separate disk volumes.
Distributed partitioned views are used to scale out by providing a
logical unified view of multiple tables on different servers.

![](media/image2.wmf){width="3.109722222222222in"
height="2.615972222222222in"}

The distribution of the data rows in the largest table is shown in Table
1, showing the declination zone partitioning and fraction of the data on
each slice. Table 1 also shows how the data is locally partitioned on
the master (main) and each slice, to allow quick movement of data slices
from the main to the slice servers.

Using this sliced configuration, we have been able to achieve
near-linear parallelization for the cross-matching of detections with
objects. The DPVs also give us parallel query plans, but due to current
SQL Server limitations on some types of queries with DPVs, most of the
parallel query performance will come from simultaneous user queries that
access data on different slices. This is the most frequent type of query
anyway (localized to a certain region of the sky), so we expect to get
good query performance overall.

The most important result of this work is that we have demonstrated the
cross-matching performance that we can achieve with the scaled-out 10 TB
dataset.

# Asynchronous Query Execution

With partial funding from a previous AISR grant, we developed a batch
query workbench service called CasJobs. CasJobs provides a personal user
database called MyDB, and an asynchronous query execution mode that
sends query results to the MyDB database.

In the past year, we have begun to investigate how this framework can be
extended to provide *distributed* query execution in an asynchronous
workflow model. This will require the ability to temporarily store and
retrieve data in a distributed storage framework.

We are actively involved in preparing and proposing the VOSpace
distributed storage specification to the International Virtual
Observatory Alliance (IVOA) \[3\]. The VOSpace standard will provide the
higher-level abstractions, integrating multiple heterogeneous storage
systems into a global virtual file system. It also define a common
interface that all of the storage systems must implement, enabling a
VOSpace service to move data between storage locations without having to
handle multiple different interfaces.

Members of our group worked with scientists at CalTech to come up with a
draft VOSpace specification that has recently been proposed to IVOA. Our
next task is to develop a VOSpace wrapper for CasJobs/MyDB.

# EPO

We are continuing to collaborate with Wil van der Veen and Theresa Moody
of the New Jersey Astronomy Center for Education (NJACE) on an EPO
component to this grant. The astronomical content has shifted from brown
dwarfs to cosmology to better reflect content that appears in national
standards. The target audience has also changed slightly to become high
school elective astronomy classes. However, the overall approach of
using web-based tools to access large data sets remains the same.

We have also developed a tool to visually display cross-match results
from SDSS and GALEX. This will allow students to contextualize the data
that their queries return -- they can see exactly which galaxies they
will be considering for their graphs.

The new content of the lesson plan is to create a Hubble Diagram to show
that the Universe is expanding, and as an extension to create an
ultraviolet color-magnitude diagram to show that it is also evolving. We
have outlined a 15-day lesson plan consisting of ten activities,
following the 5E learning cycle format. Theresa Moody has written first
drafts of all but the extension activity.

# References

1.  Budavári, T., et al. "Open SkyQuery -- VO Compliant Dynamic
    Federation of Astronomical Archives" in Ochsenbein F., Allen M. and
    Egret D. *Astronomical Data Analysis Software and Systems* XIII ASP
    Conference Series Vol. 314, 2004.

2.  Gray, J., Nieto-Santisteban, M.A., and Szalay, A.S., "[The Zones
    Algorithm for Finding Points-Near-a-Point or Cross-Matching Spatial
    Datasets,](http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-52)"
    Microsoft Technical Report MSR-TR-2006-52, April 2006

3.  Graham, M. et al., "VOSpace 1.1 Specification", IVOA Draft
    Specification,
    http://www.ivoa.net/twiki/bin/view/IVOA/VOSpace11Specification.