Samouczek: Wyodrębnianie, przekształcanie i ładowanie danych przy użyciu technologii Apache Hive w usłudze Azure HDInsightTutorial: Extract, transform, and load data by using Apache Hive on Azure HDInsight

W ramach tego samouczka wykonasz operację ETL: wyodrębnianie, przekształcanie i ładowanie danych.In this tutorial, you perform an ETL operation: extract, transform, and load data. Pobierzesz plik danych pierwotnych w formacie CSV, zaimportujesz te dane do klastra usługi Azure HDInsight, przekształcisz je przy użyciu technologii Apache Hive i załadujesz do bazy danych Azure SQL Database za pomocą narzędzia Apache Sqoop.You take a raw CSV data file, import it into an Azure HDInsight cluster, transform it with Apache Hive, and load it into an Azure SQL database with Apache Sqoop.

Ten samouczek zawiera informacje na temat wykonywania następujących czynności:In this tutorial, you learn how to:

  • Wyodrębnianie danych i przekazywanie ich do klastra usługi HDInsight.Extract and upload the data to an HDInsight cluster.
  • Przekształcanie danych przy użyciu technologii Apache Hive.Transform the data by using Apache Hive.
  • Ładowanie danych do bazy danych Azure SQL Database za pomocą narzędzia Sqoop.Load the data to an Azure SQL database by using Sqoop.

Jeśli nie masz subskrypcji platformy Azure, przed rozpoczęciem utwórz bezpłatne konto.If you don't have an Azure subscription, create a free account before you begin.

Wymagania wstępnePrerequisites

Pobieranie danych lotówDownload the flight data

  1. Przejdź do strony Research and Innovative Technology Administration, Bureau of Transportation Statistics.Browse to Research and Innovative Technology Administration, Bureau of Transportation Statistics.

  2. Na stronie wybierz następujące wartości:On the page, select the following values:

    Name (Nazwa)Name WartośćValue
    Rok filtrowaniaFilter Year 20132013
    Filter Period (Okres filtrowania)Filter Period January (Styczeń)January
    PolaFields Year, FlightDate, Reporting_Airline, IATA_CODE_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.Year, FlightDate, Reporting_Airline, IATA_CODE_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.

    Wyczyść wszystkie pozostałe pola.Clear all other fields.

  3. Wybierz przycisk Download (Pobierz).Select Download. Uzyskasz plik zip z wybranymi polami danych.You get a .zip file with the data fields you selected.

Wyodrębnianie i przekazywanie danychExtract and upload the data

W tej sekcji przekażesz dane do klastra usługi HDInsight, a następnie skopiujesz te dane na konto usługi Data Lake Storage Gen2.In this section, you'll upload data to your HDInsight cluster and then copy that data to your Data Lake Storage Gen2 account.

  1. Otwórz wiersz polecenia i za pomocą następującego polecenia Secure Copy (Scp) przekaż plik zip do węzła głównego klastra usługi HDInsight:Open a command prompt and use the following Secure Copy (Scp) command to upload the .zip file to the HDInsight cluster head node:

    scp <file-name>.zip <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net:<file-name.zip>
    
    • Zamień symbol zastępczy <file-name> na nazwę pliku zip.Replace the <file-name> placeholder with the name of the .zip file.
    • Zamień symbol zastępczy <ssh-user-name> na nazwę logowania protokołu SSH dla klastra usługi HDInsight.Replace the <ssh-user-name> placeholder with the SSH login for the HDInsight cluster.
    • Zamień symbol zastępczy <cluster-name> na nazwę klastra usługi HDInsight.Replace the <cluster-name> placeholder with the name of the HDInsight cluster.

    Jeśli uwierzytelniasz nazwę logowania SSH przy użyciu hasła, zostanie wyświetlony monit o podanie hasła.If you use a password to authenticate your SSH login, you're prompted for the password.

    Jeśli używasz klucza publicznego, może być konieczne użycie parametru -i w celu określenia ścieżki do zgodnego klucza prywatnego.If you use a public key, you might need to use the -i parameter and specify the path to the matching private key. Na przykład scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.For example, scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

  2. Po zakończeniu przekazywania połącz się z klastrem przy użyciu protokołu SSH.After the upload has finished, connect to the cluster by using SSH. W wierszu polecenia wprowadź następujące polecenie:On the command prompt, enter the following command:

    ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
    
  3. Użyj następującego polecenia, aby rozpakować plik zip:Use the following command to unzip the .zip file:

    unzip <file-name>.zip
    

    To polecenie umożliwia wyodrębnienie pliku csv.The command extracts a .csv file.

  4. Użyj następującego polecenia, aby utworzyć kontener Data Lake Storage Gen2.Use the following command to create the Data Lake Storage Gen2 container.

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

    Zastąp <container-name> symbol zastępczy nazwą, którą chcesz przypisać do kontenera.Replace the <container-name> placeholder with the name that you want to give your container.

    Zastąp symbol zastępczy <storage-account-name> nazwą konta magazynu.Replace the <storage-account-name> placeholder with the name of your storage account.

  5. Utwórz katalog za pomocą następującego polecenia.Use the following command to create a directory.

    hdfs dfs -mkdir -p abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data
    
  6. Za pomocą następującego polecenia skopiuj plik csv do katalogu:Use the following command to copy the .csv file to the directory:

    hdfs dfs -put "<file-name>.csv" abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data/
    

    Jeśli nazwa pliku zawiera spacje lub znaki specjalne, należy umieścić ją w cudzysłowie.Use quotes around the file name if the file name contains spaces or special characters.

Przekształcanie danychTransform the data

W tej sekcji uruchomisz zadanie Apache Hive za pomocą usługi Beeline.In this section, you use Beeline to run an Apache Hive job.

W ramach zadania Apache Hive zaimportujesz dane z pliku csv do tabeli Apache Hive o nazwie delays.As part of the Apache Hive job, you import the data from the .csv file into an Apache Hive table named delays.

  1. W wierszu polecenia SSH, który jest już dostępny dla klastra usługi HDInsight, wydaj następujące polecenie, aby utworzyć i edytować nowy plik o nazwie 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. Zmodyfikuj następujący tekst, zastępując <container-name> symbole zastępcze i <storage-account-name> nazwą swojego kontenera i konta magazynu.Modify the following text by replace the <container-name> and <storage-account-name> placeholders with your container and storage account name. Następnie skopiuj i wklej tekst do konsoli programu nano, klikając prawym przyciskiem myszy przy naciśniętym klawiszu SHIFT.Then copy and paste the text into the nano console by using pressing the SHIFT key along with the right-mouse click button.

    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 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. Zapisz ten plik przy użyciu kombinacji klawiszy CTRL+X, a następnie wpisz Y po wyświetleniu monitu.Save the file by using use CTRL+X and then type Y when prompted.

  4. Aby uruchomić oprogramowanie Hive i uruchomić plik flightdelays.hql, użyj następującego polecenia: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. Gdy skrypt flightdelays.hql zakończy działanie, użyj następującego polecenia, aby otworzyć interaktywną sesję usługi 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 otrzymaniu wiersza jdbc:hive2://localhost:10001/> użyj następującego zapytania, aby pobrać dane z zaimportowanych danych opóźnień lotów: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;
    

    To zapytanie pobiera listę miejscowości z opóźnieniami ze względu na pogodę i średni czas opóźnienia, a następnie zapisuje ją w lokalizacji abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output.This query retrieves a list of cities that experienced weather delays, along with the average delay time, and saves it to abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Później narzędzie Sqoop odczyta dane z tej lokalizacji i wyeksportuje je do bazy danych Azure SQL Database.Later, Sqoop reads the data from this location and exports it to Azure SQL Database.

  7. Aby zakończyć działanie usługi Beeline, wprowadź polecenie !quit w wierszu polecenia.To exit Beeline, enter !quit at the prompt.

Tworzenie tabeli bazy danych SQLCreate a SQL database table

Aby wykonać tę operację, musisz dysponować nazwą serwera bazy danych SQL Database.You need the server name from your SQL database for this operation. Wykonaj następujące kroki, aby znaleźć nazwę serwera.Complete these steps to find your server name.

  1. Przejdź do witryny Azure Portal.Go to the Azure portal.

  2. Wybierz pozycję Bazy danych SQL.Select SQL Databases.

  3. Filtruj listę według nazwy bazy danych, która ma zostać użyta.Filter on the name of the database that you choose to use. Nazwa serwera jest wyświetlana w kolumnie Nazwa serwera.The server name is listed in the Server name column.

  4. Filtruj listę według nazwy bazy danych, której chcesz użyć.Filter on the name of the database that you want to use. Nazwa serwera jest wyświetlana w kolumnie Nazwa serwera.The server name is listed in the Server name column.

    Uzyskiwanie szczegółów serwera Azure SQLGet Azure SQL server details

    Istnieje wiele sposobów nawiązywania połączenia z bazą danych SQL i tworzenia tabeli.There are many ways to connect to SQL Database and create a table. W poniższej procedurze użyto rozwiązania FreeTDS z klastra usługi HDInsight.The following steps use FreeTDS from the HDInsight cluster.

  5. Aby zainstalować rozwiązanie FreeTDS, użyj następującego polecenia dla połączenia SSH z klastrem:To install FreeTDS, use the following command from an SSH connection to the cluster:

    sudo apt-get --assume-yes install freetds-dev freetds-bin
    
  6. Po zakończeniu instalacji użyj poniższego polecenia, aby połączyć się z serwerem usługi SQL Database.After the installation completes, use the following command to connect to the SQL Database server.

    TDSVER=8.0 tsql -H '<server-name>.database.windows.net' -U '<admin-login>' -p 1433 -D '<database-name>'
    
    • Zamień symbol zastępczy <server-name> na nazwę serwera usługi SQL Database.Replace the <server-name> placeholder with the SQL Database server name.

    • Zamień symbol zastępczy <admin-login> na identyfikator logowania administratora usługi SQL Database.Replace the <admin-login> placeholder with the admin login for SQL Database.

    • Zamień symbol zastępczy <database-name> na nazwę bazy danych.Replace the <database-name> placeholder with the database name

    Gdy zostanie wyświetlony monit, wprowadź hasło dla nazwy logowania administratora bazy danych SQL Database.When you're prompted, enter the password for the SQL Database admin login.

    Uzyskasz dane wyjściowe podobne do następującego tekstu: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>
    
  7. W wierszu 1> wprowadź następujące instrukcje:At the 1> prompt, enter the following statements:

    CREATE TABLE [dbo].[delays](
    [OriginCityName] [nvarchar](50) NOT NULL,
    [WeatherDelay] float,
    CONSTRAINT [PK_delays] PRIMARY KEY CLUSTERED
    ([OriginCityName] ASC))
    GO
    
  8. Jeśli wprowadzono instrukcję GO, zostaną obliczone poprzednie instrukcje.When the GO statement is entered, the previous statements are evaluated.

    Zapytanie tworzy tabelę o nazwie delays z indeksem klastrowanym.The query creates a table named delays, which has a clustered index.

  9. Za pomocą następującego zapytania sprawdź, czy utworzono tabelę:Use the following query to verify that the table is created:

    SELECT * FROM information_schema.tables
    GO
    

    Dane wyjściowe będą podobne do następującego tekstu:The output is similar to the following text:

    TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
    databaseName       dbo             delays        BASE TABLE
    
  10. Wprowadź ciąg exit w wierszu 1>, aby zakończyć działanie narzędzia tsql.Enter exit at the 1> prompt to exit the tsql utility.

Eksportowanie i ładowanie danychExport and load the data

W poprzednich sekcjach skopiowano przekształcone dane w lokalizacji abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output.In the previous sections, you copied the transformed data at the location abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. W tej sekcji użyjesz narzędzia Sqoop, aby wyeksportować dane z lokalizacji abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output do tabeli utworzonej w bazie danych Azure SQL Database.In this section, you use Sqoop to export the data from abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output to the table you created in the Azure SQL database.

  1. Aby sprawdzić, czy baza danych SQL jest widoczna w narzędziu Sqoop, użyj następującego polecenia:Use the following command to verify that Sqoop can see your SQL database:

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

    To polecenie zwraca listę baz danych, łącznie z bazą danych, w której utworzono tabelę delays.The command returns a list of databases, including the database in which you created the delays table.

  2. Za pomocą następującego polecenia wyeksportuj dane z tabeli hivesampletable do tabeli delays:Use the following command to export data from the hivesampletable table to the delays table:

    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
    

    Narzędzie Sqoop nawiązuje połączenie z bazą danych zawierającą tabelę delays i eksportuje dane z katalogu /tutorials/flightdelays/output do tabeli 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. Po zakończeniu działania polecenia sqoop użyj narzędzia tsql, aby nawiązać połączenie z bazą danych:After the sqoop command finishes, use the tsql utility to connect to the database:

    TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>
    
  4. Za pomocą następujących instrukcji sprawdź, czy dane zostały wyeksportowane do tabeli delays:Use the following statements to verify that the data was exported to the delays table:

    SELECT * FROM delays
    GO
    

    Powinna zostać wyświetlona lista danych w tabeli.You should see a listing of data in the table. Tabela zawiera nazwę miejscowości i średni czas opóźnienia lotów dla tej miejscowości.The table includes the city name and the average flight delay time for that city.

  5. Wpisz polecenie exit, aby wyjść z narzędzia tsql.Enter exit to exit the tsql utility.

Oczyszczanie zasobówClean up resources

Wszystkie zasoby używane w tym samouczku istniały już wcześniej.All resources used in this tutorial are preexisting. Oczyszczanie nie jest konieczne.No cleanup is necessary.

Następne krokiNext steps

Aby poznać więcej sposobów pracy z danymi w usłudze HDInsight, zobacz następujący artykuł:To learn more ways to work with data in HDInsight, see the following article: