TripPin, parte 10: plegado de consultas básicas

En este tutorial de varias partes se describe la creación de una nueva extensión de origen de datos para Power Query. El tutorial está diseñado para realizarse secuencialmente: cada lección se basa en el conector creado en las lecciones anteriores, agregando incrementalmente nuevas funcionalidades al conector.

En esta lección:

• $top
• $skip
• $count
• $select
• $orderby

Una de las características eficaces del lenguaje M es su capacidad de insertar el trabajo de transformación en uno o varios orígenes de datos subyacentes. Esta capacidad se denomina Plegado de consultas (otras herramientas o tecnologías también se refieren a una función similar como Pushdown de predicado o Delegación de consultas).

Al crear un conector personalizado que usa una función M con funcionalidades de plegado de consultas integradas, como OData.Feed o Odbc.DataSource, el conector hereda automáticamente esta funcionalidad de forma gratuita.

En este tutorial se replica el comportamiento integrado de plegado de consultas para OData mediante la implementación de controladores de funciones para la función Table.View . Esta parte del tutorial implementa algunos de los controladores más fáciles de implementar (es decir, los que no requieren el análisis de expresiones y el seguimiento de estado).

Para obtener más información sobre las funcionalidades de consulta que podría ofrecer un servicio OData, vaya a Convenciones de dirección URL de OData v4.

Vaya a Información general sobre la evaluación de consultas y el plegado de consultas en Power Query para obtener más información sobre el plegado de consultas.

Uso de Table.View

La función Table.View permite que un conector personalizado invalide los controladores de transformación predeterminados para el origen de datos. Una implementación de Table.View proporciona una función para uno o varios de los controladores admitidos. Si un controlador no está implementado o devuelve un error durante la evaluación, el motor de M vuelve a su controlador predeterminado.

Cuando un conector personalizado usa una función que no admite el plegado implícito de consultas, como Web.Contents, los controladores de transformación predeterminados siempre se realizan localmente. Si la API REST a la que se conecta admite parámetros de consulta como parte de la consulta, Table.View le permite agregar optimizaciones que permiten insertar el trabajo de transformación en el servicio.

La función Table.View tiene la siguiente firma:

Table.View(table as nullable table, handlers as record) as table

La implementación envuelve la función principal del origen de datos. Hay dos controladores necesarios para Table.View:

• GetType: devuelve el resultado esperado table type de la consulta.
• GetRows: devuelve el resultado real table de la función de origen de datos.

La implementación más sencilla sería similar al ejemplo siguiente:

TripPin.SuperSimpleView = (url as text, entity as text) as table =>
    Table.View(null, [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => GetEntity(url, entity)
    ]);

Actualice la función TripPinNavTable para llamar a TripPin.SuperSimpleView en lugar de GetEntity.

withData = Table.AddColumn(rename, "Data", each TripPin.SuperSimpleView(url, [Name]), type table),

Si vuelve a ejecutar las pruebas unitarias, no se cambia el comportamiento de la función. En este caso, la implementación de Table.View simplemente pasa la llamada a GetEntity. Puesto que aún no ha implementado ningún controlador de transformación , el parámetro original url permanece intacto.

Implementación inicial de Table.View

La implementación anterior de Table.View es sencilla, pero no muy útil. La siguiente implementación se usa como base de referencia: no implementa ninguna funcionalidad de plegado, pero tiene el scaffolding que necesita para hacerlo.

TripPin.View = (baseUrl as text, entity as text) as table =>
    let
        // Implementation of Table.View handlers.
        //
        // We wrap the record with Diagnostics.WrapHandlers() to get some automatic
        // tracing if a handler returns an error.
        //
        View = (state as record) => Table.View(null, Diagnostics.WrapHandlers([
            // Returns the table type returned by GetRows()
            GetType = () => CalculateSchema(state),

            // Called last - retrieves the data from the calculated URL
            GetRows = () => 
                let
                    finalSchema = CalculateSchema(state),
                    finalUrl = CalculateUrl(state),

                    result = TripPin.Feed(finalUrl, finalSchema),
                    appliedType = Table.ChangeType(result, finalSchema)
                in
                    appliedType,

            //
            // Helper functions
            //
            // Retrieves the cached schema. If this is the first call
            // to CalculateSchema, the table type is calculated based on
            // the entity name that was passed into the function.
            CalculateSchema = (state) as type =>
                if (state[Schema]? = null) then
                    GetSchemaForEntity(entity)
                else
                    state[Schema],

            // Calculates the final URL based on the current state.
            CalculateUrl = (state) as text => 
                let
                    urlWithEntity = Uri.Combine(state[Url], state[Entity])
                in
                    urlWithEntity
        ]))
    in
        View([Url = baseUrl, Entity = entity]);

Si observa la llamada a Table.View, hay una función contenedora adicional en torno al registro handlers—Diagnostics.WrapHandlers. Esta función auxiliar se encuentra en el módulo Diagnósticos (introducida en la lección de adición de diagnóstico) y proporciona una manera útil de realizar un seguimiento automático de los errores generados por controladores individuales.

Las GetType funciones y GetRows se actualizan para usar dos nuevas funciones auxiliares:CalculateSchema y CalculateUrl. En este momento, las implementaciones de esas funciones son bastante sencillas. Observe que contienen partes de lo que ha hecho anteriormente la GetEntity función .

Por último, observe que va a definir una función interna (View) que acepta un state parámetro. A medida que implementa más controladores, llaman recursivamente a la función interna View, actualizando y pasando state a medida que avanzan.

Actualice la TripPinNavTable función una vez más, reemplazando la llamada a TripPin.SuperSimpleView por una llamada a la nueva TripPin.View función y vuelva a ejecutar las pruebas unitarias. Todavía no hay ninguna nueva funcionalidad, pero ahora tiene una línea base sólida para las pruebas.

Implementación del plegado de consultas

Dado que el motor de M vuelve automáticamente al procesamiento local cuando no se puede plegar una consulta, debe realizar algunos pasos adicionales para validar que los controladores Table.View funcionan correctamente.

La manera manual de validar el comportamiento de plegado es ver las solicitudes de dirección URL que realizan las pruebas unitarias mediante una herramienta como Fiddler. Como alternativa, el registro de diagnóstico que agregó para TripPin.Feed emita la dirección URL completa en ejecución, debe incluir los parámetros de cadena de consulta de OData que agregan los controladores.

Una manera automatizada de validar el plegado de consultas consiste en forzar que la ejecución de pruebas unitarias produzca un error si una consulta no se plega por completo. Para hacer que la prueba unitaria falle cuando una consulta no se pliega completamente, abra las propiedades del proyecto y establezca Error on Folding Failure a True. Con esta configuración habilitada, cualquier consulta que requiera el procesamiento local produce el siguiente error:

We couldn't fold the expression to the source. Please try a simpler expression.

Para probar este cambio, agregue un nuevo Fact archivo de prueba unitaria que contenga una o varias transformaciones de tabla.

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
)

Las secciones restantes de este tutorial agregan un nuevo controlador Table.View . Va a tomar un enfoque de desarrollo controlado por pruebas (TDD), donde primero agrega pruebas unitarias con errores y, a continuación, implementa el código M para resolverlos.

En las secciones siguientes del controlador se describe la funcionalidad proporcionada por el controlador, la sintaxis de consulta equivalente de OData, las pruebas unitarias y la implementación. Con el código de scaffolding descrito anteriormente, cada implementación del controlador requiere dos cambios:

• Agregar el controlador a Table.View que actualiza el state registro.
• Modificar CalculateUrl para recuperar los valores de state y agregarlos a los parámetros de cadena de consulta y dirección URL.

Gestión de Table.FirstN con OnTake

El OnTake controlador recibe un count parámetro, que es el número máximo de filas que se van a tomar de GetRows. En términos de OData, puede traducirlo al parámetro de consulta $top .

Usted usa las siguientes pruebas unitarias:

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
),
Fact("Fold $top 0 on Airports", 
    #table( type table [Name = text, IataCode = text, Location = record] , {} ), 
    Table.FirstN(Airports, 0)
),

Estas pruebas utilizan Table.FirstN para filtrar el conjunto de resultados a las primeras X filas. Si ejecuta estas pruebas con Error al plegar configurado en False (el valor predeterminado), las pruebas deben tener éxito, pero si ejecuta Fiddler (o verifica los registros de seguimiento), verá que la solicitud que envía no contiene ningún parámetro de consulta de OData.

Si configura Error al fallar el doblado en True, las pruebas fallan con el error Please try a simpler expression.. Para corregir este error, debe definir el primer controlador Table.View para OnTake.

El OnTake controlador tiene el siguiente aspecto:

OnTake = (count as number) =>
    let
        // Add a record with Top defined to our state
        newState = state & [ Top = count ]
    in
        @View(newState),

La CalculateUrl función se actualiza para extraer el Top valor del state registro y establecer el parámetro correcto en la cadena de consulta.

// Calculates the final URL based on the current state.
CalculateUrl = (state) as text => 
    let
        urlWithEntity = Uri.Combine(state[Url], state[Entity]),

        // Uri.BuildQueryString requires that all field values
        // are text literals.
        defaultQueryString = [],

        // Check for Top defined in our state
        qsWithTop =
            if (state[Top]? <> null) then
                // add a $top field to the query string record
                defaultQueryString & [ #"$top" = Number.ToText(state[Top]) ]
            else
                defaultQueryString,

        encodedQueryString = Uri.BuildQueryString(qsWithTop),
        finalUrl = urlWithEntity & "?" & encodedQueryString
    in
        finalUrl

Cuando vuelva a ejecutar las pruebas unitarias, observe que la dirección URL a la que accede ahora contiene el $top parámetro . Debido a la codificación de direcciones URL, $top aparece como %24top, pero el servicio OData es lo suficientemente inteligente como para convertirlo automáticamente.

Manejo de Table.Skip con OnSkip

El OnSkip controlador es muy parecido a OnTake. Recibe un count parámetro , que es el número de filas que se van a omitir del conjunto de resultados. Este controlador es compatible con el parámetro de consulta OData $skip.

Pruebas unitarias:

// OnSkip
Fact("Fold $skip 14 on Airlines",
    #table( type table [AirlineCode = text, Name = text] , {{"EK", "Emirates"}} ), 
    Table.Skip(Airlines, 14)
),
Fact("Fold $skip 0 and $top 1",
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ),
    Table.FirstN(Table.Skip(Airlines, 0), 1)
),

Implementación:

// OnSkip - handles the Table.Skip transform.
// The count value should be >= 0.
OnSkip = (count as number) =>
    let
        newState = state & [ Skip = count ]
    in
        @View(newState),

Actualizaciones que coinciden con CalculateUrl:

qsWithSkip = 
    if (state[Skip]? <> null) then
        qsWithTop & [ #"$skip" = Number.ToText(state[Skip]) ]
    else
        qsWithTop,

Para obtener más información, vaya a Table.Skip.

Control de Table.SelectColumns con OnSelectColumns

Se llama al controlador OnSelectColumns cuando el usuario selecciona o quita columnas del conjunto de resultados. El controlador recibe una lista de valores listtext, representando una o varias columnas para seleccionar.

En términos de OData, esta operación se asigna a la opción de consulta $select .

La ventaja de las opciones plegables de selección de columnas resulta evidente cuando se trabaja con tablas con muchas columnas. El $select operador quita las columnas no seleccionadas del conjunto de resultados, lo que da lugar a consultas más eficaces.

Pruebas unitarias:

// OnSelectColumns
Fact("Fold $select single column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode"}), 1)
),
Fact("Fold $select multiple column", 
    #table( type table [UserName = text, FirstName = text, LastName = text],{{"russellwhyte", "Russell", "Whyte"}}), 
    Table.FirstN(Table.SelectColumns(People, {"UserName", "FirstName", "LastName"}), 1)
),
Fact("Fold $select with ignore column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode", "DoesNotExist"}, MissingField.Ignore), 1)
),

Las dos primeras pruebas seleccionan distintos números de columnas con Table.SelectColumns e incluyen una llamada a Table.FirstN para simplificar el caso de prueba.

La tercera prueba usa la opción MissingField.Ignore , que indica al motor M que omita las columnas seleccionadas que no existan en el conjunto de resultados. El OnSelectColumns controlador no necesita preocuparse por esta opción: el motor de M lo controla automáticamente (es decir, las columnas que faltan no se incluyen en la columns lista).

La implementación de OnSelectColumns hace dos cosas:

• Agrega la lista de columnas seleccionadas a .state
• Recalcula el Schema valor para que pueda establecer el tipo de tabla correcto.

OnSelectColumns = (columns as list) =>
    let
        // get the current schema
        currentSchema = CalculateSchema(state),
        // get the columns from the current schema (which is an M Type value)
        rowRecordType = Type.RecordFields(Type.TableRow(currentSchema)),
        existingColumns = Record.FieldNames(rowRecordType),
        // calculate the new schema
        columnsToRemove = List.Difference(existingColumns, columns),
        updatedColumns = Record.RemoveFields(rowRecordType, columnsToRemove),
        newSchema = type table (Type.ForRecord(updatedColumns, false))
    in
        @View(state & 
            [ 
                SelectColumns = columns,
                Schema = newSchema
            ]
        ),

CalculateUrl se actualiza para recuperar la lista de columnas del estado y combinarlas (con un separador) para el $select parámetro .

// Check for explicitly selected columns
qsWithSelect =
    if (state[SelectColumns]? <> null) then
        qsWithSkip & [ #"$select" = Text.Combine(state[SelectColumns], ",") ]
    else
        qsWithSkip,

Control de Table.Sort con OnSort

El OnSort controlador recibe una lista de registros de tipo:

type [ Name = text, Order = Int16.Type ]

Cada registro contiene un Name campo, que indica el nombre de la columna y un Order campo que es igual a Order.Ascending o Order.Descending.

En términos de OData, esta operación se asigna a la opción de consulta $orderby . La $orderby sintaxis tiene el nombre de columna seguido de asc o desc para indicar el orden ascendente o descendente. Al ordenar en varias columnas, los valores se separan con una coma. Si el columns parámetro contiene más de un elemento, es importante mantener el orden en que aparecen.

Pruebas unitarias:

// OnSort
Fact("Fold $orderby single column",
    #table( type table [AirlineCode = text, Name = text], {{"TK", "Turkish Airlines"}}),
    Table.FirstN(Table.Sort(Airlines, {{"AirlineCode", Order.Descending}}), 1)
),
Fact("Fold $orderby multiple column",
    #table( type table [UserName = text], {{"javieralfred"}}),
    Table.SelectColumns(Table.FirstN(Table.Sort(People, {{"LastName", Order.Ascending}, {"UserName", Order.Descending}}), 1), {"UserName"})
)

Implementación:

// OnSort - receives a list of records containing two fields: 
//    [Name]  - the name of the column to sort on
//    [Order] - equal to Order.Ascending or Order.Descending
// If there are multiple records, the sort order must be maintained.
//
// OData allows you to sort on columns that do not appear in the result
// set, so we do not have to validate that the sorted columns are in our 
// existing schema.
OnSort = (order as list) =>
    let
        // This will convert the list of records to a list of text,
        // where each entry is "<columnName> <asc|desc>"
        sorting = List.Transform(order, (o) => 
            let
                column = o[Name],
                order = o[Order],
                orderText = if (order = Order.Ascending) then "asc" else "desc"
            in
                column & " " & orderText
        ),
        orderBy = Text.Combine(sorting, ", ")
    in
        @View(state & [ OrderBy = orderBy ]),

Actualizaciones de CalculateUrl:

qsWithOrderBy = 
    if (state[OrderBy]? <> null) then
        qsWithSelect & [ #"$orderby" = state[OrderBy] ]
    else
        qsWithSelect,

Gestión de Table.RowCount con GetRowCount

A diferencia de los otros controladores de consulta que está implementando, el GetRowCount controlador devuelve un valor único, el número de filas esperadas en el conjunto de resultados. En una consulta M, este valor normalmente sería el resultado de la transformación Table.RowCount .

Tiene algunas opciones diferentes sobre cómo controlar este valor como parte de una consulta de OData:

• El parámetro de consulta $count, que devuelve el recuento como un campo independiente en el conjunto de resultados.
• El segmento de la ruta /$count, que devuelve solo el recuento total, como valor escalar.

El inconveniente del enfoque del parámetro de consulta es que todavía necesita enviar toda la consulta al servicio OData. Dado que el recuento vuelve en línea como parte del conjunto de resultados, debe procesar la primera página de datos del conjunto de resultados. Aunque este proceso sigue siendo más eficaz que leer todo el conjunto de resultados y contar las filas, es probable que siga siendo más trabajo que lo que desea hacer.

La ventaja del enfoque del segmento de ruta de acceso es que solo se recibe un único valor escalar en el resultado. Este enfoque hace que toda la operación sea mucho más eficaz. Sin embargo, como se describe en la especificación de OData, el segmento de ruta de acceso /$count devuelve un error si incluye otros parámetros de consulta, como $top o $skip, lo que limita su utilidad.

En este tutorial, implementó el GetRowCount controlador mediante el enfoque de segmento de ruta. Para evitar los errores que obtiene si se incluyen otros parámetros de consulta, ha comprobado otros valores de estado y devolvió un "error no implementado" (...) si encontró alguno. Si se devuelve cualquier error de un controlador Table.View , se indica al motor M que la operación no se puede plegar y que debe volver al controlador predeterminado (que en este caso contaría el número total de filas).

En primer lugar, agregue una prueba unitaria:

// GetRowCount
Fact("Fold $count", 15, Table.RowCount(Airlines)),

Dado que el /$count segmento de ruta de acceso devuelve un valor único (en formato de texto simple) en lugar de un conjunto de resultados JSON, también tiene que agregar una nueva función interna (TripPin.Scalar) para realizar la solicitud y controlar el resultado.

// Similar to TripPin.Feed, but is expecting back a scalar value.
// This function returns the value from the service as plain text.
TripPin.Scalar = (url as text) as text =>
    let
        _url = Diagnostics.LogValue("TripPin.Scalar url", url),

        headers = DefaultRequestHeaders & [
            #"Accept" = "text/plain"
        ],

        response = Web.Contents(_url, [ Headers = headers ]),
        toText = Text.FromBinary(response)
    in
        toText;

A continuación, la implementación usa esta función (si no se encuentra ningún otro parámetro de consulta en state):

GetRowCount = () as number =>
    if (Record.FieldCount(Record.RemoveFields(state, {"Url", "Entity", "Schema"}, MissingField.Ignore)) > 0) then
        ...
    else
        let
            newState = state & [ RowCountOnly = true ],
            finalUrl = CalculateUrl(newState),
            value = TripPin.Scalar(finalUrl),
            converted = Number.FromText(value)
        in
            converted,

La función CalculateUrl se actualiza para agregar /$count a la dirección URL si el campo RowCountOnly está establecido en state.

// Check for $count. If all we want is a row count,
// then we add /$count to the path value (following the entity name).
urlWithRowCount =
    if (state[RowCountOnly]? = true) then
        urlWithEntity & "/$count"
    else
        urlWithEntity,

La nueva Table.RowCount prueba unitaria ahora debería superarse.

Para probar el caso de reserva, agregue otra prueba que fuerza el error.

En primer lugar, agregue un método auxiliar que compruebe el resultado de una try operación para un error de plegado.

// Returns true if there is a folding error, or the original record (for logging purposes) if not.
Test.IsFoldingError = (tryResult as record) =>
    if ( tryResult[HasError]? = true and tryResult[Error][Message] = "We couldn't fold the expression to the data source. Please try a simpler expression.") then
        true
    else
        tryResult;

A continuación, agregue una prueba que use Table.RowCount y Table.FirstN para forzar el error.

// test will fail if "Fail on Folding Error" is set to false
Fact("Fold $count + $top *error*", true, Test.IsFoldingError(try Table.RowCount(Table.FirstN(Airlines, 3)))),

Una nota importante aquí es que esta prueba ahora devuelve un error si Error on Folding Error está establecido en false, porque la operación Table.RowCount recurre al controlador local (predeterminado). Ejecutar las pruebas con Error en Error de Plegado configurado en true hace que Table.RowCount falle y permite que la prueba finalmente se realice correctamente.

Conclusión

La implementación de Table.View para el conector agrega una cantidad significativa de complejidad al código. Dado que el motor de M puede procesar todas las transformaciones localmente, agregar controladores Table.View no habilita nuevos escenarios para los usuarios, pero da como resultado un procesamiento más eficaz (y potencialmente, usuarios más felices). Una de las principales ventajas de que los controladores Table.View sean opcionales es que permite agregar nuevas funcionalidades de forma incremental sin afectar a la compatibilidad con versiones anteriores de su conector.

Para la mayoría de los conectores, un controlador importante (y básico) que se va a implementar es OnTake (lo que se traduce en $top en OData), ya que limita el número de filas devueltas. La experiencia de Power Query siempre realiza una OnTake de 1000 filas al mostrar vistas previas en el navegador y el editor de consultas, por lo que los usuarios podrían ver mejoras de rendimiento significativas al trabajar con conjuntos de datos más grandes.