Eind september 2017 had ik de eer om deel te mogen nemen aan Microsoft Ignite in Orlando (Florida). Tijdens deze editie van Ignite werd de public preview van Azure Data Factory (ADF) v2 aangekondigd. Dit was een enorme verbetering ten opzichte van v1, mede vanwege de introductie van een user interface die de ontwikkelaars zo gewend waren vanuit SQL Server Integration Services (SSIS). Hierdoor werd ADF een waardige vervanger voor SSIS. Nu, inmiddels bijna 3 jaar verder, is ADF doorontwikkeld en is het vanwege alle integratiemogelijkheden met cloud resources een onmisbare tool geworden voor de ontwikkelaar in data & analytics.
“Als je slim gebruik maakt van de mogelijkheden binnen ADF, scheelt het je ontwikkeltijd en in de toekomst enorm veel onderhoud.”
Ik schrijf deze blog vanwege mijn enthousiasme over de tool, maar vooral omdat ik anderen (jij, de lezer) wil behoeden voor fouten die ik heb gemaakt toen ik net met de tool begon te werken. Als je slim gebruik maakt van de mogelijkheden binnen ADF, scheelt het je ontwikkeltijd en in de toekomst enorm veel onderhoud. Daar wordt iedereen toch blij van?
Bij de ontwikkeling van een ETL-straat in ADF ontwikkel je feitelijk een stuk software, maar dan in een PaaS omgeving. Als je goed programmeert werk je object georiënteerd. Hierdoor herbruik je zoveel mogelijk ontwikkelde functionaliteit zodat je dezelfde logica niet op meerdere plekken hebt. Dat werkt in Azure Data Factory feitelijk ook zo. Repetitieve handelingen bouw je niet meerdere keren, je bouwt een ‘loop’ die een aantal keer dezelfde actie uitvoert met andere parameters.
Werken met parameters in ADF
Door te werken met variabelen en parameters, kun je heel veel activiteiten binnen een pipeline gebruiken voor hetzelfde doeleind. Zo kun je bijvoorbeeld één pipeline met één Copy Data activiteit gebruiken om 1, 10 of 100 bestanden in te laden met een verschillende structuur.
“Daar wordt toch iedereen blij van?”
Hiervoor ontwikkel je dus één keer je ontsluiting met als input een array gevuld met bestandsnamen. Hiervoor maak je twee pipelines:
De PL_*_Multi zet een variabele (array) met bestandsnamen, zonder extensie. Hierdoor kan de variabele ook gebruik worden als (doel)tabelnaam. De PL_*_Single doet het eigenlijke inladen van het bestand. Daaromheen zit nog wel wat metadatabepaling, zoals kijken of het bestand bestaat, de extensie (.csv) toevoegen aan de bestandsnaam, archivering van het bestand, etc.
De PL_*_Multi ziet er als volgt uit:
Eerst wordt een variabele gevuld met een kommagescheiden lijst van strings, bijvoorbeeld: Adres,Client,Verkoop,Medewerker
Vervolgens zorg je dat deze variabele met de array wordt gebruikt voor de items in de ForEach loop:
In deze ForEach heb je dan alleen een verwijzing naar de PL_*_Single met als parameter de bestandsnaam die je aan de ForEach als item hebt doorgegeven.
De PL_*_Single pipeline plaatst een extensie achter de variabele, checkt of het bestand bestaan en áls dat het geval is wordt de data verwerkt in de ‘True’ activiteit van de If Condition:
Als het bestand bestaat worden de volgende acties uitgevoerd:
Als het laden succesvol verloopt gaat het bestand naar het archief, zo niet; naar error.
Verschillende bestandsstructuren
Omdat ieder bestand dat je inleest andere kolommen hebt, is de configuratie van de linker COPY-activiteit van de PL_*_Single pipeline een hele belangrijke. Daarvoor gebruik ik een dataset die overweg kan met de aangeboden CSV structuur. Let daarbij op dat je dataset aanmaakt met de juiste delimiters en text qualifiers. Als je vervolgens voor de (blob)container, directory en filename een parameter maakt, kun je deze dataset voor alle bestanden met dit type CSV structuur gebruiken.
In de COPY-data activiteit kun je vervolgens opgeven waar het bestand zich bevindt. De locatie is meestal hetzelfde en de bestandsnaam hadden we hierboven al in de variabele gestopt (mét extensie).
In de Sink zorg je vervolgens dat (o.b.v. de ingevoerde parameter Filename) de juiste tabel wordt gevuld. Je kunt als pre-copy script nog een truncate op de betreffende tabel uitvoeren. Je kunt hier ook een drop table (if exists) doen en auto create table aanzetten. Hierdoor klopt de structuur in je staging ook altijd met het bestand (niet altijd gewenst).
Het idee van een flexibele kolommenstructuur is dat je geen mapping hebt. Die bepaalt hij zelf o.b.v. kolomnamen. Zorg er dus voor dat in Mapping de lijst met kolommen leeg is:
Als je de pipeline op deze manier hebt ingericht en je de bestanden op de juiste plek in je storage account hebt staan, kun je pipeline draaien:
Ziezo; met twee pipelines draai je moeiteloos een hele set aan verschillende bestandsstructuren in je SQL Database.
Mocht dit globale stappenplan je te snel gaan, dan kun je het beste daar eerst bedreven in worden door artikelen als deze door te nemen. Helaas zijn de artikelen van Cathrine Wilhelmsen momenteel offline, die waren erg sterk. Wie weet komt ze snel terug met bijgewerkte versies.
Mocht je ergens hulp bij nodig hebben, laat het me gerust weten!
