PowerBI Connector ODBC error when the column name has open bracket character (

1

Hi,

I got a DataSource.Error: ODBC: ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query when i connect my Azure MS SQL Server Table View with open bracket character ( in the column name. The column name is “Factory Name(Chinese Name)”. However the query script wrongly select “Factory Name(__Chinese Name)”, so there is an ERROR.

Think it is a bug in Dremio connector. please see if it can be fixed. Thanks,

In the Dremio UI SQL query, there is no such issue and i can query the data without any problem. However, when i use Dremio Connector in Power BI, it got error.

2

Error log attached:
DataSource.Error: ODBC: ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query: select “Factory Code”,
“Factory Name”,
“Factory Name( Chinese Name)”
from “xxxxxxx.xxxxxxxx.dbo”.“V_Factory”
[30029]Query execution error. Details:[
VALIDATION ERROR: Column ‘Factory Name( Chinese Name)’ not found in any table

SQL Query select “Factory Code”,
“Factory Name”,
“Factory Name( Chinese Name)”
from “xxxxxxx.xxxxxxxx.dbo”.“V_Factory”
startLine 3
startColumn 5
endLine 3
endColumn 33

[Error Id: cec3c3f0-aa5a-4331-ad08-d30c8265dd8f on localhost:31010]

(org.apache.calcite.runtime.CalciteContextException) From line 3, column 5 to line 3, column 33: Column ‘Factory Name( Chinese Name)’ not found in any table
sun.reflect.GeneratedConstructorAccessor207.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1
java.lang.reflect.Constructor.newInstance():-1
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.sql.SqlUtil.newContextException():798
org.apache.calcite.sql.SqlUtil.newContextException():786
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName():104
org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName():173
org.apache.calcite.sql.validate.ListScope.findQualifyingTableName():131
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():220
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4579
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4561
org.apache.calcite.sql.SqlIdentifier.accept():334
org.apache.calcite.sql.validate.SqlValidatorImpl.expand():4254
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem():402
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():3581
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3088
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():172
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():170
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():159
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():290
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Column ‘Factory Name( Chinese Name)’ not found in any table
sun.reflect.GeneratedConstructorAccessor206.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1
java.lang.reflect.Constructor.newInstance():-1
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():798
org.apache.calcite.sql.SqlUtil.newContextException():786
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName():104
org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName():173
org.apache.calcite.sql.validate.ListScope.findQualifyingTableName():131
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():220
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4579
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4561
org.apache.calcite.sql.SqlIdentifier.accept():334
org.apache.calcite.sql.validate.SqlValidatorImpl.expand():4254
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem():402
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():3581
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3088
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():172
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():170
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():159
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():290
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1

]
Details:
DataSourceKind=Dremio
DataSourcePath=localhost
OdbcErrors=Table

3
  1. Do you have our latest ODBC driver installed? https://www.dremio.com/download/
  2. Have you reviewed & followed our doc on how to integrate with PBI? https://docs.dremio.com/client-applications/microsoft-power-bi.html
  3. Assuming yes to both questions above, it would help to better understand exactly at what point in PBI you run into this issue or what you are exactly doing when this occurs. Is it when you are just trying to connect to a table and preview the data (step #5 from our docs)?
4

Thanks @anthony for your quick response.
Both 1 and 2 are yes. The error message i posted prompts when i try to preview the data in Step 5 from the doc). All the other tables from Dremio can be loaded and previewed using PBI, but just having problem when i am loading a table with column name contains bracket characters from MS SQL server.
Maybe, we can have a test to create a table with column name contains bracket characters, then see if the problem can be reproduced?

5

I was able to repro the issue. It seems like PBI adds a space after every ( so therefore a column named “Test(” actually becomes "Test( " in the query. Let me investigate to see if this is something we can fix. Meanwhile, I would recommend you create a VDS on top of that table and rename the column.

1 Like
6

@poonhs-esquel May I ask which Dremio connector version you are using? (You can check this by going to the ODBC Admin > System DSN > Dremio Connector > Configure)

7

Sorry for late reply. The version is v1.3.14.1043 (64-bit)

image
image.png412×602 12.8 KB

8

No worries! Thanks!
There is a newer version of the Dremio Connector v1.3.19 available for download.
Would you mind giving it a try?