TripPin ส่วนที่ 6 - Schema

บทความ
05/17/2023

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

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

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

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

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

หมายเหตุ

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

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

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

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

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

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

ในผลลัพธ์ คุณจะเห็นสี่คอลัมน์ที่ส่งกลับ:

@odata.id
@odata.editLink
AirlineCode
ชื่อ

สายการบินที่ไม่มีสคีมา

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

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

Airlines Table.Schema

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

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

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

Column	รายละเอียด
ชื่อ	ชื่อของคอลัมน์ ซึ่งต้องตรงกับชื่อในผลลัพธ์ที่ส่งกลับโดยบริการ
ขนิด	ชนิดข้อมูล M ที่คุณกําลังจะตั้งค่า ซึ่งอาจเป็นชนิดดั้งเดิม (`text`, , `number`, `datetime`และอื่น ๆ) หรือเป็นชนิดโดยกําเนิด (`Int64.Type`, `Currency.Type`และอื่น ๆ)

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

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 อธิบายไว้ด้านล่างจะถูกใช้เพื่อบังคับใช้ schema กับข้อมูลของคุณ ซึ่งจะใช้พารามิเตอร์ต่อไปนี้:

พารามิเตอร์	ชนิด	คำอธิบาย
ตาราง	ตาราง	ตารางข้อมูลคุณจะต้องการบังคับใช้ Schema ของคุณ
สคีมา	ตาราง	ตาราง Schema สําหรับอ่านข้อมูลคอลัมน์จากชนิดต่อไปนี้: `type table [Name = text, Type = type]`
enforceSchema	ตัวเลข	(ไม่บังคับ) enum ที่ควบคุมลักษณะการทํางานของฟังก์ชัน ค่าเริ่มต้น (`EnforceSchema.Strict = 1`) ช่วยให้แน่ใจว่าตารางผลลัพธ์จะตรงกับตาราง Schema ที่ให้ไว้โดยการเพิ่มคอลัมน์ที่ขาดหายไปและลบคอลัมน์เพิ่มเติมออก สามารถใช้ `EnforceSchema.IgnoreExtraColumns = 2` ตัวเลือก เพื่อรักษาคอลัมน์เพิ่มเติมในผลลัพธ์ เมื่อใช้ `EnforceSchema.IgnoreMissingColumns = 3` ทั้งคอลัมน์ที่ขาดหายไปและคอลัมน์เพิ่มเติมจะถูกละเว้น

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

พิจารณาว่ามีคอลัมน์ใดหายไปจากตารางต้นทางหรือไม่
ตรวจสอบว่ามีคอลัมน์เพิ่มเติมใด ๆ หรือไม่
ละเว้นคอลัมน์ที่มีโครงสร้าง (ของชนิด list, record, และ table) และคอลัมน์ที่ตั้งค่าเป็นtype any
ใช้ Table.TransformColumnTypes เพื่อตั้งค่าชนิดคอลัมน์แต่ละชนิด
จัดลําดับคอลัมน์ใหม่ตามลําดับที่ปรากฏในตาราง Schema
ตั้งค่าชนิดบนตารางเองโดยใช้ Value.ReplaceType

หมายเหตุ

ขั้นตอนสุดท้ายในการตั้งค่าชนิดตารางจะลบความจําเป็นสําหรับ UI ของ Power Query เพื่ออนุมานข้อมูลชนิดเมื่อดูผลลัพธ์ในตัวแก้ไขคิวรี ซึ่งจะลบปัญหาการร้องขอสองชั้นที่คุณเห็นใน ตอนท้ายของบทช่วยสอนก่อนหน้า

คุณสามารถคัดลอกและวางรหัสผู้ช่วยเหลือต่อไปนี้ลงในส่วนขยายของคุณ:

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 ใหม่

กําหนดตาราง Schema หลัก (SchemaTable) ที่มีข้อกําหนดของ Schema ทั้งหมดของคุณ
TripPin.Feedอัปเดต , GetPageและ GetAllPagesByNextLink เพื่อยอมรับschemaพารามิเตอร์
บังคับใช้ Schema ของคุณในGetPage
อัปเดตรหัสตารางการนําทางของคุณเพื่อตัดแต่ละตารางด้วยการเรียกไปยังฟังก์ชันใหม่ (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 จริงจะดําเนินการในฟังก์ชันของคุณ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 คือสมมติว่าระเบียนทั้งหมดในรายการมีชุดเขตข้อมูลเดียวกัน ซึ่งสามารถทํางานได้กับบริการ TripPin เนื่องจากระเบียน OData มีการระบุให้ประกอบด้วยเขตข้อมูลเดียวกัน แต่อาจไม่เป็นกรณีสําหรับ REST API ทั้งหมด การใช้งานที่มีประสิทธิภาพมากขึ้นจะใช้การผสมผสานของ Table.FromList และ Table.ExpandRecordColumn บทช่วยสอนในภายหลังจะเปลี่ยนการใช้งานเพื่อรับรายการคอลัมน์จากตาราง Schema ตรวจสอบให้แน่ใจว่าไม่มีคอลัมน์ใดสูญหายหรือหายไปในระหว่างการแปล 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;

จากนั้นคุณจะอัปเดตฟังก์ชันของคุณ 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)

ตอนนี้คุณเห็นว่าตารางสายการบินของคุณมีสองคอลัมน์ที่คุณกําหนดไว้ใน schema เท่านั้น:

สายการบินที่มี Schema

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

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

คุณจะเห็นว่าชนิดโดยการกําหนดที่คุณใช้ (Int64.Type) ถูกตั้งค่าอย่างถูกต้อง

บุคคลที่มี Schema

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

บทสรุป

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

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

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

บุคคลที่ไม่มี Schema

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

บุคคลที่มี Schema

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

TripPin ส่วนที่ 7 - เค้าร่างขั้นสูงที่มีชนิด M

แชร์ผ่าน

TripPin ส่วนที่ 6 - Schema

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

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

ฟังก์ชันตัวช่วยเหลือ SchemaTransformTable

ปรับปรุงตัวเชื่อมต่อ TripPin

ตารางเค้าร่างหลัก

เพิ่มการสนับสนุน schema ไปยังฟังก์ชันข้อมูล

การบังคับใช้เค้าร่าง

การเพิ่มฟังก์ชัน GetEntity

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

บทสรุป

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

คำติชม

แหล่งทรัพยากรเพิ่มเติม