johnmyleswhite/ProjectTemplate — pull request #21 by KentonWhite (2013-12-12 03:16:06+00:00)
Title: Added string interpolation to queries

This is probably the most controversial proposed feature. For me the big vision of ProjectTemplate is being able to bootstrap data analysis programmatically. This goes a long way to encouraging reproducibility in data science.
Currently the process supports loading data from single sources and then munging the data. The problem is if the data needs to be loaded in multiple stages.

Here is my use case:

I store Tweet texts in an ElasticSearch index and I store meta-data for each Tweet in a DB table. I run a query against ElasticSearch to retrieve the Tweet IDs. I then run a query against the DB table using the IDs to retrieve the meta-data.

Currently, I would load the IDs from the ElasticSearch index and then munge the data. This requires me to write code to access and query the database in the munging script -- a real case of code duplication. What I would like is to read the data from the ElasticSearch index and then use that data in the .sql file to make the query against the database, bootstrapping my final dataset from multiple sources.

My solution is to add string interpolation to the query term in the database reader:

query: SELECT * FROM my_table WHERE id IN (@{paste(ids, collapse=',')})

As long as the ElasticSearch data is loaded before this command there is no problem. For simple multistep data loading it is not hard to order the data files by hand.

If this feature seems like a good idea and we want to go further with multi-step data loading, the next step would be to add decencies to the data files so that the data loader can work out the order to load data files.