TripPin 파트 6 - 스키마

이 다중 파트 자습서에서는 파워 쿼리에 대한 새 데이터 원본 확장의 생성에 대해 설명합니다. 이 자습서는 순차적으로 수행됩니다. 각 단원은 이전 단원에서 만든 커넥터를 기반으로 하여 커넥터에 새 기능을 증분 방식으로 추가합니다.

이 단원에서는 다음을 수행합니다.

표준 REST API에 비해 OData 서비스의 가장 큰 장점 중 하나는 $metadata 정의입니다. $metadata 문서에서는 모든 엔터티(테이블) 및 필드(열)에 대한 스키마를 포함하여 이 서비스에서 찾은 데이터를 설명합니다. 이 함수는 OData.Feed 이 스키마 정의를 사용하여 데이터 형식 정보를 자동으로 설정합니다. 따라서 모든 텍스트 및 숫자 필드를 가져오는 대신(예: Json.Document) 최종 사용자는 날짜, 정수, 시간 등을 가져와 전반적인 사용자 환경을 개선합니다.

대부분의 REST API는 해당 스키마를 프로그래밍 방식으로 확인할 방법이 없습니다. 이러한 경우 커넥터 내에 스키마 정의를 포함해야 합니다. 이 단원에서는 각 테이블에 대해 간단하고 하드 코딩된 스키마를 정의하고 서비스에서 읽은 데이터에 스키마를 적용합니다.

전반적으로 커넥터에서 반환된 데이터에 스키마를 적용하면 다음과 같은 여러 이점이 있습니다.

• 올바른 데이터 형식 설정
• 최종 사용자에게 표시할 필요가 없는 열 제거(예: 내부 ID 또는 상태 정보)
• 응답에서 누락될 수 있는 열을 추가하여 데이터의 각 페이지에 동일한 셰이프가 있는지 확인합니다(REST API가 필드를 null나타내는 일반적인 방법).

Table.Schema를 사용하여 기존 스키마 보기

이전 단원에서 만든 커넥터는 TripPin 서비스에서 AirlinesAirportsPeople세 개의 테이블을 표시합니다. 다음 쿼리를 실행하여 테이블을 봅니다 Airlines .

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

결과에서 네 개의 열이 반환됩니다.

• @odata.id
• @odata.editLink
• 항공사 코드
• 이름

"@odata.*" 열은 OData 프로토콜의 일부이며 커넥터의 최종 사용자에게 표시하거나 표시해야 하는 항목이 아닙니다. AirlineCode 및 Name는 유지하려는 두 개의 열입니다. 테이블의 스키마(편리한 Table.Schema 함수 사용)를 살펴보면 테이블의 모든 열에는 데이터 형식 Any.Type이 있습니다.

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

Table.Schema 는 이름, 위치, 형식 정보 및 Precision, Scale 및 MaxLength와 같은 많은 고급 속성을 포함하여 테이블의 열에 대한 많은 메타데이터를 반환합니다. 추후 수업에서는 이러한 고급 속성을 설정하기 위한 디자인 패턴을 제공하지만, 지금은 지정된 형식(TypeName), 기본 형식(Kind), 그리고 열 값이 null일 수 있는지 여부에만 IsNullable 유의해야 합니다.

간단한 스키마 테이블 정의

스키마 테이블은 다음 두 열로 구성됩니다.

테이블 Airlines에 대한 하드 코딩된 스키마 테이블은 AirlineCode 열과 Name 열을 text로 설정하며, 다음과 같습니다:

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

테이블에는 Airports 유지하려는 4개의 필드(형식 record중 하나 포함)가 있습니다.

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

마지막으로 People 테이블에는 7개의 필드가 있으며, 그 중 목록(Emails, AddressInfo), null 허용 열(Gender), 그리고 지정된 유형()을 가지는 열이 포함됩니다.

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

SchemaTransformTable 도우미 함수

SchemaTransformTable 이 섹션에서 설명하는 도우미 함수는 데이터에 스키마를 적용하는 데 사용됩니다. 다음 매개 변수를 사용합니다.

이 함수의 논리는 다음과 같습니다.

1. 원본 테이블에서 누락된 열이 있는지 확인합니다.
2. 추가 열이 있는지 확인합니다.
3. 구조화된 열(형식 list, record, 및table), 그리고 type any로 설정된 열을 무시합니다.
4. Table.TransformColumnTypes를 사용하여 각 열 형식을 설정합니다.
5. 스키마 테이블에 표시되는 순서에 따라 열의 순서를 다시 지정합니다.
6. Value.ReplaceType을 사용하여 테이블 자체의 형식을 설정합니다.

다음 도우미 코드를 복사하여 확장에 붙여넣을 수 있습니다.

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;

TripPin 커넥터 업데이트

새 스키마 적용 코드를 사용하려면 커넥터를 다음과 같이 변경합니다.

1. 모든 스키마 정의를 포함하는 마스터 스키마 테이블(SchemaTable)을 정의합니다.
2. TripPin.Feed, GetPage, 및 GetAllPagesByNextLink를 업데이트하여 schema 매개 변수를 수락하도록 합니다.
3. GetPage에서 스키마를 강제합니다.
4. 새 함수(GetEntity)에 대한 호출을 사용하여 각 테이블을 래핑하도록 탐색 테이블 코드를 업데이트합니다. 이 함수는 나중에 테이블 정의를 조작할 수 있는 유연성을 제공합니다.

마스터 스키마 테이블

이제 스키마 정의를 단일 테이블로 통합하고 엔터티 이름(GetSchemaForEntity예 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 &"'";

데이터 함수에 스키마 지원 추가

이제 , schema및 TripPin.Feed 함수에 GetPage선택적 GetAllPagesByNextLink 매개 변수를 추가합니다. 이 매개 변수를 사용하면 스키마(원하는 경우)를 페이징 함수에 전달할 수 있습니다. 이 매개 변수는 서비스에서 다시 가져오는 결과에 적용됩니다.

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 => ...

또한 스키마를 올바르게 통과하도록 이러한 함수에 대한 모든 호출을 업데이트해야 합니다.

스키마 적용

실제 스키마 적용은 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];

GetEntity 함수 추가

GetEntity 함수가 TripPin.Feed 호출을 래핑합니다. 엔터티 이름을 기반으로 스키마 정의를 조회하고 전체 요청 URL을 빌드합니다.

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;

TripPinNavTable 함수를 업데이트하여 모든 호출을 인라인으로 만드는 대신 GetEntity을 호출하도록 합니다. 이 업데이트의 주요 장점은 탐색 테이블 논리를 건드리지 않고도 엔터티 빌드 코드를 계속 수정할 수 있다는 것입니다.

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;

이 모든 것을 하나로 묶습니다.

모든 코드 변경이 완료되면 Airlines 테이블을 호출 Table.Schema 하는 테스트 쿼리를 컴파일하고 다시 실행합니다.

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

이제 Airlines 테이블에는 스키마에 정의한 두 개의 열만 있습니다.

People 테이블에 대해 동일한 코드를 실행하는 경우...

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

사용한 기록된 형식(Int64.Type)도 올바르게 설정되었습니다.

이 구현에서는 SchemaTransformTable 형식과 list 및 record 열의 타입을 수정하지 않지만, Emails 및 AddressInfo 열은 여전히 list 형식으로 지정된다는 점에 유의해야 합니다. 이 동작은 JSON 배열을 M 목록에 올바르게 매핑하고 JSON 개체를 M 레코드에 올바르게 매핑하기 때문에 Json.Document 발생합니다. 파워 쿼리에서 목록 또는 레코드 열을 확장하면 확장된 모든 열이 형식 any입니다. 이후 자습서에서는 중첩된 복합 형식에 대한 형식 정보를 재귀적으로 설정하도록 구현을 개선합니다.

결론

이 자습서에서는 REST 서비스에서 반환된 JSON 데이터에 스키마를 적용하기 위한 샘플 구현을 제공했습니다. 이 샘플에서는 간단한 하드 코딩된 스키마 테이블 형식을 사용하지만 JSON 스키마 파일 또는 데이터 원본에서 노출하는 메타데이터 서비스/엔드포인트와 같은 다른 원본에서 스키마 테이블 정의를 동적으로 빌드하여 접근 방식을 확장할 수 있습니다.

열 형식(및 값)을 수정하는 것 외에도 코드는 테이블 자체에 올바른 형식 정보를 설정합니다. 이 형식 정보를 설정하면 사용자 환경이 항상 최종 사용자에게 올바른 UI 큐를 표시하기 위해 형식 정보를 유추하려고 시도하고 유추 호출이 기본 데이터 API에 대한 다른 호출을 트리거할 수 있으므로 파워 쿼리 내에서 실행할 때 성능이 향상됩니다.

이전 단원의 TripPin 커넥터를 사용하여 People 테이블을 보는 경우 모든 열에는 '형식 임의' 아이콘(목록이 포함된 열도 있음)이 있습니다.

이 단원에서 TripPin 커넥터를 사용하여 동일한 쿼리를 실행하면 형식 정보가 올바르게 표시됩니다.

다음 단계

TripPin 7부 - M 형식의 고급 스키마