Leer en inglés

Compartir a través de


Control de esquemas

Según el origen de datos, es posible que se brinde o no información explícita sobre los tipos de datos y los nombres de columnas. Por lo general, las API REST de OData lo controlan a través de la definición de metadatosy el método Power Query OData.Feed controla automáticamente el análisis de la información y lo aplica a los datos que devuelve un Origen de OData.

Muchas API REST no cuentan con una manera de determinar el esquema de forma programática. En estos casos, se deberá incluir una definición de esquema en el conector.

Enfoque codificado de forma sencilla

El enfoque más sencillo es codificar una definición de esquema en el conector. Esto es suficiente para la mayoría de los casos de uso.

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 que los campos deben ser NULL y omitirse por completo).

Visualizar el esquema existente con Table.Schema

Observe el código siguiente que devuelve una tabla sencilla del servicio de ejemplo De TripPin OData:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    asTable

Nota

TripPin es un origen de OData, por lo que en realidad tendría más sentido simplemente usar el control automático de esquemas de la función OData.Feed. En este ejemplo, se tratará el origen como una API REST típica y se usará Web.Contents para demostrar la técnica de codificación manual de un esquema.

El resultado es esta tabla:

Tabla de datos de TripPin Airline.

Se puede usar la función útil Table.Schema para verificar el tipo de datos de las columnas:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    Table.Schema(asTable)

Resultado de Table.Schema aplicado a los datos de TripPin Airline.

AirlineCode y Name son de tipo any. Table.Schema devuelve una gran cantidad de metadatos acerca de las columnas de una tabla: nombres, posiciones, información de tipo y otras propiedades avanzadas, como Precisión, Escala y MaxLength. De momento, solo hay que preocuparse por el tipo asignado (TypeName), el tipo primitivo (Kind) y si el valor de columna fuera NULL (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 (texto, número, fecha y hora, etc.) o un tipo asignado (Int64.Type, Currency.Type, etc.).

La tabla de esquema codificada para la tabla Airlines establecerá las columnas AirlineCode y Name en text y se verá así:

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

Cuando se busquen los otros puntos de conexión, se deben considerar las siguientes tablas de esquema:

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}
    })

La tabla People tiene siete campos, que incluyen list(Emails, AddressInfo), una columna que acepta valores NULL (Gender), y una columna con un tipo definido (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}
    })

Puede colocar todas las tablas en una única tabla de esquema maestro SchemaTable :

SchemaTable = #table({"Entity", "SchemaTable"}, {
        {"Airlines", Airlines},
        {"Airports", Airports},
        {"People", People}
    })

Esquemas de tabla.

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, recordy table) y las columnas establecidas en el tipo any.
  4. Utilice 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 tabla mediante Value.ReplaceType.

Nota

El último paso para establecer el tipo de tabla eliminará la necesidad de que la interfaz de usuario de Power Query infiera información de tipos al ver los resultados en el editor de consultas; esto a veces puede generar una doble llamada a la API.

Resumen

En el contexto de una extensión completa, el control de esquemas ocurrirá cuando se devuelva una tabla desde la API. Habitualmente esta funcionalidad ocurre en el nivel más bajo de la función paginación (si existe), con información de entidad que se pasa desde una tabla de navegación.

Como gran parte de la implementación de tablas de paginación y navegación es específica del contexto, aquí no se mostrará el ejemplo completo de implementación de un mecanismo de control de esquemas codificados. Este ejemplo de TripPin muestra el aspecto que tendría una solución completa.

Enfoque sofisticado

La implementación codificada que se mostró más arriba realiza un buen trabajo al asegurarse de que los esquemas sean coherentes con las respuestas JSON simples, pero se limita al análisis del primer nivel de la respuesta. Los conjuntos de datos profundamente anidados se beneficiarían del enfoque que se describe a continuación, que utiliza los tipos M.

Esta es una actualización rápida de los tipos en el lenguaje M de la Especificación del lenguaje:

Un valor de tipo es un valor que clasifica otros valores. Se dice que un valor que está clasificado por un tipo se ajusta a ese tipo. El sistema de tipos de M consta de los tipos siguientes:

  • Tipos primitivos, que clasifican valores primitivos (binary, date, datetime, datetimezone, duration, list, logical, null, number, record, text, time, type) e incluyen también una serie de tipos abstractos (function, table, any y none).
  • Tipos de registro, que clasifican valores de registro que se basan en nombres de campo y tipos de valor.
  • Tipos de lista, que clasifican listas mediante un tipo base de un solo elemento.
  • Tipos de función, que clasifican valores de función según los tipos de sus parámetros y los valores devueltos.
  • Tipos de tabla, que clasifican valores de tabla de acuerdo a nombres de columna, tipos de columna y claves.
  • Tipos que admiten valores NULL, que clasifican el valor null y todos los valores que se han clasificado por un tipo base.
  • Tipos de tipo, que clasifican valores que son tipos.

Con la salida JSON sin procesar que se obtiene (o mediante la búsqueda de las definiciones en los metadatos del servicio ), se pueden definir los siguientes tipos de registro para representar tipos complejos de OData:

LocationType = type [
    Address = text,
    City = CityType,
    Loc = LocType
];

CityType = type [
    CountryRegion = text,
    Name = text,
    Region = text
];

LocType = type [
    #"type" = text,
    coordinates = {number},
    crs = CrsType
];

CrsType = type [
    #"type" = text,
    properties = record
];

Observe cómo LocationType hace referencia a CityType y LocType para representar las columnas estructuradas.

Para las entidades de nivel superior que se desee representar como Tablas, es posible definir tipos de tabla:

AirlinesType = type table [
    AirlineCode = text,
    Name = text
];
AirportsType = type table [
    Name = text,
    IataCode = text,
    Location = LocationType
];
PeopleType = type table [
    UserName = text,
    FirstName = text,
    LastName = text,
    Emails = {text},
    AddressInfo = {nullable LocationType},
    Gender = nullable text,
    Concurrency  Int64.Type
];

Entonces se puede actualizar la variable SchemaTable (que se puede usar como tabla de búsqueda para las asignaciones de entidad a tipo) para usar las nuevas definiciones de tipo:

SchemaTable = #table({"Entity", "Type"}, {
    {"Airlines", AirlinesType},
    {"Airports", AirportsType},
    {"People", PeopleType}
});

Es posible utilizar una función común (Table.ChangeType) para aplicar un esquema en los datos, tal como se utilizó SchemaTransformTable en el ejercicio anterior. A diferencia de SchemaTransformTable, Table.ChangeType toma un tipo de tabla M real como argumento y aplicará el esquema de forma recursiva para todos los tipos anidados. Su firma es:

Table.ChangeType = (table, tableType as type) as nullable table => ...

Nota

Para mayor flexibilidad, la función se puede usar en tablas, así como en listas de registros (como se representan las tablas en un documento JSON).

A continuación, se deberá actualizar el código del conector para cambiar el parámetro schema de table a typey añadir una llamada a Table.ChangeType. Vale la pena repetir que los detalles para hacerlo son muy específicos de la implementación y, por lo tanto, no merece la pena entrar en detalle aquí. En este ejemplo extendido del conector TripPin se muestra una solución completa que implementa este enfoque más sofisticado para controlar el esquema.