Ładowanie danych do usługi Azure SQL Database

10 min

Gdy ładujesz dane zbiorczo, muszą one pochodzić z jakiegoś źródła. Na platformie Azure często dane są przechowywane lub zrzucane do usługi Azure Blob Storage. Usługa Blob Storage jest zoptymalizowana pod kątem przechowywania ogromnych ilości danych bez struktury przy stosunkowo niskich kosztach.

W tym scenariuszu firma Adventure Works Cycles otrzymuje dane zwrotne ze sklepów na podstawie numeru identyfikacyjnego sklepu. Te dane są przechowywane w plikach .dat , które następnie są wypychane do usługi Azure Blob Storage. Gdy dane znajdują się w magazynie obiektów blob, usługa Azure SQL musi w jakiś sposób uzyskać do nich dostęp. W tym celu można utworzyć zewnętrzne źródło danych mające dostęp do konta magazynu. Dostęp do tego konta magazynu można kontrolować za pomocą identyfikatora Entra firmy Microsoft, autoryzacji klucza współdzielonego lub sygnatury dostępu współdzielonego (SAS).

W tym ćwiczeniu zapoznamy się z jednym scenariuszem zbiorczego ładowania danych z usługi Azure Blob Storage do usługi Azure SQL Database. Metoda korzysta z języka T-SQL i sygnatur dostępu współdzielonego.

To ćwiczenie można ukończyć na dwa sposoby:

Narzędzie sqlcmd w usłudze Azure Cloud Shell
Notesy SQL w narzędziu Azure Data Studio

Oba ćwiczenia obejmują te same polecenia i zawartość, aby można było wybrać preferowaną opcję.

Opcja 1. Narzędzie `sqlcmd` w usłudze Azure Cloud Shell

sqlcmd to narzędzie wiersza polecenia umożliwiające interakcję z programem SQL Server i usługą Azure SQL przy użyciu wiersza polecenia. W tym ćwiczeniu użyjesz sqlcmd w wystąpieniu programu PowerShell usługi Azure Cloud Shell. Narzędzie sqlcmd jest zainstalowane domyślnie, więc można go łatwo używać z poziomu usługi Azure Cloud Shell.

Ze względu na sposób konfigurowania usługi Azure Cloud Shell dla powłoki Bash należy najpierw zmienić tryb terminalu, uruchamiając następujące polecenie w usłudze Azure Cloud Shell.
```
TERM=dumb
```
Po zmodyfikowaniu nazwy serwera i hasła uruchom następujące polecenie w zintegrowanym terminalu.
```
sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
```

Utwórz tabelę i schemat, do których mają zostać załadowane dane. Ten proces jest prosty w języku T-SQL. Uruchom następujący skrypt w terminalu, teraz, gdy masz połączenie z bazą danych:

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

Napiwek

Po instrukcjach języka T-SQL zostanie wyświetlony wpis liczbowy. Reprezentuje każdy wiersz wpisu T-SQL. Na przykład poprzednie polecenie kończy się ciągiem 26. Pamiętaj, aby wybrać ENTER po tych wierszach.

Wiesz, że polecenie zostało zakończone po ponownym wyświetleniu 1> , co oznacza, że sqlcmd jest gotowy do pierwszego wiersza następnego wpisu języka T-SQL.

Następnie utwórz klucz główny:

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

Klucz główny jest wymagany do utworzenia DATABASE SCOPED CREDENTIAL wartości, ponieważ usługa Blob Storage nie jest skonfigurowana do zezwalania na dostęp publiczny (anonimowy). Poświadczenie odnosi się do konta usługi Blob Storage. Część danych określa kontener dla danych zwracanych przez magazyn.

Użyj sygnatury dostępu współdzielonego jako tożsamości, którą usługa Azure SQL wie, jak interpretować. Wpis tajny jest tokenem SAS, który można wygenerować z poziomu konta magazynu obiektów blob. W tym przykładzie jest dostarczany token SAS dla konta magazynu, do którego nie masz dostępu, tak aby można było uzyskać dostęp tylko do danych zwrotnych ze sklepów.
```
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
```

Utwórz zewnętrzne źródło danych w kontenerze:

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

Zbiorcze wstawianie jednego z plików zwracanych przez magazyn. Uruchom następujący skrypt i po zakończeniu przejrzyj komentarze:

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

Sprawdź, ile wierszy zostało wstawionych do tabeli:
```
SELECT COUNT(*) FROM DataLoad.store_returns;
GO
```
Jeśli wszystko działa prawidłowo, powinna pojawić się zwrócona wartość 2807797.

Ten kod jest prostym przykładem wstawiania danych z usługi Blob Storage do usługi Azure SQL Database. Jeśli chcesz ponownie wykonać ćwiczenie, uruchom następujący kod, aby zresetować to, co zostało zrobione:

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

Opcja 2. Notesy SQL w narzędziu Azure Data Studio

W przypadku tego działania użyj notesu o nazwie LoadData.ipynb. Można go znaleźć w folderze \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata na urządzeniu. Otwórz ten plik w narzędziu Azure Data Studio, aby ukończyć to ćwiczenie, a następnie wróć tutaj.

Jeśli z jakiegoś powodu nie możesz wykonać tego ćwiczenia, możesz przejrzeć wyniki w odpowiednim pliku notesu w usłudze GitHub.

Ładowanie danych do usługi Azure SQL Database

Opcja 1. Narzędzie sqlcmd w usłudze Azure Cloud Shell

Opcja 2. Notesy SQL w narzędziu Azure Data Studio

Opinia

Opcja 1. Narzędzie `sqlcmd` w usłudze Azure Cloud Shell