Effektiv frågekörning

Skalning och optimering av datapipelines med Polars

Liam Brannigan

Data Scientist & Polars Contributor

Möt din instruktör

Liam Brannigan, Lead Data Scientist
Specialist inom maskininlärning och datateknik
Bidragsgivare till Polars

Picture of Liam Brannigan - the instructor of this course

Från laptop till molnet

Polars pipeline scaling from laptop to cloud

Är den här kursen för dig?

Polars course pages

Skapa en lat fråga med scan_csv
Skriva uttryck med filter, select och group_by

Kapitel 1 – optimering

Query optimization

Kapitel 1 – optimering

Query optimization

Kapitel 2 – effektiv I/O

Image of Polars ingesting multiple file types

Kapitel 3 – rikare datatyper

Diagram of nested data transformed into a table.

Kapitel 4 – skalning av pipelines

Image of large table being streamed.

Datamängd med Chicagos serviceärenden

The Mayor of Chicago's data analytics team

Inspektera datamängden

requests = pl.scan_csv("311_Service_Requests.csv",try_parse_dates=True)

A massive dataset, recording every service request from Chicago's citizens

Inspektera datamängden

requests.collect()

Inspektera datamängden

requests.collect().head(5)

shape: (5, 39)
| TYPE                          | STATUS    | DEPARTMENT     | CREATED_DATE        | ... |
| ---                           | ---       | ---            | ---                 | --- |
| str                           | str       | str            | str                 | ... |
|-------------------------------|-----------|----------------|---------------------|-----|
| Pothole in Street Complaint   | Completed | Transportation | 2019-12-16T10:09:08 | ... |
| Tree Trim Request             | Cancelled | Sanitation     | 2019-09-18T01:05:08 | ... |
| Garbage Cart Maintenance      | Completed | Sanitation     | 2021-01-24T09:14:58 | ... |
| Pothole in Street Complaint   | Completed | Transportation | 2019-03-21T10:41:01 | ... |
| Recycling Pick Up             | Completed | Sanitation     | 2021-02-16T08:28:59 | ... |

Begränsa antal bearbetade rader

requests.head(5)

Begränsa antal bearbetade rader

requests.head(5).collect()

shape: (5, 39)
| TYPE                            | STATUS    | DEPARTMENT     | CREATED_DATE        | ... |
| ---                             | ---       | ---            | ---                 | --- |
| str                             | str       | str            | str                 | ... |
|---------------------------------|-----------|----------------|---------------------|-----|
| Pothole in Street Complaint     | Completed | Transportation | 2019-12-16T10:09:08 | ... |
| Tree Trim Request | Completed | Sanitation     | 2019-09-18T01:05:08 | ... |
| Garbage Cart Maintenance        | Completed | Sanitation     | 2021-01-24T09:14:58 | ... |
| Pothole in Street Complaint     | Completed | Transportation | 2019-03-21T10:41:01 | ... |
| Recycling Pick Up               | Completed | Sanitation     | 2021-02-16T08:28:59 | ... |

Vänta med att anropa collect

Summering per avdelning

completed_by_department

Summering per avdelning

completed_by_department = requests

Summering per avdelning

completed_by_department = requests.filter(
    pl.col("STATUS") == "Completed"
)

Summering per avdelning

completed_by_department = requests.filter(
    pl.col("STATUS") == "Completed"
).collect()

Summering per avdelning

completed_by_department = requests.filter(
    pl.col("STATUS") == "Completed"
).collect().group_by("DEPARTMENT").len()

Summering per avdelning

completed_by_department = requests.filter(
    pl.col("STATUS") == "Completed"
).group_by("DEPARTMENT").len().collect()

shape: (10, 2)
| DEPARTMENT                     | len     |
| ---                            | ---     |
| str                            | u32     |
|-------------------------------|---------|
| 311 City Services              | 4859161 |
| Sanitation                     | 3406631 |
| Aviation                       | 2337842 |
| CDOT - Department of Transport | 1468240 |

Förgrenade frågor

completed_by_department = requests.filter(
    pl.col("STATUS") == "Completed"
).group_by("DEPARTMENT").len()

completed_by_month = requests.filter(
    pl.col("STATUS") == "Completed"
).group_by("MONTH").len()

Hur teamet kör detta idag

completed_by_department.collect()
completed_by_month.collect()

Köra förgrenade frågor

results = pl.collect_all(


)

Köra förgrenade frågor

results = pl.collect_all([
    completed_by_department,
    completed_by_month,
])

Köra förgrenade frågor

results[0]  # completed_by_department
shape: (10, 2)
| DEPARTMENT           | len     |
| ---                  | ---     |
| str                  | u32     |
|----------------------|---------|
| 311 City Services    | 4859161 |
| Sanitation           | 3406631 |
| Aviation             | 2337842 |
| Transport            | 1468240 |

results[1]  # completed_by_month
shape: (12, 2)
| MONTH | len  |
| ---   | ---  |
| i64   | u32  |
|-------|------|
| 1     | 2506 |
| 2     | 4566 |
| 3     | 2739 |
| 4     | 2922 |

Nu kör vi en övning!

Skalning och optimering av datapipelines med Polars