{"action":"created","comment":{"url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/comments/77945752","id":77945752,"diff_hunk":"@@ -84,18 +130,81 @@\n     CREDENTIALS 'aws_access_key_id={aws_access_key_id};aws_secret_access_key={aws_secret_access_key}'\n     FORMAT AS CSV;\n \"\"\"\n-\n-Q_CHECK_FOR_DAY = \"\"\"\n-    SELECT timestamp FROM flow_events\n-    WHERE timestamp >= {timestamp}\n-    AND timestamp < {timestamp} + 86400\n-    LIMIT 1;\n+Q_INSERT_METADATA = \"\"\"\n+    WITH durations AS (\n+      SELECT flowId, MAX(flowTime)\n+      FROM flow_csv\n+      GROUP BY flowId\n+    )\n+    INSERT INTO flow_metadata (\n+      flowId,\n+      beginTime,\n+      duration,\n+      completed,\n+      newAccount,\n+      browser,\n+      browserVersion,\n+      os,\n+      context,\n+      entrypoint,\n+      migration,\n+      service,\n+      utmCampaign,\n+      utmContents,\n+      utmMedium,\n+      utmSource,\n+      utmTerm\n+    )\n+    SELECT (\n+      begin.flowId,\n+      (begin.timestamp * 1000.0),\n+      (durations.flowTime * 1000.0),\n+      (CASE WHEN signed.flowId IS NULL THEN FALSE ELSE TRUE END),\n+      (CASE WHEN created.flowId IS NULL THEN FALSE ELSE TRUE END),\n+      begin.browser,\n+      begin.browserVersion,\n+      begin.os,\n+      begin.context,\n+      begin.entrypoint,\n+      begin.migration,\n+      begin.service,\n+      begin.utmCampaign,\n+      begin.utmContents,\n+      begin.utmMedium,\n+      begin.utmSource,\n+      begin.utmTerm\n+    )\n+    FROM flow_csv AS begin\n+    INNER JOIN durations\n+      ON begin.flowId = durations.flowId AND begin.type = 'flow.begin'\n+    LEFT JOIN flow_csv AS created\n+      ON begin.flowId = created.flowId AND created.type = 'account.created'\n+    LEFT JOIN flow_csv AS signed\n+      ON begin.flowId = signed.flowId AND signed.type = 'account.signed';","path":"import_flow_events.py","position":165,"original_position":165,"commit_id":"250593779a7f34eb04d94249bae28e59cb8db938","original_commit_id":"5b4bac8e885a1530a19e5c0af49c2cdfa1256a5b","user":{"login":"rfk","id":34695,"avatar_url":"https://avatars.githubusercontent.com/u/34695?v=3","gravatar_id":"","url":"https://api.github.com/users/rfk","html_url":"https://github.com/rfk","followers_url":"https://api.github.com/users/rfk/followers","following_url":"https://api.github.com/users/rfk/following{/other_user}","gists_url":"https://api.github.com/users/rfk/gists{/gist_id}","starred_url":"https://api.github.com/users/rfk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rfk/subscriptions","organizations_url":"https://api.github.com/users/rfk/orgs","repos_url":"https://api.github.com/users/rfk/repos","events_url":"https://api.github.com/users/rfk/events{/privacy}","received_events_url":"https://api.github.com/users/rfk/received_events","type":"User","site_admin":false},"body":"If the joins here get too unwieldy, another option could be to update the table piecemeal, along the lines of:\r\n\r\n1. Insert a new row for each `flow.begin` event in the import table, defaulting to existing-account, incomplete, etc.\r\n2. Do an update to set the duration for each flowid in the import table.\r\n3. Do an update to set `newAccount=1` for all `account.created` events in the import table.\r\n4. Do an update to set `completed=1` for all `account.signed` events in the import table.\r\n\r\nThis might make it easier to handle flows that cross the boundary between days, e.g. if the `flow.begin` event is at 11:59 on the 1st of May, and the `account.signed` event is at 12:03 on the 2nd of May.  It would start off being an incomplete flow, but get updated to its correct status when the next day's data is processed.\r\n\r\nOn the downside, it would leave more room for something going wrong between each of the steps.","created_at":"2016-09-08T04:54:33Z","updated_at":"2016-09-08T04:54:33Z","html_url":"https://github.com/mozilla/fxa-activity-metrics/pull/2#discussion_r77945752","pull_request_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2","_links":{"self":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/comments/77945752"},"html":{"href":"https://github.com/mozilla/fxa-activity-metrics/pull/2#discussion_r77945752"},"pull_request":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2"}}},"pull_request":{"url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2","id":84350576,"html_url":"https://github.com/mozilla/fxa-activity-metrics/pull/2","diff_url":"https://github.com/mozilla/fxa-activity-metrics/pull/2.diff","patch_url":"https://github.com/mozilla/fxa-activity-metrics/pull/2.patch","issue_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/2","number":2,"state":"open","locked":false,"title":"WIP: Update redshift import scripts","user":{"login":"philbooth","id":64367,"avatar_url":"https://avatars.githubusercontent.com/u/64367?v=3","gravatar_id":"","url":"https://api.github.com/users/philbooth","html_url":"https://github.com/philbooth","followers_url":"https://api.github.com/users/philbooth/followers","following_url":"https://api.github.com/users/philbooth/following{/other_user}","gists_url":"https://api.github.com/users/philbooth/gists{/gist_id}","starred_url":"https://api.github.com/users/philbooth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/philbooth/subscriptions","organizations_url":"https://api.github.com/users/philbooth/orgs","repos_url":"https://api.github.com/users/philbooth/repos","events_url":"https://api.github.com/users/philbooth/events{/privacy}","received_events_url":"https://api.github.com/users/philbooth/received_events","type":"User","site_admin":false},"body":"@rfk, these are my import script changes finished off and rebased against latest master.\r\n\r\nI've literally only just finished them, so I'm sure there's still heaps of syntax and logical errors. But I wanted to take advantage of the time difference to get early feedback on a few things, if you get the time to eyeball it.\r\n\r\nI'll call out the items of interest inline (go alliteration!), but to summarise:\r\n\r\n* `import_activity_events.py` is updated with `deviceId`.\r\n* References to `week` in `import_activity_events.py` have been updated to `day`.\r\n* `import_flow_events.py` splits the data into `flow_metadata` and `flow_events`.\r\n* The flow tables use TIMESTAMP and INTERVAL rather than BIGINT (I think this makes queries and graphs nicer).\r\n* It uses a temporary `flow_csv` table for importing the raw data from CSV file (conversions happen on subsequent `INSERT`).\r\n\r\nMore detail inline.","created_at":"2016-09-07T18:30:58Z","updated_at":"2016-09-08T04:54:33Z","closed_at":null,"merged_at":null,"merge_commit_sha":"2ef4deb0924db7f4fc8b89b4e043e74cc6a4e0aa","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2/commits","review_comments_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2/comments","review_comment_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/comments{/number}","comments_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/2/comments","statuses_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/statuses/250593779a7f34eb04d94249bae28e59cb8db938","head":{"label":"mozilla:phil/import-new-events","ref":"phil/import-new-events","sha":"250593779a7f34eb04d94249bae28e59cb8db938","user":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":62026178,"name":"fxa-activity-metrics","full_name":"mozilla/fxa-activity-metrics","owner":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/fxa-activity-metrics","description":"A server for managing the Firefox Accounts metrics database and pipeline","fork":false,"url":"https://api.github.com/repos/mozilla/fxa-activity-metrics","forks_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/forks","keys_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/teams","hooks_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/hooks","issue_events_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/events","assignees_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/tags","blobs_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/languages","stargazers_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/stargazers","contributors_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/contributors","subscribers_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/subscribers","subscription_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/subscription","commits_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/merges","archive_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/downloads","issues_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/releases{/id}","deployments_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/deployments","created_at":"2016-06-27T04:47:02Z","updated_at":"2016-07-08T00:09:48Z","pushed_at":"2016-09-07T20:27:48Z","git_url":"git://github.com/mozilla/fxa-activity-metrics.git","ssh_url":"git@github.com:mozilla/fxa-activity-metrics.git","clone_url":"https://github.com/mozilla/fxa-activity-metrics.git","svn_url":"https://github.com/mozilla/fxa-activity-metrics","homepage":null,"size":12,"stargazers_count":1,"watchers_count":1,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":1,"default_branch":"master"}},"base":{"label":"mozilla:master","ref":"master","sha":"08625bae3dd9dca50ec2364b8412c2caf54d32c5","user":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":62026178,"name":"fxa-activity-metrics","full_name":"mozilla/fxa-activity-metrics","owner":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/fxa-activity-metrics","description":"A server for managing the Firefox Accounts metrics database and pipeline","fork":false,"url":"https://api.github.com/repos/mozilla/fxa-activity-metrics","forks_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/forks","keys_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/teams","hooks_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/hooks","issue_events_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/events","assignees_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/tags","blobs_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/languages","stargazers_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/stargazers","contributors_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/contributors","subscribers_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/subscribers","subscription_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/subscription","commits_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/merges","archive_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/downloads","issues_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/releases{/id}","deployments_url":"https://api.github.com/repos/mozilla/fxa-activity-metrics/deployments","created_at":"2016-06-27T04:47:02Z","updated_at":"2016-07-08T00:09:48Z","pushed_at":"2016-09-07T20:27:48Z","git_url":"git://github.com/mozilla/fxa-activity-metrics.git","ssh_url":"git@github.com:mozilla/fxa-activity-metrics.git","clone_url":"https://github.com/mozilla/fxa-activity-metrics.git","svn_url":"https://github.com/mozilla/fxa-activity-metrics","homepage":null,"size":12,"stargazers_count":1,"watchers_count":1,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2"},"html":{"href":"https://github.com/mozilla/fxa-activity-metrics/pull/2"},"issue":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/2"},"comments":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/issues/2/comments"},"review_comments":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2/comments"},"review_comment":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/pulls/2/commits"},"statuses":{"href":"https://api.github.com/repos/mozilla/fxa-activity-metrics/statuses/250593779a7f34eb04d94249bae28e59cb8db938"}}}}
{"actor":{"display_login":"rfk"}}