Does xarray-sql load datasets lazily

[ahuang11]

Based on:
ctx.from_dataset('air', ds, chunks=dict(time=24)) # the dataset needs to be chunked!

It seems to be lazily loaded, but I wanted to confirm.

[alxmrs]

Hey Andrew, thanks, this is an excellent question.

Here is a more realistic demo colab notebook for xarray-sql: https://colab.research.google.com/drive/1TJ3I4I0UYbsUrxJ02vKm7UROJaJ8Za3V?usp=sharing.

In this notebook, loading a filtered down ERA5 monthly aggregate dataset takes ~50s to load. This suggests to me that xarray-sql is not currently lazy, despite our best efforts. This confirms what I suspected about the current way we register dataset with Arrow:

https://github.com/alxmrs/xarray-sql/blob/fa436e86f363851f5c597c1fb668535a474a26ea/xarray_sql/df.py#L65

Using a record batch reader seems to require a chunked scan over the dataset before creating the pyarrow Table.

We're currently working on a new way to register datasets into Datafusion via the Dataframe interchange protocol #86. According to my reading of the arrow docs and interchange protocol spec, this should provide a way for xarray-sql to load datasets in a truly lazy way.

[alxmrs]

I just read the sources from the pyarrow side of the dataframe interchange. Alas, I am skeptical if after this implementation, that loading xarray-sql will be lazy: https://arrow.apache.org/docs/_modules/pyarrow/interchange/from_dataframe.html#from_dataframe.

[alxmrs]

Update: @RohanDisa and I are exploring a new approach to achieve proper laziness (we've been pair programming for a couple of weeks).

[alxmrs]

Hey @ahuang11 (cc: @RohanDisa), I've nearly got an implementation for lazy evaluation in #100. When it's finished, I plan on making a new release of this library.

It was non-trivial to figure out -- it required more than merely integrating arrow and datafusion. It's possible that there is an error in datafusion-python. For this reason, I'm introducing native code to this package.

[ahuang11]

Wow, thank you for letting me know! Excited for the release.

[alxmrs]

Hey Andrew. I think we can safely close this issue. Now, no data is loaded on dataset registration. Instead, all loading happens after a collect() statement in DataFusion (e.g. like to_pandas()). See this demo notebook to confirm.

https://colab.research.google.com/drive/1mCqEDwvxjhmsJVMGg6Zf1zvFR8qFM0jI#scrollTo=v--q0-qbv5y0