GitHub user davlee1972 edited a comment on the discussion: Proposal: ADBC file drivers with pluggable storage
I posted something to the user mailing list since our corporate GitHub won't allow any type of discussion posting, but here is something I've been working on for two years. I was working on open sourcing a file+sql+api(rest/grpc/graphql) "pluggable" package, but it has stalled for scheduling reasons.. For file sources it was using pyarrow.dataset or datafusion. For sql sources it was using ADBC, DBAPI, Arrow-ODBC. Additional custom sources could be injected imported python classes. The unit testing includes working with sqlite using four different pluggable source configurations: adbc sqlite, dbapi sqlite3, pyodbc (dbapi) with libsqlite3idbc.so driver, arrow-odbc with the same odbc driver. You basically configure a "gateway" definition that includes "schema" (aka contract) and a "source" definition. Optionally you can add options to configure reading and writing. There are two end user functions: get_data() and write_data() that works on any combination of the above. If you want to swap in a different source, change your gateway definition and not your code.. Lately I was testing the new adbc_bridge driver package and was able to plug it in with zero code changes needed. Everything pluggable in the sense that initialization parameters for any db connect() or pyarrow filesystem classes are passed through from the gateway config including the class itself. Here's the common ground approach I took to bridge SQL and FILE sources using DNF. I thought about using substrait, but it isn't end user friendly. https://github.com/apache/arrow/issues/39128 - adds plain text DNF "expressions" to generate pyarrow.compute expressions or datafusion expressions. https://github.com/tobymao/sqlglot/issues/3217 - adds plain text DNF "expressions" to generate sqlglot expressions. Similar expressions like substring, ASCII reverse, etc. could be used to generate computed columns from source streams. Couple of file sourcing items: For file sources you designate a primary "date" or "timestamp" column and then decide whether to date partition your data using YYYYMMDD, YYYYMM or YYYY subdirectories when writing. You could also specify additional partitioning columns to create more subdirectries using any of the partitioning flavors supported by pyarrow (Hive, Directory, Filename). Defining primary key columns on a file dataset would create indexes on the client side output depending on your desired output format: aka pandas index. Reading and Writing supported pandas, pyarrow tables, pyarrow datasets, pyarrow scanner, pyarrow recordbatcheader, polars dataframe, pandas, etc.. as inputs or outputs with automatic detection when passing any of the objects to write_data(). For ADBC it would create a recordbatchreader first out of the source before calling adbc_ingest(). pyarrow.filesystem was pluggable as part of the source definition so hdfs, azure blob, s3, gcs, etc.. were all supported. Automatic casting str to int or int64 to int16 to match your schema definition is automatic. Writes using out of order columns or missing columns is supported. Column upper or lower casing is also optional for both reading and writing for sources like Snowflake which by default upper cases physical table and column names. GitHub link: https://github.com/apache/arrow-adbc/discussions/4877#discussioncomment-18953934 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected]
