แชร์ผ่าน

สคีมาการจัดการ

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

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

วิธีการฮาร์ดโค้ดอย่างง่าย

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

โดยรวมแล้ว การบังคับใช้ 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

Note

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

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

ตารางข้อมูลสายการบิน TripPin

คุณสามารถใช้ฟังก์ชันที่ 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 ที่นําไปใช้กับข้อมูลสายการบิน TripPin

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

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

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

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

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

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

เมื่อคุณดูจุดสิ้นสุดอื่นๆ ให้พิจารณาตาราง Schema ต่อไปนี้:

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

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

Peopleตารางมีเขตข้อมูลเจ็ดเขตข้อมูล ได้แก่ lists (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}
    })

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

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

ตารางสคีมา

ฟังก์ชันตัวช่วย SchemaTransformTable

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

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

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

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

Note

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

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

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

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

แนวทางที่ซับซ้อน

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

ต่อไปนี้เป็นการรีเฟรชอย่างรวดเร็วเกี่ยวกับชนิดในภาษา M จาก ข้อมูลจําเพาะของภาษา:

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

  • ประเภทดั้งเดิมซึ่งจําแนกค่าดั้งเดิม (binary, date, datetime, logicaldurationtypelisttimenullnumberrecorddatetimezonetext) และยังรวมถึงประเภทนามธรรมจํานวนหนึ่ง (function, table, any, และ )none
  • ชนิดของเรกคอร์ด ซึ่งจัดประเภทค่าเรกคอร์ดตามชื่อฟิลด์และชนิดค่า
  • ชนิดรายการ ซึ่งจัดประเภทรายการโดยใช้ชนิดพื้นฐานรายการเดียว
  • ชนิดฟังก์ชัน ซึ่งจําแนกค่าฟังก์ชันตามประเภทของพารามิเตอร์และค่าที่ส่งคืน
  • ชนิดตาราง ซึ่งจัดประเภทค่าตารางตามชื่อคอลัมน์ ชนิดคอลัมน์ และคีย์
  • ชนิด Nullable ซึ่งจัดประเภทค่า null นอกเหนือจากค่าทั้งหมดที่จําแนกตามชนิดพื้นฐาน
  • ชนิด ซึ่งจัดประเภทค่าที่เป็นชนิด

การใช้เอาต์พุต 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 การอ้างอิง และ CityTypeLocType เพื่อแสดงคอลัมน์ที่มีโครงสร้างอย่างไร

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

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 (ซึ่งคุณสามารถใช้เป็นตารางการค้นหาสําหรับการแม็ปเอนทิตีกับชนิด) เพื่อใช้ข้อกําหนดชนิดใหม่เหล่านี้:

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

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

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

Note

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

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

  • Last updated on