---
format:
    revealjs:
        theme: simple
        chalkboard: true
        auto-stretch: false
---

## &nbsp;

## Data Engineering: Databases

Keith VanderLinden<br>
Calvin University

::: {.notes}
In this module, we continue our discussion of data engineering by looking at DBMSs.

Notes:

- Using {sql} in later slides messed up the title slide for some reason, so I included the empty first slide title.

:::

## Database Management Systems

Database Management Systems (DBMS) provide:

- Reliability
- Scalability
- Flexibility
- Usability

Data scientists benefit from being able to manage data stored in a variety of types of data systems.

::: {.notes}
- DBMSs are an improvement over flat files (e.g., CSV) because they provide:
    - *Reliability*
        - Relational DBMSs have been used profitably used since the 1970s.
        - They provide support for data integrity & consistency.
        - E.g., my relational/SQL queries have worked for decades. Granted, CSVs are also reliable, but they're not as powerful.
    - *Scalability*
        - DBMSs are designed to handle databases that don't fit into main memory.
        - Excel can handle ~1M rows, but it's not pleasant. It can be slow; you can't load just part of the data; it's not easy to share; it's not easy to automate.
        - E.g., M. Heun's issue with publishing a large, complicated dataset.
            - "Big data is when your workflow breaks." - R. Pruim, [MDSR](https://mdsr-book.github.io/mdsr2e/ch-big.html)
    - *Flexibility*
        - DBMSs are already in-place at most organizations.
        - They're easily integrated into the ML workflow using well-established tools.
        - E.g., they can be used to support the sharing of M. Heun's data.
    - *Usability*
        - DBMSs support well-known DDLs & DMLs (especially for SQL but also for noSQL).
        - E.g., they'd be more accessible than M. Heun's idiosyncratic CSV-handling scripts.
- We include them in the course to provide a fuller picture of the data management ecosystem.
    - E.g., C. McQueen's value to a DS team as a data engineer who could manage heterogeneous data sources.

References

- [NOT GREAT, BUT USEFUL] <https://www.datascienceverse.com/data-engineering/sql-databases-in-data-science-why-they-matter-and-how-to-use-them>
:::

## Database Models

:::: {.columns}

::: {.column width="40%"}
*Relational*

- Relations
    - Structured *schema*
    - Normalized (often)
- Vertical scaling
- SQL
:::

::: {.column width="60%"}
*Non-Relational*

- Multi-mode
    - Semi/Un-structured
    - De-normalized (often)
- Vertical &amp; horizontal scaling
- Not SQL
:::

::::

This course will focus on non-relational database systems.

::: {.notes}
- Structure
    - C. Huyen says that the pain of schema management is commonly cited as a reason to use noSQL databases (DMLS, p. 63).
    - When the data is inherently unstructured, noSQL databases are a good choice. You can just add new documents to the collection without worrying about whether their structure matches a schema.
    - Normalization
        - Relational Structure
            - normalized (databases)
            - de-normalized (warehouses)
        - non-Relational Structure
            - unstructured or de-normalized (usually)
- There's only 1 relational model and only one SQL (mostly).
    - SQL is "inter-galactic data-speak" and is widely supported (e.g., MySQL, PostgreSQL, SQLite, Oracle, SQL Server)
- There are many noSQLs, including:
    - Key-value (cf. persistent hash tables) - for simpler DB applications
    - Document (cf. persistent hierarchically-structured objects) - JSON/BSON, HTML, XML, ...
    - Tabular (cf., column-oriented data) - BigTable
    - Graph
- How to choose? Use a non-relational model if it's likely that you'll need:
    - Flexible structure/schema - Data will be non-structured or heterogenous, i.e., it won't look like tables with joins.
    - Scalability - There will be enough data to require horizontal scaling (e.g., sharding).
    - We won't care so much about consistency (cf. the CAP theorem).
- You've seen (or will soon see) relational databases using SQL (e.g., in DATA 654).

References

- C. Huyen lists two types of noSQL models, but there are others (see [noSQL](https://en.wikipedia.org/wiki/NoSQL)).
- <https://www.datacamp.com/blog/nosql-databases-what-every-data-scientist-needs-to-know>
- <https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-introduction?view=azuresql#horizontal-and-vertical-scaling>
:::

## MongoDB

A MongoDB *database* is a set of *collections*, each of which is set of *documents*. Here&rsquo;s a collection of three documents.

```{sql}
[
    {
        "name": "Halley's Comet",
        "officialName": "1P/Halley",
        "specs": {"orbitalPeriod": 75, "radius": 3.4175, "mass": 2.2e14 }
    },
	{
        "name": "Wild2",
        "officialName": "81P/Wild",
        "specs": {"orbitalPeriod": 6.41, "radius": 1.5534, "mass": 2.3e13 }
    },
	{
        "name": "Comet Hyakutake",
        "officialName": "C/1996 B2",
        "specs": {"orbitalPeriod": 17000, "radius": 0.77671, "mass": 8.8e12 }
    }
]
```

::: {.notes}
- MongoDB is a popular noSQL, document-oriented database.
- That collection is a list of nested JSON objects.
- We could add any structure that we need.

References

- <https://www.mongodb.com/docs/manual/reference/sql-comparison/>
- <https://www.mongodb.com/basics/aggregation-pipeline>
:::

## MongoDB Queries

:::: {.columns}

::: {.column}
*MongoDB*

```{sql}
collection.find()
```
&nbsp;
```{sql}
collection.find(
    {
        "name": "Wild2"
    }
)
```
&nbsp;
```{sql}
collection.find({
    "specs.orbitalPeriod": {
        "$gt": 5,
        "$lt": 85
    }
})
```
:::

::: {.column}
*SQL*

```{sql}
SELECT * FROM collection
```
&nbsp;
```{sql}
SELECT *
FROM collection
WHERE name = "Wild2"



```
&nbsp;
```{sql}
SELECT *
FROM collection
WHERE specs.orbitalPeriod > 5
  AND specs.orbitalPeriod < 85



```
:::

::::

::: {.notes}
- CRUD: Create, **Read**, Update, Delete (see `src/pymongo_crud.py`)
- Understanding relational model and how to use SQL can help you understand the context of MongoDB, but it's not necessary to know SQL to use MongoDB (it might even be detrimental!).
:::

## MongoDB Aggregation Pipelines

Aggregation Pipelines are a more flexible way to query data.

```{sql}
# Standard approach

collection.find(
    { "specs.orbitalPeriod": { "$lt": 100 } },
    { "name": 1, "specs.orbitalPeriod": 1, "_id": 0}
).sort("orbitalPeriod", 1)


```

```{sql}
# Aggregation version of the same query

collection.aggregate([
    { "$match": { "specs.orbitalPeriod": { "$lt": 100 } } },
    { "$project": { "name": 1, "specs.orbitalPeriod": 1, "_id": 0} },
    { "$sort": { "specs.orbitalPeriod": 1 } }
])


```

::: {.notes}
- Aggregation Pipelines: A way to transform data in MongoDB (see `src/pymongo_aggregation.py`)
:::

## MongoDB Join Queries

```{sql}
orders.insert_many( [
    { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
    { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
] )
inventory.insert_many( [
    { "_id" : 1, "sku" : "almonds", "in-stock" : 120 },
    { "_id" : 3, "sku" : "cashews", "in-stock" : 60 },
    { "_id" : 4, "sku" : "pecans", "in-stock" : 70 }
] )
```

```{sql}
orders.aggregate([
    { "$lookup": {
            "from": "inventory",
            "localField": "item",
            "foreignField": "sku",
            "as": "inventory_documents"
        }
    } ])
```
```{sql}
   _id     item  price  quantity  inventory_documents
0    1  almonds     12         2  [{'_id': 1, 'sku': 'almonds', "in-stock": 120}]
1    2   pecans     20         1  [{'_id': 4, 'sku': 'pecans', "in-stock": 70 }]
```

::: {.notes}
- Joins are implemented using `$lookup` (see `src/pymongo_join.py`).
- This returns two records: almonds and pecans.
:::
