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 esperadotable type
de la consulta.GetRows
: devuelve el resultado realtable
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 destate
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.
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.
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 parámetro de consulta $count, que devuelve el recuento como un campo independiente en el conjunto de resultados.
- El segmento de ruta de acceso /$count, que devolverá solo el recuento total, como un valor escalar.
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.