cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dchiulan New Member
New Member

Spark - Wrong query generated

 

Hi,

 

We're using Spark Connector to send queries to our custom Spark Server.

 

However, we receive some queries that are not Spark protocol (e.g. SELECT TOP). This is not valid Hive SQL.

They are slightly different than the most of the queries that we receive in the following ways (just a few big differences that I first noticed):

 

- usage of TOP instead of LIMIT

- not all uppercase

- queries are formatted and multi-line 

 

Example of working query:

SELECT C_4331 AS C_1, COUNT(C_43) AS C_4332 FROM (SELECT C_4f54424c.`Name` AS C_43, C_4954424c.`Age` AS C_4331 FROM `file_azbs1_static`.`titanic` C_4f54424c LEFT OUTER JOIN `es_local_spark`.`demotitanic___titanic` C_4954424c ON (C_4f54424c.`PassengerId` = C_4954424c.`PassengerId`) ) ITBL_0 GROUP BY C_4331 LIMIT 1000001

 

Example of a query wrongly generated (notice the TOP instead of LIMIT and not only)

select top 1000001
    `C4`,
    { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`
from 
(
    select `OTBL`.`Age`,
        `ITBL`.`Name` as `C4`,
        case
            when `Age` is null
            then 1
            else 0
        end as `C1`
    from `es_local_spark`.`demotitanic___titanic` as `OTBL`
    left outer join `file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)
) as `ITBL`
group by `C4`' 
select top 1000001
    `C4`,
    { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`
from 
(
    select `OTBL`.`Age`,
        `ITBL`.`Name` as `C4`,
        case
            when `Age` is null
            then 1
            else 0
        end as `C1`
    from `es_local_spark`.`demotitanic___titanic` as `OTBL`
    left outer join `file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)
) as `ITBL`
group by `C4`

The query is generated by:

We have two titanic tables, joining them on a common PassengerId column generating a One-To-One relationship, then doing a Stacked Column Chart with "Name" column from one table at "Legend" and Count Distinct of "Age" from othe other table at "Value".

 

If you need more specific info let me know

 

Does anyone know anything about that ?

 

Here some logs from PBI

DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8921589Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DRIVER_NAME","Value":"Microsoft Spark ODBC Driver","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0001077"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8922767Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DRIVER_VER","Value":"1.2.5.1006","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000089"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8923317Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DBMS_NAME","Value":"Spark SQL","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000401"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8923818Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DBMS_VER","Value":"2.4.3","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000147"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8921270Z","Action":"Engine/IO/Odbc/Connection/Info","HostProcessId":"4640","DriverName":"Microsoft Spark ODBC Driver","DriverVersion":"1.2.5.1006","DBMSName":"Spark SQL","DBMSVersion":"2.4.3","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0003105"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8924416Z","Action":"Engine/IO/Odbc/Command/ExecuteDirect","HostProcessId":"4640","CommandText":"select top 1000001\r\n `C4`,\r\n { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`\r\nfrom \r\n(\r\n select `OTBL`.`Age`,\r\n `ITBL`.`Name` as `C4`,\r\n case\r\n when `Age` is null\r\n then 1\r\n else 0\r\n end as `C1`\r\n from `SPARK`.`es_local_spark`.`demotitanic___titanic` as `OTBL`\r\n left outer join `SPARK`.`file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)\r\n) as `ITBL`\r\ngroup by `C4`","ParameterCount":"0","Skip":"0","Take":"Infinite","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.5789570"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4714876Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":4,"Duration":"00:00:00.0000967"} DataMashup.Trace Error: 24579 : {"Start":"2019-09-14T17:28:56.8002621Z","Action":"Engine/IO/Db/ODBC/RunWithRetryAttempt","HostProcessId":"4640","Attempt":"0","MaxAttempt":"3","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClass15.<ExecuteCore>b__10()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)\r\n\r\n\r\n","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.6785934"} DataMashup.Trace Error: 24579 : {"Start":"2019-09-14T17:28:56.8002554Z","Action":"Engine/IO/Db/ODBC/RunWithRetry","HostProcessId":"4640","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClass15.<ExecuteCore>b__10()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)\r\n\r\n\r\n","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.6804273"} DataMashup.Trace Warning: 24579 : {"Start":"2019-09-14T17:28:56.7990142Z","Action":"RemotePageReader/RunStub","HostProcessId":"4640","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataSource.Error] ODBC: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.ExecuteCore(String commandText, IList`1 parameters, RowRange rowRange, String[] columnNames, ColumnConversion[] columnConversions)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.TryGetReader(IPageReader& reader)\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetReader()\r\n at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.get_PageReader()\r\n at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass11.<>c__DisplayClass13.<OnBeginGetDataReaderSource>b__10()\r\n at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass7.<RunStub>b__0()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\n\r\n","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.6844163"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.7052607Z","Action":"RemoteDocumentEvaluator/Service/OnBeginGetResult","HostProcessId":"4640","evaluationID":"16","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.7785012"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4848383Z","Action":"DependencyCompiler/Dispose","HostProcessId":"4640","Spark":"True","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000184"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4849237Z","Action":"MemoryCache/CacheStats/Size","HostProcessId":"4640","entryCount":"15","totalSize":"22643","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000117"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4849856Z","Action":"MemoryCache/CacheStats/Access","HostProcessId":"4640","hits":"78","requests":"108","hitrate":"0,722222222222222","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000174"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4863907Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":4,"Duration":"00:00:00.0000641"}

 

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Spark - Wrong query generated

hi, @dchiulan 

Power bi is related to Analysis Services, So when interacting in it, it will generate SQL query in it.

and If you only have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 386 members 3,506 guests
Please welcome our newest community members: