Analizzare i dati sui ritardi dei voli con Hive in HDInsight basato su LinuxAnalyze flight delay data by using Hive on Linux-based HDInsight

Informazioni su come analizzare i dati sui ritardi dei voli usando Hive in HDInsight basato su Linux e su come esportare i dati nel database SQL di Azure usando Sqoop.Learn how to analyze flight delay data by using Hive on Linux-based HDInsight, and how to export the data to Azure SQL Database by using Sqoop.

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.

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.

Caricare i datiUpload the data

  1. Usare il comando seguente per caricare il file con estensione zip nel nodo head del cluster HDInsight:Use the following command to upload the .zip file to the HDInsight cluster head node:

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

    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:

    ssh USERNAME@CLUSTERNAME-ssh.azurehdinsight.net

    Per altre informazioni, vedere Connettersi a HDInsight (Hadoop) con SSH.For more information, see Connect to HDInsight (Hadoop) using SSH.

  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 il comando seguente per creare una directory nell'archivio di HDInsight e quindi copiare il file nella directory:Use the following command to create a directory on HDInsight storage, and then copy the file to the directory:

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

Creare ed eseguire HiveQLCreate and run the HiveQL

Usare la procedura seguente per importare dati dal file con estensione csv in una tabella Hive denominata delays.Use the following steps to import data from the .csv file into a Hive table named Delays.

  1. Usare il comando seguente per creare e modificare un nuovo file denominato flightdelays.hql:Use the following command to create and edit a new file named flightdelays.hql:

    nano flightdelays.hql
    

    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;
    
  2. Per salvare il file, usare CTRL + X e quindi premere Y.To save the file, use Ctrl+X, then Y.

  3. 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
    
  4. 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'
    
  5. 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.

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

Creazione di un database SQLCreate a SQL database

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 desidera 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 SERVER .The server name is listed in the SERVER column.

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. Salvare il nome del server usato per il database.Save the server name that's used for the database.

Creare una tabella del database SQLCreate a SQL database table

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 connettersi al cluster HDInsight basato su Linux, usare SSH ed eseguire la procedura seguente dalla sessione di SSH.Use SSH to connect to the Linux-based HDInsight cluster, and run the following steps from the SSH session.

  2. Immettere il comando seguente per installare FreeTDS:Use the following command to install FreeTDS:

    sudo apt-get --assume-yes install freetds-dev freetds-bin
    
  3. 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 <adminPassword> -p 1433 -D <databaseName>
    

    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>
    
  4. 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
    
  5. 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 con SqoopExport data with Sqoop

  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. Digitare exit per uscire dall'utilità tsql.Type exit to exit the tsql utility.

Passaggi successiviNext steps

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: