Läsa in data på ett säkert sätt med Synapse SQL

Den här artikeln visar och innehåller exempel på mekanismerna för säker autentisering för COPY-instruktionen. COPY-instruktionen är det mest flexibla och säkra sättet att massinläsning av data i Synapse SQL.

Autentiseringsmekanismer som stöds

I följande matris beskrivs de autentiseringsmetoder som stöds för varje filtyp och lagringskonto. Den här informationen gäller för källlagringsplatsen och felfilens plats.

1: .blob-endpointen (.blob.core.windows.net) i din externa platssökväg krävs för den här autentiseringsmetoden.

2: .dfs-slutpunkten (.dfs.core.windows.net) i din externa platssökväg krävs för den här autentiseringsmetoden.

A. Lagringskontonyckel med LF som radavgränsare (ny rad i Unix-format)

--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)
)

B. Signaturer för delad åtkomst (SAS) med CRLF som radavgränsare (ny rad i Windows-format)

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.
)

C. Hanterad identitet

Hanterad identitetsautentisering krävs när ditt lagringskonto är kopplat till ett virtuellt nätverk.

Förutsättningar

1. Installera Azure PowerShell. Se Installera PowerShell.
2. Om du har ett v1- eller bloblagringskonto för generell användning måste du först uppgradera till generell användning v2. Se Uppgradera till ett allmänt v2-lagringskonto.
3. Du måste aktivera Tillåt betrodda Microsoft-tjänster att komma åt det här lagringskontot under menyn Brandväggar för Azure Storage-konto och Inställningar för virtuella nätverk. Se Konfigurera Azure Storage-brandväggar och virtuella nätverk.

Steg

1. Om du har en fristående dedikerad SQL-pool registrerar du din SQL-server med Microsoft Entra-ID med Hjälp av PowerShell:

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

   Det här steget krävs inte för dedikerade SQL-pooler på en Synapse-arbetsyta. Den systemtilldelade hanterade identiteten (SA-MI) för arbetsytan är medlem i Synapse-administratörsrollen och har därmed utökade privilegier på arbetsytans dedikerade SQL-pooler.

2. Skapa ett generellt v2-lagringskonto. Mer information finns i Skapa ett lagringskonto.

   Anteckning

   • Om du har ett v1- eller bloblagringskonto för generell användning måste du först uppgradera till v2. Mer information finns i Uppgradera till ett V2-lagringskonto för generell användning.
   • Kända problem med Azure Data Lake Storage Gen2 finns i Kända problem med Azure Data Lake Storage Gen2.

3. Under ditt lagringskonto väljer du Åtkomstkontroll (IAM).

4. Klicka på Lägg till>Lägg till rolltilldelning för att öppna sidan Lägg till rolltilldelning.

5. Tilldela följande roll. Läs mer om att tilldela roller i Tilldela Azure-roller via Azure Portal.

   Inställning	Värde
   Yrkesroll	Storage Blob Data-medverkande
   Tilldela åtkomst till	SERVICEPRINCIPAL
   Medlemmar	Server eller arbetsyta som är värd för din dedikerade SQL-pool som är registrerad med Microsoft Entra-ID

   

   Anteckning

   Endast medlemmar med ägarbehörighet kan utföra det här steget. För olika inbyggda Azure-roller kan du läsa inbyggda Azure-roller.

   Viktigt!

   Ange Azure-rollen StorageBlob Data Ägare, Deltagare eller Läsare. De här rollerna skiljer sig från de inbyggda Azure-rollerna Ägare, Deltagare och Läsare.

   

6. Nu kan du köra COPY-uttrycket som anger "Hanterad identitet":

   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-autentisering

Steg

1. Under ditt lagringskonto väljer du Åtkomstkontroll (IAM).

2. Klicka på Lägg till>Lägg till rolltilldelning för att öppna sidan Lägg till rolltilldelning.

3. Tilldela följande roll. Läs mer om att tilldela roller i Tilldela Azure-roller via Azure Portal.

   Inställning	Värde
   Yrkesroll	Ägare, medarbetare eller läsare av lagringsblobbdata
   Tilldela åtkomst till	ANVÄNDARE
   Medlemmar	Microsoft Entra-användare

   

   Viktigt!

   Ange Azure-rollen StorageBlob Data Ägare, Deltagare eller Läsare. De här rollerna skiljer sig från de inbyggda Azure-rollerna Ägare, Deltagare och Läsare.

   

4. Konfigurera Microsoft Entra-autentisering. Se Konfigurera och hantera Microsoft Entra-autentisering med Azure SQL.

5. Anslut till SQL-poolen med Hjälp av Active Directory där du nu kan köra COPY-instruktionen utan att ange några autentiseringsuppgifter:

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

E. Autentisering av tjänsthuvudidentifierare

Steg

1. Skapa ett Microsoft Entra-program.

2. Hämta program-ID.

3. Hämta autentiseringsnyckeln.

4. Hämta V1 OAuth 2.0-tokenslutpunkten.

5. Tilldela läs-, skriv- och körningsbehörigheter till ditt Microsoft Entra-program på ditt lagringskonto.

6. Nu kan du köra COPY-instruktionen:

   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')
   )
   

Nästa steg

• Kontrollera artikeln om COPY-instruktionen för den detaljerade syntaxen
• Läs artikeln om översikt över datainläsning för bästa metoder för att läsa in data.