TripPin 6부 - 스키마
이 다중 파트 자습서에서는 파워 쿼리에 대한 새 데이터 원본 확장의 생성에 대해 설명합니다. 이 자습서는 순차적으로 수행됩니다. 각 단원은 이전 단원에서 만든 커넥터를 기반으로 하여 커넥터에 새 기능을 증분 방식으로 추가합니다.
이 단원에서는 다음을 수행합니다.
- REST API에 대한 고정 스키마 정의
- 열에 대한 데이터 형식을 동적으로 설정
- 열 누락으로 인한 변환 오류를 방지하기 위해 테이블 구조 적용
- 결과 집합에서 열 숨기기
표준 REST API에 비해 OData 서비스의 가장 큰 장점 중 하나는 $metadata 정의입니다. $metadata 문서에서는 모든 엔터티(테이블) 및 필드(열)에 대한 스키마를 포함하여 이 서비스에서 찾은 데이터를 설명합니다. 이 함수는 OData.Feed 이 스키마 정의를 사용하여 데이터 형식 정보를 자동으로 설정하므로 모든 텍스트 및 숫자 필드를 가져오는 대신(예: Json.Document) 최종 사용자는 날짜, 정수, 시간 등을 가져와 전반적인 사용자 환경을 개선합니다.
대부분의 REST API는 해당 스키마를 프로그래밍 방식으로 확인할 방법이 없습니다. 이러한 경우 커넥터 내에 스키마 정의를 포함해야 합니다. 이 단원에서는 각 테이블에 대해 간단하고 하드 코딩된 스키마를 정의하고 서비스에서 읽은 데이터에 스키마를 적용합니다.
참고 항목
여기에 설명된 접근 방식은 많은 REST 서비스에서 작동해야 합니다. 이후 단원 에서는 구조화된 열(레코드, 목록, 테이블)에 스키마를 재귀적으로 적용하여 이 방법을 기반으로 하며, CSDL 또는 JSON 스키마 문서에서 스키마 테이블을 프로그래밍 방식으로 생성할 수 있는 샘플 구현을 제공합니다.
전반적으로 커넥터에서 반환된 데이터에 스키마를 적용하면 다음과 같은 여러 이점이 있습니다.
- 올바른 데이터 형식 설정
- 최종 사용자에게 표시할 필요가 없는 열 제거(예: 내부 ID 또는 상태 정보)
- 응답에서 누락될 수 있는 열을 추가하여 데이터의 각 페이지에 동일한 셰이프가 있는지 확인합니다(REST API가 필드를 null이어야 함을 나타내는 일반적인 방법).
Table.Schema를 사용하여 기존 스키마 보기
이전 단원에서 만든 커넥터는 TripPin 서비스에서AirlinesAirports 세 개의 테이블을 표시합니다.People 다음 쿼리를 실행하여 테이블을 봅니다 Airlines .
let
source = TripPin.Contents(),
data = source{[Name="Airlines"]}[Data]
in
data
결과에는 다음 네 개의 열이 반환됩니다.
- @odata.id
- @odata.editLink
- AirlineCode
- 속성
"@odata.*" 열은 OData 프로토콜의 일부이며 커넥터의 최종 사용자에게 표시하거나 표시해야 하는 항목이 아닙니다. AirlineCodeName 은 유지하려는 두 열입니다. 테이블의 스키마(편리한 Table.Schema 함수 사용)를 살펴보면 테이블의 모든 열에 데이터 형식 Any.Type이 있는 것을 볼 수 있습니다.
let
source = TripPin.Contents(),
data = source{[Name="Airlines"]}[Data]
in
Table.Schema(data)
Table.Schema 는 이름, 위치, 형식 정보 및 Precision, Scale 및 MaxLength와 같은 많은 고급 속성을 포함하여 테이블의 열에 대한 많은 메타데이터를 반환합니다.
이후 단원에서는 이러한 고급 속성을 설정하기 위한 디자인 패턴을 제공하지만 지금은 지정된 형식(), 기본 형식Kind(TypeName) 및 열 값이 null(IsNullable)일 수 있는지 여부만 고려해야 합니다.
간단한 스키마 테이블 정의
스키마 테이블은 다음 두 개의 열로 구성됩니다.
| Column | 세부 정보 |
|---|---|
| 속성 | 열 이름입니다. 서비스에서 반환한 결과의 이름과 일치해야 합니다. |
| Type | 설정할 M 데이터 형식입니다. 기본 형식(text, number, datetime등) 또는 기록된 형식(Int64.Type, Currency.Type등)일 수 있습니다. |
테이블에 대한 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 는 목록(, AddressInfo), null 허용 열(Emails) 및 Ascribed 형식(Gender)이 있는 열을 포함하여 7개의 필드가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}
})
SchemaTransformTable 도우미 함수
SchemaTransformTable 아래에 설명된 도우미 함수는 데이터에 스키마를 적용하는 데 사용됩니다. 사용되는 매개 변수는 다음과 같습니다.
| 매개 변수 | 형식 | 설명 |
|---|---|---|
| table | table | 스키마를 적용할 데이터 테이블입니다. |
| schema(스키마) | table | 열 정보를 읽을 스키마 테이블입니다. 형식 type table [Name = text, Type = type]은 다음과 같습니다. |
| enforceSchema | 번호 | (선택 사항) 함수의 동작을 제어하는 열거형입니다. 기본값( EnforceSchema.Strict = 1)은 누락된 열을 추가하고 추가 열을 제거하여 출력 테이블이 제공된 스키마 테이블과 일치하도록 합니다. 이 EnforceSchema.IgnoreExtraColumns = 2 옵션은 결과에 추가 열을 유지하는 데 사용할 수 있습니다. 사용되는 경우 EnforceSchema.IgnoreMissingColumns = 3 누락된 열과 추가 열은 모두 무시됩니다. |
이 함수의 논리는 다음과 같습니다.
- 원본 테이블에서 누락된 열이 있는지 확인합니다.
- 추가 열이 있는지 확인합니다.
- 구조화된 열(형식
list,record및 ) 및table열이 로 설정된type any것을 무시합니다. - Table.TransformColumnTypes를 사용하여 각 열 형식을 설정합니다.
- 스키마 테이블에 표시되는 순서에 따라 열의 순서를 다시 지정합니다.
- Value.ReplaceType을 사용하여 테이블 자체의 형식을 설정합니다.
참고 항목
테이블 형식을 설정하는 마지막 단계에서는 쿼리 편집기에서 결과를 볼 때 파워 쿼리 UI에서 형식 정보를 유추할 필요가 없습니다. 이렇게 하면 이전 자습서의 끝에서 본 이중 요청 문제가 제거됩니다.
다음 도우미 코드를 복사하여 확장에 붙여넣을 수 있습니다.
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 커넥터 업데이트
이제 커넥터를 다음과 같은 변경하여 새 스키마 적용 코드를 사용합니다.
- 모든 스키마 정의를 포함하는 마스터 스키마 테이블(
SchemaTable)을 정의합니다. TripPin.Feed를GetPage업데이트하고GetAllPagesByNextLink매개 변수를schema수락합니다.- 에서 스키마를
GetPage적용합니다. - 새 함수(
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추가합니다.GetAllPagesByNextLink GetPage
이렇게 하면 스키마(원하는 경우)를 페이징 함수에 전달할 수 있으며, 여기서 서비스에서 다시 가져오는 결과에 적용됩니다.
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];
참고 항목
이 GetPage 구현에서는 Table.FromRecords를 사용하여 JSON 응답의 레코드 목록을 테이블로 변환합니다.
Table.FromRecords를 사용하는 경우의 주요 단점은 목록의 모든 레코드에 동일한 필드 집합이 있다고 가정한다는 것입니다.
OData 레코드가 동일한 필드를 포함하도록 보증되므로 TripPin 서비스에 대해 작동하지만 모든 REST API의 경우는 그렇지 않을 수 있습니다.
보다 강력한 구현은 Table.FromList 및 Table.ExpandRecordColumn의 조합을 사용합니다.
이후 자습서에서는 JSON에서 M으로 변환하는 동안 열이 손실되거나 누락되지 않도록 스키마 테이블에서 열 목록을 가져오기 위해 구현을 변경합니다.
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;
그런 다음 모든 호출을 인라인으로 만드는 대신 호출GetEntity하도록 함수를 업데이트 TripPinNavTable 합니다.
이 기본 장점은 탐색 테이블 논리를 건드리지 않고도 엔터티 빌드 코드를 계속 수정할 수 있다는 것입니다.
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 테이블에 스키마에 정의한 두 개의 열만 표시됩니다.
사람 테이블에 대해 동일한 코드를 실행하는 경우...
let
source = TripPin.Contents(),
data = source{[Name="People"]}[Data]
in
Table.Schema(data)
사용한 기록된 형식(Int64.Type)도 올바르게 설정된 것을 볼 수 있습니다.
이 구현은 열의 list 형식과 열을 수정하지 않지만 Emails 열과 AddressInfo record 열은 여전히 형식으로 list지정된다는 점에 유의 SchemaTransformTable 해야 합니다. 이는 JSON 배열을 M 목록에 올바르게 매핑하고 JSON 개체를 M 레코드에 올바르게 매핑하기 때문입니다 Json.Document . 파워 쿼리에서 목록 또는 레코드 열을 확장하려는 경우 확장된 모든 열의 형식이 모두 표시됩니다. 향후 자습서에서는 중첩된 복합 형식에 대한 형식 정보를 재귀적으로 설정하도록 구현을 개선합니다.
결론
이 자습서에서는 REST 서비스에서 반환된 JSON 데이터에 스키마를 적용하기 위한 샘플 구현을 제공했습니다. 이 샘플에서는 간단한 하드 코딩된 스키마 테이블 형식을 사용하지만 JSON 스키마 파일 또는 데이터 원본에서 노출하는 메타데이터 서비스/엔드포인트와 같은 다른 원본에서 스키마 테이블 정의를 동적으로 빌드하여 접근 방식을 확장할 수 있습니다.
열 형식(및 값)을 수정하는 것 외에도 코드는 테이블 자체에 올바른 형식 정보를 설정합니다. 이 형식 정보를 설정하면 사용자 환경이 항상 최종 사용자에게 올바른 UI 큐를 표시하기 위해 형식 정보를 유추하려고 시도하고 유추 호출이 기본 데이터 API에 대한 다른 호출을 트리거할 수 있으므로 파워 쿼리 내에서 실행할 때 성능이 향상됩니다.
이전 단원의 TripPin 커넥터를 사용하여 사람 테이블을 보면 모든 열에 '형식 임의' 아이콘(목록이 포함된 열도 있음)이 표시됩니다.
이 단원에서 TripPin 커넥터를 사용하여 동일한 쿼리를 실행하면 이제 형식 정보가 올바르게 표시되는 것을 볼 수 있습니다.
