Posts

Showing posts from April, 2012

OpenAir ETL Project

Image
Image via CrunchBase Reporting in OpenAir has presented our company with some challenges. As such, I was asked to take a look at how we might be able to create an ETL of this data , create our reports and automatically distribute them to our project managers. This is how I did it. First we needed to purchase OpenAir's Automated Backup service. This service will export data in text format or MySQL dumps of any or all tables in their database, zip them up and deposit them anywhere via FTP, FTPS , or SFTP . We set up an FTP server for our repository in the DMZ. Once this bit was done, I needed an installation of MySQL Server and a method to programmatically pull the data from the DMZ, deposit it on a Windows share, unzip and insert the data. Below is a batch file used for this purpose. @ECHO On SETLOCAL ENABLEDELAYEDEXPANSION @SET _LOGFILE="C:\Export\LOGS\OpenAirMysqlImports.txt" @SET _MYSQL="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe&quo