TripPin bagian 6 - Skema

Tutorial multi-bagian ini mencakup pembuatan ekstensi sumber data baru untuk Power Query. Tutorial ini dimaksudkan untuk dilakukan secara berurutan—setiap pelajaran dibangun pada konektor yang dibuat dalam pelajaran sebelumnya, secara bertahap menambahkan kemampuan baru ke konektor Anda.

Dalam pelajaran ini, Anda akan:

Salah satu keuntungan besar dari layanan OData daripada REST API standar adalah definisi $metadata. Dokumen $metadata menjelaskan data yang ditemukan pada layanan ini, termasuk skema untuk semua Entitasnya (Tabel) dan Bidang (Kolom). Fungsi ini OData.Feed menggunakan definisi skema ini untuk mengatur informasi jenis data secara otomatis—jadi alih-alih mendapatkan semua bidang teks dan angka (seperti yang Anda lakukan dari Json.Document), pengguna akhir mendapatkan tanggal, seluruh angka, waktu, dan sebagainya, memberikan pengalaman pengguna keseluruhan yang lebih baik.

Banyak REST API tidak memiliki cara untuk menentukan skema mereka secara terprogram. Dalam kasus ini, Anda harus menyertakan definisi skema dalam konektor Anda. Dalam pelajaran ini, Anda akan menentukan skema sederhana yang dikodekan secara permanen untuk setiap tabel Anda, dan menerapkan skema pada data yang Anda baca dari layanan.

Secara keseluruhan, memberlakukan skema pada data yang dikembalikan oleh konektor Anda memiliki beberapa manfaat, seperti:

• Mengatur jenis data yang benar
• Menghapus kolom yang tidak perlu ditampilkan kepada pengguna akhir (seperti ID internal atau informasi status)
• Memastikan bahwa setiap halaman data memiliki bentuk yang sama dengan menambahkan kolom apa pun yang mungkin hilang dari respons (cara umum bagi REST API untuk menunjukkan bidang harus null)

Menampilkan skema yang ada dengan Table.Schema

Konektor yang dibuat dalam pelajaran sebelumnya menampilkan tiga tabel dari layanan TripPin—Airlines, Airports, dan People. Jalankan kueri berikut untuk menampilkan Airlines tabel:

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

Dalam hasilnya, Anda akan melihat empat kolom yang dikembalikan:

• @odata.id
• @odata.editLink
• AirlineCode
• Nama

Kolom "@odata.*" adalah bagian dari protokol OData, dan bukan sesuatu yang ingin atau perlu Anda tampilkan kepada pengguna akhir konektor Anda. AirlineCode dan Name adalah dua kolom yang ingin Anda simpan. Jika Anda melihat skema tabel (menggunakan fungsi Table.Schema yang berguna), Anda dapat melihat bahwa semua kolom dalam tabel memiliki tipe Any.Typedata .

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

Table.Schema mengembalikan banyak metadata tentang kolom dalam tabel, termasuk nama, posisi, informasi jenis, dan banyak properti tingkat lanjut, seperti Presisi, Skala, dan MaxLength. Pelajaran di masa mendatang akan memberikan pola desain untuk mengatur properti lanjutan ini, tetapi untuk saat ini Anda hanya perlu khawatir dengan jenis yang ditulis (TypeName), jenis primitif (Kind), dan apakah nilai kolom mungkin null (IsNullable).

Menentukan tabel skema sederhana

Tabel skema Anda akan terdiri dari dua kolom:

Tabel skema yang dikodekan secara permanen untuk Airlines tabel mengatur AirlineCode kolom dan Name ke text, dan terlihat seperti ini:

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

Tabel Airports memiliki empat bidang yang ingin Anda simpan (termasuk salah satu jenis record):

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

Terakhir, People tabel memiliki tujuh bidang, termasuk daftar (Emails, AddressInfo), kolom yang dapat diubah ke null (Gender), dan kolom dengan jenis yang ditulis (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}
    })

Fungsi pembantu SchemaTransformTable

Fungsi pembantu SchemaTransformTable yang dijelaskan di bawah ini akan digunakan untuk memberlakukan skema pada data Anda. Prosedur ini membutuhkan parameter berikut:

Logika untuk fungsi ini terlihat seperti ini:

1. Tentukan apakah ada kolom yang hilang dari tabel sumber.
2. Tentukan apakah ada kolom tambahan.
3. Abaikan kolom terstruktur (dari jenis list, , recorddan table), dan kolom diatur ke type any.
4. Gunakan Table.TransformColumnTypes untuk mengatur setiap jenis kolom.
5. Urutkan ulang kolom berdasarkan urutan yang muncul dalam tabel skema.
6. Atur jenis pada tabel itu sendiri menggunakan Value.ReplaceType.

Kode pembantu berikut dapat disalin dan ditempelkan ke ekstensi Anda:

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;

Memperbarui konektor TripPin

Sekarang Anda akan membuat perubahan berikut pada konektor Anda untuk menggunakan kode penerapan skema baru.

1. Tentukan tabel skema master (SchemaTable) yang menyimpan semua definisi skema Anda.
2. TripPin.FeedPerbarui , GetPage, dan GetAllPagesByNextLink untuk menerima schema parameter.
3. Tertibkan skema Anda di GetPage.
4. Perbarui kode tabel navigasi Anda untuk membungkus setiap tabel dengan panggilan ke fungsi baru (GetEntity)—ini akan memberi Anda lebih banyak fleksibilitas untuk memanipulasi definisi tabel di masa mendatang.

Tabel skema master

Sekarang Anda akan mengonsolidasikan definisi skema Anda ke dalam satu tabel, dan menambahkan fungsi pembantu (GetSchemaForEntity) yang memungkinkan Anda mencari definisi berdasarkan nama entitas (misalnya, 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 &"'";

Menambahkan dukungan skema ke fungsi data

Sekarang Anda akan menambahkan parameter opsional schema ke TripPin.Feedfungsi , , GetPagedan GetAllPagesByNextLink . Ini akan memungkinkan Anda untuk meneruskan skema (ketika Anda ingin) ke fungsi halaman, di mana itu akan diterapkan ke hasil yang Anda dapatkan kembali dari layanan.

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

Anda juga akan memperbarui semua panggilan ke fungsi-fungsi ini untuk memastikan bahwa Anda meneruskan skema dengan benar.

Memberlakukan skema

Penegakan skema aktual akan dilakukan dalam fungsi Anda 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];

Menambahkan fungsi GetEntity

Fungsi ini GetEntity akan membungkus panggilan Anda ke TripPin.Feed. Ini akan mencari definisi skema berdasarkan nama entitas, dan membangun URL permintaan lengkap.

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;

Anda kemudian akan memperbarui fungsi Anda TripPinNavTable untuk memanggil GetEntity, daripada melakukan semua panggilan sebaris. Keuntungan utama dari ini adalah memungkinkan Anda terus memodifikasi kode bangunan entitas Anda, tanpa harus menyentuh logika tabel navigasi Anda.

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;

Merangkum semuanya

Setelah semua perubahan kode dibuat, kompilasi dan jalankan ulang kueri pengujian yang memanggil Table.Schema tabel Maskapai.

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

Anda sekarang melihat bahwa tabel Maskapai Anda hanya memiliki dua kolom yang Anda tentukan dalam skemanya:

Jika Anda menjalankan kode yang sama terhadap tabel Orang...

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

Anda akan melihat bahwa jenis langganan yang Anda gunakan (Int64.Type) juga diatur dengan benar.

Hal penting yang perlu diperhatikan adalah bahwa implementasi ini SchemaTransformTable tidak memodifikasi jenis list kolom dan record , tetapi Emails kolom dan AddressInfo masih di ketik sebagai list. Ini karena Json.Document akan memetakan array JSON dengan benar ke daftar M, dan objek JSON ke rekaman M. Jika Anda memperluas kolom daftar atau rekaman di Power Query, Anda akan melihat bahwa semua kolom yang diperluas akan berjenis apa pun. Tutorial di masa mendatang akan meningkatkan implementasi untuk mengatur informasi jenis secara rekursif untuk jenis kompleks berlapis.

Kesimpulan

Tutorial ini menyediakan implementasi sampel untuk memberlakukan skema pada data JSON yang dikembalikan dari layanan REST. Meskipun sampel ini menggunakan format tabel skema hardcoded sederhana, pendekatan dapat diperluas dengan membangun definisi tabel skema secara dinamis dari sumber lain, seperti file skema JSON, atau layanan/titik akhir metadata yang diekspos oleh sumber data.

Selain memodifikasi jenis kolom (dan nilai), kode Anda juga mengatur informasi jenis yang benar pada tabel itu sendiri. Mengatur performa manfaat informasi jenis ini saat berjalan di dalam Power Query, karena pengalaman pengguna selalu mencoba menyimpulkan informasi jenis untuk menampilkan antrean UI yang tepat kepada pengguna akhir, dan panggilan inferensi akhirnya dapat memicu panggilan lain ke API data yang mendasar.

Jika Anda melihat tabel Orang menggunakan konektor TripPin dari pelajaran sebelumnya, Anda akan melihat bahwa semua kolom memiliki ikon 'ketik apa pun' (bahkan kolom yang berisi daftar):

Menjalankan kueri yang sama dengan konektor TripPin dari pelajaran ini, Anda sekarang akan melihat bahwa informasi jenis ditampilkan dengan benar.

Langkah berikutnya

TripPin Bagian 7 - Skema Tingkat Lanjut dengan Jenis M