Forum Discussion
Intermittent refresh error with ODBC connection to ClickHouse
Hi everyone,
I’m using an ODBC connection to connect Power BI to a ClickHouse database, and I’m running into a difficult issue when refreshing the data.
➡️ The error message I’m getting is "ODBC: ERROR [HY000] Syntax error: Not a valid integer: {value}"
Note: The {value} part is a short text string that varies — it’s not always the same, and can be between 1 and 4 characters.
Please find at the end of the post an example of the complete error message I'm receiving.
I use the ODBC connection as my data source, and I write a custom SQL query for each table I need. That is, each query sent to ClickHouse becomes a table in my Power BI model.
The error occurs intermittently — sometimes it affects one table, other times a different one, and occasionally it doesn’t appear at all. It’s not tied to a single table; it happens across several.
I tried to fix it by adding an explicit CAST at the end of every query (I’m attaching an example of the code), but the error still showed up. So I’m starting to suspect that Power BI might be applying some additional transformation that I can’t identify.
-- Table 'FACT_g-funnel-contents'
select
cast(sector as String) as sector,
cast(company as String) as company,
cast(funnel_step_id as Int32) as funnel_step_id,
cast(funnel_step_name as String) as funnel_step_name,
toDate(publication_date_day) as publication_date_day,
cast(funnel_step_n_elements as Int64) as funnel_step_n_elements
from output
The biggest challenge is that the error message doesn’t specify which field is causing the issue, making it very difficult to troubleshoot.
Has anyone experienced something similar? Is there a better way to debug this kind of error when Power BI doesn’t provide specific details?
➡️ I’m attaching an example of the error message to help clarify, for a case where I got the error on table 'FACT_g-funnel-contents', whose query ending I shared as an example above.
Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [HY000] Syntax error: Not a valid integer: y"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ErrorCode","detail":{"type":1,"value":"10478"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Odbc"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"database=foreign-policy;driver={ClickHouse ODBC Driver (Unicode)};host=xrx1q4fjb1.germanywestcentral.azure.clickhouse.cloud;maxmemoryusage=17179869184;port=8443;sockettimeout=240000"}},{"code":"Microsoft.Data.Mashup.ValueError.OdbcErrors","detail":{"type":1,"value":"#table({\"SQLState\", \"NativeError\", \"Message\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: FACT_g-funnel-contents.
Thanks in advance for any help!
Hi mariomm17
I'll be honest, I haven't faced this issue before and don't even know where to start to fix it, but your post has been viewed 70 times with no attempt at an answer which is very rare so I wanted to try and help. Here is an AI generated response. I hope it's helpful but I can't guarantee it will be.
Understanding the Error
The error message:
ODBC: ERROR [HY000] Syntax error: Not a valid integer: y
...suggests that ClickHouse is trying to interpret a string value ("y" in this case) as an integer, and failing. This typically happens when:
- Power BI or the ODBC driver implicitly casts a column to an integer.
- The column contains mixed types (e.g., mostly integers but some strings).
- There’s a type mismatch between what Power BI expects and what ClickHouse returns.
🧠 Why It’s Intermittent
The intermittent nature of the error suggests that:
- The problematic value only appears in some refreshes, possibly due to data changes or query folding.
- Power BI might be inferring types based on a sample of the data, and then applying transformations that don’t match the full dataset.
✅ Steps to Troubleshoot and Fix
1. Double-check the source data
Ensure that the column you're casting to Int32 or Int64 (like funnel_step_id or funnel_step_n_elements) never contains non-numeric strings. Even a single "y" or "null" string can cause this.
You can test this with a query like:
SELECT funnel_step_id FROM output WHERE funnel_step_id NOT LIKE '%[^0-9]%'Or better:
SELECT funnel_step_id FROM output WHERE NOT isInt(funnel_step_id)If isInt() isn’t available, you can use regex or try casting and catching errors.
2. Use tryCast() instead of cast()
ClickHouse supports tryCast() which returns NULL instead of throwing an error if the cast fails:
SELECT cast(sector as String) as sector, cast(company as String) as company, tryCast(funnel_step_id as Int32) as funnel_step_id, cast(funnel_step_name as String) as funnel_step_name, toDate(publication_date_day) as publication_date_day, tryCast(funnel_step_n_elements as Int64) as funnel_step_n_elements FROM outputThis can prevent the refresh from failing due to bad values.
3. Disable query folding
Power BI sometimes rewrites queries during refresh. To prevent this:
- In Power BI, go to Advanced Editor and wrap your query with Value.NativeQuery(...) to ensure it’s passed directly to ClickHouse.
- Example:
let Source = Odbc.DataSource("dsn=ClickHouseDSN"), Query = " SELECT ... FROM output ", Result = Value.NativeQuery(Source, Query) in Result4. Check Power BI data types
After loading the data, check the Data Type in Power BI for each column. If Power BI expects an integer but gets a string, it might try to convert it during refresh.
5. Log or isolate the failing rows
If possible, create a diagnostic query that logs or filters out rows where casting fails. This can help you identify problematic data.
6 Replies
- wardy912
Super User
Hi mariomm17
I'll be honest, I haven't faced this issue before and don't even know where to start to fix it, but your post has been viewed 70 times with no attempt at an answer which is very rare so I wanted to try and help. Here is an AI generated response. I hope it's helpful but I can't guarantee it will be.
Understanding the Error
The error message:
ODBC: ERROR [HY000] Syntax error: Not a valid integer: y
...suggests that ClickHouse is trying to interpret a string value ("y" in this case) as an integer, and failing. This typically happens when:
- Power BI or the ODBC driver implicitly casts a column to an integer.
- The column contains mixed types (e.g., mostly integers but some strings).
- There’s a type mismatch between what Power BI expects and what ClickHouse returns.
🧠 Why It’s Intermittent
The intermittent nature of the error suggests that:
- The problematic value only appears in some refreshes, possibly due to data changes or query folding.
- Power BI might be inferring types based on a sample of the data, and then applying transformations that don’t match the full dataset.
✅ Steps to Troubleshoot and Fix
1. Double-check the source data
Ensure that the column you're casting to Int32 or Int64 (like funnel_step_id or funnel_step_n_elements) never contains non-numeric strings. Even a single "y" or "null" string can cause this.
You can test this with a query like:
SELECT funnel_step_id FROM output WHERE funnel_step_id NOT LIKE '%[^0-9]%'Or better:
SELECT funnel_step_id FROM output WHERE NOT isInt(funnel_step_id)If isInt() isn’t available, you can use regex or try casting and catching errors.
2. Use tryCast() instead of cast()
ClickHouse supports tryCast() which returns NULL instead of throwing an error if the cast fails:
SELECT cast(sector as String) as sector, cast(company as String) as company, tryCast(funnel_step_id as Int32) as funnel_step_id, cast(funnel_step_name as String) as funnel_step_name, toDate(publication_date_day) as publication_date_day, tryCast(funnel_step_n_elements as Int64) as funnel_step_n_elements FROM outputThis can prevent the refresh from failing due to bad values.
3. Disable query folding
Power BI sometimes rewrites queries during refresh. To prevent this:
- In Power BI, go to Advanced Editor and wrap your query with Value.NativeQuery(...) to ensure it’s passed directly to ClickHouse.
- Example:
let Source = Odbc.DataSource("dsn=ClickHouseDSN"), Query = " SELECT ... FROM output ", Result = Value.NativeQuery(Source, Query) in Result4. Check Power BI data types
After loading the data, check the Data Type in Power BI for each column. If Power BI expects an integer but gets a string, it might try to convert it during refresh.
5. Log or isolate the failing rows
If possible, create a diagnostic query that logs or filters out rows where casting fails. This can help you identify problematic data.
v-echaithraCommunity Support
Hi mariomm17 ,
Thank you for wardy912 your inputs.We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.
We truly appreciate your patience and look forward to assisting you further if needed.
Warm regards,
Chaithra E.- v-echaithra
Hi mariomm17 ,
We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E. - v-echaithra
Hi mariomm17 ,
We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E. What solved this issue was replacing the ODBC connector with the native ClickHouse connector. The error did not appear again.
- v-echaithra
Hi mariomm17 ,
Thank you for confirming that issue has been resolved and sharing the solution. If further assistance is still required. We are available to support you and are committed to helping you.
Best Regards,
Chaithra E.
