Samouczek: wyodrębnianie, przekształcanie i ładowanie danych przy użyciu usługi Azure HDInsight

W ramach tego samouczka wykonasz operację ETL: wyodrębnianie, przekształcanie i ładowanie danych. Zaimportujesz plik danych CSV do klastra usługi Azure HDInsight, przekształcisz go za pomocą programu Apache Hive i załadujesz go do bazy danych Azure SQL za pomocą narzędzia Apache Sqoop.

Ten samouczek zawiera informacje na temat wykonywania następujących czynności:

• Wyodrębnianie danych i przekazywanie ich do klastra usługi HDInsight.
• Przekształcanie danych przy użyciu technologii Apache Hive.
• Załaduj dane do bazy danych Azure SQL przy użyciu narzędzia Sqoop.

Jeśli nie masz subskrypcji platformy Azure, przed rozpoczęciem utwórz bezpłatne konto.

Wymagania wstępne

• Konto magazynu, które ma hierarchiczną przestrzeń nazw (Azure Data Lake Storage Gen2) skonfigurowaną dla usługi HDInsight

  Zobacz Korzystanie z usługi Azure Data Lake Storage Gen2 w połączeniu z klastrami usługi Azure HDInsight.

• Klaster Hadoop oparty na systemie Linux w usłudze HDInsight

  Zobacz Szybki start: rozpoczynanie pracy z usługami Apache Hadoop i Apache Hive w usłudze Azure HDInsight przy użyciu Azure Portal.

• Azure SQL Database

  Jako docelowy magazyn danych jest używany Azure SQL Database. Jeśli nie masz bazy danych w SQL Database, zobacz Tworzenie bazy danych w usłudze Azure SQL Database w Azure Portal.

• Interfejs wiersza polecenia platformy Azure

  Jeśli nie zainstalowano interfejsu wiersza polecenia platformy Azure, zobacz Instalowanie interfejsu wiersza polecenia platformy Azure.

• Klient protokołu Secure Shell (SSH)

  Aby uzyskać więcej informacji, zobacz Łączenie się z usługą HDInsight (Hadoop) przy użyciu protokołu SSH.

Pobieranie, wyodrębnianie, a następnie przekazywanie danych

W tej sekcji pobierzesz przykładowe dane dotyczące lotów. Następnie przekażesz te dane do klastra usługi HDInsight, a następnie skopiujesz te dane do konta Data Lake Storage Gen2.

1. Pobierz plik On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip . Ten plik zawiera dane lotu.

2. 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:

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

   • <ssh-user-name> Zastąp symbol zastępczy nazwą użytkownika SSH klastra usługi HDInsight.
   • Zamień symbol zastępczy <cluster-name> na nazwę klastra usługi HDInsight.

   Jeśli używasz hasła do uwierzytelniania nazwy użytkownika SSH, zostanie wyświetlony monit o podanie hasła.

   Jeśli używasz klucza publicznego, może być konieczne użycie parametru -i w celu określenia ścieżki do zgodnego klucza prywatnego. Na przykład scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. Po zakończeniu przekazywania połącz się z klastrem przy użyciu protokołu SSH. W wierszu polecenia wprowadź następujące polecenie:

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

4. Użyj następującego polecenia, aby rozpakować plik zip:

   unzip <file-name>.zip
   

   To polecenie umożliwia wyodrębnienie pliku csv.

5. Użyj następującego polecenia, aby utworzyć kontener Data Lake Storage Gen2.

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

   <container-name> Zastąp symbol zastępczy nazwą, którą chcesz nadać kontenerowi.

   Zastąp symbol zastępczy <storage-account-name> nazwą konta magazynu.

6. Utwórz katalog za pomocą następującego polecenia.

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

7. Za pomocą następującego polecenia skopiuj plik csv do katalogu:

   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/
   

   Jeśli nazwa pliku zawiera spacje lub znaki specjalne, należy umieścić ją w cudzysłowie.

Przekształcanie danych

W tej sekcji uruchomisz zadanie Apache Hive za pomocą usługi Beeline.

W ramach zadania Apache Hive zaimportujesz dane z pliku csv do tabeli Apache Hive o nazwie delays.

1. W wierszu polecenia SSH, który już istnieje dla klastra usługi HDInsight, użyj następującego polecenia, aby utworzyć i edytować nowy plik o nazwie flightdelays.hql:

   nano flightdelays.hql
   

2. Zmodyfikuj następujący tekst, zastępując <container-name> symbole zastępcze i <storage-account-name> nazwą kontenera i konta magazynu. Następnie skopiuj i wklej tekst do konsoli nano, naciskając klawisz SHIFT wraz z przyciskiem wyboru prawym przyciskiem myszy.

     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. Zapisz plik, wpisując klawisze CTRL+X, a następnie wpisując po wyświetleniu monitu Y .

4. Aby uruchomić program Hive i uruchomić flightdelays.hql plik, użyj następującego polecenia:

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

5. Po zakończeniu działania skryptu flightdelays.hql użyj następującego polecenia, aby otworzyć interaktywną sesję platformy Beeline:

   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:

   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;
   

   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. Później narzędzie Sqoop odczyta dane z tej lokalizacji i wyeksportuje je do bazy danych Azure SQL Database.

7. Aby zakończyć działanie usługi Beeline, wprowadź polecenie !quit w wierszu polecenia.

Tworzenie tabeli bazy danych SQL

Do tej operacji potrzebna jest nazwa serwera z SQL Database. Wykonaj następujące kroki, aby znaleźć nazwę serwera.

1. Przejdź do witryny Azure Portal.

2. Wybierz pozycję Bazy danych SQL.

3. Filtruj listę według nazwy bazy danych, która ma zostać użyta. Nazwa serwera jest wyświetlana w kolumnie Nazwa serwera.

4. Filtruj listę według nazwy bazy danych, której chcesz użyć. Nazwa serwera jest wyświetlana w kolumnie Nazwa serwera.

   

   Istnieje wiele sposobów nawiązywania połączenia z bazą danych SQL i tworzenia tabeli. W poniższej procedurze użyto rozwiązania FreeTDS z klastra usługi HDInsight.

5. Aby zainstalować rozwiązanie FreeTDS, użyj następującego polecenia dla połączenia SSH z klastrem:

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

6. Po zakończeniu instalacji użyj następującego polecenia, aby nawiązać połączenie z SQL Database.

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

   • Zastąp <server-name> symbol zastępczy nazwą logicznego serwera SQL.

   • Zastąp <admin-login> symbol zastępczy nazwą użytkownika administratora dla SQL Database.

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

   Po wyświetleniu monitu wprowadź hasło dla nazwy użytkownika administratora SQL Database.

   Uzyskasz dane wyjściowe podobne do następującego tekstu:

   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:

   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.

   Zapytanie tworzy tabelę o nazwie delays z indeksem klastrowanym.

9. Za pomocą następującego zapytania sprawdź, czy utworzono tabelę:

   SELECT * FROM information_schema.tables
   GO
   

   Dane wyjściowe będą podobne do następującego tekstu:

   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.

Eksportowanie i ładowanie danych

W poprzednich sekcjach skopiowano przekształcone dane w lokalizacji 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 abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output do tabeli utworzonej w bazie danych Azure SQL.

1. Aby sprawdzić, czy baza danych SQL jest widoczna w narzędziu Sqoop, użyj następującego polecenia:

   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.

2. Za pomocą następującego polecenia wyeksportuj dane z tabeli hivesampletable do tabeli 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
   

   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.

3. Po zakończeniu działania polecenia sqoop użyj narzędzia tsql, aby nawiązać połączenie z bazą danych:

   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:

   SELECT * FROM delays
   GO
   

   Powinna zostać wyświetlona lista danych w tabeli. Tabela zawiera nazwę miejscowości i średni czas opóźnienia lotów dla tej miejscowości.

5. Wpisz polecenie exit, aby wyjść z narzędzia tsql.

Czyszczenie zasobów

Wszystkie zasoby używane w tym samouczku istniały już wcześniej. Oczyszczanie nie jest konieczne.

Następne kroki

Aby poznać więcej sposobów pracy z danymi w usłudze HDInsight, zobacz następujący artykuł:

Korzystanie z usługi Azure Data Lake Storage Gen2 w połączeniu z klastrami usługi Azure HDInsight