{"action":"created","issue":{"url":"https://api.github.com/repos/edx/edx-analytics-pipeline/issues/141","labels_url":"https://api.github.com/repos/edx/edx-analytics-pipeline/issues/141/labels{/name}","comments_url":"https://api.github.com/repos/edx/edx-analytics-pipeline/issues/141/comments","events_url":"https://api.github.com/repos/edx/edx-analytics-pipeline/issues/141/events","html_url":"https://github.com/edx/edx-analytics-pipeline/pull/141","id":99321896,"number":141,"title":"User Profiles Table in Analytics MySQL DB","user":{"login":"bradenmacdonald","id":945577,"avatar_url":"https://avatars.githubusercontent.com/u/945577?v=3","gravatar_id":"","url":"https://api.github.com/users/bradenmacdonald","html_url":"https://github.com/bradenmacdonald","followers_url":"https://api.github.com/users/bradenmacdonald/followers","following_url":"https://api.github.com/users/bradenmacdonald/following{/other_user}","gists_url":"https://api.github.com/users/bradenmacdonald/gists{/gist_id}","starred_url":"https://api.github.com/users/bradenmacdonald/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bradenmacdonald/subscriptions","organizations_url":"https://api.github.com/users/bradenmacdonald/orgs","repos_url":"https://api.github.com/users/bradenmacdonald/repos","events_url":"https://api.github.com/users/bradenmacdonald/events{/privacy}","received_events_url":"https://api.github.com/users/bradenmacdonald/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/edx/edx-analytics-pipeline/labels/awaiting%20prioritization","name":"awaiting prioritization","color":"fad8c7"},{"url":"https://api.github.com/repos/edx/edx-analytics-pipeline/labels/open-source-contribution","name":"open-source-contribution","color":"bfe5bf"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":10,"created_at":"2015-08-05T23:38:17Z","updated_at":"2015-08-19T20:32:53Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/edx/edx-analytics-pipeline/pulls/141","html_url":"https://github.com/edx/edx-analytics-pipeline/pull/141","diff_url":"https://github.com/edx/edx-analytics-pipeline/pull/141.diff","patch_url":"https://github.com/edx/edx-analytics-pipeline/pull/141.patch"},"body":"In order to support user-specific reports in the Insights dashboard, this PR takes the first step by making user data available within the MySQL DB.\r\n\r\nTwo [related](https://github.com/edx/edx-analytics-data-api/pull/80) [PRs](https://github.com/edx/edx-analytics-data-api-client/pull/21) update the data API to allow analytics software to access the data, and [a final PR for the Dashboard](https://github.com/edx/edx-analytics-dashboard/pull/338) displays it to the user.\r\n\r\n**Changes**:\r\n* Add user's \"name\" to the existing task that imports data from the LMS `auth_userprofile` table to Hive.\r\n* Adds two new pipeline tasks:\r\n  * `InsertToMysqlUserProfilesTask` which creates the new `user_profile` table in the analytics MySQL DB. This new table contains basic user information (ID, name, email, username, gender, age, education level, login date) extracted and joined from the existing `auth_user` and `auth_userprofile` Hive tables.\r\n  * `EnrollmentSnapshotTask` which creates/updates a new MySQL table (`course_enrollment_snapshot`) to contain the most current enrollment information available.\r\n\r\nThese changes together allow us to create new Data API methods that can (a) get the profiles of all known users or (b) get the profiles of all users in a specific course.\r\n\r\n**Partner Information**: Third-party open edX instance, not an edX partner.\r\n\r\n**Test Instructions - OpenCraft devstack**:\r\nOne way to test is with the analytics devstack:\r\n\r\n1. Setup an [analytics devstack](https://github.com/open-craft/edx-analytics-devstack). \r\n2. Modify your local LMS devstack to work with the new analytics devstack (See analytics devstack README).\r\n3. Login to the analytics devstack as the `analytics` user.\r\n4. Run the command:\r\n   ```\r\n   launch-task InsertToMysqlUserProfilesTask --local-scheduler\r\n   ```\r\n5. Wait for it to complete. To check if it worked, run `mysql -u root analytics --execute=\"SELECT * FROM user_profile;\"`. You should get a result set showing all the users on your LMS devstack.\r\n\r\n**Test Instructions - Combined devstack**:\r\nAnother way is the nascent combined devstack:\r\n\r\n1. Setup a [combined devstack](https://gist.github.com/bradenmacdonald/8b3b9736bf2b5ec44bc0)\r\n2. Clear out any existing pipeline repo: `vagrant ssh` then `sudo rm -r /var/lib/analytics-tasks/devstack/repo` (This is necessary since the ansible scripts won't force the use of a different upstream repo during the next step)\r\n3. On your host, in the edx-analytics-pipeline folder (with the venv loaded), run this command:\r\n    \r\n    ```\r\n    remote-task --vagrant-path ../ --remote-name devstack --override-config ${PWD}/config/devstack.cfg --repo https://github.com/open-craft/edx-analytics-pipeline.git --branch user-table-mysql --wait InsertToMysqlUserProfilesTask --local-scheduler\r\n    ```\r\n4. Wait for it to complete, then inside the devstack, run `mysql -u root reports --execute=\"SELECT * FROM user_profile;\"` You should get a result set showing all the users on your LMS devstack.\r\n5. Now, try the other new task, `EnrollmentSnapshotTask`, with this command:\r\n    \r\n    ```\r\n    remote-task --vagrant-path ../ --remote-name devstack --override-config ${PWD}/config/devstack.cfg --repo https://github.com/open-craft/edx-analytics-pipeline.git --branch user-table-mysql --wait ImportEnrollmentsIntoMysql --local-scheduler --interval-end $(date -v+1d +%Y-%m-%d) --n-reduce-tasks 1\r\n    ```\r\n    (If the `date` subcommand isn't working, try `date +%Y-%m-%d -d \"tomorrow\"` instead)\r\n6. Then, to check that it worked, run `mysql -u root reports --execute=\"SELECT * FROM course_enrollment_snapshot;\"` as any user within the devstack.\r\n\r\n**Author Notes/questions**\r\n* Does it make sense for this data to be imported this way and stored in the analytics MySQL DB?\r\n* Is it necessary to add a migration or something to account for a new column being added to the auth_userprofile Hive table?\r\n* How do I write tests for this?"},"comment":{"url":"https://api.github.com/repos/edx/edx-analytics-pipeline/issues/comments/132773064","html_url":"https://github.com/edx/edx-analytics-pipeline/pull/141#issuecomment-132773064","issue_url":"https://api.github.com/repos/edx/edx-analytics-pipeline/issues/141","id":132773064,"user":{"login":"mulby","id":627177,"avatar_url":"https://avatars.githubusercontent.com/u/627177?v=3","gravatar_id":"","url":"https://api.github.com/users/mulby","html_url":"https://github.com/mulby","followers_url":"https://api.github.com/users/mulby/followers","following_url":"https://api.github.com/users/mulby/following{/other_user}","gists_url":"https://api.github.com/users/mulby/gists{/gist_id}","starred_url":"https://api.github.com/users/mulby/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mulby/subscriptions","organizations_url":"https://api.github.com/users/mulby/orgs","repos_url":"https://api.github.com/users/mulby/repos","events_url":"https://api.github.com/users/mulby/events{/privacy}","received_events_url":"https://api.github.com/users/mulby/received_events","type":"User","site_admin":false},"created_at":"2015-08-19T20:32:53Z","updated_at":"2015-08-19T20:32:53Z","body":"Yeah I was thinking about that as well. We will likely be tackling the story to refactor the tests for local execution in the next couple of weeks.\r\n\r\nI expect this test is so similar to the others that it may be fine to just write it blindly and I can help tweak it (if necessary) by running it on my end. You also may be able to hack the acceptance tests so that they work for you without actually implementing the \"correct\" method for running locally."}}