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

11–20 of 134 posts

Re: Friendlier SQL with DuckDB

#11

Post by 1egg0myegg0 »

eis wrote: post #6

I was just yesterday exploring DuckDB and it looked very promising but I was very surprised to find out that indexes are not persisted (and I assume that means they must fit in RAM). > 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 ins…

Persistent indexes are being actively worked on! Stay tuned. As for the crashes - DuckDB is very well tested and used in production in many places. The core functionality is very mature! Let us know if you test it out! Happy to help if I can.

(disclaimer - on the DuckDB team)

Re: Friendlier SQL with DuckDB

#12

Post by 1egg0myegg0 »

snidane wrote: post #10
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…

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;

Yes, good thought! That is listed at the bottom of the article as something we are looking at for the future.

Re: Friendlier SQL with DuckDB

#13

Post by 1egg0myegg0 »

enjalot wrote: post #8
tosh wrote: post #3

How does DuckDB compare to SQLite (e.g. which workloads are a good fit for what? Would it be a good idea to use both?) I found https://duckdb.org/why_duckdb but I'm sure someone here can share some real world lessons learned?

one thing I love about DuckDB is that it supports Parquet files, which means you can get great compression on the data. Here's an examples getting a 1 million row CSV under 50mb and interactive querying in the browser: https://observablehq.com/@observablehq/bandcamp-sales-data?c... 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 ow…

Yes, it is always difficult to use dates in SQLite... DuckDB makes dates easier - like they should be!

Re: Friendlier SQL with DuckDB

#14

Post by chrisjc »

What are some potential long-term liabilities we might see in choosing to adopt duckdb today?

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.

Re: Friendlier SQL with DuckDB

#15

Post by tosh »

1egg0myegg0 wrote: post #9
tosh wrote: post #3

How does DuckDB compare to SQLite (e.g. which workloads are a good fit for what? Would it be a good idea to use both?) I found https://duckdb.org/why_duckdb but I'm sure someone here can share some real world lessons learned?

Excellent question! I'll jump in - I am a part of the DuckDB team though, so if other users have thoughts it would be great to get other perspectives as well. 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 a…

Thanks, it does help! I understand SQLite might be better/ideal for OLTP (?) but would DuckDB also work for use cases where I query for specific records (e.g. based on primary key) or would I rather use SQLite for OLTP stuff and then read SQLite from DuckDB for analytical workloads?

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.

Re: Friendlier SQL with DuckDB

#16

Post by pholanda »

eis wrote: post #6

I was just yesterday exploring DuckDB and it looked very promising but I was very surprised to find out that indexes are not persisted (and I assume that means they must fit in RAM). > 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 ins…

Hey eis, you are correct. We do not support index storage in our latest release. I am currently implementing this, and it is in a fairly advanced stage. So it should be featured in the next release.

This took a little while because we use a fairly modern index structure with no literature definition on how to buffer manage it.

Re: Friendlier SQL with DuckDB

#17

Post by 1egg0myegg0 »

chrisjc wrote: post #14

What are some potential long-term liabilities we might see in choosing to adopt duckdb today? 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…

Let me see if I can assuage some of your concerns!

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!

Re: Friendlier SQL with DuckDB

#18

Post by wenc »

This is fantastic. Column aliases are super helpful in reducing verbose messiness.

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.

Re: Friendlier SQL with DuckDB

#19

Post by 1egg0myegg0 »

tosh wrote: post #15
1egg0myegg0 wrote: post #9

Earlier quoted context omitted.

Excellent question! I'll jump in - I am a part of the DuckDB team though, so if other users have thoughts it would be great to get other perspectives as well. 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 a…

Thanks, it does help! I understand SQLite might be better/ideal for OLTP (?) but would DuckDB also work for use cases where I query for specific records (e.g. based on primary key) or would I rather use SQLite for OLTP stuff and then read SQLite from DuckDB for analytical workloads? 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…

Good questions! You are correct that it depends. We do have indexes to help with point queries, but they are not going to be quite as fast as SQLite because DuckDB stores data in a columnar format. (Soon they will be persistent - see comments above!) That columnar format is really great for scanning many items, but not optimal for grabbing all of a single row.

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

Re: Friendlier SQL with DuckDB

#20

Post by eis »

pholanda wrote: post #16
eis wrote: post #6

I was just yesterday exploring DuckDB and it looked very promising but I was very surprised to find out that indexes are not persisted (and I assume that means they must fit in RAM). > 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 ins…

Hey eis, you are correct. We do not support index storage in our latest release. I am currently implementing this, and it is in a fairly advanced stage. So it should be featured in the next release. This took a little while because we use a fairly modern index structure with no literature definition on how to buffer manage it.

It's good to hear that persistent indexes are coming soon. I saw it was on the roadmap but didn't know how far out this feature was. Do you have an idea when that release could be out?

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 :)

Post reply on HN