Dela via

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

  • Artikel

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. Detta gäller för källlagringsplatsen och felfilens plats.

CSV Parquet ORCH
Azure Blob Storage SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/NYCKEL SAS/NYCKEL
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: .blobslutpunkten (.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)
)

Viktigt!

  • Använd hexadecimalt värde (0x0A) för att ange tecknet Radmatning/Ny rad. Observera att COPY-instruktionen tolkar strängen \n som \r\n (vagn returnerar ny rad).

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

Viktigt!

Ange ROWTERMINATOR inte som \r\n som tolkas som \r\r\n och kan resultera i parsningsproblem. Kommandot COPY prefixar automatiskt \r-tecknet när \n (ny rad) har angetts. Detta resulterar i att vagnen returnerar newline (\r\n) för Windows-baserade system.

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.

    Kommentar

  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
    Roll Storage blobb data-deltagare
    Tilldela åtkomst till SERVICEPRINCIPAL
    Medlemmar server eller arbetsyta som är värd för din dedikerade SQL-pool som du har registrerat med Microsoft Entra-ID

    Lägg till rolltilldelningssida i Azure Portal.

    Kommentar

    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 Storage Blob Data Owner, Contributor eller Reader. De här rollerna skiljer sig från de inbyggda Azure-rollerna Ägare, Deltagare och Läsare.

    Bevilja Azure RBAC behörighet att läsa in

  6. Nu kan du köra COPY-instruktionen 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
    Roll Lagringsblobdataägare, deltagare eller läsare
    Tilldela åtkomst till USER
    Medlemmar Microsoft Entra-användare

    Lägg till rolltilldelningssida i Azure Portal.

    Viktigt!

    Ange azure-rollen Storage Blob Data Owner, Contributor eller Reader. De här rollerna skiljer sig från de inbyggda Azure-rollerna Ägare, Deltagare och Läsare.

    Bevilja Azure RBAC behörighet att läsa in

  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änstens huvudnamn

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

Viktigt!

Använd V1-versionen av OAuth 2.0-tokenslutpunkten

Nästa steg