Friendlier SQL with DuckDB(duckdb.org) |
Friendlier SQL with DuckDB(duckdb.org) |
Normal insert, hard to read:
INSERT INTO table1 ( field1, field2, field3, ... ) VALUES ('value1', 'value2', 'value3', ... );
vs
Easier to read:
INSERT INTO table1 SET field1='value1', field2='value2', field3='value3', ...
SELECT age, sum(civility) as total_civility
FROM star_wars_universe
ORDER BY ALL
-- ORDER BY age, total_civility
edit: (removed edit, I blew it, sorry)
This is very cool though. There are lot of features that would make my life easier. Group By All is noice.
Column selection:
When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports:
- APPLY: apply a function to a set of columns
- COLUMN: select columns by matching a regular expression (!)
Allow trailing commas:
I can't count how many times I've run into a problem with a trailing comma. There's a whole convention developed to overcome this: the prefix comma convention where you'd write:
SELECT
first_column
,second_column
,third_column
Yep! That would be my #1 request for SQL. Seems ridiculous that it's not supported already.
SELECT
first_column,
second_column,
third_column,
null select 1 as x, x + 2 as y, y/x as z;But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta
JSON is the other one where it annoys me, but luckily I rarely hand-write any JSON anymore (and there are semi-solutions for this like json5).
In code I always add trailing commas to anything comma-separated. It makes editing simpler (you can shuffle lines without thinking about commas). In a diff or blame it doesn't show adding a comma as a change.
SQL is the one spot where this doesn't work, and it's a constant foot-gun as I often don't remember until I run and get a syntax error.
VSCode uses it for configuration, but when I wanted to use it in Python (to add context to source-controlled Elasticsearch schemas) there were only a couple old barely-maintained libraries for parsing.
>> Allowing for trailing commas should get included in the SQL spec.
So there is no "SQL spec" per se, there's an ANSI specification with decades of convention and provider-specific customizations piled on top. This support for trailing commas is the best you're going to get.
Not just SQL, trailing commas are stupidly useful and convenient, so as far as I'm concerned every language should have them. To be fair, a decent amount of them have implemented them (I was pleasantly surprised by GCC C), but there are still notable holdouts (JSON!).
> Unique and primary key indexes are rebuilt upon startup, while user-defined indexes are discarded.
The second part with just discarding previously defined indexes is super surprising.
https://duckdb.org/docs/sql/indexes
This was an instant showstopper for me or I assume most people whose databases grow to a bigger size at which point an OLAP DB becomes interesting in the first place.
Also the numerous issues in Github regarding crashes make me hesitant.
But I really like the core idea of DuckDB being a very simple codebase with no dependencies and still providing very good performance. I guess I just would like to see more SQLite-esque stability/robustness in the future and I'll surely revisit it at some point.
I found https://duckdb.org/why_duckdb but I'm sure someone here can share some real world lessons learned?
Extremely useful, is there a reason why this is something not implemented in SQL in the first place? I often find myself writing very long queries just to select basically all columns except for two or three of them.
Even if that turns out to be a constant source of trouble worth not having, then why SQL can’t provide columnsets at least, so that queries could include, group or join on these predefined sets of columns instead of repeating tens of columns and/or expressions and/or aggregations many times across a single query. You had employees.bio_set=(name, dob), now you add `edu` to it and it just works everywhere, because you think in sets rather than in specific columns. Even group by bio_set works. Heck, I bet most of ORMs partially exist only to generate SQL, because it’s sometimes unbearable as is.
It supports:
- functions,
- using an alias in same `select` that defined it,
- trailing commas,
- date literals, f-strings and other small improvements we found unpleasant with SQL.
https://lang.prql.builders/introduction.html
The best part: it compiles into SQL. It's under development, though we will soon be releasing version 0.2 which would be "you can check it out"-version.
My fear with PRQL is that i'd more easily ask too much of the DB, given how easy it looks to write larger and more complex SQL. Thoughts?
But not always. Some analytical queries may actually need such complexity. Also, during development, you would sometimes pick only first 50 rows before joining and grouping, with intention of not overloading the db. To do this you need a CTE (or nested select), but in PRQL you just add a `take 50` transform to the top.
That would be really neat to have an official one. This articles makes me want to try it even more
Edit: I came across it via the podcast: https://www.dataengineeringpodcast.com/duckdb-in-process-ola...
Latest release notes: https://github.com/duckdb/duckdb/releases/tag/v0.3.3
[1] Error message: Not implemented Error: DataType TIMESTAMPZ not supported yet...
I fully agree that error message needs to improve, however. I will have a look at that.
DuckDB has all but replaced Pandas for my use cases. It’s much faster than Pandas even when working with Pandas data frames. I “import duckdb as db” more than I “import pandas as pd” these days.
The only thing I need now is a parallelized APPLY syntax in DuckDB.
https://fugue-tutorials.readthedocs.io/tutorials/integration...
SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars
On further investigation, It seems that someone had maliciously injected lots of bogus data into the production database. We tried to clean up by truncating tables and dropping columns, but in the end it was easier to just restore from backup prior to 1999.
There still seems to be some residual corruption, most predominantly around mos_eisley and jabbas_palace data, and we had to truncate the end of Return of the Jedi, but not much was lost there.
Obviously there will be a desire to monetize this project, if not for the very simple reason of subsidizing the cost of its development and maintenance. I love everything I hear and see about this project, but it makes me nervous to recommend this internally due to it not only being in such an early stage, but also bc of any unforeseen costs and liabilities that it might introduce in the future.
First off - DuckDB is MIT licensed, so you are welcome to use and enhance it essentially however you please!
DuckDB Labs is a commercial entity that offers commercial support and custom integrations. (https://duckdblabs.com/). If the MIT DuckDB works for what you need, then you are all set no matter what!
However, much of the IP for DuckDB is owned by a foundation, so it is independent of that commercial entity. (https://duckdb.org/foundation/)
Does that help? Happy to answer any other questions!
If you have a specific question, definitely post it here and I will clarify!
Keep the puns, I just think with a bit of adjustment the examples would be easier to understand.
PS those examples were so good! really good writing :)
(Info from memory, may be wrong or a bit mangled)
EDIT: Typo
SELECT city, COUNT(*)
FROM customers
SELECT FIRST(city), COUNT(*)
FROM customers
SELECT city, COUNT(*)
FROM customers
GROUP BY city
We could definitely add a flag to enable this behavior, however.
A questions I have to author, or anyone using: Is there a easy way to transfer whole Postgres DB into DuckDB so I can do some tests with actual client data? I could export each table by hand and reimport it, but that is kind of painful.
DuckDB also has the ability to read Postgres data directly, and there is a Postgres FDW that can read from DuckDB!
It was basically syntactic sugar for a persistence API.
Instead of "select bar from foo" it used a "from foo select bar" type of syntax.
This was rather nice from a code completion perspective.
This took a little while because we use a fairly modern index structure with no literature definition on how to buffer manage it.
BTW Do you have some kind of code/docs one can take a look at regarding the index structure? I'm a part-time data structure nerd :)
Typesense DB (Typesense.org) under the hood also uses ART as the base datastructure for fast full text search queries.
So I assume it would be straightforward to build an text search engine on top of duck DB and utilize ART indices to do even more lovely things.
(disclaimer - on the DuckDB team)
First things first - we really like quite a lot about the SQLite approach. DuckDB is similarly easy to install and is built without dependencies, just like SQLite. It also runs in the same process as your application just like SQLite does. SQLite is excellent as a transactional database - lots of very specific inserts, updates, and deletes (called OLTP workloads). DuckDB can also read directly out of SQLite files as well, so you can mix and match them! (https://github.com/duckdblabs/sqlitescanner)
DuckDB is much faster than SQLite when doing analytical queries (OLAP) like when calculating summaries or trends over time, or joining large tables together. It can use all of your CPU cores for sometimes ~100x speedup over SQLite.
DuckDB also has some enhancements with respect to data transfer in and out of it. It can natively read Pandas, R, and Julia dataframes, and can read parquet files directly also (meaning without inserting first!).
Does that help? Happy to add more details!
How does DuckDB compare in that aspect? Does it have the same kind of guarantees of robustness, incorruptibility and performance (especially reading/writing binary blobs) that SQLite does?
In any case: DuckDB looks great, nice work! Good to have more players in this space!
Basically I'm wondering: if I go all in on DuckDB instead of SQLite would I notice? Do I have to keep anything in mind?
I know, probably difficult to answer without a concrete example of data, schema, queries and so on.
The SQL query features in the article seem really neat. Kudos @ shipping.
the other big thing is better native data types, especially dates. With SQLite if you want to work with timeseries you need to do your own date/time casting.
I need to do the benchmarks to substantiate this but this is my intuition.
1. SQLite has a great GUI and is really really widely supported.
2. DuckDB is properly statically typed with a much wider range of types than SQLite, which is dynamically typed and only just added support for any kind of type checking at all.
For programmers, it’s a tossup.
For most people working in data (databases, data engineering, ML etc) the column vs row thing makes a difference for datasets as small as a few hundred k records.
If you want to do something a bit more complex, you will have a bad time. Hello! With recursive.
Do they work, though? If it’s a mostly stable standard, doesn’t seem like you’d need a frequently updated parser.
https://db.in.tum.de/~leis/papers/ART.pdf
There are some other papers and details about the architecture here: https://duckdb.org/why_duckdb
We actually run that benchmark as a part of our test suite now, so I am certain that there is improvement from that version.
The biggest DuckDB I've used so far was about 400 GB on a machine with ~250 GB of RAM.
There is ongoing work that we are treating as a high priority for handling larger-than-memory intermediate results within a query. But we can handle larger than RAM in many cases already - we sometimes run into issues today if you are joining 2 larger than RAM tables together (depending on the join), or if you are aggregating a larger than RAM table with really high cardinality in one of the columns you are grouping on.
Would you be open to testing out your use case and letting us know how it goes? We always appreciate more test cases!
With DuckDB, bulk inserts are your friend and are actually super fast.
Definitely let us know what you find! Just open up a discussion on Github if you'd like to share what you find out: https://github.com/duckdb/duckdb/discussions
select y-2 as x, 3 as y, y/x as z; select x + 2 as y, 1 as x, y/x as z;
Worst, by far, than a column you didn't expect being added is a column you did expect being removed. Depending on how thorough your integration tests are (and ideally they should be pretty thorough) you could suddenly start getting strange array key access (or object key unfound) errors somewhere on the other side of the codebase.
It appears how this works is that is selects all columns and then EXCLUDES only the column's specified, the reason this doesn't exist in normal SQL is because it is a terrible idea. This is something that will break at many companies with large technical debt if it is ever used.
No, it is not. I mean it is, but not in parts where that could be seen as useful and/or convenient. [A]cyclic graph traversal/etc is one of the basic tests in a modern interview at any CRUD studio. How come it could do $%^& to any part of yours?
Because just implementing the standard stuff nearly did my $^&% nut. Also I know about graphs & posets, and it's potentially a little more complex than it seems. The variables
select x * x as y, 1 as x
select
(select tbl.z from tbl where tbl.y = y) as subq,
x * yy as y,
xx + 1 as x,
subq + yy as zzz
from (
select xx, yy
from ... )
I've implemented the ability to reference select-list aliases before; it's not that hard to do if implemented basically like a macro expansion. The main problem is user confusion due to ambiguous references, e.g.
select 2 as x, x as `which x?`
from (select 1 as x) t;
IMO only allowing references to previous select list items is a perfectly reasonable restriction; loosening it isn't worth the implementation headache or user confusion. Though we did allow using aliases in the WHERE clause.
That’s no different than the first snippet, if you aren’t parsing it with regexps, of course. The resulting AST identifiers would simply refer to not only column names, but also to other defined expressions. This is the case for both snippets. It’s either cyclic or not, and when not, it is easy to substitute/cse/etc as usual. The complexity of these expressions is irrelevant.
I can do that I just don't fancy it, and more to the point nobody is giving me an example of where it would be particularly helpful. So if anyone can, I'm interested.
(also, consider human factors; although an acyclic definition could be extracted from an unordered expression set, a consistent left to right (in the western world anyway, matching textual layout) with dependencies being introduced on the right and depending only on what came before on the left might actually be better for us meatsacks)
select id, count(...something complicated) as complicated_count
from ....
order by complicated_count
This isn't really the large, convincing example I was looking for btw.
IMO it's most useful (though somewhat more difficult to implement) to be able to use the aliases with window functions or large case/when statements, something like
SELECT
page,
SUM(clicks) AS total_clicks,
100. * total_clicks / (SUM(total_clicks) OVER ()) AS click_pct,
100. * SUM(total_clicks) OVER (ORDER BY total_clicks DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (SUM(total_clicks) OVER ()) AS cumulative_click_pct
FROM weblog
GROUP BY page;I could reach to and post a lenghty example, but it’s nothing but boring reshuffles really, spiced with 3-level joins of “modelling db in db to allow user columns”.
I agree on the LTR idea, because reading a symbol not yet defined may lead to confusion.
You're just looking for symbols in the symbol table, I think it's a big difference!
> IMO only allowing references to previous select list items is a perfectly reasonable...
agreed, see my other post where I say the same.
> Though we did allow using aliases in the WHERE clause
And the SQL standards people didn't go for this, and I'm sure they were very far from stupid. And nobody's asking why they didn't allow this, which really bothers me.
But yes, I'd be far happier doing left-to-right dependencies only, which I can believe (though I still need evidence) it would be of some value.
I just realised why this was bothering me. That means 't' and 't.x' are actually different variables. In standard SQL it's always the case (right?) that an unqualified variable ('t') is just an convenient shorthand for the fully qualified variable ('t.x', or more fully I suppose, '<db>.<schema>.t.x), and you just broke that.