Spark – Consulta incorrecta generada

Desktop
Un usuario Pregunto ✅

dchiulan

Hola,

Usamos Spark Connector para enviar consultas a nuestro Spark Server personalizado.

Sin embargo, recibimos algunas consultas que no son del protocolo Spark (por ejemplo, SELECT TOP). Esto no es Hive SQL válido.

Son ligeramente diferentes a la mayoría de las consultas que recibimos de las siguientes maneras (solo algunas grandes diferencias que noté por primera vez):

– uso de TOP en lugar de LIMIT

– no todo en mayúsculas

– las consultas tienen formato y son de varias líneas

Ejemplo de consulta de trabajo:

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`4 (C_4f54424c.`PassengerId` = C_4954424c.`PassengerId`) ) ITBL_0 GRUPO POR C_4331 LÍMITE 1000001

Ejemplo de una consulta generada incorrectamente (observe el TOP en lugar de LIMIT y no solo)

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`

La consulta es generada por:

Tenemos dos tablas titánicas, uniéndolas en una columna común de PassengerId generando una relación uno a uno, luego haciendo un gráfico de columnas apiladas con la columna «Nombre» de una tabla en «Leyenda» y Conteo distinto de «Edad» de la otra tabla en «Valor».

Si necesita información más específica, hágamelo saber

¿Alguien sabe algo de eso?

Aquí algunos registros de 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 1000001rn `C4`,rn { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`rnfrom rn(rn select `OTBL`.`Age`,rn `ITBL`.`Name` as `C4`,rn casern when `Age` is nullrn then 1rn else 0rn end as `C1`rn from `SPARK`.`es_local_spark`.`demotitanic___titanic` as `OTBL`rn left outer join `SPARK`.`file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)rn) as `ITBL`rngroup 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:rnExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35rnMessage: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.rnStackTrace:n at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClass15.<ExecuteCore>b__10()rn at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)rnrnrn","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:rnExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35rnMessage: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.rnStackTrace:n at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClass15.<ExecuteCore>b__10()rn at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)rn at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)rnrnrn","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:rnExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35rnMessage: [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 '.rnStackTrace:n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.ExecuteCore(String commandText, IList`1 parameters, RowRange rowRange, String[] columnNames, ColumnConversion[] columnConversions)rn at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.TryGetReader(IPageReader& reader)rn at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetReader()rn at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.get_PageReader()rn at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass11.<>c__DisplayClass13.<OnBeginGetDataReaderSource>b__10()rn at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass7.<RunStub>b__0()rn at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)rnrnrn","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"}

nicolas

Hola, tengo un modelo PBI en el que uno una tabla en Direct Query con otra en modo Import (todo está encima de Spark, por supuesto).

Pude solucionar este problema cambiando el tipo de clave de la tabla en el modo de importación que estaba en la condición de combinación. Estaba tratando de unir una de las claves en «Número entero» y la otra en «Cadena».

Es bastante extraño que este cambio haya solucionado el problema, pero ya no encontré estos problemas de SparkSql autogenerados incorrectos…

Espero que esto te dé algo que investigar.

hola, @dchiulan

El poder bi está relacionado con Servicios de análisis, Entonces, al interactuar en él, generará una consulta SQL en él.

y si solo tiene este problema para Power BI, será mejor que cree un ticket de soporte en Soporte de Power BI para obtener más ayuda.

Atentamente,

Lin

Entradas relacionadas

Conversión de UTC a EST

Desktop

Un usuario Pregunto ✅ Daven Hola, Estoy buscando convertir la columna de fecha y hora UTC al horario de verano EST. ¿Cómo lo convierto en…

Informes de estilo Kanban

Desktop

Un usuario Pregunto ✅ Sachinhitachi Hola a todos, Tengo un requisito en el que el cliente necesita informes de estilo de panel Kanban. Intenté lograrlo…

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *