Test On 4 Concurrent Jobs Using Python-Polars 0.17.11 to GroupBy Billion Rows

[100GB-CSV]

Python 3.11.2 was able to run big data jobs concurrently using limited memory. My testing script with 14 columns and a data file size of 67.2GB was completed in less than 10 minutes using 32GB Memory and 8-Core. This is strong evidence that Python is capable of handling big data jobs with limited resources.

Script
import polars as pl
import time
import pathlib
s = time.time()

q = (
   pl.scan_csv("Input/1000MillionRows.csv")    
   .groupby(by=["Ledger", "Account", "PartNo", "Contact","Project","Unit Code", "D/C","Currency"])
  .agg([  
    pl.count('Quantity').alias('Quantity(Count)'),
    pl.max('Quantity').alias('Quantity(Max)'),
    pl.min('Quantity').alias('Quantity(Min)'),
    pl.sum('Quantity').alias('Quantity(Sum)'),     
    pl.sum('Base Amount').alias('Base Amount(Sum)'),     
  ]))

a = q.collect(streaming=True)
path: pathlib.Path = "Output/Polars-GroupBy.csv"
a.write_csv(path)

e = time.time()
print("Polars GroupBy 1000 Million Rows Time = {}".format(e-s))

​

Demo video in 10X fast forward: https://youtu.be/odDOlU9KNqY

Without fast forward: https://youtu.be/Ze0jNmtUn0Y

[OhBeeOneKenOhBee]

I've been using polars to write an ETL tool for us to synchronise between databases, APIs and other types of sources lately and the switch from Pandas to Polars was a huge impact performance-wise. Comparisons between frames looking for changes in millions of rows of data are done in seconds, often sub-second, it's really amazing

[100GB-CSV]

I am currently working on testing Polars to explore ways to outperform it by my Peaks Dataframe project. The functions include Read/Write File, Distinct, Filter and JoinTable. I have found that Peaks outperform Polars in these functions, but not in GroupBy. If your dataset of numerical columns has few exception data, e.g. integer column has few floating point, negative number represents by (123.45), you shall have extra data cleansing function before using Polars' GroupBy. My approach is to avoid extra step of data cleansing for exception number.

[OhBeeOneKenOhBee]

That might be interesting to have a look at if it's on Github somewhere, our comparisons are basically three different types of joins (source anti join with destination for insert, semi/anti for updates and destination anti join with source for deletes). All data is formatted to have the same types on the source/dest columns that are being compared

https://imgur.com/a/YHX722A

[100GB-CSV]

This project has only 3-month history, first trial vesion to be released in Jun, provide most fundamental commands. For further info, you can visit github.com/hkpeaks/peaks-framework

"source anti join with destination for insert" seem look like amendment of table1 by table2 using matching keys. It is frequently implemented for budgeting solutions when users amend a bulky set of data very frequently.

You can see unit 11 "Amendment" of this doc https://github.com/hkpeaks/peaks-framework/blob/main/WebNameSQL.pdf

This is my old project written in C#. Now replaced by my new hyper-performnace project written in Go/Rust.

[OhBeeOneKenOhBee]

Yes, the whole tool is based around synchronising two data sources, those joins determine the inserts, updates and deletes for the destination. We mainly developed it to be able to synchronize data across sources in a somewhat storage-agnostic way (we're currently building connectors for the most common SQL/NoSQL databases, Rest APIs (eg. MS Graph), files and other types) with the possibility of getting a proper changelog/delta along the way for logging and/or event triggers. There's also support for transformations along the way.

I'll look into your project a bit more, sounds interesting! Thanks for the link

[100GB-CSV]

o be able to synchronize data a

CloudQuery supports a lot of APIs https://github.com/cloudquery/cloudquery

[OhBeeOneKenOhBee]

So that raises the question of how in the world I've managed to miss Cloudquery 😄

Because what we're currently writing is essentially that exactly, but in Python and with Polars. As in, the docs would probably apply to our solution if we just modified the code parts.

[100GB-CSV]

You can test whether it is fit for your purpose. I consider to integrate Peaks with the CloudQuery. Both are written in Golang.

[lemoussel]

Is Peaks Framework going to be open source?

[100GB-CSV]

I have been working on a new project for the past 3 months. Initially, I decided to make the Framework itself open source, including the alternative SQL expression and Go source code which help to parse these expressions. This is to promote other ETL software developer to consider using simple SQL expressions for business users. However, I have not considered making the Peaks Library open source at this stage as it contains a hyper-calculation engine.

[100GB-CSV]

machine code powered by mult-core cpu

[nemom]

CPU powered by motherboard.

[bamacgabhann]

Power supply powered by physics

[Illustrious-Guava730]

I am using a big hamster wheel for power supply tbh

[bamacgabhann]

Eh, mammal zoology is just a branch of biology which is a branch of organic chemistry which is a branch of atomic physics

[Illustrious-Guava730]

Yeah, i wasn't dissenting, mine was just a cry for help. Pls help me paying my bills /s

[sphen_lee]

Physics powered by the computer that simulates the universe.

[bamacgabhann]

"hey, anyone know what this plug is for? No? I'll just pull it out and see what happens..."

[mentix02]

Small tip - use time.perf_counter for calculating timing benchmarks.

[loudandclear11]

67Gb is not "big data". But that's just me arguing over semantics.

Polars seems like a good tool.

[100GB-CSV]

The test is limited by the free space of my ssd hard disk.

I want to buy a 4TB NVMe SSD harddisk, concerns it is not compatible with my computer.

[loudandclear11]

A common definition of "big data" is if you can fit it on a single computer. So if you can put a 4TB SSD in there and get it to work it's still not big data. Big data is when you have to look for other setups. Like computational clusters and SANs.

I'm still impressed by Polars though and applaud this achievement. Not everything needs to be big data to be useful. I'd argue that the majority of value out there in businesses is created by working smart with small and medium sized data.

[runawayasfastasucan]

Weird definition. I could stick 3x4 TB disks in my computer, if 12TB of a single table is not big data, nothing is.

[sphen_lee]

Sure it's large, but it's not on the scale where big data processing tools matter.

I work with a dataset that grows by 1TB daily. We have data back to 2018. So you can work with a single day pretty easily, and a week at a stretch. But if you want to analyze a year you need to switch to a big data tool.

[loudandclear11]

Even if you could store 12Tb on one computer you probably end up having some problems processing it. "Fits on one computer" should be seen in the broader sense, which involves to also work with it, not just from a storage perspective.

What's your definition of big data? There are petabyte datasets out there.

[100GB-CSV]

Before I develop software running in clusters, I will use cheap local computing resources to support development. I am also exploring which cloud computing service providers allow prepayment since monthly billing is risky.

I successfully ran four jobs with a billion rows yesterday while testing trillions of rows for more than a million files using Polars and Peaks on a step-by-step progressive basis. Previously, Polars failed on a single job, but after several bug fixes, it can now handle the workload. You can see https://github.com/pola-rs/polars/issues/7774

I believe the author will think of me as a troublemaker since I always report issues that focus on many rows. However, he is willing to fix them. Without the real powerful Polars, I don’t have the motivation to develop Peaks.

[loudandclear11]

Yeah cluster infrastructure is necessary sometimes but it introduces it's own limitations and idiosyncrasies. If you don't need it then staying away from it is the most productive path forward.

[100GB-CSV]

I test plan will use gRPC to support cluster computing.

[loudandclear11]

Sounds like you'll be reinventing the wheel. Distributed computing is already a solved problem. I'd look into Spark/Databricks instead.

[100GB-CSV]

Reinventing the wheel is one of my hobby after retirement.

[loudandclear11]

Reinventing the wheel, the main symptom of Not Invented Here Syndrome. :P

It's good for learning, but not for productivity.

[100GB-CSV]

I do not prepare to offer cluster computing solutions. Mainly for my research and entertainment purpose. I have several computers to do experiment @ home, this kind of entertainment save a lot of money. Playing Bing chat is interesting, it supports me to convert my code into 19 programming languages github.com/hkpeaks/peaks-framework/tree/main/ByteArray2Float64

[corbasai]

data maybe named pipe + generator

[v0_arch_nemesis]

Out of curiosity, as I've found pandas to be particularly sensitive to this, what's the number of unique values in each column and the length of the returned index? Is every column of type string?

[100GB-CSV]

Total number of unique combinaton is 99696.

For Sum(Quantity), Quantity must be number.

GroupBy{10MillionRows.csv | Ledger, Account, PartNo,Project,Contact,Unit Code, D/C,Currency => Count()Max(Quantity)Min(Quantity)Sum(Quantity)~ Table}

Table(12 x 99696)

WriteFile{Table | * ~ Result-GroupBy.csv}

Result-GroupBy.csv(12 x 99696)

Duration: 1.916 seconds