Add support for Clickhouse / Clickhouse Cloud

[stevebissett]

Support for Clickhouse / Clickhouse Cloud in kino_db would be very useful within Livebooks. Support for drivers/clients for Clickhouse within Elixir are fairly limited at this point in time with Pillar and ch

• https://hex.pm/packages/ch
• https://hex.pm/packages/pillar

It looks like we'd need an adbc or similarly compatible driver in order to add Clickhouse support to kino_db.

There is a PostgreSQL interface, which can likely be used in the interim with kino_db, however there are some quirks to this that make this undesirable long term. https://clickhouse.com/docs/en/interfaces/postgresql

Any pointers for how can help (as someone new to Elixir), I'm all ears.

[josevalim]

I think we can do a req_clickhouse, similar to req_athena, by providing a tiny of functionality that wraps around queries and load IPC streams. Btw, here is a notebook, from @cocoa-xu that can help you get started:

<!-- livebook:{"persist_outputs":true} -->
# Clickhouse
```elixir
Mix.install([
  {:req, "~> 0.5.1"},
  {:explorer, "~> 0.8.3"}
])

Section

do_query = fn q ->
  user = System.get_env("LB_CLICKHOUSE_USERNAME")
  passwd = System.get_env("LB_CLICKHOUSE_PASSWORD")
  arrow_format = " FORMAT ArrowStream"
  req = Req.new(url: System.get_env("LB_CLICKHOUSE_ENDPOINT"), auth: {:basic, "#{user}:#{passwd}"})
  q =
    if String.ends_with?(q, arrow_format) do
      q
    else
      q <> arrow_format
    end
  Explorer.DataFrame.load_ipc_stream!(Req.post!(req, body: q).body)
end

#Function<42.11126130/1 in :erl_eval.expr/6>

do_query.("SELECT * FROM iris")

#Explorer.DataFrame<
  Polars[150 x 5]
  sepal.length f64 [5.1, 4.9, 4.7, 4.6, 5.0, ...]
  sepal.width f64 [3.5, 3.0, 3.2, 3.1, 3.6, ...]
  petal.length f64 [1.4, 1.4, 1.3, 1.5, 1.4, ...]
  petal.width f64 [0.2, 0.2, 0.2, 0.2, 0.2, ...]
  variety binary ["Setosa", "Setosa", "Setosa", "Setosa", "Setosa", ...]
>

[stevebissett]

Thanks @josevalim, this is very useful. I'll put something together when I get the chance.