Freigeben über

Tutorial: Laden externer Daten mithilfe einer verwalteten Identität

  • Artikel

In diesem Artikel wird erläutert, wie Sie externe Tabellen erstellen oder Daten aus Azure Data Lake Storage Gen2-Konten (ADLS) mithilfe einer verwalteten Identität erfassen.

Voraussetzungen

Für dieses Tutorial benötigen Sie die folgenden Ressourcen:

  • Ein Azure Data Lake Storage Gen2-Konto (ADLS)
  • Einen Azure Synapse Analytics-Arbeitsbereich und einen dedizierten SQL-Pool

Erteilen von Zugriff auf das Speicherkonto für die Arbeitsbereichsidentität

Jeder Azure Synapse Analytics-Arbeitsbereich erstellt automatisch eine verwaltete Identität, mit der Sie den sicheren Zugriff auf externe Daten über Ihren Arbeitsbereich konfigurieren können. Weitere Informationen zu verwalteten Identitäten für Azure Synapse Analytics finden Sie unter Verwaltete Dienstidentität für Azure Synapse Analytics.

Damit Ihre verwaltete Identität auf Daten in ADLS Gen2-Konten zugreifen kann, müssen Sie ihr Zugriff auf das Quellkonto gewähren. Befolgen Sie diese Schritte, um die richtigen Berechtigungen zu erteilen:

  1. Suchen Sie im Azure-Portal nach Ihrem Speicherkonto.
  2. Wählen Sie Datenspeicher > Container aus, und navigieren Sie zu dem Ordner, in dem die Quelldaten sind, auf die die externe Tabelle Zugriff benötigt.
  3. Wählen Sie Zugriffssteuerung (IAM) aus.
  4. Wählen Sie Hinzufügen > Rollenzuweisung hinzufügen aus.
  5. Wählen Sie in der Liste der Stellenfunktionsrollen Mitwirkender an Speicherblobdaten aus, und wählen Sie Weiter aus.
  6. Wählen Sie auf der Seite Rollenzuweisung hinzufügen die Option + Mitglieder auswählen aus. Der Bereich Mitglieder auswählen wird geöffnet.
  7. Geben Sie den Namen Ihrer Arbeitsbereichsidentität ein. Die Arbeitsbereichsidentität ist identisch mit Ihrem Arbeitsbereichsnamen. Wenn sie angezeigt wird, wählen Sie Ihre Arbeitsbereichsidentität und dann Auswählen aus.
  8. Vergewissern Sie sich auf der Seite Rollenzuweisung hinzufügen, dass die Liste der Mitglieder Ihr gewünschtes Microsoft Entra ID-Konto enthält. Sobald Sie das bestätigt haben, wählen Sie Überprüfen und zuweisen aus.
  9. Überprüfen Sie auf der Bestätigungsseite die Änderungen, und wählen Sie Überprüfen und zuweisen aus.

Ihre Arbeitsbereichsidentität ist jetzt Mitglied der Rolle „Mitwirkender an Speicherblobdaten“ und hat Zugriff auf den Quellordner.

Hinweis

Diese Schritte gelten auch für geschützte ADLS Gen2-Konten, die so konfiguriert sind, dass der öffentliche Zugriff eingeschränkt wird. Weitere Informationen zum Schützen Ihres ADLS Gen2-Kontos finden Sie unter Konfigurieren von Azure Storage-Firewalls und virtuellen Netzwerken.

Erfassen von Daten mithilfe von COPY INTO

Die T-SQL-Anweisung COPY INTO ermöglicht eine flexible Datenerfassung mit hohem Durchsatz in Ihre Tabellen und ist die primäre Strategie zum Erfassen von Daten in Ihre dedizierten SQL-Pooltabellen. COPY INTO ermöglicht Benutzern das Erfassen von Daten von externen Speicherorten, ohne dass sie zusätzliche Datenbankobjekte erstellen müssen, die für externe Tabellen erforderlich sind.

Um die COPY INTO-Anweisung mit einer verwalteten Arbeitsbereichsidentität für die Authentifizierung auszuführen, verwenden Sie den folgenden T-SQL-Befehl:

COPY INTO <TableName>
FROM 'https://<AccountName>.dfs.core.windows.net/<Container>/<Folder>/ '
WITH
(
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    [<CopyIntoOptions>]
);

Hierbei gilt:

  • <TableName> ist der Name der Tabelle, in die Daten erfasst werden sollen.
  • <AccountName> ist Ihr ADLS Gen2-Kontoname.
  • <Container> ist der Name des Containers in Ihrem Speicherkonto, in dem die Quelldaten gespeichert werden.
  • <Folder> ist der Ordner (oder Pfad mit Unterordnern), in dem die Quelldaten in Ihrem Container gespeichert werden. Sie können auch einen Dateinamen angeben, wenn Sie direkt auf eine einzelne Datei verweisen.
  • <CopyIntoOptions> ist die Liste aller anderen Optionen, die Sie der COPY INTO-Anweisung angeben möchten.

Weitere Informationen zur vollständigen Syntax von COPY INTO finden Sie unter COPY INTO (Transact-SQL).

Abfragen von Daten in ADLS Gen2 mithilfe externer Tabellen

Externe Tabellen ermöglichen es Benutzern, Daten aus Azure Data Lake Storage Gen2-Konten (ADLS) abzufragen, ohne zuerst Daten erfassen zu müssen. Benutzer können eine externe Tabelle erstellen, die auf Dateien in einem ADLS Gen2-Container verweist, und sie wie eine gewöhnliche Benutzertabelle abfragen.

Die folgenden Schritte beschreiben den Prozess zum Erstellen einer neuen externen Tabelle, die auf Daten in ADLS Gen2 verweist, wobei eine verwaltete Identität für die Authentifizierung verwendet wird.

Erstellen der erforderlichen Datenbankobjekte

Für externe Tabellen müssen die folgenden Objekte erstellt werden:

  1. Ein Datenbankhauptschlüssel, der das datenbankbezogene Geheimnis der Anmeldeinformationen verschlüsselt
  2. Datenbankbezogene Anmeldeinformationen, die Ihre Arbeitsbereichsidentität verwenden
  3. Eine externe Datenquelle, die auf den Quellordner verweist
  4. Ein externes Dateiformat, das das Format der Quelldateien definiert
  5. Eine externe Tabellendefinition, die für Abfragen verwendet wird

Um diese Schritte auszuführen, verwenden Sie den SQL-Editor im Azure Synapse-Arbeitsbereich oder Ihren bevorzugten SQL-Client, der mit Ihrem dedizierten SQL-Pool verbunden ist. Sehen wir uns diese Schritte im Detail an.

Erstellen des Datenbankhauptschlüssels

Der Datenbankhauptschlüssel ist ein symmetrischer Schlüssel, der zum Schützen der privaten Schlüssel von Zertifikaten und der asymmetrischen Schlüssel verwendet wird, die in der Datenbank vorhanden sind, und zum Schützen der Geheimnisse in datenbankbezogenen Anmeldeinformationen. Wenn bereits ein Hauptschlüssel in der Datenbank vorhanden ist, müssen Sie keinen neuen erstellen. Ersetzen Sie <Secure Password> durch ein sicheres Kennwort. Das Kennwort wird zum Verschlüsseln des Hauptschlüssels in der Datenbank verwendet.

Verwenden Sie zum Erstellen eines Hauptschlüssels den folgenden T-SQL-Befehl:

-- Replace <Secure Password> with a secure password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Secure Password>';

Weitere Informationen zum Datenbankhauptschlüssel finden Sie unter CREATE MASTER KEY (Transact-SQL).

Erstellen der datenbankweit gültigen Anmeldeinformationen

Datenbankbezogene Anmeldeinformationen verwenden Ihre Arbeitsbereichsidentität und sind erforderlich, um jederzeit Zugriff auf die externen Daten zu haben, wenn die externe Tabelle Zugriff auf die Quelldaten benötigt.

Verwenden Sie den folgenden Befehl, um die datenbankbezogenen Anmeldeinformationen zu erstellen. Ersetzen Sie <CredentialName> durch den Namen, den Sie für Ihre Datenbankanmeldeinformationen verwenden möchten.

CREATE DATABASE SCOPED CREDENTIAL <CredentialName> WITH IDENTITY = 'Managed Service Identity';

Weitere Informationen zu datenbankbezogenen Anmeldeinformationen finden Sie unter CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

Erstellen der externen Datenquelle

Der nächste Schritt besteht darin, eine externe Datenquelle zu erstellen, die angibt, wo sich die von der externen Tabelle verwendeten Quelldaten befinden.

Verwenden Sie zum Erstellen der externen Datenquelle den folgenden T-SQL-Befehl:

CREATE EXTERNAL DATA SOURCE <ExternalDataSourceName>
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://<Container>@<AccountName>.dfs.core.windows.net/<Folder>/',
    CREDENTIAL = <CredentialName>
);

Hierbei gilt:

  • <ExternalDataSourceName> ist der Name, den Sie für Ihre externe Datenquelle verwenden möchten.
  • <AccountName> ist Ihr ADLS Gen2-Kontoname.
  • <Container> ist der Name des Containers in Ihrem Speicherkonto, in dem die Quelldaten gespeichert werden.
  • <Folder> ist der Ordner (oder Pfad mit Unterordnern), in dem die Quelldaten in Ihrem Container gespeichert werden. Sie können auch einen Dateinamen angeben, wenn Sie direkt auf eine einzelne Datei verweisen.
  • <Credential> ist der Name der Datenbankanmeldeinformationen, die Sie zuvor erstellt haben.

Weitere Informationen zu externen Datenquellen finden Sie unter CREATE EXTERNAL DATA SOURCE (Transact-SQL).

Erstellen des externen Dateiformats

Der nächste Schritt besteht darin, das externe Dateiformat zu erstellen. Es gibt das tatsächliche Layout der Daten an, auf die von der externen Tabelle verwiesen wird.

Verwenden Sie zum Erstellen des externen Dateiformats den folgenden T-SQL-Befehl. Ersetzen Sie <FileFormatName> durch den Namen, den Sie für das externe Dateiformat verwenden möchten.

CREATE EXTERNAL FILE FORMAT <FileFormatName>
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,
        USE_TYPE_DEFAULT = True
    )
);

Passen Sie in diesem Beispiel Parameter wie FIELD_TERMINATOR, STRING_DELIMITER, FIRST_ROW und weitere nach Bedarf gemäß Ihren Quelldaten an. Weitere Formatierungsoptionen und weitere Informationen zu EXTERNAL FILE FORMAT finden Sie unter CREATE EXTERNAL FILE FORMAT.

Erstellen der externen Tabelle

Da nun alle erforderlichen Objekte erstellt wurden, die die Metadaten für den sicheren Zugriff auf externe Daten enthalten, ist es an der Zeit, die externe Tabelle zu erstellen. Verwenden Sie zum Erstellen der externen Tabelle den folgenden T-SQL-Befehl:

-- Adjust the table name and columns to your desired name and external table schema
CREATE EXTERNAL TABLE <ExternalTableName> (
    Col1 INT,
    Col2 NVARCHAR(100),
    Col4 INT
)
WITH
(
    LOCATION = '<Path>',
    DATA_SOURCE = <ExternalDataSourceName>,
    FILE_FORMAT = <FileFormatName>
);

Hierbei gilt:

Denken Sie daran, den Tabellennamen und das Schema an den gewünschten Namen und das Schema der Daten in den Quelldateien anzupassen.

Abfragen der externen Tabelle

Zu diesem Zeitpunkt sind alle Metadaten erstellt, die für den Zugriff auf die externe Tabelle erforderlich sind. Verwenden Sie zum Testen der externen Tabelle eine Abfrage wie das folgende T-SQL-Beispiel, um Ihre Arbeit zu validieren:

SELECT TOP 10 Col1, Col2 FROM <ExternalTableName>;

Wenn alles ordnungsgemäß konfiguriert wurde, sollten die Daten aus den Quelldaten als Ergebnis dieser Abfrage angezeigt werden.

Weitere Informationen zur vollständigen Syntax von CREATE EXTERNAL TABLE finden Sie unter CREATE EXTERNAL TABLE (Transact-SQL).

Zugehöriger Inhalt