TripPin, parte 6: esquema

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:

• Definición de un esquema fijo para una API REST
• Establecimiento dinámico de tipos de datos para las columnas
• Aplicación de una estructura de tabla para evitar errores de transformación debido a que faltan columnas
• Ocultación de columnas del conjunto de resultados

Una de las grandes ventajas de un servicio de OData a través de una API REST estándar es su definición de $metadata. En el documento $metadata se describen los datos encontrados en este servicio, incluido el esquema de todas sus entidades (tablas) y campos (columnas). La función OData.Feed usa esta definición de esquema para establecer automáticamente la información del tipo de datos, por lo que, en lugar de obtener todos los campos de texto y número (como lo haría desde Json.Document), los usuarios finales obtienen fechas, números enteros, horas, etc., lo que brinda una mejor experiencia general del usuario.

Muchas API REST no cuentan con una manera de determinar el esquema de forma programática. En estos casos, se deberán incluir definiciones de esquema en el conector. En esta lección, definirá un esquema simple y codificado de forma rígida para cada una de las tablas y aplicará el esquema en los datos que leyó del servicio.

Nota:

El enfoque que se describe aquí debería funcionar para muchos servicios REST. En próximas lecciones se adoptará este enfoque con la aplicación recursiva de esquemas en columnas estructuradas (registro, lista, tabla) y se proporcionarán implementaciones de ejemplo que pueden generar mediante programación una tabla de esquema a partir de documentos de esquema CSDL o JSON.

En general, la aplicación de un esquema en los datos que devuelve el conector tiene varias ventajas, como:

• Establecer los tipos de datos correctos
• Eliminar las columnas que no deben mostrarse a los usuarios finales (como identificadores internos o información de estado)
• Garantizar que cada página de datos tenga la misma forma al añadir las columnas que podrían faltar en una respuesta (las API REST suelen indicar así que un campo debe ser NULL).

Visualización del esquema existente con Table.Schema

El conector creado en la lección anterior muestra tres tablas del servicio TripPin: Airlines, Airports y People. Ejecute la siguiente consulta para ver la tabla Airlines:

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    data

En los resultados verá que se devuelven cuatro columnas:

• @odata.id
• @odata.editLink
• AirlineCode
• Nombre

Las columnas "@odata.*" forman parte del protocolo OData y no es algo que quiera ni deba mostrar a los usuarios finales del conector. AirlineCode y Name son las dos columnas que querrá conservar. Si observa el esquema de la tabla (con la práctica función Table.Schema), verá que todas las columnas de la tabla tienen un tipo de datos de Any.Type.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Table.Schema devuelve numerosos metadatos acerca de las columnas de una tabla: nombres, posiciones, información de tipo y otras propiedades avanzadas, como Precisión, Escala y MaxLength. En próximas lecciones se proporcionarán patrones de diseño para establecer estas propiedades avanzadas, pero por ahora solo tiene que preocuparse por el tipo atribuido (TypeName), el tipo primitivo (Kind) y si el valor de columna puede ser nulo (IsNullable).

Definición de una tabla de esquema sencillo

La tabla de esquema está formada por dos columnas:

Columna	Detalles
Nombre	El nombre de la columna. Esto debe coincidir con el nombre que figura en los resultados que haya devuelto el servicio.
Tipo	Tipo de datos M que se van a configurar. Puede ser un tipo primitivo (text, number, datetime, etc.) o un tipo atribuido (Int64.Type, Currency.Type, etc.).

La tabla de esquema codificada para la tabla Airlines establece las columnas AirlineCode y Name en text y se ve así:

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    });

La tabla Airports tiene cuatro campos que se recomienda conservar (incluido uno de tipo record):

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    });

Por último, la tabla People tiene siete campos, incluidas las listas (Emails, AddressInfo), una columna que acepta valores NULL (Gender) y una columna con un tipo atribuido (Concurrency).

People = #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}
    })

La función auxiliar SchemaTransformTable

La función auxiliar SchemaTransformTable que se describe a continuación se usará para aplicar esquemas en los datos. Toma los parámetros siguientes:

Parámetro	Tipo	Descripción
table	table	La tabla de datos en la que querrá aplicar el esquema.
esquema	table	La tabla de esquema desde la que se va a leer la información de la columna, con el siguiente tipo: type table [Name = text, Type = type].
enforceSchema	number	(opcional) Una enumeración que controla el comportamiento de la función. El valor predeterminado (EnforceSchema.Strict = 1) garantiza que la tabla de salida se corresponda con la tabla de esquema que se proporcionó al añadir las columnas que faltan y eliminar las columnas adicionales. La opción EnforceSchema.IgnoreExtraColumns = 2 se puede usar para conservar columnas adicionales en el resultado. Cuando se use EnforceSchema.IgnoreMissingColumns = 3 se omitirán las columnas que faltan y las columnas adicionales.

La lógica de esta función es similar a lo que se muestra a continuación:

1. Especifique si faltan columnas de la tabla de origen.
2. Especifique si hay columnas adicionales.
3. Omita las columnas estructuradas (de tipo list, record y table) y las columnas establecidas en type any.
4. Use Table.TransformColumnTypes para establecer cada tipo de columna.
5. Vuelva a ordenar las columnas según el orden en que aparecen en la tabla de esquema.
6. Establezca el tipo en la propia tabla mediante Value.ReplaceType.

Nota:

El último paso para establecer el tipo de tabla evitará la necesidad de que la interfaz de usuario de Power Query infiera información de tipo al ver los resultados en el editor de consultas. De esta manera se evita el problema de la solicitud doble que vio al final del tutorial anterior.

El código auxiliar siguiente se puede copiar y pegar en la extensión:

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2;   // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
    let
        // Default to EnforceSchema.Strict
        _enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

        // Applies type transforms to a given table
        EnforceTypes = (table as table, schema as table) as table =>
            let
                map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
                mapped = Table.TransformColumns(schema, {"Type", map}),
                omitted = Table.SelectRows(mapped, each [Type] <> null),
                existingColumns = Table.ColumnNames(table),
                removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
                primativeTransforms = Table.ToRows(removeMissing),
                changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
            in
                changedPrimatives,

        // Returns the table type for a given schema
        SchemaToTableType = (schema as table) as type =>
            let
                toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
                toRecord = Record.FromList(toList, schema[Name]),
                toType = Type.ForRecord(toRecord, false)
            in
                type table (toType),

        // Determine if we have extra/missing columns.
        // The enforceSchema parameter determines what we do about them.
        schemaNames = schema[Name],
        foundNames = Table.ColumnNames(table),
        addNames = List.RemoveItems(schemaNames, foundNames),
        extraNames = List.RemoveItems(foundNames, schemaNames),
        tmp = Text.NewGuid(),
        added = Table.AddColumn(table, tmp, each []),
        expanded = Table.ExpandRecordColumn(added, tmp, addNames),
        result = if List.IsEmpty(addNames) then table else expanded,
        fullList =
            if (_enforceSchema = EnforceSchema.Strict) then
                schemaNames
            else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
                foundNames
            else
                schemaNames & extraNames,

        // Select the final list of columns.
        // These will be ordered according to the schema table.
        reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
        enforcedTypes = EnforceTypes(reordered, schema),
        withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
    in
        withType;

Actualización del conector TripPin

Ahora realizará los siguientes cambios en el conector para usar el nuevo código de aplicación de esquemas.

1. Defina una tabla de esquema maestro (SchemaTable) que contenga todas las definiciones de esquema.
2. Actualice TripPin.Feed, GetPage y GetAllPagesByNextLink para que acepten un parámetro schema.
3. Aplique el esquema en GetPage.
4. Actualice el código de la tabla de navegación para ajustar cada tabla con una llamada a una nueva función (GetEntity), lo que le proporcionará más flexibilidad para manipular las definiciones de tabla en el futuro.

Tabla de esquema maestro

Ahora consolidará las definiciones de esquema en una sola tabla y agregará una función auxiliar (GetSchemaForEntity) que le permite buscar la definición en función de un nombre de entidad (por ejemplo, GetSchemaForEntity("Airlines")).

SchemaTable = #table({"Entity", "SchemaTable"}, {
    {"Airlines", #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    })},    
    
    {"Airports", #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    })},

    {"People", #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}
    })}
});

GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";

Adición de compatibilidad con esquemas a funciones de datos

Ahora agregará un parámetro opcional schema a las funciones TripPin.Feed, GetPage y GetAllPagesByNextLink. Esto le permitirá pasar el esquema (cuando lo desee) a las funciones de paginación, donde se aplicará a los resultados que obtenga del servicio.

TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...

También deberá actualizar todas las llamadas a estas funciones para asegurarse de pasar el esquema correctamente.

Aplicación del esquema

La aplicación del esquema real se realizará en la función GetPage.

GetPage = (url as text, optional schema as table) as table =>
    let
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[value]),
        // enforce the schema
        withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
    in
        withSchema meta [NextLink = nextLink];

Nota:

Esta implementación de GetPage usa Table.FromRecords para convertir la lista de registros en la respuesta JSON a una tabla. Un inconveniente importante del uso de Table.FromRecords es que supone que todos los registros de la lista tienen el mismo conjunto de campos. Esto funciona para el servicio TripPin, ya que está garantizado que los registros de OData contienen los mismos campos, pero es posible que esto no sea así para las demás API REST. Una implementación más sólida usaría una combinación de Table.FromList y Table.ExpandRecordColumn. En tutoriales posteriores se cambiará la implementación para obtener la lista de columnas de la tabla de esquema, lo que garantiza que no se pierdan ni falten columnas durante la traducción de JSON a M.

Adición de la función GetEntity

La función GetEntity ajustará la llamada a TripPin.Feed. Buscará una definición de esquema en función del nombre de la entidad y creará la dirección URL de la solicitud completa.

GetEntity = (url as text, entity as text) as table => 
    let
        fullUrl = Uri.Combine(url, entity),
        schemaTable = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schemaTable)
    in
        result;

A continuación, se deberá actualizar la función TripPinNavTable para llamar a GetEntity en lugar de realizar todas las llamadas insertadas. La principal ventaja que ofrece esto es que permite seguir modificando el código de creación de entidades, sin tener que tocar la lógica de la tabla de navegación.

TripPinNavTable = (url as text) as table =>
    let
        entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
        rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

Resumen

Una vez realizados todos los cambios de código, compile y vuelva a ejecutar la consulta de prueba que llama a Table.Schema para la tabla Airlines.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Ahora verá que la tabla Airlines solo tiene las dos columnas definidas en su esquema:

Si ejecuta el mismo código sobre la tabla People...

let
    source = TripPin.Contents(),
    data = source{[Name="People"]}[Data]
in
    Table.Schema(data)

Verá que el tipo atribuido que usó (Int64.Type) también se ha establecido correctamente.

Es importante tener en cuenta que esta implementación de SchemaTransformTable no modifica los tipos de columnas list y record, pero las columnas Emails y AddressInfo siguen teniendo como tipo list. Esto se debe a que Json.Document asignará correctamente matrices JSON a listas M y objetos JSON a registros M. Si desea expandir la columna de lista o de registro en Power Query, verá que todas las columnas expandidas serán del tipo any (cualquiera). En próximos tutoriales se mejorará la implementación para establecer de forma recursiva la información de tipos para los tipos complejos anidados.

Conclusión

En este tutorial se proporciona una implementación de ejemplo para aplicar un esquema en los datos JSON devueltos de un servicio REST. Aunque en este ejemplo se usa un formato de tabla de esquema simple codificado de forma rígida, el enfoque podría expandirse mediante la creación dinámica de una definición de tabla de esquema desde otro origen, como un archivo de esquema JSON, o un servicio o punto de conexión de metadatos expuesto por el origen de datos.

Además de la modificación de tipos de columna (y valores), el código también establece la información de tipo correcta en la propia tabla. Establecer esta información de tipo beneficia al rendimiento cuando se ejecuta dentro de Power Query, ya que la experiencia del usuario siempre intenta inferir información de tipos para mostrar las colas de interfaz de usuario correctas al usuario final y las llamadas de inferencia pueden acabar desencadenando otras llamadas a las API de datos subyacentes.

Si ve la tabla People mediante el conector TripPin de la lección anterior, verá que todas las columnas tienen un icono "type any" (tipo cualquiera) (incluso las columnas que contienen listas):

Al ejecutar la misma consulta con el conector TripPin de esta lección, verá que la información de tipo se muestra correctamente.

Pasos siguientes

TripPin, parte 7: esquema avanzado con tipos M