Självstudie: Extrahera, transformera och läsa in data med Interaktiv fråga i Azure HDInsight

Artikel
04/27/2023

I den här självstudien laddar du ned en rådatafil med offentligt tillgängliga flygdata. Importera den till HDInsight-klusterlagring och transformera sedan data med hjälp av Interaktiv fråga i Azure HDInsight. När data har omvandlats läser du in dessa data till en databas i Azure SQL Database med hjälp av Apache Sqoop.

Den här självstudien omfattar följande uppgifter:

Ladda ned exempelflygdata
Ladda upp data till ett HDInsight-kluster
Transformera data med hjälp av Interaktiv fråga
Skapa en tabell i en databas i Azure SQL Database
Använda Sqoop för att exportera data till en databas i Azure SQL Database

Förutsättningar

Ett Interaktiv fråga kluster i HDInsight. Se Skapa Apache Hadoop-kluster med hjälp av Azure Portal och välj Interaktiv fråga för Klustertyp.
En databas i Azure SQL Database. Du använder databasen som måldatalager. Om du inte har någon databas i Azure SQL Database läser du Skapa en databas i Azure SQL Database i Azure Portal.
En SSH-klient. Mer information finns i Ansluta till HDInsight (Apache Hadoop) med hjälp av SSH.

Ladda ned flygdata

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

På sidan avmarkerar du alla fält och väljer sedan följande värden:

Name	Värde
Filtrera år	2019
Filtrera period	Januari
Fält	`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älj Hämta. En .zip fil laddas ned med de datafält som du har valt.

Ladda upp data till ett HDInsight-kluster

Det finns många sätt att överföra data till lagring som är associerade med ett HDInsight-kluster. I det här avsnittet använder du scp för att ladda upp data. Om du vill veta mer om andra sätt att ladda upp data kan du läsa Överföra data till HDInsight.

Ladda upp .zip-filen till HUVUDnoden för HDInsight-klustret. Redigera kommandot nedan genom att FILENAME ersätta med namnet på .zip-filen och CLUSTERNAME med namnet på HDInsight-klustret. Öppna sedan en kommandotolk, ange arbetskatalogen till filplatsen och ange sedan kommandot:
```
scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
```
Ange ja eller nej om du vill fortsätta om du uppmanas att göra det. Texten visas inte i fönstret när du skriver.
När uppladdningen är klar kan du ansluta till klustret med hjälp av SSH. Redigera kommandot nedan genom att CLUSTERNAME ersätta med namnet på HDInsight-klustret. Ange sedan följande kommando:
```
ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
```
Konfigurera miljövariabeln när en SSH-anslutning har upprättats. Ersätt FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USERoch SQL_PASWORD med lämpliga värden. Ange sedan kommandot:
```
export FILENAME=FILE_NAME
export SQLSERVERNAME=SQL_SERVERNAME
export DATABASE=SQL_DATABASE
export SQLUSER=SQL_USER
export SQLPASWORD='SQL_PASWORD'
```
Packa upp .zip-filen genom att ange kommandot nedan:
```
unzip $FILENAME.zip
```
Skapa en katalog på HDInsight Storage och kopiera sedan .csv-filen till katalogen genom att ange kommandot nedan:
```
hdfs dfs -mkdir -p /tutorials/flightdelays/data
hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
```

Transformera data med en Hive-fråga

Det finns många sätt att köra ett Hive-jobb på ett HDInsight-kluster. I det här avsnittet använder du Beeline för att köra ett Hive-jobb. Information om andra metoder för att köra ett Hive-jobb finns i avsnittet om att använda Apache Hive i HDInsight.

Som en del av Hive-jobbet importerar du data från .csv-filen till en Hive-tabell med namnet Delays (Fördröjningar).

Från SSH-prompten 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
```

Använd följande text som filens innehåll:

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;

Spara filen genom att trycka på Ctrl + X, sedan y och sedan ange.
Om du vill starta Hive och köra filen flightdelays.hql använder du följande kommando:
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
```
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'
```
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 /tutorials/flightdelays/output. Senare läser Sqoop data från den här platsen och exporterar dem till Azure SQL Database.
Om du vill avsluta Beeline skriver du !quit vid uppmaningen.

Skapa en SQL-databastabell

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.

Om du vill installera FreeTDS använder du följande kommando från den öppna SSH-anslutningen till klustret:
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```

När installationen är klar använder du följande kommando för att ansluta till SQL Database:

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

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

Vid uppmaningen 1> anger du följande rader:

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

När instruktionen GO har angivits värderas de föregående instruktionerna. Den här instruktionen skapar en tabell med namnet fördröjningar med ett grupperat index.

Använd följande fråga för att verifiera 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

Skriv exit vid uppmaningen 1> för att avsluta tsql-verktyget.

Exportera data till SQL Database med Apache Sqoop

I föregående avsnitt kopierade du omvandlade data på /tutorials/flightdelays/output. I det här avsnittet använder du Sqoop för att exportera data från /tutorials/flightdelays/output till den tabell som du skapade i Azure SQL Database.

Kontrollera att Sqoop kan se din SQL-databas genom att ange kommandot nedan:
```
sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
```
Det här kommandot returnerar en lista över databaser, inklusive databasen där du skapade delays tabellen tidigare.
Exportera data från /tutorials/flightdelays/output till tabellen delays genom att ange kommandot nedan:
```
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 ansluter till databasen som innehåller delays tabellen och exporterar data från /tutorials/flightdelays/output katalogen till delays tabellen.
När sqoop-kommandot har slutförts använder du tsql-verktyget för att ansluta till databasen genom att ange kommandot nedan:
```
TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
```
Använd följande instruktioner för att verifiera att data exporterades till fördröjningstabellen:
```
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.

Skriv exit för att avsluta tsql-verktyget.

Rensa resurser

När du har slutfört vägledningen kanske du vill ta bort klustret. Med HDInsight lagras dina data i Azure Storage, så att du på ett säkert sätt kan ta bort ett kluster när de inte används. Du debiteras också för ett HDInsight-kluster, även om det inte används. Eftersom avgifterna för klustret är många gånger högre än avgifterna för lagring är det ekonomiskt meningsfullt att ta bort kluster när de inte används.

Information om hur du tar bort ett kluster finns i Ta bort ett HDInsight-kluster med webbläsaren, PowerShell eller Azure CLI.

Nästa steg

I den här självstudien tog du en RÅDATA-datafil, importerade den till en HDInsight-klusterlagring och omvandlade sedan data med hjälp av Interaktiv fråga i Azure HDInsight. Gå vidare till nästa självstudie för att lära dig mer om Apache Hive Warehouse Connector.

Integrera Apache Spark och Apache Hive med Hive Warehouse Connector