Hi, we're exploring using the datacontract-cli to enforce contracts on MySQL tables. In trying to test some custom SQL checks with the tool, I ran into issues with our larger tables. It appears that when running the checks, the entire MySQL table is materialized in DuckDB before the query is executed. For large tables, this means a query that would run in milliseconds natively on MySQL takes an extremely long time when run through the datacontracts CLI. One example took 15 minutes when running a MAX aggregation on a column, compared to <1 second when run natively on MySQL.
From the changelog, the decision to use the DuckDB extension was made to avoid including dependencies with C extensions. But loading entire tables in DuckDB just to run a simple query doesn't seem like an efficient solution. Would a PR to run queries directly on MySQL using the mysql_query function rather than materializing the entire table be welcome? I don't have much DuckDB experience so I'm not sure if there are factors I'm not considering in this design.
Hi, we're exploring using the
datacontract-clito enforce contracts on MySQL tables. In trying to test some custom SQL checks with the tool, I ran into issues with our larger tables. It appears that when running the checks, the entire MySQL table is materialized in DuckDB before the query is executed. For large tables, this means a query that would run in milliseconds natively on MySQL takes an extremely long time when run through the datacontracts CLI. One example took 15 minutes when running aMAXaggregation on a column, compared to <1 second when run natively on MySQL.From the changelog, the decision to use the DuckDB extension was made to avoid including dependencies with C extensions. But loading entire tables in DuckDB just to run a simple query doesn't seem like an efficient solution. Would a PR to run queries directly on MySQL using the
mysql_queryfunction rather than materializing the entire table be welcome? I don't have much DuckDB experience so I'm not sure if there are factors I'm not considering in this design.