Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Friendlier SQL with DuckDB

duckdb.org

Post reply on HN
Print view View original

91–100 of 134 posts

Re: Friendlier SQL with DuckDB

#91

Post by wenc »

zasdffaa wrote: post #82
wruza wrote: post #79

Earlier quoted context omitted.

SQL is complex enough 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?

> 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 is meh, but what about 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 just don't f…

It’s not trivial but as someone who has implemented something similar (for an equation based modeling language) it’s not super complicated if you use the right abstractions. It’s basically traversing the AST and doing substitutions.

Re: Friendlier SQL with DuckDB

#92

Post by layer8 »

nicoburns wrote: post #5
roncohen wrote: post #2

Lots of great additions. I will just highlight two: 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 (!) Details here: https://clickhouse.com/docs/en/sql-reference/statement…

> Allowing for trailing commas should get included in the SQL spec. Yep! That would be my #1 request for SQL. Seems ridiculous that it's not supported already.

I agree, though you can always use a dummy value as a workaround:

  SELECT
      first_column,
      second_column,
      third_column,
      null

Re: Friendlier SQL with DuckDB

#93

Post by layer8 »

zasdffaa wrote: post #63
snidane wrote: post #10

Earlier quoted context omitted.

Allow referencing columns defined previously in the same query would make duckdb competitive for data analytics. Without that one has to chain With statements for just the tiniest operations. select 1 as x, x + 2 as y, y/x as z;

(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it. 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

What does "do my $%^& nut" even mean? (looks like Perl ;))

Re: Friendlier SQL with DuckDB

#94

Post by tln »

These are great features! I wish I had them in every database. Hmm, I wonder if Babelfish could support that...

PS those examples were so good! really good writing :)

Re: Friendlier SQL with DuckDB

#95

Post by kristianp »

I'm enjoying experimenting with Duckdb from python, it's a promising product and has a large list of data formats it can read, including pandas dataframes from in-memory with zero-copy. However its still quite the moving target, with a number of things not at maturity yet. e.g. the TimestampZ column type isn't implemented yet [1], although it is in the documentation.

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...

Re: Friendlier SQL with DuckDB

#96

Post by nicoburns »

skrtskrt wrote: post #62
gregmac wrote: post #58

Earlier quoted context omitted.

Yes, trailing commas should work everywhere! 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 whe…

JSONC allows comments and trailing commas, but adoption seems to be low. 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.

You can often find better maintained libraries for json5, which is a superset of jsonc

Re: Friendlier SQL with DuckDB

#98

Post by TedDallas »

Does it support a syntax for recursive queries? In T-SQL we use recursive CTEs which are ugly as hell.

This is very cool though. There are lot of features that would make my life easier. Group By All is noice.

Re: Friendlier SQL with DuckDB

#100

Post by mytherin »

kristianp wrote: post #95

I'm enjoying experimenting with Duckdb from python, it's a promising product and has a large list of data formats it can read, including pandas dataframes from in-memory with zero-copy. However its still quite the moving target, with a number of things not at maturity yet. e.g. the TimestampZ column type isn't implemented yet [1], although it is in the documentation. Edit: I came across it via the podcast: https://ww…

The correct type is TIMESTAMPTZ. Is the type TimestampZ mentioned anywhere in our documentation? If so, that looks like a typo.

I fully agree that error message needs to improve, however. I will have a look at that.

Post reply on HN