Data Science Applications to Astronomy

Week 5: Data Wrangling:

Databases & Queries

Databases

Simplest form

• Store data

• Retrieve data

• Commonly implemented as a set of tables

  • Columns contain different fields (e.g., ID, magnitude, RA, Dec)

  • Rows contain entries (e.g., 51 Pegasi, Kepler-10, HAT-P-13,... )

Value-added features

• Return subset of data efficiently

• Many strategies for how to filter data (e.g., order of operations)

• Database server can use heuristics to pick good strategy

• Allow for transactions to update database

Fundamental properties of databases

• Atomicity: All part of a transaction succeed, or the database is rolled back to its previous state

• Consistency: Data in database always satisfies its validation rules

• Isolation: Even if multiple transactions are made concurrently, there is no interference between transactions

• Durability: Once a transaction is committed, it will remain committed

SQL-based Database Servers

Open-source:

• MySQL

• PostgreSQL & Greenplum

Commercial:

• Microsoft SQL Server:

• IBM DB2

• Oracle Database

• ...

Continuing innovation in database systems

• SciDB (array & disk based database)

• MonetDB (column store)

• JuliaDB (pure Julia, for persistent data)

When selecting a database for a project, consider:

• How much data is to be stored?

• How frequent/large will transactions be?

• Are there specific hardware or OS requirements?

• Does the team have someone dedicated to supporting database?

Database Clients

• One database server many clients simultaneously

• Different clients can use different interfaces

  • Command line

  • Webpage

  • URL-based

  • Custom Graphical user interface (GUI)

    • TopCat

Queries

Query: An expression that requests database to return a specific subset of data.

Query languages:

• Structured Query Language (SQL): Dated, but by far the most common

• Astronomical Data Query Language (ADQL): Astronomy-specific

• Language Integrated Query (LINQ): Microsoft-supported

• Many more

SQL essentials

• Selecting (columns)

• Filtering (for rows)

• Joining (multiple tables)

• Aggregating (rows within a table)

SQL programming

• Variables

• Functions

• Procedures

• Data management

• Transactions

Virtual Observatory (VO)

Defines standards that help astronomers to collaborate effectively, emphasizing working with multiple data sources.

• Astronomical Data Query Language (ADQL)

• Table Access Protocol (TAP)

Astronomy-specific functions in ADQL

• AREA

• BOX

• CENTROID

• CIRCLE

• CONTAINS

• COORD1

• COORD2

• COORDSYS

• DISTANCE

• INTERSECTS

• POINT

• POLYGON

• REGION

Example Bad SQL Query

Do not send

select * from SomeTable

Why?

Example SQL Queries

Take a quick peak two columns data for first few entries

select top 10 X, Y from SomeTable 

Find extreme values of X

select top 10 X, Y 
from SomeTable 
order by X

Filter which rows are returned using expression

select top 10 x,y
from SomeTable 
where x*x+y*y between 0 and 1
order by x 

Check how many rows are in a table

select COUNT(designation) as N from gaiadr2.gaia_source

Gaia Archive

Check how many rows satisfy a filter

select COUNT(designation) as n, AVG(astrometric_n_good_obs_al) as astrometric_n_good_obs_al_ave
from gaiadr2.gaia_source
where phot_g_mean_mag < 14

Grouping data to make a histogram

select COUNT(designation) as N, 
       AVG(astrometric_n_good_obs_al) as astrometric_n_good_obs_al_ave,  
       AVG(phot_g_mean_mag) as phot_g_mean_mag_ave,
       ROUND(phot_g_mean_mag,1) as bin
from gaiadr2.gaia_source
where phot_g_mean_mag < 14
group by bin 
order by bin

Table Access Protocol (TAP)

Start with ADQL

SELECT <column list> FROM <table> WHERE <constraints>

but transform it into a url, by

1. Prepend a base service url

2. convert spaces to +'s

3. Deal with other special characters (e.g., +, quotes)

4. Optionally, specify format for results

e.g.,

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+pl_name,pl_masse,ra,dec+from+ps

Joins

Joining tables is a fundamental concept that can be applied either to DataFrames stored locally or as part of SQL/ADQL queries.

• innerjoin & semijoin: Return rows for values of the key that exist in both tables

• outerjoin: Return rows for values of the key that exist in either table

• leftjoin: Return rows for values of the key that exist in first table

• rightjoin: Return rows for values of the key that exist in second table

• antijoin: Return rows Return rows for values of the key that exist in first table but not the second table

• crossjoin: Return table with every row from first table as rows and every row from second table as columns

Examples

Join on Gaia ID

Find by position

Which row is the best match?

Questions?

Setup & Helper Code

select_cols_for_tap(cols)

Returns a string of comma-separated columns names from a vector of columns names (as either strings or symbols), for using in a TAP query.