DEV Community: Claire Maina

Data Engineering 101: Introduction to Data Engineering

Claire Maina — Sun, 21 Aug 2022 19:24:01 +0000

A data engineer designs and builds systems that collect, store, manage and analyze data. Companies collect a lot of information about their business from various resources, and they need data engineers to make this information accessible, structured and usable. A data engineer can build data pipelines, optimize queries, create automated systems, manage data warehouses, and develop data workflows.

Roles of a Data Engineer
• Build data pipelines – this involves collecting data and building data warehouses or data lakes.
• Make data accessible – involves remodeling the data in a way that is easy for all stakeholders to access interpret and manipulate. Excel, Power BI, and Tableau are some of the tools mostly used.
• Optimize queries – involves updating the current queries to meet current business needs.
• Data maintenance – involves testing and maintenance to ensure the system is running smoothly.

Skills needed

Distributed systems: Hadoop
Databases: MySQL
Data processing: Spark
Real-time data ecosystem: Kafka
Data orchestration: Airflow
Data science: pandas (Python library)

Software and Technology Requirements

Cloud account - Google GCP, AWS or Azure.
Python 3, Python IDE and a text editor - VSCode, Anaconda.
SQL server and MYSQL Workbench or DBeaver and DBVisualizer.
Git and version control system

Connecting Jupyter Notebook with Tableau

Claire Maina — Fri, 05 Aug 2022 17:52:00 +0000

The main benefit of connecting your Jupyter notebook with Tableau is that this allows for real-time analysis. To mean that, any changes made to the notebook will be reflected on Google Sheets and Tableau.

What are the steps involved?

1: Create a New Project on Google Developers Cloud

2: Enable Google Sheets and Google Drive APIs

3: Create credentials

4: Google Sheets

5: Code to add to your Jupyter Notebook

6: Tableau Public setup

Step 1: Create a New Project on Google Developers Cloud

Go to Google Cloud and select “My First Project”
This will open a pop-up window. Select “New Project” on the top right side of this pop-up window.
Choose a unique Project Name e.g. new-tutorial. You may leave the Organization field as it is and click on Create

Step 2: Enable Google Sheets and Google Drive APIs

On the Dashboard, click “Library”, on the Left Pane. This will redirect you to API Library.
Search for Google Sheets. This will redirect you to the Google sheets API window.
Proceed to click on “Enable API”.

Follow the above steps to also Enable the Google Drive API.

Note that the 2 APIs have to be enabled for each new project you create.

Step 3: Create credentials

Note: Always check that you are still working on the correct project

On the left pane, select “Credentials”
On the opened window select “+ Create Credentials”
Select “Service Account”. A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs [sic].
On Service Account Details - for the Service Account Name - you can choose a name similar to your project. E.g. new-tutorial. The Service Account ID will automatically update. You may choose to edit it. For the Service Account Description, you may choose to leave this blank as well.
Select “Create and Continue”
On “Grant this service account access to project”, select a role as Owner so that you may have full rights.
Click “Continue”

On “Grant users access to this service account”, you may add other users or groups. If you do not have other users, you may leave it blank. Below are some examples of users or groups.

a. Google Account email: [user@gmail.com](mailto:user@gmail.com),

b. Google Group: admins@googlegroups.com,

c. Service account: server@example.gserviceaccount.com

d. Google Workspace domain: example.com

Click “Done” to proceed
On the newly opened window, there is a Service Account section at the bottom of the page, select “Manage Service Accounts” on the left.
On the table, click on the 3 dots at the end, on the ”Actions sections”.
Select “Manage Keys”.
Click “Add Key” and select “Create New Key”.
Select JSON as the Key Type.
A JSON will be automatically downloaded. It should be like the below:-

Note: Ensure the JSON file is saved in the same folder as your project.

Step 4: Create a Google Sheet

Go to Google Sheets and create a new one.
Click the Share button and add the client-email that was on the JSON file e.g.

"new-tutorial@ new-tutorial-...iam.gserviceaccount.com"

Get the spreadsheet key from the URL. As highlighted below in black.

"://docs.google.com/spreadsheets/d/1l9yMNyoXt.....cZCYACyc/edit#gid=0 "

This Spreadsheet Key will be used in the notebook as shown below.

Step 5: Jupyter Notebook

Back to the notebook.

Import the necessary libraries

# import libraries

import  gspread

from  oauth2client.service_account  import  ServiceAccountCredentials

from  df2gspread  import  df2gspread  as  d2g

Access Google Sheets and Google Drive APIs

# Access the Google Sheets and Google Drive APIs

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name('name_of_your_json_file.json',scopes=scope)

gc = gspread.authorize(credentials)

Code to upload the dataframe to Google Sheets.

spreadsheet_key is, what we copied earlier when we created the Google Sheet.

worksheet_name is, by default named Sheet_1. You can change this on the Google sheet as per your preference.

# Share the dataframe with Google Sheets

spreadsheet_key = '1XccW9EiPSYHRmmESyLTTUnmRdfMDiGlzqnG0_jOmA5Q'

worksheet_name = "Main"

result = d2g.upload(your_dataframe_name, spreadsheet_key, worksheet_name, credentials = credentials, row_names=True)

print(result)

Step 6: Tableau

To download Tableau click here

On the left pane, select “Google Drive”

This will require authentication and it will redirect you to your browser.
Enter your correct details and select Allow.
A pop-up window will appear, where you will select the correct project Google Sheet, as you had saved it.
This will open your data in Tableau as shown below: -

Here is a sample of one of my visualizations created using this process.

SQL JOINS

Claire Maina — Fri, 15 Jul 2022 10:27:26 +0000

SQL Joins

Say for example you have multiple tables. One table, for example, Products, is missing the Supplier Column. But when you check a different table, for example Supplier, you find it has the Supplier Column you need. So how do you get the Supplier Column to the Product table, whilst ensuring all details in the rows are accurate and matching?

This is what we shall cover today - SQL JOINS. Let’s get started:-

What is an SQL Join statement?
Types of SQL Joins

What is an SQL Join statement?

SQL Join statement is used to combine data or rows from two or more tables, based on a related column between them.

Sample Data:-

Table 1 – Products Table

Product_ID	Product_Name	Customer_ID
100	Rice	3
101	Soap	5
102	Bread	2
103	Milk	1
104	Water	4

Table 2 – Supplier Table

Supplier_ID	Product_Name
A01	Bread
A02	Rice
A03	Milk
A04
A05	Soap

Note that for the Supplier table we have a NULL on the Product_Name for the Supplier_ID A04.

Types of Joins

Inner Join - Returns records that have matching values in both tables.
Left Join - Returns all records from the left table, and the matched records from the right table.
Right Join - Returns all records from the right table, and the matched records from the left table.
Full Join - Returns all records when there is a match in either left or right table.

1. Inner Join

As described above, the inner join returns records that have matching values in both tables.

Syntax you shall use:

Output

2. Left Join

As described above, left join returns all records from the left table, and the matched records from the right table.

Syntax:

Result

3. Right Join

As described above, right Join returns all records from the right table, and the matched records from the left table.

Syntax:

Result

4. Full Join

As described above, the full join returns all records when there is a match in either left or right table. MySQL does not support Full Join keywords, hence we shall use UNION to combine the Right Join and Left Join so as to create a Full Join.

Syntax:

Output

To Note:

As seen in the Full Join, the Supplier_ID ‘A04’ did not have a Product_Name, hence the details are marked as NULL even after performing Joins.

Introduction to Data Structures With Python.

Claire Maina — Mon, 21 Feb 2022 21:03:19 +0000

Introduction

Data structures allow us to organize, store, and manage data for efficient access and modification.

In this article, we shall cover in-built data structures and user-defined data structures.

In-Built Data Structures

1. Lists
A list stores multiple items in a single variable. It is defined using square brackets [] and holds data that is separated by commas. The list is mutable, which means we can modify its element after it is created. It is also ordered, which means the items have a defined order, and that order will not change. If you add new items to a list, the new items will be placed at the end of the list. Lists can also contain a mix of different data types. Lists are changeable, meaning that we can change, add, and remove items in a list after it has been created. The first item in a list has index [0], the second item has index [1]. Since lists are indexed, lists can have items with the same value.

2. Dictionaries
Dictionaries are used to store data values in key:value pairs. It is a collection which is ordered and changeable. It is defined using curly braces {} and holds data that is separated by commas. Values in a dictionary can be of any data type and can be duplicated, whereas keys can’t be repeated and must be immutable, which means we can not modify its element after it is created. Note that dictionary keys are case sensitive.

3. Tuples
Tuples are very similar to Lists except that they are immutable. It is defined using round brackets (). Tuple Packing is the creation of a python tuple without the use of parentheses.

4. Sets
A set is an unordered collection of data type that is iterable, mutable and has no duplicate elements. It is defined using curly braces {} or using the function set().

User-Defined data structures

1. Queues

Queue is a linear structure which follows a particular order in which the operations are performed. The order is FIFO which means First In First Out. A good example of a queue, is a queue of shoppers in a supermarket where the shopper that came first to the till is served first. In a queue, we remove the item the least recently added.

Operations on Queue:

Mainly the following four basic operations are performed on queue:

Enqueue: Adds an item to the queue. If the queue is full, then it is said to be an Overflow condition.
Dequeue: Removes an item from the queue. The items are popped in the same order in which they are pushed. If the queue is empty, then it is said to be an Underflow condition.
Front: Get the front item from queue.
Rear: Get the last item from queue.

Implementation

2. Stack
The stack is a linear data structure where elements are arranged sequentially. It follows the mechanism L.I.F.O which means Last In First Out. For example, in browsers the last opened tab will be the top most item in the history. In stack, a new element is added at one end and an element is removed from that end only. The insert and delete operations are often called push and pop respectively.

Below are some of the functions associated with stack:

empty() – Returns whether the stack is empty
size() – Returns the size of the stack
top() – Returns a reference to the topmost element of the stack
push() – Inserts an element at the top of the stack
pop() – Deletes the topmost element of the stack

Implementation using list

3. Linked List
Linked List is a linear data structure where the elements are linked using pointers. Each element of a linked list is called a node, and every node has two different fields:

Data contains the value to be stored in the node.
Next contains a address to the next node on the list.

The first node is called the head, and it’s used as the starting point for any iteration through the list. The last node must have its next reference pointing to Null to determine the end of the list.

Python 101: Ultimate Python Guide

Claire Maina — Mon, 14 Feb 2022 15:41:44 +0000

Python 101: Ultimate Python Guide

Introduction to Python

Python is an interpreted, interactive, object-oriented programming language. It is a popular programming language created by Guido van Rossum, and released in 1991.

It is used for:

Web development
Desktop Application
Automation
Data science, Artificial Intelligence, Machine Learning

Why Python?

Python works on different platforms (Windows, Mac, Linux, Raspberry Pi, etc). It has a simple syntax similar to the English language that allows developers to write programs with fewer lines than some other programming languages.
Python runs on an interpreter system, meaning that code can be executed as soon as it is written. This means that prototyping can be very quick.
Python can be treated in a procedural way, an object-oriented way or a functional way. It uses new lines to complete a command, as opposed to other programming languages which often use semicolons or parentheses.
It relies on indentation, using whitespace, to define scope; such as the scope of loops, functions and classes. Other programming languages often use curly-brackets for this purpose.
Python programs have the extension .py and can be run from the command line by typing python file_name.py.

Concepts we will be going through today:

How to install Python
Your first python program
Comments
Variables
Arithmetic Operators
Comparison Operators
Logical Operators
Control Flow and Conditional operators

1) How to install Python
The most recent major version of Python is Python 3, which we shall be using. In this tutorial, Python will be written in a text editor. It is possible to write Python in an Integrated Development Environment, such as VSCode, Pycharm, Atom, Netbeans or Eclipse which are particularly useful when managing larger collections of Python files.
Here is a guide on how to install python on your machine.

Here is a guide on how to install VSCode.

2) Your first python program
Create a new folder in your machine where you will be saving your python files, for example PythonProjects. Launch the VS code and open the new folder you created, PythonProjects. Create a new python file, helloworld.py file and enter the code below.

print() is a built-in function in Python.

3) Comments
You can add comments to your Python scripts. Comments are important to make sure that you and others can understand what your code is about. To add comments to your Python script, you can use the # tag. These comments are not run as Python code, so they will not influence your result.

4) Variables
Variables are containers for storing data values. Python has no command for declaring a variable. Just type in the variable and when values will be given to it, then it will automatically know whether the value given would be an int, float, or char or even a String. Note that variables are case-sensitive and the first character can either be character or underscore and not a digit. Once you create such a variable, you can later call up its value by typing the variable name.

Output

5) Arithmetic Operators
They are used to perform mathematical operations like addition, subtraction, multiplication, and division.

Output

6) Comparison Operators
Compares the values. It either returns True or False according to the condition.

Output

7) Logical Operators
They perform Logical AND, Logical OR, and Logical NOT operations. It is used to combine conditional statements.

Output

8) Control Flow and Conditional operators
So far the code has consisted of sequential execution, in which statements are always performed one after the next, in exactly the order specified. Frequently, a program needs to skip over some statements, execute a series of statements repetitively, or choose between alternate sets of statements to execute. That is where control structures come in. A control structure directs the order of execution of the statements in a program (referred to as the program’s control flow).

if Statement

if statement accepts boolean values – if the value is true then it will execute the block of statements below it otherwise it will not. As we know, python uses indentation to identify a block.
Let us use a real-life scenario “If the weather is sunny, I will not wear a jacket”

Output

if-else Statement

Example: “If the weather is not sunny, wear a jacket”

Output

elif statement

Example: “If the weather is sunny, no jacket, else if(elif) the weather is cold, wear a jacket, else wear a light jacket”.

Output