Tutorial: Extrahieren, Transformieren und Laden von Daten mithilfe von Apache Hive in Azure HDInsightTutorial: Extract, transform, and load data by using Apache Hive on Azure HDInsight

In diesem Tutorial führen Sie einen ETL-Vorgang durch: Daten extrahieren, transformieren und laden.In this tutorial, you perform an ETL operation: extract, transform, and load data. Sie verwenden eine unformatierte CSV-Datendatei, importieren diese Datei in einen Azure HDInsight-Cluster, transformieren sie mit Apache Hive und laden sie mit Apache Sqoop in eine Azure SQL-Datenbank.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.

In diesem Tutorial lernen Sie Folgendes:In this tutorial, you learn how to:

  • Extrahieren und Hochladen der Daten in einen HDInsight-ClusterExtract and upload the data to an HDInsight cluster.
  • Transformieren der Daten mithilfe von Apache HiveTransform the data by using Apache Hive.
  • Laden der Daten in eine Azure SQL-Datenbank mithilfe von SqoopLoad the data to an Azure SQL database by using Sqoop.

Wenn Sie kein Azure-Abonnement besitzen, können Sie ein kostenloses Konto erstellen, bevor Sie beginnen.If you don't have an Azure subscription, create a free account before you begin.

VoraussetzungenPrerequisites

Wichtig

Die Schritte in diesem Artikel erfordern einen HDInsight-Cluster unter Linux.The steps in this article require an HDInsight cluster that uses Linux. Linux ist das einzige Betriebssystem, das in Azure HDInsight Version 3.4 oder höher verwendet wird.Linux is the only operating system that's used on Azure HDInsight version 3.4 or later. Weitere Informationen finden Sie unter Welche Hadoop-Komponenten und -Versionen sind in HDInsight verfügbar?.For more information, see HDInsight retirement on Windows.

Herunterladen der FlugdatenDownload the flight data

  1. Rufen Sie die Website von Research and Innovative Technology Administration, Bureau of Transportation Statistics (RITA) auf.Browse to Research and Innovative Technology Administration, Bureau of Transportation Statistics.

  2. Wählen Sie auf der Website die folgenden Werte aus:On the page, select the following values:

    NAMEName WertValue
    Filter YearFilter Year 20132013
    Filter PeriodFilter Period JanuaryJanuary
    FelderFields 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.

    Entfernen Sie die Häkchen bei allen anderen Feldern.Clear all other fields.

  3. Wählen Sie Herunterladen aus.Select Download. Sie erhalten eine ZIP-Datei mit den ausgewählten Datenfeldern.You get a .zip file with the data fields you selected.

Extrahieren und Hochladen der DatenExtract and upload the data

In diesem Abschnitt laden Sie Daten in Ihren HDInsight-Cluster hoch und kopieren die Daten dann in Ihr Data Lake Storage Gen2-Konto.In this section, you'll upload data to your HDInsight cluster and then copy that data to your Data Lake Storage Gen2 account.

  1. Öffnen Sie eine Eingabeaufforderung, und verwenden Sie den folgenden SCP-Befehl (Secure Copy), um die ZIP-Datei in den Hauptknoten des HDInsight-Clusters hochzuladen: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>
    
    • Ersetzen Sie den Platzhalter <file-name> durch den Namen der ZIP-Datei.Replace the <file-name> placeholder with the name of the .zip file.
    • Ersetzen Sie den Platzhalter <ssh-user-name> durch den SSH-Anmeldenamen für den HDInsight-Cluster.Replace the <ssh-user-name> placeholder with the SSH login for the HDInsight cluster.
    • Ersetzen Sie den Platzhalter <cluster-name> durch den Namen des HDInsight-Clusters.Replace the <cluster-name> placeholder with the name of the HDInsight cluster.

    Wenn Sie für die Authentifizierung Ihrer SSH-Anmeldung ein Kennwort verwenden, werden Sie zur Eingabe dieses Kennworts aufgefordert.If you use a password to authenticate your SSH login, you're prompted for the password.

    Wenn Sie einen öffentlichen Schlüssel verwendet haben, müssen Sie möglicherweise den Parameter -i verwenden und den Pfad zum passenden privaten Schlüssel angeben.If you use a public key, you might need to use the -i parameter and specify the path to the matching private key. Beispiel: 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. Stellen Sie nach Abschluss des Uploadvorgangs eine SSH-Verbindung mit dem Cluster her.After the upload has finished, connect to the cluster by using SSH. Geben Sie an der Eingabeaufforderung den folgenden Befehl ein:On the command prompt, enter the following command:

    ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
    
  3. Extrahieren Sie die ZIP-Datei mit folgendem Befehl:Use the following command to unzip the .zip file:

    unzip <file-name>.zip
    

    Dieser Befehl extrahiert eine CSV-Datei.The command extracts a .csv file.

  4. Verwenden Sie den folgenden Befehl, um das Data Lake Storage Gen2-Dateisystem zu erstellen.Use the following command to create the Data Lake Storage Gen2 file system.

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

    Ersetzen Sie den Platzhalter <file-system-name> durch den Namen, den Sie für Ihr Dateisystem verwenden möchten.Replace the <file-system-name> placeholder with the name that you want to give your file system.

    Ersetzen Sie den Platzhalter <storage-account-name> durch den Namen Ihres Speicherkontos.Replace the <storage-account-name> placeholder with the name of your storage account.

  5. Verwenden Sie den folgenden Befehl, um ein Verzeichnis zu erstellen.Use the following command to create a directory.

    hdfs dfs -mkdir -p abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data
    
  6. Kopieren Sie die CSV-Datei mit dem folgenden Befehl in das Verzeichnis:Use the following command to copy the .csv file to the directory:

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

    Setzen Sie den Dateinamen in Anführungszeichen, wenn dieser Leerzeichen oder Sonderzeichen enthält.Use quotes around the file name if the file name contains spaces or special characters.

Transformieren der DatenTransform the data

In diesem Abschnitt verwenden Sie Beeline, um einen Apache Hive-Auftrag auszuführen.In this section, you use Beeline to run an Apache Hive job.

Im Rahmen des Apache Hive-Auftrags importieren Sie die Daten aus der CSV-Datei in eine Apache Hive-Tabelle namens delays.As part of the Apache Hive job, you import the data from the .csv file into an Apache Hive table named delays.

  1. Verwenden Sie an der SSH-Eingabeaufforderung, die bereits für den HDInsight-Cluster geöffnet ist, den folgenden Befehl, um eine neue Datei namens flightdelays.hql zu erstellen und zu bearbeiten: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. Ändern Sie den folgenden Text, indem Sie die Platzhalter <file-system-name> und <storage-account-name> durch den Namen Ihres Dateisystems bzw. Ihres Speicherkontos ersetzen.Modify the following text by replace the <file-system-name> and <storage-account-name> placeholders with your file system and storage account name. Kopieren Sie den Text anschließend, und fügen Sie ihn in die Nano-Konsole ein, indem Sie die UMSCHALTTASTE drücken und gleichzeitig mit der rechten Maustaste klicken.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://<file-system-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://<file-system-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. Speichern Sie die Datei mit STRG+X, und geben Sie dann Y ein, wenn Sie zur Eingabe aufgefordert werden.Save the file by using use CTRL+X and then type Y when prompted.

  4. Starten Sie Hive mit dem folgenden Befehl, und führen Sie die Datei flightdelays.hql aus: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. Öffnen Sie nach der Ausführung des Skripts flightdelays.hql mithilfe des folgenden Befehls eine interaktive Beeline-Sitzung: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. Wenn Sie die jdbc:hive2://localhost:10001/>-Eingabeaufforderung erhalten, rufen Sie die Daten mit der folgenden Abfrage aus den importierten Flugverspätungsdaten ab: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;
    

    Mit dieser Abfrage werden eine Liste von Orten, in denen Verspätungen infolge des Wetters auftraten, sowie die durchschnittliche Verspätung abgerufen. Die Liste wird anschließend in abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output gespeichert.This query retrieves a list of cities that experienced weather delays, along with the average delay time, and saves it to abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Sqoop liest später die Daten an diesem Speicherort und exportiert sie in die Azure SQL-Datenbank.Later, Sqoop reads the data from this location and exports it to Azure SQL Database.

  7. Geben Sie zum Beenden von Beeline !quit an der Eingabeaufforderung ein.To exit Beeline, enter !quit at the prompt.

Erstellen einer SQL-DatenbanktabelleCreate a SQL database table

Für diesen Vorgang benötigen Sie den Servernamen Ihrer SQL-Datenbank.You need the server name from your SQL database for this operation. Führen Sie die folgenden Schritte aus, um den Servernamen festzustellen.Complete these steps to find your server name.

  1. Öffnen Sie das Azure-Portal.Go to the Azure portal.

  2. Wählen Sie SQL-Datenbanken aus.Select SQL Databases.

  3. Filtern Sie nach dem Namen der Datenbank, die Sie verwenden möchten.Filter on the name of the database that you choose to use. Der Servername befindet sich in der Spalte Servername.The server name is listed in the Server name column.

  4. Filtern Sie nach dem Namen der Datenbank, die Sie verwenden möchten.Filter on the name of the database that you want to use. Der Servername befindet sich in der Spalte Servername.The server name is listed in the Server name column.

    Abrufen von Details für den Azure SQL-ServerGet Azure SQL server details

    Es gibt viele Möglichkeiten, eine Verbindung mit der SQL-Datenbank herzustellen und eine Tabelle zu erstellen.There are many ways to connect to SQL Database and create a table. Die folgenden Schritte verwenden FreeTDS aus dem HDInsight-Cluster.The following steps use FreeTDS from the HDInsight cluster.

  5. Verwenden Sie zum Installieren von FreeTDS den folgenden Befehl über eine SSH-Verbindung mit dem Cluster: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. Stellen Sie nach Abschluss der Installation mithilfe des folgenden Befehls eine Verbindung mit dem SQL-Datenbankserver her.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>'
    
    • Ersetzen Sie den Platzhalter <server-name> durch den Namen des SQL-Datenbankservers.Replace the <server-name> placeholder with the SQL Database server name.

    • Ersetzen Sie den Platzhalter <admin-login> durch den Administratoranmeldenamen für SQL-Datenbank.Replace the <admin-login> placeholder with the admin login for SQL Database.

    • Ersetzen Sie den Platzhalter <database-name> durch den Namen der Datenbank.Replace the <database-name> placeholder with the database name

    Geben Sie das Kennwort für die SQL-Datenbank-Administratoranmeldung ein, wenn Sie dazu aufgefordert werden.When you're prompted, enter the password for the SQL Database admin login.

    Eine Ausgabe ähnlich folgendem Text wird angezeigt: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. Geben Sie an der Eingabeaufforderung 1> die folgenden Anweisungen ein: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. Nach Eingabe der Anweisung GO werden die vorherigen Anweisungen ausgewertet.When the GO statement is entered, the previous statements are evaluated.

    Diese Abfrage erstellt eine Tabelle namens delays, die über einen gruppierten Index verfügt.The query creates a table named delays, which has a clustered index.

  9. Stellen Sie mithilfe der folgenden Abfrage sicher, dass die Tabelle erstellt wurde:Use the following query to verify that the table is created:

    SELECT * FROM information_schema.tables
    GO
    

    Die Ausgabe sieht in etwa wie folgender Text aus:The output is similar to the following text:

    TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
    databaseName       dbo             delays        BASE TABLE
    
  10. EINGABE exit at the 1> ein.Enter exit at the 1> prompt to exit the tsql utility.

Exportieren und Laden der DatenExport and load the data

In den vorherigen Abschnitten haben Sie die transformierten Daten unter abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output kopiert.In the previous sections, you copied the transformed data at the location abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. In diesem Abschnitt verwenden Sie Sqoop, um die Daten aus abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output in die Tabelle zu exportieren, die Sie in der Azure SQL-Datenbank erstellt haben.In this section, you use Sqoop to export the data from abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output to the table you created in the Azure SQL database.

  1. Verwenden Sie den folgenden Befehl, um zu überprüfen, ob Sqoop Ihre SQL-Datenbank erreichen kann: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>
    

    Der Befehl gibt eine Liste von Datenbanken zurück, in der auch die Datenbank enthalten ist, in der Sie die Tabelle delays erstellt haben.The command returns a list of databases, including the database in which you created the delays table.

  2. Verwenden Sie den folgenden Befehl, um Daten aus der Tabelle hivesampletable in die Tabelle delays zu exportieren: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://<file-system-name>@.dfs.core.windows.net/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
    

    Sqoop stellt eine Verbindung mit der Datenbank her, die die Tabelle delays enthält, und exportiert Daten aus dem Verzeichnis /tutorials/flightdelays/output in die Tabelle 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. Nach Abschluss des sqoop-Befehls stellen Sie über das Hilfsprogramm tsql eine Verbindung mit der Datenbank her: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. Überprüfen Sie mithilfe der folgenden Anweisungen, ob die Daten in die Tabelle delays exportiert wurden:Use the following statements to verify that the data was exported to the delays table:

    SELECT * FROM delays
    GO
    

    Es sollte eine Liste der Tabellendaten angezeigt werden.You should see a listing of data in the table. Die Tabelle enthält den Namen der Stadt und die durchschnittliche Flugverspätung für diese Stadt.The table includes the city name and the average flight delay time for that city.

  5. Geben Sie exit ein, um das Hilfsprogramm tsql zu beenden.Enter exit to exit the tsql utility.

Bereinigen von RessourcenClean up resources

In diesem Tutorial wurden nur Ressourcen verwendet, die bereits vorhanden waren.All resources used in this tutorial are preexisting. Eine Bereinigung ist nicht erforderlich.No cleanup is necessary.

Nächste SchritteNext steps

Weitere Informationen zum Arbeiten mit Daten in HDInsight finden Sie im folgenden Artikel:To learn more ways to work with data in HDInsight, see the following article: