Reading Large CSV

[vmsaipreeth]

Hi team,

Just curious to know what are the different ways to read a large CSV (typically 10-20GB) file into ibis. And how seamlessly can we achieve this using ibis?

Thanks!

[lostmygithubaccount]

@vmsaipreeth it's going to be very dependent on your backend -- typically a large CSV like that will expand some number in memory so you could experience out of memory (OOM) errors. You can try with con.read_csv("/path/to/file.csv") and see how it goes, or let us know about what you're trying to accomplish (which backend and why such a large CSV) so we can provide more guidance!

[vmsaipreeth]

We have Trino as our backend.

We are exploring different approaches to read such large CSV files into IBIS and perform operations like joins etc.

[cpcloud]

Which Trino connector will you be using?

[vmsaipreeth]

We can't really say which connector because it is not setup that way.

[cpcloud]

@vmsaipreeth Hey, this is still on our radar! We've been at Euroscipy last week so a few of us were out.

For reading CSVs into ibis there are a few options, mostly breaking down into local versus remote:

Local Backends

Backends like DuckDB, Polars, and DataFusion can read CSV files and they typically do well with them. If you're prototyping an analysis I would start with one of these backends.

Remote Backends

Support for reading CSV files varies quite a bit here. Some backends like Snowflake and ClickHouse have good support, while Trino does not support reading CSV files at all. More traditional DMBSs like PostgreSQL and MySQL do not support the read_csv API. Trino also doesn't support reading in a CSV file.

One of the challenges with Trino, PostgreSQL and MySQL is that there's no clear way to efficiently insert batches of rows into a table from a CSV file, or really any kind of file for that matter.

The best option for reading large CSV files is probably to use one of the Trino connectors that supports external tables backend by CSV files (I think the Hive connector may be the only one?).