Can connect to in memory duckdb

[ivanonair]

I want to connect to the MINIO on the local host through DuckDB to compare the two Parquet files. I intend to use the in-memory mode to connect to MINIO directly, rather than loading the Parquet files into a DuckDB persistence file first and then using reladiff for comparison.

Currently, this works in Python:

import pandas as pd
import duckdb
from data_diff import connect_to_table ,diff_tables

con = duckdb.connect('local_test.duckdb',config={"allow_unsigned_extensions": "true"})
con.load_extension('httpfs')
con.execute('''
    SET s3_region='local';
    SET s3_url_style='path';
    SET s3_endpoint=' minio endpoint here';
    SET s3_access_key_id=' id here' ;
    SET s3_secret_access_key=' key here ';)
con.execute("""CREATE or replace TABLE fa as (SELECT * FROM read_parquet('s3://*/fa.parquet'))""")
con.execute("""CREATE or replace TABLE fa1 as (SELECT * FROM read_parquet('s3://*/fa.parquet') LIMIT 2)  """)
df = con.execute("""SELECT * FROM fa """).df()
df1 = con.execute("""SELECT * FROM fa1 """).df()
con.close()

source = connect_to_table(db_info= {"driver":"duckdb", "filepath": "local_test.duckdb"},table_name="fa",key_columns=["CO_CD","MAIN_ASSET_NO"])
target = connect_to_table(db_info= {"driver":"duckdb", "filepath": "local_test.duckdb"},table_name="fa2",key_columns=["CO_CD","MAIN_ASSET_NO"])

diff_result = diff_tables(source,target,)
diff_df = pd.DataFrame(diff_result)

But if I change to

source = connect_to_table(db_info= {"duckdb:///:memory:"},table_name="fa",key_columns=["CO_CD","MAIN_ASSET_NO"])

I got error as below:
IOException: IO Error: Cannot open file "/:memory:": Permission denied

I'm considering whether we can first create an in-memory DuckDB connection and directly pass the connection to Reladiff.

Thanks!!

[erezsh]

Did you try using duckdb://:memory: instead?

[ivanonair]

@erezsh oh thanks!
Using duckdb://:memory: can indeed connect to an in-memory DuckDB,

but there is no data, and it's uncertain whether each connection is isolated from one another. so even I load data just before, I can't reach the data.

RuntimeError: DuckDB: Table 'fa' does not exist, or has no columns

If you have tried it, could you share the implementation?

[erezsh]

Yes, the connection is separate.

One way to approach this would be to first connect with reladiff, and then use the connection it made to populate the data, instead of connecting directly to duckdb.

If you need several connections, you can use the shared=True option to share connections.

But this is definitely a bit of a roundabout way. So overall, I'm in favor of adding a way to receive raw connections.

[ivanonair]

I saw this thread let us connect to same in memory db instance by using customized name

duckdb connecting to the same in-memory instance

and I try to connect to same in memory instance through reladiiff that's OK

con = duckdb.connect(database=':memory:compare')
con.load_extension('httpfs')
con.execute('''
    SET s3_region='local';
    SET s3_url_style='path';
    SET s3_endpoint=' minio endpoint here';
    SET s3_access_key_id=' id here' ;
    SET s3_secret_access_key=' key here ';)
con.execute("""CREATE or replace TABLE fa as (SELECT * FROM read_parquet('s3://*/fa.parquet'))""")

Then I can connect to same in memory database through
source = connect_to_table(db_info= {"driver":"duckdb", "filepath": ":memory:compare"},table_name="fa",key_columns=["CO_CD","MAIN_ASSET_NO"])

Thank you so much @erezsh !!!

But for now though this method we can't pass duckdb config into connection
for example
con = duckdb.connect(database=':memory:compare',config={"allow_unsigned_extensions": "true"})

source = connect_to_table(db_info= {"driver":"duckdb", "filepath": ":memory:compare", "allow_unsigned_extensions": "true"},table_name="fa",key_columns=["CO_CD","MAIN_ASSET_NO"])

ConnectionException: Connection Error: Can't open a connection to same database file with a different configuration than existing connections

If you could assist in modifying to accept config paramete and raw connections, we would be extremely grateful. This would allow us seamless to use DuckDB somewhat like Trino, connecting to MinIO, Delta Lake, Iceberg, etc.

[erezsh]

Can you open a PR for it in https://github.com/erezsh/sqeleton ?

It should be very easy to do.

You'll receive the argument here: https://github.com/erezsh/sqeleton/blob/master/sqeleton/databases/duckdb.py#L148

And you need to provide it here: https://github.com/erezsh/sqeleton/blob/master/sqeleton/databases/duckdb.py#L167

I don't use duckdb much, so you'll probably do a better job.

[ivanonair]

Hi @erezsh , I tried to make modificationsto accept "Raw Connection", but encountered problems. I need your assistance.

Modify to accept Raw Connection: I added a BaseConnection class that can register various connection types.

import duckdb
import trino.dbapi
class BaseConnection(ABC):
    @abstractmethod

BaseConnection.register(duckdb.DuckDBPyConnection)
BaseConnection.register(trino.dbapi.Connection)

And I add BaseConnection for validated type on call method

def __call__(self, db_conf: Union[str, dict, BaseConnection], thread_count: Optional[int] = 1, shared: bool = True) -> Database:def __call__(self, db_conf: Union[str, dict, BaseConnection], thread_count: Optional[int] = 1, shared: bool = True) -> Database:
...
if isinstance(db_conf, BaseDBConnection):
            conn = db_conf
elif isinstance(db_conf, str):
            conn = self.connect_to_uri(db_conf, thread_count)
elif isinstance(db_conf, dict):
            conn = self.connect_with_dict(db_conf, thread_count)
else:
            raise TypeError(f"db configuration must be a URI string or a dictionary. Instead got '{db_conf}'.")

However, because you have implemented many methods under sqeleton.Database, I encountered the following error:
AttributeError: 'duckdb.duckdb.DuckDBPyConnection' object has no attribute 'parse_table_name'
Do you have any idea how to accept raw connection?

2. I tried to modify duckdb.py to accept external arguments, but since the original arguments in duckdb are passed through a dict, for example:

def create_connection(self):
        ddb = import_duckdb()
        try:
            return ddb.connect(self._args["filepath"], config=self._args["config"] )

reference duckdb raw connect example:

import duckdb
con = duckdb.connect(database=':memory:compare', config={"allow_unsigned_extensions": "true"})

modified here

After making this modification, I encounter an issue where the dict is unhashable in this function.

connect_to_table(db_info= {"driver":"duckdb", "filepath": ":memory:compare","config":{"allow_unsigned_extensions": "true"}},table_name="fa",key_columns=["CO_CD","MAIN_ASSET_NO"])

TypeError: unhashable type: 'dict'
Induced by sqeleton.utils.py

So I just modified

            config = {key: value for key, value in self._args.items() if key not in ["driver", "filepath"]}
            return ddb.connect(self._args["filepath"],config=config)

and it's OK, but it should note to user how to add config for duckdb