{"action":"created","comment":{"url":"https://api.github.com/repos/DoSomething/quasar/pulls/comments/190706438","pull_request_review_id":123141510,"id":190706438,"diff_hunk":"@@ -106,79 +121,87 @@ def compile_statuses(self, queries):\n             descriptions.append(description)\n \n         out = pd.DataFrame(\n-            {'query': descriptions,\n-             'output': values,\n+            {'output': values,\n              'table': table,\n-             'timestamp': ts\n+             'timestamp': ts,\n+             'query': descriptions\n              })\n         return out\n \n     def extract_latest_value(self, table, desc):\n         max_query = \\\n-            \"SELECT  \\\n-                m.output  \\\n-            FROM quasar.monitoring m  \\\n-            INNER JOIN ( \\\n-            SELECT \\\n-                t.table, \\\n-                t.query,  \\\n-                max(t.timestamp) AS max_created \\\n-            FROM quasar.monitoring t \\\n-            WHERE t.table = '\" + table + \"' AND t.query = '\" + desc + \"' \\\n-                ) tim ON tim.max_created = m.timestamp \\\n-            WHERE m.table = '\" + table + \"'  \\\n-            AND m.query = '\" + desc + \"';\"\n+            f\"\"\"\n+            SELECT\n+                m.output\n+            FROM etl_monitoring.monitoring m\n+            INNER JOIN (\n+                SELECT \n+                    t.table,\n+                    t.query,\n+                    max(t.timestamp) AS max_created\n+                FROM etl_monitoring.monitoring t\n+                WHERE t.table = '{table}' \n+                AND t.query = '{desc}'\n+                GROUP BY t.table, t.query\n+                    ) tim ON tim.max_created = m.timestamp\n+            WHERE m.table = '{table}'\n+            AND m.query = '{desc}';\"\"\"\n         value = self.get_value(max_query)\n         return value\n \n     def extract_second_latest_value(self, table, desc):\n         max_2_query = \\\n-            \"SELECT \\\n-                m.output \\\n-            FROM quasar.monitoring m \\\n-            INNER JOIN \\\n-                (SELECT \\\n-                    max(t.timestamp) AS ts_2 \\\n-                FROM quasar.monitoring t \\\n-                WHERE t.table = '\" + table + \"' \\\n-                AND t.query = '\" + desc + \"' \\\n+            f\"\"\"\n+            SELECT \n+                m.output \n+            FROM etl_monitoring.monitoring m \n+            INNER JOIN \n+                (SELECT \n+                    max(t.timestamp) AS ts_2 \n+                FROM etl_monitoring.monitoring t \n+                WHERE t.table = '{table}' \n+                AND t.query = '{desc}' \n                 AND \\\n-                t.timestamp < (SELECT max(t1.timestamp)  \\\n-                                FROM quasar.monitoring t1 \\\n-                                WHERE t1.table = '\" + table + \"'  \\\n-                                AND t1.query = '\" + desc + \"') \\\n+                t.timestamp < (SELECT max(t1.timestamp)  \n+                                FROM etl_monitoring.monitoring t1 \n+                                WHERE t1.table = '{table}'  \n+                                AND t1.query = '{desc}') \n                 ) ts2 ON ts2.ts_2 = m.timestamp \\\n-            WHERE m.table = '\" + table + \"' AND m.query = '\" + desc + \"';\"\n+            WHERE m.table = '{table}' AND m.query = '{desc}';\"\"\"\n         value = self.get_value(max_2_query)\n         return value\n \n     def compare_distinct(self):\n         query = \\\n-            \"SELECT  \\\n-               m.query,  \\\n-               m.output  \\\n-            FROM quasar.monitoring m   \\\n-            WHERE m.table = 'quasar.users' \\\n-            AND m.timestamp = (SELECT max(t1.timestamp)  \\\n-                               FROM quasar.monitoring t1 \\\n-                               WHERE t1.query = 'user_count') \\\n-            OR m.timestamp = (SELECT max(t1.timestamp)  \\\n-                               FROM quasar.monitoring t1 \\\n-                               WHERE t1.query = 'user_distinct_user_count')\"\n+            \"\"\"\n+            SELECT  \n+               m.query,  \n+               m.output  \n+            FROM etl_monitoring.monitoring m   \n+            WHERE m.table = 'public.users' \n+            AND m.query IN ","path":"quasar/etl_monitoring.py","position":175,"original_position":175,"commit_id":"53c81f908c3125306b8659ee0c619542a23a142a","original_commit_id":"53c81f908c3125306b8659ee0c619542a23a142a","user":{"login":"stickler-ci","id":16011037,"avatar_url":"https://avatars3.githubusercontent.com/u/16011037?v=4","gravatar_id":"","url":"https://api.github.com/users/stickler-ci","html_url":"https://github.com/stickler-ci","followers_url":"https://api.github.com/users/stickler-ci/followers","following_url":"https://api.github.com/users/stickler-ci/following{/other_user}","gists_url":"https://api.github.com/users/stickler-ci/gists{/gist_id}","starred_url":"https://api.github.com/users/stickler-ci/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/stickler-ci/subscriptions","organizations_url":"https://api.github.com/users/stickler-ci/orgs","repos_url":"https://api.github.com/users/stickler-ci/repos","events_url":"https://api.github.com/users/stickler-ci/events{/privacy}","received_events_url":"https://api.github.com/users/stickler-ci/received_events","type":"User","site_admin":false},"body":"W291 trailing whitespace","created_at":"2018-05-24T19:35:03Z","updated_at":"2018-05-24T19:35:05Z","html_url":"https://github.com/DoSomething/quasar/pull/549#discussion_r190706438","pull_request_url":"https://api.github.com/repos/DoSomething/quasar/pulls/549","author_association":"NONE","_links":{"self":{"href":"https://api.github.com/repos/DoSomething/quasar/pulls/comments/190706438"},"html":{"href":"https://github.com/DoSomething/quasar/pull/549#discussion_r190706438"},"pull_request":{"href":"https://api.github.com/repos/DoSomething/quasar/pulls/549"}}},"pull_request":{"url":"https://api.github.com/repos/DoSomething/quasar/pulls/549","id":190397215,"html_url":"https://github.com/DoSomething/quasar/pull/549","diff_url":"https://github.com/DoSomething/quasar/pull/549.diff","patch_url":"https://github.com/DoSomething/quasar/pull/549.patch","issue_url":"https://api.github.com/repos/DoSomething/quasar/issues/549","number":549,"state":"open","locked":false,"title":"Port etl monitoring","user":{"login":"sohaibhasan","id":15786481,"avatar_url":"https://avatars1.githubusercontent.com/u/15786481?v=4","gravatar_id":"","url":"https://api.github.com/users/sohaibhasan","html_url":"https://github.com/sohaibhasan","followers_url":"https://api.github.com/users/sohaibhasan/followers","following_url":"https://api.github.com/users/sohaibhasan/following{/other_user}","gists_url":"https://api.github.com/users/sohaibhasan/gists{/gist_id}","starred_url":"https://api.github.com/users/sohaibhasan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sohaibhasan/subscriptions","organizations_url":"https://api.github.com/users/sohaibhasan/orgs","repos_url":"https://api.github.com/users/sohaibhasan/repos","events_url":"https://api.github.com/users/sohaibhasan/events{/privacy}","received_events_url":"https://api.github.com/users/sohaibhasan/received_events","type":"User","site_admin":false},"body":"#### What's this PR do?\r\n* Ports ETL Monitoring to new DB\r\n* Adds additional tables to monitor\r\n\r\n#### Where should the reviewer start?\r\n* etl_monitoring.py\r\n\r\n#### How should this be manually tested?\r\n\r\n* Run etl_monitoring and see if messages appear in storm watch\r\n\r\n#### Any background context you want to provide?\r\n* Transitioning to the new DB and schema structures allows for much better ETL monitoring. \r\n\r\n#### What are the relevant tickets?\r\nhttps://www.pivotaltracker.com/story/show/156853860\r\nhttps://www.pivotaltracker.com/story/show/155944355\r\n\r\n\r\n","created_at":"2018-05-24T19:34:51Z","updated_at":"2018-05-24T19:35:05Z","closed_at":null,"merged_at":null,"merge_commit_sha":"703be2e392a4471e2a0e3ff2b4dab3cae65f7f7b","assignee":null,"assignees":[],"requested_reviewers":[{"login":"sheyd","id":4562565,"avatar_url":"https://avatars0.githubusercontent.com/u/4562565?v=4","gravatar_id":"","url":"https://api.github.com/users/sheyd","html_url":"https://github.com/sheyd","followers_url":"https://api.github.com/users/sheyd/followers","following_url":"https://api.github.com/users/sheyd/following{/other_user}","gists_url":"https://api.github.com/users/sheyd/gists{/gist_id}","starred_url":"https://api.github.com/users/sheyd/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sheyd/subscriptions","organizations_url":"https://api.github.com/users/sheyd/orgs","repos_url":"https://api.github.com/users/sheyd/repos","events_url":"https://api.github.com/users/sheyd/events{/privacy}","received_events_url":"https://api.github.com/users/sheyd/received_events","type":"User","site_admin":false}],"requested_teams":[],"labels":[],"milestone":null,"commits_url":"https://api.github.com/repos/DoSomething/quasar/pulls/549/commits","review_comments_url":"https://api.github.com/repos/DoSomething/quasar/pulls/549/comments","review_comment_url":"https://api.github.com/repos/DoSomething/quasar/pulls/comments{/number}","comments_url":"https://api.github.com/repos/DoSomething/quasar/issues/549/comments","statuses_url":"https://api.github.com/repos/DoSomething/quasar/statuses/53c81f908c3125306b8659ee0c619542a23a142a","head":{"label":"DoSomething:port_etl_monitoring","ref":"port_etl_monitoring","sha":"53c81f908c3125306b8659ee0c619542a23a142a","user":{"login":"DoSomething","id":731685,"avatar_url":"https://avatars2.githubusercontent.com/u/731685?v=4","gravatar_id":"","url":"https://api.github.com/users/DoSomething","html_url":"https://github.com/DoSomething","followers_url":"https://api.github.com/users/DoSomething/followers","following_url":"https://api.github.com/users/DoSomething/following{/other_user}","gists_url":"https://api.github.com/users/DoSomething/gists{/gist_id}","starred_url":"https://api.github.com/users/DoSomething/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DoSomething/subscriptions","organizations_url":"https://api.github.com/users/DoSomething/orgs","repos_url":"https://api.github.com/users/DoSomething/repos","events_url":"https://api.github.com/users/DoSomething/events{/privacy}","received_events_url":"https://api.github.com/users/DoSomething/received_events","type":"Organization","site_admin":false},"repo":{"id":42329799,"name":"quasar","full_name":"DoSomething/quasar","owner":{"login":"DoSomething","id":731685,"avatar_url":"https://avatars2.githubusercontent.com/u/731685?v=4","gravatar_id":"","url":"https://api.github.com/users/DoSomething","html_url":"https://github.com/DoSomething","followers_url":"https://api.github.com/users/DoSomething/followers","following_url":"https://api.github.com/users/DoSomething/following{/other_user}","gists_url":"https://api.github.com/users/DoSomething/gists{/gist_id}","starred_url":"https://api.github.com/users/DoSomething/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DoSomething/subscriptions","organizations_url":"https://api.github.com/users/DoSomething/orgs","repos_url":"https://api.github.com/users/DoSomething/repos","events_url":"https://api.github.com/users/DoSomething/events{/privacy}","received_events_url":"https://api.github.com/users/DoSomething/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/DoSomething/quasar","description":":cyclone: The DoSomething data platform (ETL, Data Pipeline, Data Warehousing, etc.)","fork":false,"url":"https://api.github.com/repos/DoSomething/quasar","forks_url":"https://api.github.com/repos/DoSomething/quasar/forks","keys_url":"https://api.github.com/repos/DoSomething/quasar/keys{/key_id}","collaborators_url":"https://api.github.com/repos/DoSomething/quasar/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/DoSomething/quasar/teams","hooks_url":"https://api.github.com/repos/DoSomething/quasar/hooks","issue_events_url":"https://api.github.com/repos/DoSomething/quasar/issues/events{/number}","events_url":"https://api.github.com/repos/DoSomething/quasar/events","assignees_url":"https://api.github.com/repos/DoSomething/quasar/assignees{/user}","branches_url":"https://api.github.com/repos/DoSomething/quasar/branches{/branch}","tags_url":"https://api.github.com/repos/DoSomething/quasar/tags","blobs_url":"https://api.github.com/repos/DoSomething/quasar/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/DoSomething/quasar/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/DoSomething/quasar/git/refs{/sha}","trees_url":"https://api.github.com/repos/DoSomething/quasar/git/trees{/sha}","statuses_url":"https://api.github.com/repos/DoSomething/quasar/statuses/{sha}","languages_url":"https://api.github.com/repos/DoSomething/quasar/languages","stargazers_url":"https://api.github.com/repos/DoSomething/quasar/stargazers","contributors_url":"https://api.github.com/repos/DoSomething/quasar/contributors","subscribers_url":"https://api.github.com/repos/DoSomething/quasar/subscribers","subscription_url":"https://api.github.com/repos/DoSomething/quasar/subscription","commits_url":"https://api.github.com/repos/DoSomething/quasar/commits{/sha}","git_commits_url":"https://api.github.com/repos/DoSomething/quasar/git/commits{/sha}","comments_url":"https://api.github.com/repos/DoSomething/quasar/comments{/number}","issue_comment_url":"https://api.github.com/repos/DoSomething/quasar/issues/comments{/number}","contents_url":"https://api.github.com/repos/DoSomething/quasar/contents/{+path}","compare_url":"https://api.github.com/repos/DoSomething/quasar/compare/{base}...{head}","merges_url":"https://api.github.com/repos/DoSomething/quasar/merges","archive_url":"https://api.github.com/repos/DoSomething/quasar/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/DoSomething/quasar/downloads","issues_url":"https://api.github.com/repos/DoSomething/quasar/issues{/number}","pulls_url":"https://api.github.com/repos/DoSomething/quasar/pulls{/number}","milestones_url":"https://api.github.com/repos/DoSomething/quasar/milestones{/number}","notifications_url":"https://api.github.com/repos/DoSomething/quasar/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/DoSomething/quasar/labels{/name}","releases_url":"https://api.github.com/repos/DoSomething/quasar/releases{/id}","deployments_url":"https://api.github.com/repos/DoSomething/quasar/deployments","created_at":"2015-09-11T20:07:42Z","updated_at":"2018-05-24T03:25:08Z","pushed_at":"2018-05-24T19:34:52Z","git_url":"git://github.com/DoSomething/quasar.git","ssh_url":"git@github.com:DoSomething/quasar.git","clone_url":"https://github.com/DoSomething/quasar.git","svn_url":"https://github.com/DoSomething/quasar","homepage":"","size":693,"stargazers_count":3,"watchers_count":3,"language":"Python","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":3,"mirror_url":null,"archived":false,"open_issues_count":1,"license":{"key":"mit","name":"MIT License","spdx_id":"MIT","url":"https://api.github.com/licenses/mit"},"forks":3,"open_issues":1,"watchers":3,"default_branch":"master"}},"base":{"label":"DoSomething:master","ref":"master","sha":"dc5f5822a26895a4f107675f7b459302dcb56731","user":{"login":"DoSomething","id":731685,"avatar_url":"https://avatars2.githubusercontent.com/u/731685?v=4","gravatar_id":"","url":"https://api.github.com/users/DoSomething","html_url":"https://github.com/DoSomething","followers_url":"https://api.github.com/users/DoSomething/followers","following_url":"https://api.github.com/users/DoSomething/following{/other_user}","gists_url":"https://api.github.com/users/DoSomething/gists{/gist_id}","starred_url":"https://api.github.com/users/DoSomething/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DoSomething/subscriptions","organizations_url":"https://api.github.com/users/DoSomething/orgs","repos_url":"https://api.github.com/users/DoSomething/repos","events_url":"https://api.github.com/users/DoSomething/events{/privacy}","received_events_url":"https://api.github.com/users/DoSomething/received_events","type":"Organization","site_admin":false},"repo":{"id":42329799,"name":"quasar","full_name":"DoSomething/quasar","owner":{"login":"DoSomething","id":731685,"avatar_url":"https://avatars2.githubusercontent.com/u/731685?v=4","gravatar_id":"","url":"https://api.github.com/users/DoSomething","html_url":"https://github.com/DoSomething","followers_url":"https://api.github.com/users/DoSomething/followers","following_url":"https://api.github.com/users/DoSomething/following{/other_user}","gists_url":"https://api.github.com/users/DoSomething/gists{/gist_id}","starred_url":"https://api.github.com/users/DoSomething/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DoSomething/subscriptions","organizations_url":"https://api.github.com/users/DoSomething/orgs","repos_url":"https://api.github.com/users/DoSomething/repos","events_url":"https://api.github.com/users/DoSomething/events{/privacy}","received_events_url":"https://api.github.com/users/DoSomething/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/DoSomething/quasar","description":":cyclone: The DoSomething data platform (ETL, Data Pipeline, Data Warehousing, etc.)","fork":false,"url":"https://api.github.com/repos/DoSomething/quasar","forks_url":"https://api.github.com/repos/DoSomething/quasar/forks","keys_url":"https://api.github.com/repos/DoSomething/quasar/keys{/key_id}","collaborators_url":"https://api.github.com/repos/DoSomething/quasar/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/DoSomething/quasar/teams","hooks_url":"https://api.github.com/repos/DoSomething/quasar/hooks","issue_events_url":"https://api.github.com/repos/DoSomething/quasar/issues/events{/number}","events_url":"https://api.github.com/repos/DoSomething/quasar/events","assignees_url":"https://api.github.com/repos/DoSomething/quasar/assignees{/user}","branches_url":"https://api.github.com/repos/DoSomething/quasar/branches{/branch}","tags_url":"https://api.github.com/repos/DoSomething/quasar/tags","blobs_url":"https://api.github.com/repos/DoSomething/quasar/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/DoSomething/quasar/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/DoSomething/quasar/git/refs{/sha}","trees_url":"https://api.github.com/repos/DoSomething/quasar/git/trees{/sha}","statuses_url":"https://api.github.com/repos/DoSomething/quasar/statuses/{sha}","languages_url":"https://api.github.com/repos/DoSomething/quasar/languages","stargazers_url":"https://api.github.com/repos/DoSomething/quasar/stargazers","contributors_url":"https://api.github.com/repos/DoSomething/quasar/contributors","subscribers_url":"https://api.github.com/repos/DoSomething/quasar/subscribers","subscription_url":"https://api.github.com/repos/DoSomething/quasar/subscription","commits_url":"https://api.github.com/repos/DoSomething/quasar/commits{/sha}","git_commits_url":"https://api.github.com/repos/DoSomething/quasar/git/commits{/sha}","comments_url":"https://api.github.com/repos/DoSomething/quasar/comments{/number}","issue_comment_url":"https://api.github.com/repos/DoSomething/quasar/issues/comments{/number}","contents_url":"https://api.github.com/repos/DoSomething/quasar/contents/{+path}","compare_url":"https://api.github.com/repos/DoSomething/quasar/compare/{base}...{head}","merges_url":"https://api.github.com/repos/DoSomething/quasar/merges","archive_url":"https://api.github.com/repos/DoSomething/quasar/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/DoSomething/quasar/downloads","issues_url":"https://api.github.com/repos/DoSomething/quasar/issues{/number}","pulls_url":"https://api.github.com/repos/DoSomething/quasar/pulls{/number}","milestones_url":"https://api.github.com/repos/DoSomething/quasar/milestones{/number}","notifications_url":"https://api.github.com/repos/DoSomething/quasar/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/DoSomething/quasar/labels{/name}","releases_url":"https://api.github.com/repos/DoSomething/quasar/releases{/id}","deployments_url":"https://api.github.com/repos/DoSomething/quasar/deployments","created_at":"2015-09-11T20:07:42Z","updated_at":"2018-05-24T03:25:08Z","pushed_at":"2018-05-24T19:34:52Z","git_url":"git://github.com/DoSomething/quasar.git","ssh_url":"git@github.com:DoSomething/quasar.git","clone_url":"https://github.com/DoSomething/quasar.git","svn_url":"https://github.com/DoSomething/quasar","homepage":"","size":693,"stargazers_count":3,"watchers_count":3,"language":"Python","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":3,"mirror_url":null,"archived":false,"open_issues_count":1,"license":{"key":"mit","name":"MIT License","spdx_id":"MIT","url":"https://api.github.com/licenses/mit"},"forks":3,"open_issues":1,"watchers":3,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/DoSomething/quasar/pulls/549"},"html":{"href":"https://github.com/DoSomething/quasar/pull/549"},"issue":{"href":"https://api.github.com/repos/DoSomething/quasar/issues/549"},"comments":{"href":"https://api.github.com/repos/DoSomething/quasar/issues/549/comments"},"review_comments":{"href":"https://api.github.com/repos/DoSomething/quasar/pulls/549/comments"},"review_comment":{"href":"https://api.github.com/repos/DoSomething/quasar/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/DoSomething/quasar/pulls/549/commits"},"statuses":{"href":"https://api.github.com/repos/DoSomething/quasar/statuses/53c81f908c3125306b8659ee0c619542a23a142a"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"stickler-ci"}}