Zelfstudie: Gegevens extraheren, transformeren en laden met behulp van Azure HDInsight

In deze zelfstudie voert u een ETL-bewerking uit: gegevens extraheren, transformeren en laden. U neemt een CSV-bestand met onbewerkte gegevens, importeert dit in een Azure HDInsight-cluster, transformeert het met Apache Hive en laadt de gegevens in Azure SQL Database met Apache Sqoop.

In deze zelfstudie leert u het volgende:

• Gegevens extraheren en uploaden naar een HDInsight-cluster.
• De gegevens transformeren met behulp van Apache Hive.
• De gegevens laden in Azure SQL Database met behulp van Sqoop.

Als u geen Azure-abonnement hebt, maakt u een gratis account voordat u begint.

Vereisten

• Een opslagaccount met een hiërarchische naamruimte (Azure Data Lake Storage) die is geconfigureerd voor HDInsight

  Zie Azure Data Lake Storage gebruiken met Azure HDInsight-clusters.

• Een Hadoop-cluster op basis van Linux in HDInsight

  Zie quickstart: Aan de slag met Apache Hadoop en Apache Hive in Azure HDInsight met behulp van Azure Portal.

• Azure SQL-database

  U gebruikt Azure SQL Database als een doelgegevensarchief. Als u geen database in SQL Database hebt, raadpleegt u het artikel Een database in Azure SQL Database maken in Azure Portal.

• Azure-CLI

  Als u de Azure CLI nog niet hebt geïnstalleerd, raadpleegt u Azure CLI installeren.

• Een SSH-client (Secure Shell)

  Zie voor meer informatie Verbinding maken met HDInsight (Hadoop) via SSH.

De gegevens downloaden, extraheren en uploaden

In deze sectie downloadt u voorbeeld vluchtgegevens. Vervolgens uploadt u die gegevens naar uw HDInsight-cluster en kopieert u die gegevens vervolgens naar uw Data Lake Storage-account.

1. Download het On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip-bestand . Dit bestand bevat de vluchtgegevens.

2. Open een opdrachtprompt en gebruik de volgende Secure Copy-opdracht om het ZIP-bestand te uploaden naar het hoofdknooppunt van het HDInsight-cluster:

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

   • Vervang de <ssh-user-name> tijdelijke aanduiding door de SSH-gebruikersnaam voor het HDInsight-cluster.
   • Vervang de tijdelijke plaatsaanduiding <cluster-name> door de naam van het HDInsight-cluster.

   Als u een wachtwoord gebruikt om uw SSH-gebruikersnaam te verifiëren, wordt u gevraagd om het wachtwoord.

   Als u een openbare sleutel gebruikt, moet u mogelijk de parameter -i gebruiken en het pad naar de bijbehorende persoonlijke sleutel opgeven. Bijvoorbeeld: scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. Nadat het uploaden is voltooid, maakt u via SSH verbinding met het cluster. Voer op de opdrachtprompt de volgende opdracht in:

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

4. Gebruik de volgende opdracht om ZIP-bestand uit te pakken:

   unzip <file-name>.zip
   

   Met deze opdracht wordt een CSV-bestand uitgepakt.

5. Gebruik de volgende opdracht om de Data Lake Storage-container te maken.

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

   Vervang de tijdelijke aanduiding <container-name> door de naam die u aan uw container wilt geven.

   Vervang de tijdelijke plaatsaanduiding <storage-account-name> door de naam van uw opslagaccount.

6. Gebruik de volgende opdracht om een map te maken:

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

7. Gebruik de volgende opdracht om het CSV-bestand naar de map te kopiëren:

   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/
   

   Gebruik aanhalingstekens rond de bestandsnaam als de bestandsnaam spaties of speciale tekens bevat.

De gegevens transformeren

In deze sectie gebruikt u Beeline om een Apache Hive-taak uit te voeren.

Als onderdeel van de Apache Hive-taak, importeert u de gegevens uit het CSV-bestand in een Apache Hive-tabel met de naam delays.

1. Gebruik op de SSH-prompt die al is geopend voor het HDInsight-cluster de volgende opdracht om een nieuw bestand met de naam flightdelays.hql te maken en bewerken:

   nano flightdelays.hql
   

2. Wijzig de volgende tekst door de tijdelijke aanduidingen te <storage-account-name> vervangen door de <container-name> naam van uw container en opslagaccount. Kopieer en plak de tekst vervolgens in de nanoconsole met behulp van de Shift-toets, samen met de rechtermuisselectieknop.

     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. Sla het bestand op door Ctrl+X te typen en vervolgens te typen Y wanneer hierom wordt gevraagd.

4. Gebruik de volgende opdracht om Hive te starten en het flightdelays.hql bestand uit te voeren:

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

5. Nadat het flightdelays.hql script is uitgevoerd, gebruikt u de volgende opdracht om een interactieve Beeline-sessie te openen:

   beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
   

6. Wanneer u de prompt jdbc:hive2://localhost:10001/> ziet, gebruikt u de volgende query om gegevens op te halen uit de geïmporteerde gegevens van vertraagde vluchten:

   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;
   

   Met deze query haalt u een lijst op met plaatsen waar er vertragingen door het weer zijn ontstaan, samen met de gemiddelde vertragingstijd. Deze gegevens worden opgeslagen in abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Later worden de gegevens met Sqoop vanaf deze locatie gelezen en geëxporteerd naar Azure SQL Database.

7. Sluit Beeline af door !quit in te voeren bij de opdrachtprompt.

Een SQL-databasetabel maken

Voor deze bewerking hebt u de servernaam van SQL Database nodig. Voer deze stappen uit om de servernaam te achterhalen.

1. Ga naar de Azure Portal.

2. Selecteer SQL-databases.

3. Filter op de naam van de database die u wilt kiezen. De naam van de server wordt vermeld in de kolom Server.

4. Filter op de naam van de database die u wilt gebruiken. De naam van de server wordt vermeld in de kolom Server.

   

   Er zijn veel manieren om verbinding te maken met SQL Database en een tabel te maken. In de volgende stappen wordt FreeTDS gebruikt vanuit het HDInsight-cluster.

5. Gebruik de volgende opdracht vanuit een SSH-verbinding met het cluster om FreeTDS te installeren:

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

6. Nadat de installatie is voltooid, gebruikt u de volgende opdracht om verbinding te maken met SQL Database.

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

   • Vervang de tijdelijke aanduiding <server-name> door de naam van de logische SQL-server.

   • Vervang de <admin-login> tijdelijke aanduiding door de gebruikersnaam van de beheerder voor SQL Database.

   • Vervang de tijdelijke aanduiding <database-name> door de naam van de database.

   Wanneer u hierom wordt gevraagd, voert u het wachtwoord in voor de gebruikersnaam van de SQL Database-beheerder.

   U ziet uitvoer die vergelijkbaar is met de volgende tekst:

   locale is "en_US.UTF-8"
   locale charset is "UTF-8"
   using default charset "UTF-8"
   Default database being set to sqooptest
   1>
   

7. Voer de volgende instructies in bij de prompt 1>:

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

8. Wanneer u de instructie GO invoert, worden de vorige instructies geëvalueerd.

   Met de query maakt u een tabel met de naam delays, met een geclusterde index.

9. Gebruik de volgende query om te controleren of de tabel is gemaakt:

   SELECT * FROM information_schema.tables
   GO
   

   De uitvoer lijkt op het volgende:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

10. Voer exit als reactie op de prompt 1> om het hulpprogramma tsql af te sluiten.

De gegevens exporteren en laden

In de vorige secties hebt u de getransformeerde gegevens op de locatie abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output gekopieerd. In deze sectie gebruikt u Sqoop om de gegevens in abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output te exporteren naar de tabel die u hebt gemaakt in Azure SQL Database.

1. Gebruik de volgende opdracht om te controleren of Sqoop de SQL-database kan zien:

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

   De opdracht retourneert een lijst met databases, met inbegrip van de database waarin u de tabel delays hebt gemaakt.

2. Gebruik de volgende opdracht om gegevens uit de tabel hivesampletable te exporteren naar de tabel 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
   

   Sqoop maakt verbinding met de database met de tabel delays en exporteert gegevens uit de map /tutorials/flightdelays/output naar de tabel delays.

3. Als de sqoop-opdracht is voltooid, gebruikt u het hulpprogramma tsql om verbinding te maken met de database:

   TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>
   

4. Gebruik de volgende instructies om te controleren of de gegevens zijn geëxporteerd naar de tabel delays:

   SELECT * FROM delays
   GO
   

   U ziet als het goed is een lijst met gegevens in de tabel. De tabel bevat de plaatsnaam en de gemiddelde vertragingstijd voor vluchten van en naar die plaats.

5. Typ exit en druk op Enter om het hulpprogramma tsql af te sluiten.

Resources opschonen

Alle resources die in deze zelfstudie worden gebruikt, zijn bestaande resources. Opschonen is niet nodig.

Volgende stappen

Zie het volgende artikel voor andere manieren om te werken met gegevens in HDInsight:

Azure Data Lake Storage gebruiken met Azure HDInsight-clusters