Tech Talk: Is ETL in de cloud mogelijk zonder codering (deel 2)?

Afbeeldingen: https://www.agilytic.be/blog/tech-talk-etl-cloud-coding-part-2

In dit artikel, volgend op deel 1, wil ik de opzet van een ETL flow project illustreren voor een klant uit de horeca. Aangezien de gewenste front-end tool van de klant Power BI is, hebben we Microsoft voorgesteld als de juiste leverancier voor de cloudmigratie. Azure Portal is de meest gebruiksvriendelijke cloud UI met grondige documentatie, dus het is een goede optie voor starters. Het enige nadeel is dat Azure iets duurder is in vergelijking met concurrenten.

Er was slechts één gegevensbron voor dit proof of concept, hier paars gemarkeerd:

As-is gegevensarchitectuur

Extractie (ETL)

De eerste stap in ETL is extractie. Onze taak was om bijna 30 tabellen uit de MySQL database te halen. Hiervoor gebruikten we Azure Data Factory (ADF). Het is een van de weinige activiteiten waarbij codering niet nodig is. Het enige lastige is dat de MySQL-database op een privénetwerk wordt gehost. Dat betekent dat je ADF moet instellen op een VM die bij dit netwerk hoort. Het installatieproces van de zelf gehoste integratieruntime is niet eenvoudig. Vergeet niet om deze VM te voorzien van een Java Runtime Environment om tabellen te kunnen uitpakken in parketbestanden. Het is een effectief formaat dat metadata bevat. Snappy is de standaardcompressiemodus en we raden deze ten zeerste aan.

ADF heeft de volgende hiërarchie: Data Factory > Pipelines > Activiteiten. Zodra je je ADF hebt gekoppeld met alle benodigde bronnen (in ons geval: MySQL database, Azure Data storage en Databricks), kun je beginnen met het extraheren van gegevens met de zogenaamde 'kopieeractiviteiten'. ADF heeft twee opties: ingest templates en door de gebruiker gemaakte pipelines.

In het eerste geval kun je je gegevensbron scannen en kiezen welke tabellen interessant zijn. Je kunt hele tabellen kopiëren naar de sink die je kiest.

In het tweede geval moet je per databasetabel een aparte kopieeractiviteit maken. Ja, het is vervelend, maar je kunt er een SQL-query aan koppelen. Dit kan je veel opslagruimte besparen als je afgeschreven kolommen wilt verwijderen of sommige rijen wilt filteren (bijvoorbeeld op basis van de datum). U kunt ook controleren of u de kolommen van het type datetime correct hebt geëxtraheerd. Als dat niet het geval is, is een typecast nodig.

Transformatie (ETL)

De tweede stap in ETL is transformatie. We hebben dit gedaan met behulp van twee Spark clusters (een voor ontwikkeling en een voor batch jobs) die worden beheerd door de Databricks omgeving. Databricks notebooks zijn een handige manier om je code in Python te schrijven (dit dialect heet Pyspark) of om de specifieke jobs binnen het cluster in Scala te debuggen en te monitoren.

Belasting (ETL)

De derde en laatste stap is Laden. De taak was om verschillende dimensie- en feitentabellen die waren opgeslagen in parket in het datawarehouse te plaatsen, wat een Azure SQL-database is. Verrassend genoeg bleek ADF hier nogal beperkt te zijn. Je kunt niet meerdere parketbestanden automatisch laden. Ook kunnen we geen belangrijke laadopties definiëren, zoals het trunceren/verwijderen/overschrijven van tabellen. Dit bracht ons ertoe om de laadscripts met een JDBC-driver in de Databricks te plaatsen met behulp van de volgende code:

df_name.write \    

.format("jdbc") \    

.option("url", "jdbc:postgresql:dbserver").    

.option("dbtable", "schema.tablename").    

.option("user", "gebruikersnaam").    

.option("wachtwoord", "wachtwoord").  

.opslaan()

Er zijn veel mogelijkheden met deze methode. Ze worden hier beschreven: JDBC naar andere databases - Spark 3.2.1 documentatie (apache.org)

Volgende

Kijk uit naar deel 3, waarin ik een ander cloudverhaal beschrijf waarbij ik AWS gebruik om de klant te helpen bij het opzetten van hun ETL-stromen en datawarehouses.