Zelfstudie: Gegevens extraheren, transformeren en laden met behulp van Interactive Query in Azure HDInsight

Artikel
04/04/2025

In deze zelfstudie downloadt u een onbewerkt CSV-gegevensbestand met openbaar beschikbare vluchtgegevens. Importeer deze in HDInsight-clusteropslag en transformeer de gegevens vervolgens met behulp van Interactive Query in Azure HDInsight. Zodra de gegevens zijn getransformeerd, laadt u die gegevens in een database in Azure SQL Database met behulp van Apache Sqoop.

Deze zelfstudie bestaat uit de volgende taken:

De voorbeeldvluchtgegevens downloaden
Gegevens uploaden naar een HDInsight-cluster
De gegevens transformeren met behulp van Interactive Query
Een tabel maken in een database in Azure SQL Database
Sqoop gebruiken om gegevens te exporteren naar een database in Azure SQL Database

Vereiste voorwaarden

Een Interactive Query-cluster in HDInsight. Zie Apache Hadoop-clusters maken met behulp van De Azure-portal en selecteer Interactive Query voor clustertype.
Een database in Azure SQL Database. U gebruikt de database als doelgegevensarchief. Als u geen database in Azure SQL Database hebt, raadpleegt u Een database maken in Azure SQL Database in Azure Portal.
Een SSH-client. Zie voor meer informatie Verbinding maken met HDInsight (Apache Hadoop) via SSH.

De vluchtgegevens downloaden

Blader naar Research and Innovative Technology Administration, Bureau of Transportation Statistics.

Wis alle velden op de pagina en selecteer vervolgens de volgende waarden:

Naam	Waarde
Jaar filteren	2019
Filterperiode	Januari
Velden	`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`.

Klik op Downloaden. Er wordt een .zip-bestand gedownload met de gegevensvelden die u hebt geselecteerd.

Gegevens uploaden naar een HDInsight-cluster

Er zijn veel manieren om gegevens te uploaden naar de opslag die is gekoppeld aan een HDInsight-cluster. In deze sectie gebruikt scp u om gegevens te uploaden. Zie Gegevens uploaden naar HDInsight voor meer informatie over andere manieren om gegevens te uploaden.

Upload het .zip-bestand naar het hoofdknooppunt van het HDInsight-cluster. Bewerk de onderstaande opdracht door FILENAME te vervangen met de naam van het .zip-bestand en CLUSTERNAME met de naam van het HDInsight-cluster. Open vervolgens een opdrachtprompt, stel uw werkmap in op de bestandslocatie en voer vervolgens de opdracht in:
```
scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
```
Voer ja of nee in om door te gaan als u hierom wordt gevraagd. De tekst is niet zichtbaar in het venster terwijl u typt.
Nadat het uploaden is voltooid, maakt u via SSH verbinding met het cluster. Bewerk de onderstaande opdracht door de naam van het HDInsight-cluster te vervangen CLUSTERNAME . Voer vervolgens de volgende opdracht in:
```
ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
```
Stel de omgevingsvariabele in zodra er een SSH-verbinding tot stand is gebracht. Vervang FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USER en SQL_PASWORD door de juiste waarden. Voer vervolgens de opdracht in:
```
export FILENAME=FILE_NAME
export SQLSERVERNAME=SQL_SERVERNAME
export DATABASE=SQL_DATABASE
export SQLUSER=SQL_USER
export SQLPASWORD='SQL_PASWORD'
```
Pak het .zip bestand uit door de onderstaande opdracht in te voeren:
```
unzip $FILENAME.zip
```
Maak een map in HDInsight-opslag en kopieer het .csv bestand naar de map door de onderstaande opdracht in te voeren:
```
hdfs dfs -mkdir -p /tutorials/flightdelays/data
hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
```

Gegevens transformeren met behulp van een Hive-query

Er zijn veel manieren om een Hive-taak uit te voeren op een HDInsight-cluster. In deze sectie gebruikt u Beeline om een Hive-taak uit te voeren. Zie Apache Hive gebruiken in HDInsight voor meer informatie over andere methoden voor het uitvoeren van een Hive-taak.

Als onderdeel van de Hive-taak importeert u de gegevens uit het .csv-bestand in een Hive-tabel met de naam Delays.

Gebruik vanuit de SSH-prompt die u al hebt voor het HDInsight-cluster de volgende opdracht om een nieuw bestand met de naam flightdelays.hql te maken en te bewerken:
```
nano flightdelays.hql
```

Gebruik de volgende tekst als de inhoud van dit bestand:

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;

Als u het bestand wilt opslaan, drukt u op Ctrl+X, vervolgens op y en vervolgens op Enter.
Gebruik de volgende opdracht om Hive te starten en het bestand flightdelays.hql uit te voeren:
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
```
Nadat het script flightdelays.hql is uitgevoerd, gebruikt u de volgende opdracht om een interactieve Beeline-sessie te openen:
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
```
Wanneer u de prompt jdbc:hive2://localhost:10001/> ziet, gebruikt u de volgende query om gegevens op te halen uit de geïmporteerde gegevens van vertraagde vluchten:
```
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;
```
Met deze query haalt u een lijst op met plaatsen waar er vertragingen door het weer zijn ontstaan, samen met de gemiddelde vertragingstijd. Deze gegevens worden opgeslagen in /tutorials/flightdelays/output. Later worden de gegevens met Sqoop vanaf deze locatie gelezen en geëxporteerd naar Azure SQL Database.
Sluit Beeline af door !quit in te voeren bij de opdrachtprompt.

Een SQL-databasetabel maken

Er zijn veel manieren om verbinding te maken met SQL Database en een tabel te maken. In de volgende stappen wordt FreeTDS gebruikt vanuit het HDInsight-cluster.

Als u FreeTDS wilt installeren, gebruikt u de volgende opdracht vanuit de open SSH-verbinding met het cluster:
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```

Nadat de installatie is voltooid, gebruikt u de volgende opdracht om verbinding te maken met SQL Database:

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

U ziet uitvoer die vergelijkbaar is met de volgende tekst:

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

Voer de volgende regels in bij de prompt 1>:

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

Wanneer u de instructie GO invoert, worden de vorige instructies geëvalueerd. Met deze instructie maakt u een tabel met de naam vertragingen, met een geclusterde index.

Gebruik de volgende query om te controleren of de tabel is gemaakt:

SELECT * FROM information_schema.tables
GO

De uitvoer lijkt op het volgende:

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
databaseName       dbo             delays        BASE TABLE

Voer exit in bij de prompt 1> om het hulpprogramma tsql af te sluiten.

Gegevens exporteren naar SQL Database met Apache Sqoop

In de vorige secties hebt u de getransformeerde gegevens gekopieerd op /tutorials/flightdelays/output. In deze sectie gebruikt u Sqoop om de gegevens te exporteren van /tutorials/flightdelays/output naar de tabel die u hebt gemaakt in Azure SQL Database.

Controleer of Sqoop uw SQL-database kan zien door de onderstaande opdracht in te voeren:
```
sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
```
Met deze opdracht wordt een lijst met databases geretourneerd, inclusief de database waarin u de delays tabel eerder hebt gemaakt.
Exporteer gegevens van /tutorials/flightdelays/output naar de delays tabel door de onderstaande opdracht in te voeren:
```
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
```
Sqoop maakt verbinding met de database die de delays tabel bevat en exporteert gegevens uit de /tutorials/flightdelays/output map naar de delays tabel.
Nadat de sqoop-opdracht is voltooid, gebruikt u het hulpprogramma tsql om verbinding te maken met de database door de onderstaande opdracht in te voeren:
```
TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
```
Gebruik de volgende verklaringen om te controleren of de gegevens zijn geëxporteerd naar de tabel 'vertragingen':
```
SELECT * FROM delays
GO
```
U zou een lijst met gegevens in de tabel moeten zien. De tabel bevat de plaatsnaam en de gemiddelde vertragingstijd voor vluchten van en naar die plaats.

Typ exit om het tsql-hulpprogramma af te sluiten.

Hulpmiddelen opruimen

Nadat u de zelfstudie hebt voltooid, wilt u misschien het cluster verwijderen. Met HDInsight worden uw gegevens opgeslagen in Azure Storage zodat u een cluster veilig kunt verwijderen wanneer deze niet wordt gebruikt. Voor een HDInsight-cluster worden ook kosten in rekening gebracht, zelfs wanneer het niet wordt gebruikt. Aangezien de kosten voor het cluster vaak zoveel hoger zijn dan de kosten voor opslag, is het financieel gezien logischer clusters te verwijderen wanneer ze niet worden gebruikt.

Als u een cluster wilt verwijderen, raadpleegt u HDInsight-cluster verwijderen met behulp van uw browser, PowerShell of de Azure CLI.

Volgende stappen

In deze zelfstudie hebt u een onbewerkt CSV-gegevensbestand gemaakt, geïmporteerd in een HDInsight-clusteropslag en vervolgens de gegevens getransformeerd met behulp van Interactive Query in Azure HDInsight. Ga naar de volgende zelfstudie voor meer informatie over de Apache Hive Warehouse-connector.

Apache Spark en Apache Hive integreren met Hive Warehouse Connector

Delen via