Add support for fetching and ingesting Arrow Table/RecordBatch data

[mauropagano]

It's hard to deny how Arrow has become the standard de-facto for in-memory representation of tabular data.
Multiple competing product (Snowflake, BigQuery, etc) as well as libraries (ADBC, TurboODBC) enable extracting data as Arrow, unlocking better performance, lower memory usage and increased consistency / interoperability between platforms.

In the python space, there are a number of libraries, especially around data processing, data science and ML (pandas, polars, duckdb, etc) that work on Arrow data natively, normally via zero-copy.

It would be extremely beneficial to have python-oracledb be able to:

1. Extract data as Arrow, something like cursor.fetch_as_arrow() that can return either an Arrow Table or RecordBatch from the query. This method could bypass a python representation, speed up data extraction and ultimately keep Oracle closer to where some processing occurs.
2. Opposite direction, enabling ingesting Arrow Table/RecordBatch into the database, something like cursor.executemany("', arrow_object) could skip python representation, use less memory and ultimately entice users to rely more on Oracle for that processing that works better in-db / storing of data produced elsewhere

[anthony-tuininga]

Thanks, @mauropagano. We will look into this and get back to you.

[cjbj]

Tagging @aosingh who is exploring the space (e.g. he created apache/arrow-nanoarrow#502) and working with us on what python-oracledb could do.

@mauropagano if you have psuedocode of end-to-end uses of this datatype, and how it would plug in to Pandas, SQLAlchemy etc (or suggestions of what would need to be done to such libraries), please share.

[mauropagano]

Thanks @cjbj

These are a couple quick pseudo-code examples that come to mind, but really this feature would enable saving time / memory of almost all use cases where data starts or land in Oracle and any of the modern python data processing/science libs are in the mix.

The main goal of this ER in my mind is to bypass going through python types just to immediately after going into arrow (or viceversa), that takes a significant toll on performance and memory for no real benefit.

Some examples below will look a bit clunky because as of now there is no yet a zero-copy way to create a pandas df from arrow⁽¹⁾, I believe

⁽¹⁾lots of ways have been already added in pandas2 to go from at-rest formats to pandas with arrow backend, you just can't yet do "map" a df on top of that arrow table

Extracting data from Oracle to pandas because you need to apply fancy lib X that requires pandas

In batches, via pyarrow.RecordBatch

cursor.execute("....")
 # this would return a pyarrow.RecordBatch, could work like fetchmany()
for rb in cursor.fetch_record_batch(n=100_000):
     # the to_pandas() is because of (1) , but this is DRASTICALLY faster than tuple/dict -> pandas
     fancy_lib_output = fancy_lib.do_fancy_stuff(rb.to_pandas()) 

All at once, via pyarrow.Table

cursor.execute("....")
# same (1) applies, once to_pandas() can be skipped for some pd.DataFrame(arrow_obj) you'd save the temporary 2x memory 
df = cursor.fetch_arrow_table().to_pandas(self_destruct=True)
fancy_lib_output = fancy_lib.do_fancy_stuff(df) 

Ingesting data into Oracle

In batches, from pq

pf = pyarrow.parquet.ParquetFile(f)
# there are a lot of ways to do this, picking reading a single row_group at time vs iter_batches() just to minimize waste
for rg_num in range(pf.num_row_groups) :
     cursor.execute("insert into ...", pf.read_row_group(rg_num)) # executemany maybe? turboodbc calls it executemanycolumns()

All at once, via pandas.read_csv(..., dtype_backend="pyarrow") (somewhat example of (1) above, thought this is quite silly, if the goal is to just load csv there are better ways

df = pd.read_csv(f, dtype_backend="pyarrow") 
cursor.execute("insert into ...", df)

From other libraries with arrow backend, e.g. polars

for df_slice in df.iter_slices(n_rows=100_000):  # df here is polars.DataFrame
    cursor.execute("insert into ...", df_slice.to_arrow()) # to_arrow() is mostly zero-copy 

Hope it helps, anything else I can provide just let me know

[hvbtup]

Note: Arrow is not the only format for storing big tables in a columnar format.
There is also the Apache Parquet format, but Oracle already supports reading and writing parquet files on the DB side, see https://blogs.oracle.com/datawarehousing/post/parquet-files-oracle-database.

I think what would generally be more useful is a SQL*Plus (and/or sqlcli) command to upload (or download) files as BLOB data from/to the client to the DB.

[mauropagano]

@cjbj I was wondering if you guys made a decision about supporting Arrow or not

[cjbj]

@mauropagano work is ongoing. There are some technical and legal things to be done before we can release anything.

[cjbj]

An update on this project, thanks to @aosingh we are well on track for the next release of python-oracledb to have support for querying data into Python DataFrame Interchange Protocol format. (Inserting into the DB is a future task).

Comments welcome. (Tagging @mauropagano)

Examples might be like the following (subject to last minute changes!).

To use data in Pandas:

# Get an OracleDataFrame.
sql = "select id, name from SampleQueryTab order by id"
odf = connection.fetch_df_all(statement=sql, arraysize=100)

# Get a Pandas DataFrame from the data: this is a zero copy call
df = pandas.api.interchange.from_dataframe(odf)

# Perform various Pandas operations on the DataFrame
print(df.columns)

and to write in Parquet file format

sql = "select id, name from SampleQueryTab order by id"
pqwriter = None

for odf in connection.fetch_df_batches(statement=sql, size=FETCH_BATCH_SIZE):

    pyarrow_table = pyarrow.Table.from_arrays(
        arrays=odf.column_arrays(), names=odf.column_names()
    )

    if not pqwriter:
        pqwriter = pq.ParquetWriter(PARQUET_FILE_NAME, pyarrow_table.schema)

    print(f"Writing a batch of {odf.num_rows()} rows")
    pqwriter.write_table(pyarrow_table)

pqwriter.close()

We'll do some more testing before pushing anything to GitHub.

[mauropagano]

Thanks @cjbj , this is really exciting!

Few questions based on the examples:

1. What does .fetch_df_all() and .fetch_df_batches() return exactly? Asking because pyarrow.Table.from_arrays() is a little misleading (pyarrow.Table.from_dataframe() seems a bit more natural)
2. Does it mean that python-oracledb will provide a concrete implementation of the DataFrame class (which is abstract in the protocol specification) ?
3. Internally, does the API still fetches data from db into python objects? I'm assuming no (to unblock the perf gains this change can bring), just checking
4. There is no mention about the arrow conversion being zero-copy, from the protocol spec that should be as well (not just pandas), again just checking
5. Do you have a sense if the ingest will come in the release after the next or if it's a longer term goal?

Again, this is really exciting, thanks for making it happen!

[cjbj]

@mauropagano They return OracleDataFrame which is an Oracle implementation of the DataFrame class shown in https://data-apis.org/dataframe-protocol/latest/API.html

Current doc is:

Connection.fetch_df_all(statement, parameters=None, arraysize=None)

Fetches all rows of the SQL query statement, returning them in an OracleDataFrame object. An empty OracleDataFrame is returned if there are no rows available.

The parameters parameter can be a list of tuples, where each tuple item maps to one bind variable placeholder in statement. It can also be a list of dictionaries, where the keys match the bind variable placeholder names in statement.

The arraysize parameter can specified to tune performance of fetching data across the network. It defaults to defaults.arraysize. Internally, the fetch_df_all()’s Cursor.prefetchrows size is always set to the value of the explicit or default arraysize parameter value.

and

Connection.fetch_df_batches(statement, parameters=None, size=None)

This returns an iterator yielding the next size rows of the SQL query statement in each iteration as an OracleDataFrame object. An empty OracleDataFrame is returned if there are no rows available.

The parameters parameter can be a list of tuples, where each tuple item maps to one bind variable placeholder in statement. It can also be a list of dictionaries, where the keys match the bind variable placeholder names in statement.

The size parameter controls the number of records fetched in each batch. It defaults to defaults.arraysize. Internally, the fetch_df_batches()’s Cursor.arraysize. and Cursor.prefetchrows sizes are always set to the value of the explicit or default size parameter value.

    sql = "select * from departments"
    odf = connection.fetch_df_all(statement=sql, arraysize=1000)
    print(type(odf))

gives:

<class 'oracledb.interchange.dataframe.OracleDataFrame'>

We'd be very happy to hear of suggestions. I'm tempted to designate the support as "pre-view" which will give us scope to break the API if better ideas come up during the early days.

I don't have a timeframe for INSERT support. I'd like it to be soon, but let us get this release out before we prioritize all the tasks to do.

[mauropagano]

Thanks for sharing the docstrings.

I believe these two operations cover what most people would like to do here.
Even though I can't think of any significant change on top of what you shared, very much agreed the preview API makes sense this early on.

Is this going to work in both thin and thick mode?

Out of curiosity, do you have any very-rough-and-subject-to-change numbers around speedup and memory footprint changes?

[cjbj]

It will work in Thin & Thick.

Performance is being checked; we have one area of improvement that needs further investigation post initial release.

I will update my previous comments and change to use the current API doc.

[anthony-tuininga]

@mauropagano, the code has been uploaded. I have initiated a build which you can use for testing once it completes. Let us know any feedback you have!