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

DuckDB – An in-process SQL OLAP database management system

duckdb.org

Post reply on HN
Print view View original

91–100 of 104 posts

Re: DuckDB – An in-process SQL OLAP database management system

#91

Post by camgunz »

RobinL wrote: post #87
camgunz wrote: post #75

Earlier quoted context omitted.

Databases are just much, much faster than Pandas, and that's before you start factoring the extraction and loading of data. I treat Pandas as a last resort when I can't do something in SQL, generally this is something like integrating with external services or running recordlinkage.

If you're curious, I've written a FOSS record linkage library that executes everything as SQL. It supports multiple SQL backends including DuckDB and Spark for scale, and runs faster than most competitors because it's able to leverage the speed of these backends: https://github.com/moj-analytical-services/splink

Oh hot tip! Thank you! Love the blog btw

Re: DuckDB – An in-process SQL OLAP database management system

#92

Post by aynyc »

tkhattra wrote: post #86
aynyc wrote: post #77

I don't get this: * When to not use DuckDB: Multiple concurrent processes reading from a single writable database* So no concurrent reads? Or is it no concurrent reads while writing?

the latter - you can either have multiple concurrent readers or a single writer. see https://duckdb.org/faq.html#how-does-duckdb-handle-concurren... .

What if I run multiple processes? One is to write, the rest are reads?

Re: DuckDB – An in-process SQL OLAP database management system

#93

Post by jetter »

SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

I use Clickhouse to store close to 1TB of API analytics data (which would be 10TB in MongoDB, Clickhouse has insane compression ) and it's a wonderful and stable SQL-first alternative to DuckDB - which is a very exciting piece of software, but is indeed too young to embed into boring production. The last time I checked DuckDB npm package, it used callbacks instead of awaits..

Re: DuckDB – An in-process SQL OLAP database management system

#94

Post by wenc »

brunoqc wrote: post #61

Anyone both tried duckdb and clickhouse-local?

I use duckdb and looked into clickhouse-local. The dealbreaker for me was that clickhouse-local supported only a subset of SQL (that most people would probably be ok with, but not sufficient for a lot of complex analytics work). For instance, clickhouse doesn't support lead/lag functions natively (though it does propose workarounds).

Here's clickhouse's SQL support: https://clickhouse.com/docs/en/sql-reference/

Compare this to DuckDB's (on the sidebar): https://duckdb.org/docs/sql/introduction

DuckDB's SQL coverage is much more complete and matches my experience with full blown databases like Postgres and Redshift.

As well, performance-wise DuckDB is currently still somewhat faster than clickhouse-local [1] but I would say this is a secondary consideration -- as long as either is "fast enough for your purposes" this shouldn't be an issue -- and clickhouse is plenty fast.

The primary consideration for me would be the SQL support. That said, if you don't use any complex SQL, clickhouse-local seems like it would be a worthy contender.

[1] https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...

Re: DuckDB – An in-process SQL OLAP database management system

#95

Post by kiratp »

mcdonje wrote: post #58
kiratp wrote: post #30

Its disappointing that C++ was chosen to build something that is going to live in-process. Rust would have been so much safer. All the segfaults you get when running DucDB supports this statement.

I agree about Rust's memory safety advantage over C++, but I disagree that it's disappointing from a project perspective. Some DB experts made a good DB using a performant language they're comfortable with. You can't make project choices in a vacuum, and you can't assume others can either. People have limited time. The choice they were facing was probably not C++ vs Rust, but C++ vs nothing because they didn't have t…

We built in Rust and we started in 2019. Our first lines of product code were also my first lines of Rust.

It can be done and it’s not that hard.

I think the choice of C++ for an in-process DB that is going to be very popular makes the entire industry less secure. If Chrome, one of the largest budget C++ code bases, still has memory bugs then there is no way DuckDB won’t.

Re: DuckDB – An in-process SQL OLAP database management system

#96

Post by antonycourtney »

jetter wrote: post #93
SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

I use Clickhouse to store close to 1TB of API analytics data (which would be 10TB in MongoDB, Clickhouse has insane compression ) and it's a wonderful and stable SQL-first alternative to DuckDB - which is a very exciting piece of software, but is indeed too young to embed into boring production. The last time I checked DuckDB npm package, it used callbacks instead of awaits..

I can understand how the older callback API for node.js might form a negative impression, but it's really not indicative of the maturity of the core db engine at all. And remember: the vast majority of users use the Python API. Even better news is that, as of a couple of months ago, there is now this package (which I wrote at MotherDuck and we have open sourced) which provides typed promise wrappers for the Duckdb API: https://www.npmjs.com/package/duckdb-async. This is an independent npm package for now, but was developed in close coordination with the DuckDb core team.

Re: DuckDB – An in-process SQL OLAP database management system

#97

Post by antonycourtney »

krimpenrik wrote: post #47
SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

Recently tried the GUI tool for ducks, forgot what's it called, something like 'Tab' and was quite disappointed. I feel duckdb needs a good tool like sqliteviewer to really take off.

I think you're referring to Tad (https://www.tadviewer.com), which I developed. Tad isn't "the GUI tool for DuckDb"; it's a desktop app that provides a pivot table based viewer for tabular data files (CSV, Parquet, and DuckDb/SQLite database files). It uses DuckDb as its engine, but pre-dates DuckDb and was developed independently. It's listed in the DuckDb docs along with several others tools that work with or use DuckDb. All that said, I'm sorry you found it disappointing, and would welcome any constructive feedback on what specifically you found lacking, either here or to tad-feedback@tadviewer.com.

Re: DuckDB – An in-process SQL OLAP database management system

#98

Post by nojito »

wenc wrote: post #90
nojito wrote: post #83

Earlier quoted context omitted.

How would you do df.T in sql?

df.T is a special Pandas dataframe transpose on the dataframe index and the columns. DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other. But to answer your original question, the SQL analogue to a transpose are PIVOT/UNPIVOT operations which are mathematically rotation operations on invariants (your dimensions). This makes them much more general than a transpose -- w…

>DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other.

Transforming into a pandas df isn't zero copy.

UNPIVOT and PIVOT are quite verbose compared to df.T.

Re: DuckDB – An in-process SQL OLAP database management system

#99

Post by brahbrah »

nerdponx wrote: post #22
SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?

Pandas is in a separate category from all of these, including polars. If you were to say “pandas in long format only” then yes that would be correct, but the power of pandas comes in its ability to work in a long relational or wide ndarray style. Pandas was originally written to replace excel in financial/econometric modeling, not as a replacement for sql. Models written solely in the long relational style are near unmaintainable for constantly evolving models with hundreds of data sources and thousands of interactions being developed and tuned by teams of analysts and engineers.

For example, this is how some basic operations would look in pandas.

Bump prices in 2020 up $1:

    prices_df.loc['2020'] += 1
Add expected temperature offsets to base temperature forecast:

    temp_df + offset_df
Now imagine thousands of such operations, and you can see the necessity of pandas in models like this.

Re: DuckDB – An in-process SQL OLAP database management system

#100

Post by brahbrah »

benjaminwootton wrote: post #56
nerdponx wrote: post #22

Earlier quoted context omitted.

As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?

SQL is easier and more natural to work with than Pandas.

Sometimes yes and sometimes no. For example if you had price data and you wanted to bump prices up a dollar in 2020 how would you do that in sql?

In pandas it’s:

    prices_df.loc['2020'] += 1
If you had a temperature forecast and you wanted to add the expected temperature miss to them, how would you do that on sql?

In pandas it’s:

    temps_df + expected_miss_df
Post reply on HN