Int64 columns are loaded as binary columns in PowerBI

[nudles]

ClickHouse version: 24.7.1.2092
ODBC version: 1.2.1.20220905
PowerBI version: 2024 July
Table DDL

CREATE TABLE `Store`
(
	location Int64,
	name String
)
ENGINE = MergeTree()
ORDER BY (location);

After creating the table, I tried to load it from PowerBI via ODBC driver. However, only the 'name' column is visible.
The LocationID column is missing.

In the transform view, it shows that the 'location' column's type is binary. I changed it to number manually.

Then I can see the location column

The question is how can we change ODBC driver to fix the data type mapping?
Thank you!

[nudles]

I also tried columns of other data types, including Int32, UInt32, UInt64.
Only Int32 column can be displayed. All other columns are recongized as Binary column by PowerBI.

[nudles]

Hi @traceon , could you please give some hints on how to debug/fix this issue? Thank you!

[slabko]

The problem seems to come from the fact that SQLGetTypeInfo returns the same type name for different types—signed and unsigned. That behavior is defined here. For example, both Int64 and UInt64 return the type name BIGINT.

At least in my testing, when I remove this type name overlap—either by completely removing unsigned types or by assigning unique names to each type—the problems seem to go away, and all columns appear as whole numbers in Power BI.

Using unique names for each type is indeed the correct approach. In fact, the ODBC documentation requires that the type names returned by SQLGetTypeInfo match the type names used in CREATE TABLE or ALTER TABLE clauses when defining column types: ODBC Documentation for SQLGetTypeInfo.

However, this change might break some backward compatibility, so further investigation is needed.

Additionally, the SQLColumns function should return a dataset with columns of a specific type, as described
here. This makes sense because Power BI should not check the types in the resulting dataset; instead, it should start
reading the values as described in the standard.

[slabko]

@nudles, am I correct in understanding that you can change the type of the location column to Whole Number in the Power BI Query Editor? While it's annoying, there is a workaround for the problem for the time being.