How does one control the column datatype to be non-null in a mutate?

[seandavi]

I have an ibis table with the following schema:

>>> t1.schema()
ibis.Schema {
  pmid2          !int32
  grant_id       string
  grant_acronym  string
  country        string
  agency         string
  g2             array<string>
}

And a clickhouse connection:

>>> con
<ibis.backends.clickhouse.Backend object at 0x11fac2e10>

I try to create the table using con.create_table() and get this error:

>>> con.create_table('t1',t1, engine="MergeTree()")
Code: 43. DB::Exception: Nested type Array(Nullable(String)) cannot be inside Nullable type. (ILLEGAL_TYPE_OF_ARGUMENT) (version 23.8.3.48 (official build))

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/seandavis/.pyenv/versions/3.11.1/lib/python3.11/site-packages/ibis/backends/clickhouse/__init__.py", line 641, in create_table
    self.con.raw_query(
  File "/Users/seandavis/.pyenv/versions/3.11.1/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 442, in raw_query
    return self._raw_request(body, params, fields=fields).data
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/seandavis/.pyenv/versions/3.11.1/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 407, in _raw_request
    self._error_handler(response)
  File "/Users/seandavis/.pyenv/versions/3.11.1/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 332, in _error_handler
    raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
clickhouse_connect.driver.exceptions.DatabaseError: :HTTPDriver for http://clickhouse.cancerdatasci.org:8123 returned response code 500)
 Code: 43. DB::Exception: Nested type Array(Nullable(String)) cannot be inside Nullable type. (ILLEGAL_TYPE_OF_ARGUMENT) (version 23.8.3.48 (official build))

The SQL that is generated (viewed in the Clickhouse query log) is:

CREATE TABLE default.t1 (`pmid2` Int32, `grant_id` Nullable(String), `grant_acronym` Nullable(String), `country` Nullable(String), `agency` Nullable(String), `g2` Nullable(Array(Nullable(String)))) ENGINE = MergeTree 

The g2 column is specified in a mutate that includes a coalesce at the end to ensure a non-null result. How can I get the g2 column to be non-null either at the mutate step or via a cast?

[cpcloud]

@seandavi Thanks for opening a discussion!

You should be able to use notation similar to what shows up in the t1.schema() for the pmid2 column. The ! character indicates a type that is not nullable.

You should be able to use the Table.cast method for this:

In [1]: from ibis.interactive import *

In [2]: con = ibis.clickhouse.connect(host="localhost", database="ibis_testing")

In [3]: t = ibis.memtable({"words": [["hello", "goodbye"], ["wat"]]})

In [4]: s = con.create_table("test", t.cast({"words": "!array<string>"}))

In [5]: s
Out[5]:
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ words                ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ !array<string>       │
├──────────────────────┤
│ ['hello', 'goodbye'] │
│ ['wat']              │
└──────────────────────┘