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 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:

Una de las grandes ventajas de un servicio 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 OData.Feed función usa esta definición de esquema para establecer automáticamente la información del tipo de datos. Por lo tanto, 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 proporciona una mejor experiencia general del usuario.

Muchas API REST no tienen una manera de determinar mediante programación su esquema. En estos casos, debe incluir definiciones de esquema dentro del conector. En esta lección, definirá un esquema simple y predefinido para cada una de las tablas, y aplicará el esquema a los datos que lee del servicio.

En general, aplicar un esquema en los datos devueltos por el conector tiene varias ventajas, como:

• Establecimiento de los tipos de datos correctos
• Quitar columnas que no es necesario mostrar a los usuarios finales (como identificadores internos o información de estado)
• Asegurarse de que cada página de datos tiene la misma forma agregando las columnas que podrían faltar de una respuesta (una manera común de que las API REST indiquen 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, Airportsy People. Ejecute la consulta siguiente para ver la Airlines tabla:

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

En los resultados, se devuelven cuatro columnas:

• @odata.id
• @odata.editLink
• Código de aerolínea
• Nombre

Las columnas "@odata.*" forman parte del protocolo OData y no algo que quiera o necesite mostrar a los usuarios finales del conector. AirlineCode y Name son las dos columnas que desea conservar. Si observa el esquema de la tabla (con la función Table.Schema práctica), 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 muchos metadatos sobre las columnas de una tabla, incluidos nombres, posiciones, información de tipo y muchas propiedades avanzadas, como Precisión, Escala y MaxLength. Las lecciones futuras proporcionan patrones de diseño para establecer estas propiedades avanzadas, pero por ahora solo necesita preocuparse por el tipo descrito (), el tipo primitivo (TypeNameKind) y si el valor de columna puede ser null (IsNullable).

Definición de una tabla de esquema simple

La tabla de esquema se va a componer de dos columnas:

La tabla de esquema codificada para la tabla Airlines establece sus columnas AirlineCode y Name en text, y tiene el siguiente aspecto:

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

La Airports tabla tiene cuatro campos que desea 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 People tabla tiene siete campos, incluidas las listas (Emails, AddressInfo), una columna que acepta valores NULL (Gender) y una columna con un tipo descrito (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 SchemaTransformTable función auxiliar descrita en esta sección se usa para aplicar esquemas en los datos. Toma los parámetros siguientes:

La lógica de esta función tiene un aspecto similar al siguiente:

1. Determine si faltan columnas de la tabla de origen.
2. Determine si hay columnas adicionales.
3. Omita las columnas estructuradas (de tipo list, recordy table) y las columnas establecidas en type any.
4. Use Table.TransformColumnTypes para establecer cada tipo de columna.
5. Reordene 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.

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

Para usar el nuevo código de cumplimiento de esquemas, realice los siguientes cambios en el conector.

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

Tabla de esquema maestro

Ahora consolide las definiciones de esquema en una sola tabla y agregue una función auxiliar () que le permita buscar la definición basada en un nombre de entidad (GetSchemaForEntitypor 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 agregue un parámetro opcional schema a las TripPin.Feedfunciones , GetPagey GetAllPagesByNextLink . Este parámetro le permite pasar el esquema a las funciones de paginación cuando lo desee, donde se aplica a los resultados que se devuelven 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 debe actualizar todas las llamadas a estas funciones para asegurarse de pasar el esquema correctamente.

Aplicación del esquema

La aplicación real del esquema se realiza 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];

Adición de la función GetEntity

La función GetEntity envuelve la llamada a TripPin.Feed. Busca una definición de esquema basada en el nombre de la entidad y compila la dirección URL de 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, actualice su función TripPinNavTable para llamar a GetEntity, en lugar de realizar todas las llamadas insertadas. La principal ventaja de esta actualización 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;

Integrándolo todo

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

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

La tabla Airlines ahora solo tiene las dos columnas definidas en su esquema:

Si ejecutas el mismo código en la tabla Personas...

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

Tenga en cuenta que el tipo atribuido que usó (Int64.Type) también fue configurado correctamente.

Lo importante que hay que tener en cuenta es que esta implementación de SchemaTransformTable no modifica los tipos de list columnas y record , pero las Emails columnas y AddressInfo siguen tipadas como list. Este comportamiento se produce porque Json.Document asigna correctamente matrices JSON a listas M y objetos JSON a registros M. Si expande la columna de lista o registro en Power Query, todas las columnas expandidas son de tipo any. Los tutoriales futuros mejoran la implementación para establecer de forma recursiva información de tipos para tipos complejos anidados.

Conclusión

En este tutorial se proporciona una implementación de ejemplo para aplicar un esquema en los datos JSON devueltos desde un servicio REST. Aunque en este ejemplo se usa un formato de tabla de esquema codificado de forma sencilla, 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 modificar los 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 el rendimiento cuando se ejecuta dentro de Power Query, ya que la experiencia del usuario siempre intenta deducir 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, todas las columnas tienen un icono de 'tipo cualquiera' (incluso las columnas que contienen listas):

Si ejecuta la misma consulta con el conector TripPin desde esta lección, la información de tipo ahora se muestra correctamente.

Pasos siguientes

TripPin, parte 7: esquema avanzado con tipos M