Oktatóanyag: Adatok kinyerése, átalakítása és betöltése Interactive Query használatával az Azure HDInsightban

Ebben az oktatóanyagban egy nyers CSV-adatfájlt tölt le nyilvánosan elérhető repülési adatokból. Importálja őket a HDInsight-fürttárolóba, majd alakítsa át az adatokat Interactive Query használatával az Azure HDInsightban. Az adatok átalakítása után betölti az adatokat egy adatbázisba Azure SQL Database-ben az Apache Sqoop használatával.

Ez az oktatóanyag a következő feladatokat mutatja be:

• A repülőjárat-mintaadatok letöltése
• Az adatok feltöltése egy HDInsight-fürtre
• Adatok átalakítása Interactive Query használatával
• Tábla létrehozása egy adatbázisban Azure SQL Database-ben
• Adatok exportálása Azure SQL database-be a Sqoop használatával

Előfeltételek

• Egy Interactive Query-fürt a HDInsighton. Lásd: Apache Hadoop-fürtök létrehozása a Azure Portal használatával, és válassza a fürttípus Interactive Query lehetőséget.

• Egy adatbázis a Azure SQL Database-ben. Az adatbázist céladattárként használja. Ha nem rendelkezik adatbázissal Azure SQL Database-ben, olvassa el az Adatbázis létrehozása Azure SQL-adatbázisban című témakört a Azure Portal.

• Egy SSH-ügyfél. További információért lásd: Csatlakozás a HDInsighthoz (Apache Hadoop) SSH-val.

A repülőjárat-adatok letöltése

1. Nyissa meg a Research and Innovative Technology Administration, Bureau of Transportation Statistics (Kutatási és Innovációs Műszaki Felügyelőség, Közlekedési Statisztikai Hivatal) oldalt.

2. A lapon törölje az összes mezőt, majd válassza ki a következő értékeket:

   Name	Érték
   Filter Year (Szűrési év)	2019
   Filter Period (Szűrési időszak)	January
   Mezők	Year, FlightDate, Reporting_Airline, DOT_ID_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.

3. Válassza a Letöltés lehetőséget. A rendszer letölt egy .zip fájlt a kiválasztott adatmezőkkel.

Az adatok feltöltése egy HDInsight-fürtre

Számos különböző módon tölthet fel adatokat egy HDInsight-fürthöz tartozó tárolóra. Ebben a szakaszban az scp segítségével fogja feltölteni az adatokat. Az adatok feltöltésének egyéb módjaival kapcsolatban lásd: Adatok feltöltése a HDInsightba.

1. Töltse fel a .zip fájlt a HDInsight-fürt átjárócsomópontjára. Szerkessze az alábbi FILENAME parancsot az .zip fájl nevére, és CLUSTERNAME a HDInsight-fürt nevére. Ezután nyisson meg egy parancssort, állítsa be a munkakönyvtárat a fájl helyére, majd írja be a következő parancsot:

   scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
   

   Ha a rendszer kéri, adja meg az igen vagy a nem értéket a folytatáshoz. A szöveg beíráskor nem látható az ablakban.

2. Ha a feltöltés befejeződött, csatlakozzon a fürthöz az SSH-val. Szerkessze az alábbi parancsot a HDInsight-fürt nevére cserélve CLUSTERNAME . Ezután írja be a következő parancsot:

   ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
   

3. Állítsa be a környezeti változót az SSH-kapcsolat létrehozása után. Cserélje le FILE_NAMEa , SQL_SERVERNAME, SQL_DATABASE, SQL_USERés SQL_PASWORD értéket a megfelelő értékekre. Ezután írja be a következő parancsot:

   export FILENAME=FILE_NAME
   export SQLSERVERNAME=SQL_SERVERNAME
   export DATABASE=SQL_DATABASE
   export SQLUSER=SQL_USER
   export SQLPASWORD='SQL_PASWORD'
   

4. Bontsa ki a .zip fájlt az alábbi paranccsal:

   unzip $FILENAME.zip
   

5. Hozzon létre egy könyvtárat a HDInsight-tárolóban, majd másolja a .csv fájlt a könyvtárba az alábbi paranccsal:

   hdfs dfs -mkdir -p /tutorials/flightdelays/data
   hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
   

Adatok átalakítása egy Hive-lekérdezéssel

Számos módon futtathat Hive-feladatokat egy HDInsight-fürtön. Ebben a szakaszban a Beeline használatával futtat egy Hive-feladatot. További információ a Hive-feladatok futtatásának egyéb módszereiről: Az Apache Hive használata a HDInsighton.

A Hive-feladat keretében importálja az adatokat a .csv fájlból egy Delays (Késések) nevű Hive-táblába.

1. A HDInsight-fürthöz már meglévő SSH-parancssorból a következő paranccsal hozzon létre és szerkesszen egy flightdelays.hql nevű új fájlt:

   nano flightdelays.hql
   

2. A fájl tartalma legyen a következő szöveg:

   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 '/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 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. A fájl mentéséhez nyomja le a Ctrl + X billentyűkombinációt, majd az y billentyűt, majd írja be a következőt: .

4. Indítsa el a Hive-ot, és futtassa a flightdelays.hql fájlt az alábbi paranccsal:

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

5. Miután a flightdelays.hql szkript lefutott, a következő 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(origin_city_name, '''', ''),
       avg(weather_delay)
   FROM delays
   WHERE weather_delay IS NOT NULL
   GROUP BY origin_city_name;
   

   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: /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

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.

1. A FreeTDS telepítéséhez használja a következő parancsot a fürthöz megnyitott SSH-kapcsolatból:

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

2. A telepítés befejezése után a következő paranccsal csatlakozzon SQL Database:

   TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
   

   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 <yourdatabase>
   1>
   

3. Az 1> parancssorban írja be a következő sorokat:

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

   A GO utasítás megadásakor a rendszer kiértékeli az előző utasításokat. Ez az utasítás létrehoz egy delays nevű táblát egy fürtözött indexszel.

   Az alábbi lekérdezéssel ellenőrizheti, hogy a tábla létrejött-e:

   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
   

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

Adatok exportálása SQL Database az Apache Sqoop használatával

Az előző szakaszok során átmásolta az átalakított adatokat a következő helyre: /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/tutorials/flightdelays/output.

1. Ellenőrizze, hogy a Sqoop látja-e az SQL-adatbázist az alábbi paranccsal:

   sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
   

   Ez a parancs visszaadja az adatbázisok listáját, beleértve azt az adatbázist is, amelyben korábban létrehozta a táblát delays .

2. Exportálja az adatokat a delays táblába /tutorials/flightdelays/output az alábbi parancs beírásával:

   sqoop export --connect "jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433;database=$DATABASE" --username $SQLUSER --password $SQLPASWORD --table 'delays' --export-dir '/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
   

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

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

   TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
   

   A következő utasításokkal ellenőrizheti, hogy az adatok exportálva lettek-e a delays 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.

   A tsql eszközből való kilépéshez írja be az exit parancsot.

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

Az oktatóanyag befejezése után érdemes törölni a fürtöt. A HDInsight segítségével az adatok az Azure Storage-ban tárolódnak, így biztonságosan törölheti a fürtöket, ha nincsenek használatban. A HDInsight-fürtökért is díjat kell fizetnie, még akkor is, ha nincs használatban. Mivel a fürt díjai sokszor nagyobbak, mint a tárolási díjak, gazdasági szempontból érdemes törölni a fürtöket, ha nincsenek használatban.

A fürtök törléséről a HDInsight-fürt törlése a böngésző, a PowerShell vagy az Azure CLI használatával című témakörben olvashat.

Következő lépések

Ebben az oktatóanyagban egy nyers CSV-adatfájlt vett fel, importálta egy HDInsight-fürttárolóba, majd átalakította az adatokat Interactive Query használatával az Azure HDInsightban. Folytassa a következő oktatóanyagban az Apache Hive Warehouse-összekötő megismeréséhez.

Az Apache Spark és az Apache Hive integrálása a Hive Warehouse-összekötővel