Exercício – Carregar dados na Base de Dados SQL do Azure

10 minutos

Quando está a fazer um carregamento em massa de dados, estes têm de vir de algum lado. No Azure, é comum armazenar ou despejar dados no Armazenamento de Blobs do Azure. O Blob Storage é otimizado para armazenar grandes quantidades de dados não estruturados a um custo relativamente baixo.

Neste cenário, o Adventure Works Cycles está a receber dados de devolução do armazenamento em função do número de identificação do armazenamento. Esses dados são armazenados em arquivos .dat , que são enviados por push para o armazenamento de Blob do Azure. Assim que os dados estiverem no Armazenamento de blobs, é necessário arranjar uma forma de o SQL do Azure poder aceder ao mesmo. Pode fazê-lo ao criar uma origem de dados externa que tenha acesso à conta de armazenamento. Você pode controlar o acesso a essa conta de armazenamento por meio da ID do Microsoft Entra, autorização de chave compartilhada ou uma assinatura de acesso compartilhado (SAS).

Neste exercício, exploramos um cenário para o carregamento em massa de dados do armazenamento de Blob do Azure no Banco de Dados SQL do Azure. A abordagem usa T-SQL e assinaturas de acesso compartilhado.

Para concluir este exercício, existem duas opções:

sqlcmd no Azure Cloud Shell
Blocos de notas do SQL no Azure Data Studio

Ambos os exercícios contêm os mesmos comandos e conteúdos, portanto pode escolher a opção que preferir.

Opção 1: `sqlcmd` no Azure Cloud Shell

sqlcmd é uma ferramenta de linha de comandos, que lhe permite interagir com o SQL Server e com o SQL do Azure através da linha de comandos. Neste exercício, você usa sqlcmd a instância do PowerShell do Azure Cloud Shell. sqlcmd é instalado por predefinição, pelo que é fácil de utilizar no Azure Cloud Shell.

Devido à maneira como o Azure Cloud Shell for Bash é configurado, primeiro você precisa alterar o modo de terminal executando o seguinte comando no Azure Cloud Shell.
```
TERM=dumb
```
Execute o seguinte comando no terminal integrado após ter modificado o seu nome do servidor e palavra-passe.
```
sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
```

Crie uma tabela e um esquema para os dados a serem carregados. Este processo é simples T-SQL. Execute o seguinte script no terminal, agora que você está conectado ao seu banco de dados:

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

Gorjeta

Você verá uma entrada numérica após as instruções T-SQL. Ele representa cada linha da entrada T-SQL. Por exemplo, o comando anterior termina com 26. Certifique-se de selecionar ENTER após estas linhas.

Você sabe que o comando terminou quando você vê 1> novamente, o que indica que sqlcmd está pronto para a primeira linha da sua próxima entrada T-SQL.

Em seguida, crie uma chave mestra:

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

Uma chave mestra é necessária para criar um DATABASE SCOPED CREDENTIAL valor porque o armazenamento de Blob não está configurado para permitir acesso público (anônimo). A credencial refere-se à conta de armazenamento de Blob. A parte de dados especifica o contêiner para os dados de retorno de armazenamento.

Use uma assinatura de acesso compartilhado como a identidade que o SQL do Azure sabe interpretar. O segredo é o token SAS que pode gerar a partir da conta do Armazenamento de blobs. Neste exemplo, foi indicado o token de SAS de uma conta de armazenamento à qual não tem acesso, para que possa aceder apenas aos dados de devolução do armazenamento.
```
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
```

Crie uma fonte de dados externa para o contêiner:

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

Insira em massa um dos arquivos de retorno da loja. Execute o seguinte script e, enquanto terminar, revise os comentários:

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

Verifique quantas linhas foram inseridas na tabela:
```
SELECT COUNT(*) FROM DataLoad.store_returns;
GO
```
Se tudo correu como previsto, deverá ver que 2807797 foi devolvido.

Este código é um exemplo simples de como inserir dados do armazenamento de Blob no Banco de Dados SQL do Azure. Se você quiser executar o exercício novamente, execute o seguinte código para redefinir o que você fez:

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

Opção 2: Blocos de anotações SQL no Azure Data Studio

Para essa atividade, use o bloco de anotações chamado LoadData.ipynb. Você pode encontrá-lo em \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata no seu dispositivo. Abra este ficheiro no Azure Data Studio para concluir este exercício e, em seguida, regresse aqui.

Se, por algum motivo, não conseguir concluir este exercício, poderá analisar os resultados no ficheiro do bloco de notas correspondente no GitHub.

Exercício – Carregar dados na Base de Dados SQL do Azure

Opção 1: sqlcmd no Azure Cloud Shell

Opção 2: Blocos de anotações SQL no Azure Data Studio

Comentários

Opção 1: `sqlcmd` no Azure Cloud Shell