search

chdb-io / chdb

chDB is an in-process OLAP SQL Engine 🚀 powered by ClickHouse
https://clickhouse.com/docs/en/chdb
Apache License 2.0
2.03k stars 72 forks source link

Support for H3 functions #216

Closed jsheedy closed 3 months ago

jsheedy commented 4 months ago

ClickHouse has support for H3 Indexes, but chdb 1.3.0 does not. Here's an example query which returns an H3 index (a 64-bit integer) in ClickHouse:

chdb.query("SELECT geoToH3(0.0, 0.0, 1)")

ChdbError: Code: 46. DB::Exception: Unknown function geoToH3: While processing geoToH3(0., 0., 1). (UNKNOWN_FUNCTION)

It would be great to access the h3 functions in chdb.

auxten commented 4 months ago

I did not enable H3 in chDB build script. I could do it in the next release. Could share more about your usage scenario?

jsheedy commented 4 months ago

That would be great!

We have a dataset of Parquet files in S3 containing latitude and longitude columns along with measurements, and find H3 useful for spatially aggregating and joining data. Currently I'm using DuckDB with a third party H3 extension for generating the H3 index. In this particular use case it takes some time in a cold run while DuckDB scans the Parquet metadata with a glob like s3://bucket/parquet/**/*.parquet.

I've found chDB can make these cold queries faster, but we're missing the H3 functions. In particular I'm looking for the ClickHouse function geoToH3.

auxten commented 3 months ago

Fixed at v1.4.1