# Presentation: 075426

## María Nieto-Santisteban
Ani Thakar 
Alex Szalay, et al.

The Johns Hopkins University

- Ani Thakar
- Alex Szalay, et al.
*The Johns Hopkins University*

**AISRP 2008 @ College Park, University of Maryland**

- Large-Scale Cross-Matching  with Open SkyQuery

## Goals

*Implement an access and cross-matching engine that facilitates access to large digital archives and enables new scientific discoveries by cross correlating multi-wavelength datasets*

## Handling the Large-Scale

**The “20” Spatial Queries**
**Partitioning & Parallelization**
**Asynchronous Data Access**
**Efficient Cross-Match**
**Workflow Management**
**Cluster Management**
**Data transport**

## The “20” Spatial Queries

- Single/Multi Catalog & Regions
- Cone search:  Find objects within a circle
- Find objects within a circle satisfying a high multi-dimensional constraints
- Find the closest neighbor
- Find objects within a region
- Find objects in/outside masked regions
- Find objects near the edges of a region
- Compute the area of a region
- Find surveys covering a given region
- Find the intersection between several surveys
- Count objects from a list of regions

## The “20” Spatial Queries

- Find these 1k - 100k objects in these catalogs
- For all catalogs, extract a random sample of existing objects within a given region
- Cross-match 2 catalogs within a given region
- Cross-match n catalogs, n > 2, within a given region
- Find objects which are in A, B, and C but not in D
- Given a sparse grid, find the closest grid point  for all objects in the catalog
- Find multiple detections of the same object with given magnitudes variations
- Find all quasars within a region and compute their distance to surroundings galaxies
- more . . . **open to discussion ...**

## Zones (spatial partitioning and indexing algorithm)
Partition and bin the data in declination zones
ZoneID = floor ((dec + 90.0) / zoneHeight)
Some tricks required to handle spherical geometry
Place the data close on disk
Clustered Index on  ZoneID and RA
Fully implemented in SQL 
Efficient
Cone searches 
Cross-Match (especially)

Enables Parallelization
Execute the query on a data partition
Partition the query and execute it on the full dataset

- Partition and bin the data in declination zones
- ZoneID = floor ((dec + 90.0) / zoneHeight)
- Some tricks required to handle spherical geometry
- Place the data close on disk
- Clustered Index on  ZoneID and RA
- Fully implemented in SQL
- Efficient
- Cone searches
- Cross-Match (especially)
**Enables Parallelization**
- Execute the query on a data partition
- Partition the query and execute it on the full dataset

- Partitioning & Parallelization

## CasJobs, Asynchronous Data Access

- Solution to the SDSS increasing size and demand
- Astronomer’s workbench
- “Unlimited” queries against the large SDSS databases
- Minimize data movement
- Personal database, MyDB, under user’s full control
- Full power to create tables, stored procedures, functions, load personal data, etc.
- Collaborative environment
- Easy access to prior data releases
- Job tracking system
- Accessed through a Guide User Interface
- Accessed though a WS  interface
- Not exclusive of SDSS nor Astronomy!

**Notes:**
What is CasJobs?

## Efficient Cross-Matching

- Matching stars between 2MASS and SDSS DR5
- 	74 M x 54 M rows, 4.5 h instead of 2+ days
- 1 degree match between SDSSDR5 and a sparse grid
- 	350 M x 50 k  rows, 7 h instead of 1 year
- LSST simulations for alert detection
- 	6 M x 125 k rows,  40 s
- Pan-STARRS on-the-fly association
- 	1.3 billion objects x 120 million detections,  1.5 h

**Notes:**
Points
Astronomical data is spread all over the World
Schemas, data structures are very diverse. Each archive, each publisher, each owner has its own way to publish the data
Through the VO we are trying to establish protocols that help communication and minimize diversity
Cross-Match requires to have the data together at some point
How can we tackle this problem?

## Graywulf

- Date: Tue, 2 Nov 2004 14:26:37 -0800
- From: Jim Gray <gray@microsoft.com>
- To: Maria A. Nieto-Santisteban <nieto@skysrv.pha.jhu.edu>
- Subject: RE: Scaleout
- I think your cluster finding work, the loader, the sector stuff, the
- match stuff, ... all are examples of map-reduce.
**I would like to build a system to describe these parallel workflows and run them on a replicated database, then take the outputs and glue them together (map-reduce).**
- Make sense?

## Graywulf

- HP cluster

- DB cluster

- User

- SkyQuery

## Architecture

- Web Based Interface (WBI)

- Query Manager (QM)

- Workflow Manager (WFM)

- Cluster Manager (CLM)

- Application

- Perf. Monitor

**Notes:**
Split the loader into WorkFlow sys and 

## Open SkyQuery Next Generation

- Web Based Interface (WBI)

- Query Manager (QM)

- Workflow Manager (WFM)

- Cluster Manager (CLM)

- Perf. Monitor

- DRL

**Linked Servers**

- MatchDB**1**

**SDSS**

**2MASS**

- MatchDB**n**

**OSQ**

**myDB**

**SDSS**

**2MASS**

**VoSpace**

**myDB**

**myDB**

**Notes:**
Split the loader into WorkFlow sys and 

## Pan-STARRS

_**Legend**_
**Database **
**Full table ****	****[partitioned table]**
**Partitioned View**

- Web Based Interface (WBI)

- Query Manager (QM)

- Workflow Manager (WFM)

- Cluster Manager (CLM)

**PS1**

**Objects**
**Meta**

**[Objects_pm]**
**[Detections_pm]**
**Meta**

**Detections**

**Linked Servers**

- PS1 database

**Pm**

**P1**

**[Objects_p1]**
**[Detections_p1]**
**Meta**

- Performance Monitor

- DRL

**Notes:**
Split the loader into WorkFlow sys and 

## Pan-STARRS Prototype in Context

| Survey | Objects | Detections |
| --- | --- | --- |
| SDSS DR6 | 3.8  108 |  |
| 2MASS | 4.7  108 |  |
| USNO-B | 1.0  109 |  |
| Prototype | 1.3  109 | 1.4  1010 |
| PS1 (end of survey) | 5.5  109 | 2.3  1011 |

## Pan-STARRS Prototypes

|  | miniProto | myProto | Prototype | PS1 |
| --- | --- | --- | --- | --- |
| SDSS* Stars | 5.7 x 104 | 1.3 x 107 | 1.1 x 108 |  |
| SDSS* Galaxies | 9.1 x 104 | 1.1 x 107 | 1.7 x 108 |  |
| Galactic Plane | 1.5 x 106 | 3 x 106 | 1.0 x 109 |  |
| TOTAL | 1.6 x 106 | 2.6 x 107 | 1.3 x 109 | 5.5 x 109 |

- *  “SDSS” includes a mirror of 11.3 <  < 30 objects to  < 0

**Total GB of csv loaded data**:		   300 GB
**CSV Bulk insert load:**			       8 MB/s
**Binary Bulk insert:**			18-20 MB/s
**Creation** 	_Started_:	 	October 15th 2007
- 		_Finished_:	October 29th 2007
**Includes**
-  10   epochs of single image detections (2 x 5 filters)
-    5   epochs of Stack detections   (1 x 5 filters)

**Notes:**
SDSS Stars: 7.42 GB
SDSS Galaxies: 94.11 GB
GP Stars: 196.84 GB
Total = 298.37

## Size of PS1 Prototype Database

| Table | Main | Slice1 | Slice2 | Slice3 | Loader | Total |
| --- | --- | --- | --- | --- | --- | --- |
| Objects | 547.6 | 165.4 | 165.3 | 165.3 | 137.1 | 1180.6 |
| StackPsfFits | 841.5 |  |  |  |  | 841.6 |
| StackToObj | 300.9 |  |  |  |  | 300.9 |
| StackModelFits | 476.7 |  |  |  |  | 476.7 |
| P2PsfFits |  | 879.9 | 853.0 | 733.5 | 74.7 | 2541.1 |
| P2ToObj |  | 125.7 | 121.9 | 104.8 | 3.8 | 356.2 |
| Total | 2166.7 | 1171.0 | 1140.2 | 1003.6 | 215.6 | 5697.1 |
| Extra Tables | 207.9 | 987.1 | 960.2 | 840.7 | 957.3 | 3953.2 |
| Allocated / Free | 1878.0 | 1223.0 | 1300.0 | 1121.0 | 666.0 | 6188.0 |
| Grand Total | 4252.6 | 3381.1 | 3400.4 | 2965.3 | 1838.9 | 15838.3 |

**9.6 TB of data in a distributed database**

- Table sizes are in GB

## Well-Balanced Partitions

| Server | Partition | Rows | Fraction | Dec Range |
| --- | --- | --- | --- | --- |
| Main | 1 | 432,590,598 | 33.34% | 32.59 |
| Slice 1 | 1 | 144,199,105 | 11.11% | 14.29 |
| Slice 1 | 2 | 144,229,343 | 11.11% |   9.39 |
| Slice 1 | 3 | 144,162,150 | 11.12% |   8.91 |
| Main | 2 | 432,456,511 | 33.33% | 23.44 |
| Slice 2 | 1 | 144,261,098 | 11.12% |   8.46 |
| Slice 2 | 2 | 144,073,972 | 11.10% |   7.21 |
| Slice 2 | 3 | 144,121,441 | 11.11% |   7.77 |
| Main | 3 | 432,496,648 | 33.33% | 81.98 |
| Slice 3 | 1 | 144,270,093 | 11.12% | 11.15 |
| Slice 3 | 2 | 144,090,071 | 11.10% | 14.72 |
| Slice 3 | 3 | 144,136,484 | 11.11% | 56.10 |

## VO Space @ JHU

- - C# 2.0 implementation based on new Window Communication Foundation (WCF)
- - Self-contained SQL Server 2005 backend
- VOPipe Architecture
- - Higher level services for data/work flows
- - Basis for next generation VO services such as Open SkyQuery

## Education and Public Outreach

- Visualization tool for Open SkyQuery
- Lesson plan for high school astronomy
- Hubble Diagram with SDSS & GALEX

## Summary

**The “20” Spatial Queries**
**Partitioning & Parallelization**
**10 TB distributed DB well balanced partitioned**
**Asynchronous Data Access**
**CasJobs **
**Efficient Cross-Match**
**1.3 billion x 120 m in 1.5 h **
**Work in progress**
**Workflow Management**
**Cluster Management**
**Data transport**
**20 Spatial Queries benchmark**

## Related Publications

**20 Spatial Queries for an Astronomer’s Bench(mark), **M. Nieto-Santisteban, T. School, A. Szalay, A. Kemper, in Proceedings of Astronomical Data Analysis Software and Systems XVII, London, UK, 23rd - 26th September 2007.
**Probabilistic Cross-Identification of Astronomical Sources,** T. Budavari, A. Szalay, and M. Nieto-Santisteban, in Proceedings of Astronomical Data Analysis Software and Systems XVII, London, UK, 23rd - 26th September 2007.
**The Pan-STARRS Object Data Manager Database, **J. Heasley, M. Nieto-Santisteban, A. Szalay, A. Thakar, AAS Meeting 210th  - Honolulu, HW, USA, 5th – 10th, May 2007.
**LSST, the Spatial Cross-Match Challenge, **María A. Nieto-Santisteban, Alexander S. Szalay, Aniruddha R. Thakar, Jim Gray** **Astronomical Data in Proceedings of Astronomical Data Analysis Software and Systems XVI, Tucson, AZ, USA, 15th - 18th  October 2006.
**When Database Systems Meet the Grid. **María A. Nieto-Santisteban, Jim Gray, Alexander Szalay, James Annis, Aniruddha R. Thakar, William J. O’Mullane, in Proceedings of ACM CIDR 2005, Asilomar, CA, January 2005.

## Related Publications

**Cross-matching Multiple Spatial Observations and Dealing with Missing Data, **J. Gray, A. Szalay, T. Budavári, R. Lupton, M. Nieto-Santisteban, A. Thakar, Microsoft Technical Report: MSR TR 2006-175, December 2006.
**The Zones Algorithm for Finding Points-Near-a-Point or Cross-Matching  Spatial Datasetes**, Jim Gray, María A. Nieto-Santisteban, Alexander S. Szalay, Microsoft Technical Report: MSR-TR-2006-52, April 2006.
**Batch is back: CasJobs, serving multi-TB data on the Web. **William O’Mullane, Nolan Li, Maria A. Nieto-Santisteban, Ani Thakar, Alexander S. Szalay, Jim Gray in in the Proceedings of the 2005 IEEE International Conference on Web Services (ICWS 2005). Orlando, FL, July 2005.
**Large-Scale Query and XMatch, Entering the Parallel Zone. **María A. Nieto-Santisteban, Aniruddha R. Thakar, Alexander S. Szalay, Jim Gray** **Astronomical Data Analysis Software and Systems XV ASP Conference Series, Vol. 351, Proceedings of the Conference Held 2-5 October 2005 in San Lorenzo de El Escorial, Spain. Edited by Carlos Gabriel, Christophe Arviset, Daniel Ponz, and Enrique Solano. San Francisco: Astronomical Society of the Pacific, 2006., p.493.