Oefening - Gegevens in Azure SQL Database laden

10 minuten

Wanneer u gegevens bulksgewijs laadt, moet deze ergens vandaan komen. In Azure is het gebruikelijk om gegevens op te slaan of te dumpen in Azure Blob Storage. Blob Storage is geoptimaliseerd voor het opslaan van enorme hoeveelheden ongestructureerde gegevens tegen relatief lage kosten.

In dit scenario ontvangt Adventure Works Cycles retourgegevens uit de winkel op basis van een winkelidentificatienummer. Deze gegevens worden opgeslagen in .dat bestanden, die vervolgens worden gepusht naar Azure Blob Storage. Als de gegevens zich in de blob-opslag bevinden, heeft Azure SQL een manier nodig om toegang te krijgen. U kunt dit doen door een externe gegevensbron te maken die toegang heeft tot het Azure-opslagaccount. U kunt de toegang tot dat opslagaccount beheren via Microsoft Entra ID, autorisatie van gedeelde sleutels of een Shared Access Signature (SAS).

In deze oefening verkennen we één scenario voor het bulksgewijs laden van gegevens uit Azure Blob Storage in Azure SQL Database. De benadering maakt gebruik van T-SQL en handtekeningen voor gedeelde toegang.

Er zijn twee opties voor het voltooien van deze oefening:

sqlcmd in Azure Cloud Shell
SQL-notebooks in Azure Data Studio

Beide oefeningen bevatten dezelfde opdrachten en inhoud, zodat u de gewenste optie kunt kiezen.

Optie 1: `sqlcmd` in de Azure Cloud Shell

sqlcmd is een opdrachtregelprogramma waarmee u kunt communiceren met SQL Server en Azure SQL via de opdrachtregel. In deze oefening gebruikt sqlcmd u het PowerShell-exemplaar van Azure Cloud Shell. sqlcmd wordt standaard geïnstalleerd, zodat het eenvoudig te gebruiken is vanuit Azure Cloud Shell.

Vanwege de manier waarop Azure Cloud Shell voor Bash is geconfigureerd, moet u eerst de terminalmodus wijzigen door de volgende opdracht uit te voeren in Azure Cloud Shell.
```
TERM=dumb
```
Voer de volgende opdracht in de geïntegreerde terminal uit nadat u de servernaam en het wachtwoord hebt gewijzigd.
```
sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
```

Maak een tabel en schema voor het laden van gegevens. Dit proces is eenvoudig T-SQL. Voer het volgende script uit in de terminal, nu u bent verbonden met uw database:

IF SCHEMA_ID('DataLoad') IS NULL
EXEC ('CREATE SCHEMA DataLoad')
CREATE TABLE DataLoad.store_returns
(
    sr_returned_date_sk             bigint,
    sr_return_time_sk               bigint,
    sr_item_sk                      bigint,
    sr_customer_sk                  bigint,
    sr_cdemo_sk                     bigint,
    sr_hdemo_sk                     bigint,
    sr_addr_sk                      bigint,
    sr_store_sk                     bigint,
    sr_reason_sk                    bigint,
    sr_ticket_number                bigint,
    sr_return_quantity              integer,
    sr_return_amt                   float,
    sr_return_tax                   float,
    sr_return_amt_inc_tax           float,
    sr_fee                          float,
    sr_return_ship_cost             float,
    sr_refunded_cash                float,
    sr_reversed_charge              float,
    sr_store_credit                 float,
    sr_net_loss                     float
);
GO

Tip

U ziet een nummervermelding na de T-SQL-instructies. Het vertegenwoordigt elke regel van de T-SQL-vermelding. De voorgaande opdracht eindigt bijvoorbeeld met 26. Zorg ervoor dat u ENTER selecteert na deze regels.

U weet dat de opdracht is voltooid wanneer u het opnieuw ziet 1> , wat aangeeft dat sqlcmd deze gereed is voor de eerste regel van de volgende T-SQL-vermelding.

Maak vervolgens een hoofdsleutel:

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD='MyComplexPassword00!';
GO

Een hoofdsleutel is vereist om een DATABASE SCOPED CREDENTIAL waarde te maken omdat Blob Storage niet is geconfigureerd om openbare (anonieme) toegang toe te staan. De referentie verwijst naar het Blob Storage-account. Het gegevensgedeelte geeft de container voor de retourgegevens van het archief op.

Gebruik een handtekening voor gedeelde toegang als de identiteit die Azure SQL moet interpreteren. Het geheim is het SAS-token dat u kunt genereren op basis van het Blob Storage-account. In dit voorbeeld wordt het SAS-token voor een opslagaccount waartoe u geen toegang hebt, gegeven zodat u alleen toegang hebt tot de retourgegevens van de winkel.
```
CREATE DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'st=2020-09-28T22%3A05%3A27Z&se=2030-09-29T22%3A05%3A00Z&sp=rl&sv=2018-03-28&sr=c&sig=52WbuSIJCWyjS6IW6W0ILfIpqh4wLMXmOlifPyOetZI%3D';
GO
```

Maak een externe gegevensbron naar de container:

CREATE EXTERNAL DATA SOURCE dataset
WITH
(
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://azuresqlworkshopsa.blob.core.windows.net/data',
    CREDENTIAL = [https://azuresqlworkshopsa.blob.core.windows.net/data/]
);
GO

Voeg een van de retourbestanden van het archief bulksgewijs in. Voer het volgende script uit en bekijk de opmerkingen zolang het is voltooid:

SET NOCOUNT ON -- Reduce network traffic by stopping the message that shows the number of rows affected
BULK INSERT DataLoad.store_returns -- Table you created in step 3
FROM 'dataset/store_returns/store_returns_1.dat' -- Within the container, the location of the file
WITH (
DATA_SOURCE = 'dataset' -- Using the external data source from step 6
,DATAFILETYPE = 'char'
,FIELDTERMINATOR = '\|'
,ROWTERMINATOR = '\|\n'
,BATCHSIZE=100000 -- Reduce network traffic by inserting in batches
, TABLOCK -- Minimize number of log records for the insert operation
);
GO

Controleer hoeveel rijen in de tabel zijn ingevoegd:
```
SELECT COUNT(*) FROM DataLoad.store_returns;
GO
```
Als alles goed is uitgevoerd, ziet u 2807797 geretourneerd.

Deze code is een eenvoudig voorbeeld van het invoegen van gegevens uit Blob Storage in Azure SQL Database. Als u de oefening opnieuw wilt uitvoeren, voert u de volgende code uit om opnieuw in te stellen wat u hebt gedaan:

DROP EXTERNAL DATA SOURCE dataset;
DROP DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/];
DROP TABLE DataLoad.store_returns;
DROP MASTER KEY;
GO

Optie 2: SQL-notebooks in Azure Data Studio

Gebruik voor deze activiteit het notebook LoadData.ipynb. U vindt deze in \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata op uw apparaat. Open dit bestand in Azure Data Studio om deze oefening te voltooien en keer vervolgens hier terug.

Als u de oefening om welke reden dan ook niet kunt voltooien, bekijkt u de resultaten in het bijbehorende notebookbestand op GitHub.

Oefening - Gegevens in Azure SQL Database laden

Optie 1: sqlcmd in de Azure Cloud Shell

Optie 2: SQL-notebooks in Azure Data Studio

Feedback

Optie 1: `sqlcmd` in de Azure Cloud Shell