Oktatóanyag: Adatok kinyeréséről, átalakításáról és betöltéséről az Azure HDInsight használatával

Ebben az oktatóanyagban egy ETL-műveletet hajt végre: adatok kinyerése, átalakítása és betöltése. Egy nyers CSV-adatfájlt fog, importálja egy Azure HDInsight-fürtbe, átalakítja az Apache Hive-tel, és betölti az Azure SQL Database-be az Apache Sqoop használatával.

Ebben az oktatóanyagban az alábbiakkal fog megismerkedni:

• Kinyerheti és feltöltheti az adatokat egy HDInsight-fürtbe.
• Alakítsa át az adatokat az Apache Hive használatával.
• Töltse be az adatokat az Azure SQL Database-be a Sqoop használatával.

Ha még nincs Azure-előfizetése, kezdés előtt hozzon létre egy ingyenes fiókot.

Előfeltételek

• A HDInsighthoz konfigurált hierarchikus névtérrel (Azure Data Lake Storage) rendelkező tárfiók

  Lásd: Az Azure Data Lake Storage használata Azure HDInsight-fürtökkel.

• Linux-alapú Hadoop-fürt a HDInsighton

  Tekintse meg a rövid útmutatót: Az Apache Hadoop és az Apache Hive használatának első lépései az Azure HDInsightban az Azure Portal használatával.

• Azure SQL Database

  Céladattárként az Azure SQL Database-t használja. Ha nem rendelkezik adatbázissal az SQL Database-ben, olvassa el az Adatbázis létrehozása az Azure SQL Database-ben az Azure Portalon című témakört.

• Azure CLI

  Ha még nem telepítette az Azure CLI-t, tekintse meg az Azure CLI telepítését.

• Secure Shell-ügyfél (SSH)

  További információ: Csatlakozás a HDInsighthoz (Hadoop) SSH használatával.

Töltse le, bontsa ki és töltse fel az adatokat

Ebben a szakaszban letölti a repülési mintaadatokat. Ezután feltölti ezeket az adatokat a HDInsight-fürtbe, majd átmásolja az adatokat a Data Lake Storage-fiókjába.

1. Töltse le a On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip fájlt. Ez a fájl tartalmazza a repülési adatokat.

2. Nyisson meg egy parancssort, és a következő Biztonságos másolás (Scp) paranccsal töltse fel a .zip fájlt a HDInsight-fürt főcsomópontjára:

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

   • Cserélje le a <ssh-user-name> helyőrzőt a HDInsight-fürt SSH-felhasználónevére.
   • Cserélje le a <cluster-name> helyőrzőt a HDInsight-fürt nevére.

   Ha jelszóval hitelesíti SSH-felhasználónevét, a rendszer kéri a jelszót.

   Nyilvános kulcs használatakor lehetséges, hogy az -i paramétert kell használnia, és meg kell adnia a megfelelő titkos kulcs elérési útját. Például: scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. Ha a feltöltés befejeződött, csatlakozzon a fürthöz az SSH-val. A parancssorban adja meg a következő parancsot:

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

4. Használja az alábbi parancsot a .zip fájl kicsomagolásához:

   unzip <file-name>.zip
   

   A parancs kinyer egy .csv fájlt.

5. A Data Lake Storage-tároló létrehozásához használja az alábbi parancsot.

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

   Cserélje le a <container-name> helyőrzőt a tárolónak adni kívánt névre.

   Cserélje le a <storage-account-name> helyőrzőt a tárfiók nevére.

6. A következő paranccsal hozzon létre egy könyvtárat.

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

7. A következő paranccsal másolja a .csv fájlt a könyvtárba:

   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/
   

   Idézőjeleket használjon a fájlnév körül, ha a fájlnév szóközöket vagy speciális karaktereket tartalmaz.

Az adatok átalakítása

Ebben a szakaszban a Beeline használatával futtat egy Apache Hive-feladatot.

Az Apache Hive-feladat részeként importálja az adatokat a .csv fájlból egy Apache Hive-táblába, amely késések néven szerepel.

1. A HDInsight-fürthöz már megnyitott SSH-parancssorba írja be a következő parancsot, amely létrehoz egy flightdelays.hql nevű a szerkesztéséhez:

   nano flightdelays.hql
   

2. Módosítsa a következő szöveget úgy, hogy lecseréli a <container-name> <storage-account-name> helyőrzőket a tároló és a tárfiók nevére. Ezután másolja és illessze be a szöveget a nanokonzolba a SHIFT billentyű és a jobb egérkijelölő gomb lenyomásával.

     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. Mentse a fájlt a CTRL+X billentyűkombináció lenyomásával, majd amikor a rendszer kéri, írja be Y a fájlt.

4. A Hive elindításához és a flightdelays.hql fájl futtatásához használja a következő parancsot:

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

5. flightdelays.hql A szkript futtatása után az alábbi paranccsal nyisson meg egy interaktív Beeline-munkamenetet:

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

6. Amikor a jdbc:hive2://localhost:10001/> parancssor megjelenik, a következő lekérdezéssel nyerhet ki adatokat az importált repülőjárat-késési adatokból:

   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;
   

   Ez a lekérdezés lekéri azon városok listáját, ahol időjárás miatti késések történtek, valamint a késések átlagos idejét, és menti ezeket az adatokat a következő helyen: abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Később a Sqoop erről a helyről olvassa be az adatokat, amelyeket exportál az Azure SQL Database-be.

7. A Beeline-ból való kilépéshez írja be a parancssorba a !quit parancsot.

SQL Database-tábla létrehozása

Ehhez a művelethez szüksége van az SQL Database kiszolgálónevére. Hajtsa végre ezeket a lépéseket a kiszolgáló nevének megkereséséhez.

1. Nyissa meg az Azure Portalt.

2. Válassza az SQL Databases lehetőséget.

3. Szűrjön a használni kívánt adatbázis nevére. A kiszolgáló neve a Kiszolgáló neve oszlopban látható.

4. Szűrjön a használni kívánt adatbázis nevére. A kiszolgáló neve a Kiszolgáló neve oszlopban látható.

   

   Számos módon csatlakozhat az SQL Database-hez, majd hozhat létre egy táblát. A következő lépések során a FreeTDS eszközt használjuk a HDInsight-fürtről.

5. A FreeTDS telepítéséhez használja a következő parancsot egy, a fürthöz csatlakozó SSH-kapcsolaton:

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

6. A telepítés befejezése után az alábbi paranccsal csatlakozzon az SQL Database-hez.

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

   • Cserélje le a <server-name> helyőrzőt a logikai SQL-kiszolgáló nevére.

   • Cserélje le a <admin-login> helyőrzőt az SQL Database rendszergazdai felhasználónevére.

   • Cserélje le a <database-name> helyőrzőt az adatbázis nevére

   Amikor a rendszer kéri, adja meg az SQL Database rendszergazdai felhasználónevének jelszavát.

   A kimenet a következő szöveghez fog hasonlítani:

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

7. A parancssorba 1> írja be a következő utasításokat:

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

8. A GO utasítás megadásakor a rendszer kiértékeli az előző utasításokat.

   A lekérdezés létrehoz egy késések nevű táblát, amely fürtözött indexet tartalmaz.

9. A tábla létrehozásának ellenőrzéséhez használja a következő lekérdezést:

   SELECT * FROM information_schema.tables
   GO
   

   A kimenet az alábbi szöveghez hasonló:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

10. A tsql eszközből való kilépéshez írja be az exit kifejezést az 1> parancssorba.

Adatok exportálása és betöltése

Az előző szakaszokban átmásolta az átalakított adatokat a helyre abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Ebben a szakaszban a Sqoop használatával exportálja az adatokat az Azure SQL Database-ben létrehozott táblába abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output .

1. A következő paranccsal ellenőrizze, hogy a Sqoop látja-e az SQL-adatbázist:

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

   A parancs visszaadja az adatbázisok listáját, beleértve azt az adatbázist is, amelyben létrehozta a késleltetési táblát .

2. Az alábbi paranccsal exportálhat adatokat a hivesampletable táblából a delays táblába:

   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
   

   A Sqoop a delays táblát tartalmazó adatbázishoz csatlakozik, és adatokat exportál a címtárból a /tutorials/flightdelays/output delays táblába.

3. sqoop A parancs befejeződése után a tsql segédprogrammal csatlakozzon az adatbázishoz:

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

4. Az alábbi utasítások segítségével ellenőrizheti, hogy az adatok exportálva lett-e a késési táblába:

   SELECT * FROM delays
   GO
   

   A táblában látnia kell az adatok listáját. A tábla a városok nevét és az egyes városokhoz tartozó átlagos késések idejét tartalmazza.

5. Lépjen ki exit a tsql segédprogramból.

Az erőforrások eltávolítása

Az oktatóanyagban használt összes erőforrás már létezik. Nincs szükség törlésre.

Következő lépések

Az adatok HDInsightban való használatával kapcsolatos további lehetőségekért tekintse meg a következő cikket:

Az Azure Data Lake Storage használata Azure HDInsight-fürtökkel