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

DuckDB is probably the most important geospatial software of the last decade

dbreunig.com

Post reply on HN
Print view View original

171–177 of 177 posts

Re: DuckDB is probably the most important geospatial software of the last decade

#171

Post by aynyc »

wenc wrote: post #166
aynyc wrote: post #165

Earlier quoted context omitted.

I was really surprised duckdb choked on 500GB. That's maybe a week's worth of data. The partitioning of partquet files might be an issue as not all data are neatly partitioned by date. We have trades with different execution dates, clearance dates and other date values that we need query on.

It doesn’t usually choke on 500 gb of data. I query 600 gb (equivalent to a few TBs of CSVs?) of parquets daily. It’s not the size of the data. It’s the type of data. If date partitioning doesn’t work, just find another chunking key. The key is to get it into parquet format. CSV is just hugely inefficient. Or spin up a larger compute instance with more memory. I have 256gb on mine. I tried running an Apache Spark job…

We have a spark cluster too. Then switch to Athena. I just dislike the cost structure.

The problem with disk based partition is keys are difficult to manage properly.

Re: DuckDB is probably the most important geospatial software of the last decade

#172

Post by wenc »

aynyc wrote: post #171
wenc wrote: post #166

Earlier quoted context omitted.

It doesn’t usually choke on 500 gb of data. I query 600 gb (equivalent to a few TBs of CSVs?) of parquets daily. It’s not the size of the data. It’s the type of data. If date partitioning doesn’t work, just find another chunking key. The key is to get it into parquet format. CSV is just hugely inefficient. Or spin up a larger compute instance with more memory. I have 256gb on mine. I tried running an Apache Spark job…

We have a spark cluster too. Then switch to Athena. I just dislike the cost structure. The problem with disk based partition is keys are difficult to manage properly.

Did Athena on CSV work for you? I've used Athena and it struggles with CSV at scale too.

Btw I'm not suggesting to use Spark. I'm saying that even Spark didn't work on large TSV datasets (it only takes a JOIN or GROUP BY to kill the query performance). The CSV data storage format is simply the wrong one for analytics.

Partitioning is irreversible, but coming up with a thoughtful scheme isn't that hard. You just need to hash something. Even something as simple as a HNV hash on some meaningful field is sufficient. In one of my datasets, I chunk it by week, then by HNV modulo 50 chunks, so it looks like this:

/yearwk=202501/chunk=24/000.parquet

Ask an LLM to suggest partioning scheme or think of one.

CSV is the mistake. The move here is to get out of CSV. Partitioning is secondary -- partitioning here is only used for chunking the Parquet, nothing else. You are not locked into anything.

Re: DuckDB is probably the most important geospatial software of the last decade

#173

Post by wenc »

wodenokoto wrote: post #163
wenc wrote: post #137

Earlier quoted context omitted.

Reprojection is accurate locally but inaccurate at scale. Geodesics are the most accurate (Vincenty etc) but are computationally heavy. Haversine is a nice middle ground.

But is it actually inaccurate at scale? I get that drawing a projection is inaccurate at scale, but if I do all my calculation as meters north/south and east/west of 0,0 on the equator, won’t all my distance calculations be correct? Like 5.000.000 east and 0 m north is 5.000km from the origin. I cannot see how that could ever become inaccurate as I move further away. Where is the inaccuracy introduced? When I reproje…

Btw, you can plug your comment into ChatGPT and it'll give you a reasonable answer. The short answer is: distortions.

Re: DuckDB is probably the most important geospatial software of the last decade

#174

Post by touisteur »

wenc wrote: post #141
touisteur wrote: post #98

Earlier quoted context omitted.

And now I'm curious whether there's a way to actually index external files (make these queries over 600GB faster) and have this index (or many indices) be persistent. I might have missed that when I looked at the docs...

If the data is in Parquet they are already indexed in a sense. No further indexing necessary. If they are stored in DuckDB’s native format (which I don’t use), it supports some state of the art indices. https://duckdb.org/docs/stable/sql/indexes.html I find Parquet plenty fast though.

Ah thanks, of course. I was thinking of dealing with millions of (Geo)JSON files adding up to terabytes, without copying/duplicating them though, mostly indexing. I used to do that with postgres foreign data wrappers and had hopes for duckdb :-). But that's a question for SO or other forum.

Re: DuckDB is probably the most important geospatial software of the last decade

#175

Post by aynyc »

wenc wrote: post #172
aynyc wrote: post #171

Earlier quoted context omitted.

We have a spark cluster too. Then switch to Athena. I just dislike the cost structure. The problem with disk based partition is keys are difficult to manage properly.

Did Athena on CSV work for you? I've used Athena and it struggles with CSV at scale too. Btw I'm not suggesting to use Spark. I'm saying that even Spark didn't work on large TSV datasets (it only takes a JOIN or GROUP BY to kill the query performance). The CSV data storage format is simply the wrong one for analytics. Partitioning is irreversible, but coming up with a thoughtful scheme isn't that hard. You just need…

Yes, on Athena, we process much larger CSV files. But the cost is too crazy. We also have ORC and Parquet files for other dataset which we process with EMR Spark. I really want to get off those distributed analytic engines whenever possible.

I have to think about partition, Spark/Athena both had issues with partitioning by received date. They are scanning way too much data.

Re: DuckDB is probably the most important geospatial software of the last decade

#176

Post by rotten »

vasco wrote: post #77
wenc wrote: post #46

Earlier quoted context omitted.

How is the adoption among DevOps folks? I'm still getting feedback that many devs are not too comfortable with reading and writing SQL. They learned simple SELECT statements in school, but get confused by JOINs and GROUP BYs.

There's no point in learning any much deeper SQL anymore, AI assistants have largely solved SQL querying. Just ask for what you want with natural language.

Getting a sql query to optimal performance is still much more of an art than a specific science. Having the LLM generate a query that appears to work (correctness issues aside), is much more likely than the LLM generating an optimal performing query. While this may not matter for one-off queries common in analytics, when you start worrying about scalability, even the tiniest tweaks can make a huge difference.

Re: DuckDB is probably the most important geospatial software of the last decade

#177

Post by keynesyoudigit »

Demiurge wrote: post #44

> Prior to this, getting up and running from a cold-start might’ve required installing or even compiling severall OSS packages, carefully noting path locations, standing up a specialized database… Enough work that a data generalist might not have bothered, or their IT department might not have supported it. I've been able to "CREATE EXTENSION postgis;" for more than a decade. There have been spatial extensions for PG…

The benefits of a simple install were exaggerated, I think the real point is the cloud native integration and the sheer scalability
Post reply on HN