Exercício – carregar dados no Banco de Dados SQL do Azure

• 10 minutos

Quando você está carregando dados em massa, eles devem vir de algum lugar. No Azure, é comum armazenar ou despejar dados no Armazenamento de Blobs do Azure. O Armazenamento de Blobs é otimizado para armazenar grandes quantidades de dados não estruturados a um custo relativamente baixo.

Nesse cenário, o Adventure Works Cycles está recebendo dados de retorno de repositório com base no número de identificação do repositório. Esses dados são armazenados em arquivos .dat que, a seguir, são enviados por push para o Armazenamento de Blobs do Azure. Depois que os dados estiverem no Armazenamento de Blobs, o SQL do Azure precisará de uma forma de acessá-los. Você pode fazer isso criando uma fonte de dados externa que tem acesso à conta de armazenamento. Você pode controlar o acesso a essa conta de armazenamento por meio do Microsoft Entra ID, da autorização de chave compartilhada ou de uma assinatura de acesso compartilhado (SAS).

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

Há duas opções para concluir esse exercício:

• sqlcmd no Azure Cloud Shell
• Notebooks do SQL no Azure Data Studio

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

Opção 1: sqlcmd no Azure Cloud Shell

sqlcmd é uma ferramenta de linha de comando, que permite que você interaja com o SQL Server e o SQL do Azure usando a linha de comando. Neste exercício, você usa sqlcmd na instância do PowerShell do Azure Cloud Shell. sqlcmd é instalado por padrão, portanto, é fácil usá-lo no Azure Cloud Shell.

1. Devido à maneira como o Azure Cloud Shell para Bash é configurado, primeiro você precisa alterar o modo de terminal executando o comando a seguir no Azure Cloud Shell.

   TERM=dumb
   

2. Execute o comando a seguir no terminal integrado depois de modificar o nome do servidor e a senha.

   sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
   

3. Crie uma tabela e um esquema nos quais os dados possam ser carregados. Esse processo é um T-SQL direto. Execute o seguinte script no terminal, agora que você está conectado ao 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
   

   Dica

   Você vê uma entrada numérica após as instruções T-SQL. Os números representam cada linha da inserção no T-SQL. Por exemplo, o comando anterior termina com 26. Certifique-se de selecionar ENTER após essas linhas.

   Você saberá que o comando foi concluído quando 1> estiver visível novamente, indicando que o sqlcmd está pronto para a primeira linha da sua próxima inserção no T-SQL.

4. Em seguida, crie uma chave mestra:

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

5. Uma chave mestra é necessária para criar um valor de DATABASE SCOPED CREDENTIAL, pois o Armazenamento de Blobs não está configurado para permitir acesso público (anônimo). A credencial se refere à conta de armazenamento de Blobs. A parte dos dados especifica o contêiner para os dados de retorno do repositório.

   Usamos uma assinatura de acesso compartilhado como uma identidade que o SQL do Azure sabe como interpretar. O segredo é o token SAS que você pode gerar da conta de Armazenamento de Blobs. Neste exemplo, o token SAS de uma conta de armazenamento para a qual você não tem acesso é fornecido para que você possa acessar apenas os dados de retorno do repositório.

   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
   

6. 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
   

7. Insira em massa um dos arquivos de retorno do repositório. Execute o seguinte script e, ao final da execução, examine 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
   

8. Verifique quantas linhas foram inseridas na tabela:

   SELECT COUNT(*) FROM DataLoad.store_returns;
   GO
   

   Se tudo tiver sido executado corretamente, 2807797 terá sido retornado.

Esse código é um exemplo simples de como inserir dados do armazenamento de Blobs no Banco de Dados SQL do Azure. Para realizar o exercício novamente, execute o seguinte código a fim de reverter o que foi feito:

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: Notebooks do SQL no Azure Data Studio

Para essa atividade, você usará o notebook chamado LoadData.ipynb. Você pode encontrá-lo em \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata no seu dispositivo. Abra o arquivo no Azure Data Studio para concluir o exercício e, em seguida, retorne aqui.

Se não for possível concluir o exercício por qualquer motivo, você poderá examinar os resultados no arquivo de notebook correspondente no GitHub.