{"action":"closed","number":198,"pull_request":{"url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/198","id":54824723,"html_url":"https://github.com/UrbanCCD-UChicago/plenario/pull/198","diff_url":"https://github.com/UrbanCCD-UChicago/plenario/pull/198.diff","patch_url":"https://github.com/UrbanCCD-UChicago/plenario/pull/198.patch","issue_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/198","number":198,"state":"closed","locked":false,"title":"Restructure API and ETL","user":{"login":"WillEngler","id":6283143,"avatar_url":"https://avatars.githubusercontent.com/u/6283143?v=3","gravatar_id":"","url":"https://api.github.com/users/WillEngler","html_url":"https://github.com/WillEngler","followers_url":"https://api.github.com/users/WillEngler/followers","following_url":"https://api.github.com/users/WillEngler/following{/other_user}","gists_url":"https://api.github.com/users/WillEngler/gists{/gist_id}","starred_url":"https://api.github.com/users/WillEngler/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/WillEngler/subscriptions","organizations_url":"https://api.github.com/users/WillEngler/orgs","repos_url":"https://api.github.com/users/WillEngler/repos","events_url":"https://api.github.com/users/WillEngler/events{/privacy}","received_events_url":"https://api.github.com/users/WillEngler/received_events","type":"User","site_admin":false},"body":"## Overview\r\n\r\nThis pull request restructures Plenario's database schema and implementation of the API and ETL for point-based datasets (that is, datasets derived from CSVs as opposed to shapefiles). The motivation is to simplify Plenario's core to ensure that Plenario's essential backend functionality (ingesting and delivering open datasets) works correctly and to ease the implementation of new features that will create relationships between point and shape datasets.\r\n\r\n## Context\r\n\r\nPlenario's original database design was - as I can tell - intended to implement a data-warehousing technique called [slowly changing dimensions](https://en.wikipedia.org/wiki/Slowly_changing_dimension) (SCD). A 'Master Table' holds one row for every record in every point dataset. The Master Table contains columns like `current_flag` that are intended to mark which version of a record is valid and should be used for queries, while also maintaining old versions of a record. However, the implementation of SCD wasn't fully carried out. That's causing all kinds of unfortunate effects like reporting duplicate values and never updating records once they've been ingested. \r\n\r\n## Decision\r\n\r\nAt the very least, Plenario should be able to mirror the state of its source datasets at the last time it ingested them. To implement this, I could have overhauled the existing SCD schema. However, I decided that it would be simpler (for both this fix and for future features) to scrap SCD. In lieu of the Master Table, I'm constructing DB queries to go straight to individual point datasets.\r\n\r\nThis PR mostly maintains the old schema's behavior. That's because I wanted to split up the work of restructuring and fixing to reduce the risk of a too-massive deployment.\r\n\r\n## Consequences\r\n\r\n**Performance**\r\nThe Master Table does more than just hold versioning information. It also enables a common geospatial index over all point datasets. That helps large requests against the `/timeseries` endpoint that need to query lots of datasets at once. However, every other endpoint only queries one dataset at a time. And our heaviest users only use the one-at-a-time endpoints. Those endpoints are faster with the new schema. (Examples: a `/timeseries` request against every point dataset over four years takes 3 minutes with Master, 4 minutes without. [This request](http://plenar.io/v1/api/timeseries/?obs_date__ge=2007-11-12&obs_date__le=2008-11-12&dataset_name__in=crimes_2001_to_present,cdph_environmental_complaints&agg=day) over two datasets takes 25 seconds with Master, 3 seconds without.)\r\n\r\n**ETL**\r\nOne big behavior change is that Plenario no longer captures duplicate records from a source dataset, nor does it report them. Performance-wise, I've cut out lots of intermediate table creation that should speed up ingest and update time.\r\n\r\n**API**\r\nThe `weather` parameter of the `/detail` endpoint has been confusingly causing `/detail` to return no records since we stopped joining points with NOAA sensor stations back in 2014. So I'm removing that `weather` parameter. (Nothing is changing about the raw weather endpoints though.)\r\n\r\nThe one breaking change I'm making is also removing the `census_block` parameter from all endpoints that supported it. I intend to bring back this functionality soon in a more generalized way. I regret this breaking change, but I judged it was more important to push through fixes for our fundamental ETL problems first.\r\n\r\n## Follow-up Steps\r\n\r\n* Add support for updating, deleting records on update.\r\n* Clean up and automate nascent test suite.\r\n* Make usability tweaks to API (already started with tweaks in this PR by - for example - returning a descriptive error message instead of a 500 on misformatted requests)","created_at":"2015-12-30T19:49:32Z","updated_at":"2016-01-06T22:24:21Z","closed_at":"2016-01-06T22:24:20Z","merged_at":"2016-01-06T22:24:20Z","merge_commit_sha":"81ac6c3f312130227c6febff2e6db8f482d8781c","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/198/commits","review_comments_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/198/comments","review_comment_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/comments{/number}","comments_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/198/comments","statuses_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/statuses/91bf54f801c7a3a205822b96420d47016e1ef57c","head":{"label":"UrbanCCD-UChicago:schema-change","ref":"schema-change","sha":"91bf54f801c7a3a205822b96420d47016e1ef57c","user":{"login":"UrbanCCD-UChicago","id":6922503,"avatar_url":"https://avatars.githubusercontent.com/u/6922503?v=3","gravatar_id":"","url":"https://api.github.com/users/UrbanCCD-UChicago","html_url":"https://github.com/UrbanCCD-UChicago","followers_url":"https://api.github.com/users/UrbanCCD-UChicago/followers","following_url":"https://api.github.com/users/UrbanCCD-UChicago/following{/other_user}","gists_url":"https://api.github.com/users/UrbanCCD-UChicago/gists{/gist_id}","starred_url":"https://api.github.com/users/UrbanCCD-UChicago/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/UrbanCCD-UChicago/subscriptions","organizations_url":"https://api.github.com/users/UrbanCCD-UChicago/orgs","repos_url":"https://api.github.com/users/UrbanCCD-UChicago/repos","events_url":"https://api.github.com/users/UrbanCCD-UChicago/events{/privacy}","received_events_url":"https://api.github.com/users/UrbanCCD-UChicago/received_events","type":"Organization","site_admin":false},"repo":{"id":14935974,"name":"plenario","full_name":"UrbanCCD-UChicago/plenario","owner":{"login":"UrbanCCD-UChicago","id":6922503,"avatar_url":"https://avatars.githubusercontent.com/u/6922503?v=3","gravatar_id":"","url":"https://api.github.com/users/UrbanCCD-UChicago","html_url":"https://github.com/UrbanCCD-UChicago","followers_url":"https://api.github.com/users/UrbanCCD-UChicago/followers","following_url":"https://api.github.com/users/UrbanCCD-UChicago/following{/other_user}","gists_url":"https://api.github.com/users/UrbanCCD-UChicago/gists{/gist_id}","starred_url":"https://api.github.com/users/UrbanCCD-UChicago/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/UrbanCCD-UChicago/subscriptions","organizations_url":"https://api.github.com/users/UrbanCCD-UChicago/orgs","repos_url":"https://api.github.com/users/UrbanCCD-UChicago/repos","events_url":"https://api.github.com/users/UrbanCCD-UChicago/events{/privacy}","received_events_url":"https://api.github.com/users/UrbanCCD-UChicago/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/UrbanCCD-UChicago/plenario","description":"RESTful API for geospatial and time aggregation across multiple open datasets.","fork":false,"url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario","forks_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/forks","keys_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/keys{/key_id}","collaborators_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/teams","hooks_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/hooks","issue_events_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/events{/number}","events_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/events","assignees_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/assignees{/user}","branches_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/branches{/branch}","tags_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/tags","blobs_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/refs{/sha}","trees_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/trees{/sha}","statuses_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/statuses/{sha}","languages_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/languages","stargazers_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/stargazers","contributors_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/contributors","subscribers_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/subscribers","subscription_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/subscription","commits_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/commits{/sha}","git_commits_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/commits{/sha}","comments_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/comments{/number}","issue_comment_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/comments{/number}","contents_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/contents/{+path}","compare_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/compare/{base}...{head}","merges_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/merges","archive_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/downloads","issues_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues{/number}","pulls_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls{/number}","milestones_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/milestones{/number}","notifications_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/labels{/name}","releases_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/releases{/id}","created_at":"2013-12-04T21:12:31Z","updated_at":"2015-12-05T03:13:41Z","pushed_at":"2016-01-06T22:24:20Z","git_url":"git://github.com/UrbanCCD-UChicago/plenario.git","ssh_url":"git@github.com:UrbanCCD-UChicago/plenario.git","clone_url":"https://github.com/UrbanCCD-UChicago/plenario.git","svn_url":"https://github.com/UrbanCCD-UChicago/plenario","homepage":"http://plenar.io","size":31758,"stargazers_count":72,"watchers_count":72,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":23,"mirror_url":null,"open_issues_count":37,"forks":23,"open_issues":37,"watchers":72,"default_branch":"master"}},"base":{"label":"UrbanCCD-UChicago:master","ref":"master","sha":"f71071931af342efadd1315680ec2ca0589409b4","user":{"login":"UrbanCCD-UChicago","id":6922503,"avatar_url":"https://avatars.githubusercontent.com/u/6922503?v=3","gravatar_id":"","url":"https://api.github.com/users/UrbanCCD-UChicago","html_url":"https://github.com/UrbanCCD-UChicago","followers_url":"https://api.github.com/users/UrbanCCD-UChicago/followers","following_url":"https://api.github.com/users/UrbanCCD-UChicago/following{/other_user}","gists_url":"https://api.github.com/users/UrbanCCD-UChicago/gists{/gist_id}","starred_url":"https://api.github.com/users/UrbanCCD-UChicago/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/UrbanCCD-UChicago/subscriptions","organizations_url":"https://api.github.com/users/UrbanCCD-UChicago/orgs","repos_url":"https://api.github.com/users/UrbanCCD-UChicago/repos","events_url":"https://api.github.com/users/UrbanCCD-UChicago/events{/privacy}","received_events_url":"https://api.github.com/users/UrbanCCD-UChicago/received_events","type":"Organization","site_admin":false},"repo":{"id":14935974,"name":"plenario","full_name":"UrbanCCD-UChicago/plenario","owner":{"login":"UrbanCCD-UChicago","id":6922503,"avatar_url":"https://avatars.githubusercontent.com/u/6922503?v=3","gravatar_id":"","url":"https://api.github.com/users/UrbanCCD-UChicago","html_url":"https://github.com/UrbanCCD-UChicago","followers_url":"https://api.github.com/users/UrbanCCD-UChicago/followers","following_url":"https://api.github.com/users/UrbanCCD-UChicago/following{/other_user}","gists_url":"https://api.github.com/users/UrbanCCD-UChicago/gists{/gist_id}","starred_url":"https://api.github.com/users/UrbanCCD-UChicago/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/UrbanCCD-UChicago/subscriptions","organizations_url":"https://api.github.com/users/UrbanCCD-UChicago/orgs","repos_url":"https://api.github.com/users/UrbanCCD-UChicago/repos","events_url":"https://api.github.com/users/UrbanCCD-UChicago/events{/privacy}","received_events_url":"https://api.github.com/users/UrbanCCD-UChicago/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/UrbanCCD-UChicago/plenario","description":"RESTful API for geospatial and time aggregation across multiple open datasets.","fork":false,"url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario","forks_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/forks","keys_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/keys{/key_id}","collaborators_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/teams","hooks_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/hooks","issue_events_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/events{/number}","events_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/events","assignees_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/assignees{/user}","branches_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/branches{/branch}","tags_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/tags","blobs_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/refs{/sha}","trees_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/trees{/sha}","statuses_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/statuses/{sha}","languages_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/languages","stargazers_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/stargazers","contributors_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/contributors","subscribers_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/subscribers","subscription_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/subscription","commits_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/commits{/sha}","git_commits_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/git/commits{/sha}","comments_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/comments{/number}","issue_comment_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/comments{/number}","contents_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/contents/{+path}","compare_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/compare/{base}...{head}","merges_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/merges","archive_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/downloads","issues_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues{/number}","pulls_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls{/number}","milestones_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/milestones{/number}","notifications_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/labels{/name}","releases_url":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/releases{/id}","created_at":"2013-12-04T21:12:31Z","updated_at":"2015-12-05T03:13:41Z","pushed_at":"2016-01-06T22:24:20Z","git_url":"git://github.com/UrbanCCD-UChicago/plenario.git","ssh_url":"git@github.com:UrbanCCD-UChicago/plenario.git","clone_url":"https://github.com/UrbanCCD-UChicago/plenario.git","svn_url":"https://github.com/UrbanCCD-UChicago/plenario","homepage":"http://plenar.io","size":31758,"stargazers_count":72,"watchers_count":72,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":23,"mirror_url":null,"open_issues_count":37,"forks":23,"open_issues":37,"watchers":72,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/198"},"html":{"href":"https://github.com/UrbanCCD-UChicago/plenario/pull/198"},"issue":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/198"},"comments":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/issues/198/comments"},"review_comments":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/198/comments"},"review_comment":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/pulls/198/commits"},"statuses":{"href":"https://api.github.com/repos/UrbanCCD-UChicago/plenario/statuses/91bf54f801c7a3a205822b96420d47016e1ef57c"}},"merged":true,"mergeable":null,"mergeable_state":"unknown","merged_by":{"login":"WillEngler","id":6283143,"avatar_url":"https://avatars.githubusercontent.com/u/6283143?v=3","gravatar_id":"","url":"https://api.github.com/users/WillEngler","html_url":"https://github.com/WillEngler","followers_url":"https://api.github.com/users/WillEngler/followers","following_url":"https://api.github.com/users/WillEngler/following{/other_user}","gists_url":"https://api.github.com/users/WillEngler/gists{/gist_id}","starred_url":"https://api.github.com/users/WillEngler/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/WillEngler/subscriptions","organizations_url":"https://api.github.com/users/WillEngler/orgs","repos_url":"https://api.github.com/users/WillEngler/repos","events_url":"https://api.github.com/users/WillEngler/events{/privacy}","received_events_url":"https://api.github.com/users/WillEngler/received_events","type":"User","site_admin":false},"comments":1,"review_comments":0,"commits":24,"additions":3453,"deletions":3220,"changed_files":54}}