DEV Community: Chidiebere Ogujeiofor

Scenario 2: Mastering Backfilling for New dB Columns – Update Like a Pro!

Chidiebere Ogujeiofor — Wed, 25 Sep 2024 09:56:35 +0000

Cover Photo Credit: Pixels

Introduction

One of the most interesting challenges I’ve encountered is when adding new fields to an existing database table and needing to back-fill values for those fields. Often, back-filling involves performing some sort of aggregation or calculation—sometimes involving other tables or columns—and then updating the target table with those calculated values.

Let’s explore a concrete example using some sample data.

Problem Example

Imagine we decide to add two new fields to our events table:

number_of_ticket_types: This will hold the count of different ticket types associated with each event.
number_of_tickets_available: This will represent the total number of tickets still available for the event, calculated as the sum of ticket.quantity minus ticket.quantity_sold for each event.

These fields can be useful if, for instance, you are retrieving a list of events and need to display ticket information without requiring a user to click into the details of each event.

We’ll start by adding a migration that adds these new columns to our events table:

Fields	Type	Constraints
title	text
owner_id	text	FK -> Users
id	VARCHAR(30)	PK
number_of_ticket_types	INT	default:0
number_of_tickets_available	INT	default:0

However, when these new fields are added, their values will be set to 0 for all existing events, even though we already have tickets in the tickets table. Now, we need to backfill those fields with the correct data.

A First Attempt at Backfilling

One way to backfill these new fields is to retrieve data from the tickets table, perform calculations, and then update the events table accordingly.

Here’s an example of how to backfill the number_of_ticket_types field:

async function backFillTicketTypes() {
  const ticketTypeResult = await TicketsModel.findAll({
    attributes: [
      "event_id",
      [sequelize.fn("count", sequelize.col("id")), "ticket_type_count"],
    ],
    group: ["Tickets.event_id"],
  });

  /*
    Result would look like this:
    ticketTypeResult = [ 
        {event_id: 'eventId1', ticket_type_count: 10},
        {event_id: 'eventId2', ticket_type_count: 4},
        {event_id: 'eventId3', ticket_type_count: 3},
    ]
  */

  for (const eventTicketTypeObject of ticketTypeResult) {
    const { event_id, ticket_type_count } = eventTicketTypeObject;
    await EventsModel.update(
      {
        ticketTypeCount: ticket_type_count,
      },
      {
        where: {
          id: event_id,
        },
      }
    );
  }
}

The second field, number_of_tickets_available, can be backfilled similarly by calculating the total available tickets:

async function backFillNumberOfTicketsAvailable() {
  const ticketsAvailableResult = await TicketsModel.findAll({
    attributes: [
      "event_id",
      [sequelize.fn("sum", sequelize.col("quantity")), "total_tickets"],
      [
        sequelize.fn("sum", sequelize.col("quantity_sold")),
        "total_tickets_sold",
      ],
    ],
    group: ["Tickets.event_id"],
  });

  /*
    Result would look like this:
    ticketsAvailableResult = [ 
        {event_id: 'eventId1', total_tickets: 10, total_tickets_sold: 2},
        {event_id: 'eventId2', total_tickets: 23, total_tickets_sold: 12},
        {event_id: 'eventId3', total_tickets: 14, total_tickets_sold: 8},
    ]
  */

  for (const numberOfTicketAvailable of ticketsAvailableResult) {
    const { event_id, total_tickets, total_tickets_sold } =
      numberOfTicketAvailable;
    await EventsModel.update(
      {
        number_of_ticket_types: total_tickets - total_tickets_sold,
      },
      {
        where: {
          id: event_id,
        },
      }
    );
  }

  // Run update query...
}

What's Wrong with This Approach?

While this method works, it’s quite verbose and involves multiple database queries:

A query to retrieve ticket_type_count for each event.
A query to retrieve total_tickets and total_tickets_sold for each event.
An UPDATE query for each event that has been created in the database. This is a really expensive task if many items have been created in the database previously

In the above example the total number of dB calls is dependent on the total number of events that exists in the application.

Which would greatly slow down our server

Note: If we use the techniques from the previous article(Senerio 1), we would be able to reduce the number of UPDATE query calls to 1 per function. Which would reduce the total queries to four

A Better Solution

A more efficient way to handle this problem is to use a single SQL query to perform both updates in one go. We can achieve this using a WITH clause (common table expression, or CTE) and a single UPDATE query.

To learn more about CTEs, check out [this article(https://dev.to/chidioguejiofor/whats-with-cte-53ao)]

Here’s how we can do it:


WITH update_cte AS (
  SELECT
    event_id,
    COUNT(id) AS ticket_type_count,
    SUM(quantity) - SUM(quantity_sold) AS number_of_tickets_available
  FROM
    tickets
  GROUP BY
    event_id
)
UPDATE
  events
SET
  number_of_ticket_types = update_cte.ticket_type_count,
  number_of_tickets_available = update_cte.number_of_tickets_available
FROM
  update_cte
WHERE
  update_cte.event_id = events.id;

Explanation

The WITH update_cte clause calculates both the ticket_type_count and number_of_tickets_available in one go using COUNT and SUM functions, grouped by event_id.
In the UPDATE statement, we join the events table with the CTE (update_cte) based on event_id.
The fields in events (number_of_ticket_types and number_of_tickets_available) are updated with the values from the CTE.

Why Is This Better?

This approach performs both updates in a single query, eliminating the need for multiple database queries and significantly improving performance. Instead of managing two separate functions and multiple steps, we only need to maintain one query that efficiently backfills the data.

Additionally, the same concept can be applied to future backfills if similar aggregate fields are added to the events table.

Conclusion

Backfilling data after adding new fields to an existing table is a common task in application development. While the initial approach of making multiple queries works, it can be optimized significantly using SQL’s powerful aggregation and CTE features. By consolidating updates into a single query, we reduce database load, improve performance, and make the code more maintainable.

Scenario 1: When You Need to Update Multiple Rows with Different Values – The Smart Way!

Chidiebere Ogujeiofor — Tue, 24 Sep 2024 18:36:11 +0000

Here's an improved version of the introduction for Scenario 1:

Updating multiple rows in a database is a common task when managing data, whether you're setting the same value across several rows or handling different updates for each one. While the former is straightforward, the latter can quickly become complex and lead to performance issues due to excessive database calls.

In this article, we’ll explore how to efficiently update multiple rows with different values in a single query to minimize database overhead and improve performance.

Problem Example

Imagine you want event owners to update the prices of multiple tickets at once. The event owner provides an array of objects containing the ticket IDs and their new prices. The input might look something like this:

{
  "event_id": "my-event-conference-id",
  "ticketUpdateArr": [
    { "id": "id1", "newPrice": 400 },
    { "id": "id2", "newPrice": 700 },
    { "id": "id3", "newPrice": 790 },
    { "id": "id4", "newPrice": 1500 }
  ]
}

A straightforward implementation to handle this update might look like this:

async function bulkUpateTickets(ticketUpdateArr) {
  for (const updateObj of ticketUpdateArr) {
   await TicketsModel.update(
      {
        amount: updateObj.newPrice,
      },
      { where: { id: updateObj.id } }
    );
  }
}

In this code, we loop through each item in the array and make a separate database call to update the price for each ticket.

While this works, it’s not efficient. Each database call is expensive, and as the number of tickets grows, this function becomes significantly slower.

How Can We Improve This?

We can improve performance by moving the logic to the database and utilizing SQL CASE statements. This allows us to update all tickets in a single query.

Here’s how the query would look:


UPDATE
    tickets
SET
    amount = CASE
        WHEN id = 'id1' THEN 400
        WHEN id = 'id2' THEN 700
        WHEN id = 'id3' THEN 790
        WHEN id = 'id4' THEN 1500
        ELSE amount
    END
WHERE
    id IN ('id1', 'id2', 'id3', 'id4');

This query updates all the ticket prices in one go. The parts that come from user input are the WHEN ... THEN conditions and the list of ids in the WHERE clause.

Now, let's update our JavaScript code to dynamically build the query and make just one database call.

Improved JavaScript Code

async function bulkUpdateTickets(ticketUpdateArr) {
  if (ticketUpdateArr.length === 0) return;

  let caseStatement = "";
  const ticketIds = [];
  const replacements = {};

  for (let index = 0; index < ticketUpdateArr.length; index++) {
    const updateObj = ticketUpdateArr[index];
    const ticketIdReplacement = `ticket_id_${index}`;
    const ticketValueReplacement = `ticket_value_${index}`;

    caseStatement += /*sql*/ `
      WHEN id = :${ticketIdReplacement} THEN :${ticketValueReplacement}`;

    replacements[ticketIdReplacement] = updateObj.id;
    replacements[ticketValueReplacement] = updateObj.newPrice;
    ticketIds.push(updateObj.id);
  }

  const rawQuery = /*sql*/ `
    UPDATE
        tickets
    SET
        amount = CASE ${caseStatement}
        ELSE amount
    END
    WHERE id IN (:ticket_ids);
  `;

  await sequelize.query(rawQuery, {
    type: QueryTypes.UPDATE,
    replacements: {
      ...replacements,
      ticket_ids: ticketIds,
    },
  });
}

This code builds a dynamic SQL CASE statement based on the input array, constructing the WHEN ... THEN clauses and the list of ticket ids for the WHERE clause. Here's an example of the generated SQL:


UPDATE
    tickets
SET
    amount = CASE
        WHEN id = :ticket_id_0 THEN :ticket_value_0
        WHEN id = :ticket_id_1 THEN :ticket_value_1
        WHEN id = :ticket_id_2 THEN :ticket_value_2
        WHEN id = :ticket_id_3 THEN :ticket_value_3
        ELSE amount
    END
WHERE
    id IN (:ticket_ids);

The :ticket_id_n and :ticket_value_n placeholders will be replaced by the corresponding values in the replacements object provided to Sequelize. This dynamic construction not only prevents SQL injection but also improves performance significantly.

Performance Gains

Although the code still loops through the input array to build the query, the big improvement is that it only makes one database call for all updates. This drastically reduces the number of database interactions, making the application much faster, especially when handling a large number of tickets.

Conclusion

In my experience building backend applications and APIs, it’s common to encounter scenarios where we need to update multiple records in the database. While looping through each item and making individual calls works, it's not scalable. By using CASE statements and bulk queries, we can efficiently update multiple records in a single call, resulting in significant performance gains.

Turbocharge Your App: Slash DB Calls with Raw SQL Magic!

Chidiebere Ogujeiofor — Tue, 24 Sep 2024 18:34:59 +0000

Introduction

In my experience building backend applications, one of the key factors in improving performance is reducing the number of I/O operations—such as database calls and external API requests—your app makes. Every I/O operation adds latency and can bottleneck your application's overall performance.

Of all I/O operations, database queries are the most common, and if not optimized, can severely slow down your app. In this series of articles, I will explore common scenarios where an application might make multiple database calls unnecessarily and demonstrate techniques to optimize those operations.

We will use a PostgreSQL database for the examples, but the techniques discussed can be applied to any SQL-based database.

Articles in the series

In this series, I dive into two key scenarios where we often end up making more database queries than necessary—and how to optimize them.

Scenario 1: Multi-Row Updates with Unique Values

When updating multiple rows with different values, we usually fire off individual update queries for each row, which can slow things down. In this article, I break down the issue and show how to tackle it more efficiently.

Scenario 2: Backfilling Data for New Columns

Adding a new column to a table with existing data? Backfilling that column can be a challenge. In this article, I demonstrate a raw SQL approach that simplifies the process.

Sample Data

For our examples, we will use a simplified schema of a ticketing application. The app allows event owners to create and manage events, while users can purchase tickets through an API.

Here are the sample tables we'll work with:

Users Table

Fields	Type	Constraints
first_name	text
last_name	text
id	VARCHAR(30)	PK

events Table

Fields	Type	Constraints
title	text
owner_id	text	FK -> Users
id	VARCHAR(30)	PK

tickets Table

Fields	Type	Constraints
event_id	text	FK -> events
id	VARCHAR(30)	PK
title	text
amount	INT
quantity	INT
quantity_sold	INT

quantity: represents the number of that tickets that is available at the beginning. This would be like the number of seats in a concert
quantity_sold: is the number of tickets that has been sold.

See you in the next article

Let's explore the first type of problem in the next article.

Crunch the frontend

Chidiebere Ogujeiofor — Wed, 16 Aug 2023 08:58:29 +0000

In the previous article, I talked about the different things that I tried and how I came up with an initially deployment strategy of hosting a PWA on Google PlayStore using the TWA technology

Let's talk about the work that was done on the project so far.

Note that the date of this writing is Aug 16th.

Day 7(Aug 9th) to Day 9(Aug 11th)

In this period, I started by deleting some of the junk react native code that I added to the project when trying experimenting merging both react native and next js in the same project.

After that, I went on the implement components for the connections, notification, settings and events page. Previously I had done the empty states of these pages but now I implemented the page with content.

Note that since we do not have a backend api yet all of these are just staitic dummy pages.

Also whent on to create modals that would allow users to edit their profile and invite people to the application.

Day 10(Aug 12th) to Day 11(Aug 13th)

These 2 days were weekends and since I had made good progress in the project, I didn't touch it at all and used that as an opportunity to catch up with friends.

It is very important to have maintain this balance. If not one is at the risk of burn out.

It was also an opportunity to relax my mind and take a break everything

Day 12(Aug 14th) to Day 16(Aug 17th)

I didn't do much on these days because I fell ill on Aug 15th and I am currently on medication while writing this.

However, the current plan is to test out that deployment assumption and convert the app to a PWA before moving forward.

I would like to test that idea as early as possible so as not to be surprised when I am actually ready to deploy.

Next steps

If I can do create the PWA quickly then I would start working on the backend.

My strategy there is to comsume the API as I create them.

This is the list of things that the API should be able to do:

1.Authentication
a. Login with Google
b. Login with Password and Email
c. Signup with email and password

Create,Update and Delete Events
Send connection request
Accept connection request
Remove connection
Send emails to users: a. Setup email sending with AWS SES b. Create templates for Authentication c. Create templates for upcoming events reminder d. Create templates for
Send emails when: a. User registers b. Beginning of the month with a list of upcoming events for that month
User can edit profile

Looks almost like I haven't even started

Conclusion

Had a bit of set back with the illness but hopefully I'll be back very quickly and conver more grounds in this one

Journey of a thousand miles

Chidiebere Ogujeiofor — Wed, 09 Aug 2023 12:25:45 +0000

I recently started working on a personal project whose aim is to help users remember their birthdays, anniversaries and any special dates that is important to them.

The app includes features like creating events, sharing and connecting with others.

My goal in this series is to document the decisions and thought process I have had throughout the journey so as to be able to share with others in the future.

Think of this as an online journal dedicated to this project.

Project Technical Requirement

The project is to be built as a mobile app(android and iOS) and web app (https://never4get.app). We believe that the best usecase/experience is the native app.
We would have a backend that would service both projects
Login should preferably be with Google and Apple ID

Initial Plan

My very first goal is to build the application as quickly as possible and get it to the hands of users so as to be able to get quick feedback and iterate the solution.

Basically following the lean startup ideas.

Since I am an expert in building web apps(both backend and frontend) but haven't written a single line of mobile app code before this project, I decided that it would be great if I could build a website and then use tools that can convert a web app to a native app with out writing any code.

This was my initial plan and my first goal was to test this possibility as quickly as possible.

Day 1 to 2

The first day of development was August 3rd 2023. And in 3 days I was able to build the welcome page, sign in page, login with google and onboarding screen after sign in.

The part that took me a tiny time to figure out was the animation in the welcome page and how Next 13 app directory works(this is the first project where I was using it) including the difference between server-side and client side components.

This rapid progress made me realise that I could finish the frontend very quickly if I continued. I however thought it was now time to test out the idea of converting the website to a mobile app

Day 3(Aug 5th): Experimenting

On this day I started researching the different options of converting a website to a native app easily.

For a long time I had thought that Enzyme was the tool for this job but I quickly realised that it is used to convert websites to desktop apps and not native mobile app(android/iOS).

On further investigation I found Capacitor a tool that promises to do this well. Looked like something that would come out well.

After running a few experiments and actually build the website and run it using capacitor and immediately found that the native experience was worse than it was on the web.

These findings brought me to the final conclusion that the best way to move forward was to use a specialised tool and since I had a lot of experience working with React, I settled for React Native.

The first experiment I ran was to see if I could keep the react-native and react code in the same codebase but I couldn't get this to work (as it kept throwing an "invalid Router" error). Thus, I decided that to separate these into 2 different projects.

Day 4(Aug 6th) - Day 7(Aug 9th): React Native

I was able to leverage both the React Native documentation to setup the project using expo Go and configure tailwind(note that tailwind is also used in the web).

I really dislike the way React Native styles component by default but since I was able to add tailwind, I found that I could re-use a lot of the styles from the web.

Thus, I was able to copy and paste components from the web project and change their styling and "elements" to use native elements.

However, there was still a lot of work. I couldn't get the login with Google to work, rendering SVGs required extra effort, a simple transition: was implemented using a whole new component(Animation.View) etc. It required a lot of googling to understand what was going on.

Even though I couldn't get the login with Google to work, I was able to roughly get to the place I was on web. I did the welcome, onboarding, signin and events screen in this time. Copying, pasting(and a lot of), refactoring code from the web to get it to work in the early morning(aka 12 am ish) of August 9th

Day 7(Aug 9th): Way forward

As at the time of this writing we are currently at 1pm Aug 9th. I have thought about the best strategy to maintain speed and momentum in this project and I am aware that using React Native is going to slow things down a bit.

There are still unknowns like:

Login with Apple
Notification API on native apps
Payments and how it would work
etc.

Thus, I have decided to build the web app end to end this month, launch it(aka show a few people how it works), get feedback as quick as possible then gradually work on the native android and iOS from next month.

Conclusion

So far so good. Good progress has been made and a final plan hatched.

It is also interesting to see how much can happen in 8 days.

Automatically notify your team when you deploy new changes in a Python project

Chidiebere Ogujeiofor — Sun, 12 Dec 2021 15:17:52 +0000

Photo from ThisisEngineeringPexels

Introduction

One of the things that I love about the Agile methodology is how it gives you the ability to make frequent tiny improvements to your project. An essential advantage of “being” Agile is deploying small changes frequently that add value to our customers or improve some internal processes.

In scrum, we would usually have a demo after every sprint in which we show new features/changes to other stakeholders(design, data, non-technical teammates etc.). However, in these demos, we rarely go into the minute details of the new changes (e.g. a column being renamed in a database table or the meaning of a column changing). These omissions could lead to problems with teams that depend on your work.

Imagine that you have a web team(that adds and maintains features on your web app) and a data team(that analyses the data generated by your web app to perform different tasks). Let’s say the web team deploys a feature that replaces a boolean is_customer field with a role field(that has enum CUSTOMER, SHOP_OWNER, ADMIN). The problem here is that during demos at the end of the sprint, the web team would often communicate the “new features” that were added and may forget to talk about the column change.

After deploying such changes, the existing queries of the data team would suddenly stop working or give incorrect results.

Also, imagine that a non-technical teammate(e.g. a marketer) was to ask you when the web team deployed the “google login” feature to the website; where would you find that information?

We experienced these problems in my team at Stears, and I think our solutions to all of them are related. In this article, I would like to share a high-level view of how we solved this.

Below is a summary of the problems we found:

Demos usually omits crucial minute changes to the other team members, and we need a way to notify them when we deploy automatically.
There was no place to see the history of changes that we made in the past

Here is how the rest of the article structured:

How we solved this
Link between changelog and commit convention
Choosing a commit convention
Enforcing a commit convention
Generating a changelog
Sending the message to slack
Sending different messages from the pipeline

How we solved this

We deploy new changes to production in our system by creating a release tag. Once a release is created, a manual job in our pipeline, which when run deploys our app to production.

Since our deployment strategy involved creating tags and changelogs contain information about the changes that have been deployed in each release, we decided to solve the problems stated above by maintaining a changelog in our project.
Adding a changelog led us to choose a commit convention, enforce it, and add commands that extract the most recent changes from our changelog and send it to slack whenever we deploy.

Link between changelog and commit convention

Maintaining a CHANGELOG lets us know the releases(and the changes within each release) that have been made historically and allows us to extract the most recent updates easily.

For us, we also wanted our changelog to have:

a link to the ticket so we can see more details about each change
a link that shows us the file changes that were made while implementing that feature

Below is an example of how our changelog looks:

I have used dummy messages here.

Notice how the changelog shows the date the web team deployed each release and how it groups the changes based on their type(bug fixes, features, automation, etc.)

Manually updating this information to your changelog is going to be painful.

Since we usually add a description of changes made in our commit messages, it makes sense to use these messages to automate the way we generate our changelog. To do this, however, we needed to have a consistent commit message format(a commit convention) so as to easily extract the data we want to be in our changelog.

Next, let’s talk about how we chose our commit convention.

Choosing a commit convention

When choosing a commit convention, you have to decide what data you want your commit to have. There is a standard called conventional commit which has the following structure

<type>[optional scope]: <description>
<new-line>
[optional body]
<new-line>
[optional footer(s)]

The “type” section is used to state whether the commit is a feature(feat), refactor, ci change, etc.
The scope is one or two words that give a general context of the task. This is usually the name of the epic the commit is part of. E.g. authentication, notification, subscription etc
The “description” is one sentence or phrase that describes the task
The “body” is where you add more details of the task
Footers are where you specify extra details like whether the task is a breaking change, ticket number etc.

For us in my team, however, we decided to make slight changes to this by
replacing the ‘footer’ part with a required issue_number and
ensuring that the optional body is in bullet points(when provided).

We did this because we wanted to ensure that the issue number was always in the message we sent to our teammates on deployment.

Below is the structure of the commit convention we chose


<type>[optional scope]: <description>
<required blank-line>
[optional bullet point body]
<required blank-line>
closes docs#<required issue_number>

Examples of valid commits are:

Example 1: scope and body is present

feat(auth): Adds signup page to the app

- adds signup.html file to the app 

closes docs#101

Example 2:: body and scope not provided

chore: Refactors auth folder 

closes docs#121

Example 3:: scope provided but body omitted

ci(deploy): Improve deploy changes

closes docs#121

When we get to the practical section of this series, you will see that you could use any format you want as long as it is consistent and has all the information you want. To ensure consistency, you would have to enforce it.

Enforcing commit convention

It is one thing for everybody to agree on a commit convention, but it is very easy for mistakes and typos to be made when writing commits. Thus, it would be best to enforce the convention you choose on your local machine and on a CI pipeline to ensure that everyone obeys/follows it.

We achieved this by adding a command to our pre-commit hook that validates that our commit messages followed our convention and added the same check to our CI pipeline.

To enforce the agreed commit convention, we used a tool called commitizen which reads a regex specified in a cz.toml configuration file to test each commit message that is created. You will see how we did this when we get to the implementation detail section.

Once we have enforced our commit conventions, the next thing we do is to generate a changelog.

Generating the changelog

The commitizen package also allows you to generate a changelog from your commit messages.

To achieve this, we had to determine how we wanted each bullet point that highlights a change to look, create a grouped regex that extracts the parts of our commit message and use a class-based configuration from the commitizen package.

I will show you how I did this in a subsequent article.

So now we have the valid commits, and we have a changelog its time to talk about slack.

Sending the message to slack

After generating the changelog, the final step is to add code that automatically extracts the most recent release from the changelog and add it to the slack message.

We achieved this by creating a slack bot in our slack workspace and programmatically sending messages to a channel via a slack client.

While doing this, however, we quickly realised that slack does not recognise markdown but has its own rules for achieving rich text formatting via its API. Let’s call this slack technique of formatting messages “slackdown” (a fancy name invented by my manager)

As a result, we created a parser that converts markdown to “slackdown” alongside building a slack client. Once this parser has been completed, sending the slack message becomes easy.

Sending different messages from the pipeline

Once the slack bot has been created and is working, the next step is to develop different commands that would run on different scenarios(or events) in the pipeline. In our case, we needed a CLI command that would send a message on:

QA-deploy-success
QA-deploy-failure
prod-deploy-success
prod-deploy-failure

Conclusion

Hurray! If you got here, then you are fantastic! This article discussed a lot, and I explained the steps we took to maintain a changelog and notify our teammates when we deploy new features.

In asubsequent article, I will take you through each step with code examples.

You can follow StearsBusiness to get notified once it is created.

Methods of Data Storage in Data Science You Should Know

Chidiebere Ogujeiofor — Fri, 18 Jun 2021 22:26:02 +0000

Photo by Art Wall - Kittenprint on Unsplash

Introduction

We live in extraordinary times where technology is part of everything. One of the things that have made technology so powerful and valuable is data. A vast amount of data is generated every day on the internet. Consider the data generated on Instagram alone:

500 million Instagram stories are uploaded everyday
3.5 billion likes are recorded daily
As of 2016, 95 million posts are made every day, and Instagram usage doubled between 2016 and 2018.

This massive amount of data (called big data because of the size) cannot be stored using traditional techniques or on one machine. Storing and retrieving big data requires multiple interconnected machines. This article focuses on the different techniques of storing big data and their pros, cons, and use cases.

Types of Data

Before diving into the different types of data storage, we must first look at the types of data.

Structured Data

Structured data is anything that you can easily store in an excel spreadsheet, such as user first name, last name, bank details, etc. Such data usually has a defined length and format. On the internet, structured data is generated by both humans and machines. Some sources of this data are listed below:

Human

Filling your details or information on a form
Behaviour while watching videos online. For example, the site tracks the timestamp/episode you left the video at last.

Machine

Sensor data from your IoT devices or smartphones like your GPS location, battery life, and so on
Application logs, which help engineers figure out what errors exist in their platform or how efficient their system works.

Structured data accounts for about 20% of all the data on the internet and is usually easy to analyze to derive meaningful insights.

Unstructured Data

Unstructured data does not have any predefined format or model. You will usually not find a lot of similarities between 2 objects of this type of data. Classic examples of unstructured data are videos, images, audio, etc. If you were given two audio files, the difference in the author’s voice alone leads to a considerable difference between the two files. Like structured data, unstructured data also has its human and machine sources. Some of these include:

Human

Posting that morning sunshine image on Instagram
Updating your WhatsApp status with a comic video
Live streaming on social media platforms
The last PDF you exported

Machine

Images of the earth taken from a satellite

Analyzing unstructured data is more complex and usually involves creating machine learning models. This data comprises about 80% of the data on the internet and has become easier to store and maintain.

                    Image Source

Semi-structured Data

I like to think of this type of data as a kind of hybrid of both the above-mentioned types. It usually contains a structured part and an unstructured part.

Some examples of semi-structured data are emails and word documents. Notice these contain text (structured data) and media files (unstructured data). Think now of the data stored in NoSQL DBs in which one item has a certain number of fields, but the next does not—that is a semi-structured dataset. Notice that an email has the to, from, cc, and bcc fields, which are structured data, but it may also contain images.

How to Store Data

Now that we have familiarised ourselves with the different types of data, we can look at the various methods of storing this data.

Image Source

File Storage

In this type of storage, data is stored in data form on a disk. Anyone who wants to access it has to know the file path of the information on the disk. Some other characteristics of file storage include:

Files can simultaneously be read and written.
Only users on the same network can access them.
It is not easy to replicate your data—you might lose data in case of machine failure.

Block Storage

In block storage, data is stored as a contiguous chunk (called a block). It allows us to spread our network easily across different networks, and you don’t need to know where it is stored on the disk to access it. Relational databases are examples of RDMS, making it the best type of storage for structured data.

Other characteristics include:
They are highly scalable: You can increase the size of your block storage by adding more nodes to your network, thus making it easy to scale.
Easy to replicate: Most block storage services are easy to backup/replicate. Thus, in case of machine failure, your data is still intact.
Reads and writes are fast, and you do not need to know where the data is on disk.
Block storage is generally expensive as the data increases. This makes it expensive for data that is large, for instance.

Object Storage

Object storage is the most suitable way of storing unstructured data. The data is usually stored as an “object”.

An object is made up of 3 main parts:

The data: This is the image, picture, etc., that we want to store.
Metadata: This can be a description of what this data stands for. Most object storage services allow us to search the content of this metadata.
Unique Identifier: This can be used to retrieve the object easily at any point.

Anytime an object is created, most services replicate it three times. This makes it easy to retrieve the object and search for the data. Thus, object storage is ideal for storing unstructured data as it can easily be accessed using an ID.

Advantages of object storage include:

Way cheaper than other types of storage
Data replication comes in by default
Rich metadata feature
The storage is not coupled to a machine and thus is easy to scale
Grouping of data can be done using a metadata value

Conclusion

In this article, we looked at the different types of data in big data and explored the main techniques of storing big data. I hope you learn something new from this article.

Relative calculations 1: LAG and LEAD

Chidiebere Ogujeiofor — Sun, 20 Sep 2020 19:28:25 +0000

In this post, we are going to continue our discussion by looking at calculations that involve getting the value from the previous/next row. We would see how using ORDER BY clause in an SQL window function can make these calculations pretty easy and possible.

Let's dive right in.

Our Big Question

One particularly important insight is the increase is the way the number of cases has been increasing. Suppose we had the following question:

What is the increase in cases recorded in the UK for each day in the month of June?

Assuming we had access to only the total number of confirmed cases one way we could answer this to generate a table where each row gives us the difference between the case recorded today and the case recorded previous day. Soemthing like this:

date	total_confirmed_cases	daily_case_recorded
2020-06-01	v1	0
2020-06-02	v2	v2 - v1
2020-06-03	v3	v3 - v2
2020-06-04	v4	v4 - v3
.	.	.
.	.	.
.	.	.
n	vn	vn - vn-1

Notice how the value in each row in the total_confirmed_cases column uses the value from the previous column to calculate its value.

Note that we assumed that we had access to only the total_confirmed_cases. Google COVID Dataset already gives us the daily_confirmed_cases which is the same as what we want to calculate.

In order to solve this, we would use the LAG aggregate function and ORDER BY in our window function.

The ORDER BY in a window function

Using the ORDER BY as an argument in the window function specifies how you want the window function to sort the rows in the ResultSet while performing calculations.

For example, in order to answer the question we have above, it is important that we sort the result by date so that when getting the value from the previous row, we would be getting the confirmed case from the previous day.

Using the ORDER BY clause makes it possible for us to specify that.

We can use the ORDER BY like so:


OVER(ORDER BY column_1, column_2, ..., column_n [ASC|DESC])

LAG aggregate function

The LAG function is a special aggregate function the works only when used with window functions. It takes a column as an argument and returns the previous value of that column in the ResultSet.

We use the LAG function with the ORDER BY clause like so:

LAG(column_name) OVER(ORDER BY column_1, column_2, ..., column_n {ASC|DESC|)

The SQL QUERY

Combining these together we can answer our Big question using the following query.

SELECT
  date,
  countries_and_territories as country,
  confirmed_cases,
  confirmed_cases - LAG(confirmed_cases) OVER(
    ORDER BY
      date
  ) as daily_increase
FROM
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
WHERE
  date >= "2020-06-01"
  AND date <= "2020-06-30"
  AND geo_id = "UK";

This filters the resultset and generates the increase in a number of cases using:

  confirmed_cases - LAG(confirmed_cases) OVER(
    ORDER BY
      date
  ) as daily_increase

The window function orders by date while the LAG(confirmed_cases) returns cases confirmed for the previous row.

This generates the following table.

date	country	confirmed_cases	daily_increase
2020-06-01	United_Kingdom	274762	NULL
2020-06-02	United_Kingdom	276332	1570
2020-06-03	United_Kingdom	277985	1653
2020-06-04	United_Kingdom	279856	1871
2020-06-05	United_Kingdom	281661	1805
2020-06-06	United_Kingdom	283311	1650
2020-06-07	United_Kingdom	284868	1557
2020-06-08	United_Kingdom	286194	1326
2020-06-09	United_Kingdom	287399	1205
2020-06-10	United_Kingdom	289140	1741
2020-06-11	United_Kingdom	290143	1003
2020-06-12	United_Kingdom	291409	1266
2020-06-13	United_Kingdom	292950	1541
2020-06-14	United_Kingdom	294375	1425
2020-06-15	United_Kingdom	295889	1514
2020-06-16	United_Kingdom	296857	968
2020-06-17	United_Kingdom	298136	1279
2020-06-18	United_Kingdom	299251	1115
2020-06-19	United_Kingdom	300469	1218
2020-06-20	United_Kingdom	301815	1346
2020-06-21	United_Kingdom	303110	1295
2020-06-22	United_Kingdom	304331	1221
2020-06-23	United_Kingdom	305289	958
2020-06-24	United_Kingdom	306210	921
2020-06-25	United_Kingdom	306862	652
2020-06-26	United_Kingdom	307980	1118
2020-06-27	United_Kingdom	309360	1380
2020-06-28	United_Kingdom	310250	890
2020-06-29	United_Kingdom	311151	901
2020-06-30	United_Kingdom	311965	814

One thing that you may have noticed is that the first value in the table is NULL.

Remember what we said in previous articles that window functions use the ResultSet generated after filtering in the WHERE clause, well, this is one of the consequences.

At the first instance, the LAG function returns the value of the specified column in the previous row but the first row does not have a previous row. Therefore, we get NULL( which means non-existent).

But we know that the confirmed case for the first day of July exists, so how do we get that?

One quick way is to modify the filter in the WHERE clause so that it returns data for a day before July 1st like so:

WHERE
  date >= "2020-05-31"
  AND date <= "2020-06-30"
  AND geo_id = "UK";

Then we omit the first row in our resultset by adding OFFSET 1. The new query would now become

SELECT
  date,
  countries_and_territories as country,
  confirmed_cases,
  confirmed_cases - LAG(confirmed_cases) OVER(
    ORDER BY
      date
  ) as daily_increase
FROM
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
where
  date >= "2020-05-31"
  AND date <= "2020-06-30"
  AND geo_id = "UK"
LIMIT
  31 OFFSET 1;

This would return the correct data for the first row

date	country	confirmed_cases	daily_increase
2020-06-01	United_Kingdom	274762	1936
2020-06-02	United_Kingdom	276332	1570
2020-06-03	United_Kingdom	277985	1653
2020-06-04	United_Kingdom	279856	1871
2020-06-05	United_Kingdom	281661	1805
2020-06-06	United_Kingdom	283311	1650
2020-06-07	United_Kingdom	284868	1557
2020-06-08	United_Kingdom	286194	1326
2020-06-09	United_Kingdom	287399	1205
2020-06-10	United_Kingdom	289140	1741
2020-06-11	United_Kingdom	290143	1003
2020-06-12	United_Kingdom	291409	1266
2020-06-13	United_Kingdom	292950	1541
2020-06-14	United_Kingdom	294375	1425
2020-06-15	United_Kingdom	295889	1514
2020-06-16	United_Kingdom	296857	968
2020-06-17	United_Kingdom	298136	1279
2020-06-18	United_Kingdom	299251	1115
2020-06-19	United_Kingdom	300469	1218
2020-06-20	United_Kingdom	301815	1346
2020-06-21	United_Kingdom	303110	1295
2020-06-22	United_Kingdom	304331	1221
2020-06-23	United_Kingdom	305289	958
2020-06-24	United_Kingdom	306210	921
2020-06-25	United_Kingdom	306862	652
2020-06-26	United_Kingdom	307980	1118
2020-06-27	United_Kingdom	309360	1380
2020-06-28	United_Kingdom	310250	890
2020-06-29	United_Kingdom	311151	901
2020-06-30	United_Kingdom	311965	814

Now we can generate our chart:

You can interact with the chart by following this link.

More on LAG, intro to LEAD

The LAG function takes an optional integer as value as its second argument this integer indicates how many rows back we want to get. This number is 1 by default, that's why we get the previous row( 1 row before the current row).

Similar to the LAG function is the LEAD function which takes the value of the next row. It has the same constraints and syntax of the LAG function.

So LEAD(col_name, 2) indicate we want to get a value 2 rows after the current row from col_name while LEAD(col_name) indicate we want the next row value.

Conclusion

In this article, we've learnt how to perform a calculation that involves getting a single value from a row before or after our current row.

That's great but what if we want to perform an aggregation based on more than one row before or after our current row? We would see how to do that using ROWS PRECEDING and ROWS FOLLOWING in the next article of our series.

See you soon.

Comparing aggregated data to unaggregated data in SQL

Chidiebere Ogujeiofor — Sun, 13 Sep 2020 15:44:54 +0000

Depending on the type of questions we want to answer there might be times when we need to compare aggregated data to unaggregated data in SQL.

This is where the fact that the GROUP BY clause forces us to 'squeeze' all unaggregated columns into one row can be a headache.

In this article, you would see how a window function can make this quite easy.

The Big question

Suppose we wanted to answer the following question using our COVID Dataset:

How does each case recorded in Nigeria from March 2020 to June 2020 compared with the maximum case recorded within the same time frame.

We can answer this question by returning a table that has the recorded case for each day and the maximum case recorded in a day within the time frame. Something like this:

date	country	confirmed_today	max_value_in_timeframe
2020-03-01	Nigeria	value1	max_value_in_result_set
2020-03-02	Nigeria	value2	max_value_in_result_set
2020-03-03	Nigeria	value3	max_value_in_result_set
2020-03-04	Nigeria	value4	max_value_in_result_set

This would allow us to easily plot graphs that help us with the comparison. Let's see how we can solve this

The Inefficient solution

We can generate this result using a subquery in the SELECT clause. This subquery would calculate the maximum value and return that value for each row in our table. See the code below:


SELECT
  date,
  countries_and_territories as country,
  daily_confirmed_cases AS confirmed_today,
  (
    SELECT
      MAX(daily_confirmed_cases)
    FROM
      `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
    WHERE
      geo_id = 'NG'
      AND date >= '2020-03-01'
      AND date < '2020-06-01'
  ) as highest_case_recorded_in_a_day
FROM
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
WHERE
  geo_id = 'NG'
  AND date >= '2020-03-01'
  AND date < '2020-06-01';

This returns the following results:

date	country	confirmed_today	highest_case_recorded_in_a_day
2020-03-01	Nigeria	0	533
2020-03-02	Nigeria	0	553
2020-03-10	Nigeria	1	553
2020-03-15	Nigeria	0	553
2020-03-16	Nigeria	0	553
2020-03-17	Nigeria	0	553
2020-03-18	Nigeria	1	553
2020-03-19	Nigeria	5	553
2020-03-20	Nigeria	0	553
2020-03-21	Nigeria	4	553
2020-03-22	Nigeria	10	553
2020-03-23	Nigeria	8	553
2020-03-24	Nigeria	10	553
2020-03-25	Nigeria	4	553
2020-03-26	Nigeria	7	553
2020-03-27	Nigeria	14	553
2020-03-28	Nigeria	16	553
2020-03-29	Nigeria	16	553
2020-03-30	Nigeria	0	553
2020-03-31	Nigeria	34	553
2020-04-01	Nigeria	0	553
2020-04-02	Nigeria	20	553
2020-04-03	Nigeria	23	553
2020-04-04	Nigeria	16	553
2020-04-05	Nigeria	20	553
2020-04-06	Nigeria	22	553
2020-04-07	Nigeria	6	553
2020-04-08	Nigeria	16	553
2020-04-09	Nigeria	22	553
2020-04-10	Nigeria	12	553
2020-04-11	Nigeria	17	553
2020-04-12	Nigeria	13	553
2020-04-13	Nigeria	5	553
2020-04-14	Nigeria	20	553
2020-04-15	Nigeria	30	553
2020-04-16	Nigeria	34	553
2020-04-17	Nigeria	35	553
2020-04-18	Nigeria	51	553
2020-04-19	Nigeria	49	553
2020-04-20	Nigeria	85	553
2020-04-21	Nigeria	38	553
2020-04-22	Nigeria	117	553
2020-04-23	Nigeria	91	553
2020-04-24	Nigeria	108	553
2020-04-25	Nigeria	114	553
2020-04-26	Nigeria	87	553
2020-04-27	Nigeria	91	553
2020-04-28	Nigeria	64	553
2020-04-29	Nigeria	195	553
2020-04-30	Nigeria	196	553
2020-05-01	Nigeria	204	553
2020-05-02	Nigeria	238	553
2020-05-03	Nigeria	218	553
2020-05-04	Nigeria	170	553
2020-05-05	Nigeria	244	553
2020-05-06	Nigeria	148	553
2020-05-07	Nigeria	195	553
2020-05-08	Nigeria	381	553
2020-05-09	Nigeria	386	553
2020-05-10	Nigeria	239	553
2020-05-11	Nigeria	248	553
2020-05-12	Nigeria	242	553
2020-05-13	Nigeria	146	553
2020-05-14	Nigeria	184	553
2020-05-15	Nigeria	191	553
2020-05-16	Nigeria	283	553
2020-05-17	Nigeria	176	553
2020-05-18	Nigeria	338	553
2020-05-19	Nigeria	216	553
2020-05-20	Nigeria	226	553
2020-05-21	Nigeria	276	553
2020-05-22	Nigeria	339	553
2020-05-23	Nigeria	245	553
2020-05-24	Nigeria	265	553
2020-05-25	Nigeria	313	553
2020-05-26	Nigeria	229	553
2020-05-27	Nigeria	276	553
2020-05-28	Nigeria	389	553
2020-05-29	Nigeria	182	553
2020-05-30	Nigeria	387	553
2020-05-31	Nigeria	553	553

As you can see it returns exactly the shape of the data that we want in our table. Now we can go ahead and generate a chart, like the one shown below, which are easier to comprehend:

You can interact with the chart here

So what are the problems

The first issue is that subqueries perform calculations by running through the entire table. This simply means that the query above runs through the table twice to get the results that we got. This can affect performance

Another problem is that subqueries are quite difficult to read and maintain. It is quite difficult to grasp what the query above is doing at a glance

The window function solution

Now let's try to solve this problem using a window function.
The way we would do this is to replace the subquery part of the first query with our window function, like so:

SELECT
  date,
  countries_and_territories as country,
  daily_confirmed_cases AS confirmed_today,
  MAX(daily_confirmed_cases) OVER() as highest_case_recorded_in_a_day
FROM
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
WHERE
  geo_id = 'NG'
  AND date >= '2020-03-01'
  AND date < '2020-06-01'

The OVER() function is what starts our window function(as stated in the previous article in the series) while the MAX() gets the maximum case recorded on a particular day in the specified time frame.

The window function uses the ResultSet gotten from the WHERE part of the query thus there it does not run through the entire table like the subquery alternative.

In the end, you get a much shorter, more readable, maintainable and performant code.

Conclusion

What I have described in this article is a very simple use case of a window function. Let me know what insightful questions related to COVID-19 we could answer by comparing aggregated and unaggregated data in the comments section.

Next in this series, we would be talking about performing calculations that are dependent on the value of other rows eg. running totals, daily_increase in cases etc.

Watch OVER your WINDOW of opportunities

Chidiebere Ogujeiofor — Sun, 13 Sep 2020 15:25:20 +0000

Howdy!

From the very first time I heard of SQL Window Functions, I have always been amazed at how powerful it is. Also found that a lot of folks in my circle don't really understand where or how to use it.

Thus, I decided to write a series of articles that explain SQL Window functions, what they are, where they can be used etc.

To make the examples more fun and easily replicable, we would be answering questions about the COVID-19 pandemic by analysing data from the Google COVID-19 BigQuery Dataset.

In particular the bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide table.

Let's begin our journey by understanding some definitions.

What are SQL Window Functions

Window functions are used to perform aggregations without grouping the unaggregated data to a single row. Also, they are used to perform calculations that are relative to the current row(eg running sums/averages).

A more complete definition from the PostgreSQL documentation is seen below:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

When should we use them

I think the best way to make sense of the definition above is to look at some use cases of window functions. They come in handy when:

Comparing aggregated data to unaggregated data.
Performing a calculation that is dependent on the values of previous rows(eg. running totals/averages, increase of a particular value with time etc.).
Ranking data.
Performing calculations that involves breaking the resultSet into section(technically called 'windows').

We'll take a deeper dive into these in subsequent articles in the series. For now, let's look at the basic structure of a query with a window function

Syntax of Window Functions

Window functions can be created in the same way we specify columns in a SQL query. The key difference is that we use the OVER clause to specify that it is a window function.


SELECT 
   _agg_func_call_ OVER([window_aggs]) AS col_name,
   other_col_1,
   other_col_2, 
   ...,
   other_col_n

FROM _table_name_
[other_select parts]

As you can see, the window function is part of the SELECT statement. SQL knows that it is a window function if it has the OVER() function.

The agg_func_call here can be any aggregate function eg AVG, MIN, MAX, SUM, etc.

Note:
One very important thing to bear in mind here is the order of operations of an SQL statement and when the Window functions are called. The order of operation is as follows

FROM and JOINs

WHERE

GROUP BY

HAVING

SELECT (this is when our window functions run)

DISTINCT

ORDER BY

LIMIT/OFFSET

Thus, our Window functions can access the ResultSet after the HAVING. As we would see in subsequent articles, whenever we have to choose between a window function and a Subquery solution, the window function would usually be faster.

This is because a subquery performs its calculation by running through the entire table while a window function performs calculation with ResultSet produced from the WHERE clause.

OVER function arguments

The OVER function optionally takes some arguments
The full syntax of the argument of the OVER() function is shown here.


OVER(
    [PARTITION BY args] 
    [ORDER BY args] 
    [{ROWS| ROWS BETWEEN} args]
)

ORDER BY col_1, ..., col_n

This has the same syntax as when used at the end of a SELECT statement.

In the window function, however, it sorts the ResultSet which would be used in the window function. We would see examples of this later on.

[ROWS rows_args| ROWS BETWEEN starting_row BETWEEN ending_row]

This is used to perform calculations relative to the current row. It comes in very handy when performing aggregations that are relative to previous/next row values eg leading sums/averages, daily rise in cases etc.

PARTITION BY

This is used to break up the data into more windows. This comes in handy in some complex calculations where we want to break up the result-set into smaller 'windows' and perform the calculations within that window.

The examples of this would explain this way better than words will.

So that's what you should expect in this series. I hope you have a fun read.

The next on the series would be Comparing aggregated data to unaggregated data. See you there!

Eager Loading VS Lazy Loading in SQLAlchemy

Chidiebere Ogujeiofor — Wed, 12 Feb 2020 08:20:00 +0000

I have heard of eager loading data relationships a lot in the past, but it was only recently that I became more conscious of it in my APIs. I will be showing you guys what it is and why you should bother about it in this post.

Prerequisites

The prerequisites for this post is a basic understanding of SQL Joins, SQLAlchemy and Python

Definitions of Eager Loading VS Lazy Loading

Eager loading is a technique of retrieving a model's relationship data while querying the model either through a JOIN or subquery.

Lazy loading, on the other hand, retrieves the related models only when an attempt is made to retrieve the relationship field by emitting SELECT statements.

I think examples would help make this clearer.

Examples

Let's say we have three models, namely User, Membership and Company, such that there is a many-to-many relationship between users and companies like so:

In SQLAlchemy models:



class User(db.Model):
   id = db.Column(db.Integer, primary_key=True, autoincrement=True)
   username = db.Column(
     db.String(20),
     nullable=False,
   )
   password_hash = db.Column(db.VARCHAR(130), nullable=False)
   memberships = db.relationship('Membership',
                                 back_populates='member')


class Company(db.Model):
   id = db.Column(db.Integer, primary_key=True, autoincrement=True)
   name = db.Column(db.String(120), nullable=False)
   website = db.Column(db.String(), nullable=False)
   address = db.Column(db.String(), nullable=False)
   memberships = db.relationship('Membership',
                                 back_populates='company')


class Membership(db.Model):
   id = db.Column(db.Integer, primary_key=True, autoincrement=True)
   user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
   company_id = db.Column(db.Integer,
                               db.ForeignKey('company.id'))
   role = db.Column(db.String, default='REGULAR_USER')
   member = db.relationship("User", back_populates="memberships")
   company = db.relationship('Company', back_populates="memberships")

The Lazy Problem

Now if we query a membership from our database, like so:



>>> membership = Membership.query.first()

By default, SQLAlchemy retrieves only the data contained in the Membership table by running the following SQL query:



INFO:sqlalchemy.engine.base.Engine:SELECT membership.id AS membership_id, membership.user_id AS membership_user_id, membership.company_id AS membership_company_id, membership.role AS membership_role
FROM membership
 LIMIT %(param_1)s

Now when you try to access the member.username contained in the membership object:



>>> membership.member.username

it makes another DB call to retrieve the member from the User's table which then contains the username:



INFO:sqlalchemy.engine.base.Engine:SELECT "user".id AS user_id, "user".username AS user_username, "user".password_hash AS user_password_hash
FROM "user"
WHERE "user".id = %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}

Similarly, when we attempt to retrieve name of the company, it results in another DB call:



>>> membership.company.name



INFO:sqlalchemy.engine.base.Engine:SELECT company.id AS company_id, company.name AS company_name, company.website AS company_website, company.address AS company_address
FROM company
WHERE company.id = %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}

When you retrieve multiple rows(say 50) from a table using lazy loading and you run a loop to access a related field, SQLAlchemy would emit SELECT statements for each of the (50) models you retrieved.

Note that looping through each model described here is how marshmallow dumps nested fields to JSON.

That is lazy-loading relationship fields work. You try to retrieve data only when you need them.

The Eager Solution

When we Eager-Load relationship fields, we tell SQLAlchemy to retrieve those fields when the first query is being made either through a JOIN or a subquery. In general, JOINs tend to be more efficient than subqueries.

We do this by using sqlalchemy.orm module like so:



>>> from sqlalchemy import orm
>>> Membership.query.options(orm.joinedload('company')).first()

This generates the following SQL:



INFO:sqlalchemy.engine.base.Engine:SELECT membership.id AS membership_id, membership.user_id AS membership_user_id, membership.company_id AS membership_company_id, membership.role AS membership_role, company_1.id AS company_1_id, company_1.name AS company_1_name, company_1.website AS company_1_website, company_1.address AS company_1_address
FROM membership LEFT OUTER JOIN company AS company_1 ON company_1.id = membership.company_id
 LIMIT %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}

As you can see a join is made to retrieve the company relationship thus trying to access the company name



>>> membership.company.name
'Ade Store1'

does not lead to any extra DB call.

Eager Loading multiple fields

What if we wanted to return nested data from both the company and member fields, how do we eager load both? We can do that easily by adding more arguments to the options method call like so:



membership = Membership.query.options(
    orm.joinedload('company'), orm.joinedload('member')
).first()

This generates the following SQL statement:



INFO:sqlalchemy.engine.base.Engine:SELECT membership.id AS membership_id, membership.user_id AS membership_user_id, membership.company_id AS membership_company_id, membership.role AS membership_role, user_1.id AS user_1_id, user_1.username AS user_1_username, user_1.password_hash AS user_1_password_hash, company_1.id AS company_1_id, company_1.name AS company_1_name, company_1.website AS company_1_website, company_1.address AS company_1_address
FROM membership LEFT OUTER JOIN "user" AS user_1 ON user_1.id = membership.user_id LEFT OUTER JOIN company AS company_1 ON company_1.id = membership.company_id
 LIMIT %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}

With that accessing either the company or member field does not lead to any extra SQL statement being run.

Takeaways

You should eager-load relationship fields only when you know that those fields would be used in your code else you would fall into a situation where you are retrieving values you don't actually need which can make your API a little slower. Also, be sure to avoid lazy-loading fields that you would need for your logic

One other thing to note is that this concept is not tied to only SQLAlchemy. It also exists in Django ORM and some other non-python ORMs.

You can get more info on this topic from the SQLAlchemy Docs

Conclusion

I would like to end by saying that the syntax for the eager-loading doesn't quite cumbersome. You can make this easier by putting the logic in a BaseModel as I did in the blog post below

Simplifying SQLAlchemy models by creating a BaseModel

Chidiebere Ogujeiofor ・ Feb 12 '20

#beginners #sqlachemy #python #oop

Finally, you can view all the code(including setup) can be gotten the gist below:

Thanks for your time

Simplifying SQLAlchemy models by creating a BaseModel

Chidiebere Ogujeiofor — Wed, 12 Feb 2020 08:19:28 +0000

SQLAlchemy's approach of making all requests a transaction and requiring you to constantly add things to the session before committing them could lead to you repeating yourself constantly. I decided to share how you can use OOP to avoid these repetitions by creating a BaseModel.

Prerequisites for this is a basic understanding of SQL, SQLAlchemy and Python.

Model Parts

All the models in our application would most likely have the following properties:

id
created_at
update_at

Also, the following actions:

save
update
bulk_create

Depending on the application we might want to add more attributes and reuseable methods to the BaseModel but this is usually a good place to start.

Our BaseModel

Now let me walk through the parts of the BaseModel

Attributes




class BaseModel(db.Model):
    __abstract__ = True

The __abstract__ attribute tells SQLAlchemy that this model should not be created in the database. Thus, when creating tables/migrations this class would be ignored.



class BaseModel(db.Model):
    .
    .
    .
   id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    created_at = db.Column(db.DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime(timezone=True), nullable=True)

These are the fields that would be repeating in our models. Specifying it here removes the need to add these columns when creating our models as they would be implicitly created for us.

When you have time fields in your models, it is best to make them timezone sensitive. Here, I have used a lambda expression, the datetime.timezone and datetime.datetime classes to generate a default datetime whenever a model is saved to the database.

Instance Methods: Create, Update, Delete.



class BaseModel(db.Model):
    .
    .
    .
    def before_save(self, *args, **kwargs):
        pass

    def after_save(self, *args, **kwargs):
        pass

    def save(self, commit=True):
        self.before_save()
        db.session.add(self)
        if commit:
            try:
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                raise e

        self.after_save()


    def before_update(self, *args, **kwargs):
        pass

    def after_update(self, *args, **kwargs):
        pass

    def update(self, *args, **kwargs):
        self.before_update(*args, **kwargs)
        db.session.commit()
        self.after_update(*args, **kwargs)

    def delete(self, commit=True):
        db.session.delete(self)
        if commit:
            db.session.commit()

The save method inserts the model object to the DB and rolls back when an error occurs. Since SQLAchemy performs its queries in a transaction, it rolls back all the things that have been added to this session once an error occurs.
We can control whether we want to commit to the DB or not by specifying a value for commit

Although SQLAlchemy has events listeners that run after_insert and before_insert hooks, I have found it handy to specify these operations as methods in your model.

The other methods for update and delete are similar

Class Methods: Bulk Create and Eager Loading

Eager Loading



class BaseModel(db.Model):
    .
    .
    .
    @classmethod
    def eager(cls, *args):
        cols = [orm.joinedload(arg) for arg in args]
        return cls.query.options(*cols)

Eager loading is a very important concept to be conscious of when querying your data. This method makes eager loading easier by allowing us to just specify only the fields we want to eager load. For example



Model.eager('rel1', 'rel2').all()

The above call would eager-load relationships rel1 and rel2. You can get more info about eager loading in my post

Eager Loading VS Lazy Loading in SQLAlchemy

Chidiebere Ogujeiofor ・ Feb 12 '20

#sql #python #sqlalchemy

Bulk Create



class BaseModel(db.Model):
    .
    .
    .

    @classmethod
    def before_bulk_create(cls, iterable, *args, **kwargs):
        pass

    @classmethod
    def after_bulk_create(cls, model_objs, *args, **kwargs):
        pass


    @classmethod
    def bulk_create(cls, iterable, *args, **kwargs):
        cls.before_bulk_create(iterable, *args, **kwargs)
        model_objs = []
        for data in iterable:
            if not isinstance(data, cls):
                data = cls(**data)
            model_objs.append(data)

        db.session.bulk_save_objects(model_objs)
        if kwargs.get('commit', True) is True:
            db.session.commit()
        cls.after_bulk_create(model_objs, *args, **kwargs)
        return model_objs


    @classmethod
    def bulk_create_or_none(cls, iterable, *args, **kwargs):
        try:
            return cls.bulk_create(iterable, *args, **kwargs)
        except exc.IntegrityError as e:
            db.session.rollback()
            return None

The bulk_create implementation above allows the user to INSERT multiple rows in the database either from an array of the model instances or array of dictionaries. I found the dictionaries part super helpful while writing automated tests.

Like the save method, the bulk_create method would persist to the DB when the commit kwarg is set to True and makes calls to before_bulk_create and after_bulk_create. It returns a list the created models on success but leaves error handling to the calling function

The bulk_create_or_none differs from the bulk_create by handling only IntegrityError and returning None when they occur.

BaseModel in use

Here are some examples of how you can use this BaseModel

Creating concrete Models




class User(BaseModel):

    username = db.Column(
    db.String(20),
    nullable=False,
    )
    password_hash = db.Column(db.VARCHAR(130), nullable=False)
    memberships = db.relationship('Membership',
                                 back_populates='member')

    def before_update(self, *args, **kwargs):
        print('Calling User models before_update')

    def after_update(self, *args, **kwargs):
        print('Calling User models after_update')


class Company(BaseModel):

    name = db.Column(db.String(120), nullable=False)
    website = db.Column(db.String(), nullable=False)
    address = db.Column(db.String(), nullable=False)
    memberships = db.relationship('Membership',
                                 back_populates='company')


class Membership(BaseModel):

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    company_id = db.Column(db.Integer,
                           db.ForeignKey('company.id'))
    role = db.Column(db.String, default='REGULAR_USER')
    member = db.relationship("User", back_populates="memberships")
    company = db.relationship('Company', back_populates="memberships")

INSERTING

You can insert fields with something like:



>>> user = User(username='some_username', password_hash='password')
>>> user.save()
>>> company = Company(name='Company 1', website='somewebsite.com', address='Atom 1 close, Ojodu Lagos')
>>> company.save()
>>> membership = Membership(user_id=user.id, company_id=company.id)
>>> membership.save()
>>> company = Company(name='SomeUnknownCompany', website='some.com',address='Aguda, Surulere Lagos')
>>> company.save(commit=False) # When commit is false
>>> Company.query.filter_by(name='SomeUnknownCompany').count()
1
>>> db.session.rollback()
>>> Company.query.filter_by(name='SomeUnknownCompany').count()
0

Eager Loading

We can eager load fields:



>>> Membership.eager('company', 'member').all()
[<Membership 1>, <Membership 2>, <Membership 3>, <Membership 4>, <Membership 5>, <Membership 6>, <Membership 7>, <Membership 8>, <Membership 9>, <Membership 10>]

Generating the following SQL



INFO:sqlalchemy.engine.base.Engine:SELECT membership.id AS membership_id, membership.created_at AS membership_created_at, membership.updated_at AS membership_updated_at, membership.user_id AS membership_user_id, membership.company_id AS membership_company_id, membership.role AS membership_role, user_1.id AS user_1_id, user_1.created_at AS user_1_created_at, user_1.updated_at AS user_1_updated_at, user_1.username AS user_1_username, user_1.password_hash AS user_1_password_hash, company_1.id AS company_1_id, company_1.created_at AS company_1_created_at, company_1.updated_at AS company_1_updated_at, company_1.name AS company_1_name, company_1.website AS company_1_website, company_1.address AS company_1_address
FROM membership LEFT OUTER JOIN "user" AS user_1 ON user_1.id = membership.user_id LEFT OUTER JOIN company AS company_1 ON company_1.id = membership.company_id
INFO:sqlalchemy.engine.base.Engine:{}

UPDATING



>>> user = User.query.first()
>>> user.username
'some_username'
>>> user.username = 'Adebayor1010'
>>> user.update()
Calling User models before_update
Calling User models after_update
>>> User.query.filter_by(username='Adebayor1010').count()
1

Generating the following SQL query



INFO:sqlalchemy.engine.base.Engine:SELECT "user".id AS user_id, "user".created_at AS user_created_at, "user".updated_at AS user_updated_at, "user".username AS user_username, "user".password_hash AS user_password_hash

FROM "user"

WHERE "user".username = %(username_1)s

INFO:sqlalchemy.engine.base.Engine:{'username_1': 'user1010'}

Bulk Creating



>>> user_dicts = [

...     {'username': 'user1010', 'password_hash': 'some_hash'},

...     {'username': 'user1011', 'password_hash': 'some_hash'},

...     {'username': 'user1012', 'password_hash': 'some_hash'},

...     {'username': 'user1013', 'password_hash': 'some_hash'},

... ]

>>> users  = User.bulk_create(user_dicts)

Conclusion

One small advice I have is this

Before abstracting a particular logic to some Base class, ensure that you have repeated that logic a significant amount of time.

One thing I have found myself doing is rushing into abstracting a particular logic and then realising 10 commits later that I need to edit the method in the Base class but it has been tightly coupled to some concrete implementation. You can avoid falling into this by waiting until you have repeated something about 4-5 times before abstracting

Hope you have fun creating your SQLAlchemy models.