How to Load Data into AWS Aurora
AWS Aurora was made generally available this week. Aurora is an exciting piece of technology—the RDBMS effectively re-engineered from the ground up—providing exceptional performance for high-traffic databases at a comparatively low cost. Read more about it on the AWS blog. Below we’ll focus on how to migrate your existing data into Aurora.
Amazon has some documentation for migrating an existing MySQL running on RDS, but what if you are using another RDS database, or your data is on premises? Fortunately, Aurora has a standard MySQL interface that makes it easy to load data in.
Loading data from an existing MySQL database
You can use the standard mysqldump tool to export data from your existing MySQL, and then use mysqlimport to import the data into Aurora. If you want more power and flexibility, you will need to use an integration tool.
FME not only allows you to migrate the data, but it also gives you complete control over the process. You can clean and validate data, rename and drop fields, and extract a subset of the data—all within a GUI without writing any code. There is a free trial of the full version, so if you only want to do a one-time load into Aurora then you can do so with FME for free.
Loading data from another database such as Oracle, SQL Server or PostgreSQL
To load data from another database into Aurora, you also need an integration tool. FME has support for over 350 formats, including all of the major databases.
Watch the first half of this video to see how to load data from a PostgreSQL database into Aurora.
Loading data from a tabular source such as CSV or Excel
FME has a very powerful set of tools for loading tabular data stored in flat files into databases. Unlike loading a database into Aurora, if you are loading a CSV file, more tidy-up will likely be required.
Watch the second half of the video to see how to load a 900MB CSV file showing all the 311 calls in NYC in 2009. We rename fields and do some simple validation before bulk loading the data into Aurora.
