Challenge Overview
Here are Topcoder we are in the process of moving off Informix, in order to do this we need to safely and repeateadly be able to load data from our Infromix warehouse into Amazon Redshift but we need your help. We have built a pretty good dockerfile that runs Informix with all the topcoder tables. We also have have a Jar that loads a substainal amount of data. See this repo to set up the Docker container and load the data: https://github.com/kbowerma/tc-docker-informix
We are looking for you help in creating scripts to pull data from Informix to be loaded into Amazon Redshift. In the Docker image above we have installed ‘myexport’ (dbexport without file locking) as well as our data loading tool we call testDataTool.jar. This is a handy tool but it only loads to a abstracted table called tcs_cataloug. These may or may not be useful to you for this challenge however the table and schemas are full loaded so that should come in handy.
In the Forum we have provided css files for topcoder_dw.country and topcoder_dw.calendar which should be pretty easy for you to load and a good warm up exercise. We also will need you to fill tcs_dw.project, tcs_dw.project_results with some dummy data (50-100 rows should be fine). You may find it more useful to select into those two tables from the tcs_catalouge.project table. Either way, the data doesn’t matter it is the process that we care about for this challenge.
At this point you should have 4 tables (see Required table export/import Scripts) with data. What we want is a script or tool to export from these tables and import into Redshift. We don’t really have an Amazon Redshift account for you to use. You can (a) use a trial account or (b) just simulate the import process by formatting the file correctly. We are looking of this tool to be repeatable and extensible such that we can use it for the remaining optional tables. You may reference a config file as well as a transformation file for each of the tables if you like, but your script should be repeatable for each of the files and support any type of exotic transformation we may encounter (think special characters in the titles of challenges)
Requirements:
1. Download the Dockerfile from the repo and run the container. (optional run 'ant run’ and ‘ant bulk-test-data-load’ to load project data into tcs_cataloug.project)
2. Load topcoder_dw.calendar, topcoder_dw.country from the files provided in the forum.
3. Create a script/commandline tool to export these 4 tables and important them into Redshift.
4. Create supporting document that describes the process and the tool you have built. Think : repeatable, extensible, Informix to Redshift.
5. You are free to use any scripting technology you like or even something like Talend, however if you need to install anything on the image please update the Docile so we can repeat it. Your process will be followed by our engineers to move data from our production Informix instances into Redshift for reporting and analytic purposes.
Required table export/import Scripts
topcoder_dw.calendar
topcoder_dw.country
tcs_dw.project
tcs_dw.project_results
Optional table export / import scripts
topcoder_dw.coder
tcs_dw.design_project_results
tcs_dw.project_platform
tcs_dw.project_technology
topcoder_dw.user_payment
tcs_dw.jira_issue
tcs_dw.client_project_dim
tcs_dw.direct_project_dim
tcs_dw.jira_issue
tcs_dw.client_project_dim
tcs_dw.direct_project_dim
Final Submission Guidelines
1. Provide you solution as a single zip file.
2. Provide detailed documentation to support you solution: instalation, process and operation. Remember this will be used us so please be as detailed as possible.
3. A video walk through is desired but not required. (Judges mark Yes on this score card item)