Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"}
Hi, I have a PBI model where I join a table in Direct Query with another one in Import mode (everything is on top of spark of course).
I was able to fix this issue by changing the type of the key from the table in import mode that was in the join condition. It was trying to join one of the key in "Whole Number" and the other one in "String".
It's quite weird that this change fixed the issue but I did not encounter this wrong auto-generated SparkSql issues anymore...
Hope this gives you something to look into.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |