Gegevens veilig laden met Synapse SQL

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

Ondersteunde verificatiemechanismen

De volgende matrix beschrijft de ondersteunde verificatiemethoden voor elk bestandstype en opslagaccount. Dit geldt voor de bronopslaglocatie en de locatie van het foutbestand.

	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: 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. Opslagaccountsleutel met LF als het rij-eindpunt (nieuwe regel voor 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)
)

Belangrijk

• Gebruik de hexadecimale waarde (0x0A) om de lijnfeed/het teken voor nieuwe regel op te geven. Houd er rekening mee dat de instructie COPY de \n tekenreeks interpreteert als \r\n (regelterugloop newline).

B. Shared Access Signatures (SAS) met CRLF als het rij-eindpunt (nieuwe regel 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.
)

Belangrijk

Geef niet op ROWTERMINATOR als '\r\n' die wordt geïnterpreteerd als '\r\r\n' en kan leiden tot parseringsproblemen. Met de opdracht COPY wordt het teken \r automatisch voorafgegaan wanneer \n (nieuwe regel) is opgegeven. Dit resulteert in regelterugloop newline (\r\n) voor Windows-systemen.

C. Beheerde identiteit

Beheerde identiteitsverificatie is vereist wanneer uw opslagaccount is gekoppeld aan een VNet.

Vereisten

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 vertrouwde Microsoft-services toegang geven tot dit opslagaccount ingeschakeld onder het instellingenmenu van uw Azure Storage-account Firewalls en virtuele netwerken. Raadpleeg Azure Storage-firewalls en virtuele netwerken configureren.

Stappen

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 nodig voor toegewezen SQL-groepen 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.

   Notitie

   • 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 Toevoegen>Roltoewijzing toevoegen om het deelvenster Roltoewijzing toevoegen te openen.

5. Wijs de volgende rol toe. Raadpleeg Azure-rollen toewijzen met Azure Portal voor informatie over het toewijzen van rollen.

   Instelling	Weergegeven als
   Role	Inzender voor opslagblobgegevens
   Toegang toewijzen aan	SERVICEPRINCIPAL
   Leden	server of werkruimte die als host fungeert voor uw toegewezen SQL-pool die u hebt geregistreerd bij Microsoft Entra-id

   

   Notitie

   Alleen leden met de bevoegdheid Eigenaar kunnen deze stap uitvoeren. Raadpleeg ingebouwde Azure-rollen voor verschillende ingebouwde Azure-rollen.

   Belangrijk

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

   

6. U kunt nu de instructie COPY uitvoeren om een "Beheerde identiteit" op te geven:

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

Stappen

1. Selecteer toegangsbeheer (IAM) onder uw opslagaccount.

2. Selecteer Toevoegen>Roltoewijzing toevoegen om het deelvenster Roltoewijzing toevoegen te openen.

3. Wijs de volgende rol toe. Raadpleeg Azure-rollen toewijzen met Azure Portal voor informatie over het toewijzen van rollen.

   Instelling	Weergegeven als
   Role	Eigenaar, inzender of lezer van opslagblobgegevens
   Toegang toewijzen aan	GEBRUIKER
   Leden	Microsoft Entra-gebruiker

   

   Belangrijk

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

   

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

5. Maak verbinding met uw SQL-groep met Active Directory waarbij u nu de instructie COPY 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. Service-principal-verificatie

Stappen

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

Belangrijk

Gebruik de v1- versie van het OAuth 2.0-token-eindpunt

Volgende stappen

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