Power BI ODBC: ClickHouse throws 'CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN' on Nullable Int/Decimal fields in DirectQuery mode

[atifiu]

Problem

ClickHouse fails with the following error when queried via Power BI (ODBC) in DirectQuery mode:

DB::Exception: Cannot convert NULL value to non-Nullable type...
(CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN)

This occurs even when the underlying schema defines the field as Nullable(Int32) or Nullable(Decimal).

Repro Case

SELECT CAST(persona_id AS Float64)
FROM clickhouse_dev.event

ClickHouse throws:
Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type...

[slabko]

Hi @atifiu,

This problem does exist. A quick workaround would be to create a view and convert nullable values to zero or another default value.
Do I understand correctly that you're trying to convert persona_id to a floating-point number in the Power BI interface, which resulted in this error?

We can enable cast_keep_nullable by default in the driver, which would make it behave similarly to other ODBC solutions. Let me think about it, and we’ll probably release another version with the fix later.

[atifiu]

@slabko Basically this is automatic conversion that powerbi is trying to do i.e. casting non nullable column to its original data type Float but since the column has NULL and NULL can't be converted to float it throws error, if instead all values in this float column is already populated by clickhouse by default it to 0.0 then it will not throw any error.

[alimans3]

Facing this issue too, all columns are not nullable in my schema

[alimans3]

Facing this issue too, all columns are not nullable in my schema

cast_keep_nullable works for me

[mshustov]

cast_keep_nullable works for me

@slabko I wonder if we should start with documenting it in https://clickhouse.com/docs/integrations/powerbi

[atifiu]

In my case I get the issue where columns are defined as NULLABLE and regardless of cast_keep_nullable setting, I get this error.

Below is power BI generated query, I have just added the settings to non default value

SET cast_keep_nullable = 1;

select `name`,
    `is_group`,
    `C1`,
    `C2`
from 
(
    select `name`,
        `is_group`,
        sum(cast(`group_id` as Float64)) as `C1`,
        sum(cast(`id` as Decimal)) as `C2`
    from `application_mgt`.`application`
    group by `name`,
        `is_group`
) as `ITBL`
where not `C1` is null or not `C2` is null
limit 1000001;

[slabko]

Hi @atifiu,

From your description, it’s not entirely clear how you’re setting this flag, but based on what I can see, it seems you’re executing SET cast_keep_nullable = 1 in the SQL console or ClickHouse client, and then connecting to ClickHouse from Power BI. If that’s the case, then indeed, it won’t work as expected. cast_keep_nullable is a session setting — it only affects the session in which it is set. See Settings Overview for more information.

However, session settings can be configured as defaults for specific users. In that case, if you enable cast_keep_nullable for a particular user and then use that user to connect to ClickHouse from Power BI, the setting will be applied automatically for the Power BI session.

As a quick example, for the OSS version of ClickHouse (installed as described here: Installing ClickHouse), you need to modify /etc/users.xml and add cast_keep_nullable to the default profile:

<clickhouse>
    <profiles>
        <default>
            <cast_keep_nullable>1</cast_keep_nullable>
        </default>
        ... the rest of the file is truncated ...

User management is a topic of its own, and since I don’t know your specific configuration, I’m providing only a minimal example that works for a freshly installed OSS version of ClickHouse.

My recommendation for now would be to use views until we release a version of the ODBC driver with this setting enabled by default.

[atifiu]

@slabko Example I have given above is executed in dbeaver but query is generated from
PowerBi and I have executed same query in dbeaver with session parameter set.

[slabko]

Ah! I see now.

Yes, in DBeaver each query is independent from the others, so the setting set by SET cast_keep_nullable = 1 will not apply to the following query.

You can see the effect of this setting by adding SETTINGS cast_keep_nullable = 1 to your query, as shown in this example.

select `name`,
    `is_group`,
    `C1`,
    `C2`
from 
(
    select `name`,
        `is_group`,
        sum(cast(`group_id` as Float64)) as `C1`,
        sum(cast(`id` as Decimal)) as `C2`
    from `application_mgt`.`application`
    group by `name`,
        `is_group`
) as `ITBL`
where not `C1` is null or not `C2` is null
limit 1000001
SETTINGS cast_keep_nullable = 1;