Självstudie: Extrahera, transformera och läsa in data med hjälp av Azure HDInsight

I den här självstudien ska du utföra en ETL-åtgärd: extrahera, transformera och läsa in data. Du tar en CSV-rådatafil, importerar den till ett Azure HDInsight-kluster, transformerar den med Apache Hive och läser in den i Azure SQL Database med Apache Sqoop.

I den här guiden får du lära dig att:

• Extrahera och ladda upp data till ett HDInsight-kluster.
• Transformera data med hjälp av Apache Hive.
• Läs in data till Azure SQL Database med hjälp av Sqoop.

Om du inte har en Azure-prenumeration kan du skapa ett kostnadsfritt konto innan du börjar.

Förutsättningar

• Ett lagringskonto som har ett hierarkiskt namnområde (Azure Data Lake Storage Gen2) som har konfigurerats för HDInsight

  Se Använda Azure Data Lake Storage Gen2 med Azure HDInsight-kluster.

• Ett Linux-baserat Hadoop-kluster i HDInsight

  Se Snabbstart: Kom igång med Apache Hadoop och Apache Hive i Azure HDInsight med hjälp av Azure Portal.

• Azure SQL Database

  Du använder Azure SQL Database som måldatalager. Om du inte har någon databas i SQL Database läser du Skapa en databas i Azure SQL Database i Azure Portal.

• Azure CLI

  Om du inte har installerat Azure CLI läser du Installera Azure CLI.

• En SSH-klient (Secure Shell)

  Mer information finns i Ansluta till HDInsight (Hadoop) med hjälp av SSH.

Ladda ned, extrahera och ladda sedan upp data

I det här avsnittet laddar du ned exempel på flygdata. Sedan laddar du upp dessa data till DITT HDInsight-kluster och kopierar sedan dessa data till ditt Data Lake Storage Gen2-konto.

1. Ladda ned filenOn_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip . Den här filen innehåller flygdata.

2. Öppna en kommandotolk och använd följande Secure Copy-kommando (Scp) för att ladda upp .zip-filen till HDInsight-klustrets huvudnod:

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

   • <ssh-user-name> Ersätt platshållaren med SSH-användarnamnet för HDInsight-klustret.
   • Ersätt platshållaren <cluster-name> med namnet på HDInsight-klustret.

   Om du använder ett lösenord för att autentisera ditt SSH-användarnamn uppmanas du att ange lösenordet.

   Om du använder en offentlig nyckel kan du behöva använda -i-parametern och ange sökvägen till motsvarande privata nyckel. Till exempel scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. När uppladdningen är klar kan du ansluta till klustret med hjälp av SSH. Öppna kommandotolken och ange följande kommando:

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

4. Använd följande kommando för att packa upp .zip-filen:

   unzip <file-name>.zip
   

   Kommandot extraherar en .csv-fil.

5. Använd följande kommando för att skapa Data Lake Storage Gen2-containern.

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

   <container-name> Ersätt platshållaren med det namn som du vill ge containern.

   Ersätt platshållaren <storage-account-name> med namnet på ditt lagringskonto.

6. Använd följande kommando för att skapa en katalog.

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

7. Använd följande kommando för att kopiera .csv-filen till katalogen:

   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/
   

   Använd citattecken runt filnamnet om filnamnet innehåller blanksteg eller specialtecken.

Transformera data

I det här avsnittet ska du använda Beeline för att köra ett Apache Hive-jobb.

Som en del av Apache Hive-jobbet importerar du data från CSV-filen till en Apache Hive-tabell med namnet delays.

1. Från SSH-prompten som du redan har för HDInsight-klustret använder du följande kommando för att skapa och redigera en ny fil med namnet flightdelays.hql:

   nano flightdelays.hql
   

2. Ändra följande text genom att <container-name> ersätta platshållarna och <storage-account-name> med namnet på containern och lagringskontot. Kopiera och klistra sedan in texten i nanokonsolen genom att trycka på SKIFT-tangenten tillsammans med höger musmarkeringsknappen.

     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. Spara filen genom att skriva CTRL+X och sedan skriva när du uppmanas att göra Y det.

4. Starta Hive och kör flightdelays.hql filen med följande kommando:

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

5. När skriptet flightdelays.hql har körts klart använder du följande kommando för att öppna en interaktiv Beeline-session:

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

6. När du får uppmaningen jdbc:hive2://localhost:10001/> ska du använda följande fråga för att hämta data från de importerade flygförseningsdata:

   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;
   

   Frågan returnerar en lista över städer som berörs av förseningar på grund av vädret samt genomsnittlig förseningstid och sparar det till abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Senare läser Sqoop data från den här platsen och exporterar dem till Azure SQL Database.

7. Om du vill avsluta Beeline skriver du !quit vid uppmaningen.

Skapa en SQL-databastabell

Du behöver servernamnet från SQL Database för den här åtgärden. Slutför stegen nedan för att hitta namnet på servern.

1. Gå till Azure-portalen.

2. Välj SQL-databaser.

3. Filtrera efter namnet på den databas som du vill använda. Serverns namn finns i kolumnen Servernamn.

4. Filtrera efter namnet på den databas som du vill använda. Serverns namn finns i kolumnen Servernamn.

   

   Det finns många sätt att ansluta till SQL Database och skapa en tabell. Följande steg använder FreeTDS från HDInsight-klustret.

5. För att installera FreeTDS använder du följande kommando från en SSH-anslutning till klustret:

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

6. När installationen är klar använder du följande kommando för att ansluta till SQL Database.

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

   • <server-name> Ersätt platshållaren med det logiska SQL-servernamnet.

   • <admin-login> Ersätt platshållaren med administratörsanvändarnamnet för SQL Database.

   • Ersätt platshållaren <database-name> med databasnamnet

   När du uppmanas till det anger du lösenordet för SQL Database administratörsanvändarnamn.

   Du får utdata som liknar följande 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. Ange följande instruktion vid 1>-prompten:

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

8. När instruktionen GO har angivits värderas de föregående instruktionerna.

   Frågan skapar en tabell med namnet delays, som har ett grupperat index.

9. Använd följande fråga för att kontrollera att tabellen har skapats:

   SELECT * FROM information_schema.tables
   GO
   

   De utdata som genereras liknar följande text:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

10. Skriv exit vid uppmaningen 1> för att avsluta tsql-verktyget.

Exportera och läsa in data

I föregående avsnitt kopierade du transformerade data på platsen abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. I det här avsnittet använder du Sqoop för att exportera data från abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output till den tabell som du skapade i Azure SQL Database.

1. Använd följande kommando för att verifiera att Sqoop kan se din SQL-databas:

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

   Det här kommandot returnerar en lista med databaser, däribland databasen som du skapade delays-tabellen i.

2. Använd följande kommando för att exportera data från tabellen hivesampletable till tabellen 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 ansluter till databasen som innehåller tabellen delays och exporterar data från /tutorials/flightdelays/output-katalogen till tabellen delays.

3. När sqoop-kommandot avslutas använder du tsql-verktyget för att ansluta till databasen:

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

4. Använd följande instruktioner för att verifiera att data exporterades till tabellen delays:

   SELECT * FROM delays
   GO
   

   Du ska se en lista över data i tabellen. Tabellen innehåller stadens namn och genomsnittlig flygförseningstid för den staden.

5. Skriv exit för att avsluta tsql-verktyget.

Rensa resurser

Alla resurser som använts i den här självstudien fanns redan. Ingen rensning krävs.

Nästa steg

Mer information om att arbeta med data i HDInsight finns i följande artikel:

Använda Azure Data Lake Storage Gen2 med Azure HDInsight-kluster