Using AWS Athena in Anger

The journey down this particular rabbit hole started with this challenge "Create Read-replicas for MSSQL 2017 database hosted in RDS". Hosting services on AWS typically reduces IT load and makes focus on product development easier, however there are certain edge cases where AWS falls down and requires special intervention.

The above challenge turned out to be one of them. RDS supports seamless read-replicas on a lot of other types of databases , however MSSQL is not one of them. The recommended approach for creating read-replicas for MSSQL is to enable CDC and do so using DMS (Database Migration Service). This is similar to what the RDS does internally for multi-AZ redundancy. We hit another edge-case here, DMS replication support with CDC does not extend to the 2017 version.

We did manage to run DMS successfully from MSSQL 2017 to S3 as sort of a disaster recovery measure. Then I was stuck with the conundrum of what to do with the CSV table dumps this got me, at the AWS Summit in Cape Town one of the solution specialists introduced me to Athena and the adventure began.

Athena is essentially a managed Apache Hive based map-reduce setup which can churn through webserver logs or in this case CSV's to give me back the database I was replicating. The replica is not perfect, it is missing stored procedures and views. These need to be redone in HiveQL.

All tables can be mapped using corresponding schema and DDL to Athena tables, thus replicating fully the database without foreign keys and other niceties. AWS now has cross region S3 replication , this coupled with Athena can give a very Rube-Goldberg cross-region database replication.

Athena is also particularly useful with scrapers where data is being collected from 3rd-party sites and consolidated into S3 tables. It can seamlessly blast through millions of rows of scraped data for goodies in seconds.

This post is a WIP and will be enriched with Athena goodies over time.

AWS Summit Sydney 2016

Any web application that targets a mass market and wants scalability ends up in the cloud, rather than in an individual data centre. Of all the cloud providers the biggest currently is Amazon. After running the metromap application with AWS for a year mostly on a self-taught basis I realized that it was time to rub shoulders with some experts and possibly bring in some help.

Amazon was hosting a sort of marketing conference in Sydney and unlike the academic conferences I am used to attending this one was free. So I signed up for it with some work backing.

On arrival I walked into a massive Kogan keynote speech, it had all the pomp and fanfare of a boxing match or WWF rumble. Felt very American.

There were lots of AWS customer stories including one by Origin Energy about customer analysis. They have over 100million customer interactions. Traditional meters are read only 4 times a year, while Smart Meters produce data at a 30min interval. With this data Origin can offer a fixed bill plan using predictive analytics. The creation of this service started in stealth mode hotel wifi and personal CC, interesting collision of corporate culture with internet realities.

Moving data to and from the cloud was big sticking point as well and the AWS Snowball (80TB) was announced. I have recently ordered one, still waiting for it to turn up. Will write another article on this Data Exchange unit once I have had a real life play with it. Working at an aerial photography company I am used to huge amounts of data coming in from aircraft on similar hard drive units, but none of them feature 10GB ethernet and in transit encryption like the Snowball does.

There was a big focus on IoT capabilities in AWS as well including an Earth, Air, Fire and Water demo with Alexa playing stage manager.

I had a small blast from my CSIRO past. Peter Blaine had a talk about IMOS data sharing. 50Million NetCDF's are up in the cloud. The big challenge being the heterogeneity of the data. Apparently they have moved to the warehouse next to my old office and things are progressing okay. Even though CSIRO is rapidly shedding science staff, the engineering half is still alive and well.

On the display floor I managed to knock over a remote presence bot and chat to a lot of people in various booths, including Sumo Logic and Puppet which were particularly relevant to my application of image serving and service monitoring.

 

After a couple of days of hard conferencing and networking I managed to get in some R&R and learn the weird game of shuffleboard and enjoy some cocktails at the Little Darlin'. It was an exciting couple of days in Sydney and since then I have been putting my knowledge to good use. I will write up another post from my mass of notes collected during the summit.

OTB-GPU and Amazon AWS (Cuda Enabled) - Cloud Processing

A while ago OTB did some experiments running image processing code on GPU's. It has not made it to mainline yet since we are waiting on ITK to add more structured and pervasive support for GPU's in their infrastructure. I though it would be a nice bit of code to test the not-so-new but still shiny Amazon AWS Cuda support.

The preconfigured instance with Nvidia CUDA toolkit installed runs CentOS 5. You will need additional repositories to grab goodies like cmake and mercurial to get going, from RPMForge. You will need lots and lots of version controls e.g. subversion and mercurial, even compilers. I should have started from the GIS AMI.Getting GDAL installed as a dependency can be slightly tricky, the CentOS packages from ELGIS did not work form me, lot's of missing dependent libraries. Best bet is to install from source. Then use a small hack to copy over some headers and build OTB.

Build servers like Hudson can easily make use of such an image on AWS once configured with the right version control, configuration and build tools. I will have to test drive the Hudson CMake support with this instance. Otherwise I have been playing puppet master at home with Virtual Box and real hardware. I got the swarm plugin to register most my available platform to hudson as a build slaves, including the BeagleBoard. AWS can be accessed via a similar cloud/cluster plugin. I found the VirtualBox plugin rather cryptic, I think I will have to use the source for that one - it can be very useful for multi-platform installer and GUI testing, even recording instruction videos by playing through a UI test suite. Especially when using bootstrappers, errors are not detected at compile time. They only become apparent when the program is run on a clean system. Having a set of clean virtual machine snapshots makes it much easier to track down the error before it is released into the client base.