Migrating AppEngine Datastore Entities to a SQL Database: Jenkins Automation

In two previous posts I laid out the process for extracting the entities from the Google AppEngine datastore and then loading them into Azure SQL.  While this process was successful, it was also a bit labor intensive.  I felt pretty certain that the process could be automated, so I set out to script it out and run it from Jenkins.

My initial goal was to complete the entire process, from initiating the datastore backup to pushing to Azure, without modifying the existing app in any way.  So, while the most straight forward way to backup the enties periodically is with a (shocker) cron job, I wanted to try to trigger the backup from outside the app.

Migrating AppEngine Datastore Entities to a SQL Database: Azure Load

In my last post, I went over the process of getting our datastore entities from into a .csv file via BigQuery.  This seemed like a good split point, since getting a .csv or Excel file into an Azure SQL database might be of interest regardless of where the .csv came from.  I'll also quickly go over some Azure gotcha's to look out for.

As a bit of background, initially I was just going to do all the querying from within BigQuery.  But it was a bit clunky, and kind of intimately tied me in to the process of getting the data the supervisors wanted.  My manager pointed out that a more general solution was probably preferable, so I looked into getting the .csv data into a SQL database.  This is the rest of the story...