Allow to set `columnar=True` attribute when using the DB API and deserialize results into numpy objects

[paxcodes]

There is currently no way to set columnar=True when using the DB API:

with connections['clickhouse'].cursor() as cursor:
   cursor.execute(sql, params)

From the code, it looks like:

• For the result set to return in columnar format, we pass columnar=True to the Client.execute() method.
• The clickhouse-driver's cursor object does not allow columnar to be specified. See:
  clickhouse-driver/clickhouse_driver/dbapi/cursor.py

Proposed

I assume we cannot change the Cursor.execute() method signature since we don't want to deviate from the python DB API.

We can allow specification of columnar parameter via the django settings:

DATABASES = {
    "default": {
        "ENGINE": "clickhouse_backend.backend",
        "OPTIONS": {
            "settings": {
                "columnar": True,
            }
        }
    }
}

In the clickhouse_backend.driver.client.Client.__init__(), we can pop this setting (to ensure it doesn't cause trouble to clickhouse_driver since it's a not a setting it expects)

def __init__(self, *args, **kwargs):
        # https://clickhouse.com/docs/en/sql-reference/data-types/datetime/#usage-remarks
        # The clickhouse-client applies the server time zone by default
        # if a time zone isn’t explicitly set when initializing the data type.
        # To use the client time zone, run clickhouse-client with the --use_client_time_zone parameter.
        settings = kwargs.pop("settings", None) or {}
        settings.setdefault("use_client_time_zone", True)
        columnar = settings.pop("columnar", False)       # <---------- Pop the setting to not cause trouble with underlying clickhouse_driver package
        kwargs.update(settings=settings)
        super().__init__(*args, **kwargs)
        self.client_settings["columnar"] = columnar     # <---------- Set columnar as one of client_settings

And then in the clickhouse_backend.Cursor object, let's override the _prepare method so that we can allow setting of columnar:

def _prepare(self):
   execute, execute_kwargs = super()._prepare()
   execute_kwargs["columnar"] = self._client.client_settings["columnar"]
   return execute, execute_kwargs

I can create a PR for this.

Note: I opened a similar issue in the underlying package clickhouse_driver last week but have not received a response. It might be quicker to apply the changes to this package.

[paxcodes]

A related issue: It seems like this backend does not support use_numpy=True. For example, given DATABASES setting that looks like this:

{
        "ENGINE": "clickhouse_backend.backend",
        "OPTIONS": {
            "settings": {
                "use_numpy": True,
            },
        },
}

Inserting data causes an error: ValueError: NumPy inserts is only allowed with columnar=True

[paxcodes]

I am working on a PR that will allow data to be returned in columns and to be deserialized to numpy objects, without affecting other queries that could run in the system (e.g. DatabaseIntrospection queries, INSERT queries, etc.): #120

[paxcodes]

Thanks for reviewing and merging #120 @jayvynl What's the release schedule like for this package / When do you plan to do a new release?

[jayvynl]

released as v1.4