Exportieren Sie Dataverse-Daten mit Data Lake und ADF nach SQL

Ich hatte kürzlich die Herausforderung, Dataverse-Daten in eine SQL-Datenbank zu exportieren. Am Ende habe ich einen Azure Data Lake Gen2 in Kombination mit einer Azure Data Factory (ADF) verwendet. Während des Einrichtungsprozesses bin ich auf einige Probleme gestoßen. Wir werden diese in diesem Blogbeitrag besprechen.

Teile der gezeigten Implementierung befinden sich zum Zeitpunkt der Erstellung dieses Artikels in der Vorschau.

Hintergrund

Einer meiner Kunden verwendet immer noch den veralteten Data Export Service (DES), um alle Zeilen aus bestimmten Tabellen in eine Azure SQL-Datenbank zu kopieren. Diese Informationen werden dann sowohl von Integrations- als auch von Reporting-Tools verwendet. Die Idee eines solchen Setups, anstatt Dataverse direkt anzufordern, besteht darin, die Anzahl der Anfragen an die Dataverse-APIs zu verringern, um sowohl die Leistung für die Benutzer zu steigern als auch die Drosselungs- und API-Grenzen von Dataverse zu respektieren.

Da DES veraltet ist und in der letzten Novemberhälfte 2022 sowohl das „End of Life“ als auch das „End of Support“ erreichen wird (siehe MS-Ankündigung), mussten wir dieses Setup durch etwas anderes ersetzen.

Ziellösung

Microsoft empfiehlt die Verwendung von Azure Synapse Analytics und die Einspeisung von Daten aus Dataverse mithilfe des Azure Synapse-Links für Dataverse. Es gibt sogar ein Playbook, das genau beschreibt, wie man von DES zu Synapse wechselt.

Die Kehrseite davon sind die Kosten für Azure Synapse Analytics sowie die Empfehlung, bei Synapse zu „enden“. Dies würde bedeuten, dass viele Integrationen in kurzer Zeit geändert werden müssten.

Wie Sie auf der Seite des Azure Synapse-Links für Dataverse sehen können, exportiert dies entweder nach Azure Synapse oder nur nach Data Lake Gen2. Darauf zielt unsere Lösung ab. Wenn sich die Daten im Data Lake befinden, verschieben wir sie mithilfe von ADF in die bereits vorhandene Azure SQL-Datenbank (derzeit von DES verwendet).

Das bedeutet, dass wir nur ändern, wie Daten in der Datenbank gespeichert werden, aber die Struktur sowie die Datenbank selbst bleiben genau gleich. Das bedeutet auch, dass sich zum jetzigen Zeitpunkt keine Integration ändern muss.

Die langfristige Idee ist, alle Integrationen so zu verschieben, dass sie den Data Lake direkt verwenden und das SQL überspringen.

Probleme

Während der Implementierung hatte ich die folgenden Probleme, die nicht in der Dokumentation behandelt wurden.

Kollation

Unser Azure-Datenbankserver wurde mit einer Sortierung eingerichtet, während die von uns verwendete Datenbank eine andere Sortierung hatte. Ein Teil des neuen Prozesses besteht darin, Änderungen in der Dataverse-Tabellenstruktur mit der Exportdatenbank zu synchronisieren. Dazu vergleicht das Template das eingehende Modell mit der Systemtabelle “INFORMATION_SCHEMA.COLUMNS”. Wenn die erwähnte Systemtabelle und die von uns verwendete Datenbank unterschiedliche Sortierungen haben, schlägt jeder gleiche Vergleich fehl. Das ist eher ein Problem, das wir selbst verursacht haben, aber trotzdem musste ich es im neuen Setup handhaben.

Tabellenpräfix

Bei der Konfiguration von DES hatte man die Möglichkeit, ein Tabellenpräfix zu definieren. Wenn Sie beispielsweise „Vertrieb“ als Präfix wählen und die Kontakttabelle von Dataverse mit DES mit SQL synchronisieren, heißt die Tabelle in der Exportdatenbank „dbo.sales_contact“ anstelle von „dbo.contact“.
Die ADF-Vorlage von MS berücksichtigt keine Präfixe.

Abzug

Ich hatte massive Probleme mit meiner ADF-Pipeline, die alle 15 Minuten über 50 Mal ausgelöst wurde. Das lag im Grunde daran, dass ich die Dokumentation nicht sorgfältig genug gelesen habe. Stellen Sie also sicher, dass Sie es tun!

Die Lösungen dazu sind im Einrichtungsteil dieses Artikels enthalten.

Konfiguration

Lassen Sie uns in das Setup eintauchen und sehen, was wir tun müssen.

Die Dokumentation von Microsoft zu diesem Thema ist eigentlich ganz gut. Ich werde daher auf die Artikel verlinken, in denen das betreffende Setup erklärt wird, und dem Beitrag nur meine Ergänzungen hinzufügen.

Erstellen Sie Data Lake Gen2

Wichtig! Stellen Sie sicher, dass Sie Ihren Data Lake Gen2 gleichzeitig erstellen Azure-Mandant und Azure-Region wie Ihr Dataverse installiert ist. Andernfalls können Sie die Verknüpfung zwischen diesen beiden nicht herstellen. Wenn Sie in das Maker-Portal gehen und versuchen, einen neuen Link zu erstellen (Dataverse -> Azure Synapse Link), wird Ihnen angezeigt, wo sich Ihre Dataverse-Umgebung befindet.

Erstellen Sie eine neue Verknüpfung

Abgesehen von der erwähnten Region ist es einfach, einen solchen Data Lake zu erstellen. Die Dokumentation ist ziemlich eindeutig. Entweder erstellen Sie direkt ein neues Speicherkonto mit den Data Lake Gen2-Funktionen oder Sie aktualisieren ein vorhandenes Speicherkonto.

Das erste Speicherkonto, das ich für diese Implementierung erstellt habe, habe ich fälschlicherweise ohne die Data Lake Gen2-Funktionen erstellt, was bedeutete, dass ich es aktualisieren musste, obwohl ich es gerade erst erstellt hatte. Das waren ein paar zusätzliche Schritte, die aber nur etwa 5 Minuten länger gedauert haben.

Für diesen Teil werden verschiedene Dokumentationen erstellt. Es gibt eine allgemeine Dokumentation zum Verknüpfen von Dataverse mit einem Data Lake mithilfe von Azure Synapse Link for Dataverse. Aus dieser Dokumentation sind die Voraussetzungen wichtig!

Damit unsere ADF-Pipeline funktioniert, müssen Sie diese Dokumentation genau befolgen (einschließlich des hackigen Zusatzes zur URL in der Mitte der Linkerstellung).

Die Dokumentation, die Sie befolgen sollten, schlägt vor, dass Sie über einen Azure Synapse-Arbeitsbereich verfügen müssen. Es funktioniert gut, wenn nur ein Data Lake verwendet wird. Aktivieren Sie beim Erstellen des Links nur nicht das erste Kontrollkästchen. Es kann sein, dass Sie die Eingabetaste drücken müssen (wodurch die Site neu geladen wird), wenn Sie den zusätzlichen Parameter zur URL hinzufügen, wie in der Dokumentation beschrieben.

Es besteht die Möglichkeit, Ihre Azure Synapse Link for Dataverse-Konfiguration in Ihre Lösung aufzunehmen. Auf diese Weise können Sie es als Teil Ihrer üblichen Bereitstellung bereitstellen. Weiterlesen.

ADF erstellen

Folgen Sie zunächst der Dokumentation zum Einrichten der Vorlage für dieses Szenario.

Machen Sie nicht den gleichen Fehler wie ich und vergessen Sie, „/model.json“ zum Feld „Blob-Pfad endet mit“ im Trigger der ADF-Pipeline hinzuzufügen.

Jetzt beginnt der Spaß und ich musste tatsächlich ein paar Sachen reparieren!

Kollation

Das erste Problem, das ich hatte, war die Sortierung. Um dies zu beheben, musste ich das Skript ändern, das im Schritt “CreateTable” der ADF-Pipeline ausgeführt wird. Klicken Sie dazu einfach auf den Schritt „CreateTable“ innerhalb von „ForEach“, wenn Sie sich in Ihrer Pipeline im DataFactory-Studio befinden. Unten haben Sie einige Registerkarten, auf denen Sie zu “Einstellungen” gehen. Durch Klicken in das Skript öffnet sich ein Editor.

Skript öffnen

„Profi-Tipp“: Kopieren Sie das Skript in einen Editor Ihrer Wahl, nehmen Sie dort Ihre Änderungen vor und kopieren Sie das geänderte Skript zurück in das ADF-Portal. Das macht die Bearbeitung wesentlich einfacher als im Portal-Editor.

Sie müssen grundsätzlich “COLLATE “, in unserem Fall war es „COLLATE Finnish_Swedish_CI_AS“, wenn eine temporäre Tabelle erstellt oder eine Gleichheitsoperation für ein Feld von INFORMATION_SCHEMA.COLUMNS ausgeführt wird.

Dies endet im Wesentlichen in folgendem Code/Skript:

Declare @attributes nvarchar(max) =replace(replace('@{item().attributes}', '',''), 'cdm:traits', 'traits') ;
declare @entity nvarchar(200) = '@{item().name}';

declare @metadata table (
Name nvarchar(200) COLLATE Finnish_Swedish_CI_AS,
datatype NVARCHAR(50) COLLATE Finnish_Swedish_CI_AS,
maxLength int,
precision int,
scale int,
datatypeSQL nvarchar(100) COLLATE Finnish_Swedish_CI_AS
);

insert into @metadata
select   
 x.Name,  x.dataType,  x.maxLength,  x.precision,  x.scale,  
 case    
 when x.dataType="nvarchar" and x.maxLength  0 then 'nvarchar(' + convert(nvarchar(10), x.maxLength) + ')'
 when x.datatype="decimal" then 'decimal(' + convert(nvarchar(10), x.precision) + ',' + convert(nvarchar(10), x.scale)+ ')'  
 else x.datatype end as datatypeSQL  
 from ( 
 select Name,   
 case datatype     
 when 'guid' then 'UNIQUEIDENTIFIER'    
 when 'string' then 'nvarchar'    
 when 'int64' then 'bigInt'    
 when 'boolean' then 'bit'   
 when 'double' then 'real'    
 else datatype end as dataType,    
 case    when datatype="string" and  maxLength > 4000 then -1    
 when datatype="string"  then maxLength end as maxLength,   
 case  when datatype="decimal" then precision end as precision,   
 case  when datatype="decimal" then scale end as scale   
 FROM OPENJSON(@attributes)  WITH (  name nvarchar(200) '$.name',  datatype NVARCHAR(50) '$.dataType' , maxLength int '$.maxLength' ,precision int '$.traits[0].arguments[0].value' ,scale int '$.traits[0].arguments[1].value')  )x ; 
 
 
Declare @CreateTableDDL nvarchar(max) = (SELECT 
'IF OBJECT_ID(''dbo.' + @entity + ''',''U'') IS NULL 
create table ' + @entity + '(' + STRING_AGG(CONVERT(NVARCHAR(max), + '[' + name + '] ' +  datatypeSQL) , ',') + ' 
, CONSTRAINT ' + @entity + '_ID PRIMARY KEY CLUSTERED (Id)' + ')' 
from @metadata);

 execute sp_executesql  @CreateTableDDL;

  -- add Columns
Declare @AlterTableAddColumnDDL nvarchar(max) = (SELECT 
'alter table ' + @entity + ' ADD ' + STRING_AGG(CONVERT(NVARCHAR(max), + '[' + name + '] ' +  datatypeSQL), ',')
from @metadata m
left join INFORMATION_SCHEMA.COLUMNS c on 
c.TABLE_NAME COLLATE Finnish_Swedish_CI_AS = @entity and TABLE_SCHEMA = 'dbo'
and c.COLUMN_NAME COLLATE Finnish_Swedish_CI_AS = m.Name
where c.COLUMN_NAME COLLATE Finnish_Swedish_CI_AS is null);

execute sp_executesql @AlterTableAddColumnDDL;

--select @AlterTableAddColumnDDL;

Declare @AlterTableAlterColumnDDL nvarchar(max) = (SELECT 
  STRING_AGG(CONVERT(NVARCHAR(max), + 'alter table ' + @entity + ' ALTER COLUMN ' + '[' + name + '] ' +  datatypeSQL), ';')
from @metadata m
left join  INFORMATION_SCHEMA.COLUMNS c on 
c.TABLE_NAME COLLATE Finnish_Swedish_CI_AS = @entity and TABLE_SCHEMA = 'dbo'
and c.COLUMN_NAME COLLATE Finnish_Swedish_CI_AS = name
where c.COLUMN_NAME is not null and (c.Data_type COLLATE Finnish_Swedish_CI_AS != datatype or c.CHARACTER_MAXIMUM_LENGTH != maxLength or NUMERIC_PRECISION != precision or NUMERIC_SCALE != scale));

execute sp_executesql @AlterTableAlterColumnDDL;

--select @AlterTableAlterColumnDDL;

Declare @CreateLogTableDDL nvarchar(max) =
'IF OBJECT_ID(''dbo.DeleteLog'',''U'') IS NULL 
CREATE TABLE [dbo].[DeleteLog](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[EntityName] [nvarchar](64) NOT NULL,
	[RecordId] [nvarchar](64) NOT NULL,
	[SinkDeleteTime] [datetime] NOT NULL,
	[VersionNumber] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]';

execute sp_executesql  @CreateLogTableDDL;

Präfix

Es wurde etwas mehr Arbeit geleistet, um das Präfixproblem zu beheben. In unserem Fall haben wir „sales“ als Präfix in DES verwendet. Da wir unsere DES-Datenbank genau so haben wollten, wie diese gehandhabt werden musste.

Parameter

Als erstes habe ich der Pipeline einen zusätzlichen Parameter, Präfix, hinzugefügt.

Parameter hinzufügen
Parameter hinzufügen
Abzug

Als nächstes würde ich den neuen Parameter im Trigger verwenden und ihn statisch auf “sales_” setzen.

Auslöser bearbeiten
Präfix festlegen
Präfix festlegen
CreateTable-Skript

Die dritte Sache, die ich geändert habe, ist das CreateTable-Skript, das wir beim Korrigieren der Sortierung besprochen haben. Ich habe die zweite Zeile so geändert, dass sie das Präfix enthält

declare @entity nvarchar(200) = '@{pipeline().parameters.prefix}' + '@{item().name}';
Datenfluss

Der letzte Teil, der geändert wurde, war der Datenfluss. Dieser Teil besteht aus verschiedenen Schritten.

Zuerst müssen wir auch dem Datenfluss einen zusätzlichen Parameter, Präfix, hinzufügen.

Parameter zum Datenfluss hinzufügen
Parameter zum Datenfluss hinzufügen

Dieser Parameter muss in zwei verschiedenen Schritten innerhalb des Datenflusses verwendet werden.

Datenflussänderungen markiert
Datenfluss

Im Schritt getMaxVersion müssen wir das Präfix am Ende der Abfrage hinzufügen, damit die Tabelle, die wir abfragen, den richtigen Namen erhält.

Die Abfrage sollte so aussehen.

"select max(versionnumber) as maxversion from dbo." + $prefix + $entity
GetMaxVersion ändern
GetMaxVersion ändern

Im Schritt „AzureSQLDB“ müssen wir „$prefix +“ am Anfang des Felds „Tabellenname“ auf der Registerkarte „Einstellungen“ hinzufügen. Dies gibt dem Skript wiederum den richtigen Tabellennamen und berücksichtigt das Präfix, das wir bereits in der SQL haben.

Ändern Sie AzureSQLDB
Ändern Sie AzureSQLDB

Zu guter Letzt müssen wir beim Aufruf des Datenflusses den Parameter des Datenflusses mit dem Wert des Parameters der Pipeline setzen.

Dazu gehen wir zurück zu unserer Pipeline, doppelklicken auf die „ForEachEntity“ und wählen den Schritt „DataverseToSQL“ aus (möglicherweise müssen Sie ihn zweimal anklicken). Auf der Registerkarte Parameter fügen wir dem Feld des Präfixwerts den folgenden Ausdruck hinzu.

@pipeline().parameters.prefix
OpenForEach
OpenForEach
Präfix hinzufügen
Präfix hinzufügen

Veröffentlichen

Nachdem wir nun alle Änderungen vorgenommen haben, müssen wir nur noch alle unsere Änderungen veröffentlichen, damit sie wirksam werden.

Veröffentlichen
Veröffentlichen

Fazit

Das neue Setup ist nicht so schwer zu erreichen, wenn Sie wissen, wo die Probleme liegen könnten und wie Sie diese beheben können. Die Dokumentation zu diesem Thema ist besser als erwartet, auch wenn es manchmal etwas schwierig ist, ihr zu folgen.

Das Skript, das als Teil der ADF-Vorlage geliefert wird, ist nicht sehr optimal. Es gibt vieles, was man verbessern oder sogar anders machen könnte. Ich könnte ein Update geben, wenn ich Zeit hatte, es umzugestalten.

Ich hoffe, dieser Artikel war hilfreich. Bitte hinterlassen Sie mir einen Kommentar oder kontaktieren Sie mich, wenn Sie weitere Fragen oder Feedback haben.

Der Beitrag Export Dataverse data to SQL using Data Lake and ADF erschien zuerst auf Benedikts Power Platform Blog.

.

Author: admin

Leave a Reply

Your email address will not be published. Required fields are marked *