Esercitazione: Estrarre, trasformare e caricare dati con Azure HDInsight

In questa esercitazione viene eseguita un'operazione ETL (Extract, Transform, Load), ossia estrazione, trasformazione e caricamento dei dati. Un file di dati non elaborati in formato CSV verrà importato in un cluster Azure HDInsight, trasformato con Apache Hive e caricato nel database SQL di Azure con Apache Sqoop.

In questa esercitazione verranno illustrate le procedure per:

• Estrarre e caricare i dati in un cluster HDInsight.
• Trasformare i dati con Apache Hive.
• Caricare i dati nel database SQL di Azure con Sqoop.

Se non si ha una sottoscrizione di Azure, creare un account gratuito prima di iniziare.

Prerequisiti

• Un account di archiviazione con uno spazio dei nomi gerarchico (Azure Data Lake Storage Gen2) configurato per HDInsight

  Vedere Usare Archiviazione Azure Data Lake Storage Gen2 con cluster Azure HDInsight.

• Cluster Hadoop basato su Linux in HDInsight

  Vedere Avvio rapido: Iniziare a usare Apache Hadoop e Apache Hive in Azure HDInsight con il portale di Azure.

• Database SQL di Azure

  Si usa Azure SQL Database come archivio dati di destinazione. Se non è presente alcun database nel database SQL di Azure, vedere Creare un database nel database SQL di Azure nel portale di Azure.

• Interfaccia della riga di comando di Azure

  Se l'interfaccia della riga di comando di Azure non è installata, vedere Installare l'interfaccia della riga di comando di Azure.

• Un client Secure Shell (SSH)

  Per altre informazioni, vedere Connettersi a HDInsight (Hadoop) con SSH.

Scaricare, estrarre e quindi caricare i dati

In questa sezione si scaricano i dati dei voli di esempio. Caricare quindi i dati nel cluster HDInsight e quindi copiarli nell'account Data Lake Storage Gen2.

1. Scaricare il file On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip . Questo file contiene i dati di anteprima.

2. Aprire un prompt dei comandi e usare il comando Secure Copy (scp) seguente per caricare il file con estensione zip nel nodo head del cluster HDInsight:

   scp On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net:
   

   • Sostituire il <ssh-user-name> segnaposto con il nome utente SSH per il cluster HDInsight.
   • Sostituire il segnaposto <cluster-name> con il nome del cluster HDInsight.

   Se si usa una password per autenticare il nome utente SSH, viene richiesta la password.

   Se si usa una chiave pubblica, può essere necessario usare il parametro -i e specificare il percorso alla chiave privata corrispondente. Ad esempio: scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. Al termine del caricamento connettersi al cluster tramite SSH. Nel prompt dei comandi immettere il comando seguente:

   ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
   

4. Usare il comando seguente per decomprimere il file con estensione zip:

   unzip <file-name>.zip
   

   Questo comando estrae un file con estensione csv.

5. Usare il comando seguente per creare il contenitore di Data Lake Storage Gen2.

   hadoop fs -D "fs.azure.createRemoteFileSystemDuringInitialization=true" -ls abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/
   

   Sostituire il segnaposto <container-name> con il nome che si vuole assegnare al contenitore.

   Sostituire il segnaposto <storage-account-name> con il nome del proprio account di archiviazione.

6. Usare il comando seguente per creare una directory.

   hdfs dfs -mkdir -p abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data
   

7. Usare il comando seguente per copiare il file con estensione csv nella directory:

   hdfs dfs -put "On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2016_1.csv" abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data/
   

   Se il nome del file contiene spazi o caratteri speciali, racchiuderlo tra virgolette.

Trasformare i dati

In questa sezione si usa Beeline per eseguire un processo Apache Hive.

Come parte del processo Apache Hive, importare i dati contenuti nel file con estensione csv in una tabella Apache Hive denominata delays.

1. Dal prompt SSH già disponibile per il cluster HDInsight, usare il comando seguente per creare e modificare un nuovo file denominato flightdelays.hql:

   nano flightdelays.hql
   

2. Modificare il testo seguente sostituendo i segnaposto e <storage-account-name> con il <container-name> nome del contenitore e dell'account di archiviazione. Copiare quindi e incollare il testo nella console nano premendo MAIUSC insieme al pulsante di selezione del mouse con il pulsante destro del mouse.

     DROP TABLE delays_raw;
     -- Creates an external table over the csv file
     CREATE EXTERNAL TABLE delays_raw (
        YEAR string,
        FL_DATE string,
        UNIQUE_CARRIER string,
        CARRIER string,
        FL_NUM string,
        ORIGIN_AIRPORT_ID string,
        ORIGIN string,
        ORIGIN_CITY_NAME string,
        ORIGIN_CITY_NAME_TEMP string,
        ORIGIN_STATE_ABR string,
        DEST_AIRPORT_ID string,
        DEST string,
        DEST_CITY_NAME string,
        DEST_CITY_NAME_TEMP string,
        DEST_STATE_ABR string,
        DEP_DELAY_NEW float,
        ARR_DELAY_NEW float,
        CARRIER_DELAY float,
        WEATHER_DELAY float,
        NAS_DELAY float,
        SECURITY_DELAY float,
        LATE_AIRCRAFT_DELAY float)
     -- The following lines describe the format and location of the file
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
     LINES TERMINATED BY '\n'
     STORED AS TEXTFILE
     LOCATION 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data';
   
     -- Drop the delays table if it exists
     DROP TABLE delays;
     -- Create the delays table and populate it with data
     -- pulled in from the CSV file (via the external table defined previously)
     CREATE TABLE delays
     LOCATION 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/processed'
     AS
     SELECT YEAR AS year,
        FL_DATE AS FlightDate, 
        substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS IATA_CODE_Reporting_Airline,
        substring(CARRIER, 2, length(CARRIER) -1) AS Reporting_Airline, 
        substring(FL_NUM, 2, length(FL_NUM) -1) AS Flight_Number_Reporting_Airline,
        ORIGIN_AIRPORT_ID AS OriginAirportID,
        substring(ORIGIN, 2, length(ORIGIN) -1) AS OriginAirportSeqID,
        substring(ORIGIN_CITY_NAME, 2) AS OriginCityName,
        substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS OriginState,
        DEST_AIRPORT_ID AS DestAirportID,
        substring(DEST, 2, length(DEST) -1) AS DestAirportSeqID,
        substring(DEST_CITY_NAME,2) AS DestCityName,
        substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS DestState,
        DEP_DELAY_NEW AS DepDelay,
        ARR_DELAY_NEW AS ArrDelay,
        CARRIER_DELAY AS CarrierDelay,
        WEATHER_DELAY AS WeatherDelay,
        NAS_DELAY AS NASDelay,
        SECURITY_DELAY AS SecurityDelay,
        LATE_AIRCRAFT_DELAY AS LateAircraftDelay
     FROM delays_raw;
   

3. Salvare il file digitando CTRL+X e quindi digitando Y quando richiesto.

4. Per avviare Hive ed eseguire il flightdelays.hql file, usare il comando seguente:

   beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
   

5. Al termine dell'esecuzione dello flightdelays.hql script, usare il comando seguente per aprire una sessione di Beeline interattiva:

   beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
   

6. Quando si riceve il prompt jdbc:hive2://localhost:10001/>, usare la query seguente per recuperare i dati dai dati sui ritardi dei voli importati:

   INSERT OVERWRITE DIRECTORY '/tutorials/flightdelays/output'
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
   SELECT regexp_replace(OriginCityName, '''', ''),
     avg(WeatherDelay)
   FROM delays
   WHERE WeatherDelay IS NOT NULL
   GROUP BY OriginCityName;
   

   Grazie a questa query vengono recuperati un elenco di città in cui si sono verificati ritardi meteo e il tempo di ritardo medio che può essere salvato in abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Sqoop leggerà in seguito i dati da questo percorso e li esporterà nel database SQL di Azure.

7. Per uscire da Beeline, immettere !quit al prompt dei comandi.

Creare una tabella del database SQL

Per eseguire questa operazione, è necessario il nome del server del database SQL. Completare questi passaggi per trovarlo.

1. Accedere al portale di Azure.

2. Selezionare Database SQL.

3. Filtrare il nome del database che si sceglie di usare. Il nome del server è indicato nella colonna Nome server.

4. Filtrare il nome del database che si vuole usare. Il nome del server è indicato nella colonna Nome server.

   

   Sono disponibili diversi modi per connettersi al database SQL per creare una tabella. Nei seguenti passaggi viene usato FreeTDS dal cluster HDInsight.

5. Per installare FreeTDS, usare il comando seguente da una connessione SSH al cluster:

   sudo apt-get --assume-yes install freetds-dev freetds-bin
   

6. Al termine dell'installazione, usare il comando seguente per connettersi a Database SQL.

   TDSVER=8.0 tsql -H '<server-name>.database.windows.net' -U '<admin-login>' -p 1433 -D '<database-name>'
   

   • Sostituire il segnaposto <server-name> con il nome del server logico SQL.

   • Sostituire il <admin-login> segnaposto con il nome utente amministratore per database SQL.

   • Sostituire il segnaposto <database-name> con il nome del database.

   Quando richiesto, immettere la password per il nome utente amministratore database SQL.

   L'output che si riceve è simile al testo seguente:

   locale is "en_US.UTF-8"
   locale charset is "UTF-8"
   using default charset "UTF-8"
   Default database being set to sqooptest
   1>
   

7. Al prompt 1> immettere le istruzioni seguenti:

   CREATE TABLE [dbo].[delays](
   [OriginCityName] [nvarchar](50) NOT NULL,
   [WeatherDelay] float,
   CONSTRAINT [PK_delays] PRIMARY KEY CLUSTERED
   ([OriginCityName] ASC))
   GO
   

8. Dopo aver immesso l'istruzione GO, vengono valutate le istruzioni precedenti.

   La query crea una tabella denominata delays, con un indice cluster.

9. Usare la query seguente per verificare se la tabella è stata creata:

   SELECT * FROM information_schema.tables
   GO
   

   L'output è simile al testo seguente:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

10. Per uscire dall'utilità tsql, immettere exit al prompt 1>.

Esportare e caricare i dati

Nelle sezioni precedenti sono stati copiati i dati trasformati nella posizione abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. In questa sezione viene usato Sqoop per esportare i dati da abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output alla tabella creata nel database SQL di Azure.

1. Usare il comando seguente per verificare che Sqoop possa visualizzare il database SQL:

   sqoop list-databases --connect jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433 --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD>
   

   Questo comando restituisce un elenco di database, compreso il database in cui è stata creata la tabella delays.

2. Usare il comando seguente per esportare i dati dalla tabella hivesampletable alla tabella delays:

   sqoop export --connect 'jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433;database=<DATABASE_NAME>' --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD> --table 'delays' --export-dir 'abfs://<container-name>@.dfs.core.windows.net/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
   

   Sqoop si connette al database contenente la tabella delays ed esporta i dati dalla directory /tutorials/flightdelays/output nella tabella delays.

3. Al termine dell'esecuzione del comando sqoop, usare l'utilità tsql per connettersi al database:

   TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>
   

4. Usare le istruzioni seguenti per verificare che i dati siano stati esportati nella tabella delays:

   SELECT * FROM delays
   GO
   

   Dovrebbe essere visualizzato un elenco di dati della tabella. La tabella include il nome della città e il tempo di ritardo medio dei voli per la città.

5. Immettere exit per uscire dall'utilità tsql.

Pulire le risorse

Tutte le risorse usate in questa esercitazione sono preesistenti. Non è necessario effettuare operazioni di pulizia.

Passaggi successivi

Per altre informazioni su come usare i dati in HDInsight, vedere l'articolo seguente:

Usare Azure Data Lake Storage Gen2 con cluster Azure HDInsight