Compartir a través de


TripPin, parte 10: plegado básico de consultas

Nota:

Actualmente, este contenido hace referencia al contenido de una implementación heredada para los registros de Visual Studio. El contenido se actualizará en un futuro próximo para cubrir el nuevo SDK de Power Query en Visual Studio Code.

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 seguirse secuencialmente: cada lección se basa en el conector creado en las lecciones anteriores, agregando incrementalmente nuevas funcionalidades al conector.

En esta lección, aprenderá lo siguiente:

  • Información sobre los conceptos básicos del plegado de consultas
  • Más información sobre la función Table.View
  • Replique controladores de plegado de consultas de OData para:
  • $top
  • $skip
  • $count
  • $select
  • $orderby

Una de las características eficaces del lenguaje M es su capacidad de insertar trabajo de transformación en uno o más orígenes de datos subyacentes. Esta funcionalidad se conoce como Plegado de consultas (otras herramientas o tecnologías también hacen referencia a una función similar como Aplicación de predicado o Delegación de consultas).

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

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

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

Nota:

Como se indicó anteriormente, la función OData.Feed proporcionará automáticamente funcionalidades de plegado de consultas. Puesto que la serie TripPin trata el servicio de OData como una API REST normal, con Web.Contents en lugar de OData.Feed, deberá implementar los controladores de plegado de consultas usted mismo. Para el uso real, se recomienda usar OData.Feed siempre que sea posible.

Vaya a Información general sobre el plegado y la evaluación 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 proporcionará 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 volverá a su controlador predeterminado.

Cuando un conector personalizado usa una función que no admite el plegado de consultas implícito, como Web.Contents, los controladores de transformación predeterminados siempre se realizarán 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 firma siguiente:

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

La implementación ajustará 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 del origen de datos.

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

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, verá que el comportamiento de la función no ha cambiado. 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 usará como base de referencia: no implementa ninguna funcionalidad de plegado, pero tiene el scaffolding que necesitará para llevarla a cabo.

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, verá una función de contenedor adicional alrededor del registro handlers: Diagnostics.WrapHandlers Esta función auxiliar se encuentra en el módulo Diagnósticos (que se introdujo en la lección de adición de diagnósticos) y ofrece una manera útil de realizar un seguimiento automático de los errores generados por distintos controladores.

Las funciones GetType y GetRows se han actualizado para usar dos nuevas funciones auxiliares: CalculateSchema y CalculateUrl. En este momento, las implementaciones de esas funciones son bastante sencillas; observará que contienen partes de lo que hizo anteriormente la función GetEntity.

Por último, observará que está definiendo una función interna (View) que acepta un parámetro state. A medida que implemente más controladores, llamarán recursivamente a la función interna View, actualizando y pasando state mientras avancen.

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

Implementación del plegado de consultas

Dado que el motor de M revertirá automáticamente al procesamiento local cuando no se pueda plegar una consulta, se deben realizar algunos pasos adicionales para validar que los controladores de Table.View funcionen correctamente.

La manera manual de validar el comportamiento de plegado es observar mediante una herramienta como Fiddler las solicitudes de dirección URL que las pruebas unitarias realizan. Como alternativa, el registro de diagnóstico que agregó TripPin.Feed emitirá la dirección URL completa que se está ejecutando, que debe incluir los parámetros de cadena de consulta de OData que los controladores agregarán.

Una manera automatizada de validar el plegado de consultas consiste en forzar que la ejecución de pruebas unitarias falle si una consulta no se pliega por completo. Para ello, abra las propiedades del proyecto y establezca Error on Folding Failure (Error al fallar el plegado) en True (Verdadero). Con esta configuración habilitada, cualquier consulta que requiera el procesamiento local produce el siguiente error:

No ha sido posible plegar la expresión en el origen. Pruebe una expresión más sencilla.

Para probarlo, agregue un nuevo Fact al 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)
)

Nota:

La opción Error on Folding Failure (Error al fallar el plegado) es un enfoque de "todo o nada". Si desea probar consultas que no están diseñadas para plegarse como parte de las pruebas unitarias, deberá agregar alguna lógica condicional para habilitar o deshabilitar las pruebas en consecuencia.

Las secciones restantes de este tutorial agregarán un nuevo controlador Table.View. Deberá aplicar un enfoque de Desarrollo controlado por pruebas (TDD) en el que primero agregará pruebas unitarias con errores y, a continuación, implementará el código M para resolverlas.

Las siguientes secciones del controlador describirán 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 actualizará el registro state.
  • Modificar CalculateUrl para recuperar los valores de state y agregarlos a los parámetros de cadena de consulta y dirección URL.

Control de Table.FirstN con OnTake

El controlador OnTake recibe un parámetro count, 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.

Deberá usar 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 dos pruebas usan Table.FirstN para filtrar por el conjunto de resultados al primer número X de filas. Si ejecuta estas pruebas con Error on Folding Failure (Error al fallar la consulta) establecido en False (valor predeterminado), las pruebas deben realizarse correctamente, pero si ejecuta Fiddler (o comprueba los registros de seguimiento), observará que la solicitud que envía no contiene ningún parámetro de consulta de OData.

Seguimiento del diagnóstico.

Si establece Error on Folding Failure (Error al fallar la consulta) en True, las pruebas fallarán con el error Please try a simpler expression.. Para corregir el error, deberá definir el primer controlador Table.View para OnTake.

El controlador de OnTake es similar al código siguiente:

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

La función CalculateUrl se actualiza para extraer el valor Top del registro state 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

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

Seguimiento de diagnóstico con la parte superior.

Control de Table.Skip con OnSkip

El controlador OnSkip es muy parecido a OnTake. Recibe un parámetro count, que es el número de filas que se van a omitir del conjunto de resultados. Este controlador se traduce correctamente en el parámetro de consulta de 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 coincidentes con CalculateUrl:

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

Más información: 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 list de valores text, que representa una o varias columnas que se van a seleccionar.

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

La ventaja de plegar la selección de columnas se hace evidente cuando se trabaja con tablas con muchas columnas. El operador $select quitará las columnas no seleccionadas del conjunto de resultados, lo que dará 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 Table.FirstN para simplificar el caso de prueba.

Nota:

Si la prueba consistía simplemente en devolver los nombres de columna (con Table.ColumnNames) pero no datos, la solicitud al servicio OData nunca se enviará realmente. Esto se debe a que la llamada a GetType devolverá el esquema, que contiene toda la información que el motor M necesita para calcular el resultado.

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 controlador OnSelectColumns no tiene que preocuparse por esta opción: el motor de M la controlará automáticamente (es decir, las columnas que faltan no se incluirán en la lista columns).

Nota:

La otra opción para Table.SelectColumns, MissingField.UseNull, requiere un conector para implementar el controlador OnAddColumn. Esto se llevará a cabo en una lección posterior.

La implementación de OnSelectColumns lleva a cabo dos cosas:

  • Agrega la lista de columnas seleccionadas a state.
  • Vuelve a calcular el valor de Schema para que se 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 parámetro $select.

// 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 controlador OnSort recibe una lista de registros de tipo:

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

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

En términos de OData, esta operación se asignará a la opción de consulta $orderby. La sintaxis de $orderby 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 parámetro columns contiene más de un elemento, es importante mantener el orden en el 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,

Control de Table.RowCount con GetRowCount

A diferencia de los otros controladores de consulta que ha implementado, el controlador GetRowCount devolverá 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 inconveniente del enfoque del parámetro de consulta es que todavía es necesario enviar toda la consulta al servicio de OData. Dado que el recuento se devuelve en línea como parte del conjunto de resultados, tendrá que 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, probablemente aún implica más trabajo del que deseará hacer.

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

En este tutorial, ha implementado el controlador GetRowCount mediante el enfoque de segmento de ruta de acceso. Para evitar los errores que obtendría si se incluyen otros parámetros de consulta, ha comprobado si hay otros valores de estado y ha devuelto un "error no implementado" (...) si ha encontrado 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 revertirse al controlador predeterminado en su lugar (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 segmento de ruta de acceso /$count devuelve un valor único (en texto sin formato) en lugar de un conjunto de resultados JSON, también tendrá 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 anexar /$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 prueba unitaria Table.RowCount ahora debería superarse.

Para probar el caso de reserva, deberá añadir otra prueba que fuerza el error.

En primer lugar, agregue un método auxiliar que compruebe el resultado de una operación try para ver si hay 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 cuestión importante a tener en cuenta aquí es que esta prueba devuelve ahora un error si Error on Folding Error (Error al fallar el plegado) está establecido en false, ya que la operación Table.RowCount se revierte al controlador local (predeterminado). La ejecución de las pruebas con Error on Folding Error (Error al fallar el plegado) establecido en true provoca que Table.RowCount falle y permite que la prueba se realice correctamente.

Conclusión

La implementación de Table.View para el conector aumenta bastante la complejidad del código. Dado que el motor de M puede procesar todas las transformaciones localmente, al agregar controladores Table.View no se habilitan nuevos escenarios para los usuarios, pero se logra 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 de forma incremental nuevas funciones sin que ello afecte a la compatibilidad con versiones anteriores del conector.

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