Building an AWS Trading System - Basic Aurora RDS Operations (Part 5) (Python Tutorial)

Dec 11, 2022

∙ Paid

Title image created with Cacoo.com

This post is part of a series of tutorials with the subject of building a scalable algorithmic trading system on the Amazon Web Services (AWS) cloud platform. Bear with me, as I’m learning AWS myself.

The advantage of using AWS is that it offers dozens of powerful, integrated software services that can be used to build a scalable, high-performance, low-cost production trading system.

In this post we are going to over basic operations on how to create and delete Aurora MySQL database clusters, databases, how to create a table, load pricing information, and query for the pricing data.

This story is solely for general information purposes, and should not be relied upon for trading recommendations or financial advice. Source code and information is provided for educational purposes only, and should not be relied upon to make an investment decision. Please review my full cautionary guidance before continuing.

Previous Tutorials

The following posts are suggested reading for this post to get you started:

Trade Ideas provides AI stock suggestions, AI alerts, scanning, automated trading, real-time stock market data, charting, educational resources, and more. Get a 15% discount with promo code ‘BOTRADING15’.

What is AWS Aurora?

AWS Aurora is Amazon’s scalable, relational cloud database service. A relational database is a database where data is stored in tables and columns with predefined data types and sizes and can be accessed using SQL (Structured Query Language).

Aurora is part of the AWS database service Amazon Relational Database Service (Amazon RDS). Amazon RDS is a web service that for managing relational databases in the cloud. The database service supports auto scaling and auto management.

For automated trading this database can be used to store data such as instrument historical and live price data, market data, backtest results, broker transactions, logging data, analytics data, etc.

AWS has different database Engine types available, the most common being MySQL and PostgreSQL. In this tutorial we will be using MySQL.

Learn more about AWS Aurora here.

What are Database Engines?

Database engines are basically the type of relational database product that is used in combination with the AWS RDS service. Here the list of available engines:

Amazon RDS
RDS for MySQL
RDS for PostgreSQL
RDS for MariaDB
RDS for Oracle
RDS for SQL Server
Amazon RDS Custom.

The database engine needs to be specified when creating a database cluster or instance so AWS knows what type of database to create. In this tutorial we will be using MySQL.

Read more about AWS Database Engines here.

What are Aurora Database Instance Types?

When creating a new database, a database instance type needs to be selected. An database instance type defines the hardware architecture for the processor and memory of the underlying hardware used to run the database.

You can find a complete list of database instance types here.

The question which database instance type to select is not a straight-forward one since it’s a function of your data volume, complexity of your queries, performance requirements, etc. You can use the ‘db.serverless’ instance class to let Amazon RDS manage your computing requirements. For other instance classes, I suggest to start with a small one for cost reasons (unless you know you will have complex data and computing requirements) and then migrate to a more powerful instance class if you notice capacity issues.

Not all instance types are available in all regions. To find out which DB Instance classes are available in your region, you can use the AWS Management Console / Amazon RDS Pricing page, or the describe-orderable-db-instance-options command for the AWS Command Line Interface (AWS CLI).

Note that the DB instance classes available for the free tier are limited. Also, be aware that the available db instance classes are dependent on which database engine you selected.

What are Aurora Database Clusters?

A database cluster is a set of multiple database instances that are replicated, sometimes over multiple regions. The cluster usually consists of a primary database with read and write access and other replicated instances with only read access. Database clusters are created for reasons such as data backup, security, availability and performance.

When using clusters, the cluster is created first. Then, other database instances can be added to the cluster. In the same fashion, the databases first have to be removed from the cluster before the cluster can be deleted.

Although you don’t have to use database clusters, you may want to consider them for in a production environment for the reasons mentioned above. In this tutorial we will be creating a database cluster.

Read more about Aurora database clusters here.

What is Boto3?

AWS functionality can be accessed in different ways, e.g. AWS Console, AWS Command-line Interface (CLI), or different SDKs. In this series of tutorials we will be using the Boto3 library to interact with the different AWS services.

Boto3 is a Python SDK provided by Amazon to interface with services such as EC2, S3, Amazon Athena, etc.

Creating DB Clusters and Instances

There are several ways to create a database instance. You can use the AWS console, which is the easiest way, AWS Command line interface (CLI) or one of the AWS SDKS. In this tutorial we will be using the Python Boto3 SDK.

Creating clusters and instances can be overwhelming due to the number of available database types, database engines, database instance classes and parameters. It helps to remind yourself that you don’t have to use all options and parameters. Just stick with the ones that work for you in the beginning and then use others as you need them.

One tip I can give you when you don’t know what a particular input parameter should be is to create the db cluster or instance using the AWS console first. Then you can describe the cluster or instance using Boto3 to see all the parameters and use them in your code.

Also, note that the list of required input parameters for the Boto3 statement changes based on which Database Engine you selected.

Here the AWS user guide for Amazon RDS.

Here the Boto3 documentation for RDS. Look for ‘create_db_cluster()’ and ‘create_db_instance()’ APIs.

Tutorial Outline

In this tutorial we will go over these basic Aurora RDS operations:

Create an Aurora MySQL database cluster
Wait for cluster to come online
Add a database instance to the cluster
Wait for the instance to come online
List database clusters
List database instances
Get database instance by id
.Create a price table
Show all tables
Describe the price table
Fetch all price records and store them in DataFrame.
Drop the table
Delete the instance
Delete the cluster.

B/O Trading Blog