Exercício: criar tabelas, importar em massa e consultar dados

  • 10 minutos

Atualmente a universidade armazena os dados em uma série de arquivos delimitados por vírgula. Você deve migrar esses dados para o Banco de Dados SQL do Azure.

Neste exercício, você cria um servidor de banco de dados e um banco de dados individual usando o serviço do Banco de Dados SQL. Em seguida, você cria tabelas e importa dados para o banco de dados. Por fim, você usa o editor de consultas e o utilitário sqlcmd para consultar os dados.

Examinar os dados delimitados por vírgula existentes

  1. Na janela do Cloud Shell à direita, execute o comando a seguir para baixar os arquivos de dados e o código do aplicativo para o sistema da universidade.

    git clone https://github.com/MicrosoftDocs/mslearn-develop-app-that-queries-azure-sql education

  2. Execute esses comandos para mover os dados de exemplo para a sua própria pasta e listar os arquivos na pasta.

    mv ~/education/data ~/educationdata
cd ~/educationdata
ls

    Essa pasta contém três arquivos: courses.csv, modules.csv e studyplans.csv.

  3. Exiba o conteúdo do arquivo courses.csv.

    cat courses.csv

    Esse arquivo contém os seguintes dados separados por vírgula. Os dados compreendem o nome do curso e uma ID para cada curso que a universidade oferece.

    ID,Course
1,Computer Science
2,Maths with Computing
3,Maths with Physics
4,Computer Science with Physics
5,Maths with Chemistry
6,Physics with Chemistry
7,Maths
8,Physics
9,Chemistry

  4. Exiba o conteúdo do arquivo modules.csv.

    cat modules.csv

    Este arquivo lista os diversos módulos que os alunos podem estudar para cumprir com os requisitos dos cursos. Cada módulo tem um código de identificação e um nome.

    Module Code,Title
CS101,Introduction to Computer Science
CS102,Java Programming
CS103,Distributed Applications
CS104,Cloud-based systems
MA101,Foundations of Applied Maths
MA102,Advanced Calculus
MA103,Number Theory
MA104,String Theory
PH101,Foundations of Physics
PH102,Basic Experimental Phyics
PH103,Basic Theoretical Physics
PH104,Subatomic Physics
CH101,Elements of Chemistry
CH102,Basic Inorganic Chemistry
CH103,Basic Organic Chemistry
CH104,Chemical Engineering

  5. Exiba o conteúdo do arquivo studyplans.csv.

    cat studyplans.csv

    Esse arquivo contém os dados que especificam quais módulos um aluno deve passar para concluir um curso com êxito. A coluna Sequência especifica a ordem em que o aluno deve realizar cada módulo. Por exemplo, para o curso 1 (Ciência da Computação) o aluno deve realizar o módulo CS101 antes do módulo MA101. Parte dos dados é mostrada abaixo.

    Course ID,Module Code,Sequence
1,CS101,1
1,MA101,2
1,CS102,3
1,CS103,4
1,CS104,5
2,MA101,1
2,MA102,2
2,CS101,3
2,CS102,4
2,CS103,5
3,MA101,1
3,MA102,2
3,PH101,3
3,PH102,4
3,PH103,5
...

Criar um servidor de banco de dados e um banco de dados usando o Banco de Dados SQL

Vamos criar o banco de dados e o servidor para armazenar os dados do aplicativo.

  1. Entre no portal do Azure usando a conta que você utilizou para ativar a área restrita.

  2. No menu do portal do Azure, em Serviços do Azure, selecione Criar um recurso.

    Screenshot of Azure portal menu and Create a resource option.

    O painel Criar um recurso será exibido.

  3. No painel de menu à esquerda, selecione Bancos de Dados e, em Serviços do Azure populares, selecione Banco de Dados SQL.

    Screenshot of the Databases and SQL Database options.

    O painel Criar Banco de Dados SQL será exibido.

  4. Na guia Básico, insira os valores a seguir para cada configuração.

    Configuração Valor
    Detalhes dos projetos
    Assinatura Assinatura do Concierge
    Grupo de recursos [Grupo de recursos da Área Restrita]
    Detalhes do banco de dados
    Nome do banco de dados O banco de dados precisa ter um nome exclusivo. Sugerimos algo como coursedatabaseNNN, em que NNN é um número aleatório.
    Servidor Selecione Criar link e, no painel Novo servidor, insira os detalhes que estão na tabela a seguir.
    Deseja usar o pool elástico SQL? Não
    Computação + armazenamento Uso geral

    Para o servidor, insira os valores a seguir para cada configuração.

    Configuração Valor
    Nome do servidor courseserverNNN, em que NNN é o mesmo número que você selecionou para o banco de dados
    Logon de administrador do servidor azuresql
    Senha Insira uma senha que atenda aos requisitos.
    Confirmar senha Confirme sua senha.
    Localização Centro dos EUA

  5. Selecione OK.

  6. Selecione Avançar: Rede.

  7. Na guia Rede, insira os valores a seguir para cada configuração.

    Configuração Valor
    Conectividade de rede
    Método de conectividade Ponto de extremidade público
    Regras de firewall
    Permitir que serviços e recursos do Azure acessem este servidor Sim
    Adicionar endereço IP atual do cliente Sim

  8. Selecione Examinar + criar.

  9. Selecione Criar. Aguarde até que o servidor e o banco de dados sejam criados antes de continuar.

Criar as tabelas

Agora é possível criar as tabelas que armazenarão os dados dos arquivos .csv.

  1. Selecione Ir para o recurso. Seu banco de dados SQL para coursedatabaseNNN é exibido.

  2. No painel de menu à esquerda, selecione Editor de consultas (versão prévia).

    The database page in the Azure portal with the query editor option highlighted.

    O painel Editor de consultas para coursedatabaseNNN é exibido.

  3. Insira os valores a seguir para cada configuração.

    Configuração Valor
    Autenticação do SQL Server
    Logon azuresql
    Senha Especifique a senha usada ao criar esse usuário.

    Observação

    Se você receber um erro ao entrar no banco de dados, verifique o IP listado no erro e confira se ele foi adicionado como o IP do cliente. Você pode fazer isso ao selecionar Visão Geral>Definir firewall do servidor.

  4. Selecione OK para se conectar ao serviço de banco de dados.

  5. No painel Consulta 1, insira a instrução T-SQL (Transact-SQL) a seguir, depois selecione Executar. Essa instrução cria uma tabela para armazenar as informações do curso. Verifique se que a instrução é executada sem erros.

    CREATE TABLE Courses
(
    CourseID INT NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
)

    The Query editor window in the Azure portal. The user has entered a statement to create the Courses table.

  6. Substitua a instrução existente pela instrução a seguir, que cria uma tabela para armazenar os módulos. Selecione Executar e verifique se a instrução é executada sem erros.

    CREATE TABLE Modules
(
    ModuleCode VARCHAR(5) NOT NULL PRIMARY KEY,
    ModuleTitle VARCHAR(50) NOT NULL
)

  7. Altere a instrução para criar uma tabela chamada StudyPlans e selecione Executar.

    CREATE TABLE StudyPlans
(
    CourseID INT NOT NULL,
    ModuleCode VARCHAR(5) NOT NULL,
    ModuleSequence INT NOT NULL,
    PRIMARY KEY(CourseID, ModuleCode)
)

  8. Na janela do banco de dados, selecione o ícone Atualizar na barra de ferramentas. Expanda Tabelas e, em seguida, expanda uma tabela por vez. Você deve ver as três tabelas, dbo.Courses, dbo.Modules e dbo.StudyPlans, com as colunas e a chave primária de cada tabela.

    Observação

    dbo significa database owner (proprietário do banco de dados). É o esquema padrão no banco de dados. Todas as três tabelas foram criadas neste esquema.

    The database window in the Azure portal, showing the tables and columns.

Importar os dados

  1. Volte para o Cloud Shell e garanta que você está na pasta educationdata.

    cd ~/educationdata

  2. Crie as variáveis a serem usadas em etapas posteriores. Substitua NNN pelo número usado para o banco de dados e o servidor.

    export DATABASE_NAME=coursedatabaseNNN
export DATABASE_SERVER=courseserverNNN
export AZURE_USER=azuresql
export AZURE_PASSWORD=[enter your password]

  3. Execute o utilitário bcp para criar um arquivo de formato com base no esquema da tabela dbo.Courses no banco de dados. O arquivo de formato especifica que os dados estão no formato de caractere (-c) e separados por vírgulas (-t,).

    bcp "[$DATABASE_NAME].[dbo].[courses]" format nul -c -f courses.fmt -t, -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD

  4. No editor de código, abra courses.fmt. O arquivo de formato gerado pelo comando anterior.

    code courses.fmt

    O arquivo deve ter esta aparência:

    14.0
2
1       SQLCHAR             0       12      ","    1     CourseID                                     ""
2       SQLCHAR             0       50      "\n"   2     CourseName                                   SQL_Latin1_General_CP1_CI_AS

  5. Examine o arquivo. Os dados da primeira coluna do arquivo separado por vírgulas entram na coluna CourseID da tabela dbo.Courses. O segundo campo entra na coluna CourseName. A segunda coluna é baseada em caracteres e tem uma ordenação associada a ela. Espera-se que o separador de campos no arquivo seja uma vírgula. O terminador de linha (após o segundo campo) deve ser um caractere de nova linha. Em um cenário real, seus dados podem não estar tão bem organizados. Você pode ter diferentes separadores de campo e campos em uma ordem diferente das colunas. Nessa situação, edite o arquivo de formato para alterar esses itens campo por campo. Pressione Ctrl+Q para sair do editor.

  6. Execute o seguinte comando para importar os dados para o arquivo courses.csv usando o formato especificado no arquivo courses.fmt corrigido. O sinalizador -F 2 direciona o utilitário bcp para iniciar a importação de dados a partir da linha 2 no arquivo de dados. A primeira linha contém cabeçalhos.

    bcp "[$DATABASE_NAME].[dbo].[courses]" in courses.csv -f courses.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2

    Verifique se o utilitário bcp importará nove linhas sem relatar erros.

  7. Execute a sequência de operações a seguir para importar os dados para a tabela dbo.Modules do arquivo modules.csv.

    1. Gere um arquivo de formato.

      bcp "[$DATABASE_NAME].[dbo].[modules]" format nul -c -f modules.fmt -t, -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD

    2. Importe os dados do arquivo modules.csv para a tabela dbo.Modules do banco de dados.

      bcp "[$DATABASE_NAME].[dbo].[modules]" in modules.csv -f modules.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2

      Verifique se este comando importa 16 linhas.

  8. Execute a sequência de operações a seguir para importar os dados para a tabela dbo.StudyPlans do arquivo studyplans.csv.

    1. Gere um arquivo de formato.

      bcp "[$DATABASE_NAME].[dbo].[studyplans]" format nul -c -f studyplans.fmt -t, -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD

    2. Importe os dados do arquivo studyplans.csv para a tabela dbo.StudyPlans do banco de dados.

      bcp "[$DATABASE_NAME].[dbo].[studyplans]" in studyplans.csv -f studyplans.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2

      Verifique se este comando importa 45 linhas.

Consultar os dados no banco de dados

  1. Retorne ao portal do Azure.

  2. No menu do portal do Azure, selecione Bancos de dados SQL.

  3. No painel Banco de dados SQL, selecione coursedatabaseNNN. O painel coursedatabaseNNN é exibido.

  4. No painel de menu à esquerda, selecione Editor de consultas. O painel Editor de consultas para coursedatabaseNNN é exibido.

  5. Insira os valores a seguir para cada configuração.

    Configuração Valor
    Autenticação do SQL Server
    Logon azuresql
    Senha Insira a senha para esse usuário.

  6. Selecione OK para se conectar ao serviço de banco de dados.

  7. No painel Consulta 1, insira a instrução T-SQL a seguir, depois selecione Executar.

    SELECT * FROM dbo.Courses

    Essa instrução recupera os dados da tabela dbo.Courses. A janela de resultados deve exibir nove linhas.

    Screenshot of the query editor in the Azure portal, showing the data retrieved from the Courses table.

  8. Altere a consulta conforme mostrado a seguir e, em seguida, selecione Executar.

    SELECT * FROM dbo.Modules

    Desta vez, você deve ver os módulos na janela Resultados. Há 16 linhas.

  9. Volte para o Cloud Shell e execute o comando a seguir para se conectar ao banco de dados.

    sqlcmd -S "$DATABASE_SERVER.database.windows.net" -d "$DATABASE_NAME" -U $AZURE_USER -P $AZURE_PASSWORD

  10. No prompt 1>, execute o comando T-SQL a seguir para buscar os dados na tabela dbo.StudyPlans.

    SELECT * FROM StudyPlans;  
GO

    Essa consulta deve retornar 45 linhas.

  11. No prompt 1>, digite exit para fechar o utilitário sqlcmd.

Você criou um banco de dados individual usando o Banco de Dados SQL. Em seguida, criou tabelas usando o Editor de Consultas no portal do Azure. Depois usou o utilitário bcp para carregar dados de vários arquivos de dados delimitados por vírgula. Por fim, executou consultas em tabelas no banco de dados por meio do editor de consultas no portal do Azure e do utilitário sqlcmd no Cloud Shell.