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:

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

Förutsättningar

• Ett Azure Data Lake Storage Gen2-lagringskonto 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: Komma 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 en databas i SQL Database kan du se Skapa en databas i Azure SQL Database i Azure Portal.

• Azure CLI: Om du inte har installerat Azure CLI kan du gå till Installera Azure CLI.

• En Secure Shell-klient (SSH): Mer information finns i Anslut till HDInsight (Hadoop) med hjälp av SSH.

Ladda ned flygdata

1. Gå till Research and Innovative Technology Administration, Bureau of Transportation Statistics.

2. Välj följande värden på sidan:

   Name	Värde
   Filtrera år	2013
   Filtrera period	Januari
   Fält	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.

   Rensa alla andra fält.

3. Välj Hämta. Du får en .zip-fil med de datafält du valde.

Extrahera och ladda upp data

I det här avsnittet laddar du upp data till ditt HDInsight-kluster och kopierar sedan dessa data till ditt Data Lake Storage Gen2-konto.

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

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

   • Ersätt platshållaren <file-name> med namnet på .zip-filen.
   • Ersätt platshållaren <ssh-user-name> med SSH-inloggningen 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 din SSH-inloggning 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:.

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

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

   unzip <file-name>.zip
   

   Kommandot extraherar en .csv-fil.

4. 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/
   

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

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

5. 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
   

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

   hdfs dfs -put "<file-name>.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 den SSH-prompt 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 ersätta <container-name> <storage-account-name> platshållarna och med namnet på containern och lagringskontot. Kopiera och klistra sedan in texten i nano-konsolen genom att trycka på Skift-tangenten tillsammans med höger musknapp.

   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. Spara filen med hjälp av CTRL+X och skriv sedan Y när du tillfrågas.

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(origin_city_name, '''', ''),
       avg(weather_delay)
   FROM delays
   WHERE weather_delay IS NOT NULL
   GROUP BY origin_city_name;
   

   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>'
   

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

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

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

   När du uppmanas att göra det anger du lösenordet för SQL Database-administratörsinloggningen.

   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 abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output från till tabellen 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: