Int64 columns are loaded as binary columns in PowerBI

[nudles]

trafficstars

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.

[mshustov]

@slabko do we need to release #478 to close the issue ?

[slabko]

@mshustov Yes, we need to release the fix.

[slabko]

Fixed in 1.4.1.20250523

Note: BI caches data definitions. This means that even if you update the driver and try to load a table previously accessed with the older driver, Power BI may still use the data definitions it received from the older version. To prevent issues such as numeric columns being recognized as binary data, you need to reload the table definitions by clicking the small reload button in the upper-right corner.