Updated Apr 15 posted queries
You may work in a team of three-four students. You are encouraged to engage in general discussions with other teams regarding the project, but specific details of a solution, including the solution itself, must always be the team's own work.
Submit:
antlrworks.jar is installed in /home/db2inst1/lib/antlrworks.jar on the submission machine
The project will use a benchmark based on the Pan-STARRS project. In brief, the Pan-STARRS project scans the sky for astronomical objects that might pose a threat to Earth. Each scan of the sky produces detections of astronomical objects at different positions. By "stacking up" different scans of the same position/region of the sky, moving objects may be detected.
The benchmark consists of the following tables with associated cardinalities:
41983848 rows in Object
1014349449 rows in Detection
209919240 rows in StackDetection
83646580 rows in StackApFlx
83646580 rows in StackModelFit
3458632 rows in Photoz
2903 rows in FrameMeta
5 rows in Filter
2 rows in PhotozRecipe
Loaded into an SQLite database this produces a file of 164,978,269,184 bytes (before indexing on objectID).
The DB2 DDL file for all the tables can be found in
crtables.sql.
crtables.db2 (optimized for DB2).
The data resides on ec2-67-202-8-208.compute-1.amazonaws.com in the /mnt/astronomy/ directory. Consult the wiki in laulima for more information.
Tne nneighbor (nearest neighbor) table hasn't been populated yet and is pending the actual neighbor determinations. If we limit the associations to the N nearest neighbors, this table will be N times larger than the number of objects. It could be smaller should we decide to limit it to N nearest neighbors within some fixed distance.
Since the current query workload only queries the object and detection table, you only need to partition and load those two tables.
We will partition the large tables on the objectID. The objectID is constructed
from the position of the object in the sky (see image below).
The current PS1 database has adopted an approach that slices the sky into zones in declination (our default zone height is 0.008333 deg). The sky is partitioned into 16 slices with the declination widths adjusted to give approximately the same number of sources in each slice. All the large tables (objects, detections, stackdetection, stackapflx, stackmodelfit, etc.) are partitioned in this way. The partition key is the objectID which is an attribute in each of these tables. This approach is used to keep sources that are close together on the sky close together on the computer disks.
The calculation of the objectID given a source's reference position (right ascension and declination in degrees) is shown in the reproduction below of one of our PowerPoint slides. Given the object's declination we compute the zone id (and fraction) the integer part of the zone id comprises the first 5 digits of the objectID. The next 9 digits of the objectID are computed by multiplying the right ascension by 1,000,000 and taking the integer part of the result. The final 4 digits of the objectID are taken from the fractional part of the full zone id.
Thus, the objid includes the zoneid as its most significant digits, the right ascension, and declination. Examination of the object id computed in this way for real sources shows that the least significant digit of the objid is essentially distributed randomly over the digits 0-9 and could be used as the basis for a random hashing of the sources.
If one wants to apply a form of spatial partitioning, a simple approach would be to partition on the zoneid. This attribute can be found either in the Object table or by simply taking the first 5 digits of the objid.
The current data set has the following characteristics: