6.1 Databases 101

AWS Database Services

• RDS (Relational database)

• DynamoDB (NoSQL database)

• ElastiCache (In-memory database)

• Redshift (Data warehouse)

• DMS (Database migration service)

Relational database

Relational databases are what most of us are all used to. They have been around since the 70's. Think of a traditional spreadsheet.

• Database

• Tables

• Row

• Fields (Columns)

Six databases available on AWS RDS (Relational Database Service)

• SQL Server

• Oracle

• MySQL Server

• PostgreSQL

• Aurora

• MariaDB

Non-relational databases

DynamoDB database:

• Collection = Table

• Document (like object data type in JavaScript) = Row

• Key-value pairs (value can be any data type) = Fields

DynamoDB is document-oriented (like MongoDB) and it also supports key-value data model. Document database is essentially a key-value database with more features.

Data warehouse

Used for business intelligence. Tools like Cognos, Jaspersoft, SQL Server Reporting Services, Oracle Hyperion, SAP NetWeaver.

Used to pull in very large and complex data sets. Usually used by management to do queries on data (such as current performance vs targets etc).

OLTP vs. OLAP

OLTP - Relational database.

OLAP - Data warehouse.

Online Transaction Processing (OLTP) differs from Online Analytics Processing (OLAP) in terms of the types of queries run.

#1. OLTP Example:

Order number 2120121

Pulls up a row of data such as Name, Date, Address to Deliver to, Delivery Status etc.

#2. OLAP transection Example:

Net Profit for EMEA and Pacific for the Digital Radio Product.

Pulls in large numbers of records: ...

You can imagine the amount of data that's going to be involved in this sort of transaction. It will be huge and it is going to hit the database really hard with lots of different queries and it is going to have a big effect on the database. Now with relational databases traditionally they're very difficult to scale out. You need to have a production database and if you are running these queries on your production database and also trying to do online transaction processing which allows your customers to pay new orders, it is going to hurt the database and it is going to really effect the IO. So data warehouse has come into existence where you more or less have a copy of your production database and this copy of the database is where you are going to run your OLAP transactions on.

Data warehousing databases use different type of architecture both from a database perspective and infrastructure layer.

In-memory database/cache

ElastiCache is a web service that makes it easy to deploy, operate, and scale an in-memory cache in the cloud. The service improves the performance of web applications by allowing you to retrieve information from fast, managed, in-memory caches, instead of relying entirely on slower dist-based databases. ElastiCache supports two open-source in-memory caching engines:

• Memcached

• Redis

ElastiCache is cool because it can cache the most consistently queried aspects of your database. So if your web application is constantly requesting what the top 10 deals for today you might want to cache that information in an ElastiCache and in that way when your web application handle the query for the top 10 deals it is going to get from ElastiCache and it is not going to get that from you database. It just takes a load off your database and allows you to improve the performance of your underlining database.

DMS (Database Migration Service)

DMS allows you to migrate your production database to AWS. Once the migration has started, AWS manages all the complexities of the migration process like data type transformation, compression, and parallel transfer (for faster data transfer) while ensuring that data changes to the source database that occur during the migration process are automatically replicated to the target.

AWS schema conversion tool automatically converts the source database schema and a majority of the custom code, including views, stored procedures, and functions, to a format compatible with the target database.