TripPin ตอนที่ 6 - Schema

บทช่วยสอนแบบหลายส่วนนี้ครอบคลุมการสร้างส่วนขยายแหล่งข้อมูลใหม่สําหรับ Power Query บทช่วยสอนมีไว้ให้ทําตามลําดับ—แต่ละบทเรียนจะสร้างขึ้นจากตัวเชื่อมต่อที่สร้างขึ้นในบทเรียนก่อนหน้า โดยจะเพิ่มความสามารถใหม่ให้กับตัวเชื่อมต่อของคุณทีละน้อย

ในบทเรียนนี้ คุณ:

ข้อได้เปรียบที่สําคัญอย่างหนึ่งของบริการ OData เหนือ REST API มาตรฐานคือคําจํากัดความ$metadata เอกสาร $metadata อธิบายข้อมูลที่พบในบริการนี้ รวมถึง Schema สําหรับเอนทิตี (ตาราง) และฟิลด์ (คอลัมน์) ทั้งหมด ฟังก์ชันนี้ OData.Feed ใช้ข้อกําหนด Schema นี้เพื่อตั้งค่าข้อมูลชนิดข้อมูลโดยอัตโนมัติ ดังนั้นแทนที่จะรับฟิลด์ข้อความและตัวเลขทั้งหมด (เหมือนที่คุณทํา) Json.Documentผู้ใช้ปลายทางจะได้รับวันที่ จํานวนเต็ม เวลา และอื่นๆ เพื่อมอบประสบการณ์ผู้ใช้โดยรวมที่ดีขึ้น

REST API จํานวนมากไม่มีวิธีกําหนดสคีมาโดยทางโปรแกรม ในกรณีเหล่านี้ คุณต้องรวมข้อกําหนด Schema ภายในตัวเชื่อมต่อของคุณ ในบทเรียนนี้ คุณจะกําหนด Schema แบบฮาร์ดโค้ดแบบง่ายสําหรับแต่ละตารางของคุณ และบังคับใช้ Schema กับข้อมูลที่คุณอ่านจากบริการ

โดยรวมแล้ว การบังคับใช้ Schema กับข้อมูลที่ส่งคืนโดยตัวเชื่อมต่อของคุณมีประโยชน์หลายประการ เช่น:

• การตั้งค่าประเภทข้อมูลที่ถูกต้อง
• การนําคอลัมน์ที่ไม่จําเป็นต้องแสดงต่อผู้ใช้ปลายทาง (เช่น รหัสภายในหรือข้อมูลสถานะ)
• ตรวจสอบให้แน่ใจว่าข้อมูลแต่ละหน้ามีรูปร่างเหมือนกันโดยการเพิ่มคอลัมน์ใดๆ ที่อาจขาดหายไปจากการตอบกลับ (วิธีทั่วไปสําหรับ REST API ในการระบุฟิลด์ควรเป็น null)

การดู Schema ที่มีอยู่ด้วย Table.Schema

ตัวเชื่อมต่อที่สร้างขึ้นในบทเรียนก่อนหน้านี้จะแสดงตารางสามตารางจากบริการ TripPin ดังนี้ Airlines, AirportsและPeople เรียกใช้แบบสอบถามต่อไปนี้เพื่อดู Airlines ตาราง:

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

ในผลลัพธ์ จะมีการส่งคืนสี่คอลัมน์:

• @odata.id
• @odata.editLink
• รหัสสายการบิน
• ชื่อ

คอลัมน์ "@odata.*" เป็นส่วนหนึ่งของโปรโตคอล OData และไม่ใช่สิ่งที่คุณต้องการหรือจําเป็นต้องแสดงต่อผู้ใช้ปลายทางของตัวเชื่อมต่อของคุณ AirlineCode และ Name สองคอลัมน์ที่คุณต้องการเก็บไว้ ถ้าคุณดู Schema ของตาราง (โดยใช้ฟังก์ชัน Table.Schema ที่มีประโยชน์) คอลัมน์ทั้งหมดในตารางจะมีชนิด Any.Typeข้อมูล

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

Table.Schema ส่งกลับข้อมูลเมตาจํานวนมากเกี่ยวกับคอลัมน์ในตาราง รวมถึงชื่อ ตําแหน่ง ข้อมูลชนิด และคุณสมบัติขั้นสูงมากมาย เช่น ความแม่นยํา มาตราส่วน และความยาวสูงสุด บทเรียนในอนาคตมีรูปแบบการออกแบบสําหรับการตั้งค่าคุณสมบัติขั้นสูงเหล่านี้ แต่สําหรับตอนนี้ คุณต้องกังวลกับชนิดที่กําหนด (TypeName) ชนิดดั้งเดิม (Kind) และค่าคอลัมน์อาจเป็น null (IsNullable) หรือไม่

การกําหนดตาราง Schema อย่างง่าย

ตาราง Schema ของคุณจะประกอบด้วยสองคอลัมน์:

ตาราง Schema แบบฮาร์ดโค้ดสําหรับตารางตั้งค่าAirlinestextคอลัมน์และAirlineCodeNameเป็น และมีลักษณะดังนี้:

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

Airportsตารางมีสี่ฟิลด์ที่คุณต้องการเก็บไว้ (รวมถึงหนึ่งประเภทrecord):

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

สุดท้าย People ตารางมีเขตข้อมูลเจ็ดเขตข้อมูล รวมถึงรายการ (Emails, AddressInfo) คอลัมน์ที่ ว่างได้ (Gender) และคอลัมน์ที่มี ชนิด ที่ระบุไว้ (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ฟังก์ชันตัวช่วยที่อธิบายไว้ในส่วนนี้ใช้เพื่อบังคับใช้สคีมากับข้อมูลของคุณ ใช้พารามิเตอร์ต่อไปนี้:

ตรรกะสําหรับฟังก์ชันนี้มีลักษณะดังนี้:

1. ตรวจสอบว่ามีคอลัมน์ที่ขาดหายไปจากตารางต้นฉบับหรือไม่
2. ตรวจสอบว่ามีคอลัมน์พิเศษหรือไม่
3. ละเว้นคอลัมน์ที่มีโครงสร้าง (ของชนิด list, record, และ table) และคอลัมน์ที่ตั้งค่าเป็นtype any
4. ใช้ Table.TransformColumnTypes เพื่อตั้งค่าแต่ละชนิดของคอลัมน์
5. จัดลําดับคอลัมน์ใหม่ตามลําดับที่ปรากฏในตาราง Schema
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

เมื่อต้องการใช้รหัสการบังคับใช้ Schema ใหม่ ให้ทําการเปลี่ยนแปลงต่อไปนี้กับตัวเชื่อมต่อของคุณ

1. กําหนดตาราง Schema หลัก (SchemaTable) ที่เก็บข้อกําหนด Schema ทั้งหมดของคุณ
2. อัปเดต TripPin.Feed, GetPageและ GetAllPagesByNextLink เพื่อยอมรับ schema พารามิเตอร์
3. บังคับใช้ Schema GetPageของคุณใน
4. อัปเดตโค้ดตารางการนําทางของคุณเพื่อตัดแต่ละตารางด้วยการเรียกฟังก์ชันใหม่ (GetEntity) ฟังก์ชันนี้ช่วยให้คุณมีความยืดหยุ่นมากขึ้นในการจัดการข้อกําหนดของตารางในอนาคต

ตารางสคีมาหลัก

ตอนนี้รวมข้อกําหนด Schema ของคุณไว้ในตารางเดียว และเพิ่มฟังก์ชันตัวช่วย (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 ให้กับฟังก์ชันข้อมูล

ตอนนี้เพิ่มพารามิเตอร์ที่ไม่บังคับschemaให้กับฟังก์ชัน TripPin.Feed, GetPageและGetAllPagesByNextLink พารามิเตอร์นี้ช่วยให้คุณสามารถส่งต่อ Schema (เมื่อคุณต้องการ) ไปยังฟังก์ชันเพจ ซึ่งจะถูกนําไปใช้กับผลลัพธ์ที่คุณได้รับกลับมาจากบริการ

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

คุณต้องอัปเดตการเรียกฟังก์ชันเหล่านี้ทั้งหมดเพื่อให้แน่ใจว่าคุณส่งผ่าน Schema อย่างถูกต้อง

การบังคับใช้ Schema

การบังคับใช้ Schema จริงจะทําในฟังก์ชันของคุณ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 ค้นหาข้อกําหนด Schema ตามชื่อเอนทิตี และสร้าง 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;

รวมทุกอย่างเข้าด้วยกัน

เมื่อทําการเปลี่ยนแปลงโค้ดทั้งหมดแล้ว ให้คอมไพล์และเรียกใช้แบบสอบถามทดสอบที่เรียก Table.Schema ตาราง Airlines อีกครั้ง

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

ตอนนี้ตาราง Airlines ของคุณมีเพียงสองคอลัมน์ที่คุณกําหนดไว้ใน Schema เท่านั้น

หากคุณเรียกใช้โค้ดเดียวกันกับตารางบุคคล...

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

โปรดทราบว่าประเภทที่ระบุไว้ที่คุณใช้ (Int64.Type) ก็ถูกตั้งค่าอย่างถูกต้องเช่นกัน

สิ่งสําคัญที่ควรทราบคือการใช้งานSchemaTransformTableนี้ไม่ได้ปรับเปลี่ยนชนิดของrecordlistคอลัมน์และ แต่คอลัมน์และEmailsAddressInfoยังคงพิมพ์เป็น list. ลักษณะการทํางานนี้เกิดขึ้นเนื่องจาก Json.Document การแมปอาร์เรย์ JSON กับรายการ M และวัตถุ JSON ไปยังระเบียน M อย่างถูกต้อง ถ้าคุณขยายคอลัมน์รายการหรือเรกคอร์ดใน Power Query คอลัมน์ที่ขยายทั้งหมดจะเป็นชนิดany บทช่วยสอนในอนาคตปรับปรุงการใช้งานเพื่อตั้งค่าข้อมูลชนิดซ้ําสําหรับชนิดที่ซับซ้อนที่ซ้อนกัน

บทสรุป

บทช่วยสอนนี้ให้ตัวอย่างการใช้งานสําหรับการบังคับใช้ Schema บนข้อมูล JSON ที่ส่งคืนจากบริการ REST แม้ว่าตัวอย่างนี้จะใช้รูปแบบตาราง Schema แบบฮาร์ดโค้ดอย่างง่าย แต่วิธีการนี้สามารถขยายได้โดยการสร้างข้อกําหนดตาราง Schema แบบไดนามิกจากแหล่งข้อมูลอื่น เช่น ไฟล์ Schema JSON หรือบริการข้อมูลเมตา/ปลายทางที่เปิดเผยโดยแหล่งข้อมูล

นอกเหนือจากการแก้ไขชนิดคอลัมน์ (และค่า) แล้ว โค้ดของคุณยังตั้งค่าข้อมูลชนิดที่ถูกต้องบนตารางด้วย การตั้งค่าข้อมูลชนิดนี้มีประโยชน์ต่อประสิทธิภาพเมื่อเรียกใช้ภายใน Power Query เนื่องจากประสบการณ์ของผู้ใช้จะพยายามอนุมานข้อมูลชนิดเพื่อแสดงคิว UI ที่ถูกต้องแก่ผู้ใช้ปลายทางเสมอ และการเรียกใช้การอนุมานสามารถจบลงด้วยการทริกเกอร์การเรียกอื่นๆ ไปยัง API ข้อมูลพื้นฐาน

หากคุณดูตารางบุคคลโดยใช้ ตัวเชื่อมต่อ TripPin จากบทเรียนก่อนหน้า คอลัมน์ทั้งหมดจะมีไอคอน 'พิมพ์ใดๆ ' (แม้แต่คอลัมน์ที่มีรายการ):

ถ้าคุณเรียกใช้คิวรีเดียวกันกับตัวเชื่อมต่อ TripPin จากบทเรียนนี้ ข้อมูลชนิดจะแสดงอย่างถูกต้อง

ขั้นตอนถัดไป

TripPin ตอนที่ 7 - สคีมาขั้นสูงพร้อมประเภท M