TripPin bölüm 6 - Şema

Bu çok bölümlü öğretici, Power Query için yeni bir veri kaynağı uzantısı oluşturmayı kapsar. Öğreticinin sırayla yapılması amaçlanır; her ders önceki derslerde oluşturulan bağlayıcı üzerinde oluşturulur ve bağlayıcınıza artımlı olarak yeni özellikler ekler.

Bu derste şunları yapacaksınız:

OData hizmetinin standart REST API'ye kıyasla en büyük avantajlarından biri, $metadata tanımıdır. $metadata belge, tüm Varlıkları (Tablolar) ve Alanlar (Sütunlar) şeması dahil olmak üzere bu hizmette bulunan verileri açıklar. OData.Feed İşlev, veri türü bilgilerini otomatik olarak ayarlamak için bu şema tanımını kullanır; bu nedenle tüm metin ve sayı alanlarını almak yerine (sizin gibiJson.Document), son kullanıcılar tarihleri, tam sayıları, saatleri vb. alır ve daha iyi bir genel kullanıcı deneyimi sağlar.

Birçok REST API'sinde şemalarını program aracılığıyla belirlemenin bir yolu yoktur. Bu gibi durumlarda bağlayıcınıza şema tanımları eklemeniz gerekir. Bu derste, tablolarınızın her biri için basit, sabit kodlanmış bir şema tanımlayacak ve hizmetten okuduğunuz veriler üzerinde şemayı zorlayacaksınız.

Genel olarak, bağlayıcınız tarafından döndürülen veriler üzerinde bir şema zorlamanın aşağıdakiler gibi birden çok avantajı vardır:

• Doğru veri türlerini ayarlama
• Son kullanıcılara gösterilmesi gerekmeyen sütunları kaldırma (iç kimlikler veya durum bilgileri gibi)
• Yanıtta eksik olabilecek sütunları ekleyerek her veri sayfasının aynı şekle sahip olduğundan emin olma (REST API'lerinin alanın null olması gerektiğini belirtmenin yaygın bir yolu)

Table.Schema ile mevcut şemayı görüntüleme

Önceki derste oluşturulan bağlayıcı TripPin hizmetinden üç tablo görüntüler:Airlines , Airportsve People. Tabloyu görüntülemek için aşağıdaki sorguyu Airlines çalıştırın:

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

Sonuçlarda dört sütun döndürülür:

• @odata.id
• @odata.editLink
• AirlineCode
• Veri Akışı Adı

"@odata.*" sütunları OData protokolünün bir parçasıdır ve bağlayıcınızın son kullanıcılarına gösterilmesini isteyeceğiniz veya göstermeniz gereken bir şey değildir. AirlineCode ve Name tutmak istediğiniz iki sütun. Tablonun şemasına bakarsanız (kullanışlı Table.Schema işlevini kullanarak), tablodaki tüm sütunların veri türüne Any.Typesahip olduğunu görebilirsiniz.

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

Table.Schema , tablodaki sütunlar hakkında adlar, konumlar, tür bilgileri ve Precision, Scale ve MaxLength gibi birçok gelişmiş özellik gibi birçok meta veri döndürür. Gelecekteki dersler bu gelişmiş özellikleri ayarlamak için tasarım desenleri sağlayacaktır, ancak şimdilik yalnızca atfedilen tür (TypeName), ilkel tür (Kind ) ve sütun değerinin null (IsNullable) olup olmadığıyla ilgilenmeniz yeterlidir.

Basit bir şema tablosu tanımlama

Şema tablonuz iki sütundan oluşur:

Tablonun sabit kodlanmış şema tablosu Airlines ve Name sütunlarını AirlineCode olarak textayarlar ve şöyle görünür:

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

Tabloda Airports tutmak istediğiniz dört alan vardır (türünden recordbiri dahil):

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

Son olarak, People tabloda listeler (, AddressInfo), null atanabilir sütun (Emails ) ve atfedilen türe (GenderConcurrency) sahip bir sütun da dahil olmak üzere yedi alan vardır.

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 yardımcı işlevi

SchemaTransformTable Aşağıda açıklanan yardımcı işlevi, verilerinizde şemaları zorunlu kılmak için kullanılır. Aşağıdaki parametreleri alır:

Bu işlevin mantığı şuna benzer:

1. Kaynak tabloda eksik sütun olup olmadığını belirleyin.
2. Fazladan sütun olup olmadığını belirleyin.
3. Yapılandırılmış sütunları ( listtürü , recordve ) ve tableolarak ayarlanmış sütunları yoksayın type any.
4. Her sütun türünü ayarlamak için Table.TransformColumnTypes kullanın.
5. Şema tablosunda göründükleri sırayla sütunları yeniden sıralar.
6. Value.ReplaceType kullanarak tablonun kendisinde türü ayarlayın.

Aşağıdaki yardımcı kod kopyalanabilir ve uzantınıza yapıştırılabilir:

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 bağlayıcısını güncelleştirme

Şimdi yeni şema zorlama kodunu kullanmak için bağlayıcınızda aşağıdaki değişiklikleri yapacaksınız.

1. Tüm şema tanımlarınızı barındıran bir ana şema tablosu (SchemaTable) tanımlayın.
2. parametresini TripPin.Feedkabul schema etmek için , GetPageve GetAllPagesByNextLink güncelleştirme.
3. içinde şemanızı GetPagezorunlu kılma.
4. Her tabloyu yeni bir işleve (GetEntity) yapılan bir çağrıyla sarmalayacak şekilde gezinti tablosu kodunuzu güncelleştirin; bu, gelecekte tablo tanımlarını işlemek için daha fazla esneklik sağlar.

Ana şema tablosu

Şimdi şema tanımlarınızı tek bir tabloda birleştirip bir varlık adına (örneğin, GetSchemaForEntity("Airlines")) göre tanımı aramanıza olanak tanıyan bir yardımcı işlev (GetSchemaForEntity) ekleyeceksiniz.

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 &"'";

Veri işlevlerine şema desteği ekleme

Şimdi , ve işlevlerine TripPin.Feedisteğe bağlı schema bir parametre ekleyeceksiniz.GetAllPagesByNextLink GetPage Bu, şemayı (istediğiniz zaman) hizmetten geri alınan sonuçlara uygulanacağı disk belleği işlevlerine geçirmenizi sağlar.

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

Ayrıca şemayı doğru bir şekilde geçirdiğinizden emin olmak için bu işlevlere yapılan tüm çağrıları güncelleştireceksiniz.

Şemayı zorlama

Gerçek şema zorlama işlevinizde GetPage yapılır.

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 işlevini ekleme

İşlev, GetEntity Çağrınızı TripPin.Feed'e sarmalar. Varlık adına göre bir şema tanımı arar ve tam istek URL'sini oluşturur.

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;

Ardından işlevinizi TripPinNavTable , tüm çağrıları satır içinde yapmak yerine çağrısı GetEntityyapacak şekilde güncelleştireceksiniz. Bunun temel avantajı, gezinti tablosu mantığınıza dokunmanıza gerek kalmadan varlık oluşturma kodunuzu değiştirmeye devam etmenizi sağlayacak olmasıdır.

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;

Hepsini bir araya getirme

Tüm kod değişiklikleri yapıldıktan sonra Airlines tablosunu çağıran Table.Schema test sorgusunu derleyin ve yeniden çalıştırın.

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

Artık Airlines tablonuzda yalnızca şemasında tanımladığınız iki sütunun bulunduğunu görürsünüz:

Kişiler tablosunda aynı kodu çalıştırırsanız...

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

Kullandığınız (Int64.Type) atfedilen türün de doğru ayarlandığını göreceksiniz.

Dikkat edilmesi gereken önemli bir nokta, bu uygulamasının SchemaTransformTable ve record sütunlarının türlerini değiştirmemesi, ancak Emails ve AddressInfo sütunlarının list yine olarak listyazıldığıdır. Bunun nedeni Json.Document , JSON dizilerini M listelerine ve JSON nesnelerini M kayıtlarına doğru şekilde eşleyecektir. Power Query'de liste veya kayıt sütununu genişletirseniz, tüm genişletilmiş sütunların herhangi bir türde olacağını görürsünüz. Gelecekteki öğreticiler, iç içe karmaşık türler için tür bilgilerini özyinelemeli olarak ayarlamak üzere uygulamayı geliştirecektir.

Sonuç

Bu öğreticide, REST hizmetinden döndürülen JSON verilerinde şema zorlamaya yönelik örnek bir uygulama sağlanmıştır. Bu örnek basit bir sabit kodlanmış şema tablosu biçimi kullansa da, JSON şema dosyası veya veri kaynağı tarafından kullanıma sunulan meta veri hizmeti/uç noktası gibi başka bir kaynaktan dinamik olarak şema tablosu tanımı oluşturularak yaklaşım genişletilebilir.

Sütun türlerini (ve değerlerini) değiştirmenin yanı sıra kodunuz da tablonun kendisinde doğru tür bilgilerini ayarlar. Kullanıcı deneyimi her zaman son kullanıcıya doğru kullanıcı arabirimi kuyruklarını görüntülemek için tür bilgilerini çıkarsamaya çalıştığından ve çıkarım çağrıları temel alınan veri API'lerine yönelik diğer çağrıları tetikleyene kadar bu tür bilgisinin ayarlanması Power Query'nin içinde çalışırken performans açısından avantaj sağlar.

Önceki dersten TripPin bağlayıcısını kullanarak Kişiler tablosunu görüntülerseniz, tüm sütunların bir 'herhangi birini yazın' simgesi (liste içeren sütunlar bile) olduğunu görürsünüz:

Bu dersten TripPin bağlayıcısıyla aynı sorguyu çalıştırdığınızda artık tür bilgilerinin doğru görüntülendiğini göreceksiniz.

Sonraki adımlar

TripPin Bölüm 7 - M Türleriyle Gelişmiş Şema