Azure Data Factory is een heel krachtige tool, waarbij je zonder veel programmeerkennis toch een sterk stukje software kunt opzetten. Het is een clouddienst en daardoor zijn er veel integratiemogelijkheden met andere cloudtools. Verschil met volledige on-premises systemen is dat je regelmatig met webcalls te maken krijgt. Het berichtenverkeer is wisselend, maar vaak in JSON of XML formaat. De opzet hiervan kan complex zijn vanwege nesting en arrays. Hoe krijg je die data hier eigenlijk uit?
“Het berichtenverkeer van webcalls (HTTP activities) is vaak in JSON of XML formaat.”
Data via web protocol (HTTP)
Met een web-call via HTTP kun je triggers uitvoeren, maar ook data uitwisselen. Data kan in de vorm van bestanden (bijv. ZIP of CSV) maar de response kan ook de data zelf bevatten. Je krijg dan een flinke lap tekst in een bepaald formaat die je zelf ergens moet opslaan. De data in zo’n response is veelal in JSON of XML formaat. Voordeel van dit formaat is dat de mogelijkheden van data-aanlevering eindeloos zijn, maar dat het verwerken ervan tot gestructureerde datasets best ingewikkeld kan zijn.
Sterker nog; als het echt complex wordt, kan Azure Data Factory je niet eens helpen met het volledig parsen van JSON data naar rijen en kolommen. Wat wel kan is een relatief simpele JSON verwerken in de mapping van de copy-data, maar als er een Array aan te pas komt dan stokt het hier.
Als je even Googlet (of is het Googelt?) kom je al snel op een artikel van Gary Strange. Het lijkt heel eenvoudig, totdat je in de wat complexere structuren komt, zie zijn kopje ‘Not So Good’. Daarvoor heb ik een workaround proberen te vinden en dat is me gelukt.
Data Factory i.c.m. SQL Database
Als je complexe JSON niet kan verwerken in ADF, dan maar in een andere tool. Voor data engineers zoals ik grijp ik dan al snel naar SQL Database. De manier om dit te doen is je JSON als tekst op te slaan in een tabel en die te parsen naar rijen en kolommen.
JSON opslaan in SQL
Om de JSON die je via een web-call hebt opgehaald op te slaan in een SQL tabel, maak ik er eerst een blob-file van. Je hebt dan een tekstbestand waarin de JSON inhoud van de web-call response staat opgeslagen. Dat is het startpunt van dit stappenplan. Ik schrijf later nog wel een artikel over hoe je iteratief (als er sprake is van paginering in de API) alle data uit een API kunt halen.
Als je een JSON-bestand hebt opgeslagen in de blob storage, kun je de inhoud hiervan wegschrijven in een tabel. Deze tabel noem ik JSON_Aanlevering en heeft de volgende structuur:
Elke regel in deze tabel vertegenwoordigd één bestand, waarbij je wat metadata kunt opslaan en de inhoud (JSON) zelf. Vervolgens kun je de inhoud parsen met T-SQL en de data verder verwerken in ADF.
Begin in ADF met een lege pipeline met alleen een Copy-data activiteit.
Source dataset
De truc in ADF bij het verwerken van een JSON naar één record in SQL is dat de source dataset de gehele bestandsinhoud moet gaan behandelen als één record met één kolom (één waarde). Je maakt een dataset waarbij je net doet alsof je een tab-gescheiden bronbestand zonder header verwerkt:
Het JSON-bestand bevat geen tab’s en row delimiter, waardoor de gehele inhoud dus als één waarde wordt gepakt. Soms ongewenst maar in dit geval een gewenst neveneffect :). Gebruik deze dataset als source, hardcode voor nu de bestandsnaam.
Sink dataset
Deze dataset is zoals wellicht al had verwacht een SQL Database met een verwijzing naar de eerder genoemde SQL-tabel:
Mapping
Nu willen we dat de waarde uit de source-dataset in de JSON_Inhoud-kolom wordt geschreven van de tabel stage.JSON_Aanlevering. Dat bereik je door handmatig een mapping toe te voegen via + New mapping en als Source de (naamloze) kolom 1 te selecteren en deze te koppelen aan JSON_Inhoud.
Het is niks meer dan de enige bekende kolom uit de source-dataset mappen naar de kolom in de JSON_Aanlevering tabel.
Metadata vullen
Als je deze Copy-data activiteit uitvoert, zal de JSON-inhoud in de SQL DB gevuld worden met de inhoud van het bestand. Ik heb na deze copy-data activity nog een stored procedure aanroep staan die voor de zojuist ingevoerde JSON-inhoud de metadata bijwerkt. Denk hierbij aan de naam, laad-datum en eventueel volgnummer als je er meer hebt.
JSON parsen in SQL
Nu je de inhoud van de JSON-file in een SQL-tabel hebt, kun je aan de hand van SQL statements de gehele JSON uit elkaar gaan halen.
Dit stuk werk ik later uit, to be continued…
Mocht je ergens hulp bij nodig hebben, laat het me gerust weten!
