Kurz: Extrakce, transformace a načítání dat pomocí Apache Hive v Azure HDInsightTutorial: Extract, transform, and load data using Apache Hive in Azure HDInsight

V tomto kurzu trvat nezpracovaný datový soubor CSV veřejně dostupné zapisovači letových údajů, importujte ho do úložiště clusteru HDInsight a pak transformuje data pomocí Apache Hive v Azure HDInsight.In this tutorial, you take a raw CSV data file of publicly available flight data, import it into an HDInsight cluster storage, and then transform the data using Apache Hive in Azure HDInsight. Po transformaci dat, načtete data do databáze Azure SQL pomocí Apache Sqoop.Once the data is transformed, you load that data into an Azure SQL database using Apache Sqoop.

Tento kurz se zabývá následujícími úkony:This tutorial covers the following tasks:

  • Stažení ukázkových údajů o letechDownload the sample flight data
  • Nahrání dat do clusteru HDInsightUpload data to an HDInsight cluster
  • Transformace dat pomocí HivuTransform the data using Hive
  • Vytvoření tabulky ve službě Azure SQL databaseCreate a table in an Azure SQL database
  • Použít Sqoop k exportování dat do Azure SQL databaseUse Sqoop to export data to an Azure SQL database

Následující obrázek ukazuje obvyklý běh aplikace ETL.The following illustration shows a typical ETL application flow.

Operace ETL s využitím Apache Hivu ve službě Azure HDInsightETL operation using Apache Hive on Azure HDInsight

Pokud ještě nemáte předplatné Azure, vytvořte si bezplatný účet před tím, než začnete.If you don’t have an Azure subscription, create a free account before you begin.

PožadavkyPrerequisites

Stažení letových údajůDownload the flight data

  1. Přejděte do výzkumu a inovativní technologie správy, Bureau of Transportation statistiky.Browse to Research and Innovative Technology Administration, Bureau of Transportation Statistics.

  2. Na stránce zrušte zaškrtnutí všech polí a pak vyberte následující hodnoty:On the page, clear all fields, and then select the following values:

    NázevName HodnotaValue
    Filter Year (Filtr roku)Filter Year 20192019
    Filter Period (Filtr období)Filter Period January (Leden)January
    Fields (Pole)Fields Rok, FlightDate, Reporting_Airline, DOT_ID_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, původu, OriginCityName, OriginState, DestAirportID, cíl, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.Year, FlightDate, Reporting_Airline, DOT_ID_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.
  3. Vyberte Download (Stáhnout).Select Download. Získáte soubor .zip s vybranými datovými poli.You get a .zip file with the data fields you selected.

Nahrání dat do clusteru HDInsightUpload data to an HDInsight cluster

Do úložiště přidruženého ke clusteru HDInsight můžete data nahrát mnoha způsoby.There are many ways to upload data to the storage associated with an HDInsight cluster. V této části k nahrání dat použijete scp.In this section, you use scp to upload data. Informace o dalších způsobech nahrání dat najdete v tématu Nahrání dat do služby HDInsight.To learn about other ways to upload data, see Upload data to HDInsight.

  1. Nahrajte soubor ZIP do hlavního uzlu clusteru HDInsight.Upload the .zip file to the HDInsight cluster head node. Upravte následující příkaz tak, že nahradíte FILENAME s názvem souboru ZIP a CLUSTERNAME s názvem clusteru HDInsight.Edit the command below by replacing FILENAME with the name of the .zip file, and CLUSTERNAME with the name of the HDInsight cluster. Pak otevřete příkazový řádek, nastavte pracovní adresář na umístění souboru a potom zadejte příkaz.Then open a command prompt, set your working directory to the file location, and then enter the command.

    scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
    
  2. Po dokončení nahrávání se ke clusteru připojte pomocí SSH.After the upload has finished, connect to the cluster by using SSH. Upravte následující příkaz tak, že nahradíte CLUSTERNAME s názvem clusteru HDInsight.Edit the command below by replacing CLUSTERNAME with the name of the HDInsight cluster. Potom zadejte následující příkaz:Then enter the following command:

    ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
    
  3. Nastavte proměnnou prostředí po vytvoření připojení SSH.Set up environment variable once an SSH connection has been established. Nahraďte FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USER, a SQL_PASWORD příslušnými hodnotami.Replace FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USER, and SQL_PASWORD with the appropriate values. Potom zadejte příkaz:Then enter the command:

    export FILENAME=FILE_NAME
    export SQLSERVERNAME=SQL_SERVERNAME
    export DATABASE=SQL_DATABASE
    export SQLUSER=SQL_USER
    export SQLPASWORD='SQL_PASWORD'
    
  4. Rozbalte soubor .zip tak, že zadáte následující příkaz:Unzip the .zip file by entering the command below:

    unzip $FILENAME.zip
    
  5. Vytvoření adresáře ve službě HDInsight storage a potom tento soubor .csv zkopírujte do adresáře tak, že zadáte následující příkaz:Create a directory on HDInsight storage, and then copy the .csv file to the directory by entering the command below:

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

Transformace dat pomocí dotazu HiveTransform data using a Hive query

Úlohu Hive můžete v clusteru HDInsight spustit různými způsoby.There are many ways to run a Hive job on an HDInsight cluster. V této části použijete Beeline ke spuštění úlohy Hive.In this section, you use Beeline to run a Hive job. Informace o dalších metodách spuštění úlohy Hive najdete v tématu použití Apache Hive s HDInsight.For information on other methods of running a Hive job, see Use Apache Hive on HDInsight.

V rámci úlohy Hive provedete import dat ze souboru .csv do tabulky Hive s názvem Delays (Zpoždění).As part of the Hive job, you import the data from the .csv file into a Hive table named Delays.

  1. Z řádku SSH, které už máte pro HDInsight cluster, pomocí následujícího příkazu vytvořte a upravte nový soubor s názvem 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. Jako obsah souboru použijte následující text: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. Chcete-li soubor uložit, stiskněte Ctrl + X, pak y, stiskněte enter.To save the file, press Ctrl + X, then y, then enter.

  4. Spusťte Hive a soubor flightdelays.hql pomocí následujícího příkazu: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. Po dokončení skriptu flightdelays.hql pomocí následujícího příkazu otevřete interaktivní relaci Beeline: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. Po zobrazení příkazového řádku jdbc:hive2://localhost:10001/> pomocí následujícího dotazu načtěte data z importovaných dat o zpožděných letech: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;
    

    Tento dotaz načte seznam měst, ve kterých došlo ke zpožděním kvůli nepřízni počasí, společně s průměrnou délkou zpoždění a uloží ho do umístění /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. Později z tohoto umístění data načte Sqoop a exportuje je do služby Azure SQL Database.Later, Sqoop reads the data from this location and exports it to Azure SQL Database.

  7. Beeline ukončíte zadáním !quit na příkazovém řádku.To exit Beeline, enter !quit at the prompt.

Vytvoření tabulky databáze SQLCreate a SQL database table

Existuje mnoho způsobů, jak se připojit ke službě SQL Database a vytvořit tabulku.There are many ways to connect to SQL Database and create a table. V následujících krocích se používá FreeTDS z clusteru HDInsight.The following steps use FreeTDS from the HDInsight cluster.

  1. K instalaci FreeTDS, použijte následující příkaz z otevřené připojení SSH ke clusteru:To install FreeTDS, use the following command from the open SSH connection to the cluster:

    sudo apt-get --assume-yes install freetds-dev freetds-bin
    
  2. Po dokončení instalace se pomocí následujícího příkazu připojte k serveru služby SQL Database.After the installation finishes, use the following command to connect to the SQL Database server.

    TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
    

    Zobrazí se výstup podobný následujícímu textu: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 <yourdatabase>
    1>
    
  3. Na příkazovém řádku 1> zadejte následující řádky: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
    

    Po zadání příkazu GO se vyhodnotí předchozí příkazy.When the GO statement is entered, the previous statements are evaluated. Tento příkaz vytvoří tabulku s názvem zpoždění, pomocí clusterovaného indexu.This statement creates a table named delays, with a clustered index.

    K ověření vytvoření tabulky použijte následující dotaz:Use the following query to verify that the table has been created:

    SELECT * FROM information_schema.tables
    GO
    

    Výstup se bude podobat následujícímu:The output is similar to the following text:

    TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
    databaseName       dbo             delays        BASE TABLE
    
  4. Zadáním exit na příkazovém řádku 1> ukončete nástroj tsql.Enter exit at the 1> prompt to exit the tsql utility.

Export dat do SQL database s použitím Apache SqoopExport data to SQL database using Apache Sqoop

V předchozích částech jste zkopírovali transformovaná data do umístění /tutorials/flightdelays/output.In the previous sections, you copied the transformed data at /tutorials/flightdelays/output. V této části použijete Sqoop k exportu dat z /tutorials/flightdelays/output do tabulky, kterou jste vytvořili v databázi Azure SQL.In this section, you use Sqoop to export the data from /tutorials/flightdelays/output to the table you created in Azure SQL database.

  1. Ověřte, že Sqoop vidí vaše databáze SQL tak, že zadáte následující příkaz:Verify that Sqoop can see your SQL database by entering the command below:

    sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
    

    Tento příkaz vrátí seznam databází, včetně databáze, ve které jste vytvořili delays tabulky dříve.This command returns a list of databases, including the database in which you created the delays table earlier.

  2. Export dat z /tutorials/flightdelays/output k delays tabulku tak, že zadáte následující příkaz:Export data from /tutorials/flightdelays/output to the delays table by entering the command below:

    sqoop export --connect "jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433;database=$DATABASE" --username $SQLUSER --password $SQLPASWORD --table 'delays' --export-dir '/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
    

    Sqoop se připojí k databázi, která obsahuje delays tabulky a exportuje data z /tutorials/flightdelays/output do adresáře delays tabulky.Sqoop connects to the database that contains the delays table, and exports data from the /tutorials/flightdelays/output directory to the delays table.

  3. Po dokončení příkazu sqoop pomocí tsql nástroje pro připojení k databázi zadáním následujícího příkazu:After the sqoop command finishes, use the tsql utility to connect to the database by entering the command below:

    TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
    

    Pomocí následujících příkazů ověřte, že se data exportovala do tabulky delays:Use the following statements to verify that the data was exported to the delays table:

    SELECT * FROM delays
    GO
    

    Měl by se zobrazit výpis dat v tabulce.You should see a listing of data in the table. Tabulka obsahuje název města a průměrnou délku zpoždění letu pro příslušné město.The table includes the city name and the average flight delay time for that city.

    Zadáním exit ukončete nástroj tsql.Type exit to exit the tsql utility.

Vyčištění prostředkůClean up resources

Po dokončení kurzu můžete cluster odstranit.After you complete the tutorial, you may want to delete the cluster. Pomocí HDInsight jsou vaše data uložena v Azure Storage, takže můžete clusteru bezpečně odstranit, pokud není používán.With HDInsight, your data is stored in Azure Storage, so you can safely delete a cluster when it is not in use. Za cluster služby HDInsight se účtují poplatky, i když se nepoužívá.You are also charged for an HDInsight cluster, even when it is not in use. Vzhledem k tomu, že poplatky za cluster představují několikanásobek poplatků za úložiště, dává ekonomický smysl odstraňovat clustery, které nejsou používány.Since the charges for the cluster are many times more than the charges for storage, it makes economic sense to delete clusters when they are not in use.

Odstranění clusteru, naleznete v tématu odstranění clusteru HDInsight pomocí prohlížeče, Powershellu nebo rozhraní příkazového řádku Azure.To delete a cluster, see Delete an HDInsight cluster using your browser, PowerShell, or the Azure CLI.

Další postupNext steps

V tomto kurzu jste provedli nezpracovaná data souboru CSV, importovat do úložiště clusteru HDInsight a pak transformuje data pomocí Apache Hive v Azure HDInsight.In this tutorial, you took a raw CSV data file, imported it into an HDInsight cluster storage, and then transformed the data using Apache Hive in Azure HDInsight. V dalším kurzu se dozvíte, jak pomocí služby Azure Data Factory vytvářet clustery HDInsight Hadoop na vyžádání.Advance to the next tutorial to learn how to create HDInsight Hadoop clusters on-demand using Azure Data Factory.

Informace o dalších způsobech práce s daty ve službě HDInsight najdete v následujících článcích:To learn more ways to work with data in HDInsight, see the following articles: