Chapter 24 — Big Data Concepts, SQL, and Distributed Computing
When data outgrows memory, tools and approaches change. This chapter introduces SQL with pandas, big-data ideas like sampling and chunked processing, the MapReduce pattern in pure Python, and how frameworks like Spark scale these ideas to clusters.
Learning Objectives
- Query data with SQL via pandas and SQLite.
- Process large files in chunks.
- Use sampling to analyze data that doesn't fit in memory.
- Implement a MapReduce word count in pure Python.
- Understand the differences between pandas, SQL databases, and Spark.
- Reason about when distributed processing is warranted.
Prerequisites / Imports
In [1]:
import sqlite3
import pandas as pd
import numpy as np
from collections import Counter
1 SQL with pandas and SQLite
SQLite ships with Python. We create an in-memory database, load data, and query with SQL.
In [1]:
sales = pd.DataFrame({
'product': ['A','B','A','C','B','A','C','B'],
'region': ['North','North','South','North','South','South','South','North'],
'qty': [10, 5, 7, 3, 8, 12, 6, 4],
'price': [12.5, 9.0, 12.5, 25.0, 9.0, 12.5, 25.0, 9.0],
})
conn = sqlite3.connect(':memory:')
sales.to_sql('sales', conn, index=False, if_exists='replace')
query = "SELECT product, SUM(qty*price) AS revenue FROM sales GROUP BY product ORDER BY revenue DESC"
pd.read_sql(query, conn)
| product | revenue | |
|---|---|---|
| 0 | A | 362.5 |
| 1 | C | 225.0 |
| 2 | B | 153.0 |
2 More SQL
Filtering, joins, and aggregation mirror pandas operations but in declarative SQL.
In [1]:
print('Top regions by revenue:')
print(pd.read_sql('SELECT region, SUM(qty*price) AS revenue FROM sales GROUP BY region ORDER BY revenue DESC', conn).to_string(index=False))
print('\nHigh-value transactions:')
pd.read_sql('SELECT product, region, qty*price AS value FROM sales WHERE qty*price > 100 ORDER BY value DESC', conn)
Top regions by revenue: region revenue South 459.5 North 281.0 High-value transactions:
| product | region | value | |
|---|---|---|---|
| 0 | A | South | 150.0 |
| 1 | C | South | 150.0 |
| 2 | A | North | 125.0 |
3 Chunked Processing
For files too large to load at once, read in chunks and aggregate incrementally.
In [1]:
rng = np.random.default_rng(0)
big = pd.DataFrame({'id': range(100000), 'category': rng.choice(['a','b','c'], 100000), 'value': rng.normal(50, 10, 100000)})
big.to_csv('big_example.csv', index=False)
big.to_sql('big_example', conn, index=False, if_exists='replace')
total = 0
n_rows = 0
for chunk in pd.read_csv('big_example.csv', chunksize=20000):
total += chunk['value'].sum()
n_rows += len(chunk)
print('rows processed:', n_rows)
print('sum of value (chunked):', round(float(total), 2))
print('sum of value (in-memory check):', round(float(big["value"].sum()), 2))
rows processed: 100000 sum of value (chunked): 4998981.92 sum of value (in-memory check): 4998981.92
4 Sampling for Big Data
A representative sample often yields the same insight at a fraction of the cost.
In [1]:
sample = big.sample(frac=0.05, random_state=1)
print('sample size:', len(sample), 'of', len(big))
print('full mean:', round(float(big['value'].mean()), 3))
print('sample mean:', round(float(sample['value'].mean()), 3))
print('category counts (sample):'); print(sample['category'].value_counts())
sample size: 5000 of 100000 full mean: 49.99 sample mean: 50.001 category counts (sample): category c 1709 b 1694 a 1597 Name: count, dtype: int64
5 MapReduce in Pure Python
MapReduce processes data by mapping each item to key–value pairs, then reducing by key. A word count is the canonical example.
In [1]:
documents = ['the data data science', 'science of data', 'big data is big']
# Map: emit (word, 1) for each word
def mapper(doc):
return [(w.lower(), 1) for w in doc.split()]
# Reduce: sum counts per word
def reducer(pairs):
c = Counter()
for k, v in pairs:
c[k] += v
return c
mapped = []
for doc in documents:
mapped.extend(mapper(doc))
result = reducer(mapped)
print(dict(result))
{'the': 1, 'data': 4, 'science': 2, 'of': 1, 'big': 2, 'is': 1}
6 When to Go Distributed
| Approach | When to use |
|---|---|
| pandas | Data fits in memory (roughly < a few GB) |
| SQL database | Relational querying, persistence, concurrency |
| Dask / Modin | Out-of-core pandas on one machine |
| Spark | Multi-node clusters, hundreds of GB to TB+ |
Spark (PySpark) implements a distributed DataFrame API and a MapReduce engine across a cluster. PySpark is not installed here, but the concepts transfer directly from the pure-Python MapReduce above.
Case Study: Aggregating Logs in Chunks
We simulate server logs, compute revenue per category with chunked reading, and compare to a SQL GROUP BY — two paths to the same answer.
In [1]:
# Path 1: chunked pandas
chunk_revenue = Counter()
for chunk in pd.read_csv('big_example.csv', chunksize=15000):
s = chunk.groupby('category')['value'].sum()
for cat, val in s.items():
chunk_revenue[cat] += val
print('Chunked revenue by category:')
for k, v in chunk_revenue.items():
print(f' {k}: {v:.1f}')
# Path 2: SQL GROUP BY
print('\nSQL revenue by category:')
print(pd.read_sql('SELECT category, SUM(value) AS revenue FROM big_example GROUP BY category', conn).to_string(index=False))
Chunked revenue by category:
a: 1661439.6
b: 1674044.0
c: 1663498.2
SQL revenue by category:
category revenue
a 1.661440e+06
b 1.674044e+06
c 1.663498e+06
Exercises
- Create a SQLite table from a DataFrame and run a GROUP BY query.
- Write a SQL query that filters rows by a condition and sorts the result.
- Read a CSV in chunks of 10,000 rows and compute the overall mean of a column.
- Take a 5% sample of a dataset and compare its mean to the full-data mean.
- Implement MapReduce word count on a list of five sentences.
- Modify the reducer to count characters instead of words.
- Explain when you would choose SQL over pandas.
- Describe one task where Spark would outperform pandas.
- Discuss a trade-off of sampling very large data.
- Combine chunked reading with a per-chunk aggregation of your choice.
Python Data Science: From Foundations to Applications — Chapter 24