DEV Community: Msaghu

Python for Data Analysis Cheatsheet

Msaghu — Sat, 10 May 2025 10:51:58 +0000

Pandas is a panda package for data manipulation and visualization. Pandas is built ontop of Numpy and Matlplotlib(data visualization). In pandas, data analysis is done on rectangular data which is represented as dataframes, in SQL they are referred to as tables.
We will be using the data from a csv of 'russian alcohol consumption'.

Exploring a Dataframe using the table

Returns first few rows of the dataframe

print(alcohol_df.head())

To get the names of columns, and their data types and if they contain missing values

print(alcohol_df.info())

To get the number of rows and columns of the datframe displayed as a tuple. This is an attribute, not a method.

print(alcohol_df.shape)

To get the summary statistics of the dataframe;

print(alcohol_df.describe())

To get the values in a 2D NumPy array

print(alcohol_df.values)

To get the column names

print(alcohol_df.columns)

To get row numbers/row names

print(alcohol_df.index)

1. Dataframes

Change order of the rows by sorting them using a particular column. This automatically sorts be ascending order, from smallest to the largest.

sorted_drinks = alcohol_df.sort_values("region")
print(sorted_drinks)

To sort by descending order

sorted_drinks = alcohol_df.sort_values("region", ascending=False)
print(sorted_drinks)

Sort my multiple variables by passing a list to the method

sorted_drinks = alcohol_df.sort_values(["region", "year"], ascending=[True, False])
print(sorted_drinks)

Subset columns to only see the data in a particular column

print(alcohol_df["region"])

To subset multiple columns to only see the data in the particular columns

print(alcohol_df[["region", "year"]])
or 
column_info = ["region", "year"]
print(alcohol_df[column_info])

Subset rows to only see the data in a particular column. It displays the answer in boolean True or False.;

print(alcohol_df["year"]>1999)

To get the whole dataframe displayed with only the year above 2000;

print(alcohol_df[alcohol_df["year"]>"1999"])

or
only where the region is "Chuvash Republic":

print(alcohol_df[alcohol_df["region"]=="Chuvash Republic"])

Combining these conditions to get multiple insights at once;

c_republic = alcohol_df["region"] == "Chuvash Republic"
wine_percent = alcohol_df["wine"] > 2.0
print(alcohol_df[c_republic & wine_percent])

Subset using .isin()

is_chuvash_or_yaroslavl = alcohol_df["region"].isin(["Chuvash Republic","Yaroslavl Oblast"])
print(alcohol_df[is_chuvash_or_yaroslavl])

Adding new column

alcohol_df["wine_m"] = alcohol_df["wine"] / 100
print(alcohol_df.head())

2. Aggregating Data

Calculate column mean

print(alcohol_df["wine"].mean())

We use the same method to calculate .median(), .mode(), .min(), .max(), .var(), .std(), .sum()

To get the earliest year

print(alcohol_df["year"].min())

To get the latest year

print(alcohol_df["year"].max())

Use the .agg() method to compute custom summary statistics. To get the 30th percentile of the wines column

def pct30(column):
    return column.quantile(0.3)
print(alcohol_df["wine"].agg(pct30))

To get the 30th percentile of multiple columns

def pct30(column):
    return column.quantile(0.3)
print(alcohol_df[["wine", "beer", "vodka"]].agg(pct30))

def pct40(column):
    return column.quantile(0.4)
print(alcohol_df["wine"].agg([pct30, pct40]))

Cumulative statistics

Calculating the cumulative sum of a column and return an entire column of the DataFrame
.cummax(), .cummin(), .cumprod()

Remove repeat values

print(alcohol_df.drop_duplicates(subset="region"))

unique = alcohol_df.drop_duplicates(subset=["year","region"])
print(unique)

print(unique["region"].value_counts())
print(unique["region"].value_counts(sort=True))

To turn the counts into proportions of the total

print(unique["region"].value_counts(normalize=True))

Summary statistics

print(alcohol_df.groupby(["year", "region"])["wine"].mean())
print(alcohol_df.groupby("year")["wine"].mean())

print(alcohol_df.groupby("year")["wine"].agg([min, max, sum]))

Pivot Tables

Groups elements together where, values
arguments is the column we want to summarize and the index is the column we want to group by. The pivot will take the mean value of each group

print(alcohol_df.pivot_table(values="wine", index="year"))

Also done as:

import numpy as np
year_percent = alcohol_df.pivot_table(values="wine", index="year", aggfunc=median)
print(year_percent)

import numpy as np
year_percent = alcohol_df.pivot_table(values="wine", index="year", aggfunc=[np.median, np.mean])
print(year_percent)

To group by 2 variables

year_percent = alcohol_df.pivot_table(values="wine", index="year", columns="week")
print(year_percent)

To fill empty values

year_percent = alcohol_df.pivot_table(values="wine", index="year", columns="week", fill_value=0, margins=True)
print(year_percent)

3. Slicing and Indexing Data

Setting a column as the index

alcohol_ind = alcohol_df.set_index("year")
print(alcohol_ind)

Removing an index

print(alcohol_ind.reset_index())

We can drop the index, therefore fully removing it from our dataframe

print(alcohol_ind.reset_index(drop=True))

Subset on index values using loc

print(alcohol_ind.loc[[2000,2003]])

Multi-level indexes

alcohol_ind3 = alcohol_df.set_index(["region", "year"]).sort_index()
print(alcohol_ind3.loc[["Amur Oblast"]])

alcohol_ind4 = alcohol_df.set_index(["region", "year"])
print(alcohol_ind4.loc[["Amur Oblast", "Sakha (Yakutia) Republic"]])

Subsetting with a list of tuples

alcohol_ind4 = alcohol_df.set_index(["region", "year"])
print(alcohol_ind4.loc[[("Amur Oblast", 2000), ("Sakha (Yakutia) Republic", 2000)]])

Controlling the sort index

print(alcohol_ind4 = alcohol_df.sort_index(level=["region", "year"], ascending=[True, False]))

Slicing and subsetting with .loc and .iloc
This works on only outer index levels, if we want to index inner levels, we need to pass tuples
Slicing the outer index level, but first we sort the index

alcohol_srt = alcohol_df.set_index(["year"]).sort_index()
print(alcohol_srt.loc[2000:2005])

Slicing columns

alcohol_srt = alcohol_df.set_index(["year"]).sort_index()
print(alcohol_srt.loc[2000:2005])
print(alcohol_srt.loc[:, "region":"wine"])

Slicing on rows and columns at the same time

#Wine consumption for the year 2000 for "Amur Oblast", "Astrakhan Oblast"

alcohol_srt = alcohol_df.set_index(["region", "year"]).sort_index()
years_ind = alcohol_srt.loc[[("Amur Oblast", 2000), ("Sakha (Yakutia) Republic", 2000)]]
print(years_ind.loc[:, "wine":"beer"])

#Wine consumption for the year 2000 for "Amur Oblast", "Astrakhan Oblast"
alcohol_srt = alcohol_df.set_index(["region", "year"]).sort_index()
years_ind = alcohol_srt.loc[[("Amur Oblast", 2000):("Sakha (Yakutia) Republic", 2000), "wine":"beer"]]
print(years_ind)

# Wine and beer consumption for the year 2000-2010 from the regions "Amur Oblast"only
alcohol_srt = alcohol_df.set_index(["region", "year"]).sort_index()
years_ind = alcohol_srt.loc[("Amur Oblast", 2000):("Amur Oblast", 2010), "wine":"beer"]
print(years_ind)

Slicing row and column number using iloc method

years_ind = alcohol_df.iloc[2:5, 1:4]
print(years_ind)

4. Creating and Visualizing data

Launching a Static Website using AWS Amplify with CI/CD! v1

Msaghu — Wed, 03 Jan 2024 19:01:17 +0000

Happy New Year 2024! Big things in store for 2024 but first, lets launch our very first personal website using AWS. This will be my very first iteration of the website that I will update weekly, hopefully! If you have any comments/recommendations, please contact me below via the message box in my website!
The website will go through numerous iterations by the time 2024 ends so come along and we will learn/go through all its versions!

First about the website. I followed a website locally on my laptop using VSCode and the languages of choice were HTML, CSS and React.

Prerequisites

Must know how to use Git and Github
VSCode installed
Must have the code for our website

What is AWS Amplify?

AWS Amplify is a set of purpose-built tools and features that enables frontend web and mobile developers to quickly and easily build full-stack applications on AWS. Amplify provides two services: Amplify Hosting and Amplify Studio. For this instance we will be using Amplify Hosting . It provides a git-based workflow for hosting full-stack serverless web apps with continuous deployment.

Creating and connecting a repository

Since our application was written in React, we can follow the following steps as is:

1. Upload our development code to Github

If you already used an online code editor such as Gitpod, these steps will be somehow similar.

a. New repository

Create a new GitHub repository for your app (link)

b. Push code to the repository

From VSCode we will now initialize git and push the application to the new GitHub repo we created above. Execute the following commands in your command line interface:

git init
git remote add origin https://github.com/Msaghu/dhfhkdfkfj.git
git remote -v
git pull origin master
git add .
git commit -m “initial commit”
git push origin master

i. Line 1* >
if 'WebApp' is the folder you have your local web app. Change it to the directory cd WebApp
ii. Line 1 >
initialize git in the folder
iii. Line 2 >
add github url as a remote
get the git@github command from the HTTPS tab on Github.
iv. Line 3 >
Now pull the read me file from the github repo
v. Line 4 >
Verify that the Remote URL added or not utilizing the following command:

c. Connect your repository,

d. Select preferred VCS

On the Get started with Amplify Hosting page, select GitHub and choose Continue.
On the Add repository branch step, select yourbranchname from the Select a repository dropdown.
We will then need to authorize AWS Amplify to your GitHub account.
In the Branch dropdown select the branch master and choose Next.

e.

On the Build settings page, leave all the defaults, select Allow AWS Amplify to automatically deploy all files hosted in your project root directory and choose Next.

On the Review page select Save and deploy.

f. Deploy

The process takes a couple of minutes for Amplify Console to create the necessary resources and to deploy your code.
Once completed, select the site image, or the link underneath the thumbnail to launch your website.

You have now successfully launched your website!

RESOURCES

Create a React Portfolio Website
Hosting a Static Website on AWS
[AWS Amplify Documentation](https://docs.aws.amazon.com/amplify/latest/userguide/welcome.html

Terraform Cloud Project Bootcamp with Andrew Brown - Storing the state file in Terraform Cloud

Msaghu — Wed, 22 Nov 2023 19:08:35 +0000

Hi guys , in this piece we will learn how to store the terraform state file that is created when we run terraform apply to Terraform Cloud.

This article is part of my Terraform journey with Terraform Bootcamp by Andrew Brown and Andrew Bayko, together with Chris Williams(I am also using his resources that he published here and the beloved Shala Warner. I am also using some other resources from Aaron Brooks so that I can better explain new terms. And a special shout out to Gwen Leigh for such a helpful outline that I used as a guide to create this series so that the documentation is easy to read!

As I learn more about Terraform, feel free to follow Andrew Brown on Youtube and see their free (and paid) content . Now let's jump in.

Persisiting our local state file in Terraform Cloud
- What are Terraform State files?
Create a Terraform Cloud account
- 1. We will create a new project called terraform-beginner-bootcamp2023.
- 2. Create a new workspace within the project.
- 3. Run terraform init
- 4. Generating our terraform state file
- 5. Choose our preferred workflow
- 6. In main.tf file, we will edit it as below
Automating the creation of the terraform login
Resources

Persisiting our local state file in Terraform Cloud

What are Terraform State files?

The statefile can contain a lot of secrets!!!

resource IDs
DB username/passwords
private keys

So you should treat it like you would your company passwords. By default local state is stored in plaintext as JSON. You can mark sensitive information in your config files as such with the sensitive = true argument.

redacted from the CLI output, but still is in the statefile as plaintext (that's why it's important to lock down the statefile).

Use a backend that encrypts and protects your statefile from unauthorized access.

TFC encrypts state at rest & in transit
Turn on encryption if you are using S3 (& use state locking!)
Terraform does not persist state to local disk when remote state is being used

Create a Terraform Cloud account

We will need to create a Terraform Cloud account here

A Terraform project can have multiple workspaces, and a workspace is a configuration for an infrastructure project.

1. We will create a new project called `terraform-beginner-bootcamp2023`.

2. Create a new workspace within the project.

3. Run `terraform init`

We have to run this multiple times because our Gitpod environment is restarted every time we shut down. We dont have to do this if we are doing this project on a local machine/environment.

4. Generating our terraform state file

To generate our terraform state file terraform.tfstate, we will run terraform apply --auto-approve.

Since Terraform is agnostic, we can store our statefile in S3, locally or on Terraform Cloud.

5. Choose our preferred workflow

We will choose our preferred workflow in Terraform Cloud, which is the CLI-driven workflow, name the workspace, terrahouse-1. and add in the description. Create the workspace and. The organization will appear as below, with the name and email address already filled in

6. In `main.tf` file, we will edit it as below

terraform {
  cloud {
    organization = "msaghu"

    workspaces {
      name = "terra-house-renaissance"
    }
  }

  required_providers {
    random = {
      source = "hashicorp/random"
      version = "3.5.1"
    }
    aws = {
      source = "hashicorp/aws"
      version = "5.19.0"
    }
  }
}

provider "aws" {
  # Configuration options
}

provider "random" {
  # Configuration options
}

#https://registry.terraform.io/providers/hashicorp/random/latest/docs/resources/string
resource "random_string" "bucket_name" {
  lower = true
  upper = false
  length           = 32
  special          = false
}

#Terraform AWS S3 
#https://registry.terraform.io/providers/hashicorp/aws/latest/docs/resources/s3_bucket
resource "aws_s3_bucket" "example" {
  #bUCKET NAMING RULES
  #https://docs.aws.amazon.com/AmazonS3/latest/userguide/bucketnamingrules.html
  bucket = random_string.bucket_name.result
}

output "random_bucket_name" {
  value = random_string.bucket_name.result
}

We have the organization as the organization name we made when creating a Terraform Cloud

  backend "remote" {
    hostname = "app.terraform.io"
    organization = "company"

    workspaces {
      name = "my-app-prod"
    }

Establish a connection to Terraform cloud, run; terraform login and enter yes when prompted

In the Terraform Cloud page, go to the Tokens tab and copy the token provided.

Create a file in Gitpod touch /home/gitpod/.terraform.d/credentials.tfrc.json

Open the file open /home/gitpod/.terraform.d/credentials.tfrc.json and add in the following:

{
  "credentials": {
    "app.terraform.io": {
      "token": "YOUR_TERRAFORMCLOUD_TOKEN//PASTE IN THE CODE ABOV"
    }
  }
}

Save and close the file

Run terraform init which will tell you that it wants to copy the terraform.tfstate file to Terraform. When prompted enter yes

Go to Terraform cloud and see that the resources were created when we ran terraform init, plan and apply, i.e the S3 bucket and its random name, can now be seen on the Terraform cloud workspace.

Automating the creation of the terraform login

If we are using a workspace like Gitpod, we will need to do this step. Find the code here
We need to create credentials.tfrc.json;

In Terraform Cloud, create a user token for about 30 days.
Add the token as an environment variable in Gitpod by adding in the terminal;
gp env TERRAFORM_CLOUD_TOKEN='usertokenfor30daysusertokenfor30daysusertokenfor30daysusertokenfor30days'

then
export TERRAFORM_CLOUD_TOKEN='usertokenfor30daysusertokenfor30daysusertokenfor30daysusertokenfor30days'

We will create a bash file that will generate the tfrc credentials in bin folder ; bin/generate_tfrc_credentials

#!/usr/bin/env bash

# Define the target directory
TARGET_DIR="/home/gitpod/.terraform.d"
TARGET_FILE="${TARGET_DIR}/credentials.tfrc.json"

# Check if the TERRAFORM_CLOUD_TOKEN environment variable is set
if [ -z "$TERRAFORM_CLOUD_TOKEN" ]; then
  echo "Error: TERRAFORM_CLOUD_TOKEN environment variable is not set."
  exit 1
fi

# Create the directory if it doesn't exist
if [ ! -d "$TARGET_DIR" ]; then
  mkdir -p "$TARGET_DIR"
fi

# JSON structure for credentials.tfrc.json
json_data='{
  "credentials": {
    "app.terraform.io": {
      "token": "'"$TERRAFORM_CLOUD_TOKEN"'"
    }
  }
}'

# Write the JSON data to credentials.tfrc.json
echo "$json_data" > "$TARGET_FILE"

echo "$TARGET_FILE has been generated."

Change the file permissions by 'chmod u+x .bin/generate_tfrc_credentials

Make sure to note the Terraform 30 day token somewhere but DO NOT commit the file.

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Creating a command alias

Msaghu — Wed, 22 Nov 2023 18:59:50 +0000

Hi guys , in this piece we will learn how to create an alias for the terraform command i.e instead of using terraform init, we use tf init.

As I learn more about Terraform, feel free to follow Andrew Brown on Youtube and see their free (and paid) content . Now let's jump in.

[Shortening commands ; creating tf instead of Terraform](#shortening-commands-ccreating-tf-instead-of-ter

Shortening commands ; creating tf instead of Terraform

Set an alias for terraform to be tf in terraform, this can be replicated across other projects as you may like.
Open ~/.bash_profile for the terminal where we can set bash configurations/bash commands.
Add in a line
alias tf="terraform"
We need to reload the file, therefore we will run source ~/.bash_profile
To make sure that it is set, type tf in the terminal to test it.
To persist it, we need to create a bash script for it;
Create a new file set_tf_alias and paste in;

#!/usr/bin/env bash

#Checks if the alias already exists in the .bash_profile
grep -q 'alias tf="terraform"' ~/.bash_profile

# $? is a special variable in bash that holds the exit status of the last
if [ $? -ne 0 ]; then
    #if the alias does not exist, append it
    echo 'alias tf="terraform"' >> ~/.bash_profile
    echo "Alias added successfully."
else
    # inform the user if the alias already exists
    echo "Alias already exists in .bash_profile"
fi 

#Optional: source the .bash_profile to make the alias variable immediately
source ~/.bash_profile

Change its permissions in the terminal;
chmod u+x ./bin/set_tf_alias
Execute it in the terminal(it should say that its already created`
Add it to gitpod.yml and add it for both terraform and aws.
`
tasks:
- name: terraform before: | source ./bin/set_tf_alias source ./bin/install_terraform_cli source ./bin/generate_tfrc_credentials
- name: aws-cli env: AWS_CLI_AUTO_PROMPT: on-partial before: | source ./bin/set_tf_alias source ./bin/install_aws_cli

vscode:
extensions:
- amazonwebservices.aws-toolkit-vscode
- hashicorp.terraform
`

Restart the workspace to make sure that it loads as expected.

Terraform Cloud Project Bootcamp with Andrew Brown - Git Basics

Msaghu — Wed, 22 Nov 2023 18:54:47 +0000

Hi , guys. I have had quite an informative class in Andrew Brown's bootcamp. Here are a few items I have been able to pick up from the class regarding Git.

I will be showcasing examples where I demonstrate the steps one would take when executing a certain task and the definitions. In this use-case, we will be using GIT that has been installed on our Gitpod account.

Prerequisites

A Gitpod environment that gives you 500 free credits per month!
If you will not be using Gitpod, make sure that Git is installed on your local computer.

What is Git?

Installing Git on Gitpod environment

Install a Gitpod extension on Chrome to access Gitpod on Github.

What is Semantic Versioning in Git?

The basic git workflow

The basic Git workflow goes something like this:

You modify files in your working tree.
You selectively stage just those changes you want to be part of your next commit, which adds only those changes to the staging area.
You do a commit, which takes the files as they are in the staging area and stores that snapshot permanently to your Git directory.

But what happens if we want to provide better/streamlined and seamless workflow for a project? Then we follow the lead below;

Basic Git workflow chart for projects or for this project:

Stage 1: Create an Issue

On the github page, choose Issues and choose Create new issue. Then add a title to it and Submit the new issue. You the right hand side,

Create a new issue and create a label for it i.e is it a bug?, is it documentation?

Stage 2: Create a Branch

Create a branch
Stage 3: Merge the Branch into Main
Stage 4: Verify

Stage 1: Creating an issue

We create a ticket/issue then name the branch after the feature issue i.e 1-a-new-issue

The issue will give us a number which we can use to create the branch with the command below.

Stage 2: Create a Branch

In the issues page, on the right hand, scroll down and select create branch.

The branch name should be the same as the issue name with the issue number, and accept checkout locally.

Choose the create branch button.

In our terminal, we can now create the branch. Then run the command below to streamline the code from the remote repository
git fetch origin git checkout 1-a-new-issue

Stage 3: Merge the Branch into Main

Pushing the code to the remote branch on Github

In the version control knob in VSCode/ Gitpod , we can view the changes made. We will then stage the changes and then commit them with a commit message and the issue number i.e
#1 added an emoji to the code

If asked, sync the changes

NOTE

In the Git tree,
main are the files on your local machine,
origin the files on Github

Create a pull request

Go to the Git tree and view the tag position which we now want to pull into the main branch.

Stage 4: Verify and Sync the changes

In Github, go to pull requests then;

1. Create a pull request

Bring the feature branch to main
Add a comment to the pull request.
Squash and merge the commit
Confirm the squash and merge.

Commit the changes

git commit

Push the changes to remote

git push

Go back to main

git checkout main

Pull the changes

git pull

Create a tag for the branch

git tag 0.1.0

Push the tags to remote Github branch

git push --tags

Refresh on Github and make sure that the branches and the tags have been created.

In a larger team the squashed branch will be deleted to clear the version control code.

Switching to a branch from main

If we have a branch that we have made changes in and we want to move into it from main, we can follow the following steps.

git fetch
git add .
git stash save
git checkout branch name
git stash apply

We made changes to main instead of the branch, what do we do?

Mistakes happen, even to the most meticulous of us, so don't fret. Here's what to do;

git add .
git stash save
git stash list
git fetch
git checkout the-branch-we-should-have-made-changes-in
git pull
git stash apply

git stash list temporarily stores files
git fetch shows you all the new branch

To create tags

git checkout main
git pull
git tag 0.1.0
git push --tags
git fetch

HOW TO AUTOMATE DELETION OF BRANCHES AFTER MERGING
HOW TO AUTOMATE TAGGING OF BRANCHES

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Creating an S3 bucket and bucket name using Terraform Providers

Msaghu — Wed, 22 Nov 2023 07:05:06 +0000

Hi guys , in this piece we will now start creating resources with Terraform. In this post, we will outline how to create a resource that creates a random value that becomes the S3 bucket name.

As I learn more about Terraform, feel free to follow Andrew Brown on Youtube and see their free (and paid) content . I Now let's jump in.

Install and use the AWS Terraform provider
Create a bucket name using the random resource string
- Creating the main.tf file
- 1. Required providers blocks
- 2. Configure the credentials
- 3. The resource random_string
- 4. The resource aws_s3_bucket
- 5. Gives the output
Resources

Install and use the AWS Terraform provider

To refresh our memory about providers, they are Direct interfaces to APIs for providers. Find more info about them from here

We will create an AWS S3 bucket using Terraform. S3 is an object storage service from AWS that allows us to store items, referred to as objects in containers known as buckets in AWS terminology.

The "random" provider allows the use of randomness within Terraform configurations. This is a logical provider, which means that it works entirely within Terraform's logic, and doesn't interact with any other services.

Create a bucket name using the random resource string

Creating the `main.tf` file

Create a main.tf file(a module) and add the code below:

terraform {
  required_providers {
    random = {
      source = "hashicorp/random"
      version = "3.5.1"
    }
    aws = {
      source = "hashicorp/aws"
      version = "5.19.0"
    }
  }
}

provider "aws" {
  # Configuration options
}

provider "random" {
  # Configuration options
}

#https://registry.terraform.io/providers/hashicorp/random/latest/docs/resources/string
resource "random_string" "bucket_name" {
  lower = true
  upper = false
  length           = 32
  special          = false
}

#Terraform AWS S3 
#https://registry.terraform.io/providers/hashicorp/aws/latest/docs/resources/s3_bucket
resource "aws_s3_bucket" "example" {
  #bUCKET NAMING RULES
  #https://docs.aws.amazon.com/AmazonS3/latest/userguide/bucketnamingrules.html
  bucket = random_string.bucket_name.result
}

output "random_bucket_name" {
  value = random_string.bucket_name.result
}

1. Required providers blocks

In the required providers block, we need to add to use the aws provider as we will be creating a resource S3 in AWS and we also have to add an aws provider block random_string to create a random string for the bucket name.
terraform { required_providers { random = { source = "hashicorp/random" version = "3.5.1" } aws = { source = "hashicorp/aws" version = "5.26.0" } } }

2. Configure the credentials

We will now configure the credentials for the AWS provider within the main.tf file.

provider "aws" { AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY AWS_DEFAULT_REGION=us-west-2 }
Afterwards we can set the credentials as environment variables in the terminal.

Make sure to delete them before committing the file to VCS and let the provider look like the code block above.

3. The resource `random_string`

We are following the S3 bucket naming convention i.e making sure all letters are lowercase, have no special characters and have 32 characters.

Generates a random permutation of alphanumeric characters and optionally special characters.

This resource does use a cryptographic random number generator.
resource "random_string" "bucket_name" { lower = true upper = false length = 32 special = false }

4. The resource `aws_s3_bucket`

A resource that creates the S3 bucket with the bucket name as the output of the random-string resource above.
resource "aws_s3_bucket" "example" { bucket = random_string.bucket_name.result }

5. The output

Gives the random string which is the S3 bucket name.
output "random_bucket_name" { value = random_string.bucket_name.result }

Now run terraform init and get the out put below. The command will create 2 files: a .terraform file and a terraform.lock.hcl.

Initializing the backend...

Initializing provider plugins...
- Finding hashicorp/random versions matching "3.5.1"...
- Installing hashicorp/random v3.5.1...
- Installed hashicorp/random v3.5.1 (signed by HashiCorp)

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

Whenever we make a change to the main.tf file, we need to make sure that we always run terraform init to add the new providers/resources to state file.

terraform.lock.hcl is a package lock file that locks in the current version of terraform that we are using.
Then run terraform plan creates a changeset, which shows the infrastructure that IaC plans to change. It should appear as below. If no issues show up, then run terraform apply.


Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the
following symbols:
  + create

Terraform will perform the following actions:

  # random_string.bucket_name will be created
  + resource "random_string" "bucket_name" {
      + id          = (known after apply)
      + length      = 16
      + lower       = true
      + min_lower   = 0
      + min_numeric = 0
      + min_special = 0
      + min_upper   = 0
      + number      = true
      + numeric     = true
      + result      = (known after apply)
      + special     = false
      + upper       = true
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Changes to Outputs:
  + random_bucket_name = (known after apply)

────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Note: You didn't use the -out option to save this plan, so Terraform can't guarantee to take exactly these actions if
you run "terraform apply" now.

Now run terraform apply and enter Yes when prompted. It generates a terraform.tfstate file. (o use terraform apply --auto-approve
Now run terraform destroy --auto-approve to remove resources.

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Configuring Terraform & AWS CLI on Gitpod

Msaghu — Mon, 13 Nov 2023 18:32:16 +0000

Hi guys , in this piece we will be figuring out how to configure our Gitpod workspace for a project i.e installing Terraform in this case. The content will be broad enough to cove any other projects or languages that you might need.

As I learn more about Terraform, feel free to follow Andrew Brown on Youtube and see their free (and paid) content . Now let's jump in.

Why create a gitpod.yml file?
Install the Terraform CLI
- Considerations with the Terraform CLI changes
- Refactoring into Bash scripts
- Shebang Considerations
- Execution considerations
- Considerations for different Linux distributions.
- Linux Permissions Considerations
Creating a bash script to install Terraform
Creating a bash script to install the AWS CLI
- Add AWS credentials to our Gitpod environment
- Create the script that automatically installs AWS
The structure of the .gitpod.yml
- Github Lifecycle (Init, Before, Command)
Resources

Why create a gitpod.yml file?

A gitpod.yml file is a configuration file that is written in YAML that installs dependencies needed for your environment.
This makes it easier for continuing projects as you only need to configure the file once for a particular project and it automatically reloads as it is also stored in your preferred Version Control.
There is a base .gitpod.yml file that is automatically created whenever we launch a new workspace for anew project. Or you can run in the terminal the command below to generate the YAML file if it has not been automatically created.
gp init

Install the Terraform CLI

As we already learnt in the Git Basics, we will:

Create a new issue and add a label and comment. Since we had already copied the template from Andrew Brown's Github, we already have the .gitpod.yml created however, we will go through the steps when creating a new project so as to properly configure your workspace.

Considerations with the Terraform CLI changes

Using the Terraform CLI installation instructions to install a local version of Terraform. We need to refer to the latest install CLI¹ instructions via Terraform documentation and change the scripting for install.

Refactoring into Bash scripts

While fixing the Terraform CLI gpg deprecation issues, we noticed that the Bash script steps had a considerable amount more of code and we decided to create a bash script to install the Terraform CLI.
This will keep the Gitpod Task file .gitpod.yml tidy.
This allows us an easier to debug and executes manually Terraform CLI install .
This will allow for better portability for other projects that need to install the Terraform CLI.

Shebang Considerations

A shebang (#!) tells the bash script what program will interpret the script e.g #!/bin/bash

ChatGPT recommend this format for bash: #!/usr/bin/env bash

for portability for different OS distributions
will allow us to search the user's PATH for the bash executable

Execution considerations

When executing the bash scripts we can use the ./ shorthand notation to execute the bash script as opposed to the '$ source gdjshh.sh' notation.

If we are using a script in Gitpod YAML, we need to point the script to a program to interpret it e.g $ source gdjshh.sh notation.

Considerations for different Linux distributions.

This project is built on Ubuntu.
Please check your distribution to check then change accordingly to distribution needs.

$cat /etc/os-release

>output
PRETTY_NAME="Ubuntu 22.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.3 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy

Linux Permissions Considerations

Linux permissions works as follows:

In order to make a bash script executable, we need to change the Linux permissions for the file to be executable


chmod u+x ./bin/install_terraform_cli

alternatively:


chmod 744 ./bin/install_terraform_cli

Creating a bash script to install Terraform

Creating a bash script to automate the installation of Terraform in our Gitpod Linux environment.

But before we create the script we need to set the PROJECT_ROOT working directory. When we start our Gitpod workspace an example can be /workspace/terraform-beginner-bootcamp=2023
which we can now set in the terminal as an environment variable making it easy for us to access in the bash scripts.
We can set it using
echo $THEIA_WORKSPACE_ROOT export PROJECT_ROOT='/workspace/terraform-beginner-bootcamp=2023' echo $PROJECT_ROOT

#!/usr/bin/env bash

cd /workspace

sudo apt-get update && sudo apt-get install -y gnupg software-properties-common curl 

wget -O- https://apt.releases.hashicorp.com/gpg | \
gpg --dearmor | 
sudo tee /usr/share/keyrings/hashicorp-archive-keyring.gpg

gpg --no-default-keyring \
--keyring /usr/share/keyrings/hashicorp-archive-keyring.gpg \
--fingerprint

echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] \
https://apt.releases.hashicorp.com $(lsb_release -cs) main" | \
sudo tee /etc/apt/sources.list.d/hashicorp.list

sudo apt update

sudo apt-get install terraform
cd $PROJECT_ROOT

This will avoid us downloading junk files into our workspace
cd /workspace
This line of code will update the system, ensure that gnupg, curl has been installed;

sudo apt-get update && sudo apt-get install -y gnupg software-properties-common curl

We now install the Hashicorp GPG key
wget -O- https://apt.releases.hashicorp.com/gpg | \ gpg --dearmor | \ sudo tee /usr/share/keyrings/hashicorp-archive-keyring.gpg
Verify the Hashicorp security key
gpg --no-default-keyring \ --keyring /usr/share/keyrings/hashicorp-archive-keyring.gpg \ --fingerprint
Add the official HashiCorp repository to your system. The lsb_release -cs command finds the distribution release codename for your current system, such as buster, groovy, or sid.

echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] \ https://apt.releases.hashicorp.com $(lsb_release -cs) main" | \ sudo tee /etc/apt/sources.list.d/hashicorp.list

Download the package information from HashiCorp and Install Terraform from the new repository.
sudo apt update sudo apt-get install terraform -y
We will now make the script executable by running the instructions above and then run the file using:
source ./bin/install-terraform_cli or ./bin/install-terraform_cli
To go back to the working directory
cd $PROJECT_ROOT

We will reference this file in .gitpod.yml to install the dependencies and store away the binary files whenever we (re)start a new workspace.

Creating a bash script to install the AWS CLI

1. Add AWS credentials to our Gitpod environment

Add our AWS credentials that we want to set for the workspace
export AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE export AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY export AWS_DEFAULT_REGION=us-west-2
Confirm that they are set
env |grep AWS
Set them to Gitpod by adding
gp env AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE gp env AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY gp env AWS_DEFAULT_REGION=us-west-2

We avoid using the aws configure to set the credentials for our workspace, as we do not want to use/create a credentials files that might be easily leaked when we forget to add it to .gitignore

2. Create the script that automatically installs AWS

#!/usr/bin/env bash

cd /workspace

rm -f '/workspace/awscliv2.zip'
rm -rf '/workspace/aws'

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install

aws sts get-caller-identity

cd $PROJECT_ROOT

This will avoid us downloading junk files into our workspace
cd /workspace
We remove previous aws cli installations in the workspace

rm -f '/workspace/awscliv2.zip'
rm -rf '/workspace/aws'

Installs the goes to the https address and downloads the file, unzips it then installs it.
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip" unzip awscliv2.zip sudo ./aws/install
Confirms that we are using the 'correct' AWS account/credentials in json
aws sts get-caller-identity
Switch back to the working directory
cd $PROJECT_ROOT

We will reference this file in .gitpod.yml to install the dependencies and store away the binary files whenever we (re)start a new workspace.

The structure of the `.gitpod.yml`

Gitpod has tasks that allow it to launch terminals
Take a look at the sample gitpod.yml file below,

tasks:
  - name: terraform
    before: |
      source ./bin/install_terraform_cli

  - name: aws-cli
    env:
      AWS_CLI_AUTO_PROMPT: on-partial
    before: |
      source ./bin/install_aws_cli

vscode:
  extensions:
    - amazonwebservices.aws-toolkit-vscode
    - hashicorp.terraform

For new workspaces, we need to install the dependencies therefore we need the before first.

We will install the terraform CLI from using the bash script that we created above. (we use source to run the Terraform CLI script as it does not care if the file is to be made executable)

tasks:
  - name: terraform
    before: |
      source ./bin/install_terraform-cli

Github Lifecycle (Init, Before, Command)

We need to be mindful when using init as it will not run if we restart an existing workspace.

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Week 0, 1 & 2 Journal

Msaghu — Fri, 10 Nov 2023 16:44:43 +0000

Hi guys. Welcome back. I recently decided to learn Terraform as I upskill for my first tech role. This article is part of my Terraform journey with Terraform Bootcamp by Andrew Brown and Andrew Bayko, together with Chris Williams(I am also using his resources that he published here and the beloved Shala Warner. I am also using some other resources from Aaron Brooks so that I can better explain new terms. And a special shout out to Gwen Leigh for such a helpful outline that I used as a guide to create this series so that the documentation is easy to read!

As I learn more about Terraform, feel free to follow Andrew Brown on Youtube and see their free (and paid) content . I Now let's jump in. Follow the links below to be redirected to the respective week;

Week 0 - Prep week
Week 0 Live Stream
0.0.0 Basic Definitions
0.1.0 Configuring Terraform on Gitpod
0.2.0 Git Basics
0.3.0 Creating an S3 bucket and bucket name using Terraform Providers
0.4.0 Storing the state file in Terraform Cloud
0.4.0 Creating a command alias

Week 1
Week 1 Live Stream
1.0.0 Journal TOC and Major Version
Git basics
1.1.0 Restructure Root Module
1.3.0 Create AWS Terrahouse Module
1.4.0 Static Website Hosting
1.5.0 Content Delivery Network
1.6.0 Terraform Data Content Version
1.7.0 Invalidate Cache Local Exec
1.8.0 Assets Upload For Each
1.8.2 Working Through Git Graph Issues

Week 2
Week 2 Live Stream
2.0.0 Setting up TerraTown Mock Web Server
2.1.0 Setup Skeleton for Custom Terraform Provider
2.2.0 Provider Block for Custom Terraform Provider
2.3.0 Resource Skeleton
2.4.0 CRUD Resource
2.5.0 Deploying to TerraTown
2.6.0 Terraform Cloud and Multi Homes

Week 3
Terraform
AWS

Terraform Bootcamp Week 0 with Andrew Brown: Basic Definitions

Msaghu — Fri, 10 Nov 2023 16:43:45 +0000

Hi guys. Welcome. I recently decided to learn Terraform as I upskill for my first tech role. This article is part of my Terraform journey with Terraform Bootcamp by Andrew Brown and Andrew Bayko, together with Chris Williams(I am also using his resources that he published here and the beloved Shala Warner. I am also using some other resources from Aaron Brooks so that I can better explain new terms. And a special shout out to Gwen Leigh for such a helpful outline that I used as a guide to create this series so that the documentation is easy to read!

As I learn more about Terraform, feel free to follow Andrew Brown on Youtube and see their free (and paid) content . I Now let's jump in;

Basic Definitions
- What is Terraform?
- What is IaC?
- Types of infrastructure
- Why use IAC?
- What is Configuration Management?
  - Configuration Management vs Infrastructure Provisioning?
- What is Hashicorp Vault?
- Terraform Infrastructure Lifecycle
  - Idempotency.
  - Configuration Drift:
    - Mutable vs Immutable infrastructure
- Terraform workflow
  - 1. Code
  - 2. Terraform init
  - 3. Terraform plan
  - 4. Terraform validate
  - 5. Terraform apply
  - 6. Terraform destroy - unmakes the things! 😱
- Resources

Basic Definitions

What is Terraform?

Terraform is an infrastructure as code tool that lets you build, change, and version cloud and on-prem resources safely and efficiently.
HashiCorp Terraform is an infrastructure as code tool that lets you define both cloud and on-prem resources in human-readable configuration files that you can version, reuse, and share. You can then use a consistent workflow to provision and manage all of your infrastructure throughout its lifecycle. ¹

What is IaC?

Infrastructure as Code (IaC) is the blueprint of our infrastructure that allows managing and provisioning of infrastructure through code instead of through manual processes. This is because provisioning infrastructure at scale is prone to error. It allows us to provision the SAME INFRASTRUCTURE everytime.

With IaC, configuration files are created that contain your infrastructure specifications, which makes it easier to edit and distribute configurations. It also ensures that you provision the same environment every time. By codifying and documenting your configuration specifications, IaC aids configuration management and helps you to avoid undocumented, ad-hoc configuration changes.

Version control is an important part of IaC, and your configuration files should be under source control just like any other software source code file. Deploying your infrastructure as code also means that you can divide your infrastructure into modular components that can then be combined in different ways through automation. ²

Types of infrastructure

We have 2 types of infrastructure:

Declarative
Imperative

1. Declarative
What you see is what you get. It's explicit with 0 chance of misconfiguration:

Azure only -> ARM Templates, Azure Blueprints
AWS only -> CloudFormation
GCP only -> Cloud Deployment Manager
All of the above (& many others) -> Terraform

2. Imperative
Uses existing programming languages like Python, JS or Ruby:

AWS only -> AWS CDK
AWS, Azure, GCP, K8s -> Pulumi
Terraform supports For loops, dynamic blocks, complex data structures – so it's declarative with some imperative benefits.

Why use IAC?

Creates a consistent and reusable ways to deploy and manage infrastructure and configuration.
Help prevent configuration drift.
Provides free documentation i.e code and logs of current state.
Share & reuse your configurations more easily
Manage infra on multiple cloud platforms
Track resource changes
Use version control (Git, GitHub, etc..) to collaborate with team members

What is Configuration Management?

The primary goal of configuration management tools is to configure the server. Meaning, if you want to automate the installation and configuration of an application(e.g., Nginx) in a server, we use a tool like Ansible and Chef. It does all the configurations in an idempotent manner. It can be used to maintain computer systems, servers, applications, network devices, and other IT components in a desired state.
They allow administrators to set up an IT system, such as a server or workstation, then build and maintain other servers and workstations with the same settings. IT teams use configuration assessments and drift analyses to continuously identify systems that have strayed from the desired system state and need to be updated, reconfigured, or patched. It’s a way to help ensure that a system performs as expected, even after many changes are made over time. ³
Also, these tools help in managing the configuration drift. It ensures all the servers are running in the same configuration mentioned in the ansible-playbook or a chef cookbook .In the case of an agent-based chef/puppet, if someone changes the server config manually, the chef agent brings it back to the desired state, as mentioned in the cookbook.
All the configuration management tools keep an inventory of the server’s IP address and SSH credentials to connect to the servers. However, in cloud environments where servers are dynamically provisioned, it uses an API-based dynamic inventory to get the server details.

Configuration Management vs Infrastructure Provisioning?

Infrastructure provisioning tool Terraform is responsible for providing the network and servers
Configuration management tool Ansible configures applications inside servers provisioned by Terraform.⁴

What is Hashicorp Vault?

HashiCorp Vault is an identity-based secrets and encryption management system. A secret is anything that you want to tightly control access to, such as API encryption keys, passwords, and certificates. Vault provides encryption services that are gated by authentication and authorization methods. Using Vault’s UI, CLI, or HTTP API, access to secrets and other sensitive data can be securely stored and managed, tightly controlled (restricted), and auditable.

Terraform Infrastructure Lifecycle

The Infrastructure Lifecycle is having clearly defined work phases for planning, designing, building, testing, maintaining and retiring your infrastructure.

Idempotency.

a property of some operations such that no matter how many times you execute them, you achieve the same result. Terraform is idempotent because, no matter how many times you run the same configuration file, you will end up with the same expected state.

Configuration Drift:

When your actual infrastructure deviates away from the desired infrastructure. Unexpected configuration change away from what is stated in the config file. Can be due to manual adjustment (console access in prod = BAD 😂), evil h@xx0rs, etc. that is done outside of the scripting tools i.e Hashicorp Terraform, AWS CloudFormation, Ansible

.. How do we fix it?

Detect:

By using configuration monitoring tools/compliance tools like AWS Config, or built-in support e.g. AWS CF Drift Detect
They monitor your infrastructure and notify based on default and custom rules. >PS: Most of these tools are not free

Correct:

TF refresh & plan commands
Manually correct (try not to do this)
Reprovision (comes with it's own risks)

Prevent:

use immutable infrastructure
always create & destroy, never reuse
never share credentials
run scheduled plans with tools like AWS Config and use Terraform Import Blocks to import resources outside your Terraform State
use drift detection tools like driftctl and Cloudquery
use GitOps to version control IaC i.e Create tf file commit Pull Request peer review commit to main GitHub action triggers build

Mutable vs Immutable infrastructure

Think of mutable infrastructure as (1) building a base image (2) Deploying that base image then (3) configuring the software after deploy.

Terraform workflow

Most of the main Terraform files end with the .tf extension.

1. Code

create or edit your terraform config file

2. Terraform init

This command initializes a working directory containing Terraform configuration files, pull providers and modules.
This is the first command that should be run after writing a new Terraform configuration or cloning an existing one from version control. It is safe to run this command multiple times.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see  
any changes that are required for your infrastructure. All Terraform commands  
should now work.

If you ever set or change modules or backend configuration for Terraform,      
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

3. Terraform plan

Moves items from current state to the configuration state described in the main.tf file.
It creates an execution plan, which lets you preview the changes that Terraform plans to make to your infrastructure. By default, when Terraform creates a plan it:

Reads the current state of any already-existing remote objects to make sure that the Terraform state is up-to-date.
Compares the current configuration to the prior state and noting any differences.
Proposes a set of change actions that should, if applied, make the remote objects match the configuration.

4. Terraform validate

Ensure types, values, and required attributes are valid and present

5. Terraform apply

After making our plan we can now execute it.

6. Terraform destroy - unmakes the things! 😱

Resources

Free AWS BootCamp WEEK 4 PART 2: PostgreSQL Databases in AWS

Msaghu — Wed, 15 Mar 2023 09:08:13 +0000

Hi guys, we will now cover how to install PostgreSQL on our local machine for use to run SQL commands. But first, we will have go through some basic definitions:

What is AWS Relational Database Service(RDS)?

What are the advantages of AWS Relational Database Service(RDS)?

Allows you to create and scale relational databases in the cloud.
RDS runs on virtual machines (can’t log in to the OS or SSH in).
AWS handles admin tasks for you like hardware provisioning, patching & backups.
RDS is not serverless — (one exception Aurora Serverless)
Allows you to control network access to your database Offers encryption at rest — done with KMS (data stored, automated backups, read replicas and snapshots all encrypted)
AWS Aurora is the managed version of SQL databases that is fully managed by AWS.
When create a database we need to be sure that it has been created in the same region where our account is.
You can create and modify a DB instance by using the AWS Command Line Interface (AWS CLI), the Amazon RDS API, or the AWS Management Console.
Amazon RDS is responsible for hosting the software components and infrastructure of DB instances and DB cluster. We are responsible for query tuning, which is the process of adjusting SQL queries to improve performance.
We can run our DB instance in several Availability Zones, an option called a Multi-AZ deployment. When we choose this option, Amazon automatically provisions and maintains one or more secondary standby DB instances in a different Availability Zone. Your primary DB instance is replicated across Availability Zones to each secondary DB instance.
We use security groups to control the access to a DB instance. It does so by allowing access to IP address ranges or Amazon EC2 instances that you specify.

Supported RDS Database engines

A DB engine is the specific relational database software that runs on your DB instance. Amazon RDS currently supports the following engines:
-MariaDB

-Microsoft SQL Server

-MySQL

-Oracle

-PostgreSQL

RDS Main Features

Multi AZ Recovery

Have a primary and secondary database, if you lose the primary database, AWS would detect and automatically update the DNS to point at the secondary database.
Used for DISASTER RECOVERY, it doesn’t improve performance.

Read Replicas

Every time you write to the main database, it is replicated in the secondary database.
If you lose the primary database there is no automatic failover, you need to manually update the URL to it yourself
IMPROVES PERFORMANCE
Used for scaling
Automatic backups must be turned on
Up to 5 read replicas of any database
It is possible to have read replicas of read replicas - but this can introduce latency.
Each read replica has its own DNS
Can have multi AZ

RDS Backups

Automated Backups

Allows you to recover your database to any point in time within the specified retention period (Max 35 days)
Takes daily snapshots and stores transition logs
When recovering AWS will choose the most recent backup
Enabled by default
Backup data is stored in S3
May experience latency when backup is being taken
Backups are deleted once you remove the original RDS instances

Database Snapshot

User-initiated, must be manually done by yourself
Stored until you explicitly delete them, even after you delete the original RDS instance they are still persisted. However this is not the case with automated backups.

Step 1: Install PostgreSQL

Go to the link below
Follow the link
Select click on the installer and choose the latest version to begin the download process.
Follow the prompts, and finish download(do not choose the Stack driver for this purpose).
From Windows , open Postgresql and copy the shell and pgadmin4 onto the desktop.

Step 2: Connect to the DB client.

We can connect using 2 options:
1. GUI Client - i.e using DataGrip, Postico(for MAC users), pgAdmin(for Windows users).
2. Terminal/CMD - allows us to use the commands to access the DB.
3. Application - we write a server side application to return data from our database.

Step 3: Startup the Database Server to connect to our local server.

We will open the PostgreSQL shell, we can also use this to connect to a remote server by pasting in the url during

Server[localhost]: --paste in url here--

So we will press enter to cycle through the following

Server[localhost]:
Database[postgres]:  
Port[5432]: 
Username[postgres]:
Password for user postgres:  (now enter the password we used when configuring our system)

To use the GUI , click on pgAdmin4

Step 4: Create a Database

While using the Postgres shell, type the following commands

psql: \l                    --------> lists all databases
psql: CREATE DATABASE test; --------> creates a database with the name test

Step 5: Connect to a Database

To be able to use Postgresql in our cmd, we need to add Postgresql to PATH, we can do this by adding C:\Program Files\PostgreSQL\15\bin and C:\Program Files\PostgreSQL\15\lib to path
Therefore to access the postgres client from the CMD type

Users> psql --help

In the command below postgres is the default username and test is the database we created above. The command will drop us into the postgres shell for the existing DB.

Users> psql -h localhost -p 5432 -U postgres test

-To connect to the database when we are in the postgres shell, we will run:

test=# \l                      ----> lists all databases
test=# \c test
test=# \c killmonger           ----> to connect to another database called killmonger, we simply use the command
test=# DROP DATABASE test;     ----> to permanently delete a database

Step 6: Creating tables and adding data in PostgreSQL
- While logged into our database above we will run the following

test=# CREATE TABLE person(
test(# id INT,
test(# first_name VARCHAR(50),
test(# last_name VARCHAR(50),
test(# gender VARCHAR(7),
test(# date_of_birth DATE);

-- To se the table we will run
test=# \d ----> to describe the table
test=# \d person ----> shows the table schema


- To create a table with constraints i.e we must have values

test=# CREATE TABLE person(
test(# id BIGSERIAL NOT NULL PRIMARY KEY,
test(# first_name VARCHAR(50) NOT NULL,
test(# last_name VARCHAR(50) NOT NULL,
test(# gender VARCHAR(7) NOT NULL,
test(# date_of_birth DATE) NOT NULL,
test(# email VARCHAR(150) );

-- To se the table we will run
test=# \d ----> to describe the table
test=# \d person ----> shows the table schema


- To add records(which are rows) into tables
INSERT INTO person(
    first_name,
    last_name,
    gender,
    date_of_birth)
VALUES ('Anne', 'Smith', 'FEMALE', DATE '1988-01-09');

test=# \dt ----> to see just the table
test=# INSERT INTO person(first_name, last_name, gender, date_of_birth)
test=# VALUES( 'Anne', 'Smith', 'FEMALE', '1988-01-09');

--To add a value with the email column:
test=# \dt ----> to see just the table
test=# INSERT INTO person(first_name, last_name, gender, date_of_birth, email)
test=# VALUES( 'Jake', 'James', 'MALE', '1988-01-09', 'jake@gmail.com');


- As we have seen so far, physically typing in the data is time-consuming, therefore, if we need to work with large datasets , the exercise will be almost impossible to achieve, therefore to add multiple people we can use websites such as **Mockaroo**

- We will create our file there and open it with VSCode to ensure that some values are NOT NULL as below

create table person (
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(150),
gender VARCHAR(15) NOT NULL,
date_of_birth DATE NOT NULL,
country_of_birth VARCHAR(50)
);


- To import/ open it in postgres we will run

--be sure to copy the file path from where our file is located and make use of the slash as shown below and wrap in single quotes

test=# \i 'C:/path/path/person.sql'


- To make sure that we only have current values, we can drop the previous table and create a new table

test=# \d person
test=# DROP TABLE person; ---> since some values in our imported table are not in the existing table, we will delete the existing table
test=# \d
test=# \i 'C:/path/path/person.sql'
test=# SELECT * FROM person; --->all values are now pulled in


- To ensure that they are automatically numbered, add id into the code in VSCode

create table person (
id BIGSERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(150),
gender VARCHAR(15) NOT NULL,
date_of_birth DATE NOT NULL,
country_of_birth VARCHAR(50)
);


- We will repeat the following commands:

test=# DROP TABLE person; ---> since some values in our imported table are not in the existing table, we will delete the existing table
test=# \i 'C:/path/path/person.sql'
test=# SELECT * FROM person; --->all values are now pulled including the id




**Step 7: Using commands**
test=# SELECT * FROM person;  ----> shows all columns/fields in the table
test=# SELECT first_name, last_name FROM person;

***Sorting using the ORDER BY keyword***

test=# SELECT * FROM person ORDER BY country_of_birth;  ----> sort by ascending order by default
test=# SELECT DISTINCT(country_of_birth) FROM person ORDER BY country_of_birth;        ----> to only get the country value once

***Sorting using the WHERE keyword***
test=# SELECT * FROM person WHERE gender = 'Female';
test=# SELECT * FROM person WHERE gender = 'Female' AND (country_of_birth='Poland' OR country_of_birth='China');
test=# SELECT * FROM person WHERE gender = 'Female' AND (country_of_birth='Poland' OR country_of_birth='China') AND last_name = 'Kimble';

***Comparison operators***
test=# SELECT 1 < 1;
test=# SELECT 1 >= 2;
test=# SELECT 1 <> 1;   ----> 1 not equal to 2
test=# SELECT 1 + 1;

***LIMIT and OFFSET keywords***
test=# SELECT * FROM person LIMIT 12;
test=# SELECT * FROM person OFFSET 5 LIMIT 5;   ----> starts from id 6 to 10
SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY;
test=# SELECT * FROM person OFFSET 5 FETCH FIRST 1 ROW ONLY;

***IN keywords***
test=# SELECT * FROM person WHERE country_of_birth='China' OR country_of_birth='Brazil' OR country_of_birth='France';
test=# SELECT * FROM person WHERE country_of_birth IN('China', 'Brazil', 'France');
test=# SELECT * FROM person WHERE country_of_birth IN('China', 'Brazil', 'France', 'Kenya', 'Cambodia') ORDER BY gender;
test=# SELECT * FROM person WHERE country_of_birth IN('China', 'Brazil', 'France', 'Kenya', 'Cambodia') ORDER BY country_of_birth;

***BETWEEN keyword to get data from a range***
test=# SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2022-01-01' AND '2022-03-30';

***LIKE keyword values against a pattern using wildcards***
- % is a wildcard thus accepts all entries
- ILIKE accepts both uppercase and lowercase
test=# SELECT * FROM person WHERE email LIKE '%@google.com'; 
test=# SELECT COUNT(*) FROM person WHERE email LIKE '%@google.%'; 
test=# SELECT COUNT(*) FROM person WHERE country_of_birth ILIKE 'p%';       ----> ignores the case

***GROUP BY ***
test=# SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth;
test=# SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth ORDER BY country_of_birth; 

***HAVING keyword***
test=# SELECT country_of_birth,COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*)<=140 ORDER BY country_of_birth;





####RESOURCES
1. [Adding to environment variables to PATH in Windows](https://www.youtube.com/watch?v=2oAM4Q-9DMU)
2. [PostgreSQL Data types - Documentation](https://www.postgresql.org/docs/current/datatype.html)
3. [PostgreSQL Aggregate Function Documentation](https://www.postgresql.org/docs/9.5/functions-aggregate.html)

Free AWS BootCamp WEEK 4: SQL Databases

Msaghu — Tue, 14 Mar 2023 14:20:01 +0000

Hi guys, welcome back!!!
This is week 4 of the Andrew Brown AWS Free code camp that you can also follow on Youtube and LinkedIn.

This week we will be handling:

SQL
PostgreSQL

What are Databases?

A database is a server where we can store, manipulate and retrieve data.
A relational database defines relationships between tables of data inside the database.
Therefore PostgreSQL is the database engine and SQL is the Structured Query Language/the programming language.

What are the advantages of databases?

They have more storage capabilities than spreadsheets e.g Google spreadsheets or Excel sheets.
Are more secure as data is encrypted.
Multiple users can write queries to gather insight from the data at the same time.

What is SQL?

SQL stands for Structured Query Language.
This is the most widely used programming language for databases.

What are the basics of SQL?

SQL is not processed in the order in which it is written i.e. it starts from FROM ==> SELECT ==> LIMIT

What are tables in SQL?

These are the main building blocks of Databases.
Databases are organized into tables which hold related data about a particular subject.
Tables can have both columns and rows , rows are referred to as records and columns are referred to as fields.
A tables fields are limited to the number created during database creation.
While there is no limit to the number of records.
Table names should be lowercase, should not include spaces(use underscores instead) and can be plural.
Field names should be lowercase, should not include spaces(use underscores instead), should be singular, each field should have different names and should not share names with the table name.
Unique identifiers/keys help identify records in a table, are unique and are often numbers.

Data types in SQL

Data types are chosen depending on the type of data the field will hold i.e numbers, text, dates.
Data types are necessary as they are stored differently and they take up different amounts of space.
A string refers to a sequence of characters such as letters or punctuation.
VARCHAR is a flexible and popular string data type in SQL.
INT is a flexible and popular integer data type in SQL.
NUMERIC is a flexible and popular float data type in SQL.

What are Schemas?

Are blueprints of databases.
Schemas show database design i.e tables included in the database and the relationships between the tables.
Schemas also inform the data type each field can hold.
Information in a database table is stored in a hard disk of a server.

Basic SQL queries

This is the basic structure of an SQL query

SELECT name  ------------------> chooses the field name
FROM patrons; -----------------> chooses the table in which the fields are listed

Selecting multiple fields

SELECT card_num, name
FROM patrons;

Selecting all fields

SELECT *
FROM patrons;

Renaming columns using aliasing(temporarily)

SELECT name AS first_name, year_hired
FROM employees;

To show a value i.e year_hired only once using DISTINCT.

SELECT DISTINCT year_hired
FROM employees;

To show multiple fields i.e year_hired .

SELECT dept_id, year_hired
FROM employees;

To show multiple fields i.e year_hired using DISTINCT.

SELECT DISTINCT dept_id, year_hired
FROM employees;

A view is a virtual table that is a query code stored for future use.
To save a SQL query as a view;

CREATE VIEW employee_hire_years AS
SELECT id, name, year_hired
FROM employees;

Querying databases

COUNT() - returns number of records with a value in a field

SELECT COUNT(birthdate) AS count_birthdates 
FROM people;

-- to count multiple fields from a table
SELECT COUNT(name) AS count_names, COUNT(birthdate) AS count_birthdates 
FROM people;

-- to count the number of records in a table
SELECT COUNT(*) AS total_records
FROM people;

To find distinct values in table

SELECT COUNT(DISTINCT birthdate) AS count_distinct_birthdates
FROM people;

Filtering data

Using WHERE -n Comparison operators >, <, =, <=, >=, <>(not equal to)

WHERE with comparison operators

SELECT title
FROM films
WHERE release_year > 1960;

To use a comparison operator from a films table to find is a film language could be English

-- Count the Spanish-language films
SELECT COUNT(language) AS count_spanish
FROM films
WHERE language = 'Spanish';

To use multiple criteria with : OR, AND, BETWEEN

-- Using OR operator
SELECT *
FROM films
WHERE language = 'Spanish' OR length = 120;

-- Using AND operator
SELECT *
FROM films
WHERE language = 'Spanish' AND length = 120;

-- Using AND, OR
SELECT title
FROM films
WHERE (release_year = 1994 OR release_year = 1995) AND (certification = "PG" OR certification = "R");

-- Using BETWEEN operator(shorthand for the AND operator)
SELECT title
FROM films
WHERE length BETWEEN 120 AND 180;

-- Using BETWEEN operator(shorthand for the AND operator)
SELECT title
FROM films
WHERE length BETWEEN 120 AND 180 AND country='Kenya';

Filtering text

Its usually recommended to filter with a pattern rather than specific text.
We will then use: LIKE, NOT LIKE, IN.
Use Like for records that match the specified pattern

- LIKE use like with % to match zero, one or many characters 

SELECT name
FROM people
WHERE name LIKE 'Ade%';

- LIKE use like with _ to match a single character
SELECT name
FROM people
WHERE name LIKE 'Ev_';

Use NOT LIKE for records that do not match the specified pattern

SELECT name
FROM people
WHERE name NOT LIKE 'A.%';

To filter across many conditions or a range of numbers with WHERE, OR or WHERE, IN

SELECT title
FROM films
WHERE release_year = 1920
OR release_year = 1930
OR release_year = 1940;

SELECT title
FROM films
WHERE release_year IN (1920, 1930, 1940);

Filtering Data with NULL values

Use IS NULL incase of missing data

--Gives the titles where release_year was not provided 
SELECT title
FROM films
WHERE release_year IS NULL;

--Gives the total number of titles where release_year was not provided 
SELECT COUNT(*) AS not_released
FROM films
WHERE release_year IS NULL;

--Gives the total number of titles where release_year was provided
SELECT COUNT(title) AS not_released
FROM films
WHERE release_year IS NOT NULL;

Summarizing Data

For numerical data AVG(), SUM()

SELECT AVG(budget)
FROM films;

For non-numerical data MIN(), MAX(), COUNT()

Summarizing subsets

SELECT AVG(budget) AS avg_budget
FROM films
WHERE release_year >= 2010;

ROUND()

--number_to_round, decimal_places

SELECT ROUND(AVG(budget), 2) AS avg_budget
FROM films
WHERE release_year >= 2010;

--to round to a whole number
SELECT ROUND(AVG(budget)) AS avg_budget
FROM films
WHERE release_year >= 2010;

--to round using a negative number
SELECT ROUND(AVG(budget), -5) AS avg_budget
FROM films
WHERE release_year >= 2010;

Basic Arithmetic

Basic arithmetic is +, -, * and /

SELECT (4 + 3);

SELECT (4 / 3);  --------------> 1

SELECT (4.0 / 3.0); -----------> 1.3333

Sorting results

Using ORDER BY(sorts in ascending order)

-- Using ORDER BY
SELECT title, budget
FROM films
ORDER BY budget;

-- Using ORDER BY and ASCending order
SELECT title, budget
FROM films
ORDER BY budget ASC;

-- Using ORDER BY and DESCending order
SELECT title, budget
FROM films
WHERE budget IS NOT NULL
ORDER BY budget DESC;

ORDER BY multiple fields

-- Using ORDER BY and DESCending order
SELECT title, wins, imdb_score
FROM best_movies
ORDER BY wins DESC, imdb_score DESC;

Grouping data

GROUP BY single fields,

-- Grouping by certification, then counting
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification;

GROUP BY multiple fields,

-- Grouping by certification, then counting
SELECT certification, language COUNT(title) AS title_count
FROM films
GROUP BY certification, language;

-- Modify the query to also list the average budget and average gross
SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross
FROM films
WHERE release_year > 1990
GROUP BY release_year;

Using Grouping BY with ORDER BY

SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification
ORDER BY title_count DESC;

Filtering Grouped Data

Since we cant filter aggregate functions with WHERE

-- This will not work
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification
WHERE COUNT(title) > 10;

--This WILL work
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification
HAVING COUNT(title) > 10;

--This WILL work
SELECT release_year
FROM films
GROUP BY release_year
HAVING AVG(duration) > 10;

RESOURCES

Free AWS Bootcamp: Week 1 - App Containerization

Msaghu — Tue, 21 Feb 2023 12:10:41 +0000

Hi guys, welcome back to week 2.

This week we will be learning about Docker and containers.

What is a container?

An isolated environment for running an application.
It can also be described as a running instance of an image.
Are more lightweight compared to VMs, as they do not require/or contain a full OS and they use the OS of the host, thus allowing them to start quickly.
We need less hardware resources unlike VMs thus allowing us to run even 100s of containers side-by-side.

What is Docker?

A platform for building, running and shipping applications and all its dependencies in a consistent manner.
Virtual Machines allow us to run applications in isolation inside a VM.

What is the Docker Architecture?

Uses the Client-Server Architecture, where the Client talks to the Server(This is the Docker Engine) using a RESTful API.
Containers use the Kernel of the Host, and each kernel has its own APIs thus making it hard to run windows applications on Mac and vice versa, however we can run Linux applications natively on Windows due to WSL.

What are the differences between docker desktop and Docker?

Docker desktop makes use of a hidden VM(virtual machine)/subsystem depending on your OS or subsystem to run an isolated instance of Docker.
Docker then uses the hidden instance to configure a Linux OS with the Docker runtime. Once its installed we will then have the Docker CLI and User Interface.
Docker desktop Is segmented from your main OS.

What is a Docker image?

A template for creating an environment of your choice i.e a database, a web application, an application.
An image is a Snapshot, which is a version of our image.

What is the process of dockerizing an application?

This means that we add a Docker file to the application.
A dockerfile is a plain text file that includes instructions to package the application into an image.
An image contains everything an application needs to run e.g:

A cut-down OS
A runtime environment
Application files
Third-party libraries
Environment variables

Then Docker starts a container with the image, thus making a container a process with its own file system.
We can then post our application image to Docker hub which can then be downloaded and started on any other machine.

Traditional environment for a JavaScript application

Start with an OS
Install Node (the execution environment for JS code)
Copy app files
Run node app.js

What are Container Volumes?

Allows us to share data between host and container or between containers which can be a file or folder.

Key Terms

VM - Virtual Machines
API - Application Programming Interface
Client-Server Architecture - A client can be a web browser or desktop application that a person interacts with to make requests to computer servers. A server can be a type of virtual server.
Kernel - Manages applications and hardware resources eg. memory, CPU.

For example, suppose that a client makes a request for a news article, the score in an online game, or even you clicking the links in the resources below. The server evaluates the details of this request and fulfills it by returning the information to the client.

Resources

DEV Community: Msaghu

Python for Data Analysis Cheatsheet

Exploring a Dataframe using the table

1. Dataframes

Subset columns to only see the data in a particular column

Subset rows to only see the data in a particular column. It displays the answer in boolean True or False.;

Subset using .isin()

Adding new column

2. Aggregating Data

Cumulative statistics

Remove repeat values

Summary statistics

Pivot Tables

3. Slicing and Indexing Data

4. Creating and Visualizing data

Launching a Static Website using AWS Amplify with CI/CD! v1

Prerequisites

What is AWS Amplify?

Creating and connecting a repository

1. Upload our development code to Github

a. New repository

b. Push code to the repository

c. Connect your repository,

d. Select preferred VCS

e.

f. Deploy

RESOURCES

Terraform Cloud Project Bootcamp with Andrew Brown - Storing the state file in Terraform Cloud

Table of Contents

Persisiting our local state file in Terraform Cloud

What are Terraform State files?

Create a Terraform Cloud account

1. We will create a new project called terraform-beginner-bootcamp2023.

2. Create a new workspace within the project.

3. Run terraform init

4. Generating our terraform state file

5. Choose our preferred workflow

6. In main.tf file, we will edit it as below

Automating the creation of the terraform login

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Creating a command alias

Table of Contents

Shortening commands ; creating tf instead of Terraform

Terraform Cloud Project Bootcamp with Andrew Brown - Git Basics

Prerequisites

What is Git?

Installing Git on Gitpod environment

What is Semantic Versioning in Git?

The basic git workflow

Basic Git workflow chart for projects or for this project:

Stage 1: Creating an issue

Stage 2: Create a Branch

Stage 3: Merge the Branch into Main

Pushing the code to the remote branch on Github

Create a pull request

Stage 4: Verify and Sync the changes

1. Create a pull request

Commit the changes

Push the changes to remote

Go back to main

Pull the changes

Create a tag for the branch

Push the tags to remote Github branch

Switching to a branch from main

We made changes to main instead of the branch, what do we do?

To create tags

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Creating an S3 bucket and bucket name using Terraform Providers

Table of Contents

Install and use the AWS Terraform provider

Create a bucket name using the random resource string

Creating the main.tf file

1. Required providers blocks

2. Configure the credentials

3. The resource random_string

4. The resource aws_s3_bucket

5. The output

Resources

Terraform Cloud Project Bootcamp with Andrew Brown - Configuring Terraform & AWS CLI on Gitpod

Table of Contents

1. We will create a new project called `terraform-beginner-bootcamp2023`.

3. Run `terraform init`

6. In `main.tf` file, we will edit it as below

Creating the `main.tf` file

3. The resource `random_string`

4. The resource `aws_s3_bucket`

The structure of the `.gitpod.yml`