Indietro

Prossima

Creare tabelle, importare in blocco ed eseguire query sui dati

Completato

L'università necessita di una posizione in cui archiviare i dati che attualmente si trovano all'interno di file di testo e vuole rendere relazionali i dati per migliorarne l'accessibilità. È stato selezionato un database singolo nel database SQL di Azure come servizio di archiviazione per questi dati. Si esaminerà ora il database SQL e si vedrà come caricare i dati ed eseguire query su di essi.

Creare un database singolo usando il portale di Azure

Il database SQL di Azure è un servizio di database relazionale basato sulla versione stabile più recente del motore di database di Microsoft SQL Server. Il database SQL è un database di facile uso e ad elevate prestazioni, affidabile ed estremamente sicuro. È possibile usare il database SQL per creare nuove app, siti Web e microservizi nel linguaggio di programmazione preferito, senza dover gestire l'infrastruttura.

È possibile creare un database singolo tramite il portale di Azure o usando Azure PowerShell o l'interfaccia della riga di comando di Azure.

1. Dal menu del portale di Azure, selezionare Crea una risorsa.

   

2. Selezionare Database e quindi Database SQL.

   

3. Per usare l'interfaccia della riga di comando, eseguire i comandi az sql server create e az sql db create.

4. Per usare PowerShell, eseguire i comandi New-AzSqlServere New-AzSqlDatabase.

Quando si crea un database singolo, viene richiesto di specificare il server da usare per gestirlo. È possibile creare un nuovo server o usare un server esistente.

Quando si crea un nuovo server e si sceglie di usare l'autenticazione di SQL, viene chiesto di specificare un nome utente amministratore e una password per il server. Usare queste credenziali per connettersi al server per eseguire attività amministrative e accedere ai database controllati dal server stesso. Il database SQL supporta anche l'autenticazione Di Microsoft Entra. È anche possibile scegliere di usare l'autenticazione SQL e Microsoft Entra. È tuttavia sempre necessario impostare un amministratore o creare un account amministratore quando si crea un nuovo server. Concedere quindi l'accesso agli account archiviati in Microsoft Entra ID.

Ogni server di database è protetto da un firewall, per bloccare processi potenzialmente dannosi. È possibile aprire il firewall per altri servizi di Azure, nonché abilitare in modo selettivo l'accesso ad altri computer in base all'indirizzo o all'intervallo di indirizzi IP di questi. Il database SQL offre anche funzionalità di sicurezza dei dati avanzate che consentono di:

• Specificare la riservatezza dei dati all'interno di colonne singole nelle tabelle.
• Valutare la vulnerabilità dei database ed eseguire le procedure di correzione necessarie.
• Inviare avvisi quando viene rilevata una minaccia.

Per configurare le risorse è possibile usare il modello vCore, che specifica le risorse di memoria, I/O e CPU da allocare. È possibile ridimensionare le risorse di calcolo e di archiviazione in modo indipendente. In alternativa, è possibile assegnare le risorse in termini di unità di elaborazione di database (DTU, Database Transaction Unit). Una DTU è una misura del costo calibrato delle risorse necessarie per eseguire una transazione di cui è stato effettuato un benchmark.

Se si usano più database e le risorse necessarie per i database variano, è possibile usare il pool elastico SQL. Questa funzionalità consente di condividere un pool di risorse tra database in pool in base alla domanda.

Quando si crea un database, si specifica anche la modalità di confronto dei dati. Le regole di confronto definiscono il modo in cui il database ordina e confronta i dati, oltre a specificare il set di caratteri da usare per i dati di testo. Dopo aver creato il database è possibile modificare le regole di confronto, ma è consigliabile non farlo dopo che i dati sono stati inseriti nel database.

Creare tabelle

Per creare tabelle, è possibile usare uno qualsiasi di questi strumenti:

• Editor di query nel portale di Azure
• Utilità sqlcmd e Cloud Shell
• SQL Server Management Studio

Indipendentemente dallo strumento scelto, per definire la tabella si usa il comando Transact-SQL (T-SQL) CREATE TABLE. Per le tabelle, il database SQL di Azure supporta chiavi primarie, chiavi esterne, indici e trigger. Il codice di esempio seguente crea una coppia di tabelle correlate e un indice non cluster. È possibile eseguire questi comandi in batch nell'editor di query o nell'utilità sqlcmd.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

CREATE TABLE MyTable2
(
    AnotherColumn1 INT NOT NULL,
    AnotherColumn2 INT NOT NULL REFERENCES MyTable,
    AnotherColumn3 VARCHAR(50) NULL,
    PRIMARY KEY (AnotherColumn1, AnotherColumn2)
);

CREATE INDEX cci ON MyTable2(AnotherColumn3);

Per accedere all'editor di query nel portale di Azure, passare alla pagina del database e selezionare Editor di query. Vengono richieste le credenziali. È possibile impostare Tipo di autorizzazione su Autenticazione SQL Server e immettere il nome utente e la password impostati durante la creazione del database. In alternativa, è possibile selezionare Autenticazione della password Active Directory e specificare le credenziali di un utente autorizzato in Microsoft Entra ID. Se la funzione Single Sign-On di Active Directory è abilitata, è possibile connettersi usando l'identità di Azure.

Immettere il codice T-SQL nel riquadro delle query e quindi selezionare Esegui per eseguirlo. Se l'istruzione T-SQL è una query, tutte le righe restituite vengono visualizzate nel riquadro Risultati. Il riquadro Messaggi visualizza alcune informazioni, ad esempio il numero di righe restituite o eventuali errori verificatisi:

Per usare l'utilità sqlcmd, passare a Cloud Shell ed eseguire il comando seguente. Sostituire <server> con il nome del server di database creato in precedenza, <database> con il nome del database e <user name> e <password> con le proprie credenziali.

sqlcmd -S <server>.database.windows.net -d <database> -U <username> -P <password>

Se il comando di accesso ha esito positivo, verrà visualizzato il prompt 1>. È possibile immettere comandi T-SQL su più righe e quindi digitare GO per eseguirli.

Importare in blocco i dati con bcp

Microsoft offre diversi strumenti che è possibile usare per caricare i dati nel database SQL di Azure:

• SQL Server Integration Services (SSIS)
• Istruzione SQL BULK INSERT
• Utilità bcp (Bulk Copy Program, programma per la copia bulk)

L'utilità bcp viene spesso usata perché è comoda e consente di creare facilmente script per importare dati in più tabelle. bcp è uno strumento da riga di comando che è possibile usare per importare ed esportare dati da un database. Per l'importazione dei dati, bcp richiede i tre elementi seguenti:

• I dati di origine da caricare.
• Una tabella esistente nel database di destinazione.
• Un file di formato che definisce il formato dei dati e il modo in cui eseguire il mapping dei dati alle colonne della tabella di destinazione.

L'utilità bcp è flessibile. I dati di origine possono avere praticamente qualsiasi formato strutturato. Il file di formato indica il layout dei dati e se i dati sono binari o basati su caratteri. Specifica inoltre il tipo e la lunghezza di ogni elemento e la modalità di delimitazione dei dati. Il file di formato specifica inoltre come eseguire il mapping di ogni elemento del file a una colonna della tabella. È importante definire correttamente il contenuto di questo file. In caso contrario, è possibile che i dati non vengano importati o che vengano letti nelle colonne non corrette.

Si supponga di avere i dati seguenti nel file mydata.csv e di voler importare i dati nella tabella MyTable creata in precedenza.

Column1,Column2
99,some text
101,some more text
97,another bit of text
87,yet more text
33,a final bit of text

La prima riga contiene i nomi dei campi, che non corrispondono alle colonne della tabella. I dati sono delimitati da virgole e ogni riga termina con un carattere di nuova riga. Si ricordi che l'ordine delle colonne nel file può essere diverso dalla tabella. In questo esempio la prima colonna della tabella è numerica e la seconda colonna è di tipo stringa, come illustrato di seguito:

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

È possibile usare il comando bcp per creare un file di formato per l'importazione. Il comando bcp è in grado di creare un file di formato basato sullo schema della tabella di destinazione nel database. È quindi possibile modificare il file in modo che corrisponda ai dati nel file di origine.

Eseguire il comando seguente per creare un file di formato. Sostituire gli elementi tra parentesi acute con i valori per database, server, nome utente e password:

bcp <database>.dbo.mytable format nul -c -f mytable.fmt -t, -S <server>.database.windows.net -U <username> -P <password>

L'utilità bcp ha diversi parametri che controllano le funzionalità dell'utilità. e nello specifico:

• Tabella di destinazione (<database>.<schema>.<table>)
• Dati da importare e dettagli sui dati stessi (format nul -c -f mytable.fmt -t,)
• Dettagli di connessione per il database (-S <server>.database.windows.net -U <username> -P <password>)

Per la sintassi completa e i parametri della riga di comando per l'utilità, vedere la documentazione della Guida.

Il comando genera il file di formato mytable.fmt con contenuto simile al seguente:

14.0
2
1       SQLCHAR             0       12      ","    1     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   2     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

La prima riga indica il numero di versione interno del database SQL. La seconda riga indica il numero di colonne nella tabella di origine. Le ultime due righe indicano come eseguire il mapping dei dati nel file di origine in queste colonne.

Entrambe le righe iniziano con un numero che rappresenta il numero di colonna nella tabella. Il secondo campo (SQLCHAR) specifica che, quando si usa questo file di formato per importare i dati, ogni campo del file di origine contiene dati di tipo carattere. L'utilità bcp tenterà di convertire questi dati nel tipo appropriato per la colonna corrispondente nella tabella. Il campo successivo (12 e 50) è la lunghezza dei dati in ogni colonna del database. Non modificare questo campo. Gli elementi successivi ("," e "\n") sono, rispettivamente, il carattere di terminazione del campo nel file di origine e il carattere di nuova riga. La colonna successiva contiene il numero di campi nel file di origine. Il penultimo campo (MyColumn1 e MyColumn2) è il nome della colonna nel database. L'ultimo campo corrisponde alle regole di confronto da usare, che vengono applicate solo ai dati di tipo carattere nel database.

Si ricordi che i campi nel file di origine sono in un ordine diverso rispetto alle colonne nel database. È quindi necessario modificare il file di formato e cambiare i numeri di campo, come illustrato qui:

14.0
2
1       SQLCHAR             0       12      ","    2     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   1     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

Verrà eseguito il mapping dei dati nel campo 2 del file di origine alla prima colonna del database e il mapping del campo 1 alla seconda colonna.

A questo punto è possibile usare il comando bcp per importare i dati, come indicato di seguito:

bcp <database>.dbo.mytable in mydata.csv -f mytable.fmt -S <server>.database.windows.net -U <username> -P <password> -F 2

Il flag in specifica che per l'importazione dei dati si sta usando bcp. È possibile usare out per trasferire dati da un database a un file. Il flag -F 2 indica che l'operazione di importazione deve iniziare dalla riga 2 del file di origine. Si ricordi che la prima riga contiene intestazioni anziché dati.

Il comando dovrebbe essere eseguito correttamente e restituire messaggi simili a questi esempi:

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 46     Average : (108.7 rows per sec.)

La riga importante nell'output è "5 rows copied" (5 righe copiate). Questa riga segnala il numero di righe del file di origine contenenti i dati importati. Se questo numero è diverso (o corrisponde a zero), è possibile che il file di formato non sia corretto.

Eseguire query sui dati

Per verificare che l'importazione sia riuscita, eseguire una query sui dati. È possibile usare l'editor di query del portale di Azure. In alternativa, è possibile usare l'utilità sqlcmd per connettersi al database dalla riga di comando. In entrambi i casi si esegue un'istruzione SELECT simile alla seguente:

SELECT *
FROM MyTable;

Vengono visualizzati i risultati seguenti.

Continua