Sicheres Laden von Daten mithilfe von Synapse SQL

In diesem Artikel werden sichere Authentifizierungsmechanismen für die COPY-Anweisung erläutert und Beispiele dazu bereitgestellt. Die COPY-Anweisung ist die flexibelste und sicherste Methode zum Massenladen von Daten in Synapse SQL.

Unterstützte Authentifizierungsmechanismen

In der nachstehenden Matrix werden die unterstützten Authentifizierungsmethoden für jeden Dateityp und jedes Speicherkonto beschrieben. Dies gilt für den Quellspeicherort und den Speicherort der Fehlerdatei.

	CSV	Parquet	ORC
Azure Blob Storage	SAS/MSI/SERVICE PRINCIPAL/KEY/AAD	SAS/KEY	SAS/KEY
Azure Data Lake Gen2	SAS/MSI/SERVICE PRINCIPAL/KEY/AAD	SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD	SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD

1: Der .blob-Endpunkt (.blob.core.windows.net) unter Ihrem externen Speicherortpfad ist für diese Authentifizierungsmethode erforderlich.

2: Der .dfs-Endpunkt (.dfs.core.windows.net) unter Ihrem externen Speicherortpfad ist für diese Authentifizierungsmethode erforderlich.

A. Speicherkontoschlüssel mit LF als Zeilenabschlusszeichen (Neue-Zeile-Zeichen im Unix-Stil)

--Note when specifying the column list, input field numbers start from 1
COPY INTO target_table (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<secret>'),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

Wichtig

• Verwenden Sie den Hexadezimalwert (0x0A) zur Angabe des Zeilenvorschub-/Neue-Zeile-Zeichens. Beachten Sie, dass die COPY-Anweisung die Zeichenfolge „\n“ als „\r\n“ (Wagenrücklaufzeichen, Zeilenumbruchzeichen) interpretiert.

B. Shared Access Signatures (SAS) mit CRLF als Zeilenabschlusszeichen (Neue-Zeile-Zeichen im Windows-Stil)

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSFSYsz4AkN'),
    ,ROWTERMINATOR='\n'-- COPY command automatically prefixes the \r character when \n (newline) is specified. This results in carriage return newline (\r\n) for Windows based systems.
)

Wichtig

Geben Sie ROWTERMINATOR nicht als „\r\n“ an. Dies wird nämlich als „\r\r\n“ interpretiert und kann zu Analyseproblemen führen. Der COPY-Befehl stellt beim Angeben von „\n“ (Zeilenumbruch) automatisch das „\r“-Zeichen voran. Dies führt zu einem Wagenrücklauf-Zeilenumbruch (\r\n) auf Windows-basierten Systemen.

C. Verwaltete Identität

Eine Authentifizierung der verwalteten Identität ist erforderlich, wenn Ihr Speicherkonto an ein VNET angefügt ist.

Voraussetzungen

1. Installieren Sie Azure PowerShell. Siehe Installieren von PowerShell.
2. Falls Sie über ein universelles Speicherkonto V1 oder ein Blobspeicherkonto verfügen, müssen Sie zuerst ein Upgrade auf Version 2 des universellen Speicherkontos durchführen. Weitere Informationen finden Sie im Artikel zum Durchführen eines Upgrades auf ein Speicherkonto vom Typ „Universell V2“.
3. Im Einstellungsmenü Firewalls und virtuelle Netzwerke des Azure Storage-Kontos muss die Option Vertrauenswürdigen Microsoft-Diensten den Zugriff auf dieses Speicherkonto erlauben aktiviert sein. Weitere Informationen finden Sie unter Konfigurieren von Azure Storage-Firewalls und virtuellen Netzwerken.

Schritte

1. Wenn Sie über einen eigenständigen dedizierten SQL-Pool verfügen, registrieren Sie Ihren SQL-Server mithilfe von PowerShell bei Microsoft Entra ID:

   Connect-AzAccount
   Select-AzSubscription -SubscriptionId <subscriptionId>
   Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
   

   Dieser Schritt ist für dedizierte SQL-Pools in einem Synapse-Arbeitsbereich nicht erforderlich. Die vom System zugewiesene verwaltete Identität (SA-MI) des Arbeitsbereichs ist ein Mitglied der Synapse-Administratorrolle und verfügt daher über erhöhte Rechte für die dedizierten SQL-Pools des Arbeitsbereichs.

2. Erstellen Sie ein universelles v2-Speicherkonto. Weitere Informationen finden Sie unter Erstellen eines Speicherkontos.

   Hinweis

   • Falls Sie über ein universelles Speicherkonto (v1) oder ein Blobspeicherkonto verfügen, müssen Sie zuerst das Upgrade auf Version 2 durchführen. Weitere Informationen finden Sie unter Durchführen eines Upgrades auf ein Speicherkonto vom Typ „Allgemein v2“.
   • Weitere Informationen zu Problemen mit Azure Data Lake Storage Gen2 finden Sie unter Bekannte Probleme mit Azure Data Lake Storage Gen2.

3. Wählen Sie unter Ihrem Speicherkonto die Option Zugriffssteuerung (IAM) aus.

4. Wählen Sie Hinzufügen>Rollenzuweisung hinzufügen aus, um den Bereich „Rollenzuweisung hinzufügen“ zu öffnen.

5. Weisen Sie die folgende Rolle zu. Ausführliche Informationen finden Sie unter Zuweisen von Azure-Rollen über das Azure-Portal.

   Einstellung	Wert
   Role	Mitwirkender an Storage-Blobdaten
   Zugriff zuweisen zu	SERVICEPRINCIPAL
   Member	Server oder Arbeitsbereich, auf bzw. in dem Ihr bei Microsoft Entra ID registrierter dedizierter SQL-Pool gehostet wird

   

   Hinweis

   Nur Mitglieder mit der Berechtigung „Besitzer“ können diesen Schritt ausführen. Informationen zu verschiedenen integrierten Azure-Rollen finden Sie unter Integrierte Azure-Rollen.

   Wichtig

   Geben Sie die Azure-Rolle „Besitzer von“, „Mitwirkender an“ oder „Leser von“ Speicherblobdaten an. Diese Rollen sind anders als die integrierten Azure-Rollen „Besitzer“, „Mitwirkender“ und „Leser“.

   

6. Jetzt können Sie die COPY-Anweisung zur Angabe von „verwaltete Identität“ ausführen:

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
   WITH (
       FILE_TYPE = 'CSV',
       CREDENTIAL = (IDENTITY = 'Managed Identity'),
   )
   

D. Microsoft Entra-Authentifizierung

Schritte

1. Wählen Sie unter Ihrem Speicherkonto die Option Zugriffssteuerung (IAM) aus.

2. Wählen Sie Hinzufügen>Rollenzuweisung hinzufügen aus, um den Bereich „Rollenzuweisung hinzufügen“ zu öffnen.

3. Weisen Sie die folgende Rolle zu. Ausführliche Informationen finden Sie unter Zuweisen von Azure-Rollen über das Azure-Portal.

   Einstellung	Wert
   Role	„Besitzer von Speicherblobdaten“, „Mitwirkender“ oder „Leser“
   Zugriff zuweisen zu	USER
   Member	Microsoft Entra-Benutzer*in

   

   Wichtig

   Geben Sie die Azure-Rolle „Besitzer von“, „Mitwirkender an“ oder „Leser von“ Speicherblobdaten an. Diese Rollen sind anders als die integrierten Azure-Rollen „Besitzer“, „Mitwirkender“ und „Leser“.

   

4. Konfigurieren Sie die Microsoft Entra-Authentifizierung. Weitere Informationen finden Sie unter Konfigurieren und Verwalten der Microsoft Entra-Authentifizierung mit Azure SQL.

5. Stellen Sie mithilfe von Active Directory eine Verbindung zu Ihrem SQL-Pool her. Dort können Sie jetzt die COPY-Anweisung ohne Angabe von Anmeldeinformationen ausführen:

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
   WITH (
       FILE_TYPE = 'CSV'
   )
   

E. Dienstprinzipalauthentifizierung

Schritte

1. Erstellen einer Microsoft Entra-Anwendung

2. Abrufen der Anwendungs-ID.

3. Abrufen des Authentifizierungsschlüssels.

4. Abrufen des V1 OAuth 2.0-Token-Endpunkts.

5. Zuweisen von Lese-, Schreib- und Ausführungsberechtigungen zu Ihrer Microsoft Entra-Anwendung in Ihrem Speicherkonto

6. Jetzt können Sie die COPY-Anweisung ausführen:

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
   WITH (
       FILE_TYPE = 'CSV'
       ,CREDENTIAL=(IDENTITY= '<application_ID>@<OAuth_2.0_Token_EndPoint>' , SECRET= '<authentication_key>')
       --CREDENTIAL should look something like this:
       --,CREDENTIAL=(IDENTITY= '92761aac-12a9-4ec3-89b8-7149aef4c35b@https://login.microsoftonline.com/72f714bf-86f1-41af-91ab-2d7cd011db47/oauth2/token', SECRET='juXi12sZ6gse]woKQNgqwSywYv]7A.M')
   )
   

Wichtig

Verwenden Sie dazu die Version V1 des OAuth 2.0-Token-Endpunkts.

Nächste Schritte

• Informieren Sie sich im Artikel „COPY-Anweisung“ über alle Details der Syntax.
• Informieren Sie sich im Artikel data loading overview (Übersicht über das Laden von Daten) über bewährte Methoden zum Laden von Daten.