Algae Education Services

Setup Virtual Private Cloud in GCP - LAB

2024-09-21T17:41:00.000-07:00

My First VPC in Google Cloud

Virtual Private Cloud networks help you build the house for all your resources, and help provide the networking for all of your services.

Problem Statement:

You have been tasked with creating a test VPC network with a public subnet in the IOWA region. Your company would like to provision and test resources as they wish in this environment. In the end, you will provision a VPC network that meets their exact need.

Steps :

Create the VPC
From Google Cloud console's main navigation, choose VPC Network.
Click Create VPC Network. (In a production environment, you would choose the applicable project before creating the VPN Network.
Give your VPC a name.
On Subnets, click Custom. Give a unique name to your subnet.
Set Region to US-Central-1
Set you IP Address range to 10.0.1.0/24.
Click Done.
Click Create.

My First VPC is ready with its subnet

Machine Learning with Big Query

2024-09-21T16:57:00.000-07:00

Bigquery Studio for Training the model

I'm preparing to train a logistic regression model using a publicly available dataset in Bigquery. I will use inbuilt functions availabe in BigQuery

Steps:

Log in to Google Cloud Platform and Open BigQuery.
Create the dataset in BigQuery
Create and Train the ML Model
Evaluate the ML Model
Predict Using the ML Model

Create the BigQuery Dataset

From the Google Cloud console main navigation (hamburger menu on the top left), select BigQuery. (Click DONE to close the "Welcome" dialog box if it is displayed.)
Click the vertical three-dot menu next to the pinned project (in the Explorer) to View actions.
Select Create dataset.
For Dataset ID, enter acg_bqml.
For Data location, select us.
Click CREATE DATASET.

Create and Train the ML Model

Run a SQL function to create and train a logistic regression model.
Add the following query to the Query editor.

This query first creates and trains the model with the dataset/table first_bqml.transactions_model.
The query then declares the model_type as a logistic regression.
The query selects the columns declared within the SELECT statement.
Data is selected from the public bigquery-public-data.google_analytics_sample.ga_sessions_* dataset, where the * indicates you'll query a set of tables sharded by date.
The WHERE clause indicates that tables between August 1, 2016 and June 30, 2017 will be queried:

CREATE MODEL `first_bqml.transactions_model`

OPTIONS(model_type='logistic_reg') AS

SELECT

IF(totals.transactions IS NULL, 0, 1) AS label,

IFNULL(device.operatingSystem, "") AS os,

device.isMobile AS is_mobile,

IFNULL(geoNetwork.country, "") AS country,

IFNULL(totals.pageviews, 0) AS pageviews

FROM

`bigquery-public-data.google_analytics_sample.ga_sessions_*`

WHERE

_TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

At the top, click RUN. It will take a minute or two for the query to process.
In the Results tab at the bottom, click GO TO MODEL.
Select the TRAINING tab. Observe the Loss is decreasing, while the Learn rate is increasing.

Evaluate the ML Model

Evaluate the predicted values against the current data via SQL.
Go back to the Query editor, and replace the existing query with the query below.
This query will evaluate the model and is similar to the previous query.
Note that the SELECT statement will include all fields from the evaluated model.
Additionally, observe the inclusion of the ML.EVALUATE statement, which will evaluate the newly created model:

SELECT

FROM

ML.EVALUATE(MODEL `first_bqml.transactions_model`, (

SELECT

IF(totals.transactions IS NULL, 0, 1) AS label,

IFNULL(device.operatingSystem, "") AS os,

device.isMobile AS is_mobile,

IFNULL(geoNetwork.country, "") AS country,

IFNULL(totals.pageviews, 0) AS pageviews

FROM

`bigquery-public-data.google_analytics_sample.ga_sessions_*`

WHERE

_TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))

In the Results tab at the bottom, observe the fields returned, which evaluate the model's accuracy and precision.

[{
"precision": "0.468503937007874",
"recall": "0.11080074487895716",
"accuracy": "0.98534315834767638",
"f1_score": "0.17921686746987953",
"log_loss": "0.046242211011772519",
"roc_auc": "0.98174825174825175"
}]

Predict Using the ML Model

Use BigQuery ML SQL to predict the number of transactions for various countries.
Back up in the Query editor, replace the existing query with the query below to make predictions.
The initial SELECT statement retrieves the country column and computes a sum of the predicted_label column from the ML.PREDICT function.
The nested SELECT, FROM, and WHERE clauses are repeated, with the results grouped by country and ordered by total_predicted_purchases:

SELECT
country,
SUM(predicted_label) as total_predicted_purchases
FROM
ML.PREDICT(MODEL `first_bqml.transactions_model`, (
SELECT
IFNULL(device.operatingSystem, "") AS os,
device.isMobile AS is_mobile,
IFNULL(totals.pageviews, 0) AS pageviews,
IFNULL(geoNetwork.country, "") AS country
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY country
ORDER BY total_predicted_purchases DESC
LIMIT 15

In the Results tab at the bottom, observe the total_predicted_purchases column in the output. Note the United States has the most predicted purchases, followed by Taiwan, and then Canada.

Normalization and Decomposition

2024-09-11T09:30:00.000-07:00

Normalization

Window Functions in SQL Server PPT

2024-09-11T09:15:00.000-07:00

Window function

Module 1 Introduction to Database Management System
Module 2: Database Design
Module 3: DBMS | Relational Model
MODULE 4: DBMS | SQL Language
Module 5: DBMS | Transactions

What is Cloud Function in GCP

2024-09-08T01:24:00.000-07:00

How Cloud Functions Works

Google Cloud Functions is a serverless event-driven managed platform for building and connecting cloud services.

Cloud Functions is a serverless service, which means that the underlying infrastructure is automatically allocated as needed.
This cloud service is event-driven.
Cloud Functions is a fully managed platform.
Google Cloud handles pretty much everything for you. All you need to provide is the actual function.
The primary benefit is that Cloud Functions connects and extends services.
No server management, no provisioning, no configuring, no maintaining.
Benefit of being serverless is that scaling on-demand, whether up or down, is automatic.
Because Cloud Functions is all about the code, you only pay for when that code executes.
The code is available in a wide range of flavors, including Node.js, Python, Go, Java, .NET, Ruby, and PHP.

General Cloud Functions workflow.

The event triggers a Cloud Function.
The code in the Cloud Function executes
Outputs a result.

Workflow Example

First an image is uploaded to a Cloud Storage bucket
That action in the Cloud Storage triggers a Cloud Function.
When the function is executed, it calls the Vision API.
The Vision API extracts the text from the message and posts its results in a Pub/Sub topic, which in turn, triggers another Cloud Function.
This particular function calls Cloud Translate, and that API translates the extracted text.
The API stores its results in a different Pub/Sub topic, which triggers our third and final Cloud Function.
And this Cloud Function saves the results in a different Cloud Storage bucket.

Here we see 3 different Cloud Functions, linking together 4 different services numerous times. It's worth noting that the code for all the functions in this workflow can be contained in a single file.

Cloud Function can play a part in especially when it comes to connecting and expanding existing services, both on and off the Google Cloud Platform.

Cloud Functions can integrate with those services off GCP, and it's all done through

Available APIs:

Applications programming interfaces. APIs, such as those from GitHub, Slack, and Twilio.
Mobile backends. Cloud Functions have always had direct connection to Google's mobile application development platform, Firebase, and you can trigger functions based on changes to your backend database, like a new user signing up and adding a record as well as events from analytics, authentication, or even remote configuration to the entire app.

IoT Backends:

It is a specific area of stream processing that deserves a special callout. As the internet of things continues to grow exponentially, data from these devices, whether we're talking traffic monitors, thermostats, or home smart hubs can trigger any number of Cloud Functions.

Unstructured data:

Cloud Functions can be integrated to react to detected images or categorize video clips for easy search and retrieval.
Cloud Functions can work with other services, like the Cloud Natural Language API, to perform sentiment analysis of text and trigger appropriate alerts and responses.

Cloud DataLab or Jupyter Notebook

2024-09-08T00:34:00.000-07:00

Cloud Datalab

An interactive tool for data exploration, analysis, visualization, and machine learning.

What is Cloud Datalab?

As a GCP service, it's basically a preexisting technology that gets wrapped in some GCP conveniences.
That preexisting technology is called Jupyter Notebooks.

Jupyter Notebooks an interactive webpages, look a bit like a web-based text editor,
Jupyter Notebooks can contain documentation, code, and most importantly, elements which are the results of compiled code.

Datalab/Jupyter elements could include graphs, visualizations,or just the results of some mathematical calculation.

So I can say, Data lab is a simple notebook in which we can add Some text to explain what my notebook is all about

I can also add some Python code right inside the notebook, and then I can actually run that code and the results will be displayed inside my notebook.
Datalab notebook, something called a kernel process is launched on the VM hosting the notebook.
The DataLab kernel process can execute code within the notebook and access GCP services like BigQuery or ML Engine.
Everything you do while working on your notebook exists within a session run on this kernel.
Outputs from one section of code can be used in the next section of code.
As its mixed media support in Jupyter Notebooks/Data lab, they are a fantastic way to share and collaborate on information.
Datalab/Jupyter Notebooks have built-in support for a variety of Python graphing and plotting libraries.
You can easily share notebooks with other people, allowing them to run calculations for themselves with their own instance of the notebook.
Cloud Datalab configures a Google Source Repository for your notebooks, which is automatically cloned onto the persistent disk attached to your Datalab instance.

If Jupyter Notebooks are so great on their own, why do we need Cloud Datalab?

It's all about convenience.

Google's Datalab command line tool manages the lifecycle of a Datalab instance, which hosts your notebooks and kernel processes.
It can quickly create a Datalab VM in seconds without you having to worry about downloading, installing, and configuring the correct software.
Datalab creates the Git repo for your notebooks in Google Cloud Source Repositories, can be cloned onto the persistent disk of the Datalab instance.
Changes to notebooks can then be committed back to the repo. You can also safely delete a Datalab instance while retaining its persistent disk.
You can then use the existing disk when you create a new Datalab instance.

To Create Google Cloud Datalab, follow the below instructions

Enable Cloud Source Repositories API (If your repository is not created, this option is disable for use)
Open Google Cloud shell and run the below command to Create DataLab VM

gcloud components update
gcloud components install datalab
datalab create datalab-demo

Say yes in option for SSH key and copy output URL to open Datalab instance

Import Data in Mongo using Python

2024-08-25T21:48:00.000-07:00

PY-MONGO DATA IMPORT

STEPS

Install Mongodb

Download MongoDB “https://www.mongodb.com/try/download/community
Install Mongodb from GUI
Download and Install Python “https://www.python.org/downloads/”
Install Py-Mongo from command prompt using command from command prompt "python -m pip install pymongo"Collect Connection String

Install

Python [using command prompt "pip install notebook"]
Jupyter [using command prompt "jupyter notebook"]

Data Preparation

Prepare a csv file with name “data.csv”

Install

Pymongo

Execute the Python code

Import the Data and Validate
Your CV is loaded successfully Mongodb database “CSV” , collection name “data_csv”
Connect to MongoDB compass and validate

Code To be Executed from Jupyter Notebook

/Python Code to Import Data/

#Install Py-Mongo from command prompt using below "python -m pip install pymongo"
#My Local MongoDB string: "mongodb://localhost:27017"

import csv

from pymongo import MongoClient

#Connect to MongoDB

client = MongoClient("mongodb://localhost:27017")

#Name of You database

db=client["CSV"]

#Name of your collection

collection =db["cdv_data_4"]

#Path to your CSV file

csv_file ="data.csv"

#Open the CSV File

with open(csv_file,"r") as file:

reader=csv.DictReader(file)

# Iterate each row in csv file

for row in reader:

#Insert rows in Mongodb

collection.insert_one(row)

if (collection.count_documents({})==2):

print("CSV data imported into MongoDB successfully !")

else:

print("Something went Wrong!!")

/********************************/

Validate The Data in MongoDB Compass

Connect with MongoDB using Local host
Check databases
Open database CSV
Check Collection CSV_data_4

Inserting data In Mongodb via Python

2024-08-24T22:14:00.000-07:00

Py-Mongo - Data Insertion

Demo will show how to insert one or multiple records in mongodb using python code. Here we will use mongodb commands for insert triggered from Python

Insert_One:

Insert only one document

Insert_Many:

Insert multiple document

Steps:

Setup Mongodb and collect connection strings
Setup Python opne Jupyter
Executing Python code
Validate the Data in MongoDB

Step 1.

Install MongoDB

Step 2.

Install Python

Step 3.

Execute Python Code

/*Python Code To insert One Document**********/

#Import Librarys

import pprint

from bson.objectid import ObjectId

from pymongo import MongoClient

#Connect to MongoDB

client = MongoClient("mongodb://localhost:27017")

# Get Reference to database

db = client.algae

# Get a reference to the 'emp' collection

emp_collection = db.emp

#Prepare Data and store in the variable

new_emp = {

"account_holder": "Linus Torvalds",

"account_id": "MDB829001337",

"account_type": "checking",

"balance": 50352434

}

# Write an expression that inserts the 'new_account' document into the 'accounts' collection.

result = emp_collection.insert_one(new_emp)

# Print DocumentID inserted

document_id = result.inserted_id

print(f"_id of inserted document: {document_id}")

client.close()

/***************************/

/*Python Code To insert One Document**********/

#Import Librarys

import pprint

from bson.objectid import ObjectId

from pymongo import MongoClient

#Connect to MongoDB

client = MongoClient("mongodb://localhost:27017")

# Get Reference to database

db = client.algae

# Get a reference to the 'emp' collection

emp_collection = db.emp

#Prepare Data and store in the variable

new_accounts = [

{

"account_id": "MDB011235813",

"account_holder": "Ada Lovelace",

"account_type": "checking",

"balance": 60218,

{

"account_id": "MDB829000001",

"account_holder": "Muhammad ibn Musa al-Khwarizmi",

"account_type": "savings",

"balance": 267914296,

]

# Write an expression that inserts the documents

result = emp_collection.insert_many(new_accounts)

document_ids = result.inserted_ids

print("# of documents inserted: " + str(len(document_ids)))

print(f"_ids of inserted documents: {document_ids}")

client.close()

/******************************/

Step 4

Validate the data using Mongdb compass or from console using query
db.emp.find({'key':'value'})

Configuring resources for Azure SQL platform-as-a-service

2024-08-04T09:37:00.001-07:00

Azure PASS SQL
Purchasing Model and Service Tier

Configuring resources for PASS services involves two different performance-related aspects

The purchasing model.
The service tier

The purchasing model:

It is where we configure direct allocation of CPU, memory, storage space, and possible storage IO operations per second to the Azure SQL offering that we are configuring.

The service tier:

This defines the maximum amount of resources that could be allocated to our database, the type of storage used for our data, as well as the high availability methodology Azure SQL will use behind the scenes to keep our databases or instances up and running.

The performance sources available to our Azure SQL solution are governed by the specific purchasing model and service tier that we have chosen to use.

We only have 2 purchasing models,

DTU: Measuring resource allocation by database transaction unit,or DTU
vCores: Allocating by virtual CPU cores

For service tier, our options are:

Basic
Standard
General Purpose,
Premium
Business Critical
Hyperscale.

Note: The underlying infrastructure for Standard and General Purpose, and Premium and Business Critical, respectively, are practically identical, which is why they are group

When provisioning Azure SQL platform-as-a-service resources, the actual service tiers that we will be able to choose from will depend on the purchasing model that we have selected.

DTU model: Basic, Standard or Premium service tiers.
vCore purchasing model: General Purpose, Business Critical, and the Hyperscale service tiers.

The settings for both purchasing model and service tier can be easily reconfigured, often with only a very brief interruption of service. You can even decide to change to a different purchasing model or service tier entirely, though changing service tiers might take a little longer, as the data will most likely need to be transferred to a different type of storage.

Note: Databases using the Hyperscale tier cannot ever be changed to a different service tier.

Elastic pools:

Resource configuration is performed at the pool level, not at the level of any member databases.
These pool-level resources are then allocated to the member databases as needed, automatically by the Azure SQL service.
Minimum and maximum resource levels can be configured for member databases collectively, not individually.
Nor can all of the collective pool members ever use more resources than have been configured for the entire pool.
elastic pools will automatically allocate resources to their member databases as needed, but are only able to do so within their configured limits.

Elastic Pools in Azure and DTUs?

2024-08-04T09:37:00.000-07:00

Elastic Pools and DTUs? How's that work?

Elastic Pools manage resource allocation to multiple Azure SQL databases.
Elastic pools may use either the DTU or vCore purchasing models, and all of their member databases use the same purchasing model as the pool. So you can't have a vCore database in a DTU Elastic Pool.
eDTUs

We refer to DTUs allocated to elastic pools as eDTUs. eDTUs aren't really a different thing than DTUs.
It's just a term to distinguish the potential capability of an elastic pool.
eDTUs simply represent the maximum number of DTUs that the elastic pool can allocate to any of its member databases within that elastic pool.
Once an eDTU has been allocated to a database, it's simply referred to as a DTU once again.

The range of pool resources that may be allocated to individual database pool members can be controlled, and the pool will automatically allocate or deallocate resources to member databases as needed within your configured per database limits.
When configuring elastic pools to use the DTU purchasing model, the Basic service tier can be used.
With elastic pools using the Basic tier, you have several different eDTU classes to choose from, which naturally affects the maximum amount of resources available to the elastic pool, not to the individual pool member databases.
The resource limits for single databases still apply to databases that are members of an elastic pool.
Single databases using Basic DTU can have no more than 5 DTUs and no more than 2 gigabytes of storage.
So while elastic pools at the Basic service tier, depending on their selected eDTU class, might have up to 16 eDTUs to allocate to their member databases and up to 156 gigabytes of total allocatable storage, each of the member databases may not be allocated more than the 5 DTUs or 2 gigabytes of storage that is allowed to a single database at that Basic class.
Elastic pools using the Basic service tier may support up to 500 member databases regardless of the selected DTU class.

Methods of hosting SQL Server databases within the Azure ecosystem.

2024-08-04T08:08:00.000-07:00

Microsoft SQL Server databases

in Azure ecosystem

Microsoft collectively refers to these different options as the Azure SQL service family.

There are 3 deployment options:

Running SQL Server on an Azure virtual machine
The Azure SQL Database service,
The Azure SQL Managed Instance service.

1. Running SQL Server on a virtual machine

It provides 100% compatibility with any version of SQL Server that you might be using on-prem, which extends to the usual options for interacting with the server's operating system.
This is an infrastructure-as-a-service offering.
While administrators have full control of the operating system and the services that they choose to install,it also means they're responsible for maintaining and securing that operating system, as well as optimizing performance for their application.
Deploying SQL Server on infrastructure-as-a-service provides increased control at the cost of increased management.
Azure has responsibility for managing, maintaining, and securing the underlying virtual machine and virtual network services, what the customer does with those services is entirely their responsibility to manage.

2. Azure SQL Database service.

Azure SQL Database is a platform-as-a-service offering.
Azure handles more of the boring server stuff, allowing us to focus on the database and what we want to do with it.
We can get an Azure SQL database provisioned with minimal server-level configuration. T
There's is still a logical server and it is important.
Setting up Azure SQL databases and servers is extremely fast and simple.
Unlike databases and traditional SQL Server installations, hardware resources like CPU, memory, and disk performance aren't a server-level factor.
Configuration is applied directly to each Azure SQL database and is easily reconfigurable if the performance needs of your database change.
Optionally, databases with varying resource needs may be organized into pools, where collective pool-level resources are allocated to pool member databases as needed.
Azure SQL platform-as-a-service options reduces management overhead and increases operational flexibility by letting go of lower-level control, your time is free to focus on the aspects of your deployment that are more pertinent to your organization.

3. Azure SQL Managed Instance service.

Managed instances are intended to be nearly 100% identical to an on-prem SQL Server running the current enterprise edition.
This offering provides DBAs full control over an SQL Server instance, not just a single database.
Managed instances are a platform-as-a-service offering,
The customers have no visibility or access to the underlying operating system.
I don't have to spend any time taking care of that operating system.
Like a virtual machine, managed instances must be integrated into the customer's virtual network infrastructure.

SQL database solutions in Azure that are not based on Microsoft SQL. While you can always install what you like on a virtual machine, Azure does provide platform-as-a-service offerings for popular open source relational databases, Azure Database for MariaDB, for MySQL, and for PostgreSQL.

Reference:

What is Azure SQL? - Azure SQL | Microsoft Learn

Azure SQL documentation - Azure SQL | Microsoft Learn

Deeper Look At the Azure Database Transaction Unit Purchasing Model

2024-08-04T07:57:00.000-07:00

Database Transaction Unit Purchasing Model

What is DTU?

Database transaction units are an Azure proprietary measure of available resources.
This purchasing model may be used by single databases and elastic pools.
A DTU is an abstraction of representative OLTP database performance encompassing CPU, memory, disk reads, and disk writes in a single unit of measure.
Microsoft developed their own proprietary benchmark testing of what they have deemed typical OLTP database activity to define the typical performance levels of those 4 performance areas (CPU, memory, disk reads, and disk writes).
DTU is often illustrated using a graph that contains a dimension for each of these performance areas.
The typical values determined by Microsoft effectively create a performance envelope on the graph, implying that a DTU is capable of providing that much oomph for a particular performance area, but no more.
DTU is intended to be an abstraction of overall performance, a simplification.
What we do need to understand regarding DTUs is that the DTU values are a proportional measure of available resource power.
Example, if we compare to a database that was allocated 10 DTUs with a database that had been allocated 3000 DTUs, a 3000 DTU database is 300 times as powerful as the 10 DTU database.
There's no way to allocate only additional memory or only improve read IOPS to a database using the DTU purchasing model.

What happens if you make your database work really hard?

Azure uses resource governance to constrain performance within the DTU envelope.
Database workloads will be throttled in order to keep them within the performance envelope for the selected DTU class.
It's very easy to change the DTUs allocated to a database and can be done with very minimal service downtime, assuming you even notice.
So if you've decided that the lack of hardware resources is the cause of your database's problems, you can simply change to a larger DTU class to increase your performance limits.

What's a DTU class?

DTU classes are how Azure identifies the available selections for your Azure SQL resources when you are configuring them to use the DTU purchasing model.

Selection of DTU values are limited to predefined classes within a service tier. Each number on the slider line represents a valid class selection, and each class can also be identified by a class code as well. You cannot select a DTU value outside of these classes. So while you might have decided that 300 DTU is just perfect for your database, here the only selectable classes are either 200 or 400 DTUs.

Basic service tier:

It's only available with the DTU model.
Databases using the Basic service tier are fixed at 5 DTUs, have a maximum database size of 2 gigabytes, and a maximum point-in-time retention of 7 days.

The Standard service tier:

It is the default service tier for the DTU purchasing model
Its supporting options ranging from 10 to 3000 DTUs, and supporting databases from 100 megabytes to 1 terabyte in size, again, depending on the selected DTU class.

The Premium service tier:

It supports from 125 to 4,000 DTUs, databases up to 4 terabytes in size, as well as additional features to enhance the availability of your databases.

Classes of Standard

The lowest 3 classes of Standard (S0, S1, and S2) share some limitations with the Basic service tier.
Databases using these classes use less than a single vCore on the supporting hardware.
That means behind the scenes, they have to share a virtual CPU core with other databases.
Data in these classes is stored using Standard page blobs in Azure storage, which means they're stored on old-school, spinning-platter hard disk drives.
The higher standard DTU classes use Premium storage,which uses solid state drives, giving better I/O performance.
None of these service tier classes support creating columnstore indexes.

What's point-in-time retention?

It's the amount of time in which you can restore an Azure SQL database to any specific point in time.
PITR cannot be increased while using the Basic service tier.
All long-term retention options are supported out to 10 years but only provide full database restore capability.
DTU service tier are not recommended for production. These are relatively inexpensive and useful for development and testing environments, databases using the workloads.

Reference:

Purchasing models - Azure SQL Database | Microsoft Learn

DTU-based purchasing model - Azure SQL Database | Microsoft Learn

Elastic Pools and DTUs? How's that work?

SCRIPT TO LIST All TABLES AND FOREGIN KEY RELATIONSHIP WITH OTHER TABLES

2024-06-10T11:59:00.000-07:00

List All Tables and Their Relationship with other tables via Foreign Key

Script:

WITH CTE AS (

SELECT

OBJECT_NAME(f.parent_object_id) AS ParentTable,

COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn,

OBJECT_NAME (f.referenced_object_id) AS ReferencedTable,

COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumn,

f.name AS ForeignKey

FROM

sys.foreign_keys AS f

INNER JOIN

sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id

)

SELECT

t.name AS TableName,

ISNULL(C.ParentTable, 'Standalone Table') AS ParentTable,

ISNULL(C.ForeignKey, 'Standalone Table') AS ForeignKey

FROM

sys.tables AS t

LEFT JOIN

CTE AS C ON t.name = C.ReferencedTable

ORDER BY

CASE WHEN C.ReferencedTable IS NULL THEN 1 ELSE 0 END,

C.ReferencedTable,

t.name;

Output will look like below:

Use Apache Spark in - Azure Databricks Lab 2

2024-02-10T00:44:00.000-08:00

Use Apache Spark in Azure Databricks

One of the benefits of Spark is support for a wide range of programming languages, including Java, Scala, Python, and SQL; making Spark a very flexible solution for data processing workloads including data cleansing and manipulation, statistical analysis and machine learning, and data analytics and visualization. Azure Databricks is built on Apache Spark and offers a highly scalable solution for data engineering and analysis tasks that involve working with data in files.

Step 1. Setup Azure Databricks Workspace and open notebook

Steps are available: https://saurabhsinhainblogs.blogspot.com/2024/01/azure-databricks-lab-how-to-start-with.html

Connect to Azure Portal
Setup Azure Datbricks
Setup Cluster for Azure Databricks
Open Notebook

Step 2. Prepare data to consume

Shell commands can also use to download data files from GitHub into the Databricks file system (DBFS)

Code:

%sh
 rm -r /dbfs/spark_lab
 mkdir /dbfs/spark_lab
 wget -O /dbfs/spark_lab/2019.csv https://raw.githubusercontent.com/MicrosoftLearning/mslearn-databricks/main/data/2019.csv
 wget -O /dbfs/spark_lab/2020.csv https://raw.githubusercontent.com/MicrosoftLearning/mslearn-databricks/main/data/2020.csv
 wget -O /dbfs/spark_lab/2021.csv https://raw.githubusercontent.com/MicrosoftLearning/mslearn-databricks/main/data/2021.csv

Step 3. Load the data frame and view the data

Data is loaded and visible here but

Data doesn't include the column headers
Information about the data types is missing

Code:

df = spark.read.load('spark_lab/*.csv', format='csv') 
display(df.limit(100))

Step 3. Define a schema for the data frame

Observe that this time, our below problems are solved

The data frame includes column headers
Information about the data types is also available

Code:

from pyspark.sql.types import *
from pyspark.sql.functions import *
orderSchema = StructType([ 
     StructField("SalesOrderNumber", StringType()), StructField("SalesOrderLineNumber", IntegerType()), StructField("OrderDate", DateType()),
     StructField("CustomerName", StringType()),  StructField("Email", StringType()),  StructField("Item", StringType()),
     StructField("Quantity", IntegerType()),  StructField("UnitPrice", FloatType()), StructField("Tax", FloatType())  ])
df = spark.read.load('/spark_lab/*.csv', format='csv', schema=orderSchema)
display(df.limit(100))

df.printSchema()

Step 4. Filter a data frame

Filter the columns of the sales orders data frame to include only the customer name and email address.

When you perform an operation on a data frame, the result is a new data frame (in this case, a new customer data frame is created by selecting a specific subset of columns from the df data frame)
The dataframe['Field1', 'Field2', ...] syntax is a shorthand way of defining a subset of column. You can also use select method, so the first line of the code above could be written as customers = df.select("CustomerName", "Email")

Count the total number of order records
Count the number of distinct customers
Display the distinct customers

Dataframes provide functions such as count and distinct that can be used to summarize and filter the data they contain.

Code:

customers = df['CustomerName', 'Email']
print(customers.count())
print(customers.distinct().count())
display(customers.distinct())

Let’s apply a filter to include only the customers who have placed an order for a specific product by running the following code

Code:

customers = df.select("CustomerName", "Email").where(df['Item']=='Road-250 Red, 52')
print(customers.count())
print(customers.distinct().count())
display(customers.distinct())

Step 5. Aggregate and group data in a data frame

Show the sum of order quantities grouped by product. The groupBy method groups the rows by Item, and the subsequent sum aggregate function is applied to all of the remaining numeric columns

Code:

productSales = df.select("Item", "Quantity").groupBy("Item").sum()
display(productSales)

Show the number of sales orders per year. Note that the select method includes a SQL year function to extract the year component of the OrderDate field, and then an alias method is used to assign a column name to the extracted year value. The data is then grouped by the derived Year column and the count of rows in each group is calculated before finally the orderBy method is used to sort the resulting dataframe.

Code:

yearlySales = df.select(year("OrderDate").alias("Year")).groupBy("Year").count().orderBy("Year")
display(yearlySales)

Step 6. Query data using Spark SQL

The native methods of the data frame object you used previously enable you to query and analyze data quite effectively. However, many data analysts are more comfortable working with SQL syntax.
Spark SQL is a SQL language API in Spark that you can use to run SQL statements, or even persist data in relational tables.
The code you just ran creates a relational view of the data in a data frame, and then uses the spark.sql library to embed Spark SQL syntax within your Python code query the view and return the results as a data frame.

Code:

df.createOrReplaceTempView("salesorders")
spark_df = spark.sql("SELECT * FROM salesorders")
display(spark_df)

Execute SQL code in datbricks

The ``%sql` line at the beginning of the cell (called a magic) indicates that the Spark SQL language runtime should be used to run the code in this cell instead of PySpark.
The SQL code references the salesorder view that you created previously.
The output from the SQL query is automatically displayed as the result under the cell.

Code:

%sql
SELECT YEAR(OrderDate) AS OrderYear,
       SUM((UnitPrice * Quantity) + Tax) AS GrossRevenue
FROM salesorders
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;

Step 7. Visualize data with Spark

Azure Databricks include support for visualizing data from a data frame or Spark SQL query.
It is not designed for comprehensive charting.
You can use Python graphics libraries like Matplotlib and Seaborn to create charts from data in data frames.

Code:

%sql    
SELECT * FROM salesorders

Table of results, select + and then select Visualization to view the visualization editor, and then apply the following options:

Visualization type: Bar
X Column: Item
Y Column: Add a new column and select Quantity. Apply the Sum aggregation.
Save the visualization and then re-run the code cell to view the resulting chart in the notebook.

Step 8. Use Matplotlib in Databricks

Let's utilize the Python Visualization Library for the same'

Code:

sqlQuery = "SELECT CAST(YEAR(OrderDate) AS CHAR(4)) AS OrderYear, \
                SUM((UnitPrice * Quantity) + Tax) AS GrossRevenue \
         FROM salesorders \
         GROUP BY CAST(YEAR(OrderDate) AS CHAR(4)) \
         ORDER BY OrderYear"
df_spark = spark.sql(sqlQuery)
df_spark.show()

Results, which consist of a column chart with the total gross revenue for each year.

Note the following features of the code used to produce this chart:

The matplotlib library requires a Pandas dataframe, so you need to convert the Spark dataframe returned by the Spark SQL query to this format.
At the core of the matplotlib library is the pyplot object. This is the foundation for most plotting functionality.

Code:

from matplotlib import pyplot as plt
# matplotlib requires a Pandas dataframe, not a Spark one
df_sales = df_spark.toPandas()
# Create a bar plot of revenue by year
plt.bar(x=df_sales['OrderYear'], height=df_sales['GrossRevenue'])
# Display the plot
plt.show()

Lets Customize the plot

Code:

# Clear the plot area
plt.clf()
# Create a bar plot of revenue by year
plt.bar(x=df_sales['OrderYear'], height=df_sales['GrossRevenue'], color='orange')
# Customize the chart
plt.title('Revenue by Year')
plt.xlabel('Year')
plt.ylabel('Revenue')
plt.grid(color='#95a5a6', linestyle='--', linewidth=2, axis='y', alpha=0.7)
plt.xticks(rotation=45)
# Show the figure
plt.show()

The figure was created implicitly for you; but you can create it explicitly.

Code:

# Clear the plot area
plt.clf()
# Create a Figure
fig = plt.figure(figsize=(8,3))
# Create a bar plot of revenue by year
plt.bar(x=df_sales['OrderYear'], height=df_sales['GrossRevenue'], color='orange')
# Customize the chart
plt.title('Revenue by Year')
plt.xlabel('Year')
plt.ylabel('Revenue')
plt.grid(color='#95a5a6', linestyle='--', linewidth=2, axis='y', alpha=0.7)
plt.xticks(rotation=45)
# Show the figure
plt.show()

A figure can contain multiple subplots, each on its own axis

Code:

# Clear the plot area
plt.clf()
# Create a figure for 2 subplots (1 row, 2 columns)
fig, ax = plt.subplots(1, 2, figsize = (10,4))
# Create a bar plot of revenue by year on the first axis
ax[0].bar(x=df_sales['OrderYear'], height=df_sales['GrossRevenue'], color='orange')
ax[0].set_title('Revenue by Year')
# Create a pie chart of yearly order counts on the second axis
yearly_counts = df_sales['OrderYear'].value_counts()
ax[1].pie(yearly_counts)
ax[1].set_title('Orders per Year')
ax[1].legend(yearly_counts.keys().tolist())
# Add a title to the Figure
fig.suptitle('Sales Data')
# Show the figure
plt.show()

Step 9. Use the Seaborn Library

Using the seaborn library (which is built on matplotlib and abstracts some of its complexity) to create a chart:

Code:

import seaborn as sns
plt.clf() # Clear the plot area
# Create a bar chart
ax = sns.barplot(x="OrderYear", y="GrossRevenue", data=df_sales)
plt.show()

Seaborn library makes it simpler to create complex plots of statistical data, and enables you to control the visual theme for consistent data visualizations.

Code:

plt.clf() # Clear the plot area
# Set the visual theme for seaborn
sns.set_theme(style="whitegrid")
# Create a bar chart
ax = sns.barplot(x="OrderYear", y="GrossRevenue", data=df_sales)
plt.show()

Like matplotlib. seaborn supports multiple chart types

Code:

plt.clf()  # Clear the plot area
# Create a bar chart
ax = sns.lineplot(x="OrderYear", y="GrossRevenue", data=df_sales)
plt.show()

Step Last. Cleanup Resources

In the Azure Databricks portal, on the Compute page, select your cluster and select ■ Terminate to shut it down.
If you’ve finished exploring Azure Databricks, you can delete the resources you’ve created to avoid unnecessary Azure costs and free up capacity in your subscription.

Explore Azure Databricks - Lab 1

2024-02-10T00:42:00.000-08:00

Explore Azure Databricks

Step 1. Setup Azure Databricks Workspace and open notebook

Steps are available: https://saurabhsinhainblogs.blogspot.com/2024/01/azure-databricks-lab-how-to-start-with.html

Connect to Azure Portal
Setup Azure Datbricks
Setup Cluster for Azure Databricks
Open Notebook

Step 2. Prepare Data to consume

Go to the URL: https://raw.githubusercontent.com/MicrosoftLearning/mslearn-databricks/main/data/products.csv
Download data in CSV file, I named the file product.csv
On the File menu, select Upload data to DBFS.
In the Upload Data dialog box, note the DBFS Target Directory to where the file will be uploaded.
Then select the Files area, and upload the products.csv file you downloaded to your computer.
When the file has been uploaded, select Next
In the Access files from notebooks pane, select the sample PySpark code and copy it to the clipboard.
You will use it to load the data from the file into a data frame. Then select Done.

Step 3. Execute code in Notebook

In the notebook, in the empty code cell, paste the code you copied; which should look similar to this:
Use the ▸ Run Cell menu option at the top-right of the cell to run it, starting and attaching the cluster if prompted.
Wait for the Spark job run by the code to complete. The code has created a data frame object named df1 from the data in the file you uploaded.

Code:

df1 = spark.read.format("csv").option("header", "true").load("dbfs:/FileStore/shared_uploads/a@b.com/products.csv")

Step 4. Display the contents of the data frame

Under the existing code cell, use the + icon to add a new code cell. Then in the new cell, enter the following code:
Use the ▸ Run Cell menu option at the top-right of the new cell to run it. This code displays the contents of the data frame, which should look similar to this:

Code:

print(df1)

df1.collect()

display(df1)

Step 5. Create Data Visualization and Data Profile

Above the table of results, select + and then select Visualization to view the visualization editor, and then apply the following options:
Visualization type: Bar
X Column: Category
Y Column: Add a new column and select ProductID. Apply the Count aggregation.

Step 6. Create and query a table

Save the data frame as table object
SQL code to return the name and price of products in the Touring Bikes category.

Code:

df1.write.saveAsTable("products")

%sql

SELECT ProductName, ListPrice FROM products WHERE Category = 'Touring Bikes';

Now if you want, you can try a couple more options

Step Last. Cleanup Resources

In the Azure Databricks portal, on the Compute page, select your cluster and select ■ Terminate to shut it down.
If you’ve finished exploring Azure Databricks, you can delete the resources you’ve created to avoid unnecessary Azure costs and free up capacity in your subscription.

Azure DataBricks Lab Setup

2024-02-10T00:36:00.000-08:00

My Lab Setup for Azure Databricks practice

Azure Databricks is a Microsoft Azure-based version of the popular open-source Databricks platform. Similarly to Azure Synapse Analytics, an Azure Databricks workspace provides a central point for managing Databricks clusters, data, and resources on Azure.

Step 1. Signup for Azure Free trial

Link: https://azure.microsoft.com/en-in/free

Step 2. Sign in to the Azure portal

Link: https://portal.azure.com

Step 3. Create Azure Resource Group

Step 4. Open Cloud Shell in Azure Portal

Use the [>_] button to the right of the search bar at the top of the page to create a new Cloud Shell in the Azure portal, selecting a PowerShell environment and creating storage if prompted. The cloud shell provides a command line interface in a pane at the bottom of the Azure portal, as shown here:

We will get the option to select a subscription, create/ use a resource group, storage account, file share

Let us create using Cloudshell, though we can create from GUI as well without much hustle.

Step 5. Clone repository from git hub

Here we can clone the existing repository to create Databricks services or we can do manually using the Azure portal also this has some resources to be used in the future like datasets.

In the PowerShell pane, enter the following commands to clone this repo:

rm -r mslearn-databricks -f
git clone https://github.com/MicrosoftLearning/mslearn-databricks

After the repo has been cloned, enter the following command to run the setup.ps1 script, which provisions an Azure Databricks workspace in an available region:

./mslearn-databricks/setup.ps1

I have tried executing the setup.ps1 file in the folder "mslearn-databricks" but no luck. I got a warning Insufficient resources

Step 6. Create Azure Databricks Service

This step would not have been required if setup.ps1 in the previous step had been successful.

I can see Azure Dataribcks service is created along the managed resource group name required for Azure Databricks.

Step 7. Launch Azure Databricks

We will search Azure data bricks in the resource list and click the "Launch Workspace button"

Step 8. Create Cluster in Azure DataBricks

Azure Databricks is a distributed processing platform that uses Apache Spark clusters to process data in parallel on multiple nodes. Each cluster consists of a driver node to coordinate the work, and worker nodes to perform processing tasks. For practice, we’ll create a single-node cluster to minimize the compute resources used in the lab environment (in which resources may be constrained). In a production environment, we typically create a cluster with multiple worker nodes.

In the sidebar on the left, select the (+) New task, and then select Cluster.
In the New Cluster page, create a new cluster with the following settings:

Cluster name: User Name’s cluster (the default cluster name)
Policy: Unrestricted
Cluster mode: Single Node
Access mode: Single user (with your user account selected)
Databricks runtime version: 13.3 LTS (Spark 3.4.1, Scala 2.12) or later
Use Photon Acceleration: Selected
Node type: Standard_DS3_v2
Terminate after 20 minutes of inactivity

Wait for the cluster to be created. It may take a minute or two.

Step 9. Create Pyspark Notebook

In the sidebar, use the (+) New link to create a Notebook.
Change the default notebook name (Untitled Notebook [date]) to a new name
In the Connect drop-down list, select your cluster if it is not already selected. If the cluster is not running, it may take a minute or so to start.

Now you can practice all the code you want and practice for your interview at least for a month

Step Last. Cleanup Resources

In the Azure Databricks portal, on the Compute page, select your cluster and select ■ Terminate to shut it down.
If you’ve finished exploring Azure Databricks, you can delete the resources you’ve created to avoid unnecessary Azure costs and free up capacity in your subscription.

Neo4J Update Command Day 3

2023-06-24T22:14:00.001-07:00

Neo4J Commands Lab Day 3

Update and Set Commands:

The SET clause is used to update labels on nodes
The SET clause is used to update properties on nodes and relationships.

Set a Property :

Use SET to set a property on a node or relationship:

MATCH (n {name: 'Andy'})
SET n.surname = 'Taylor'
RETURN n.name, n.surname

Update a Property

SET can be used to update a property on a node or relationship. This query forces a change of type in the age property:

MATCH (n {name: 'Andy'})
SET n.age = 26
RETURN n.name, n.age

MATCH (n {name: 'Andy'})
SET n.age = toString(n.age)
RETURN n.name, n.age

Remove a Property

Although REMOVE is normally used to remove a property, it is sometimes convenient to do it using the SET command. A case in point is if the property is provided by a parameter.

MATCH (n {name: 'Andy'})
SET n.name = null
RETURN n.name, n.age

Remove All Properties Using an Empty Map and =

All existing properties can be removed from a node or relationship by using

SET with = and an empty map as the right operand:

CREATE (p:Person {name: 'Peter'})
MATCH (p {name: 'Peter'})
SET p = {}
RETURN p.name, p.age

Set Multiple Properties Using one SET Clause:

Set multiple properties at once by separating them with a comma:

MATCH (n {name: 'Tom'})
SET n.position = 'Developer', n.surname = 'Taylor'

Set a Label on a Node:

Use SET to set a label on a node:

CREATE (p:Person {name: 'Michael'})
WITH p
MATCH (n {name: 'Michael'})
SET n:German
RETURN n.name, labels(n) AS labels

Set Multiple Labels on a Node:

Set multiple labels on a node with SET and use : to separate the different labels:

CREATE (p:Person {name: 'Kelly'})
WITH p
MATCH (n {name: 'Kelly'})
SET n:Swedish:Bossman
RETURN n.name, labels(n) AS labels

Neo4J Delete Command Lab Day 2

2023-06-24T21:40:00.001-07:00

Neo4J Commands Lab Day 2

Delete Command:

You can use delete command in for scenarios

Delete single node
Delete relationships only
Delete relationships only
Delete all nodes and relationships

To start with Lab, lets quickly create a few nodes and relation ships

Command:

CREATE p = (:Person {name:'Andy'})-[:WORKS_AT]->(:Company {name: 'Neo4j'})<-[:WORKS_AT]-(:Person {name: 'Michael'})
RETURN p

Commands and Syntax for Delete

Delete single node

To delete a single node, I created anode and use the DELETE clause:

CREATE p = (:Person {name:'Tom Hanks'})
MATCH (n:Person {name: 'Tom Hanks'})
DELETE n

Delete Relationships Only

It is possible to delete a relationship while leaving the node(s) connected to that relationship otherwise unaffected.

CREATE p = (:Person {name: 'Laurence Fishburne'})-[r:ACTED_IN]->(:Person {name: 'Andy Fishburne'}) return p
MATCH (n:Person {name: 'Laurence Fishburne'})-[r:ACTED_IN]->()
DELETE r

Delete a node with all its relationships

To delete nodes and any relationships connected them, use the DETACH DELETE clause.

CREATE p = (:Person {name: 'Laurence Fishburne'})-[r:ACTED_IN]->(:Person {name: 'Andy Fishburne'}) return p
MATCH (n:Person {name: 'Carrie-Anne Moss'})
DETACH DELETE n

Delete all nodes and relationships

It is possible to delete all nodes and relationships in a graph.

MATCH (n)
DETACH DELETE n

Neo4j CQL

2023-06-24T21:02:00.000-07:00

Cypher Query Language Commands

Neo4j Read CQL Clauses

MATCH

This clause is used to search the data with a specified pattern.
Syntax: Match (n)

OPTIONAL MATCH

This is the same as match
The only difference being it can use nulls in case of missing parts of the pattern.

WHERE

This clause id is used to add contents to the CQL queries.

START

This clause is used to find the starting points through the legacy indexes.

LOAD CSV

This clause is used to import data from CSV files.

Neo4j Write CQL Clauses

CREATE

This clause is used to create nodes, relationships, and properties.

MERGE

This clause verifies whether the specified pattern exists in the graph. If not, it creates the pattern.

This clause is used to update labels on nodes, properties on nodes and relationships.

DELETE

This clause is used to delete nodes and relationships or paths etc. from the graph.

REMOVE

This clause is used to remove properties and elements from nodes and relationships.

FOREACH

This class is used to update the data within a list.

CREATE UNIQUE

Using the clauses CREATE and MATCH, you can get a unique pattern by matching the existing pattern and creating the missing one.

Neo4j General CQL Clauses

RETURN

This clause is used to define what to include in the query result set.

ORDER BY

This clause is used to arrange the output of a query in order. It is used along with the clauses RETURN or WITH.

LIMIT

This clause is used to limit the rows in the result to a specific value.

SKIP

This clause is used to define from which row to start including the rows in the output.

WITH

This clause is used to chain the query parts together.

UNWIND

This clause is used to expand a list into a sequence of rows.

UNION

This clause is used to combine the result of multiple queries.

CALL

This clause is used to invoke a procedure deployed in the database.

Operators in Cypher Query Language

Mathematical +, -, *, /, %, ^
Comparison +, <>, <, >, <=, >=
Boolean AND, OR, XOR, NOT
String +
List +, IN, [X], [X…..Y]
Regular Expression =-
String matching STARTS WITH, ENDS WITH, CONSTRAINTS

Introduction to Google Kubernetes Engine Cluster

2023-06-17T21:49:00.003-07:00

Clusters in GKE

Anatomy of a Kubernetes cluster

The most simple way to think of Kubernetes is just like any other cluster.
It's a collection of computers joined together to perform some function.
A typical Kubernetes cluster will contain one or more masters and one or more nodes.
Nodes are just communal gang variety computers.
These can be large physical servers or even raspberry pies.

In the case of Google Kubernetes engine, they are virtual machines running in Google compute engine.

Kubernetes Master:

Masters run several components that form what's called the control plane.
This means they're in charge of controlling the cluster.
They make decisions about the cluster.
Such as where to schedule certain workloads.
The master is responsible for the state of a cluster.
It's constantly watching everything to make sure the state is as it should be.

Components of a Kubernetes Master.

API Server:

The API Server is the front end of the control plane it exposes the API for all the master functions.
Every time you communicate with the master or anything else communicates with the master It would be through this API.
Most of the time you'll use google cloud counsel or command line tool but in the background it's always talking to this API.
Once a cluster is up and running your day to day interaction is just going to be with that API Server.
And with GKE there is a good reason you don't need to worry too much about this individual components.

etcd

etcd is Kubernetes own database.
Storing all of it's configuration in state.
We call it a database but really etcd is just a key value store.
It is very well known one that is designed for scale and higher availability.

Scheduler

The Scheduler is responsible for scheduling work loads.
That means is when you want to deploy a container the scheduler will pick a node to run that container on.
The node it picks can be affected by all kinds of factors such as the current load on each available node.
The requirements of your container and some other customizable constraints.

Cloud Controller Manager

The Cloud Controller Manager, is what allows Kubernetes to work with cloud platforms.
Kubernetes itself is an open source project with contributions from lots of large companies
not just Google So it doesn't natively contain functionality for google cloud only.
This manager is responsible for handling things like networking and load balancing as they translate to the product and services of a particular cloud platform.

Kube Controller Manager

Its job is to manage a handful of controllers in the cluster.
The controllers themselves look after things like nodes and a few other objects.

Kubernetes Node

Nodes run components that form the runtime environment.
There basically the workers with a container runtime.
In this node computers, provide the resources of the cluster.
All of the CPU and RAM can be used to run containers
When you deploy a container in Kubernetes the master will pick a node to run it on.
These are going to be our actual virtual machines that will run containers for us.
They're a lot more straightforward than the master.

Component's of Kubernetes node

Kubelet:

It is an agent for Kubernetes.
It communicates with the control play and takes instructions such as deploying containers when it's told to.

Kube-proxy:

It is responsible for managing network connections in and out of the node.

Actual Container Runtime:

Just like when we run our first container in cloud shell the noble run docker as a container runtime to allow it to run containers.

How to build a Kubernetes cluster?

The hard way.

You'll need provision of virtual machines for you master and your nodes.
You'll need to install the Kubernetes software create a network overlay.
Set up certificates so that all of the components can talk to each other secularly.

The easy way.

Use GKE.
GKE provisions and manages the underlying cloud resources automatically.

GKE is a fully managed service

It will create masters and nodes for you to your specification using a container-optimized operating system.
You design your cluster within GKE without having to build or maintain individual masters or nodes.
It comes with built-in automatic upgrading, automatic self-repair.
It has many options for fine grain resource control and cluster scaling.
Its integrated with all of the other products and services Google cloud platform.
You don't even touch the master control plane.
It is completely managed for you.
It's made of compute engine VMs just like your nodes, but they won't even show up in your project.
Google's own CRET are looking after them for you.
Abstracting away the control plane takes away a lot of the operation overhead.

How do we operate and interact with GKE

Google Cloud Console.

Kubernetes functionality is exposed to us to the simple UI of the cloud console.

Command lines for Kubernetis
Kubectl for operating a running cluster

Kubectl allows us to interact with Kubernetes by defining the objects we want to deploy.
These objects are just abstractions of what we want to run and how we want to run it.
Kubectl takes our instructions and passes them to the API server.

Communicate with API Server

Perform their own function to carry out our commands.

Create and manage life-cycle of Kubernetes objects

Everything in Kubernetes is an object and every object has a state.

Fire up your First Container in Google Cloud

2023-06-12T21:46:00.005-07:00

Containers - Lab

Anatomy of a Container

Containers start life as an image.
You can think of Image just like any other packaging format, like a Tarbell.
The difference is that container images, are made up of different layers, one on top of the other.
When you run a container, the image is used, and a small read-write layer is added over the top.
Every time you run a container from an image, you'll get exactly the same behavior.
When a container stops, the read-write layer goes away.

Setting up Container in GCP

We're going to create a Python app in Docker and we'll run this app in a Docker container and learn how to interact with local containers.
We'll tag our Docker image and push it to a registry, and we'll do all this from inside the Google Cloud Console.

Steps:

Open the cloud shell
create a folder myapp and create 3 files in it namely server.py, requirements.txt, Dockerfile
Copy the sample code from url: https://github.com/ACloudGuru-Resources/Course_GKE_Beginner_To_Pro/tree/master/Chapter_One/Lecture_4_Lab/myapp
Change directory to myapp using cd myapp
Build your first container image
See all the images created using
Get the container id with below command
To see all running containers use docker ps
Test the application using curl command
Check whats there in containers using
Enable google container registry api 🡪 enable
Now we can tag the image and push in container registry gcr.io

Code For Lab:

https://github.com/ACloudGuru-Resources/Course_GKE_Beginner_To_Pro/tree/master/Chapter_One/Lecture_4_Lab/myapp

Step 1 & 2:

In Command shell create a folder myapp and create 3 files in it namely and dump the code copied from github

server.py
requirements.txt
Dockerfile

Step 3 : change directory to myapp using cd myapp

Step 4 : build your first container image with below command

Step 5 : see all the images created using

Docker File Explain

We can create a container image with a Dockerfile.

This is just a normal text file
It contain a set of instructions that tell Docker how to build an image.
We can inherit from an image that already exists, like a very minimal Linux distribution, then we might add some other instructions, to copy some files into the image or run other commands.

We can specify in the Docker file, what commands should be executed when the container is running.
Every instruction in the Docker file creates a new layer within the image.
A basic Docker File looks like.

Explain Docker File Code:

"FROM unbuntu: 18.04":

We inherit from a public Ubuntu image (We could inherit from anything here, including our own images.)

"COPY . /app":

We use the copy command to copy everything in the local directory to slash app inside the Docker image. This creates a new layer within the image.

"RUN make /app":

The next instruction executes the make command to compile our application.

"CMD python /app/app.py":

The final Command states that when this container image is running a Python Application should be executed.

Consuming Docker File

We saved this file with the name "Dockerfile' in our local working directory alongside our code.
To build an image with these instructions we use the Docker command line.
The keyword built tells Docker that we want to build a new image minus t lets us add a tag or name to that to refer to it later.
The dot at the end of the command refers to the directory the Docker should build the image from. So, dot means our local working directory.
If you don't specify a location, or you specify a directory that does not contain a Dockerfile, the command will fail.

Visualization of image, once built alongside our Docker File Instructions.

The layers are in reverse order, as it's best to imagine Docker building up adding layers from the bottom to the top.
Each layer gets its own unique ID.
The first and largest layer is our inherited Ubuntu image.
The next layer at about 195k is where we've copied in our application files.
The top most layer, represents what changed when we run the make command inside the image.
The real benefit to developers here is containers promotes smaller shared images.
If we make a change to this container, only the layer that has changed has to be updated. Built updates might only be a few kilobytes of changes.
By inheriting images, we can remain consistent in our bills, and avoid reinventing the wheel with every image we create.

Step 6 : get the container id with below command

Step 7 : to see all running containers use docker ps

Step 8 : test the application using curl command

How to run a simple container:

We simply use Docker run with the name or tag of our image.
We can add minus d which demonizes the process and puts it in the background.
Other options can also be add to Docker when running containers such as mounting volumes and exposing certain ports. This layer will disappear if the container is deleted.

Step 9: check whats there in containers using

Step 10 : enable google container registry api 🡪 enable

Step 11 : now we can tag the image and push in container registry gcr.io

Docker commands

The Docker command provides all the tools we need to manage our containers locally with easy to use verbs like

$ docker ps: Shows us all the running containers.
$ docker logs container-name: Used to view a containers logs
$ docker stop container-name: Used to stop a container
$ docker rm: Used to delte container entirely
$ docker images:
$ docker tag myapp gcr.io/myapp: Tag images
$ docker Push gcr.io/myapp: push images to google registry
$ docker rmi: Remove local images

In Google Cloud projects, we have a built in registry called Google Container Registry, or GCR. If we have an image locally, we can simply add an additional tag to it containing the URL of our registry using Docker tag, and then use Docker push to copy it up to the registry.

Human Communities And The Environment (EVS)

2023-06-09T03:13:00.004-07:00

Human Communities And The Environment

Environmental Studies

Environmental Policies And Practices (EVS)

2023-06-09T03:06:00.000-07:00

Environmental Policies And Practices

Environmental Studies

Environmental Pollution (EVS)

2023-06-09T02:59:00.005-07:00

Environmental Pollution

Environmental Studies

Biodiversity and its Conservation (EVS)

2023-06-09T02:54:00.003-07:00

Biodiversity And Its Conservation

Environmental Studies

Algae Education Services

Setup Virtual Private Cloud in GCP - LAB

My First VPC in Google Cloud

Problem Statement:

Steps :

Machine Learning with Big Query

Bigquery Studio for Training the model

Steps:

Create the BigQuery Dataset

Create and Train the ML Model

Evaluate the ML Model

Predict Using the ML Model

Normalization and Decomposition

Normalization

Window Functions in SQL Server PPT

Window function

What is Cloud Function in GCP

How Cloud Functions Works

Google Cloud Functions is a serverless event-driven managed platform for building and connecting cloud services.

General Cloud Functions workflow.

Workflow Example

Cloud Functions can integrate with those services off GCP, and it's all done through

Available APIs:

IoT Backends:

Unstructured data:

Cloud DataLab or Jupyter Notebook

Cloud Datalab

An interactive tool for data exploration, analysis, visualization, and machine learning.

What is Cloud Datalab?

So I can say, Data lab is a simple notebook in which we can add Some text to explain what my notebook is all about

If Jupyter Notebooks are so great on their own, why do we need Cloud Datalab?

To Create Google Cloud Datalab, follow the below instructions

Import Data in Mongo using Python

PY-MONGO DATA IMPORT

STEPS

Code To be Executed from Jupyter Notebook

/***********Python Code to Import Data***********/

#Install Py-Mongo from command prompt using below "python -m pip install pymongo"#My Local MongoDB string: "mongodb://localhost:27017"

#Connect to MongoDB

#Name of You database

#Open the CSV File

# Iterate each row in csv file

#Insert rows in Mongodb

Validate The Data in MongoDB Compass

Inserting data In Mongodb via Python

Py-Mongo - Data Insertion

Demo will show how to insert one or multiple records in mongodb using python code. Here we will use mongodb commands for insert triggered from Python

Steps:

Step 1.

Step 2.

Step 3.

/*********Python Code To insert One Document******************/

/*********Python Code To insert One Document******************/

Step 4

Configuring resources for Azure SQL platform-as-a-service

Azure PASS SQLPurchasing Model and Service Tier

The purchasing model:

The service tier:

Elastic pools:

Elastic Pools in Azure and DTUs?

Elastic Pools and DTUs? How's that work?

Methods of hosting SQL Server databases within the Azure ecosystem.

Microsoft SQL Server databases

in Azure ecosystem

There are 3 deployment options:

1. Running SQL Server on a virtual machine

2. Azure SQL Database service.

3. Azure SQL Managed Instance service.

SQL database solutions in Azure that are not based on Microsoft SQL. While you can always install what you like on a virtual machine, Azure does provide platform-as-a-service offerings for popular open source relational databases, Azure Database for MariaDB, for MySQL, and for PostgreSQL.

Deeper Look At the Azure Database Transaction Unit Purchasing Model

Database Transaction Unit Purchasing Model

What is DTU?

What happens if you make your database work really hard?

What's a DTU class?

Basic service tier:

The Standard service tier:

The Premium service tier:

Classes of Standard

What's point-in-time retention?

Elastic Pools and DTUs? How's that work?

/Python Code to Import Data/

#Install Py-Mongo from command prompt using below "python -m pip install pymongo"
#My Local MongoDB string: "mongodb://localhost:27017"

/*Python Code To insert One Document**********/

/*Python Code To insert One Document**********/

Azure PASS SQL
Purchasing Model and Service Tier