Challenge Overview
Requirements Details
Load missing columns into topcoder_dw:coder
- The table topcoder_dw:coder has the following columns not have any date loaded (the added columns data exist in OLTP database in tables common_oltp:user, common_oltp:phone TBD )
- home_phone
- work_phone
- modify_date
- last_login
- image
- handle_lower
- Update https://coder.topcoder.com/internal/shared/trunk/src/main/com/topcoder/shared/util/dwload/TCLoadCoders.java to load the missing in the list columns above.
Load added data into Google Big Query
For the Big Query part
- Update userSchema.json to add the columns metion above.
- Update UserDataExtractor.java to extract these columns into json data and load into the Big Query Project.
- Test the loading of extracted user data with these new columns into the Test Big Query Project. For Test Big Query Project, refer to #Google Big Query Access
Google Big Query requires setting up billing to be able to use the API, so we created a test project (PROJECT_ID: tc-data-accessibility-30044673) under our account. You need to request acess in challenge forum.
VM Environment
You need a TopCoder VM to work on this challenge, you will mainly use the informix database on VM and if you choose to run data loader to load the warehouse data, you will need to run the loaders under account 'tc'. Please request the VM in the challenge forum.
Information about VM can be found below:
VM specific information is found here: http://www.topcoder.com/wiki/display/docs/VM+Image+2.5. Upon registration as a submitter or reviewer you will need to request a VM based on the new TopCoder Cockpit/Direct image. To request your image, please use the forum. Before requesting your VM, you need to ensure that you have an SSH key created and in your member profile. Instructions to do so are here: http://www.topcoder.com/wiki/display/projects/Generate+SSH+Key, and instructions to connect afterwards are here: http://www.topcoder.com/wiki/display/projects/Connect+Using+SSH+Key.
Testing
- The dataware house databases on the TopCoder VM assigned to you will not have any data, so please
- Prepare the test data for dataware house load. After inserting the test data, run TCLoadCoders on the VM to load oltp data into DW.
- After data has been loaded into DW, run the user extractor to extract the data and load into the Google Big Query project.
Repository
The latest source codes can be found:
- https://coder.topcoder.com/internal/shared/trunk/src/main/com/topcoder/shared/util/dwload/TCLoadCoders.java
- https://github.com/cloudspokes/tc-reporting (commit : aa61c9b9350abe798ef3e5c2f5e97a3da463435d)
Final Submission Guidelines
- The newly added / updated files.
- A detailed deployment guide with verification steps and the screenshots of your verification.