9 ms·
DuckDB Doesn't Need Data to Be a Database
- jhoechtl 2y agoWaiting for DDb to be able to read delta tables natively.
- NortySpock 2y agohttps://github.com/duckdb/duckdb_delta https://github.com/duckdb/duckdb_delta Extension available (read only, apparently)
- noone_important 2y agoI tried to use the extension, but unfortunately i couldn't resolve my problems with it. I always run into errors when i try to execute queries on delta tables.
- aleatorisch 2y agoI'm curious what errors you were running into? Mind posting an issue in the repo, or here? Thanks!
- noone_important 2y agoSorry for the late answer. I get the following errors if I try to execute queries on delta tables (accessing them with spark works fine) InvalidInputException: Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result Error: IO Error: Hit DeltaKernel FFI error (from: kernel_scan_data_next in DeltaSnapshot GetFile): Hit error: 2 (ArrowError) with message (Invalid argument error: Incorrect datatype for StructArray field "partitionValues", expected Map(Field { name: "entries", data_type: Struct([Field { name: "keys", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "values", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, false) got Map(Field { name: "key_value", data_type: Struct([Field { name: "key", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "value", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, false))
- aleatorisch 2y agoThanks, appreciate the reply. I believe this should have been fixed by https://github.com/delta-incubator/delta-kernel-rs/pull/210 https://github.com/delta-incubator/delta-kernel-rs/pull/210. I'm guessing you were reading from a table with a checkpoint :) DuckDB unfortunately hasn't pulled in the latest changes just yet, but as soon as they update to include that fix, I expect your queries will work. If the table is public, I'd be happy to check that kernel now supports reading it.
- noone_important 2y agoMy problem is probably already covered by: https://github.com/duckdb/duckdb_delta/issues/18 https://github.com/duckdb/duckdb_delta/issues/18
- mbreese 2y agoBack in the day (early 2000’s), I worked with a DB2 instance that had similar functionality. At the time, they called this feature federated databases. If you had the appropriate wrapper, you could use any data source in a query. Even output from other programs. At the time I used it for including dynamic DNA sequence alignments in queries. IIRC, SQLite can do similar things with virtual tables (with a more limited set of data file types). I always liked this way of working, but I also wonder why it never really took off. Data discovery can be an issue, and I can see the lack of indexing as being a problem. I guess that’s a long winded way to ask: as interesting as this is, what are the use cases where one would really want (or need) to use it?
- solidsnack9000 2y agoA related functionality is "SQL/MED", a SQL specification for federated databases that has some kind of relationship to medical data historically (I believe one of the use cases is data at one site that another site is allowed to query, in a limited way, data hosted at another site that may not be moved from it).
- sixdimensional 2y agoThe MED stands for management of external data, nothing to do with medical data. It’s actually a chapter in the ISO/ANSI SQL specification.
- solidsnack9000 2y agoMy understanding from reading the literature around SQL/MED and related technologies is that they were, indeed, inspired by data management problems in medicine. The use cases are like those I outlined -- we can't move the data from this physical location, but we want to allow other sites to query it. As a result, a specification was added, as part of ISO/ANSI SQL specification process. The MED term is an acronym and not a shortening of "medical".
- sixdimensional 2y ago
- countvonbalzac 2y agoDoes DuckDB cache the S3 downloads? Otherwise it could get pretty expensive, no?
- davesque 2y agoIf the parquet file includes any row group stats, then I imagine DuckDB might be able to use those to avoid scanning the entire file. It's definitely possible to request specific sections of a blob stored in S3. But I'm not familiar enough with DuckDB to know whether or not it does this.
- akdor1154 2y agoIt does do that. I can't answer OP's qn about caching though.
- chrisjc 2y agoDuckDB can do some pushdowns to certain file formats like parquet, but every release seems to be getting better and better at doing it. Parquet pushdowns combined with Hive structuring is a pretty good combination. There are some HTTP and Metadata caching options in DuckDB, but I haven't really figured out how and when they really making a difference.
- ayhanfuat 2y agoI asked this some time ago on their Discord in relation to AWS lambda and the Python client and the answer was that you need to handle caching on your own but it is easy to do with fsspec. I haven’t tried it yet though.
- wenc 2y agoDo you have any details on this? Duckdb over vanilla S3 has latency issues because S3 is optimized for bulk transfers, not random reads. The new AWS S3 Express Zone supports low-latency but there's a cost. Caching Parquet reads from vanilla S3 sounds like a good intermediate solution. Most of the time, Parquet files are Hive-partitioned, so it would only entail caching several smaller Parquet files on-demand and not the entire dataset.
- blyry 2y agoThis is a great feature. We've been able to significantly extend the scope and usefulness of our on-prem SQL Cluster for analytics and reporting with PolyBase by building new transactional systems with cheaper postgres, doing ETLs of third-party data to delta tables in azure storage, and then federating access to them with PolyBase so that nobody in the business has to change how they actually query the data. I'm sure in another decade we'll be fully migrated to some cloud platform but for now, federating the queries is a huge win.
- brutuscat 2y agoDoes it work with some format that supports indexes like Apache carbon data rather than parquet? https://github.com/apache/carbondata https://github.com/apache/carbondata
- Jgrubb 2y agoI admit I'm not crazy deep in this space but I'm _pretty_ into this space and I've never heard of Carbon. Parquet is kind of winning the OSS columnar format race right now.
- wenc 2y agoParquet the most popular columnar format. (owing to support in Spark and various other big data tools, as well as local tools like pandas, polars and duckdb) It's technically not the very best format (ORC has some advantages), but it's so ubiquitous and good enough -- still far better than than CSV or the next best competing format. I have not heard of Carbon -- it sounds like an interesting niche format, hopefully it's gaining ground. It's the VHS, not the betamax.
- stdbrouw 2y ago"Good enough" makes it sound like barely a step up from a CSV file. I'd say its support for various encodings [1] including a great default (dictionary + run length encoding on the indices) and compression algorithms that can be set for each individual column, columnar access, partitioning, a parallelized reader out of the box, in-memory filtering and other ops concurrently with loading in the data (thanks to Arrow) etc. etc. are all really wonderful when working with medium-sized data. [1] https://parquet.apache.org/docs/file-format/data-pages/encodings/ https://parquet.apache.org/docs/file-format/data-pages/encod...
- wenc 2y agoAgreed. On a scale of 10 in terms of current technology, CSV is a 1 while Parquet is 7. ORC is maybe 7.2. But parquet is far more ubiquitous than ORC (I’ve never seen ORC in prod but I also have limited sample sizes) I’m sure there are more advanced formats.
- dangoodmanUT 2y agodoes view creation still list all files? ime even if not queried, the view would do a lot of s3 calls
- rcaught 2y agohttps://duckdb.org/docs/sql/statements/create_view https://duckdb.org/docs/sql/statements/create_view - the View is not physically materialized, but the query is run every time the view is referenced in a query.
- clumsysmurf 2y agoDuckDB has Swift bindings, but unfortunately, afaik, nothing official for Android. If anyone has gotten it working on Android I'd love to hear about it.
- halfcat 2y agoConceptually is this sort of a database-agnostic SQL view? Seems like these could be stacked to simplify (or complicate?) an entire ETL pipeline. I haven’t decided where I land on this. In some ways, stacking SQL views looks like it simplifies a bunch of ETL jobs, but I also fear a few things: * It either breaks catastrophically when there’s a change in the source data * Fails silently and just passes the incorrect data along * More challenging to debug than an ETL pipeline where we have a clear point of error, can see the input and output of each stage, etc * Source control of SQL views seems less great than code. Often when we have too many views, you can’t update one without dropping all of the dependencies and recreating them all But I also wonder if I feel this way because I know programming better than SQL
- banku_brougham 2y agoNo you are right, views have an use case but they get abused as ersatz orchestration, creating a nested structure that multipkies the amount of needed compute. edit: now I read your question more carefully. I think the s3 data is meant to be managed by other orchestration. This is a quick easy way to share a data source with an analyst, PM or end consumer. I do not expect poster is advocating this as any intermediate stage in a data pipeline.
- remram 2y agoWhy wouldn't you mail your co-worker the SQL text of this query? What is the advantage of the binary database file with no data?
- wild_egg 2y agoYou then also have to mail them every update to the SQL and hope they're handling versioning well With this approach, next time they attach the DB, they automatically see the latest version
- remram 2y agoOh I missed the part where they put that DB file on S3 too and sent a link. I thought they were attaching the DB to the email. Still, I would more readily send people a link to a Gist or playground than a binary DB on S3.
- ramraj07 2y agoOr if all your data is in a place like snowflake, you just create a view and send them the name..
- banku_brougham 2y agowell yeah when you pay the big bucks you can expect all the bells and whistles.
- MR4D 2y agoThe future is here, it's just not evenly distributed. Wait a decade (or less) and this will be in open source. Heck, it might already be.
- wild_egg 2y agoNot a whole lot of operational difference between sending them a snowflake view name or sending an S3 URL but there's probably two orders of magnitude cost difference
- zie 2y agoFor things like this I just export to a sqlite DB file and send that along. This is interesting for AWS/S3 datastores though.
- tobilg 2y agoYou can do the exact same thing with DuckDB as well...
- zie 2y agoI didn't know that(I don't use DuckDB), but SQLite is very standard and anyone can play with it. Query/UX interfaces exist for almost every imaginable platform even. So I'm still a SQLite recommender for stuff like this.
- chrisjc 2y agoDuckDB is really for OLAP/Columnar (analytical workloads) whereas SQLite is more for OLTP/Row-based (transactional). When you start running aggregations/windows over large amounts of data, you'll soon see the difference in performance.
- zie 2y agoSure, but the OP is using DuckDB as a way to export some smaller amount of data for someone. That's a great use-case for SQLite, from whatever your real DB is, which for the OP is also DuckDB.
- deleted 2y ago[deleted]
- jewelry 2y agoThis feels just like Presto/Trino to be honest
- willvarfar 2y agoThey are similar in that you have some data stored somewhere, and you can query it as though it was data stored inside a 'conventional' database that has it's own storage that it manages itself. But whereas Presto/Trino/Bigquery/etc are server-based where queries execute on a cluster of compute nodes, duckdb is something you run locally, in-process.
- youngbum 2y agoThis is the exact reason we applied duckdb and duckdb-wasm into our service. Our team is currently building a form builder SaaS. Most forms have responses under 1,000, but some of them would have more than 50,000 responses. So, when user tries to explore through all responses in our “response sheet” feature, usually they could be loaded via infinite scrolling (load as they scroll). This uses up to 100MB of network in total if they had to get object arrays of 50,000 rows of data with 50 columns. That was where duckdb kicked in : just store the responses into S3 as parquet file(in our case Cloudflare R2). Then, load the whole file into duckdb-wasm into client. So when you scroll through sheet, instead of getting rows from server, you query rows from local db. This made our sheet feature very efficient and consistent in terms of their speed and memory usage. If network speed and memory is your bottle neck when loading “medium” data into your client, you definitely should give it a try. PS. If you have any questions, feel free to ask! PS. Our service is called Walla, check it out at https://home.walla.my/en https://home.walla.my/en
- hamandcheese 2y ago> you query rows from local db. But the data is still remote (in object storage) right? If I understand correctly, this works then the first solution because parquet is a much more efficient format to query?
- foota 2y agoIt's probably part of it, but also overhead from small requests and latency from round trips.
- youngbum 2y agoGreat point. The advantages of loading “parquet” in “client side” are that 1) you only have to load data once from server and 2) the parquet files are surprisingly well zipped. 1) If you load once from server, no more small network requests while you are scrolling a table. Moreover, you could use the same duckdb table to visualize data or show raw data. 2) Sending whole data as a parquet file is faster through network than receiving data as json in response.
- wodenokoto 2y agoIs the `weird_rides.db` file python or some sort of duckdb script? I'm quite intrigued about DuckDBs ability to read parquet files off of buckets. How good is at at simply ignoring files (e.g, filtering based on info in datafiles paths)?
- rcaught 2y agoI've successfully used it's glob support on S3.
- mvc 2y agoIf the objects in S3 are named according to hive conventions, it will do what you would hope and not read any files with no data to match the query. It will even use range requests in S3 to avoid fetching the entire blob. See here for the details https://duckdb.org/2021/06/25/querying-parquet.html#automatic-filter--projection-pushdown https://duckdb.org/2021/06/25/querying-parquet.html#automati...
- mr-karan 2y agoCurious, why not use httpfs extension and directly read multiple parquet files as demonstrated here[1]? SELECT * FROM read_parquet([ 's3://bucket/file1.parquet', 's3://bucket/file2.parquet' ]); [1]: https://duckdb.org/docs/extensions/httpfs/s3api#reading https://duckdb.org/docs/extensions/httpfs/s3api#reading
- rcaught 2y agoThe article shows dynamically fetching a database, with database level views, that dynamically fetch data. You're just showing a query that dynamically fetches data.
- valval 2y agoUp until this point in my career, I've always wanted to incorporate some of these new age SQL tools into my workflows, but I just never seem to realize the utility. What's going on here that wouldn't warrant building a processing pipeline that places the data in a more permanent data warehouse and create all the necessary views there?
- rcaught 2y agoThis is not showing what you would always do, it's showing something novel that you could do.
- filleokus 2y agoHow stable is the DuckDB file format nowadays? One annoying thing I've experienced (like a year ago) was when I upgraded the rust bindings that produced my DuckDB files, which then couldn't be opened with DBeaver because they hadn't released a new version yet. I guess you could run into something similar with this solution?
- pletnes 2y agoI had the same issue. I solved it real easy by keeping a python/sql script to create the db, which was VIEWs over parquet files, similar to the article although with local files. The sql syntax is stable so this works great. Creating a db as a few VIEWs is also very fast.
- uwemaurer 2y agoThis got a lot better with duckdb 0.10.0 https://duckdb.org/2024/02/13/announcing-duckdb-0100#backward-compatibility https://duckdb.org/2024/02/13/announcing-duckdb-0100#backwar...
- wodenokoto 2y agoNo, because parquet is quite stable.
- tobilg 2y agoYou can try DuckDB in your browser via DuckDB WASM with https://sql-workbench.com https://sql-workbench.com
- philipwhiuk 2y ago> That’s right – most databases no longer have any data in them. That's a pretty outlandish statement.
- rcaught 2y agoIn the full context of their statement, many modern databases separate the data storage component. RDS Aurora, etc.
- deleted 2y ago[deleted]
- Night_Thastus 2y agoI swear there's a skit this reminds me of, but I cannot for the life of me remember what it is.
- hipjiveguy 2y agoit's turtles... all the way down?
- RyanHamilton 2y agoI recently took a big gamble on DuckDB. I've added it to the core of qStudio a free SQL editor. Now within the editor you can save table results from any database to the local duckdb instance or you can even perform joins amongst any server and I do mean almost any server. Out of the box DuckDB supports mysql/postgresql but I've added support for 20+ other database though it probably doesn't support all their types. If you want to get started with DuckDB qStudio is a good IDE: https://www.timestored.com/qstudio/duckdb-file-viewer#qduckdb https://www.timestored.com/qstudio/duckdb-file-viewer#qduckd...
- netcraft 2y agoqStudio looks very cool and im right in your target demo - and I hate to complain about free but just a bit of feedback: jar on mac is a lot of friction to be able to try it. I just spent about 5 minutes downloading and trying to get it to work - mac says it cant open because its from an unidentified developer, I went into security to get it to open anyway, it gave me another scary warning and then it still couldn't launch because I dont have a jvm installed somehow? I'll probably try again later because it really does look cool and as much as I absolutely love my intellij/datagrip I'm always looking for alternatives and have thought several times about building a tool with these automatic analysis tools built in.
- RyanHamilton 2y agoDamn that's dissapointing as I actually just recorded a mac demo this week. I got the false impression all macs have java but I guess it was just the one I had. I'm happy to try creating an installer if you are willing to test it. If so please email me ryan A_T timestored.com.
- irq-1 2y agoTake a look at https://www.hydraulic.dev/ https://www.hydraulic.dev/ >Build self-updating desktop app packages in minutes. Deploy to every OS from any OS. Platform native formats, fully signed and notarized. Electron, Flutter, JVM and native. And free: > Conveyor is free for open source projects.
- zokier 2y agoNot saying that this technique doesn't have any use, but the example scenario is pretty weak. Why not just send s3 link to the analysts s3://robotaxi-inc/daily-ride-data/*.parquet Or just the query: SELECT pickup_at, dropoff_at, trip_distance, total_amount FROM 's3://robotaxi-inc/daily-ride-data/*.parquet' WHERE fare_amount > 100 AND trip_distance < 10 does the intermediate database and view really provide much value? Thinking more, I feel this boils down to ownership. Does it make sense for you to own this abstraction layer? Or does it make sense to shift the ownership towards the receiving end, and just provide the raw data. To me maintaining this sort of views to data sounds more like responsibility of data analysts that random devs. They are the experts in wrangling data afterall. But of course there is no single right or wrong answer either
- astronautas 2y agoThis
- slachterman 2y agoHow does authentication work in this scheme? The view uses separate S3 credentials, which are configured per external user consuming the database?
- chrisjc 2y agoVia the AWS extension https://duckdb.org/docs/guides/network_cloud_storage/s3_import https://duckdb.org/docs/guides/network_cloud_storage/s3_impo... https://duckdb.org/docs/extensions/aws https://duckdb.org/docs/extensions/aws
- robertlagrant 2y ago> Changes in format, different partitioning strategies, schema changes – through all of it the receiver’s view remains the same. I don't understand this - if I start saving those files in a different format how will it continue to work? Why would the view remain the same if I just rename columns, even?
- snthpy 2y agoI think what was implied was that the user just references the view so as long as you update the view in concert with the data format change then the user is none the wiser about the change.
- robertlagrant 2y agoOh - I see. Because the .db file is centralised, you can connect to it from somewhere and do stuff to it. I think I missed that the DB is like that. Is it like dumping a SQLite database somewhere with a view in it, and connecting over that as well? Or does DuckDB have more magic to transfer less data in the query work?
- snthpy 2y agoYeah, I guess you could equivalently put a SQLite database with a view or virtual table in S3 which would give you the same level of indirection and API abstraction provided by this mechanism. Where DuckDB will have an advantage is in the processing speed for OLAP type queries. I don't know what the current state of SQLite Virtual Tables for parquet files on S3 is, but DuckDB has a number of optimisations for it like only reading the required columns and row groups through range queries. SQLite has a row oriented processing model so I suspect that it wouldn't do that in the same way unless there is a specific vtable extension for it. You can get a comparable benefit for data in a sqlite db itself with the project from the following blogpost but that wouldn't apply to collections of parquet files: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database...
- unnouinceput 2y agoQuote: "How do you share that dataset with an analyst?" ..and blah blah about the sharing from a developer perspective. In reality the analyst is some higher up that only knows how to import/view CSV in Excel so that's exactly what will ask for ("hey zzz can you send me those daily parcels in a .csv file? thank you")
- rcaught 2y agoCOPY (SELECT * FROM view_name) TO 'output.csv' (HEADER, DELIMITER ',');
- dforsber 2y agoNobody says the obvious: Using DuckDB as the Catalog. You can easily do snapshots, and thus also time travel.
- vgt 2y agoShameless plug - MotherDuck[0] is a serverless managed DuckDB data warehouse with some interesting properties: - Managed storage with zero-copy clone (and upcoming time travel) - Secure Sharing - Hybrid Mode that allows folks to combine client (WASM, UI, CLI, etc) with cloud data - Approved and supported ecosystem of third party vendors - Since we're running DuckDB in production, we're working very closely with the DuckDB team to improve both our service and open source DDB in terms of reliability, semantics, and capabilities [0]https://motherduck.com/ https://motherduck.com/ (co-founder at MotherDuck)
- dforsber 2y agoShameless plug - Data Tap[1] is a custom made Lambda function with embedded DuckDB and AWS managed ingestion URL where you can HTTP post your data as much as you like. It will buffer your data and then use DuckDB SQL clause to land the datas Parquet on S3. - Deploy to your own account (BYOC) - "S3 first": Partitioned and compressed Parquet on S3 - Secure sharing of write access to the Data Tap URL - 50x more cost efficient than e.g. "Burnhose" whilst also having unmatched scalability of Lambda (1000 instances in 10s). [1] https://www.taps.boilingdata.com/ https://www.taps.boilingdata.com/ (founder)
- astronautas 2y agoI am confused... It's a cool example, but really an antipattern. Nowadays everyone gets analysts want access to raw data, since they know which aggregations they need best, whereas data engineers stay away from pre-aggregating and focus on building self-service data access tooling. Win-win this way. How about building a duckdb accessible catalog on top of s3? Like instead of read_parquet, you would select from tables, which themselves would be mapped to s3 paths aka external tables.
- rcaught 2y agohttps://duckdb.org/docs/extensions/iceberg.html https://duckdb.org/docs/extensions/iceberg.html
- astronautas 2y agoNot a catalog though, still need to input s3 path...
- mukiblejlok 2y agoX n it is my l