Werken met geneste gegevensstructuren in Query Service

Adobe Experience Platform Query Service ondersteunt het gebruik van geneste gegevensvelden. De complexiteit van bedrijfsgegevensstructuren kan het transformeren of verwerken van deze gegevens ingewikkeld maken. Dit document verstrekt voorbeelden van om, datasets met complexe gegevenstypes met inbegrip van genestelde gegevensstructuren tot stand te brengen te verwerken of om te zetten.

De Dienst van de vraag verstrekt een PostgreSQL interface om SQL vragen op alle datasets in werking te stellen die door Experience Platform worden beheerd. Het platform steunt het gebruik van of primitieve of complexe gegevenstypes in lijstkolommen zoals struct, series, kaarten, en diep genestelde struct, series, en kaarten. Datasets kunnen ook geneste structuren bevatten waarbij het gegevenstype van de kolom zo complex kan zijn als een array van geneste structuren, of een kaart met kaarten waarin de waarde van een sleutelwaardepaar een structuur met meerdere nestniveaus kan zijn.

Aan de slag

Deze zelfstudie vereist het gebruik van een externe PSQL-client of het hulpmiddel Query Editor om query's te schrijven, te valideren en uit te voeren in de gebruikersinterface van het Experience Platform (UI). De volledige details op hoe te om vragen door UI in werking te stellen kunnen in de gids UI van de Redacteur van de Vraagworden gevonden. Voor een gedetailleerde lijst waarop de derdeDesktopcliënten met de Dienst van de Vraag kunnen verbinden, zie het overzicht van cliëntverbindingen.

U moet ook een goed inzicht hebben in de syntaxis INSERT INTO en CTAS . De specifieke informatie over hun gebruik kan in de INSERT INTO en CTAS secties van de SQL documentatie van de syntaxisverwijzingworden gevonden.

Een gegevensset maken

De dienst van de vraag verstrekt de Create Lijst als Uitgezochte (CTAS) functionaliteit om een lijst tot stand te brengen die op de output van een SELECT verklaring wordt gebaseerd, of zoals in dit geval, door een verwijzing naar een bestaand schema XDM in Adobe Experience Platform te gebruiken. Hieronder wordt het XDM-schema voor Final_subscription weergegeven dat voor dit voorbeeld is gemaakt.

In het volgende voorbeeld ziet u hoe SQL wordt gebruikt om de gegevensset final_subscription_test2 te maken. final_subscription_test2 wordt gemaakt met het schema Final_subscription . Gegevens worden uit de bron geëxtraheerd met een SELECT -component om bepaalde rijen te vullen.

CREATE TABLE final_subscription_test2 with(schema='Final_subscription') AS (
        SELECT struct(userid, collect_set(subscription) AS subscription) AS _lumaservices3 FROM(
            SELECT user AS userid,
                   struct( last(eventtime) AS last_eventtime,
                           last(status) AS last_status,
                           offer_id,
                           subsid AS subscription_id)
                   AS subscription
             FROM (
                   SELECT _lumaservices3.msftidentities.userid user
                        , _lumaservices3.subscription.subscription_id subsid
                        , _lumaservices3.subscription.subscription_status status
                        , _lumaservices3.subscription.offer_id offer_id
                        , TIMESTAMP eventtime

                   FROM
                        xbox_subscription_event
                   UNION
                   SELECT _lumaservices3.msftidentities.userid user
                        , _lumaservices3.subscription.subscription_id subsid
                        , _lumaservices3.subscription.subscription_status status
                        , _lumaservices3.subscription.offer_id offer_id
                        , TIMESTAMP eventtime
                   FROM
                        office365_subscription_event
             )
             GROUP BY user,subsid,offer_id
             ORDER BY user ASC
       ) GROUP BY userid)

In de eerste dataset final_subscription_test2 wordt het gegevenstype struct gebruikt voor zowel het subscription veld als het userid , dat uniek is voor elke gebruiker. In het veld subscription worden de productabonnementen voor een gebruiker beschreven. Er kunnen meerdere abonnementen zijn, maar een tabel kan slechts de gegevens voor één abonnement per rij bevatten.

Geneste gegevensvelden bijwerken met INVOEGEN IN

Nadat de final_subscription_test2 dataset is gecreeerd, wordt de INSERT INTO verklaring gebruikt om extra gegevens aan de lijst toe te voegen. Bij het kopiëren van gegevens moeten de gegevenstypen in bron en doel overeenkomen. Het gegevenstype van de bron moet ook CAST tot het gegevenstype van het doel zijn. De stijgende gegevens worden dan toegevoegd in de doeldataset gebruikend volgende SQL.

INSERT INTO final_subscription_test
      SELECT struct(userid, collect_set(subscription) AS subscription) AS _lumaservices3 FROM(
            SELECT user AS userid,
                   struct( last(eventtime) AS last_eventtime,
                           last(status) AS last_status,
                           offer_id,
                           subsid AS subscription_id)
                   AS subscription
             FROM  SELECT _lumaservices3.msftidentities.userid user
                        , _lumaservices3.subscription.subscription_id subsid
                        , _lumaservices3.subscription.subscription_status status
                        , _lumaservices3.subscription.offer_id offer_id
                        , TIMESTAMP eventtime

                   FROM
                        xbox_subscription_event
                   UNION
                   SELECT _lumaservices3.msftidentities.userid user
                        , _lumaservices3.subscription.subscription_id subsid
                        , _lumaservices3.subscription.subscription_status status
                        , _lumaservices3.subscription.offer_id offer_id
                        , timestamp eventtime
                   FROM
                        office365_subscription_event
             )
             GROUP BY user,subsid,offer_id
             ORDER BY user ASC
       ) GROUP BY userid)

Gegevens uit een geneste gegevensset verwerken

Om de lijst van actieve abonnementen van een gebruiker van een dataset te weten te komen, moet u een vraag schrijven die de elementen van een serie in veelvoudige rijen en kolommen scheidt. Hiervoor moet u eerst de vorm van het gegevensmodel begrijpen, aangezien de abonnementsgegevens binnen een array worden bewaard die in de dataset is genest.

De opdracht PSQL \d wordt gebruikt om op niveau naar de vereiste abonnementsgegevens te navigeren. De tabellen illustreren de structuur van de final_subscription_test2 dataset. Complexe gegevenstypen kunnen in één oogopslag worden herkend, omdat het geen standaardtekstwaarden zijn, zoals tekst, booleaanse waarden, tijdstempels, enz.

De velden van de volgende kolom worden weergegeven met de opdracht \d final_subscription_test2__lumaservices3 .

subscription is een array van struct-elementen. De velden worden weergegeven met de opdracht \d _lumaservices3_subscription_e[] .

Als u de geneste velden van het abonnement wilt opvragen, moet u eerst de elementen van de array subscription in meerdere rijen scheiden en de resultaten retourneren met de functie exploderen. In het volgende SQL-voorbeeld wordt het actieve abonnement voor een gebruiker geretourneerd op basis van userid .

SELECT userid, subs AS active_subscription FROM (
    SELECT _lumaservices3.userid AS userid, explode(_lumaservices3.subscription) AS subs
    FROM final_subscription_test2
)
WHERE subs.last_status='Active';

Deze vereenvoudigde voorbeeldoplossing staat slechts voor één actief gebruikersabonnement toe. In werkelijkheid kunnen er veel actieve abonnementen zijn voor één gebruiker. In het volgende voorbeeld wordt de vorige query gewijzigd om meerdere gelijktijdige actieve abonnementen toe te staan.

SELECT userid, collect_list(subs) AS active_subscriptions FROM (
     SELECT
          _lumaservices3.userid AS userid,
          explode(_lumaservices3.subscription) AS subs
     FROM final_subscription_test2
     )
WHERE subs.last_status='Active'
GROUP BY userid ;

Ondanks de toenemende complexiteit van dit SQL-voorbeeld, garandeert collect_list voor actieve abonnementen niet dat de uitvoer in dezelfde volgorde zal zijn als de bron. Als u een lijst met actieve abonnementen voor een gebruiker wilt maken, moet u GROUP BY gebruiken of de volgorde wijzigen om de resultaten van de lijst samen te voegen.

Volgende stappen

Door dit document te lezen, begrijpt u nu hoe te om datasets te verwerken of om te zetten die complexe gegevenstypes in de Dienst van de Vraag van Adobe Experience Platform gebruiken. Gelieve te zien de leidraad van de vraaguitvoeringvoor meer informatie over het runnen van SQL vragen over datasets binnen het meer van Gegevens.