Voor deze blog stellen we ons als doel om meerdere XML files in een database te laden om de structuur daarvan over afzonderlijke tabellen te kunnen verspreiden. Een aanvullende complexiteit is dat de XML files bij elke levering andere bestandsnamen hebben. Dit is een vrij gebruikelijke case, bijvoorbeeld bij bankbetalingsverkeer. Maar hoe regelen we zoiets eigenlijk in Azure?
Niet-Azure architectuur
Eerst zetten we dit nog even af tegen een niet-Azure omgeving, waar zoiets over het algemeen eenvoudig te realiseren is. De XML files staan bijvoorbeeld op een netwerklocatie in het eigen bedrijfsnetwerk. Een database zoals SQL Server staat op de eigen databaseserver en deze kan een lijst van XML files bij de netwerklocatie opvragen. Deze lijst wandelt SQL Server vervolgens af en importeert derhalve alle XML files in een tabel met een kolom van datatype XML. Het versnipperen van de inhoud over verschillende tabellen is dan de enige vervolgstap. Deze versnippering behoeft nog wel enige uitleg, maar dat is niet het doel van deze blog.
Azure architectuur
Deze blog gaat over de case in de Azure omgeving. De XML files staan in een folder op de Azure BLOB Storage en de doeldatabase is een Azure SQL Database. Dus niet SQL Server, maar daadwerkelijk SQL Database van Azure. Schaalbaarheid, snelle ontwikkelingen en kostenbesparing zijn enkele voordelen van Azure.
Deze blog schrijf ik, omdat het inlezen van meerdere bestanden in Azure weliswaar goed te doen is, maar voor onze case loop je snel tegen beperkingen op van individuele componenten. Er is bijvoorbeeld een alternatief concept nodig om de lijst aan XML files te bepalen. Hiervoor zal ik gebruik maken van de Azure Data Factory wat één van de andere Azure Services is die Microsoft aanbiedt. Daarnaast zijn er bij de componenten ook specifieke instellingen nodig.
Eén XML inlezen in Azure SQL Database
Laten we met de basis beginnen. Het inlezen van één XML vanaf de Azure BLOB Storage in een Azure SQL Database. Hiervoor maken we in Azure SQL Database een External Data Source aan. Zo’n External Data Source kan voor meerdere typen bronnen worden aangemaakt, waaronder HADOOP, een andere Azure SQL Database, of BLOB Storage. We gebruiken het laatste type om bestanden vanaf de Azure BLOB Storage te kunnen inlezen in Azure SQL Database.
In de Azure BLOB Storage staan 5 bestanden, waarbij we het bestand “First_5gh2rfg.xml” nu eerst zullen inlezen. Hiervoor maken we een doeltabel aan in de Azure SQL Database. De inhoud van de XML file zal worden opgeslagen in een kolom met een speciaal datatype XML. Belangrijk => Dit datatype is nodig om de inhoud van een XML file verder eenvoudig te kunnen ophakken en te verspreiden over meerdere tabellen.
Met een eenvoudige query in Azure SQL Database kan de data van de Azure BLOB Storage worden opgehaald en de tabel worden geladen:
INSERT INTO [BI].[XML_IMPORT] (XML_DATA)SELECT CAST(BulkColumn AS XML)FROM OPENROWSET(BULK 'MyStorage/MainFolder/First_5gh2rfg.xml',DATA_SOURCE = 'AzureBlobStorage',SINGLE_BLOB) AS XML_IMPORT
Hierbij is “MyStorage” de BLOB Container, is “MainFolder” de hoofdfolder in de BLOB Container en “First.xml” een van de XML files in die folder. Het inlezen van 1 enkele XML file in Azure SQL Database vanaf de Azure BLOB Storage is dus eenvoudig op te zetten.
De Uitdaging
En wat nu als we meerdere bestanden met andere namen bij elke levering in 1 keer willen inlezen? Dan lopen we in eerste instantie tegen de volgende zaken op:
- Er kan geen gebruik gemaakt worden van “*.xml” bij het inlezen, omdat er een specifieke filenaam moet worden opgegeven
- Maar essentieel is dat Azure SQL Database niet de Azure BLOB Storage kan benaderen om een lijst van files op te halen
Als alternatief kan Azure Data Factory worden gebruikt. In de Azure Data Factory kun je diverse bronnen definieren en een pipeline aanmaken met één of meer Activities. Met de Copy Data Activity kun je data overhevelen vanuit diverse bronnen naar legio doelen. Zo ook van Azure BLOB Storage naar Azure SQL Database. Hieronder is zo’n pipeline te zien.
Dit lijkt precies op wat we willen. Echter, dit zou voor meerdere CSV bestanden een fluitje van een cent zijn… Maar helaas niet voor XML files die in een database kolom met datatype XML terecht moeten komen. Het specifieke datatype XML wordt namelijk (nog) niet ondersteund, zoals hieronder te zien is bij de brondefinitie van de Azure BLOB Storage.
Met datatype String zou elke regel in een XML bestand als apart record binnenkomen, waardoor de structuur verloren gaat. Het verdelen van de inhoud over meerdere tabellen wordt dan weer een uitdaging op zichzelf. De kolom van datatype XML verwacht ook een volledige XML file en zal een foutmelding geven bij het inlezen.
De oplossing
Uiteindelijk is de oplossing een hybride variant. We kunnen namelijk ook Azure Data Factory inzetten om de lijst van XML bestanden in de Azure SQL Database te krijgen. Vervolgens roept Azure Data Factory de Azure SQL Database aan zodat die zelf door die lijst heen kan wandelen. Meerdere XML files kunnen dan dus wel in 1 keer kunnen worden ingelezen in Azure SQL Database!
De pipeline ziet er als volgt uit:
In de Azure SQL Database is een hulptabel aangemaakt die enkel bestandsnamen zal gaan bevatten.
Activity 1 in de pipeline is het leeggooien van die tabel door het aanroepen van een Stored Procedure in Azure SQL Database
Activity 2 en Activity 3 behoeven meer uitleg, want hier zit de motor in van de oplossing om de lijst aan bestandnamen van Azure BLOB Storage in een Azure SQL Database tabel op te slaan.
De tweede Activity is een “Get Metadata” Activity waarvan belangrijk is dat de Arguments “Child Items” en “Item Name” worden gekozen. Die “Child Items” staan in onze case namelijk voor de bestandsnamen in de Azure BLOB Storage folder “MainFolder”. De dataset van de BLOB Storage is geconfigureerd zoals in onderstaande afbeelding. Bij “File path” is de folder wel ingevuld maar File is leeggelaten.
Terug naar de pipeline naar de derde Activity van het type ForEach
In de ForEach activity zetten we bij Items “@activity('Get Blob File Names').output.childItems” neer. Dat zorgt ervoor dat er voor elke file in de Azure BLOB Storage een zekere onderliggende activity kan worden uitgevoerd.
Als we de ForEach openen, dan zien we daar de aanroep van een Stored Procedure. De Stored Procedure is als volgt in Azure SQL Database aangemaakt:
CREATE PROCEDURE [BI].[XML_IMPORT_GET_FILE_NAME] (@Name_Of_File varchar(max)) ASBEGININSERT INTO BI.BLOB_FILE_NAME SELECT @Name_Of_FileEND
In de @Name_Of_File parameter van deze Stored Procedure kunnen we dan de XML filename doorgeven die in de ForEach loop beschikbaar moet worden gemaakt. Dit moet gedaan worden door in de parameter value de tekst “@item().Name” neer te zetten. LET OP! Dit is case sensitive, dus “@item().name” werkt niet, terwijl onderhuids de eigenschap wel als “name” zonder hoofdletter wordt gehanteerd door de Azure Data Factory.
Het tussentijdse resultaat van de pipeline na Activities 1 t/m 3 is een gevulde Azure SQL Database tabel met alle bestandsnamen die op de Azure BLOB Storage staan!
De vierde en laatste activity is wederom het aanroepen van een Stored Procedure in Azure SQL Database. Deze Stored Procedure zal door de tabel met bestandsnamen heen wandelen en deze benutten om in te lezen in de XML kolom tbv verdere onderverdeling.
Het begin van deze Stored Procedure staat hieronder, waarbij te zien is dat de simpele query voor het inlezen van 1 enkele file wordt benut. Die query wordt als het ware elke keer opgebouwd waarbij de specifieke bestandnaam uit bovengenoemde tabel wordt gehaald middels een cursor.
CREATE PROCEDURE [BI].[XML_IMPORT_AND_SPLIT] ASBEGINDECLARE @name_of_file varchar(8000)
DECLARE cursor_file_names CURSOR FAST_FORWARDFOR SELECT NAME_OF_FILE FROM BI.BLOB_FILE_NAME WHERE NAME_OF_FILE LIKE '%.xml'
OPEN cursor_file_namesFETCH NEXT FROM cursor_file_names into @name_of_file
WHILE @@FETCH_STATUS = 0BEGINEXEC('INSERT INTO [BI].[XML_IMPORT](XML_DATA)SELECT CAST(BulkColumn AS XML)FROM OPENROWSET(BULK ''MyStorage/MainFolder/' + @name_of_file + ''',DATA_SOURCE = ''EDS_AzureBlobStorage'',SINGLE_BLOB) as XML_IMPORT')FETCH NEXT FROM cursor_file_names into @name_of_fileENDDEALLOCATE cursor_file_names
Deze procedure bevat eigenlijk ook nog het ophakken van de XML in meerdere tabellen, maar dat gaat te ver voor deze blog.
Conclusie
Het inlezen van meerdere files van de Azure BLOB Storage in Azure SQL Database is op zichzelf een fluitje van een cent met Azure Data Factory. Echter, als we XML files in een XML kolom willen laden tbv verdere onderverdeling, dan lopen we tegen de individuele beperkingen op van Azure componenten. Desalniettemin is het met een specifiek concept en juiste instellingen dus alsnog op een goede manier te regelen met Azure Data Factory.
