Is it possible to use backend-specific column types and codecs for clickhouse?

[seandavi]

Clickhouse has many data types and even column-level codecs to improve performance and decrease storage size. What support exists for using those in ibis? In particular, I'm interested in LowCardinality(String) and applying compression codecs.

I did look through docs quickly and didn't see anything obvious. When I glanced at code, there is a test, but I didn't see how I could convert that back to usage (my limits of ibis code understanding).

Any suggestions?

[cpcloud]

Hey @seandavi!

We currently don't have a way to use clickhouse's optimized types at the moment nor compression codecs or any of the other per-column attributes, other than to manually construct a CREATE TABLE string yourself.

For codecs, I think we could probably add a ClickHouse-specific codecs argument to create_table which would look something like this:

con.create_table(..., codecs={"x": "LZ4", "y": ("Delta", "ZSTD")})

Regarding types:

When I glanced at code, there is a test

We test that LowCardinality(String) is inferred as an Ibis String dtype, but the LowCardinality information is lost in that inference.

I'm not entirely sure how we might be able to support this use case for ClickHouse without doing something more invasive like adding a new type.

Perhaps a type_override parameter to create_table, something like

con.create_table("t", schema=..., type_override={"x": "LowCardinality(String)"})

[cpcloud]

@seandavi Are you still interested in this functionality for the ClickHouse backend?

[seandavi]

To use clickhouse "well" really does require getting one's hands dirty with their SQL dialect and database-specific features. It's probably better for Ibis to spend time on more general functionality.

If someone asked about how to work with Clickhouse and Ibis together, what I would recommend at this point is to use Ibis as usual, create the table "as is" using Ibis (without clickhouse-specific features), and then use SQL (CTAS) to generate a fine-tuned table that has all the clickhouse bells-and-whistles.

[cpcloud]

Interesting! Makes sense!

If you can stomach the Nth chat app 😂 we have a Zulip instance now if you're interested in more real time discussion (https://ibis-project.zulipchat.com/)

Would be great to hear more about how you're using Ibis and ClickHouse, I know we have a few of our team that really like ClickHouse :)