Kurz: Extrakce, transformace a načítání dat pomocí Interactive Query ve službě Azure HDInsight

V tomto kurzu si stáhnete nezpracovaný datový soubor CSV s veřejně dostupnými daty letů. Naimportujte ho do úložiště clusteru HDInsight a potom transformujte data pomocí Interactive Query v Azure HDInsight. Jakmile se data transformují, načtete je do databáze v Azure SQL Database pomocí Apache Sqoopu.

Tento kurz se zabývá následujícími úkony:

• Stažení ukázkových údajů o letech
• Nahrání dat do clusteru HDInsight
• Transformace dat pomocí Interactive Query
• Vytvoření tabulky v databázi v Azure SQL Database
• Použití Sqoopu k exportu dat do databáze v Azure SQL Database

Požadavky

• Cluster Interactive Query ve službě HDInsight. Viz Vytváření clusterů Apache Hadoop pomocí Azure Portal a jako typ clusteru vyberte Interactive Query.

• Databáze v Azure SQL Database. Databázi použijete jako cílové úložiště dat. Pokud ve službě Azure SQL Database nemáte databázi, přečtěte si téma Vytvoření databáze v Azure SQL Database v Azure Portal.

• Klient SSH. Další informace najdete v tématu Připojení ke službě HDInsight (Apache Hadoop) pomocí SSH.

Stažení letových údajů

1. Přejděte na web Research and Innovative Technology Administration, Bureau of Transportation Statistics.

2. Na stránce vymažte všechna pole a pak vyberte následující hodnoty:

   Name	Hodnota
   Filter Year (Filtr roku)	2019
   Filter Period (Filtr období)	January (Leden)
   Pole	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. Vyberte Stáhnout. Stáhne se soubor .zip s vybranými datovými poli.

Nahrání dat do clusteru HDInsight

Do úložiště přidruženého ke clusteru HDInsight můžete data nahrát mnoha způsoby. V této části k nahrání dat použijete scp. Informace o dalších způsobech nahrání dat najdete v tématu Nahrání dat do služby HDInsight.

1. Nahrajte soubor .zip do hlavního uzlu clusteru HDInsight. Upravte následující příkaz tak, že nahradíte FILENAME názvem souboru .zip a CLUSTERNAME názvem clusteru HDInsight. Pak otevřete příkazový řádek, nastavte pracovní adresář na umístění souboru a zadejte příkaz:

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

   Po zobrazení výzvy pokračujte zadáním ano nebo ne. Text se při psaní v okně nezobrazuje.

2. Po dokončení nahrávání se ke clusteru připojte pomocí SSH. Upravte následující příkaz nahrazením CLUSTERNAME názvem clusteru HDInsight. Potom zadejte následující příkaz:

   ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
   

3. Po navázání připojení SSH nastavte proměnnou prostředí. Nahraďte FILE_NAME, SQL_DATABASESQL_SERVERNAME, SQL_USERa SQL_PASWORD příslušnými hodnotami. Pak zadejte příkaz:

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

4. Rozbalte soubor .zip zadáním následujícího příkazu:

   unzip $FILENAME.zip
   

5. Vytvořte adresář v úložišti HDInsight a potom zkopírujte soubor .csv do adresáře zadáním následujícího příkazu:

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

Transformace dat pomocí dotazu Hive

Úlohu Hive můžete v clusteru HDInsight spustit různými způsoby. V této části použijete Beeline ke spuštění úlohy Hive. Informace o dalších metodách spuštění úlohy Hive najdete v tématu Použití Apache Hivu ve službě HDInsight.

V rámci úlohy Hive provedete import dat ze souboru .csv do tabulky Hive s názvem Delays (Zpoždění).

1. Na příkazovém řádku SSH, který už máte pro cluster HDInsight, pomocí následujícího příkazu vytvořte a upravte nový soubor s názvem flightdelays.hql:

   nano flightdelays.hql
   

2. Jako obsah souboru použijte následující text:

   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. Pokud chcete soubor uložit, stiskněte Ctrl + X, pak y a pak zadejte.

4. Spusťte Hive a soubor flightdelays.hql pomocí následujícího příkazu:

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

5. Po dokončení skriptu flightdelays.hql pomocí následujícího příkazu otevřete interaktivní relaci Beeline:

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

6. Po zobrazení příkazového řádku jdbc:hive2://localhost:10001/> pomocí následujícího dotazu načtěte data z importovaných dat o zpožděných letech:

   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;
   

   Tento dotaz načte seznam měst, ve kterých došlo ke zpožděním kvůli nepřízni počasí, společně s průměrnou délkou zpoždění a uloží ho do umístění /tutorials/flightdelays/output. Později z tohoto umístění data načte Sqoop a exportuje je do služby Azure SQL Database.

7. Beeline ukončíte zadáním !quit na příkazovém řádku.

Vytvoření tabulky databáze SQL

Existuje mnoho způsobů, jak se připojit ke službě SQL Database a vytvořit tabulku. V následujících krocích se používá FreeTDS z clusteru HDInsight.

1. K instalaci FreeTDS použijte následující příkaz z otevřeného připojení SSH ke clusteru:

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

2. Po dokončení instalace se pomocí následujícího příkazu připojte k SQL Database:

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

   Zobrazí se výstup podobný následujícímu textu:

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

3. Na příkazovém řádku 1> zadejte následující řádky:

   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
   

   Po zadání příkazu GO se vyhodnotí předchozí příkazy. Tento příkaz vytvoří tabulku s názvem zpoždění s clusterovaným indexem.

   K ověření vytvoření tabulky použijte následující dotaz:

   SELECT * FROM information_schema.tables
   GO
   

   Výstup se bude podobat následujícímu:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

4. Zadáním exit na příkazovém řádku 1> ukončete nástroj tsql.

Export dat do SQL Database pomocí Apache Sqoopu

V předchozích částech jste zkopírovali transformovaná data do umístění /tutorials/flightdelays/output. V této části použijete Sqoop k exportu dat z /tutorials/flightdelays/output do tabulky, kterou jste vytvořili v Azure SQL Database.

1. Zadáním následujícího příkazu ověřte, že Sqoop vidí vaši databázi SQL:

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

   Tento příkaz vrátí seznam databází, včetně databáze, ve které jste tabulku vytvořili delays dříve.

2. Exportujte data z /tutorials/flightdelays/output do delays tabulky zadáním následujícího příkazu:

   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 se připojí k databázi, která obsahuje delays tabulku, a exportuje data z /tutorials/flightdelays/output adresáře do delays tabulky.

3. Po dokončení příkazu sqoop se pomocí nástroje tsql připojte k databázi zadáním následujícího příkazu:

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

   Pomocí následujících příkazů ověřte, že se data exportovala do tabulky delays:

   SELECT * FROM delays
   GO
   

   Měl by se zobrazit výpis dat v tabulce. Tabulka obsahuje název města a průměrnou délku zpoždění letu pro příslušné město.

   Zadáním exit ukončete nástroj tsql.

Vyčištění prostředků

Po dokončení kurzu můžete cluster odstranit. Ve službě HDInsight jsou vaše data uložená ve službě Azure Storage, takže cluster můžete bezpečně odstranit, když se nepoužívá. Účtují se vám také poplatky za cluster HDInsight, i když se nepoužívá. Vzhledem k tomu, že poplatky za cluster jsou mnohonásobně vyšší než poplatky za úložiště, dává smysl clustery odstranit, když se nepoužívají.

Pokud chcete odstranit cluster, přečtěte si téma Odstranění clusteru HDInsight pomocí prohlížeče, PowerShellu nebo Azure CLI.

Další kroky

V tomto kurzu jste vzali nezpracovaný datový soubor CSV, naimportovali ho do úložiště clusteru HDInsight a pak jste data transformovali pomocí Interactive Query ve službě Azure HDInsight. V dalším kurzu se dozvíte o konektoru Apache Hive Warehouse.

Integrace Apache Sparku a Apache Hivu s konektorem Hive Warehouse