DEV Community: Muhammad Rameez

Container Services on AWS

Muhammad Rameez — Fri, 15 Sep 2023 20:46:39 +0000

AWS offers a broad spectrum of compute offerings, that give you the flexibility to choose the right tool for the right job.

The three main categories of compute are:

virtual machines
containers
serverless

There is no one-size-fits-all service because it depends on your needs.The key is to understand what each option has to offer in order to build a more appropriate cloud architecture for your use case. Let's talk about containers first.

WHAT ARE CONTAINERS?
A container is a standardized unit that packages up your code and all of its dependencies. This package is designed to run reliably on any platform, because the container creates its own independent environment. This makes it easy to carry workloads from one place to another, such as from development to production or from on-premises to the cloud. e.g. Flutter/VCS on Github.

WHAT IS DOCKER?
You will get to hear about the docker along with the container very often. Docker is a popular container runtime that simplifies the management of the entire operating system stack needed for container isolation, including networking and storage. Docker makes it easy to create, package, deploy, and run containers.

AWS offers two container orchestration services:

Amazon Elastic Container Service (ECS)
Amazon Elastic Kubernetes Service (EKS)

MANAGE CONTAINERS WITH AMAZON ELASTIC CONTAINER SERVICE (AMAZON ECS)
Amazon ECS is an end-to-end container orchestration service that allows you to quickly spin up new containers and manage them across a cluster of EC2 instances. Have a look at the illustration, given below:

If you want to use ECS Service, you simply need to install this agent in your EC2 instance. You can run this agent on both Linux and Windows AMIs (Amazon Machine Image). An instance with the container agent installed is often called a container instance.

USE KUBERNETES WITH AMAZON ELASTIC KUBERNETES SERVICE (AMAZON EKS)
Kubernetes is a portable, extensible, open source platform for managing containerized workloads and services. By bringing software development and operations together by design, Kubernetes created a rapidly growing ecosystem that is very popular and well established in the market.

If you are familiar with Kubernetes, you can use Amazon EKS to orchestrate these workloads in the AWS Cloud. Amazon EKS is conceptually similar to Amazon ECS, but there are some differences.

An EC2 instance with the ECS Agent installed and configured is called a container instance. In Amazon EKS, it is called a worker node.
An ECS Container is called a task. In the Amazon EKS ecosystem, it is called a pod.
While Amazon ECS runs on AWS native technology, Amazon EKS runs on top of Kubernetes.

WHAT ARE SERVERLESS COMPUTE?
Amazon offer some services that are completely managed by user and some services that are some how abstracted from a user, these services only contains the action to perform not the complete insides like networking capacity, scalability. Amazon manage these things itself, these type of abstract services are called as Serverless computing.

Example: EC2 is a virtual server but amazon lambda is serverless.

Remember, EC2 is a virtual server so it cannot provide severless services. We can use Amazon Fargate with Amazon ECS and Amazon EKS as a serverless service. So, no need to manage elasticity, scalability e.t.c

Elastic compute cloud (EC2)

Muhammad Rameez — Tue, 05 Sep 2023 18:38:49 +0000

EC2 (Elastic Compute Cloud):
Amazon EC2 is a web service that provides secure, resizable compute capacity in the cloud. It allows you to provision virtual servers called EC2 instances. Although AWS uses the phrase “web service” to describe it, it doesn’t mean that you are limited to running just web servers on your EC2 instances. You can create and manage these instances through the AWS Management Console, the AWS Command Line Interface (CLI), AWS Software Development Kits (SDKs), or through automation tools.

When launching an EC2 instance, the first setting you configure is which operating system you want by selecting an Amazon Machine Image (AMI).

AMI (Amazon Machine Image):
In the traditional infrastructure world, the process of spinning up a server consists of installing an operating system from installation disks, installation drives, or installation wizards over the network. In the AWS Cloud, this operating system installation is no longer your responsibility, and is instead built into the AMI that you choose. Not only does an AMI let you configure which operating system you want, you can also select storage mappings, the architecture type (such as 32-bit, 64-bit, or 64-bit ARM), and additional software installed.

What Is the Relationship Between AMIs and EC2 Instances?
EC2 instances are live instantiations of what is defined in an AMI, much like a cake is a live instantiation of a cake recipe.

If you are familiar with software development, you can also see this kind of relationship between a Class and an Object.

One advantage of using AMIs is that they are reusable. You can easily re-use one AMI into the other EC2 Instance.

AMI Instance - Life Cycle:

Terminating an EC2 instance is a bad thing. So, you can create more instance when the demand is high and shut down the instance or stopped the instance when the demand is low. Remember, You can only get paid for what, EC2 Instance that are in running position. AWS does not charge usage or data transfer fees for your instance after you stop it.

How to launch EC2 Instance?
Launch an EC2 instance that hosts the employee directory application.

If needed, log in to the AWS Management Console as your Admin user (Other then root user/IAM user).

In the Services search bar, search for EC2, and open the service by choosing EC2.
In the navigation pane, under Instances choose Instances.
Choose Launch instances.
For Name use employee-directory-app.
Under Application and OS Images (Amazon Machine Image), choose the default Amazon Linux 2023.
Under Instance type, select t2.micro.
Under Key pair (login), choose Create a new key pair.
For Key pair name, paste app-key-pair. Choose Create key pair. The required .pem file should automatically download for you.
Under Network settings and Edit: Keep the default VPC selection, which should have (default) after the network name
Subnet: Choose the first subnet in the dropdown list
Auto-assign Public IP: Enable
Under Firewall (security groups) choose Create security group use app-sg for the Security group name and Description.
Under Inbound security groups rules choose Remove above the ssh rule.
Choose Add security group rule. For Type choose HTTP. Under Source type choose Anywhere.
Expand Advanced details and under IAM instance profile choose S3DynamoDBFullAccessRole.

In the User data box, paste the following code:

#!/bin/bash -ex
wget https://aws-tc-largeobjects.s3-us-west-2.amazonaws.com/DEV-AWS-MO-GCNv2/FlaskApp.zip
unzip FlaskApp.zip
cd FlaskApp/
yum -y install python3-pip
pip install -r requirements.txt
yum -y install stress
export PHOTOS_BUCKET=${SUB_PHOTOS_BUCKET}
export AWS_DEFAULT_REGION=<INSERT REGION HERE>
export DYNAMO_MODE=on
FLASK_APP=application.py /usr/local/bin/flask run --host=0.0.0.0 --port=80

In the pasted code, change the following line to match your Region (your Region is listed at the top right, next to your user name):

export AWS_DEFAULT_REGION=<INSERT REGION HERE>

Example:
The following example uses the US West (Oregon) Region, or us-west-2.

export AWS_DEFAULT_REGION=us-west-2

Note: In a later lab, you will modify this user data script again to use your Amazon Simple Storage Service (Amazon S3) bucket. For now, keep ${SUB_PHOTOS_BUCKET} in the script.

Choose Launch instance.
Choose View all instances.

The instance should now be listed under Instances.
Wait for the Instance state to change to Running and the Status check to change to 2/2 checks passed.

Note: Often, the status checks update, but the console user interface (UI) might not update to reflect the most recent information. You can minimize waiting by refreshing the page after a few minutes.

How to view your hosted application?
Let's see the running application on the instance in a web browser window.

Select the instance by selecting its check box.
Instance information should load on the tabs in the pane.

On the Details tab, copy the Public IPv4 address.

Note: Make sure that you only copy the address instead of choosing the open address link.

In a new browser window, paste the IP address that you copied. Make sure to remove the ‘S’ after HTTP so you are using only HTTP instead.

You should see an Employee Directory placeholder. Right now, you won’t be able to interact with it yet because the application isn’t connected to a database.

Congratulations! You have successfully created an EC2 instance, which hosts the employee directory application.

How to stop EC2 Instance?

After you finish exploring the instance, you will stop and terminate your instance so that you don’t incur future costs.

Go back to the AWS Management Console.
The employee-directory-app instance should still be selected.
At the top of the console pane, choose Instance state, choose Stop instance, and choose Stop.

The status in the Instance state column will eventually go into the Stopped state.

Next, you will terminate the instance.

Make sure that check box next to the instance Name is selected.

Choose Instance state, choose Terminate instance, and choose Terminate.

Getting Started with AWS Cloud

Muhammad Rameez — Fri, 01 Sep 2023 13:53:17 +0000

What is the Cloud?

In the past, companies and organizations hosted and maintained hardware such as compute, storage, and networking equipment in their own data centers. They needed to allocate entire infrastructure departments to take care of them, resulting in a costly operation that made some workloads and experimentation impossible.

As internet usage became more widespread, the demand for compute, storage, and networking equipment increased. For some companies and organizations, the cost of maintaining a large physical presence was unsustainable. To solve this problem, cloud computing was created.

What is the Cloud Computing?

Cloud computing is the on-demand delivery of IT resources over the internet with pay-as-you-go pricing. You no longer have to manage and maintain your own hardware in your own data centers. Companies like AWS own and maintain these data centers and provide virtualized data center technologies and services to users over the internet.

Cloud computing not only saves time but also provide reliability, scalability, cost effectiveness, presistancy, predictiveness.

The Six Benefits of Cloud Computing

You will pay for what, you have used
Auto Scalability
High Speed Support
Stop spending money for maintaining data centers
Deploy your application in minutes or in few clicks

On-Premise vs Cloud:

Case 1:
What if, we save our important data in our laptop but unfortunately laptop has stolen. There is no way to recover that data, if it is not backed-up on Cloud.

Case 2:
What if, the Cloud datacenter got destroyed through fire. No worries, in this case our data is still safe because Data Centers are decentralized, which means all of the data centers are connected with each other.

In any case of uncertainty, the second datacenter keep on running under the absence of first data center. That, cluster of datacenter is called Availability Zone. The clusters of AZ (Availability Zone) is called as Region.

When a cluster of AZ in region-1 is down for any reason then cluster of AZ in region-2 will start working.

Three thing you might consider while selecting region:

It must contains:

Low latency rate
Less Pricing in that region
Service availability

As of March 8, 2023, AWS has 32 regions and 102 Availability Zones (AZs) around the world. AWS is constantly expanding its global infrastructure, so the number of regions and AZs is always changing. You can find the latest information on the AWS website.

Now, Lets drive deeper in AWS (Amazon Web Services).
There are 3 ways to interact the AWS service on AWS Platform:

AWS Management Console
Command Line Interface
Software Development Kits (SDks)

IN AWS Management Console, We use UI interface, from where we can easily navigate to the service that we want to use.

In Command Line Interface, We use terminal and type command run any aws service.

In SDKs, We use can integrate it with the scripting tools like python script in VS Code. SDKs are open-source and maintained by AWS for the most popular programming languages, such as C++, Go, Java, JavaScript, .NET, Node.js, PHP, Python, and Ruby. Developers commonly use AWS SDKs to integrate their application source code with AWS services.

Here is an example of code you can implement to work with AWS resources using the Python AWS SDK.

import boto3

ec2 = boto3.client('ec2')

response = ec2.describe_instances()

print(response)

What is shared responsiblity model?

AWS emphasize on shared responsiblity model. Which means both the customer and AWS are responsible for managing and taking care of AWS. Some of the things, customer are the responsible like security. While other things, AWS took care about it like, Hardware issues, networking, compute etc.

AWS Access Control

Root User
IAM User

AWS strongly recommend that do enable MFA (Multi-factor Authentication) and don't use Root User on day to day routine. Instead create an IAM (Identity Access Management) Role allow access of the services and use IAM User in daily routine, just for the safety reasons.

Authentication & Authorization:

Authentication allow user to logged into the account [AWS Account] while Authorization allow user to give access or grant permission to use specific service. Example: In a company, every person logged into the account to mark attendance but only HR (Human Resource) is authorized to see the attendance of employees.

Everything in AWS is an API Call

IAM Policy allow admin/Root to Grant access to the group user or an Individual User.

You cannot apply policy to the root user but you can apply policies to the IAM User. So, It is suggested by AWS that you can create IAM Admin from the root user and then logged out from it and then logged in with the IAM Admin user. IAM supports MFA.

Deploy a Web Application on AWS:

In order to deploy a web app on AWS, you need to create an instance of EC2 (Elastic Compute Cloud) where you can add access to IAM Group users, So that all of the IAM Users in a IAM Group can get access to the hosted web application. Don't forget to select the Free Tier option while you are in a free tier account, so that will not get charged.

Joins in SQL ( Part 02 )

Muhammad Rameez — Sat, 08 Apr 2023 08:01:34 +0000

A JOIN statement in SQL is used to combine data from two or more tables based on a common field. It can be used to retrieve data from one table that is related to data in another table. A JOIN statement typically consists of the names of two or more tables, followed by an ON clause that specifies the join conditions. The type of JOIN, such as INNER JOIN, LEFT JOIN, or RIGHT JOIN, also determines how the joined tables are related and what data is returned.

All about Structure Query Language (SQL)

Muhammad Rameez — Sat, 08 Apr 2023 07:59:18 +0000

A SQL statement is a query written in Structured Query Language (SQL) that is used to interact with a database. It is used to retrieve or manipulate data stored in a database. SQL statements are composed of clauses, expressions, and predicates that specify the data that is being queried and the operations that are to be performed on that data.

SQL Statements:

Create Database:

A CREATE DATABASE statement in SQL is used to create a new database. It consists of the CREATE DATABASE keyword, followed by the name of the database. Optionally, a character set and collation can be specified. Once the database has been created, tables can be added to it.

Query:
Create Database [database name];

Create Database Inventory;

Alter Database :

If you want to change the name of your database, you can run this command to make changes:

ALTER DATABASE [olddatabase] MODIFY NAME = [newdatabase];

Alter Database Inventory Modify name = Accounts;

Remember : SQL is not a case sensitive language which means you can either write the query in lowercase or in an uppercase

Before creating the table make sure that, you are working on the same database that you want to use.

To change the database:

Create Table:

A CREATE TABLE statement in SQL is used to create a new table in a database. It consists of the CREATE TABLE keyword, followed by the name of the table and a list of column definitions. Each column definition consists of the column name, its data type, and any additional constraints. Once the table has been created, data can be added to it.

DataTypes of SQL contains:
bool, varchar, int, float, bit

Query:
Create table [table name] ( [row name] datatype validation, [row name] datatype validation, );

Table 01

Create table vendor(
   id int Primary key,
   name varchar(256) not null,
   email varchar(256) not null,
);

Table 02

Create table customer(
   id int Primary key,
   name varchar(256) not null,
   email varchar(256) null,
   regularity_status bit not null,
   vendor_id int not null,

Foreign Key (vendor_id) 
REFERENCES vendor(id)
);

Insert Statement :

An INSERT statement in SQL is used to add new records to a table. It consists of the INSERT keyword, followed by the table name, and a list of column names and values. For example, to insert a new row into a table called 'customers', the following statement could be used:

Query :

INSERT INTO customer (id, name, email, regularity_status) VALUES (1, 'John Doe', 'john@example.com', 1);

To insert multiple rows, no need to write it again and again. You can use like :

INSERT INTO customer (id, name, email, regularity_status) VALUES (2, 'John Doe', 'john@example.com', 1),
(3, 'John Smith', null, 0),
(4, 'Smith Doe', null, 0),
(5, 'John Kinidy', 'johkanidy@example.com', 1),
(6, 'John Mike', 'johnMike@example.com', 1),
;

Select Statement :

A SELECT statement in SQL is used to retrieve data from a table. It consists of the SELECT keyword, followed by a list of column names, and the FROM keyword, followed by the table name. For example, to retrieve all columns from a table called 'customers', the following statement could be used:

Query:
Select [column name] from [table name];

SELECT * FROM customer;
SELECT name, email from customer;

Update Statement :

An UPDATE statement in SQL is used to modify existing records in a table. It consists of the UPDATE keyword, followed by the table name, and a list of column names and values. For example, to update the email address of a customer with the ID of 1, the following statement could be used:

UPDATE customer SET email = 'newemail@example.com' WHERE id = 1;

Delete Statement :

A DELETE statement in SQL is used to delete records from a table. It consists of the DELETE keyword, followed by the table name, and a WHERE clause that specifies the records to be deleted. For example, to delete all customers with the ID of 1, the following statement could be used:

DELETE FROM customer WHERE id = 1;

Distinct Keyword :

The DISTINCT keyword in SQL is used to return only unique values in a result set. It is typically used in conjunction with the SELECT statement, followed by the column names that should be included in the result set. For example, to return only unique names from a table called 'customers', the following statement could be used:

SELECT DISTINCT name FROM customer;

Where Keyword :

The WHERE clause in SQL is used to filter records in a result set. It is typically used in conjunction with the SELECT statement, followed by a condition that must be met for a record to be included in the result set. For example, to return only customers with the ID of 1, the following statement could be used:

SELECT * FROM customer WHERE id = 1;

And, OR, NOT Keyword :

AND, OR, and NOT are logical operators in SQL that are used to combine multiple conditions in a WHERE clause. AND requires that both conditions be true for a record to be included in the result set, OR requires that one of the conditions be true, and NOT requires that the condition be false. For example, to return only customers with the ID of 1 or 2, the following statement could be used:

SELECT * FROM customer WHERE id = 1 OR id = 2;
SELECT * FROM customer WHERE id = 1 and email = 'newemail@example.com';

NULL Function :

The NULL keyword in SQL is used to represent a missing or unknown value. It is typically used in conjunction with the WHERE clause, followed by an operator such as IS NULL or IS NOT NULL. For example, to return only customers with a NULL email address, the following statement could be used:

SELECT * FROM customer WHERE email IS NULL;

Like Keyword :

The LIKE operator in SQL is used to compare a column value to a pattern. It is typically used in conjunction with the WHERE clause, followed by a string pattern that can include wildcards. For example, to return only customers whose name starts with 'John', the following statement could be used:

SELECT * FROM customer WHERE name LIKE 'John%';

Like statement have two wildcard % and _ When we want to retrieve all the letters before this specific word then we use % before the specific word. It could be like:

SELECT * FROM customer WHERE name LIKE '%Doe';

Similarly, When we want to retrieve a word that contains oe but that must have only one letter before this specific word then we use:

SELECT * FROM customer WHERE name LIKE '_oe';

Top Keyword :

The TOP keyword in SQL is used to limit the number of rows returned in a result set. It is typically used in conjunction with the SELECT statement, followed by a number that indicates how many rows should be returned. For example, to return the top 10 customers, the following statement could be used:

SELECT TOP 10 * FROM customer;

Aggregate functions :
count(), add(), avg()

The COUNT, SUM, and AVG functions in SQL are used to perform aggregate calculations on a set of records. COUNT returns the number of records in a result set, SUM adds up the values of a specified column, and AVG calculates the average of a specified column. For example, to calculate the average age of customers, the following statement could be used:

SELECT AVG(age) FROM customer;

Similarly, to calculate the sum of customers, the following statement could be used:

SELECT Sum(regularity_status) FROM customer;

To calculate the count of customers, the following statement could be used:

SELECT Count(name) FROM customer;

Min, Max functions :

The MIN and MAX functions in SQL are used to find the minimum and maximum values of a specified column. They are typically used in conjunction with the SELECT statement, followed by the name of the column. For example, to find the oldest and youngest customers, the following statements could be used:

SELECT MIN(age) FROM customer;

SELECT MAX(age) FROM customer;

Between keyword :

The BETWEEN operator in SQL is used to filter records that fall within a specified range. It is typically used in conjunction with the WHERE clause, followed by two values that indicate the start and end of the range. For example, to return only customers between the ages of 18 and 30, the following statement could be used:

SELECT * FROM customers WHERE age BETWEEN 18 AND 30;

Alias keyword :

An alias in SQL is used to give a column or table a temporary name. It is typically used in conjunction with the SELECT statement, followed by the name of the column or table and the desired alias. For example, to give the customers table the alias 'c', the following statement could be used:

SELECT name as Customer_Name FROM customer;

SELECT c.name FROM customer as c;

UNION, INTERSECT, and EXCEPT Operators :

The UNION, INTERSECT, and EXCEPT operators in SQL are used to combine the results of multiple SELECT statements. UNION combines the results of two SELECT statements, INTERSECT returns only the records found in both SELECT statements, and EXCEPT returns only the records found in the first SELECT statement but not the second. For example, to combine the results of two SELECT statements, the following statement could be used:

SELECT * FROM customer UNION SELECT * FROM vendor;

The UNION ALL operator in SQL is used to combine the results of two SELECT statements. It is similar to the UNION operator, except that it does not remove duplicate records from the result set. For example, to combine the results of two SELECT statements, the following statement could be used:


SELECT * FROM customers1 UNION ALL SELECT * FROM customers2;

The above statement will combine the results of the two SELECT statements and return all records, including any duplicates.

SELECT * FROM customer INTERSECT SELECT * FROM vendor;

SELECT * FROM customer EXCEPT SELECT * FROM vendor;

ORDER BY clause :

The ORDER BY clause in SQL is used to sort the records in a result set. It is typically used in conjunction with the SELECT statement, followed by the name of the column that should be used for sorting. For example, to sort customers by their age, the following statement could be used:

SELECT * FROM customer ORDER BY age;

Here, we can also sort the result based on ascending or descending order by just add a keyword asc or desc :

SELECT * FROM customer ORDER BY name desc;

By default, order by can get result in ascending order.

IN operator :

The IN operator in SQL is used to check if a value is present in a list of values. It is typically used in conjunction with the WHERE clause, followed by a list of comma-separated values. For example, to check if a customer's age is 18, 21, or 25, the following statement could be used:

SELECT * FROM customer WHERE age IN (18, 21, 25);

IS operator :

The IS operator in SQL is used to compare a value to another value or to a known constant. It is typically used in conjunction with the WHERE clause, followed by the value to be compared and the value or constant it should be compared to. For example, to check if a customer's age is equal to 18, the following statement could be used:

SELECT * FROM customers WHERE age IS 18;

Group by clause :

The GROUP BY clause in SQL is used to group records together based on a specified column or expression. It is typically used in conjunction with the SELECT statement, followed by the name of the column or expression to be used for grouping. For example, to group customers by their age, the following statement could be used:

SELECT name, count(name) from customer group by name;

HAVING clause :

The HAVING clause in SQL is used to filter records in a result set after they have been grouped. It is typically used in conjunction with the GROUP BY clause, followed by a condition that must be met for a record to be included in the result set. For example, to return only groups of customers where the average age is greater than 18, the following statement could be used:

SELECT name, count(name) as Similar_names from customer group by name having count(name) > 2;

Case clause :

The CASE statement in SQL is used to create conditional logic in a query. It is typically used in conjunction with the SELECT statement, followed by a condition and the value to be returned if the condition is met. For example, to assign a letter grade to each customer based on their age, the following statement could be used:

SELECT *, 
CASE WHEN age < 18 THEN 'Teen' 
WHEN age BETWEEN 18 AND 25 THEN 'Young Adulthood' 
WHEN age > 25 THEN 'Adulthood' 
END AS Adolescence 
FROM customer;

Joins :

A JOIN statement in SQL is used to combine data from two or more tables based on a common field. It typically consists of the names of two or more tables, followed by an ON clause that specifies the join conditions. For example, to join the customers and orders tables on the customer_id field, the following statement could be used:

SELECT * FROM customer 
JOIN orders 
ON customer.vendor_id = vendor.id;

Types of SQL Joins:

There are several types of JOINs in SQL, each of which determines how the joined tables are related and what data is returned.

• INNER JOIN: This type of JOIN returns only rows from both tables that match the join conditions.
• LEFT JOIN: This type of JOIN returns all rows from the left table, and only the rows from the right table that match the join conditions.
• RIGHT JOIN: This type of JOIN returns all rows from the right table, and only the rows from the left table that match the join conditions.
• FULL OUTER JOIN: This type of JOIN returns all rows from both tables, regardless of whether they match the join conditions or not.

Other types of JOINs in SQL include:

•** CROSS JOIN:** This type of JOIN returns all possible combinations of rows from both tables, regardless of whether they match the join conditions or not.
• SELF JOIN: This type of JOIN is used to join a table to itself. It is typically used to compare values in a single table.
• NATURAL JOIN: This type of JOIN joins two tables based on columns with the same name in each table.
• UNION JOIN: This type of JOIN combines the results of two SELECT statements into a single result set.

Structure Query Language

Muhammad Rameez — Sat, 25 Mar 2023 20:08:45 +0000

What is SQL ?

SQL (Structured Query Language) is a programming language used to communicate with databases. It is used to store, retrieve, manipulate, and manage data in relational databases. SQL uses statements to perform tasks such as updating data in a database or retrieving data from a database. We can delete, alter and fetch the data by using query of SQL within a second. SQL is not a Case sensitive language which means you can write query in all caps or in all small, it will works. SQL can create stored procedures and views in a database.

For Example :
Select * from life where memory like 'Joy'
Select * from Bytewise where fellow like 'Data Engineer'

There are 3 types of SQL :

What is DDL ?

DDL (Data Definition Language) is a type of SQL language used to define the structure of a database. It is used to create, modify, and delete database objects such as tables, views, and other database structures. DDL statements are also used to set permissions on database objects and to create and alter stored procedures and functions.

What is DML ?

DML (Data Manipulation Language) is a type of SQL language used to manipulate data. It is used to insert, update, delete, and select data in a database. DML statements are also used to control access to the data stored in a database.

What is DQL ?

DQL (Data Query Language) is a type of SQL language used to query data from a database. It is used to retrieve data from one or more tables, views, or other sources in a database. DQL statements are also used to specify conditions in the query, such as selecting data based on certain criteria.

Famous SQL Commands :

Create Database :
CREATE DATABASE <database_name>;
Alter a Database :
ALTER DATABASE <database_name> [MODIFY]<newName_database>;
Deleting a Database :
DROP DATABASE <database_name>;
TRUNCATE DATABASE <database_name>;
DELETE DATABASE <database_name>;

Data Types used in SQL :

Data Types in SQL are used to define the type of data stored in the columns of a database table. The most common SQL data types are:

INTEGER, VARCHAR, BOOLEAN, DATE, TIME, and DECIMAL.

Each data type has a specific set of properties and characteristics that define how it is stored and used.

For example, INTEGER is used to store whole numbers, VARCHAR is used to store variable-length strings, BOOLEAN is used to store true or false values, and DATE is used to store date and time values. Other data types, such as DECIMAL, can be used to store more complex data types such as currency values.

List of SQL Important Statements :
SQL all about these statements and keyword that all have different behavior and use. We can discuss it as we move forward:

Create Statements
Insert Statements
Select Statements
Update Statements
Delete Statements
Distinct
Like
Top
Aggregate functions (count(), add(), avg())
Min(), Max()
Between
Alias
Union, Intersect, Except
Order by
In
Where
is
Null
Joins
Group by
Having
Case

For studding all queries in SQL, Click here

For studding Joins in SQL, Click here

Data Engineering (Part 02)

Muhammad Rameez — Fri, 24 Mar 2023 20:21:06 +0000

In this blog we are going to discuss the working practices used in Data engineering.

What is ETL ?

ETL stands for Extract, Transform, and Load, and is a process used for transferring data from one system to another, it can be through API or local host. The Extract phase involves retrieving data from multiple sources, such as databases, files, and web services. The Transform phase involves transforming the data into a specific format that can be used in the target system. Finally, the Load phase involves loading the transformed data into the target system. ETL is used to ensure data accuracy and consistency across systems.

ETL is commonly used to migrate data from legacy systems to modern systems, as well as to integrate data from multiple sources into a single system. It can also be used to cleanse data and prepare it for analysis, as well as to load data into data warehouses and data lakes. ETL processes are usually automated to ensure efficient and accurate data transfer.

What is ELT ?

ELT (Extract-Load-Transform) is an alternative to ETL (Extract-Transform-Load) and is used for transferring data from one system to another. The Extract phase involves retrieving data from multiple sources, such as databases, files, and web services. The Load phase involves loading the data into the target system. The Transform phase involves transforming the data into a format that can be used in the target system. ELT is used to ensure data accuracy and consistency across systems.

Same like ETL, it allow migrate data from legacy systems to modern systems, as well as to integrate data from multiple sources into a single system. It can also be used to cleanse data and prepare it for analysis, as well as to load data into data warehouses and data lakes. ELT processes are usually also automated to ensure efficient and accurate data transfer. ELT is often preferred over ETL because it allows for parallel processing and makes it easier to handle large datasets.

There are many tools available for ETL :

Apache Airflow 2. Talend
AWS Glue 4. Informatica
SSIS

There are multiple types to load the data in Data Engineering in ETL process. Some of may use historical load, full load and incremental load.

Historical Load :

Historical load is the process of loading data from the past into a data warehouse or data lake. This process typically involves extracting data from multiple sources, transforming the data into a format that can be used in the target system, and loading the data into the target system.

Historical load can be used to capture data from past events and to analyze trends over time.

Full Load :

Full load is also used to load data from the data warehouse or data lake. Data extraction can be done from multiple sources.

Full load is often used when setting up a new data warehouse or data lake, or when starting from scratch with an existing system.

Incremental Load :

Incremental load is often used to keep a data warehouse or data lake up to date with the latest data. It also extract thee data from multiple sources.

3 Tier architecture :

The three-tier architecture of data engineering is a software architecture pattern that divides a system into three separate tiers:

Data ingestion
Data processing
Data storage

The data ingestion tier is responsible for collecting, cleaning, and loading data from multiple sources.

The data processing tier is responsible for transforming and aggregating the data.

The data storage tier is responsible for storing the data in a structured format.

This architecture allows for scalability and modularity, as well as improved performance and security.

This architecture is the back-bone of data engineering.

Important Questions related to Data Engineering

Muhammad Rameez — Mon, 20 Mar 2023 18:23:43 +0000

Can a database be used as DWH?

Yes, Database can be used as a Data Warehouse. Even before the data warehouse, we use database for the business. But now it's a good practice to use Data warehouse because of quick response and Analystical tools. Data warehouse is based on specific category thatswhy, we get quick response as compared to Database.

Difference between structured and UN structured data ?

Structured Data:
Is in the form of structured manner, it can be a SQL query or a script that we run on terminal of any platform to perform task is considered as structured data. This data is in uniform format.

Unstructured Data:
This data is in distributed manner. It is in scattered formed. This data contains text file, video, image, audio etc.

Roles of Data Engineer :

Design and develop databases, data models and tables to store data.
Develop ETL processes and data pipelines for data integration.
Monitor and optimize database performance.
Analyze data for patterns, trends, and insights.
Develop data visualizations and dashboards.
Troubleshoot data-related issues.
Implement security and privacy measures.
Collaborate with data scientists and business stakeholders.
Build API connection to fetch the data from the source target to the destination target.

Data engineering for beginners (Part 01)

Muhammad Rameez — Sun, 19 Mar 2023 16:17:59 +0000

What is Data Engineering ?

Perform different techniques to get transformed data including cleaning, transforming and extraction on data to send it to the data scientist so that he can use this data to perform some predictions is know as Data Engineering.

Data Scientist work starts where the work of a Data Engineers work ends.

Hahaha. Don't worry, I will elaborate each an every concept that is used in data engineering for beginner, in this blog. Buckle up and sit back relax.

What is Database ?

Database is a collection of row and columns can be in the form of structured formed/tables or can be in unstructured format. Database is used to store the data so that we can retrieve it afterwards.

So, we use Database to store the data and retrieve it but in the case of quick response this trick doesn't works because as the database contains all the data so when we call it to fetch, it takes time. That was a very bad experience for the user.

*For example: *
In case of live Dashboard, when the user call the dashboard to see his stats and it will take time ☹️

At this place, Data Warehouse got entered.

What is Data Warehouse ?

Data warehouse is same like the Database. It is also used to store the data. But the major difference is that data warehouse is specific for the Niche. Like if we want sales record then one data warehouse only contains sales records no other niches than also it is difficult to delete the data from the data warehouse, however data is easily deletable from the database.

"Data warehouse is not good so" if this question is revolving into your mind read this blog [Database vs Data Engineering]

As, data warehouse only contains the data related to specific category so it is fast for retrieval of data. Thatswhy, we do use data warehouse.

What is Data lake ?

Data lake is the pound of data where data is scattered, can be in the form structured, semi structured or unstructured. From the above meme, Data Engineer collect data from the data lake transform it into the required format and load it into the mean stream or send it to the data scientist so that he can use this data to perform calculation build prediction models etc

What is ETL ?

Extract Transform Load is the technique that a Data Engineer use to get the data from different resources and different format like data can be in the form of csv, html, excel, image, video, txt etc
The data engineer extract this data from different resources than transform it according to his use-case. Then load it to the machine learning model or send it to the data scientist.

This concept is the back bone of data engineering.

So after the transformation of data we use some tools to show the data.

Like for dashboard, we use BI tools to show the data into the live Dashboard. Such as Power BI etc

For the future specification and sorting of data, we use Data mart.

What is Data Mart ?

Data mart is a chunk of data warehouse where all the categorized data of specific need is stored, resulting quick response and fast analysis of data. In other words, we can say that, it is a subset of a data warehouse that is focused on a single subject. No doubt, the size of a Data mart is relatively small in 1Gbs but data warehouse Data capacity is more likely in 100Gbs. They are often used to provide users in an organization with easy access to particular data.

Let's Summerize with the help of perfect example :

Consider, we want to perform a sentimental analysis of Twitter. So, the data is in unstructured format. We extract data from the different Twitter resources and load it to the Data lake After that, the data is clearify and send it to the Data warehouse then send it to the Data Mart where we get very very specific category data which we than load into the Analysis Software or BI Tools. This model contains two part as:
Online transaction processing (OLTP) and other be Online Transaction Analysis processing (OLAP).

Have you noticed that there is a lots of process pipeline passing from one phase to another phase. So, don't worry let me introduce you with Lakehouse

What is Lakehouse ?

Lakehouse is a combination of Data warehouse and Data Lake. It contains every type of data including unstructured format and can have all the features of data warehouse to perform analysis itself. Data Lakehouse offers the benefits of both a data warehouse and a data lake, including data agility, scalability, cost efficiency and performance. Data Lakehouse also provides the ability to process and analyze data in real-time and at scale, enabling organizations to make better, faster decisions.

It's easy to manage, less administration, less redundancy and smooth process.

OLTP vs OLAP :

OLTP (Online Transaction Processing) is a type of system used to manage and process data from transactions. It is typically used to record data from transactions such as sales, purchases, and payments. OLTP systems are optimized for fast, real-time data access and updates. (Calculation Part)

OLAP (Online Analytical Processing) is a type of system used to analyze data from multiple perspectives. It is typically used to analyze data from multiple sources and generate reports to help make informed decisions. OLAP systems are optimized for complex queries and analysis. (Analysis Part)

As, their is alway a tread of data security and cost effectiveness so this issue is resolved with the help of Data Mesh a decentralization system to store data.

Data Mesh :

It is an architecture that focuses on the decentralization of data and the empowerment of teams to make data-driven decisions. Data meshes are designed to make data accessible to multiple teams, departments, and applications, with the goal of providing a single source of truth. Decentralization means that data is not stored on a single place which cause data safety, security and scalability, it can also reduce the risk of data loss. The data mesh architecture allows for data to be stored, managed, and processed in distributed systems.

These are the basics of data engineering concepts that you must know as a beginner.

Moving towards part 02 (Intermediate Concepts)

Habits of Billionaires

Muhammad Rameez — Mon, 09 Jan 2023 19:00:09 +0000

Wake Up Early: Billionaires typically start their day early, often before 6am.
Read: Billionaires typically read books, magazines, and industry publications to stay informed.
Exercise: Billionaires understand the importance of physical fitness and make time for exercise.
Network: Billionaires often use their contacts to create business opportunities and build their wealth.
Invest: Billionaires know that investing is one of the best ways to grow their wealth.
Take Risks: Billionaires are not afraid to take calculated risks in order to reap the rewards.
Think Big: Billionaires look beyond the current landscape and set lofty goals.
Prioritize: Billionaires are able to focus on the most important tasks and let the rest go.
Stay Focused: Billionaires stay focused on their goals and don’t get distracted by the small stuff.
Give Back: Billionaires understand the importance of giving back to the community and often donate to charities.

Data Lake vs Data Warehouse

Muhammad Rameez — Sun, 28 Aug 2022 11:20:00 +0000

Data Warehouse and Data Lake are two different methods to store data for different purpose and it is used by people of different skills. But I assure you that both are entirely different in their purpose. Let's make it clear the major difference between Data Lake and Data Warehouse.

DATA LAKE :
Data Lake is used to store the Row Data. The Data maybe in Semi-Structured form but can retrieve in Structured, Un-Structured and Semi-structured form. Like the data retrieval can be in the form of direct Q&A or it can be an images of customer feedback etc.

Difference between Structured, Unstructured and Semi-Structured Data:

Structured Data are in the form of Row and Table. It is well Synchronized and We'll Managed. This Data can be easily Fetch from the Database or Data Warehouse.

Unstructured Data on an other hand, is Scattered and not well Managed. This Data Mostly in the form of Graph, Images, Videos or in XML format.

Semi-Structured Data is not Scattered or not Well managed. However, it is some how easy to find data and query the result.

In Data Lake, Data is in Massive Amount. It can be Petabyte or Zetabyte of Data. But still Data is Cost Effective because if we had wrote data into data lake, it can update easily but in the case of data Warehouse, this trick doesn't work.

It is very costly to update data in data Warehouse. Due to large amount of data in the data lake, the analysis is very Difficult and Time taking. Time is only concise If the data is in catalogue. The data lake is used by data scientist and data engineer. The major used of data lake is in big data and real time analysis of live Dashboard.

DATA WAREHOUSE :
In the data warehouse the data is in specific order and the specific data is used for the specific purpose only.

Data warehouse contain the data mostly in structured form also the size of data is small as compared to data lake. Companies use Data Warehouse instead of Data lake because of low amount of data and the more analytical power of Data Warehouse. This small amount of data is transformed through ETL process which can be done upon Database. Resulting, Data analysis is very optimised as compared to data lake. As I told you earlier that updation of data is very costly in data warehouse. The data warehouse is used by data analyst, business analyst, data scientist and machine learning engineer.

There is a huge story that involves in data lake and data warehouse that how the data is transfer from one place to another, that medium is called data pipeline. In that topic I will also cover the qualities of data that must insure by the data engineer to work on it this will be covered in the next blog and I will attach the link in this blog as well. Till then keep Aiming, Keep Practicing.

Connect with me 😊

LinkedIn
GitHub
Others

DataBase vs Data Warehouse

Muhammad Rameez — Tue, 23 Aug 2022 17:33:32 +0000

Database and Data Warehouse are not the same at all. The major difference in both management systems is the type of storing data. The need for Data warehouses first occurred in 1970. There are two types of data.

Operational Data
Strategic Information

In a Nutshell, the Analysis of data is stored in Data Warehouse and the day-to-day use data is stored in the Database. Let's take a deep dive.

What's Database ?
A database is a storage management system of data where we can store terabytes of data or even Penta and zettabyte of data that we can use afterward to perform different actions at any time. The database is also of many types :

Structural Database
Unstructured Database
Hierarchical Database
Graph Database

What's Data Warehouse ?
Data Warehouse is used to store the insights of Business. Data Warehouse holds Strategies and information about the business. Here stores the real-time data.

Why not we use Database instead of Data Warehouse:
Because if we delete the data from the database it can be undone for the future use but in the case of data warehouse, Data cannot be deleted easily and the analysis perform are much faster and accurate while using data Warehouse. We can use different tools to get results of different purpose. Mostly SQL, Python, Power BI are used for extraction of data from the data warehouse.

What is the need for Data Warehouse:

To get to know about the age group and gender of customers for the specific product
To know about the demand for the product
To know about the sales index of the product

QUICK DEFINITION OF DATA WAREHOUSE
Data Warehouse is :

Time Variant
Integration
Non-Versatile
Subject Oriented

Details:
You can store data insight of thousands of years, Integration means data is in Structural Format and your data cannot be changed if it is stored in the warehouse however we can add or delete the data permanently. Subject Oriented means data for specific requirement usage can be managed separately.

Data Warehouse is the important Topic in Data Engineering or Data Science Domain.

Connect with me :
LinkedIn
Github