Esercitazione: Estrarre, trasformare e caricare dati usando Apache Hive in Azure HDInsightTutorial: Extract, transform, and load data using Apache Hive on Azure HDInsight

In questa esercitazione si importa un file CSV di dati non elaborati in un archivio cluster HDInsight e quindi si trasformano i dati usando Apache Hive in Azure HDInsight.In this tutorial, you take a raw CSV data file, import it into an HDInsight cluster storage, and then transform the data using Apache Hive on Azure HDInsight. Dopo averli trasformati, i dati vengono caricati in un database SQL di Azure tramite Apache Sqoop.Once the data is transformed, you load that data into an Azure SQL database using Apache Sqoop. In questo articolo si usano dati pubblicamente disponibili sui voli.In this article, you use publicly available flight data.

Importante

I passaggi descritti in questo documento richiedono un cluster HDInsight che usa Linux.The steps in this document require an HDInsight cluster that uses Linux. Linux è l'unico sistema operativo usato in Azure HDInsight versione 3.4 o successiva.Linux is the only operating system used on Azure HDInsight version 3.4 or later. Per altre informazioni, vedere la sezione relativa al ritiro di HDInsight in Windows.For more information, see HDInsight retirement on Windows.

Questa esercitazione illustra le attività seguenti:This tutorial covers the following tasks:

  • Scaricare i dati di esempio sui voliDownload the sample flight data
  • Caricare i dati in un cluster HDInsightUpload data to an HDInsight cluster
  • Trasformare i dati usando HiveTransform the data using Hive
  • Creare una tabella nel database SQL di AzureCreate a table in Azure SQL database
  • Usare Sqoop per esportare i dati nel database SQL di AzureUse Sqoop to export data to Azure SQL database

L'illustrazione seguente mostra un flusso tipico dell'applicazione ETL.The following illustration shows a typical ETL application flow.

Operazione ETL con Apache Hive in Azure HDInsightETL operation using Apache Hive on Azure HDInsight

Se non si ha una sottoscrizione di Azure, creare un account gratuito prima di iniziare.If you don't have an Azure subscription, create a free account before you begin.

prerequisitiPrerequisites

Scaricare i dati relativi ai voliDownload the flight data

  1. Passare alla pagina Research and Innovative Technology Administration, Bureau of Transportation Statistics (RITA).Browse to Research and Innovative Technology Administration, Bureau of Transportation Statistics.

  2. Selezionare i valori seguenti nella pagina:On the page, select the following values:

    NOMEName ValoreValue
    Filter YearFilter Year 20132013
    Filter PeriodFilter Period JanuaryJanuary
    FieldsFields Year, FlightDate, UniqueCarrier, Carrier, FlightNum, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.Year, FlightDate, UniqueCarrier, Carrier, FlightNum, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.

    Deselezionare tutti gli altri campi.Clear all other fields.

  3. Selezionare Download.Select Download. Si otterrà un file ZIP con i campi di dati selezionati.You get a .zip file with the data fields you selected.

Caricare i dati in un cluster HDInsightUpload data to an HDInsight cluster

Esistono diversi modi per caricare i dati nell'archivio associato a un cluster HDInsight.There are many ways to upload data to the storage associated with an HDInsight cluster. In questa sezione si usa scp per caricare i dati.In this section, you use scp to upload data. Per informazioni sugli altri modi per caricare i dati, vedere Caricare dati di HDInsight.To learn about other ways to upload data, see Upload data to HDInsight.

  1. Aprire un prompt dei comandi e usare il comando seguente per caricare il file ZIP nel nodo head del cluster HDInsight:Open a command prompt and use the following command to upload the .zip file to the HDInsight cluster head node:

    scp <FILENAME>.zip <SSH-USERNAME>@<CLUSTERNAME>-ssh.azurehdinsight.net:<FILENAME.zip>
    

    Sostituire FILENAME con il nome del file con estensione zip.Replace FILENAME with the name of the .zip file. Sostituire USERNAME con l'account di accesso SSH per il cluster HDInsight.Replace USERNAME with the SSH login for the HDInsight cluster. Sostituire CLUSTERNAME con il nome del cluster HDInsight.Replace CLUSTERNAME with the name of the HDInsight cluster.

    Nota

    Se è stata usata una password per l'autenticazione a SSH, viene richiesto di specificarla.If you use a password to authenticate your SSH login, you're prompted for the password. Se è stata usata una chiave pubblica, può essere necessario usare il parametro -i e specificare il percorso alla chiave privata corrispondente.If you use a public key, you might need to use the -i parameter and specify the path to the matching private key. Ad esempio, scp -i ~/.ssh/id_rsa FILENAME.zip USERNAME@CLUSTERNAME-ssh.azurehdinsight.net:.For example, scp -i ~/.ssh/id_rsa FILENAME.zip USERNAME@CLUSTERNAME-ssh.azurehdinsight.net:.

  2. Al termine del caricamento connettersi al cluster tramite SSH.After the upload has finished, connect to the cluster by using SSH. Nel prompt dei comandi immettere il comando seguente:On the command prompt, enter the following command:

    ssh sshuser@clustername-ssh.azurehdinsight.net
    
  3. Usare il comando seguente per decomprimere il file con estensione zip:Use the following command to unzip the .zip file:

    unzip FILENAME.zip
    

    Questo comando estrae un file con estensione CSV di circa 60 MB.This command extracts a .csv file that is roughly 60 MB.

  4. Usare i comandi seguenti per creare una directory nell'archivio di HDInsight e quindi copiare il file CSV nella directory:Use the following commands to create a directory on HDInsight storage, and then copy the .csv file to the directory:

    hdfs dfs -mkdir -p /tutorials/flightdelays/data
    hdfs dfs -put <FILENAME>.csv /tutorials/flightdelays/data/
    

Trasformare i dati usando una query HiveTransform data using a Hive query

Esistono diversi modi per eseguire un processo Hive in un cluster HDInsight.There are many ways to run a Hive job on an HDInsight cluster. In questa sezione si usa Beeline per eseguire un processo Hive.In this section, you use Beeline to run a Hive job. Per informazioni su altri metodi di esecuzione di un processo Hive, vedere Usare Hive in HDInsight.For information on other methods of running a Hive job, see Use Hive on HDInsight.

Come parte del processo Hive, importare i dati dal file CSV in una tabella Hive denominata Delays.As part of the Hive job, you import the data from the .csv file into a Hive table named 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:From the SSH prompt that you already have for the HDInsight cluster, use the following command to create and edit a new file named flightdelays.hql:

    nano flightdelays.hql
    
  2. Usare il testo seguente come contenuto del file:Use the following text as the contents of this file:

    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 '/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 AS
    SELECT YEAR AS year,
        FL_DATE AS flight_date,
        substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS unique_carrier,
        substring(CARRIER, 2, length(CARRIER) -1) AS carrier,
        substring(FL_NUM, 2, length(FL_NUM) -1) AS flight_num,
        ORIGIN_AIRPORT_ID AS origin_airport_id,
        substring(ORIGIN, 2, length(ORIGIN) -1) AS origin_airport_code,
        substring(ORIGIN_CITY_NAME, 2) AS origin_city_name,
        substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS origin_state_abr,
        DEST_AIRPORT_ID AS dest_airport_id,
        substring(DEST, 2, length(DEST) -1) AS dest_airport_code,
        substring(DEST_CITY_NAME,2) AS dest_city_name,
        substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS dest_state_abr,
        DEP_DELAY_NEW AS dep_delay_new,
        ARR_DELAY_NEW AS arr_delay_new,
        CARRIER_DELAY AS carrier_delay,
        WEATHER_DELAY AS weather_delay,
        NAS_DELAY AS nas_delay,
        SECURITY_DELAY AS security_delay,
        LATE_AIRCRAFT_DELAY AS late_aircraft_delay
    FROM delays_raw;
    
  3. Per salvare il file, premere Esc e quindi immettere :x.To save the file, press Esc and then enter :x.

  4. Per avviare Hive ed eseguire il file flightdelays.hql, usare il comando seguente:To start Hive and run the flightdelays.hql file, use the following command:

    beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
    
  5. Al termine dell'esecuzione dello script flightdelays.hql usare il comando seguente per aprire una sessione Beeline interattiva:After the flightdelays.hql script finishes running, use the following command to open an interactive Beeline session:

    beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
    
  6. Quando si riceve il prompt di jdbc:hive2://localhost:10001/>, usare la query seguente per recuperare i dati dai dati sui ritardi dei voli importati:When you receive the jdbc:hive2://localhost:10001/> prompt, use the following query to retrieve data from the imported flight delay data:

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

    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 /tutorials/flightdelays/output.This query retrieves a list of cities that experienced weather delays, along with the average delay time, and saves it to /tutorials/flightdelays/output. Sqoop leggerà in seguito i dati da questo percorso e li esporterà nel database SQL di Azure.Later, Sqoop reads the data from this location and exports it to Azure SQL Database.

  7. Per uscire da Beeline, immettere !quit al prompt dei comandi.To exit Beeline, enter !quit at the prompt.

Creare una tabella del database SQLCreate a SQL database table

Questa sezione presuppone che sia già stato creato un database SQL di Azure.This section assumes that you have already created an Azure SQL database. Se non si dispone già di un database SQL, vedere le informazioni in Creare un database SQL di Azure nel portale di Azure per crearne uno.If you don't already have a SQL database, use the information in Create an Azure SQL database in the Azure portal to create one.

Se si dispone già di un database SQL, è necessario ottenere il nome del server.If you already have a SQL database, you must get the server name. È possibile trovare il nome del server nel portale di Azure selezionando Database SQL e quindi filtrando il nome del database che si intende usare.To find the server, name in the Azure portal, select SQL Databases, and then filter on the name of the database that you choose to use. Il nome del server è elencato nella colonna Nome server.The server name is listed in the Server name column.

Ottenere i dettagli del server SQL di AzureGet Azure SQL server details

Nota

Sono disponibili diversi modi per connettersi al database SQL per creare una tabella.There are many ways to connect to SQL Database and create a table. Nei seguenti passaggi viene usato FreeTDS dal cluster HDInsight.The following steps use FreeTDS from the HDInsight cluster.

  1. Per installare FreeTDS, usare il comando seguente da una connessione SSH al cluster:To install FreeTDS, use the following command from an SSH connection to the cluster:

    sudo apt-get --assume-yes install freetds-dev freetds-bin
    
  2. Al termine dell'installazione usare il comando seguente per connettersi al server del database SQL.After the installation finishes, use the following command to connect to the SQL Database server. Sostituire serverName con il nome server del database SQL.Replace serverName with the SQL Database server name. Sostituire adminLogin e adminPassword con le credenziali di accesso per il database SQL.Replace adminLogin and adminPassword with the login for SQL Database. Sostituire databaseName con il nome del database.Replace databaseName with the database name.

    TDSVER=8.0 tsql -H <serverName>.database.windows.net -U <adminLogin> -p 1433 -D <databaseName>
    

    Quando richiesto, immettere la password per l'account di accesso amministratore al database SQL.When prompted, enter the password for the SQL Database admin login.

    L'output che si riceve è simile al testo seguente:You receive output similar to the following text:

    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    Default database being set to sqooptest
    1>
    
  3. Al prompt di 1> , immettere il codice seguente:At the 1> prompt, enter the following lines:

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

    Dopo aver immesso l'istruzione GO, vengono valutate le istruzioni precedenti.When the GO statement is entered, the previous statements are evaluated. Questa query crea una tabella denominata delays, con un indice cluster.This query creates a table named delays, with a clustered index.

    Per verificare la corretta creazione della tabella, usare la query seguente:Use the following query to verify that the table has been created:

    SELECT * FROM information_schema.tables
    GO
    

    L'output è simile al testo seguente:The output is similar to the following text:

    TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
    databaseName       dbo             delays        BASE TABLE
    
  4. Per uscire dall'utilità tsql, immettere exit al prompt di 1>.Enter exit at the 1> prompt to exit the tsql utility.

Esportare i dati nel database SQL tramite SqoopExport data to SQL database using Sqoop

Nelle sezioni precedenti sono stati copiati e trasformati i dati nel percorso /tutorials/flightdelays/output.In the previous sections, you copied the transformed data at /tutorials/flightdelays/output. In questa sezione si usa Sqoop per esportare i dati da '/tutorials/flightdelays/output' alla tabella creata nel database SQL di Azure.In this section, you use Sqoop to export the data from '/tutorials/flightdelays/output` to the table you created in Azure SQL database.

  1. Usare il comando seguente per verificare che Sqoop possa visualizzare il database SQL:Use the following command to verify that Sqoop can see your SQL database:

    sqoop list-databases --connect jdbc:sqlserver://<serverName>.database.windows.net:1433 --username <adminLogin> --password <adminPassword>
    

    Questo comando restituisce un elenco di database, compreso il database in cui è stata creata in precedenza la tabella delays.This command returns a list of databases, including the database in which you created the delays table earlier.

  2. Usare il comando seguente per esportare i dati dalla tabella hivesampletable alla tabella delays:Use the following command to export data from hivesampletable to the delays table:

    sqoop export --connect 'jdbc:sqlserver://<serverName>.database.windows.net:1433;database=<databaseName>' --username <adminLogin> --password <adminPassword> --table 'delays' --export-dir '/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.Sqoop connects to the database that contains the delays table, and exports data from the /tutorials/flightdelays/output directory to the delays table.

  3. Al termine dell'esecuzione del comando sqoop, usare l' utilità Transact-SQL per connettersi al database:After the sqoop command finishes, use the tsql utility to connect to the database:

    TDSVER=8.0 tsql -H <serverName>.database.windows.net -U <adminLogin> -P <adminPassword> -p 1433 -D <databaseName>
    

    Usare le istruzioni seguenti per verificare che i dati siano stati esportati nella tabella delays:Use the following statements to verify that the data was exported to the delays table:

    SELECT * FROM delays
    GO
    

    Dovrebbe essere visualizzato un elenco di dati della tabella.You should see a listing of data in the table. La tabella include il nome della città e il tempo di ritardo medio dei voli per la città.The table includes the city name and the average flight delay time for that city.

    Digitare exit per uscire dall'utilità tsql.Type exit to exit the tsql utility.

Passaggi successiviNext steps

In questa esercitazione si è appreso come eseguire le operazioni di estrazione, trasformazione e caricamento di dati con un cluster Apache Hadoop in HDInsight.In this tutorial, you learned how to perform extract, transform, and load data operations using an Apache Hadoop cluster in HDInsight. Passare alla prossima esercitazione per informazioni su come creare cluster Hadoop di HDInsight on demand con Azure Data Factory.Advance to the next tutorial to learn how to create HDInsight Hadoop clusters on-demand using Azure Data Factory.

Per altre informazioni su come usare i dati in HDInsight, vedere gli articoli seguenti:To learn more ways to work with data in HDInsight, see the following articles: