You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "InariSystems (via GitHub)" <gi...@apache.org> on 2025/04/04 03:14:08 UTC
[I] Python/adbc/sqlite User-defined function support [arrow-adbc]
InariSystems opened a new issue, #2671:
URL: https://github.com/apache/arrow-adbc/issues/2671
### What feature or improvement would you like to see?
Within sqlite, according to the documentation, user defined functions (UDFs) are connection-specific:
> If an application uses more than one database connection then application-defined SQL functions must be added to each database connection separately.
[source](https://www.sqlite.org/c3ref/create_function.html)
As such, for any system using adbc's sqlite driver, if a UDF is to be used, currently there's no method, as far as I can see, of being able to register a UDF. To do so, the underlying sqlite3 class structure would need to be accessed, which doesn't seem to exist at current.
To showcase this, you can look to the example code below where you can see a failure. To explain the code beforehand, a Database is created in memory, and a connection to that database is shared between a sqlite3 connection, and an adbc-sqlite connection. a table is created, and a trigger is created for this table, where upon insertion of data onto the table, a UDF called world will be used to add a "world" string to the world column.
This UDF will be only registered on the sqlite3 connection, and the insert will be done by a adbc_ingest() function call. This will lead to a failure, as the adbc connection does not have that function.
```
import adbc_driver_sqlite.dbapi as adbc
import sqlite3
import pyarrow as pa
#creating in memory connection string
db="file::memory:?cache=shared"
#making connections/cursor
sqlite_conn=sqlite3.connect(database=db,autocommit=True)
arrow_conn=adbc.connect(db,autocommit=True)
arrow_cursor=arrow_conn.cursor()
#making pyarrow table to ingest
schema=pa.schema([("hello",pa.string())])
table=pa.table(["hello"],schema=schema)
#setup for the UDF, note only sqlite3 connection gets the UDF
def world():
return "world"
sqlite_conn.create_function('world',1,world)
#generating database table
sqlite_conn.execute("""CREATE TABLE hello_world(
hello TEXT,
world TEXT
);""")
#making the trigger
sqlite_conn.execute("""
CREATE TRIGGER table_update
AFTER INSERT ON hello_world
BEGIN
UPDATE hello_world
SET world = world();
END;
""")
#ingesting the data, error will happen here
arrow_cursor.adbc_ingest('hello_world',table,'append')
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Python/adbc/sqlite User-defined function support [arrow-adbc]
Posted by "InariSystems (via GitHub)" <gi...@apache.org>.
InariSystems commented on issue #2671:
URL: https://github.com/apache/arrow-adbc/issues/2671#issuecomment-2777527659
By your first suggestion, that says to me that there's a normal python standard library object that gets instantiated when you create a adbc_connection? Is that correct? If so, that would be a perfect solution, it was actually what I was originally trying to find when I figured this out, but that search came up dry.
In the short term, simply knowing how to access that python object connection would be sufficient, and long term, I imagine that a function could be created that simply pointed to that object to use its various UDF creation functions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Python/adbc/sqlite User-defined function support [arrow-adbc]
Posted by "InariSystems (via GitHub)" <gi...@apache.org>.
InariSystems commented on issue #2671:
URL: https://github.com/apache/arrow-adbc/issues/2671#issuecomment-2778989534
Ah, that's unfortunate. That's probably still the most probable way of going about it. The only other solution I've been able to think of is pointing people to create/use extensions, but unless an extension already exists for purpose, which I've found is often not the case, that would mean making one, which would mean very much so increased complexity of projects on the end developer.
If I could be of any help in investigating this extraction, I would be willing to help out.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Python/adbc/sqlite User-defined function support [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #2671:
URL: https://github.com/apache/arrow-adbc/issues/2671#issuecomment-2777682440
> By your first suggestion, that says to me that there's a normal python standard library object that gets instantiated when you create a adbc_connection? Is that correct? If so, that would be a perfect solution, it was actually what I was originally trying to find when I figured this out, but that search came up dry.
>
> In the short term, simply knowing how to access that python object connection would be sufficient, and long term, I imagine that a function could be created that simply pointed to that object to use its various UDF creation functions.
Unfortunately, I meant it the other way: I meant I'd be curious if I could get the sqlite_connection out of the standard library SQLite connection, so that I could then justify adding a special API to the ADBC bindings to construct an ADBC connection out of it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Python/adbc/sqlite User-defined function support [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #2671:
URL: https://github.com/apache/arrow-adbc/issues/2671#issuecomment-2777502379
Thanks for filing this.
The struggle is that this gets into the weeds of binding- and database-specific functionality. IMO, being able to support UDFs in the SQLite driver would not be too hard...but being able to support *Python* UDFs is another level of complexity. There's also the challenge that ADBC mostly tries to be a generic API and this is very much a non-generic feature.
IMO, I wouldn't be against trying to support this, but it's a question of maintainer/contributor availability. I can see two ways:
- Maybe we can "steal" the SQLite connection out of the Python standard library object somehow. Then we could provide an API to wrap an existing connection in the ADBC API.
- We offer SQLite driver specific UDF functionality and the Python ADBC SQLite package has to use them to implement Python UDFs.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org