Transient Detection and SQL LOFAR Transients Key Science Project meeting January 9 2014 Gijs Molenaar @gijzelaerr [email protected] 

TRAP • TRAnsient Detection Pipeline • Detect Transients in AARTFAAC data • 24/7 full sky monitor 

Images 10 bands (image cube) • 1 image cube per second • In future 10 images per second • 20 to 50 sources per image 

Data • Source (unique id) • Position (ra & decl) • Error (ra & decl) • Flux • Shape (2d gaussian) • Timestamp • Frequency (band) 

Data rate • 500 - 5000 inserts per second • May get more in future 

Calculations • Associate in frequency • Associate in time • Transients detection 

SQL Storing, manipulating and retrieving data De facto standard Structured data Relational data Old language 

The data Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency Unique id, ra, decl, errors, flux, shape, timestamp, frequency table 

No content

No content

No content

No content

No content

No content

Quite a lot of data • So quite a lot of calculations • move calculations to data (base) • Minimal communication 

Problems with SQL ● Mix of language domains (python, SQL) ● No SQL standard (various dialects) ● Manual: ● type checking / conversion ● Error handling ● schema migrations / instantiation 

Problems with monolithic queries • hard to read • hard to reason about • Hard to atomize • hard to test • Code reuse difficult • hard to maintain 

Example 

Solution? • Server side functions? 

NO! • DB revisions, versioning hell • For every code change new version • Schema migrations • Init functions on startup? • DB specific 

SOLUTION 

Why? • Object Relational Mapper • Support multiple database dialect • Client side error checking (before query execution) • Less code • Better Query Builder Language SQLAlchemy -> SQL assembler -> C 

But also ● ● 3rd party applications! ● Schema management ● Creation and Up/down grading ● Tools for creating migration paths 

And last but not least ● Backwards compatible! ● Can still do raw queries 

example 

No content

No content

Why not earlier? • Unforeseen problem at project start • No MonetDB dialect for SQLAlchemy • (I made one) 

sqlalchemy-monetdb • pip install sqlalchemy-monetdb • https://github.com/gijzelaerr/sqlalchemy-monetdb • https://github.com/gijzelaerr/djonet • (banana) 

Possible Disadvantage A bit harder to see the actual SQL BUT SQLALchemy very similar to SQL 

Conclusion ● SQLAlchemy is SQL powertool ● I see no reason NOT to use SQLAlchemy ● Will result in improved code readability and maintainability ● (if properly used of course) 

Questions?