feat: Improve `read_csv` compatibility across backends

[grihabor]

Is your feature request related to a problem?

I'm trying to use read_csv in multiple backends at the same time, but unfortunately the options do not match across backends, here is the compatibility table for flights.csv:

Backend	Separator option	Header option	Schema option	Example
duckdb	sep	header	columns	uv run --with ibis-framework[duckdb] python import ibis con = ibis.connect('duckdb://') con.read_csv('flights.csv', sep='|', header=True, columns={ 'FlightDate': 'DATE', 'UniqueCarrier': 'VARCHAR', 'OriginCityName': 'VARCHAR', 'DestCityName': 'VARCHAR' }) DatabaseTable: ibis_read_csv_6zrnj6cuujhoxmdw5odszvpwxe FlightDate date UniqueCarrier string OriginCityName string DestCityName string
polars	separator	has_header	schema	uv run --with ibis-framework[polars] python import ibis import polars as pl con = ibis.connect('polars://') con.read_csv('flights.csv', separator='|', has_header=True, schema={ 'FlightDate': pl.Date, 'UniqueCarrier': pl.String, 'OriginCityName': pl.String, 'DestCityName': pl.String, }) DatabaseTable: ibis_read_csv_dnt5itr3nremxdn5hr6zsr55xa FlightDate date UniqueCarrier string OriginCityName string DestCityName string
datafusion	delimiter	has_header	schema	uv run --with ibis-framework[datafusion] python import ibis import pyarrow as pa con = ibis.connect('datafusion://') con.read_csv('flights.csv', delimiter='|', has_header=True, schema=pa.StructType([ pa.StructField('FlightDate', pa.DateType()), pa.StructField('UniqueCarrier', pa.StringType()), pa.StructField('OriginCityName', pa.StringType()), pa.StructField('DestCityName', pa.StringType()), ])) DatabaseTable: ibis_read_csv_tepbqv667jainbegezjvgutycy FlightDate date UniqueCarrier string OriginCityName string DestCityName string
pyspark	sep	header	schema	uv run --with ibis-framework[pyspark] python import ibis from pyspark.sql.types import DateType, StringType, StructType, StructField con = ibis.connect('pyspark://') con.read_csv('flights.csv', sep='|', header=True, schema=StructType([ StructField('FlightDate', DateType()), StructField('UniqueCarrier', StringType()), StructField('OriginCityName', StringType()), StructField('DestCityName', StringType()), ])) DatabaseTable: ibis_read_csv_tepbqv667jainbegezjvgutycy FlightDate date UniqueCarrier string OriginCityName string DestCityName string

What is the motivation behind your request?

No response

Describe the solution you'd like

Since Ibis claims to provide unified API for all backends, I suggest to improve
the compatibility and properly handle the following options for
ibis.expr.api.read_csv:

name	type	description	naming
separator	string	Single byte character to use as separator in the file.	Short names like sep might be confusing, no need to save letters. separator seems to be more common than delimiter.
has_header	bool	Indicate if the first row of the dataset is a header or not.	has_header is a better name than header because it clearly states the bool type.
schema	ibis.Struct	An optional schema representing the CSV files. If None, the CSV reader will try to infer it based on data in file.	schema seems to be more clear and common than columns

What version of ibis are you running?

10.6.0

What backend(s) are you using, if any?

DuckDB, Polars, DataFusion, PySpark

Code of Conduct

• I agree to follow this project's Code of Conduct

[NickCrews]

I think this is a great idea. The current behavior really undermines ibis's claims of write once execute anywhere.

the intended behavior of separator and has_header look good to me (I like the names you chose), and should be easy.

For schema, I would say the user should be able to pass anything that ibis.schema() accepts, and we use that util method to convert to an ibis.Schema(), and then translate to the relevant backend type, eg pyspark.StringType(). Since we are reading a table, I think we should use ibis.Schema, not ibis.dtype.Struct()

[NickCrews]

The other question is what to do about the other backends? They might not all support these options. I would lean towards doing nothing, and probably if a user tries this new API on those backends, they will get an error, and that's their problem for now. We can just continue to chip away at them. Probabaly would want to document this in the docstring.

[gforsyth]

I don't know that we ever documented this (oops) but internally we always thought of compatibility guarantees starting once you have a table expression (so after a read_* call)

But I do think this would be a nice thing to offer, especially if it's a separate API -- I didn't want to do this in the existing read_csv because of the problem of disambiguating keyword arguments.

[NickCrews]

I see, that seems like a good first guarantee.

Like you think there should be a read_csv_common() or something? Maybe a backend.common.read_csv()? I would vote for it being the other way around:

• backend.read_csv() does our best to use the common API between backends, and I guess only supports the API that ALL backends support.
• backend.raw.read_csv() (or something similar) uses the backend-specific API and just passes stuff through transparently. Basically what the current read_csv() does. We could stuff other backend-specific-versions of the common APIs into this namespace object as well.

This would be a breaking for users of the raw API, who would have to switch to the .raw.read_csv() version. But I think the most basic API we present should be agnostic, and users can reach for the more detailed one if needed.

[grihabor]

I think the most basic API we present should be agnostic, and users can reach for the more detailed one if needed.

I agree with @NickCrews here. It will be a breaking change, but it will make much more sense. (I was initially confused when I used read_csv for the first time, as I expected it to be backend-agnostic 😄)

[gforsyth]

I agree that's the ideal target state, and having generic vs specific methods will avoid the nasty disambiguation step. For a breaking change like this, it's probably worth adding the backend-specific "raw" methods first and including a warning under the current read_csv about the upcoming changes to allow for a graceful switch for users. Then in the following major version we make the switch

[NickCrews]

Sounds like a good migration path to me!

Any thoughts on if we want a full namespace, eg conn.raw.read_csv(), or should it be just conn.read_csv_raw() and we tack on the _raw() suffix for any other methods (eg read_parquet_raw(), etc)? I don't think I care, I think either would work. If we do ever end up getting to a conn.ddl.* namespace as mentioned in #8382 (comment), then I think I would lean towards the namespace.

[grihabor]

Agreed 👍

Any thoughts on if we want a full namespace

Backends already have raw_sql method, I think it makes sense to keep the API consistent and go with raw_read_csv.

we tack on the _raw() suffix for any other methods (eg read_parquet_raw(), etc)?

For consistency, I would deprecate all read_* methods and replace them with raw_read_*, e.g. for duckdb this includes read_json, read_geo, read_csv, read_parquet, read_xlsx, read_sqlite, read_mysql, read_postgres, read_delta and in the future read_iceberg.