4 ms·
Show HN: GizmoSQL – Run DuckDB as a Server with Arrow Flight SQL
Hi,
I'm Philip Moore - the founder of GizmoData, and creator of GizmoSQL - an Apache Arrow Flight SQL Server - with DuckDB (or SQLite) back-end execution engines.
GizmoSQL is a composable SQL server with Arrow Flight SQL, DuckDB, and SQLite - with the intention of making it easy to run DuckDB (or SQLite) as a server - usable by multiple people from a client (remote) computer. It also adds security (authentication) and encryption of traffic with TLS.
To run GizmoSQL - see the steps in the README.md - where you can see how easy it is to run the server as well as how to connect via ADBC and JDBC from a remote client - such as DBeaver, Python, etc. The easiest way to run GizmoSQL is via Docker - but there are downloads for Linux and macOS for both x86-64 and arm64 platforms (download links in the README).
Why?:
As you may know, DuckDB and SQLite are embedded systems - they don't enable client connectivity, and they aren't really designed for concurrency.
I've built GizmoSQL to work around that - because I believe the DuckDB engine is very powerful, and I feel like a lot of customers overpay and run distributed compute (i.e. Spark) when they don't really need to. Making it easy to have remote connectivity to DuckDB can make it easier to migrate SQL workloads from Spark or other expensive commercial platforms to this engine - with a much simpler architecture/infrastructure.
It is my intention to make GizmoSQL a commercial product - licensed for production use by organizations, but free for developers to code with - evaluate, and test.
A little bit of backstory:
* I built the initial version of this while working for a former employer - it wasn't their core focus, so they open-sourced that early version. After I left there, I forked the product and have improved it substantially - to support concurrency of both reads and writes, improving security, as well as keeping it up to date with the latest versions of Apache Arrow and DuckDB.
* This project evolved from a prototype created by the brilliant Tom Drabas.
* It feels a little weird trying to make a commercial product based upon DuckDB, but MotherDuck started it :P - and I've contributed (albeit very little) to the DuckDB and Apache Arrow projects in the form of a couple of PRs.
I'm really excited about this project - I have run benchmarks of this product against commercial platforms such as Snowflake and Databricks SQL - and it holds its own running the 22-query TPC-H SF1TB benchmark, especially on cost. See the graph at: https://gizmodata.com/gizmosql https://gizmodata.com/gizmosql
Getting started:
Github README: https://github.com/gizmodata/gizmosql-public/blob/main/README.md https://github.com/gizmodata/gizmosql-public/blob/main/READM...
DockerHub: https://hub.docker.com/r/gizmodata/gizmosql https://hub.docker.com/r/gizmodata/gizmosql
GizmoSQL homepage: https://gizmodata.com/gizmosql https://gizmodata.com/gizmosql
Phil's Github profile: https://github.com/prmoore77 https://github.com/prmoore77
Thanks for your time and feedback in advance.
- mousematrix 2y agoLooks v. cool, how does gizmo handle concurrency for multiple writes? are there any limits that you have observed?
- deleted 2y ago[deleted]
- philbe77 2y agoHey mousematrix, I've tested with 3 concurrent sessions all writing to the same table - and with "BEGIN TRANSACTION" for each. I opened a TPC-H SF1GB database with GizmoSQL, started 3 concurrent client sessions, and had each session run: BEGIN TRANSACTION; INSERT INTO lineitem SELECT * FROM lineitem; COMMIT; None of the 3 sessions were blocked - and all were able to insert into the table, and read the results of their inserts before committing. It was very cool to see also that writes do NOT block reads from other sessions - the other sessions just could not see uncommitted data. After committing in one session, the other sessions could see the data that was inserted. I haven't stress tested the system to get limits, but I thought this little test would prove useful information for your question. I'll work to do some benchmarking on concurrent inserts, etc. Updates to the same data by multiple sessions behave a little differently - see details from page: https://duckdb.org/docs/stable/connect/concurrency.html https://duckdb.org/docs/stable/connect/concurrency.html Thanks for your question!
- memhole 2y agoNice work! I guess my only thought would be if you’re trying to turn this into a product, is this basically a client server rdbms. What advantages does using embedded dbs in this way provide compared to something like Postgres or MySQL? Or the growing popularity of duckdb on s3?
- philbe77 2y agoThanks for the kind words! I think the main advantages this has over other RDBMS's such as Postgres and MySQL is performance - due to the columnar / vector processing capabilities of DuckDB. I've run this in AWS with a i8g.16xlarge, and on Azure with a Standard_E64pds_v6 - and get amazing performance - due to the use of NVMe storage, lots of CPU (64) and memory (512GB) - for less than $4/hr in cloud VM cost. This solution lets users use the large resources available on larger cloud VMs as an enterprise-grade server - for use with data notebooks, analytics dashboards, and more. You can get performance that meets and exceeds many distributed systems on a much simpler architecture - reading from parquet datasets, assuming you've copied (cached) them on the local NVMe SSD storage. Of course - this has disadvantages - such as keeping the local copy in sync with the cloud storage (S3) - but it can be well worth it if you have a mostly read-only dataset (write-once, read-many). There is a small startup time penalty for hydrating the local storage - but I've seen throughput as high as 4GB/s for this initial copy... I hope this helps. Thanks again for the question!