Creating a Connector between AWS and Microsoft SQL Server in Python

One of the challenges I came across recently was to create a connector that would collect specific columns in a csv at AWS and load to a table in Microsoft SQL Server.

To make it easier for someone that might come across the same challenge in the future, I am sharing the code here:

import pyodbc as odbc
import pandas as pd

df = pd.read_csv(".csv")

# Selecting columns to be imported
columns = (['col1', 'col2', ...])

df_data = df[columns]
records = df_data.values.tolist()


# Connection SQL Server
DRIVER = ''
SERVER_NAME = ''
DATABASE_NAME = ''
USER = ''
PASSWORD = ''

server = ''
database = ''
username = ''
password = ''
conn = odbc.connect('DRIVER={};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()


# Creating Cursor Conectors and inserting data.

sql_insert = '''
    INSERT INTO dbo.table
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, GETDATE())
'''

try:
    cursor = conn.cursor()
    cursor.executemany(sql_insert, records)
    cursor.commit()
except Exception as e:
    cursor.rollback()

finally:
    print('Task is complete.')
    cursor.close()
    conn.close()

I hope this helps!

Thank you for reading

Churn Prediction using Artificial Neural Netwoks.

One of the most exciting areas of machine learning is prediction.

To predict events gives you an almost unfair advantage to competitors.

Example:

What if a bank could predict the customers that will leave the bank?

With the Churn Prediction Model below that is possible!

This models uses predefined parameters to show the customers that are most likely to leave the bank.

The model is trained using the following data from the history of the bank's customers:

• Customer ID
• Surname 
• Credit Score
• Location
• Gender
• Age
• Tenure
• Balance
• Number of products the customer consumes
• If the customer has Credit Card
• Customer Estimated Salary,
• The customer exited the bank already?

With model trained, the data from current customers is loaded to make the prediction.

It is actually a quite short and "simple" code in python.

Here it is:

# Artificial Neural Network

# Importing the libraries
import numpy as np
import pandas as pd
import tensorflow as tf
tf.__version__

# Part 1 - Data Preprocessing

# Importing the dataset
dataset = pd.read_csv('dataset.csv')
X = dataset.iloc[:, 3:-1].values
y = dataset.iloc[:, -1].values
print(X)
print(y)

# Encoding categorical data
# Label Encoding the "Gender" column
from sklearn.preprocessing import LabelEncoder
le = LabelEncoder()
X[:, 2] = le.fit_transform(X[:, 2])
print(X)
# One Hot Encoding the "Geography" column
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder
ct = ColumnTransformer(transformers=[('encoder', OneHotEncoder(), [1])], remainder='passthrough')
X = np.array(ct.fit_transform(X))
print(X)

# Splitting the dataset into the Training set and Test set
from sklearn.model_selection import train_test_split
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = 0.2, random_state = 0)

# Feature Scaling
from sklearn.preprocessing import StandardScaler
sc = StandardScaler()
X_train = sc.fit_transform(X_train)
X_test = sc.transform(X_test)

# Part 2 - Building the ANN

# Initializing the ANN
ann = tf.keras.models.Sequential()

# Adding the input layer and the first hidden layer
ann.add(tf.keras.layers.Dense(units=6, activation='relu'))

# Adding the second hidden layer
ann.add(tf.keras.layers.Dense(units=6, activation='relu'))

# Adding the output layer
ann.add(tf.keras.layers.Dense(units=1, activation='sigmoid'))

# Part 3 - Training the ANN

# Compiling the ANN
ann.compile(optimizer = 'adam', loss = 'binary_crossentropy', metrics = ['accuracy'])

# Training the ANN on the Training set
ann.fit(X_train, y_train, batch_size = 32, epochs = 100)

# Part 4 - Making the predictions and evaluating the model

# Predicting the result of a single observation



print(ann.predict(sc.transform([[1, 0, 0, 600, 1, 40, 3, 60000, 2, 1, 1, 50000]])) > 0.5)



# Predicting the Test set results
y_pred = ann.predict(X_test)
y_pred = (y_pred > 0.5)
print(np.concatenate((y_pred.reshape(len(y_pred),1), y_test.reshape(len(y_test),1)),1))

# Making the Confusion Matrix
from sklearn.metrics import confusion_matrix, accuracy_score
cm = confusion_matrix(y_test, y_pred)
print(cm)
accuracy_score(y_test, y_pred)

Thanks for reading!

Choosing the best Cloud Service Provider

Choosing the cloud provider for your company or the company your work for can be tricky.

There are many factor to take into account, so here is a quick overview of the research I did for the company I currently work for.

The first step I took was to look for as much cloud providers as I could find (I was really surprised with the number of companies that provides cloud services).

From there, I selected the top three.  If you are a little informed about this area the results is pretty obvious for you. The top three are: Amazon Web Services, Microsoft Azure and Google Cloud Platform.

"Everybody knows that those are the top 3! Wouldn´t be smarter to skip the first step?"

NO! 

First, it doesn´t hurt to double-check. Second, this area is in constant change, even though AWS has been the leader for a very long time, AWS competitors are constantly investing and innovating, so it is always good to work with updated information.

(In my opinion, the best source of information regarding  IT solutions and services is Gartner Peer Insights)

Next, I did the following rating for each provider:

• Customer review rating.
• Location of data centers
• Cost of support (This item can vary wildly and it must be discussed internally with your team to find the optimal support package for your needs).
• How friendly the services is with the tools our company already uses.
• Expertise of our team.

We decided for AWS since it is the best rated among users, cost of support is attractive, it is easy to integrate with our current tools, and our team has experience with it.

Microsoft Azure does have better location of data centers to us, but it is not worth to use it considering the facts mentioned above.

Thank you for reading!

Are you Data Driven or Data Inspired ?

Much is said nowadays about the importance of a company being data driven.

Digital Transformation, Industry 4.0, data science and artificial intelligence are constant subjects in our feeds.

Noting how priceless a complete data architecture is, companies are investing heavily to implement solutions that make them digitally mature. (I recommend this reading about digital maturity).

But is it enough to implement this complete data structure and hire qualified professionals to get a scientific, pragmatic, and impartial data for decision-making?

As you might have guessed... not always.

I found this article  that presents a fantastic perspective on data-driven mindset. 

The human component cannot be completely removed. The influence of a decision-maker will always be above data analytics and what it provides. That influence is frequently (and naturally) biased.

For example, imagine you finish your long day of work and decide to order food online, because you don’t feel like cooking. You check around sellers online and spot one with a 4.5 rating out of 5 and a significantly short delivery time.

Ideally, you should not use only that 4.5 and the delivery time to drive your decision.

But unless this seller’s products are something you do not enjoy, your decision is pretty much done. You did not define what you want before looking at the data available in the delivery app, so you let the data in the delivery app give you the “best fit” for you.

According to Cassie Kozyrov, that is because once we have seen the answer, we are inclined to pick the most convenient question. If the first thing we do is poke around in our data without knowing what we want, our decision will be, at best, something she likes to call...

Data Inspired.

To quote the brilliant Cassie Kozyrkov:

“Turns out humans interact with data selectively to confirm choices we’ve already made in our heart of hearts. We find the most convenient light in which to see evidence, and we do not always know we are doing it. Psychologists have a lovely name for this: confirmation bias.”

Many people only use data to feel better about decisions they have already made.

This was crystal clear during the last Brazilian elections (and US elections), the voters were very polarized and shared many news, information and data that confirmed their political position and ignored any evidence that was against their position.

(I mean, don’t we still get emails, videos, memes etc. by WhatsApp, Facebook and other social medias from family and friends with “proof” of their political views?).

To avoid confirmation bias, make a pragmatic and scientific decision, that is really data driven and not just data inspired, decision makers should define the criteria before looking into the data.

So, are you Data Driven, or Data Inspired?

Data Lake e Data Warehouse.

 Data Lake is defined as the storage or repository of a large volume of data, it can be structured or semi-structured or unstructured.

It is the large central "body", which feeds the various functions that require data.

A good view of a data lake that I found is the one below:

 Interesting how Data Science, even though it is an exact science, it still requires a good capacity for abstraction, right? 😄

Okay, we have our definition of  Data Lake, so ... What is Data Warehouse? Isn't it the same thing?

No...

Data Warehouse, is where we will store structured data that has already been processed for a particular function. While at the Data Lake, we have all kinds of data (raw or not) available for use.

Look at this image, how cool!:

Visualizing things makes it easier to understand, right? By the way, later we will talk about Data Visualization, something very interesting!

That's it for today!

Thanks for the visit!

SQL & NoSQL

 Your company (or the company you work for) certainly has data available. For example:

- Sales amount

- Fixed and variable costs

- Customer data (name, contact, products purchased, purchase date, etc.).

What can we do with this data?

We can use BI (Business Intelligence) techniques to extract relevant information and answer important questions such as:

- Is the company financially healthy?

- Is the price of the products within the ideal range?

- Will customer X benefit from any product other than what he currently consumes?

- What is the sales trend for the coming years or months?

Among many other questions ...

The secret to extracting relevant information is asking the right questions.

Once you have defined what you want to know and with the data in hands, we start the Data Engineering processes.

Data Engineering is defined (in a very simplistic way) in turning "raw data" into information that is useful.

For that it is necessary to know how to Extract, Transform and last, but not least, Load this data in safe and easily accessible places. These procedures are known by the acronym ETL.

Before starting the data extraction we have to identify whether the database is Relational or Non-relational.

Relational Database

Briefly, the relational database stores the data in tables. There is an intuitive relationship between the data in the table and that between the tables themselves.

Let's look at an example:

In the example above, we see a table containing, on each line, information about one particular customer. This format allows queries that return answers to simpler questions such as:

Which customers have a Silver subscription? Which customer is located in Los Angeles? etc.

This type of database is also known as SQL, which stands for Structured Query Language. SQL is a language with which relational databases are programmed.

With specific commands it is possible to make queries, delete, update, edit and add new data.

Seems practical doesn't it?

But let's put this relational database to the test of day-to-day activities ...

What if we wanted to add a second phone number for a specific customer? For example, a cell phone number? We will also add the position of the people that we know what position they hold and the number of employees in each company (from which we have this information).

Notice that we don't know all cell phone number or their position and the number of employees from all companies served.

 This quickly causes the database become "bloated", with several empty spaces in different tables and makes the processes of query, extraction etc. extremely complex.😓

But calm down! Not everything is lost!

Let's take a look at the Non-Relational Database ...

The Non Relational Database,  stores data in an optimized way, not only im simples tables.

Also known as NoSQL, which stands for "Not Only SQL", it allows for slightly different queries.

There are four types of non-relational databases:

1. Document-oriented databases - also known as document storage, this database is designed to store, retrieve and manage document-oriented information. Document databases often combine each key with a complex data structure (called a document).

2. Key-value  - This is a database that uses different keys, where each key is associated with only one value in a collection. Think of it as a dictionary. This is one of the simplest database types among NoSQL databases.

3. Wide column  - this database uses tables, rows and columns, but unlike a relational database, the names and format of the columns can vary from line to line in the same table.

4. Charts - a chart database uses chart structures for semantic queries with "nodes", "borders" and "properties" to represent and store data.

"Okay, so what? How are we going to solve the problem of our bloated and inefficient database?"

One option is the document-oriented database. So a customer record can have how many phone number we want in a document with its particular features. Storage is optimized for every need. 😀

Cool huh?

Start Here!

 Welcome to the Practical Data blog!

Here you will find information about how you can use data analysis, processing, transformation techniques and tools to get useful information/insights.

The goal here is to be as practical as possible, so we will have content here that is formatted to be assimilated as easily as possible by beginners. However, if you have no knowledge of data in general, it is necessary to get informed a little before proceeding to the practical examples.

I recommend reading about it here.

The idea here is to popularize the great importance and potential of data in general . A fact that I believe is not generally known to people.

"But why should I care about data?"

 To answer that question, I recommend reading this article.

A brief presentation:

My name is Thiago Lyra Rocha, I am a data engineer at V4 Company and a Computer Science student at the University of London. I was a sales representative for 6 years and I fell in love with data science in general when looking for technological tools that could help my sales process.

I started to study the subject by myself through MOOC's (Massive Open Online Courses ). I noticed its importance and potential and I decided to shift my career from sales to data engineer.

My main skills are data cleaning/modeling, ETL, Cloud (mainly AWS including but not limited to S3, Redshift, Lambda, and Athena) SQL, Python, R and machine learning.

Thank you for your visit !