Esercizio: Creare tabelle, importare in blocco ed eseguire query sui dati

  • 10 minuti

L'università attualmente archivia i dati in una serie di file con valori delimitati da virgole. È necessario eseguire la migrazione di questi dati nel database SQL di Azure.

In questo esercizio si creerà un server di database e un database singolo usando il servizio database SQL. Si creeranno quindi le tabelle e si importeranno i dati nel database. Si userà infine l'editor di query e l'utilità sqlcmd per eseguire query sui dati.

Esaminare i dati delimitati da virgole già esistenti

  1. In Azure Cloud Shell eseguire il comando seguente per scaricare i file di dati e il codice dell'applicazione per il sistema dell'università.

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

  2. Eseguire i comandi seguenti per spostare i dati di esempio nella rispettiva cartella ed elencare i file nella cartella.

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

    Questa cartella contiene tre file: courses.csv, modules.csv e studyplans.csv.

  3. Visualizzare il contenuto del file courses.csv.

    cat courses.csv

    Questo file contiene i dati delimitati da virgole seguenti. I dati includono il nome e l'ID di ogni corso offerto dall'università.

    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. Visualizzare il contenuto del file modules.csv.

    cat modules.csv

    Questo file contiene i diversi moduli che gli studenti possono usare per soddisfare i requisiti dei corsi. Ogni modulo ha un nome e un codice di identificazione.

    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. Visualizzare il contenuto del file studyplans.csv.

    cat studyplans.csv

    Questo file contiene i dati che specificano quali moduli gli studenti devono superare per completare un corso. La colonna Sequence indica l'ordine in cui gli studenti devono completare ogni modulo. Per il corso 1 (Computer Science), ad esempio, gli studenti devono superare il modulo CS101 prima del modulo MA101. Qui è riportata una parte dei dati.

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

Creare un server di database e un database con il database SQL

È il momento di creare il database e il server per l'archiviazione dei dati per l'app.

  1. Accedere al portale di Azure usando l'account con cui è stata attivata la sandbox.

  2. Nel menu del portale di Azure, in Servizi di Azure, selezionare Crea una risorsa.

    Screenshot of Azure portal menu and Create a resource option.

    Viene visualizzato il riquadro Crea una risorsa.

  3. Nel riquadro del menu a sinistra selezionare Database e quindi Database SQLnell'elenco dei Servizi di Azure più diffusi.

    Screenshot of the Databases and SQL Database options.

    Viene visualizzato il riquadro Crea database SQL.

  4. Nella scheda Informazioni di base immettere i valori indicati di seguito per ogni impostazione.

    Impostazione valore
    Dettagli del progetto
    Abbonamento Concierge Subscription
    Gruppo di risorse [Gruppo di risorse sandbox]
    Dettagli database
    Nome database Il database deve avere un nome univoco. È consigliabile usare un nome simile a coursedatabaseNNN, dove NNN è un numero casuale.
    Server Selezionare il collegamento Crea nuovo e nel pannello Nuovo server immettere i dettagli indicati nella tabella seguente.
    Usare il pool elastico SQL? No
    Calcolo e archiviazione Utilizzo generico

    Per il server immettere i valori seguenti per ogni impostazione.

    Impostazione Valore
    Nome server courseserverNNN, dove NNN è lo stesso numero scelto per il database
    Accesso amministratore server azuresql
    Password Immettere una password che soddisfi i requisiti.
    Conferma password Confermare la password.
    Ubicazione Stati Uniti centrali

  5. Selezionare OK.

  6. Selezionare Avanti: Rete.

  7. Nella scheda Rete immettere i valori seguenti per ogni impostazione.

    Impostazione Valore
    Connettività di rete
    Metodo di connettività Endpoint pubblico
    Regole del firewall
    Consenti alle risorse e ai servizi di Azure di accedere a questo server Sì
    Aggiungi indirizzo IP client corrente Sì

  8. Selezionare Rivedi e crea.

  9. Seleziona Crea. Attendere che il server e il database vengano creati prima di continuare.

Creare le tabelle

È ora possibile creare le tabelle in cui archiviare i dati dei file con estensione csv.

  1. Selezionare Vai alla risorsa. Viene visualizzato il database SQL per coursedatabaseNNN.

  2. Nel riquadro del menu a sinistra selezionare Editor di query (anteprima).

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

    Viene visualizzato il riquadro Editor di query per coursedatabaseNNN.

  3. Immettere i seguenti valori per ogni impostazione.

    Impostazione valore
    Autenticazione di SQL Server
    Account di accesso azuresql
    Password Specificare la password usata quando è stato creato questo utente.

    Nota

    Se viene visualizzato un errore durante l'accesso al database, controllare l'indirizzo IP indicato nell'errore e assicurarsi che corrisponda a quello aggiunto come IP client. A tale scopo selezionare Panoramica>Imposta firewall server.

  4. Selezionare OK per connettersi al servizio del database.

  5. Nel riquadro Query 1 immettere l'istruzione Transact-SQL (T-SQL) seguente e quindi selezionare Esegui. Questa istruzione crea una nuova tabella in cui inserire le informazioni sui corsi. Verificare che l'istruzione venga eseguita senza errori.

    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. Sovrascrivere l'istruzione esistente con l'istruzione seguente che crea una tabella in cui inserire i moduli. Selezionare Esegui e quindi verificare che l'istruzione venga eseguita senza errori.

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

  7. Modificare l'istruzione per creare una tabella denominata StudyPlanse quindi selezionare Esegui.

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

  8. Nella finestra del database selezionare il pulsante Aggiorna sulla barra degli strumenti. Espandere Tabelle e quindi espandere ogni singola tabella. Verranno visualizzate le tre tabelle (dbo.Courses, dbo.Modulese dbo.StudyPlans), insieme alle colonne e alla chiave primaria per ogni tabella.

    Nota

    dbo significa database owner (proprietario del database). Si tratta dello schema predefinito nel database. Tutte e tre le tabelle sono state create in questo schema.

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

Importare i dati

  1. Tornare a Cloud Shell e assicurarsi di essere nella educationdata cartella .

    cd ~/educationdata

  2. Creare le variabili che verranno usate nei passaggi successivi. Sostituire NNN con il numero usato per il database e il server.

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

  3. Eseguire l'utilità bcp per creare un file di formato dallo schema della dbo.Courses tabella nel database. Il file di formato specifica che i dati sono in formato carattere (-c) e delimitati da virgole (-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. Nell'editor del codice aprire courses.fmt. File di formato generato dal comando precedente.

    code courses.fmt

    Il file dovrebbe avere un aspetto simile a questo:

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

  5. Esaminare il file. I dati nella prima colonna del file con valori delimitati da virgole verranno inseriti nella colonna CourseID della tabella dbo.Courses. Il secondo campo verrà inserito nella colonna CourseName. Alla seconda colonna, basata su caratteri, sono associate regole di confronto. Il separatore dei campi previsto nel file è la virgola. Il carattere di terminazione della riga (dopo il secondo campo) deve essere un carattere di nuova riga. In uno scenario reale è possibile che i dati non siano organizzati in modo così ordinato. Possono essere presenti separatori di campi e campi diversi in un ordine diverso rispetto alle colonne. In una situazione di questo tipo è possibile modificare il file di formato e cambiare questi elementi campo per campo. Premere CTRL+Q per chiudere l'editor.

  6. Eseguire il comando seguente per importare i dati nel file courses.csv nel formato specificato dal file courses.fmt modificato. Il flag -F 2 indica all'utilità bcp di avviare l'importazione dei dati dalla riga 2 nel file di dati. La prima riga contiene le intestazioni.

    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

    Verificare che l'utilità bcp importi nove righe e non segnali errori.

  7. Eseguire la sequenza di operazioni seguente per importare i dati per la dbo.Modules tabella dal modules.csv file.

    1. Generare un file di 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. Importare i dati dal file modules.csv nella tabella dbo.Modules del database.

      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

      Verificare che questo comando importi 16 righe.

  8. Eseguire la sequenza di operazioni seguente per importare i dati per la dbo.StudyPlans tabella dal studyplans.csv file.

    1. Generare un file di 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. Importare i dati dal file studyplans.csv nella tabella dbo.StudyPlans del database.

      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

      Verificare che questo comando importi 45 righe.

Eseguire query sui dati nel database

  1. Tornare al portale di Azure.

  2. Selezionare Database SQL nel menu del portale di Azure.

  3. Nel riquadro Database SQL selezionare coursedatabaseNNN. Viene visualizzato il riquadro coursedatabaseNNN.

  4. Nel riquadro del menu a sinistra selezionare Editor di query. Viene visualizzato il riquadro Editor di query per coursedatabaseNNN.

  5. Immettere i seguenti valori per ogni impostazione.

    Impostazione valore
    Autenticazione di SQL Server
    Account di accesso azuresql
    Password Immettere la password per questo utente.

  6. Selezionare OK per connettersi al servizio del database.

  7. Nel riquadro Query 1 immettere l'istruzione T-SQL seguente e quindi selezionare Esegui.

    SELECT * FROM dbo.Courses

    Questa istruzione consente di recuperare i dati della tabella dbo.Courses. Nella finestra dei risultati devono essere visualizzate nove righe.

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

  8. Modificare la query come indicato di seguito e quindi selezionare Esegui.

    SELECT * FROM dbo.Modules

    Questa volta devono essere visualizzati i moduli nella finestra Risultati, 16 righe.

  9. Tornare a Cloud Shell ed eseguire il comando seguente per connettersi al database.

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

  10. Al prompt 1> eseguire il comando T-SQL seguente per recuperare i dati dalla tabella dbo.StudyPlans.

    SELECT * FROM StudyPlans;  
GO

    Questa query deve restituire 45 righe.

  11. Al prompt 1> digitare exit per chiudere l'utilità sqlcmd.

Si è creato un database singolo usando il database SQL. Si è quindi usato l'editor di query nel portale di Azure per creare le tabelle. Subito dopo è stata usata l'utilità bcp per caricare dati da una serie di file di dati con valori delimitati da virgole. Sono state infine eseguite query sulle tabelle del database dall'editor di query nel portale di Azure e dall'utilità sqlcmd in Cloud Shell.