Optimisasi dan rencana kueri

Menskalakan dan Mengoptimalkan Pipeline Data dengan Polars

Liam Brannigan

Data Scientist & Polars Contributor

Pengenalan optimisasi kueri

Gambar tabel dengan baris dan kolom

Pengenalan optimisasi kueri

Gambar tabel dengan baris dan kolom

Pengenalan optimisasi kueri

Gambar operasi berjalan seri

Pengenalan optimisasi kueri

Gambar operasi berjalan paralel

Pengenalan optimisasi kueri

Pipa dengan operasi duplikat dijalankan dua kali

Pengenalan optimisasi kueri

Pipa dengan operasi duplikat dijalankan dua kali

Jenis permintaan terbanyak per departemen

department_request_types = (
    requests





)

Jenis permintaan terbanyak per departemen

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




)

Jenis permintaan terbanyak per departemen

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



)

Jenis permintaan terbanyak per departemen

department_request_types = (
    requests    
    .filter(pl.col("STATUS") == "Completed")
    .group_by("DEPARTMENT")
    .agg(pl.col("TYPE").n_unique().alias("n_request_types"))


)

Jenis permintaan terbanyak per departemen

department_request_types = (
    requests
    .filter(pl.col("STATUS") == "Completed")
    .group_by("DEPARTMENT")
    .agg(pl.col("TYPE").n_unique().alias("n_request_types"))
    .sort("n_request_types", descending=True)
    .head(5)
)

Rencana naif

Rencana tidak teroptimisasi

print(department_request_types)

Rencana tidak teroptimisasi

print(department_request_types)








        Csv SCAN [311_Service_Requests.csv]
        PROJECT */39 COLUMNS

Rencana tidak teroptimisasi

print(department_request_types)






      FILTER [(col("STATUS")) == ("Completed")]
      FROM
        Csv SCAN [311_Service_Requests.csv]
        PROJECT */39 COLUMNS

Rencana tidak teroptimisasi

print(department_request_types)



    AGGREGATE[maintain_order: false]
      [col("TYPE").n_unique().alias("n_request_types")] BY [col("DEPARTMENT")]
      FROM
      FILTER [(col("STATUS")) == ("Completed")]
      FROM
        Csv SCAN [311_Service_Requests.csv]
        PROJECT */39 COLUMNS

Rencana tidak teroptimisasi

print(department_request_types)

SLICE[offset: 0, len: 5]
  SORT BY [descending: [true]] [col("n_request_types")]
    AGGREGATE[maintain_order: false]
      [col("TYPE").n_unique().alias("n_request_types")] BY [col("DEPARTMENT")]
      FROM
      FILTER [(col("STATUS")) == ("Completed")]
      FROM
        Csv SCAN [311_Service_Requests.csv]
        PROJECT */39 COLUMNS

Rencana teroptimisasi

print(department_request_types.explain())

Rencana teroptimisasi







        Csv SCAN [311_Service_Requests.csv]
        PROJECT 3/39 COLUMNS
        SELECTION: [(col("STATUS")) == ("Completed")]

Rencana teroptimisasi






      simple pi 2/2 ["TYPE", "DEPARTMENT"]
        Csv SCAN [311_Service_Requests.csv]
        PROJECT 3/39 COLUMNS
        SELECTION: [(col("STATUS")) == ("Completed")]

Rencana teroptimisasi



    AGGREGATE[maintain_order: false]
      [col("TYPE").n_unique().alias("n_request_types")] BY [col("DEPARTMENT")]
      FROM
      simple pi 2/2 ["TYPE", "DEPARTMENT"]
        Csv SCAN [311_Service_Requests.csv]
        PROJECT 3/39 COLUMNS
        SELECTION: [(col("STATUS")) == ("Completed")]

Rencana teroptimisasi

SORT BY [slice: (0, 10, ...), descending: [true]] [col("n_request_types")]
  FILTER col("n_request_types").dynamic_predicate() FROM
    AGGREGATE[maintain_order: false]
      [col("TYPE").n_unique().alias("n_request_types")] BY [col("DEPARTMENT")]
      FROM
      simple pi 2/2 ["TYPE", "DEPARTMENT"]
        Csv SCAN [311_Service_Requests.csv]
        PROJECT 3/39 COLUMNS
        SELECTION: [(col("STATUS")) == ("Completed")]

Cari 10 baris teratas pada n_request_types
Saring sisanya
Urutkan 10 baris tersebut

Rencana teroptimisasi dalam grafik

print(department_request_types.show_graph())

Tampilan grafik rencana teroptimisasi yang menunjukkan pemindaian CSV dengan optimisasi.

Optimisasi lanjutan

(
    requests








)

Optimisasi lanjutan

(
    requests
    .filter(pl.col("STATUS") == "Completed")
    .filter(pl.col("DEPARTMENT") == "Sanitation")






)

Optimisasi lanjutan

(
    requests
    .filter(pl.col("STATUS") == "Completed")
    .filter(pl.col("DEPARTMENT") == "Sanitation")
    .with_columns(
        pl.col("TYPE").str.to_lowercase().alias("type_lower")
    )
    .with_columns(
        pl.col("STATUS").str.to_lowercase().alias("status_lower")
    )
)

Optimisasi lanjutan



  Csv SCAN [311_Service_Requests.csv]
  PROJECT */39 COLUMNS
  SELECTION: [([(col("DEPARTMENT")) == ("Sanitation")]) & ([(col("STATUS")) == ("Completed")])]

Predikat AND digabung

Optimisasi lanjutan

 WITH_COLUMNS:
 [col("TYPE").str.to_lowercase().alias("type_lower"), col("STATUS").str.to_lowercase().alias("status_lower")]
  Csv SCAN [311_Service_Requests.csv]
  PROJECT */39 COLUMNS
  SELECTION: [([(col("DEPARTMENT")) == ("Sanitation")]) & ([(col("STATUS")) == ("Completed")])]

Predikat AND digabung
Ekspresi WITH_COLUMNS dikelompokkan

Ayo berlatih!

Menskalakan dan Mengoptimalkan Pipeline Data dengan Polars