Kurz: Extrakce, transformace a načítání dat pomocí Azure HDInsight

V tomto kurzu provedete operaci ETL: extrakci, transformaci a načtení dat. Vezmete nezpracovaný datový soubor CSV, naimportujete ho do clusteru Azure HDInsight, transformujete ho pomocí Apache Hive a načtete ho do Azure SQL Database pomocí Apache Sqoopu.

V tomto kurzu se naučíte:

Pokud předplatné Azure ještě nemáte, napřed si vytvořte bezplatný účet.

Požadavky

• Účet úložiště Azure Data Lake Storage Gen2, který je nakonfigurovaný pro HDInsight

  Viz Použití Azure Data Lake Storage Gen2 s Azure HDInsight clustery.

• Cluster Hadoop se systémem Linux ve službě HDInsight

  Viz Rychlý start: Začínáme s Apache Hadoop a Apache Hive v Azure HDInsight s využitím Azure Portal.

• Azure SQL Database: Jako Azure SQL Database úložiště dat se používá úložiště dat. Pokud ve své databázi nemáte databázi, SQL Database tématu Vytvoření databáze v Azure SQL Database v Azure Portal.

• Azure CLI: Pokud jste si Ještě nenainstalujete Azure CLI, projděte si informace v tématu Instalace Azure CLI.

• Klient Secure Shell (SSH): Další informace najdete v tématu Připojení k HDInsight (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 vyberte následující hodnoty:

   Name	Hodnota
   Filter Year (Filtr roku)	2013
   Filter Period (Filtr období)	January (Leden)
   Pole	Year, FlightDate, Reporting_Airline, IATA_CODE_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.

   Zrušte zaškrtnutí všech ostatních polí.

3. Vyberte Stáhnout. Získáte soubor .zip s vybranými datovými poli.

Extrakce a nahrání dat

V této části nahrajete data do clusteru HDInsight a pak tato data zkopírujete do svého účtu Data Lake Storage Gen2.

1. Otevřete příkazový řádek a pomocí následujícího příkazu Scp (Secure Copy) nahrajte soubor .zip do uzlu hlavní clusteru HDInsight:

   scp <file-name>.zip <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net:<file-name.zip>
   

   • Zástupný <file-name> text nahraďte názvem souboru .zip souboru.
   • Zástupný text <ssh-user-name> nahraďte přihlašovacím údajem SSH pro cluster HDInsight.
   • Zástupný <cluster-name> text nahraďte názvem clusteru HDInsight.

   Pokud k ověření přihlášení SSH používáte heslo, zobrazí se výzva k zadání hesla.

   Pokud používáte veřejný klíč, budete pravděpodobně muset použít parametr -i k zadání cesty k odpovídajícímu privátnímu klíči. Například, scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

2. Po dokončení nahrávání se ke clusteru připojte pomocí SSH. Na příkazovém řádku zadejte následující příkaz:

   ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
   

3. Pomocí následujícího příkazu rozbalte soubor .zip:

   unzip <file-name>.zip
   

   Příkaz extrahuje .csv soubor.

4. Pomocí následujícího příkazu vytvořte kontejner Data Lake Storage Gen2.

   hadoop fs -D "fs.azure.createRemoteFileSystemDuringInitialization=true" -ls abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/
   

   Zástupný text <container-name> nahraďte názvem, který chcete kontejneru dát.

   Zástupný <storage-account-name> text nahraďte názvem vašeho účtu úložiště.

5. Pomocí následujícího příkazu vytvořte adresář.

   hdfs dfs -mkdir -p abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data
   

6. Pomocí následujícího příkazu zkopírujte soubor.csv do adresáře :

   hdfs dfs -put "<file-name>.csv" abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data/
   

   Pokud název souboru obsahuje mezery nebo speciální znaky, použijte uvozovky kolem názvu souboru.

Transformace dat

V této části použijete Beeline ke spuštění Apache Hive úlohy.

V rámci úlohy Apache Hive importujete data ze souboru .csv do Apache Hive s názvem delays.

1. Z příkazového řádku SSH, který již 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. Upravte následující text tak, že <container-name> <storage-account-name> zástupné symboly a nahradíte názvem vašeho kontejneru a účtu úložiště. Potom text zkopírujte a vložte do konzoly nano stisknutím klávesy SHIFT a kliknutím pravým tlačítkem myši.

   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 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/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
   LOCATION 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/processed'
   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. Uložte soubor stisknutím kombinace kláves CTRL+X a po zobrazení Y výzvy zadejte .

4. Ke spuštění Hivu a flightdelays.hql spuštění souboru použijte následující příkaz:

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

5. Po flightdelays.hql dokončení skriptu 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í abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/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

Pro tuto operaci budete potřebovat SQL Database serveru. Pomocí těchto kroků vyhledejte název serveru.

1. Přejděte na Azure Portal.

2. Vyberte SQL Databáze.

3. Vyfiltrujte název databáze, kterou chcete použít. Název serveru se zobrazí ve sloupci Název serveru.

4. Vyfiltrujte název databáze, kterou chcete použít. Název serveru se zobrazí ve sloupci Název serveru.

   

   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.

5. Pokud chcete nainstalovat FreeTDS, použijte následující příkaz z připojení SSH ke clusteru:

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

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

   TDSVER=8.0 tsql -H '<server-name>.database.windows.net' -U '<admin-login>' -p 1433 -D '<database-name>'
   

   • Zástupný symbol <server-name> nahraďte názvem logického SQL serveru.

   • Zástupný symbol <admin-login> nahraďte přihlašovacím údajem správce pro SQL Database.

   • Zástupný <database-name> text nahraďte názvem databáze.

   Po zobrazení výzvy zadejte heslo pro přihlašovací SQL Database správce.

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

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

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

8. Po zadání příkazu GO se vyhodnotí předchozí příkazy.

   Dotaz vytvoří tabulku s názvem delays, která má clusterovaný index.

9. Pomocí následujícího dotazu ověřte, že se tabulka vytvořila:

   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
   

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

Export a načtení dat

V předchozích částech jste zkopíroval transformovaná data v umístění abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output . V této části použijete Sqoop k exportu dat z do abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output tabulky, kterou jste vytvořili v Azure SQL Database.

1. Pomocí následujícího příkazu ověřte, že má Sqoop vhled do vaší databáze SQL:

   sqoop list-databases --connect jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433 --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD>
   

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

2. Pomocí následujícího příkazu exportujte data z tabulky hivesampletable do tabulky delays:

   sqoop export --connect 'jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433;database=<DATABASE_NAME>' --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD> --table 'delays' --export-dir 'abfs://<container-name>@.dfs.core.windows.net/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
   

   Sqoop se připojí k databázi, která obsahuje tabulku delays, a exportuje data z adresáře /tutorials/flightdelays/output do tabulky delays.

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

   TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>
   

4. Pomocí následujících příkazů ověřte, že se data exportují 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.

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

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

Všechny prostředky použité v tomto kurzu už existují. Není nutné nic vyčistit.

Další kroky

Další informace o způsobech práce s daty ve službě HDInsight najdete v následujícím článku: