5 ms·
Directly running DuckDB queries on data stored in SQLite files
- nattaylor 5y agoCan't wait to see what simonw comes up for using this with datasette!
- samwillis 5y agoThis would be super useful for something I’m working on using datasette, querying across three tables with over 1.5m rows. Going to try the same queries on this tomorrow.
- simonw 5y agoSetting up a plugin hook that lets you use alternative database backends is getting more and more tempting...
- notRobot 5y agoLike PHP PDO? https://www.php.net/manual/en/book.pdo.php https://www.php.net/manual/en/book.pdo.php Or are you talking about something else?
- 41b696ef1113 5y agoExactly what I have been hoping to see as well. Analytical queries are a tad too slow for datasette, so I have had to materialize the more interesting roll-ups.
- wenc 5y agoExciting stuff! I’ve been using duckdb instead of Pandas these days because it’s much faster on larger datasets plus I can write more compact and complex SQL than I can Pandas constructs. The fact that duckdb can query in memory Pandas dataframes faster than Pandas itself is a plus. It’s like having a local performant database engine that can query and join across Parquet, CSV, Pandas and now SQLite. Kinda like a quick efficient local Spark.
- tanin 5y agoI've built an offline desktop for this: https://superintendent.app https://superintendent.app It is a wrapper on Sqlite, but I'm looking to switch to duckdb because its dialect is more comprehensive. Previously, I was using ruby, sometimes python, and sometimes postgresql to process CSV. But it wasn't convenient enough. Most of the times I just tried to use Excel, but formula is so hard to use.
- samwillis 5y agoWould love to see a benchmark comparing SQLite, DuckDB and this SQLite/DuckDB combo with the same queries and data. Is this adapter copying the contents of the SQLite db into DuckDBs own memory layout in order to optimise the queries or is it just “proxying” to the SQLite library?
- 1egg0myegg0 5y agoIt is just scanning the data (in parallel), not copying it into DuckDB!
- eatonphil 5y agoI'm excited to use duckdb in DataStation/dsq but the Go bindings aren't yet super mature and I haven't had time to work on them myself yet.
- mastax 5y agoI really like the DuckDB philosophy, and the maintainers were really responsive and helpful with my docs questions. Didn't end up using it because the compressed database files were much larger than SQLite for my data (weird use case, admittedly), and the .NET libraries were poor. I look forward to trying it out again, though.
- wenc 5y ago> the compressed database files were much larger than SQLite for my data Just curious, what format did you use? If it's Parquet, it does column compression via Snappy by default (which more optimized for speed than size). You can change the compression to ZSTD or GZIP for higher compression ratios. https://duckdb.org/docs/data/parquet https://duckdb.org/docs/data/parquet
- mastax 5y agoI tried all of the available compression formats in parquet, natively in duckdb, and compressing raw duckdb files as a whole. IIRC just GZIPing SQLite files beat them all. The SQLite files were much smaller uncompressed (~220mb vs 800mb) which was probably most of the difference.
- pletnes 5y agoCan you still query a gzipped sqlite file? What’s the sense in gzipping it if not?
- floydnoel 5y agoDo other formats (Snappy?) allow this?
- benglish11 5y agoYou can query snappy parquet files directly.
- 5y ago
- zX41ZdbW 5y agoClickHouse can do this since maybe a year already: https://clickhouse.com/docs/en/engines/database-engines/sqlite/ https://clickhouse.com/docs/en/engines/database-engines/sqli...
- SuaveSteve 5y agoWe have too many database products.