Gegevens veilig laden met Synapse SQL

In dit artikel worden voorbeelden gegeven van en vindt u een overzicht van de veilige verificatiemechanismen voor de COPY-instructie. De COPY-instructie is de meest flexibele en veilige manier om gegevens bulksgewijs te laden in Synapse SQL.

Ondersteunde verificatiemechanismen

In de volgende matrix worden de ondersteunde verificatiemethoden voor elk bestandstype en opslagaccount beschreven. Deze informatie is van toepassing op de bronopslaglocatie en de locatie van het foutbestand.

1: Het .blob-eindpunt (.blob.core.windows.net) in uw externe locatiepad is vereist voor deze verificatiemethode.

2: Het .dfs-eindpunt (.dfs.core.windows.net) in uw externe locatiepad is vereist voor deze verificatiemethode.

A. Sleutel van opslagaccount met LF als het rijeindteken (nieuwe regel in Unix-stijl)

--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. Shared Access Signatures (SAS) met CRLF als het rijeindteken (nieuwe regel in Windows-stijl)

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. Beheerde identiteit

Verificatie van beheerde identiteit is vereist wanneer uw opslagaccount is gekoppeld aan een VNet.

Prerequisites

1. Installeer Azure PowerShell. Raadpleeg PowerShell-installeren.
2. Als u een v1- of Blob Storage-account voor algemeen gebruik hebt, moet u eerst een upgrade uitvoeren naar algemeen gebruik v2. Raadpleeg Upgraden naar een v2-opslagaccount voor algemeen gebruik.
3. U moet de optie Vertrouwde Microsoft-services toestaan deze opslagaccount te benaderen inschakelen onder de instellingen van het Azure-opslagaccount, menu Firewalls en virtuele netwerken. Raadpleeg Azure Storage-firewalls en virtuele netwerken configureren.

Steps

1. Als u een zelfstandige toegewezen SQL-pool hebt, registreert u uw SQL-server bij Microsoft Entra-id met behulp van PowerShell:

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

   Deze stap is niet vereist voor toegewezen SQL-pools in een Synapse-werkruimte. De door het systeem toegewezen beheerde identiteit (SA-MI) van de werkruimte is lid van de rol Synapse-beheerder en heeft dus verhoogde bevoegdheden voor de toegewezen SQL-pools van de werkruimte.

2. Maak een v2-opslagaccount voor algemeen gebruik. Zie Een opslagaccount maken voor meer informatie.

   Note

   • Als u een v1- of blobopslagaccount voor algemeen gebruik hebt, moet u eerst een upgrade uitvoeren naar v2. Zie Upgraden naar een algemeen v2-opslagaccount voor meer informatie.
   • Raadpleeg bekende problemen met Azure Data Lake Storage Gen2 voor bekende problemen met Azure Data Lake Storage Gen2.

3. Selecteer toegangsbeheer (IAM) onder uw opslagaccount.

4. Selecteer >Roltoewijzing toevoegen om de pagina Roltoewijzing toevoegen te openen.

5. Wijs de volgende rol toe. Zie Azure-rollen toewijzen met behulp van Azure Portal voor gedetailleerde stappen.

   Setting	Value
   Role	Bijdrager van opslagblobdata
   Toegang toewijzen aan	SERVICEPRINCIPAL
   Members	Server of werkruimte die als host fungeert voor uw toegewezen SQL-pool die is geregistreerd bij Microsoft Entra-id

   

   Note

   Alleen leden met eigenaarsbevoegdheden kunnen deze stap uitvoeren. Raadpleeg Azure ingebouwde rollen voor verschillende ingebouwde Azure-rollen.

   Important

   Geef de Azure-rol StorageBlobgegevens Eigenaar, Inzender of Lezer op. Deze rollen verschillen van de ingebouwde Azure-rollen van Eigenaar, Inzender en Lezer.

   

6. U kunt nu de COPY-statement uitvoeren met specificatie 'Beheerde identiteit':

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

Steps

1. Selecteer toegangsbeheer (IAM) onder uw opslagaccount.

2. Selecteer >Roltoewijzing toevoegen om de pagina Roltoewijzing toevoegen te openen.

3. Wijs de volgende rol toe. Zie Azure-rollen toewijzen met behulp van Azure Portal voor gedetailleerde stappen.

   Setting	Value
   Role	Eigenaar, inzender of lezer van opslagblobgegevens
   Toegang toewijzen aan	USER
   Members	Microsoft Entra-gebruiker

   

   Important

   Geef de Azure-rol Eigenaar, Inzender of Lezer van opslagblobgegevens op. Deze rollen verschillen van de ingebouwde Azure-rollen van Eigenaar, Inzender en Lezer.

   

4. Stel Microsoft Entra-verificatie in. Raadpleeg Microsoft Entra-verificatie configureren en beheren met Azure SQL.

5. Maak verbinding met uw SQL-pool met behulp van Active Directory, waar u nu de COPY-instructie kunt uitvoeren zonder referenties op te geven:

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

E. Autorisatie van Service Principal

Steps

1. Maak een Microsoft Entra-toepassing.

2. Toepassings-id ophalen.

3. Haal de verificatiesleutel op.

4. Haal het eindpunt van het V1 OAuth 2.0-token op.

5. Wijs lees-, schrijf- en uitvoeringsmachtigingen toe aan uw Microsoft Entra-toepassing in uw opslagaccount.

6. U kunt nu de COPY-statement uitvoeren:

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

Volgende stappen

• Raadpleeg het artikel over de COPY-instructie voor de gedetailleerde syntaxis
• Raadpleeg het overzichtsartikel over het laden van gegevens voor aanbevolen procedures