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

Cikk
07/27/2022
6 perc alatt elolvasható

Ebben az oktatóanyagban egy nyers CSV-adatfájlt tölt le nyilvánosan elérhető repülési adatokból. Importálja az adatokat a HDInsight-fürt tárolójába, majd alakítsa át az adatokat az Azure HDInsight Interactive Query használatával. Az adatok átalakítása után betölti az adatokat egy adatbázisba Azure SQL Database 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
Tábla létrehozása egy adatbázisban Azure SQL Database
Adatok exportálása Azure SQL Database adatbázisába 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 ki a Interactive Querya fürttípushoz.
Adatbázis Azure SQL Database. Az adatbázist céladattárként használja. Ha nem rendelkezik adatbázissal Azure SQL Database, olvassa el az Adatbázis létrehozása Azure SQL Database a Azure Portal. című témakört.
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

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.

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

Válassza a Letöltés lehetőséget. Egy .zip fájlt kap, amely a kiválasztott adatmezőket tartalmazza.

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.

Töltse fel a .zip fájlt a HDInsight-fürt átjárócsomópontjára. Szerkessze az alábbi parancsot a .zip fájl nevére és CLUSTERNAME a HDInsight-fürt nevére cserélveFILENAME. Ezután nyisson meg egy parancssort, állítsa be a munkakönyvtárat a fájl helyére, majd írja be a 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.
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
```
Á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 a megfelelő értékeket. 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'
```
Bontsa ki a .zip fájlt az alábbi paranccsal:
```
unzip $FILENAME.zip
```
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.

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

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;

A fájl mentéséhez nyomja le a Ctrl + X billentyűkombinációt, majd az y billentyűt, majd írja be az enter billentyűt.

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

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

A FreeTDS telepítéséhez használja a következő parancsot a fürthöz való nyílt SSH-kapcsolatból:
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```

A telepítés befejezése után a következő paranccsal csatlakozhat 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>

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 késések 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

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 /tutorials/flightdelays/output a Azure SQL Database létrehozott táblába.

Az alábbi parancs beírásával ellenőrizze, hogy a Sqoop látja-e a SQL-adatbázist:
```
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 .

Adatok exportálása a delays táblába /tutorials/flightdelays/output az alábbi paranccsal:

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.

A sqoop parancs befejeződése után a tsql segédprogrammal csatlakozzon az adatbázishoz az alábbi parancs beírásával:
```
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 az adatokat az Azure Storage tárolja, így biztonságosan törölheti a fürtöt, ha nincs 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 célszerű törölni a fürtöket, ha nincsenek használatban.

A fürtök törléséről további információt 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 talál.

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 az Azure HDInsight Interactive Query használatával. 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