AWS: RDSSQL (Structured Query Language) is one of the oldest programming languages and has done us well. It was created in 1974 and is more than 49 years old! But, it is rather fixed in its ways and does not cope well with newer data object formats, such as JSON. SQL datatbases use rows and columns to store data, and which are matched to a fixed schema. For a more traditional database, AWS offers the Amazon Relational Database Service (Amazon RDS), Amazon Redshift, and Amazon Aurora. RDS is used for relational databases, and where there are complex transactions or complex queries. They have a medium to high query or write rate, and supported high durability. The database types supported are Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server. Relational v Non-Relational databasesWith a relational database, we have a fixed schema, and where records are stored in rows and columns. We then use SQL to query the records. In a non-relational mode, we can have a dynamic schema and use key-value pairs: Figure 1: Relational (SQL) and Non-relational databases Ref: AWS Academy Cloud Foundations slides Amazon Aurora is an enterprise-class relational database and is compatible with MySQL and PostgreSQL. Overall, it automates key tasks such as provisioning, backup, recovery, and failure detection. Amazon Redshift has applications into Big data applications, including those related to enterprise data warehouses (EDWs). It has a core focus on the data, and less on database management. RDSWe will first create our RDS database, by selecting RDS, and with "Create database": Next we will create a MySQL database: Scroll down and select "Free tier": Next we will give the MySQL database a name of "MyDataBase", and a master account name of "admin", and with a password of "napier123": We will create a 20GB database with SSD storage: We will then enable public access to the database, and create a new VPC security group to open up TCP Port 3306: Then finally to create the database: It will then take a few minutes to create the database: We should then get the details: We then take a note of the connection. In this case it is: mydatabase.cll88eb5nhtz.us-east-1.rds.amazonaws.com and the TCP port is 3306. Install MySQL WorkbenchNow install MySQL Workbench [here]. Connecting to the databaseNow connect to the SQL service using the Hostname provided by AWS:
For each SQL command, click on this button: We can create the database schema with: CREATE DATABASE MyDataBase; Next right-click on the SCHEMAS area on the left-hand side, and refresh. You should now see the MyDataBase schema. Now double-click on it, to select it. Now enter the definition for the table: CREATE TABLE Students ( Username varchar(255), LastName varchar(255), FirstName varchar(255) ); Now we will add some data: INSERT INTO Students (Username, LastName, FirstName) values ('fsmith','Smith',"Frank"); INSERT INTO Students (Username, LastName, FirstName) values ('bsmyth',"Smyth","Bob"); INSERT INTO Students (Username, LastName, FirstName) values ('csmithes','Smithes',"Colin"); Now we will enter: SELECT * FROM Students; Now we can order based on the first name: SELECT * FROM Students ORDER BY FirstName Now we can order based on the first name (in reverse order): SELECT * FROM Students ORDER BY FirstName DESC If we try: SELECT LastName,FirstName FROM Students ORDER BY LastName We get: LastName FirstName ------------------- Smith Frank Smithes Colin Smyth Bob Security of the databaseNote, the security group has opened up the firewall for just one IP address, and which is the address of the host that created the RDS database: The /32 subnet matches all the parts of the IP address. |