การจัดการ Schema

ทั้งนี้ขึ้นอยู่กับแหล่งข้อมูลของคุณ ข้อมูลเกี่ยวกับชนิดข้อมูลและชื่อคอลัมน์อาจหรืออาจไม่ระบุอย่างชัดเจน โดยทั่วไป OData REST API จะจัดการสิ่งนี้โดยใช้ข้อ$metadata และเมธอดPower Query จะจัดการการแยกวิเคราะห์ข้อมูลนี้และใช้กับข้อมูลที่ส่งกลับจากแหล่งข้อมูล OData.Feed ODataโดยอัตโนมัติ

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

วิธี Hardcoded อย่างง่าย

วิธีที่ง่ายที่สุดในการฮาร์ดโค้ดนิยาม schema ลงในตัวเชื่อมต่อของคุณ ซึ่งเพียงพอแล้วกับกรณีใช้งานส่วนใหญ่

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

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

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

พิจารณาโค้ดต่อไปนี้ที่ส่งกลับตารางอย่างง่ายจากบริการตัวอย่าง TripPin OData:

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

หมายเหตุ

TripPin คือแหล่งข้อมูล OData ดังนั้นในที่จริงแล้วจะเข้าใจได้มากกว่าการใช้ OData.Feed เพียงแค่การจัดการสคีมาแบบอัตโนมัติของฟังก์ชัน ในตัวอย่างนี้ คุณจะปฏิบัติต่อแหล่งข้อมูลในฐานะแหล่งข้อมูลทั่วไป REST API Web.Contents สาธิตเทคนิคการโค้ดแบบฮาร์ดโค้ด Schema ด้วยมือ

ตารางนี้เป็นผลลัพธ์:

ตารางข้อมูล TripPinPin

คุณสามารถใช้ฟังก์ชัน Table.Schema ที่มีประโยชน์เพื่อตรวจสอบชนิดข้อมูลของคอลัมน์ได้:

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

ผลลัพธ์ของ Table.Schema ที่ปรับใช้กับข้อมูลการตามการปักหมุดตามเวลา

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

การนิยามตาราง Schema อย่างง่าย

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

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

ตาราง hardcoded schema ของ Airlines ตารางจะตั้งค่า AirlineCode และ 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 มีเขตข้อมูลเจ็ดเขต รวมถึง list s ( , Emails AddressInfo ), คอลัมน์ที่สามารถเป็น nullable ( ) 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}
    })

คุณสามารถวางตารางเหล่านี้ทั้งหมดลงในตาราง Schema หลักตารางเดียว SchemaTable :

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

ตารางของสคีมา

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

ฟังก์ชัน SchemaTransformTable ผู้ช่วยเหลือ ที่อธิบายไว้ด้านล่างจะถูกใช้เพื่อบังคับใช้ schema กับข้อมูลของคุณ ใช้พารามิเตอร์ต่อไปนี้:

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

ตรรกะของฟังก์ชันนี้จะมีลักษณะดังนี้:

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

หมายเหตุ

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

รวมสิ่งทั้งหมดเข้าด้วยกัน

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

เนื่องจากการใช้งานตารางการแบ่งหน้าและการนําทางเป็นข้อมูลเฉพาะบริบท ตัวอย่างที่สมบูรณ์ของการใช้กลไกการจัดการ Schema แบบฮาร์ดโค้ดจะไม่แสดงที่นี่ ตัวอย่าง TripPin นี้แสดงให้เห็นว่าโซลูชันแบบ end-to-end อาจมีลักษณะอย่างไร

วิธีการที่ซับซ้อน

การใช้งาน hardcoded ที่กล่าวถึงข้างต้นนั้นสามารถดําเนินการได้ที่ดีเพื่อให้แน่ใจว่า Schema ยังคงสอดคล้องกันกับพนักงาน JSON แบบง่าย แต่ถูกจํากัดให้แยกวิเคราะห์ระดับแรกของการตอบสนอง ชุดข้อมูลที่ซ้อนกันเชิงลึกจะได้รับประโยชน์จากวิธีการต่อไปนี้ ซึ่งใช้ประโยชน์จาก M Types

นี่คือการรีเฟรชด่วนเกี่ยวกับชนิดในภาษา M จากข้อมูล ระบุภาษา:

ค่าชนิด คือค่าที่ใช้ในการ จัดประเภท ค่าอื่น ๆ ค่าที่จัดประเภทตามชนิดจะกล่าวได้ว่า สอดคล้องกับ ชนิดนั้น ระบบชนิดในภาษา M ประกอบด้วยประเภทของชนิดดังต่อไปนี้:

  • ชนิด Primitive ซึ่งจัดประเภทค่าแรกร่วม ( , , , , , , ) และยังรวมถึงจํานวน binary date datetime datetimezone duration list logical null number record text time type ของประเภทนามธรรม ( function , , และ table any none )
  • ชนิด Record ซึ่งจัดประเภทค่าเรกคอร์ดโดยยึดตามชื่อเขตข้อมูลและชนิดค่า
  • ชนิด List ซึ่งจัดประเภทรายการโดยใช้ชนิดฐานของหน่วยข้อมูลเดียว
  • ชนิด Function ซึ่งจัดประเภทค่าฟังก์ชันที่ยึดตามชนิดของพารามิเตอร์และค่าส่งกลับ
  • ชนิด Table ซึ่งจัดประเภทค่าตารางโดยยึดตามชื่อคอลัมน์ ชนิดคอลัมน์ และคีย์
  • ชนิด Nullable ซึ่งจัดประเภท null ของค่านอกเหนือจากค่าทั้งหมดที่จัดประเภทโดยชนิดฐาน
  • ชนิด Type ซึ่งจัดประเภทค่าที่เป็นชนิด

เมื่อใช้ผลลัพธ์ JSON ดิบคุณได้รับ (และ/หรือโดยค้นหาข้อกําหนดในบริการ $metadata)คุณสามารถกําหนดชนิดของระเบียนต่อไปนี้เพื่อแสดงประเภทที่ซับซ้อนของ 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
];

สังเกตว่า LocationType วิธีการอ้างอิง CityType และ LocType เพื่อแสดงคอลัมน์ที่มีโครงสร้าง

ในเอนทิตีระดับบนสุดที่คุณต้องการจะแสดงเป็น ตาราง คุณสามารถกําหนดชนิด ตาราง:

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
];

จากนั้นคุณสามารถอัปเดต SchemaTable ตัวแปรของคุณ (ซึ่งคุณสามารถใช้เป็นตารางการค้นหาการแมปแบบ entity-to-type) เพื่อใช้นิยามประเภทใหม่เหล่านี้:

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

คุณสามารถใช้ฟังก์ชันทั่วไป ( Table.ChangeType ) เพื่อบังคับใช้ schema กับข้อมูลของคุณ เหมือนกับที่คุณใช้ SchemaTransformTable ในแบบฝึกหัดก่อนหน้านี้ ซึ่ง SchemaTransformTable Table.ChangeType แตกต่างจากจะใช้ชนิดตาราง M จริงเป็นอาร์กิวเมนต์ และจะใช้ Schema ของคุณซ้้้า กับชนิดที่ซ้อนกันทั้งหมด ลายเซ็นคือ:

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

หมายเหตุ

เพื่อความยืดหยุ่น คุณสามารถใช้ฟังก์ชันในตารางและรายการของเรกคอร์ดได้ (ซึ่งเป็นวิธีแสดงตารางในเอกสาร JSON)

จากนั้นคุณจะต้องอัปเดตรหัสตัวเชื่อมต่อเพื่อเปลี่ยนพารามิเตอร์ schema จาก table เป็น type และเพิ่มการเรียก Table.ChangeType ไปยัง อีกครั้ง รายละเอียดในการดําเนินการมีข้อมูลเฉพาะในการดําเนินการมาก และดังนั้นจึงไม่มีค่าที่จะลงในรายละเอียดที่นี่ ตัวอย่างตัวเชื่อมต่อ TripPin ที่ขยายนี้ สาธิตโซลูชันแบบ end-to-end ซึ่งใช้วิธีการที่ซับซ้อนมากขึ้นในการจัดการ Schema