Azure voor Beginners - Kopieer Data van uw Azure Storage Account naar uw Azure SQL Database

Als u onze Azure voor Beginners-serie heeft gevolgd, heeft u inmiddels een Azure Storage Account aangemaakt waarin u CSV-bestanden heeft geüpload, heeft u een Azure SQL Database én een Azure Data Factory aangemaakt. Wat u tot nu toe heeft geleerd, voelde misschien wat onsamenhangend aan, maar in dit deel van de Azure voor Beginners handleiding zullen we u laten zien hoe al deze afzonderlijke services met elkaar samenwerken. We zullen u laten zien hoe u data van uw Azure Storage Account naar uw Azure SQL Database kunt kopiëren met behulp van uw Azure Data Factory.

Note: het uiterlijk van Azure verandert regelmatig. Uw scherm ziet er mogelijk net iets anders uit dan op de afbeeldingen in deze handleiding. Sommige functies zijn bijvoorbeeld mogelijk naar een ander tabblad verplaatst, maar de algemene stappen zullen hetzelfde zijn.

Note: deze beginnershandleiding is gemaakt op basis van de Engelstalige UI. Om deze reden zullen wij in dit artikel de Engelse benamingen hanteren.

Note: houd er rekening mee dat de services binnen Azure betaalde services zijn. De kosten zijn afhankelijk van uw gebruik en configuratie. (Meer informatie vindt u hier.) Het is verstandig om dit te doen onder toezicht van een databasebeheerder of een data-engineer.

Nadat u zich heeft ingelogd in uw Azure-account, navigeert u naar Azure Data Factory. U kunt dit doen door op het Data Factory logo in de menubalk bovenaan te klikken, of, als deze er daar niet bij staat, door helemaal rechts op More services te klikken. Vervolgens scrolt u naar beneden tot de Analytics koptekst, waar u ook Data factories zult vinden. Hier klikt u op, waarna de Data Factory omgeving zal worden geladen. Ten slotte klikt u op de Data Factory welke u tijdens de vorige les heeft gemaakt.

Na het openen van uw Data Factory zult u duizelingwekkend scherm vol instellingen voor uw Data Factory te zien krijgen. Voor deze beginnershandleiding zijn deze instellingen eigenlijk niet echt interessant, omdat het daadwerkelijk gebruiken van Data Factory niet vanuit deze interface wordt gedaan. Het daadwerkelijke werk wordt gedaan in Azure Data Factory Studio, een webgebaseerde interface voor het werken met Azure Data Factory. Het wordt in uw browser geladen en helpt u bij het visueel ontwerpen van data workflows, het verbinden met databronnen, het transformeren en verplaatsen van data tussen verschillende systemen, en het plannen en monitoren van uw databewerkingen. U start Azure Data Factory Studio door op de blauwe knop te klikken.

In Azure Data Factory Studio zult u vier knoppen te zien krijgen: Ingest, Orchestrate, Transform data, en Configure SSIS. De laatste knop zullen wij in deze Azure voor Beginners handleiding geen aandacht besteden, omdat deze bedoeld is voor het migreren en draaien van uw bestaande data processen in de cloud. De eerste drie knoppen zijn daarentegen wél handig. Ze reflecteren min of meer het ETL-proces (Extract, Transform en Load), waarbij Ingest overeenkomt met Extract, Transform data overeenkomt met Transform, en Orchestrate zowel de Load fase als het algehele beheer van het hele proces omvat. Omdat we data uit ons Azure Storage Account in onze Azure SQL Database proberen te krijgen, zullen we de data eerst moeten inladen. Klik hiervoor op de knop Ingest.

Hiermee wordt de Copy Data tool gestart, die ons door de noodzakelijke stappen leidt om onze gegevens op te nemen. We beginnen met de keuze tussen Built-in copy task en Metadata-driven copy task. De eerstgenoemde optie is ideaal voor het creëren van een snelle datapijplijn, welke data van de ene plaats naar de andere overdraagt. De laatstgenoemde optie is geavanceerder, omdat deze complexe en grootschalige gegevensoverdrachten aankan. Voor onze beginnershandleiding kiezen we voor 'Built-in copy task'.

Daaronder hebben we de optie om onze uitvoering van de datapijplijn één keer uit te voeren, om elke x uur/dagen/weken/maanden uit te voeren, of om een Tumbling window te maken waarin datapijplijnen worden uitgevoerd met specifieke intervallen, op een niet-overlappende manier. Omdat onze pijplijn alleen voor demonstratieve doeleinden bedoeld is, kiezen we voor 'Run once now'. Klik op 'next' om naar de volgende stap te gaan.

Vervolgens moeten we een databron type selecteren. In essentie vraagt het waarin we de data die we naar onze Azure SQL Database willen kopiëren hebben opgeslagen. Aangezien het CSV-bestand dat we naar onze database willen kopiëren als blob in ons Azure Storage Account is opgeslagen, kiezen we voor 'Azure Blob Storage'.

Nadat we het brontype hebben opgegeven, zullen we een verbinding moeten opzetten om het CSV-bestand uit ons Azure Storage Account op te kunnen halen. Klik hiervoor op 'New connection'.

Als eerste verzint u een naam voor de nieuwe verbinding.

Daarna zullen we moeten kiezen hoe deze verbinding zal worden beheerd. Dit gebeurt via een Integration Runtime (IR), de daadwerkelijke rekeninfrastructuur die door Azure Data Factory wordt gebruikt en die de verplaatsing van uw data mogelijk maakt. Standaard gebeurt dit via Azure’s eigen AutoResolveIntegrationRuntime, die de verbinding voor u inricht en beheert. Het zal bepalen hoeveel rekenkracht er nodig is binnen het specifieke datacenter en zal deze gebruiken om de verwerkingsactiviteiten uit te voeren. U heeft ook de keuze voor een eigen IR, welke afhankelijk van uw situatie mogelijk beter bij uw wensen aansluit. Voor onze beginnershandleiding laten we het op AutoResolveIntegrationRuntime staan ​​en gebruiken we de Account key als authenticatietype.

Vervolgens selecteert u uw Azure Storage Account waarin u uw CSV-bestanden heeft opgeslagen. Dit kan door daarboven uw Azure-abonnement te selecteren, of door het handmatig in te voeren. Als u voor het laatstgenoemde kiest, zult u de naam van uw storage account, uw storage account key en uw endpoint suffix moeten invoeren. Voor deze beginnershandleiding selecteren we ons Azure Storage Account via ons Azure-abonnement. De rest van de instellingen laten we op onaangetast. Klik onderaan op de knop 'Test connection' om te zien of er succesvol een verbinding tot stand kan worden gebracht. Als dat het geval is klikt u vervolgens op 'Create'.

Aangezien we zojuist een verbinding met ons Azure Storage Account hebben gemaakt, zullen we nu de locatie moeten opgeven van het specifieke bestand dat we naar onze Azure SQL Database willen kopiëren. Klik op 'Browse' om naar deze locatie te navigeren.

Vervolgens selecteert u het bestand dat u naar uw Azure SQL Database wilt kopiëren. In ons voorbeeld kiezen we voor shippers.csv. Klik op 'OK' zodra u dit heeft gedaan.

Daaronder ziet u de opties Binary copy, Recursively en Enable partitions discovery staan.

• Binary copy - Uw bestanden zullen ongewijzigd worden gekopieerd, wat betekent dat ze niet worden geïnterpreteerd. Uw bestanden worden precies overgedragen zoals ze zijn, zonder enige conversie, transformatie of interpretatie van het bestandsformaat. De bestemming kan geen relationele database zijn.

• Recursively - Alle bestanden in de invoermap (en submappen) worden recursief verwerkt.

• Enable partitions discovery - Hierdoor worden extra kolommen aan uw dataset toegevoegd op basis van het bestandspad. Als u uw bestanden dus in afzonderlijke mappen heeft georganiseerd, worden deze mapnamen aan uw dataset toegevoegd. Als uw dataset zich bijvoorbeeld in de map 'year=2024' bevindt, wordt er een kolom aan uw dataset toegevoegd met 'year' als header en '2024' als waarde(n).

Voor onze beginners handleiding laten we deze opties staan zoals we ze hebben gevonden, net als de velden eronder. Omdat we één klein CSV-bestand kopiëren, is het instellen van het maximale aantal gelijktijdige verbindingen (Max concurrent connections) niet nodig. Als u echter data uit meerdere bronnen naar één sink kopieert, zult u dit mogelijk zo moeten installen dat de kopieeractiviteit het maximale aantal gelijktijdige verbindingen van het data storage niet overschrijdt. Het instellen van de begin- en eindtijd is ook niet nodig, omdat we het bestand direct willen kopiëren. Klik op 'next'.

Als alles goed is gegaan, heeft Azure automatisch het juiste bestandsformaat gedetecteerd. In ons geval is het een CSV-bestand, dus het bestandsformaat is 'DelimitedText'. Als dit niet automatisch is gebeurd, kunt u handmatig het juiste bestandsformaat selecteren of op de knop 'Detect text format' klikken. Mogelijk zult u ook handmatig de juiste Column delimiter (kolom scheidingsteken) moeten selecteren. Als uw bronbestand een CSV-bestand met koptekst is, zorg er dan voor dat u de optie 'First row as header' selecteert. In ons geval kiezen we geen Compression type (bzip2, gzip, deflate, ZipDeflate, TaGZip of tar), omdat ons bestand niet gecomprimeerd dient te worden. Klik op 'next'.

Net zoals we voor de gegevensbron hebben gedaan, zullen we ook voor de bestemming een type moeten kiezen en een verbinding moeten opzetten. In feite geven we hier op waar we ons CSV-bestand naartoe willen kopiëren en via welke verbinding. Bij Destination type selecteert u 'Azure SQL Database'. Klik vervolgens op 'New connection'.

Net als bij het opzetten van een verbinding met ons Azure Storage Account, zullen we een naam moeten verzinnen voor onze nieuwe verbinding, alsmede een Integration Runtime (IR). Vervolgens zullen we ook een versie voor onze connector moeten specificeren. Sommige mensen hebben aangegeven dat ze tijdens het kopiëren een foutmelding kregen wat kon worden opgelost door hier 'Legacy' te kiezen. Voorlopig kiezen we voor 'Recommended', met de mogelijkheid om dit te veranderen naar 'Legacy' als we een fout tegenkomen.

Net als voorheen zullen we moeten specificeren hoe we onze database moeten selecteren. In ons geval doen we dat via ons abonnement, waarna we onze server en onze database kunnen selecteren.

Vervolgens zullen we een Authentication type (verificatietype) moeten kiezen. We hebben hier de keuze uit User Assigned Managed Identity en System Assigned Managed Identity. Een Managed Identity is een veilige manier om te authenticeren bij Azure-services zonder dat u hoeft te rommelen met inloggegevens (gebruikersnamen, wachtwoorden en dergelijke). In principe wordt er een identiteit gecreëerd die wordt gebruikt voor authenticatie en toegang tot de Azure SQL Database. User Assigned Managed Identities worden onafhankelijk van de Azure-resource gemaakt, zodat deze kan worden gebruikt met meerdere Azure-resources, en zelfs tussen resourcegroepen of Azure-abonnementen. System Assigned Managed Identities zijn daarentegen gebonden aan de specifieke Azure-resource. Dus als deze is gekoppeld aan uw Azure SQL Database en u besluit uw database te verwijderen, wordt uw identiteit ook verwijderd. Voor deze beginnershandleiding kiezen we voor 'System Assigned Managed Identity'.

Er wordt een Managed identity name gegenereerd en aan ons toegewezen, welke u daaronder te zien krijgt. Deze dient u te kopiëren (wat u kunt doen door op icoontje ernaast te klikken) en in Kladblok of iets soortgelijks te plakken, omdat we het nodig zullen hebben om een ​​verbinding tot stand te brengen in Azure Data Studio.

Opmerking: houd Azure Data Factory Studio open op de plek waar u uw Managed identity name heeft gekopieerd. (Zie het plaatje hierboven.)

U zult nu Azure Data Studio moeten opstarten. Dit is het programma dat u op uw computer heeft geïnstalleerd tijdens de Azure voor Beginners handleiding voor het aanmaken voor het aanmaken van een Azure SQL Database. Mocht u Azure Data Studio nog niet hebben geïnstalleerd, volg dan het voorgenoemde deel van de handleiding en start Azure Data Studio op.

Maak in Azure Data Studio verbinding met de server die u heeft aangemaakt tijdens het Azure SQL Server-gedeelte van de beginnershandleiding. U kunt dit doen door met de rechtermuisknop op de server te klikken in het serversvenster en 'Edit Connection' te selecteren.

Zorg ervoor dat u, zoals u eerder ook heeft gedaan, de juiste servernaam heeft ingevoerd. (Als u bent vergeten waar u deze kunt vinden, scroll dan naar het einde van onze handleiding voor het maken van een Azure SQL Database.)

Nadat u succesvol verbinding heeft gemaakt met uw server, ziet u een groen rondje naast het SQL Database-icoon.

We zullen nu onszelf moeten toevoegen als eigenaar van de database. Om dit te doen, zullen we een nieuwe query moeten maken.

Om een foutmelding te voorkomen, zorg ervoor dat u de juiste database heeft geselecteerd. Dit is de databasenaam welke u heeft bedacht tijdens het aanmaken van een Azure SQL Database.

Kopieer en plak de onderstaande SQL-code in het queryveld en verander het 'your managed identity' gedeelte met uw daadwerkelijke managed identity name. (Welke u als het goed is in uw kladblok heeft geplakt.) Dit moet één woord zijn, zonder spaties ervoor, erin of erna. Verwijder de vierkante haakjes niet. Klik op 'Run' in de linkerbovenhoek nadat u de query heeft geplakt en de managed identity name heeft gewijzigd.

CREATE USER [your managed identity name] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [your managed identity name]

In ons geval zal dat er uitzien zoals hieronder op de plaatje te zien is. In uw situatie zal 'yourdatafactoryname' uiteraard uw eigen managed identity name zijn.

Met de eerste regel van de SQL-code maakt u een nieuwe gebruiker aan in Azure SQL Database, welke is gekoppeld aan uw managed identity name. De tweede regel van de SQL-code voegt de genoemde gebruiker toe aan de db_owner-rol, die u volledige controle geeft over alle aspecten van de database, inclusief de mogelijkheid voor het maken van tabellen, views en opgeslagen procedures, en het beheren van machtigingen. Deze SQL-code zorgt ervoor dat Azure Data Factory de benodigde toegang heeft om de gegevens in uw CSV naar Azure SQL Database te kopiëren.

Mocht u de foutmelding 'Cannot alter the 'db_owner' role, because it does not exist or you do not have permission' te zien krijgen, dan heeft u mogelijk de verkeerde database geselecteerd.

Nadat de SQL-code succesvol is uitgevoerd, zult u een soortgelijke melding te zien krijgen als hieronder op het plaatje te zien is.

Nu we onszelf als db_owner hebben toegevoegd aan Azure SQL Database keert u terug naar Azure Data Factory Studio (in uw browser), maar houdt u Azure Data Studio open en verbonden met uw server. We zijn gebleven bij het scherm waar u uw managed identity name heeft gekopieerd (zie onderstaande afbeelding). Hier gaan we verder en voltooien we het proces van het kopiëren van uw bestand naar uw Azure SQL Database.

Daaronder heeft u de mogelijkheid om Always Encrypted in te schakelen, een functie die is ontworpen om gevoelige gegevens te beschermen. U hebt ook de keuze om Encrypt verplicht of optioneel te maken, wat in principe bepaalt of u de gegevensoverdracht tussen uw Azure Storage Account en uw Azure SQL Database wilt versleutelen middels een TLS-versleuteling (Transport Layer Security). Ook heeft u de mogelijkheid om Trust server certificate in te schakelen, welke verifieert of het certificaat van de server wordt vertrouwd, om bescherming te bieden tegen man-in-the-middle-aanvallen. In het veld eronder kunt u de hostnaam opgeven die moet worden gebruikt om het servercertificaat te valideren. Deze kan verschillen van de daadwerkelijke servernaam waarmee u verbinding maakt. Het is ook mogelijk om Additional connection properties (extra verbindingseigenschappen) toe te voegen, om de verbinding te verfijnen via toevoegingen zoals connectTimeout, welke de wachttijd instelt voordat er een verbindingstime-out optreedt, of om Annotations toe te voegen, die extra informatie aan uw verbindingsconfiguratie kunnen toevoegen.

Voor onze beginnershandleiding laten we al deze instellingen ongewijzigd en gaan we controleren of de verbinding met onze Azure SQL Database werkt. Klik hiervoor rechtsonder op 'Test connection'. U zal het bericht Connection successful te zien moeten krijgen. Klik vervolgens op 'Create' om de verbinding tot stand te brengen.

Nadat we het Destination type hebben opgegeven en de verbinding hebben gemaakt, zullen we nu de bestemming in onze Azure SQL Database moeten opgeven. U zult twee lege velden te zien krijgen waarin u iets zult moeten invoeren. Het linker veld is het zogeheten schema, een container voor (onder andere) uw tabellen. Met dit schema kunt u gerelateerde objecten ordenen en groeperen, net zoals de folders op uw computer. Net als folders hebben schema's ook bepaalde regels. Zo kunt u bijvoorbeeld niet twee objecten met dezelfde naam in één schema hebben. U kunt echter wél twee objecten met dezelfde naam hebben als ze tot verschillende schema's behoren. U kunt dus niet twee keer dbo.shipping hebben, maar u kunt wel dbo.shipping + etl.shipping hebben.

In de praktijk reflecteren schema’s ook vaak de fase waarin de data zich bevindt. Bijvoorbeeld:

• Het begint vaak met het raw schema, waar de data worden opgenomen en opgeslagen.

• Het kan worden gevolgd door het schema samp (sampling), waarbij een subset van de data uit de vorige fase wordt gemaakt om te worden gebruikt voor initiële tests in dev (Development and Testing).

• Een ander veelvoorkomend schema is staging, waarbij de data uit raw of samp verder wordt verwerkt, waarna het in het etl schema wordt geladen, waarin de data wordt getransformeerd, schoongemaakt en gelogd.

• De verwerkte data wordt in het dbo (Database Owner) schema geladen, hoewel sommige bedrijven misschien de voorkeur geven aan een andere naam, zoals prod of final.

• Ten slotte wordt data mogelijk in het archive schema geladen, waarin het als historische data zal worden bewaard.

Voor onze beginnershandleiding gebruiken we dbo als schema, omdat dit het standaardschema is voor Azure SQL Database (en SQL Server). In het tweede veld vullen we ‘shippers’ in, wat de naam van onze tabel in Azure SQL Database zal worden. Klik op 'next' nadat u de namen voor uw schema en tabel heeft ingevuld.

Vervolgens zullen we column mappings moeten configureren, wat lijkt op een "matching"-proces tussen het CSV-bestand in uw Azure Storage Account en de (nog te maken) tabel in uw Azure SQL Database. Aangezien we de kolommen uit ons CSV-bestand willen kopiëren naar een tabel in onze Azure SQL Database, zullen we Azure Data Factory in principe moeten vertellen hoe de kolommen in het CSV-bestand moeten worden verbonden met de kolommen in de Azure SQL Database tabel.

• Als we naar de onderstaande afbeelding kijken, zien we twee kolommen, elk bestaande uit drie rijen. De kolommen aan de linkerkant vertegenwoordigen ons CSV-bestand, terwijl de kolommen aan de rechterkant de tabel in onze Azure SQL Database vertegenwoordigen.

• In de kolom aan de linkerkant bevat het veld bovenaan de waarde shipperid. Dit is de eerste kolom in ons CSV. Daarnaast zien we de waarde String, wat het data type is. (Het betekent in feite dat de waarden in de kolom shipperid als tekst worden behandeld.)

• Op dezelfde rij, maar in de rechterkolom, zien we ook de waarde shipperid, wat betekent dat de shipperid kolom uit ons CSV-bestand zal worden gekopieerd naar een kolom in onze Azure SQL Database met dezelfde naam.

• Controleer op dezelfde manier of alle andere kolommen op de juiste manier zullen worden gekopieerd van uw CSV-bestand naar uw Azure SQL Database.

Tijdens dit proces heeft u ook de mogelijkheid om enkel bepaalde selectie kolommen uit ons CSV-bestand te kopiëren, of om namen en gegevenstypen te wijzigen. Voor onze beginnershandleiding laten we echter alles zoals het is.

Daaronder ziet u een veld om een ​​Pre-copy-script in te voeren. Hierin kunt u een script schrijven om de gegevens op te schonen voordat u de nieuwe gegevens kopieert. Voor onze beginnershandleiding laten we dit veld leeg. Klik op 'next' om door te gaan naar het onderdeel Settings.

• Eerst zult u een naam moeten verzinnen voor de nieuwe data kopieertaak. Ook heeft u daaronder de mogelijkheid een beschrijving toe te voegen.

• Daaronder heeft u de mogelijkheid om Data consistency verification in te schakelen, waardoor Azure de data in uw CSV-bestand kan vergelijken met de data in uw Azure SQL Database. Als er inconsistenties worden gevonden, heeft u de mogelijkheid om het kopiëren af ​​te breken of door te gaan, op basis van uw selectie bij Fault tolerance.

• U heeft ook de optie om logging in te schakelen, waarbij uw gekopieerde bestandsnamen worden geregistreerd in een Copy activity. Dit kan handig zijn als u plotseling onverwachte bestanden/tabellen op uw bestemming aantreft, of als de bestanden op uw bestemming verschillen van de bestanden die u van de bron hebt gekopieerd. Als dat het geval is kunt u vervolgens in het log controleren of er wellicht door een andere applicatie wijzigingen zijn aangebracht.

• Het inschakelen van staging, waarmee u de data eerst naar een interim staging opslag (Azure Blob storage of Azure Data Lake Storage Gen2) kunt kopiëren voordat u deze naar de uiteindelijke bestemming kopieert. Dit kan handig zijn als u data uit veel verschillende bronnen wilt importeren, als u vanwege veiligheidsoverwegingen bepaalde poorten in uw firewall niet kunt openen), of als u data van een on-premises gegevensopslag naar Azure wilt kopiëren via een langzaam netwerk. (U kunt uw data op locatie comprimeren en naar de staging opslag verplaatsen, waar uw data kan worden gedecomprimeerd en naar de bestemming worden verplaatst.)

• Onder het kopje advanced kunt u de Maximum data integration unit opgeven. Een Data Integration Unit (DIU) is een maatstaf voor de rekenkracht (CPU, geheugen en netwerkbronnen) die door Azure Data Factory wordt gebruikt om (o.a.) het kopiëren van uw data uit te voeren. Hier kunt u de bovengrens opgeven. Het maximale aantal DIU's is afhankelijk van de bron, de bestemming en de grootte en het aantal bestanden dat u kopieert. We kopiëren bijvoorbeeld een CSV-bestand van ons Azure Storage-account (file store) naar onze Azure SQL Database (non-file store), waarvoor er 4 DIU's beschikbaar zijn voor het kopiëren van één bestand.

• We kunnen ook Degree of copy parallelism instellen, waarmee we het aantal parallelle threads instellen welke worden gebruikt om tijdens het kopiëren de bron te lezen (CSV-bestand) en naar de bestemming te schrijven (Azure SQL Database). Wanneer we deze verhogen, genereert Azure Data Factory meer parallelle threads om het kopiëren uit te voeren. Deze threads werken simultaan, waarbij stukjes gegevens uit de bron worden gelezen en naar de bestemming worden geschreven.

Voor onze beginnershandleiding laten we, afgezien van onze Task name, al deze velden leeg. Klik op 'next' om door te gaan.

U zult een overzicht te zien krijgen van de keuzes die u tot nu toe heeft gemaakt. Controleer uw keuzes en klik op 'next' om het kopiëren te starten.

Nadat Azure het kopiëren heeft voltooid, ziet u het onderstaande scherm te zien krijgen.

Vervolgens schakelt u over naar Azure Data Studio (het programma niet in uw browser). Nadat u op het 'Refresh' icoontje heeft gedrukt zal onze nieuwe tabel te zien zijn.

U zult nu op gebruikelijke wijze T-SQL-query's kunnen uitvoeren.

U heeft nu met succes data van uw Azure Storage Account naar uw Azure SQL Database gekopieerd. Hiermee is dit deel van de Azure voor Beginners handleiding ten einde gekomen.