I have the same problem with Sybase.. "dbo" is not recognized during SQL execution, but somehow "dbo" is recognized when validating SQL.
https://issues.apache.org/jira/browse/DRILL-5427 -----Original Message----- From: Erick Reyes [mailto:[email protected]] Sent: Monday, June 12, 2017 9:45 AM To: [email protected] Subject: RE: Problems querying with SQL server due to database name containing a period (.) Charles, Thanks for the quick reply, the table does not contain a period, the database name does. This is what my connection looks like { "type": "jdbc", "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver", "url": "jdbc:sqlserver://{conectionstring};datebaseName=TrackMan.Baseball", "username": "****", "password": "****", "enabled": true } This is what my query looks like select * from tsql.dbo.Game Best, Erick Reyes -----Original Message----- From: Charles Givre [mailto:[email protected]] Sent: Monday, June 12, 2017 12:40 PM To: user <[email protected]> Subject: Re: Problems querying with SQL server due to database name containing a period (.) Erick, Are you putting backticks around the table? IE: SELECT <fields> FROM sql.database.`TrackMan.Baseball` On Mon, Jun 12, 2017 at 12:37 PM, Erick Reyes <[email protected]> wrote: > Hello, > > I am a data engineer for a small start-up in the sport technology > industry. We have SQL and NoSQL databases and want to leverage Apache > Drill as a way to query across our DBs. I was able to set up our > MongoDB and SQL server instances without major issues but querying one > of our SQL database with the name TrackMan.Baseball is giving us > issues. I believe this is due to the name containing a period. Having > a period as part of the database name was a horrible design decision > made by us several years ago and we are now living with the > consequences, is there any way I can work around this issue? Below is > the error message I get when trying to query this database, databases > under the same connection string with a different name can be queried without > any problems. Is there a solution for me? > > Best, > > Erick S. Reyes > Data Platforms TrackMan Baseball > (312) 647-5373 > > Query Failed: An Error Occurred > org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: > From line 1, column 15 to line 1, column 18: Table 'tsql.dbo.Game' not > found SQL Query null [Error Id: 44aa1094-3bbc-4d9a-bb01-d021afa4fb6a > on Stamford-SQL01.ISG.local:31010] > (org.apache.calcite.runtime.CalciteContextException) > From line 1, column 15 to line 1, column 18: Table 'tsql.dbo.Game' not > found sun.reflect.GeneratedConstructorAccessor97.newInstance():-1 > sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 > java.lang.reflect.Constructor.newInstance():423 > org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405 > org.apache.calcite.sql.SqlUtil.newContextException():765 > org.apache.calcite.sql.SqlUtil.newContextException():753 > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(): > 3974 > org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8 > 83 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2806 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2791 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3014 > org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8 > 83 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869 > org.apache.calcite.sql.SqlSelect.validate():210 org.apache.calcite.sql. > validate.SqlValidatorImpl.validateScopedExpression():843 > org.apache.calcite.sql.validate.SqlValidatorImpl.validate():557 > org.apache.drill.exec.planner.sql.SqlConverter.validate():169 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateN > ode():606 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateA > ndConvert():192 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan() > :164 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():131 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():79 > org.apache.drill.exec.work.foreman.Foreman.runSQL():1050 > org.apache.drill.exec.work.foreman.Foreman.run():281 java.util.concurrent. > ThreadPoolExecutor.runWorker():1142 java.util.concurrent. > ThreadPoolExecutor$Worker.run():617 java.lang.Thread.run():748 Caused > By > (org.apache.calcite.sql.validate.SqlValidatorException) Table > 'tsql.dbo.Game' not found > sun.reflect.GeneratedConstructorAccessor96.newInstance():-1 > sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 > java.lang.reflect.Constructor.newInstance():423 > org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405 > org.apache.calcite.runtime.Resources$ExInst.ex():514 > org.apache.calcite.sql.SqlUtil.newContextException():765 > org.apache.calcite.sql.SqlUtil.newContextException():753 > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(): > 3974 > org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8 > 83 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2806 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2791 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3014 > org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8 > 83 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869 > org.apache.calcite.sql.SqlSelect.validate():210 org.apache.calcite.sql. > validate.SqlValidatorImpl.validateScopedExpression():843 > org.apache.calcite.sql.validate.SqlValidatorImpl.validate():557 > org.apache.drill.exec.planner.sql.SqlConverter.validate():169 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateN > ode():606 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateA > ndConvert():192 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan() > :164 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():131 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():79 > org.apache.drill.exec.work.foreman.Foreman.runSQL():1050 > org.apache.drill.exec.work.foreman.Foreman.run():281 java.util.concurrent. > ThreadPoolExecutor.runWorker():1142 java.util.concurrent. > ThreadPoolExecutor$Worker.run():617 java.lang.Thread.run():748 > > This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information. Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock’s Privacy Policy. For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations. © 2017 BlackRock, Inc. All rights reserved.
