教學課程：使用 Azure HDInsight 上的互動式查詢來擷取、轉換和載入資料

發行項
05/10/2024

在本教學課程中，您會下載可公開取得之航班資料的原始 CSV 資料檔案。將其匯入 HDInsight 叢集儲存體，然後在 Azure HDInsight 中使用互動式查詢來轉換資料。資料轉換後，您會使用 Apache Sqoop 將該資料載入 Azure SQL Database 中。

本教學課程涵蓋下列工作：

下載範例航班資料
將資料上傳至 HDInsight 叢集
使用互動式查詢轉換資料
在 Azure SQL Database 的資料庫中建立資料表
使用 Sqoop 將資料匯出至 Azure SQL Database 中的資料庫

必要條件

HDInsight 上的互動式查詢叢集。請參閱使用 Azure 入口網站建立 Apache Hadoop 叢集，然後選取 [互動式查詢] 作為 [叢集類型]。
Azure SQL Database 中的資料庫。您會使用資料庫做為目的地資料存放區。如果您在 Azure SQL Database 中沒有資料庫，請參閱在 Azure 入口網站的 Azure SQL Database 中建立資料庫，按照步驟建立資料庫。
SSH 用戶端。如需詳細資訊，請參閱使用 SSH 連線至 HDInsight (Apache Hadoop)。

下載航班資料

瀏覽至創新技術研究管理部運輸統計處。

在此頁面上清除所有欄位，然後選取下列值：

名稱	值
篩選年份	2019
篩選期間	一月
欄位	`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`.

選取 [下載]。下載的 .zip 檔案會包含您選取的資料欄位。

將資料上傳至 HDInsight 叢集

有許多方法可將資料上傳至與 HDInsight 叢集相關聯的儲存體。在本節中，您會使用 scp 來上傳資料。若要了解其他上傳資料的方式，請參閱將資料上傳至 HDInsight。

將 .zip 檔案上傳到 HDInsight 叢集前端節點。將 FILENAME 取代為 .zip 檔案的名稱，並將 CLUSTERNAME 取代為 HDInsight 叢集的名稱，以編輯下列命令。然後，開啟命令提示字元，並將工作目錄設為檔案位置，然後輸入命令：
```
scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
```
輸入是或否，以在出現提示時繼續。在輸入文字時，視窗中不會顯示出來。
完成上傳之後，使用 SSH 連線至叢集。將 CLUSTERNAME 取代為 HDInsight 叢集的名稱，以編輯下列命令。輸入下列命令：
```
ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
```
在 SSH 連線建立後，請設定環境變數。請將 FILE_NAME、SQL_SERVERNAME、SQL_DATABASE、SQL_USER 和 SQL_PASWORD 取代為適當的值。然後輸入命令：
```
export FILENAME=FILE_NAME
export SQLSERVERNAME=SQL_SERVERNAME
export DATABASE=SQL_DATABASE
export SQLUSER=SQL_USER
export SQLPASWORD='SQL_PASWORD'
```
輸入以下命令將 .zip 檔案解壓縮：
```
unzip $FILENAME.zip
```

輸入下列命令以在 HDInsight 儲存體上建立目錄，然後將 .csv 檔案複製到該目錄︰

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

使用 Hive 查詢轉換資料

有許多方法可在 HDInsight 叢集上執行 Hive 作業。在本節中，您會使用 Beeline 來執行 Hive 作業。若想了解其他用來執行 Hive 作業的方法，請參閱使用 HDInsight 上的 Apache Hive。

在執行 Hive 作業的過程中，您會將 .csv 檔案中的資料匯入至名為 Delays 的 Hive 資料表。

從 HDInsight 叢集既有的 SSH 提示字元中，使用下列命令建立並編輯名為 flightdelays.hql 的新檔案：
```
nano flightdelays.hql
```

使用下列文字做為此檔案的內容：

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;

若要儲存檔案，請依序按 Ctrl + X、y 和 Enter 鍵。

若要啟動 Hive 並執行 flightdelays.hql 檔案，請使用下列命令：

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

在 flightdelays.hql 指令碼執行完畢之後，請使用下列命令來開啟互動式 Beeline 工作階段：
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
```
當您收到 jdbc:hive2://localhost:10001/> 提示字元時，請使用以下查詢從匯入的航班延誤資料中擷取資料：
```
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;
```
此查詢會擷取因氣候因素而延誤的城市清單，以及平均延誤時間，並會儲存到 /tutorials/flightdelays/output。稍後，Sqoop 會從此位置讀取該資料，並匯出到 Azure SQL Database。
若要結束 Beeline，請在提示字元中輸入 !quit 。

建立 SQL 資料庫資料表

連接至 SQL Database 並建立資料表的方法有很多種。下列步驟會從 HDInsight 叢集使用 FreeTDS 。

若要安裝 FreeTDS，請從開啟的叢集 SSH 連線使用下列命令：
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```

安裝完成後，請使用下列命令連線至 SQL Database：

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

您會收到如以下文字的輸出：

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

在 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

輸入 GO 陳述式後，將評估先前的陳述式。此陳述式會建立名為 delays 的資料表 (具有叢集索引)。

使用下列查詢來確認已建立資料表：

SELECT * FROM information_schema.tables
GO

輸出大致如下：

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
databaseName       dbo             delays        BASE TABLE

Enter exit at the 1> 以結束 tsql 公用程式。

使用 Apache Sqoop 將資料匯出至 SQL Database

在前幾節中，您在 /tutorials/flightdelays/output 上複製了已轉換的資料。在本節中，您會使用 Sqoop 將資料從 /tutorials/flightdelays/output 匯出至您在 Azure SQL Database 中建立的資料表。

輸入下列命令，以確認 Sqoop 看得見您的 SQL 資料庫：
```
sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
```
此命令會傳回一份資料庫清單，包含您稍早在其中建立 delays 資料表的資料庫。

輸入下列命令，將資料從 /tutorials/flightdelays/output 匯出至 delays 資料表：

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 會連線至包含 delays 資料表的資料庫，並將資料從 /tutorials/flightdelays/output 目錄匯出至 delays 資料表。

在 sqoop 命令完成後，輸入下列命令以使用 tsql 公用程式連線至資料庫：
```
TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
```
使用下列陳述式來確認資料已匯出到延遲資料表：
```
SELECT * FROM delays
GO
```
您應會看到資料表中的資料清單。此資料表包含城市名稱以及該城市的平均航班延誤時間。

輸入 exit 以結束 tsql 公用程式。

清除資源

完成本教學課程之後，您可以刪除叢集。利用 HDInsight，您的資料會儲存在 Azure 儲存體中，以便您在未使用叢集時安全地刪除該叢集。您也需支付 HDInsight 叢集的費用 (即使未使用該叢集)。由於叢集費用是儲存體費用的許多倍，所以刪除未使用的叢集符合經濟效益。

若要刪除叢集，請參閱使用您的瀏覽器、PowerShell 或 Azure CLI 刪除 HDInsight 叢集。

下一步

在本教學課程中，您已取用原始 CSV 資料檔、將其匯入 HDInsight 叢集儲存體中，然後使用 Azure HDInsight 上的互動式查詢轉換了資料。請前進到下一個教學課程，以了解 Apache Hive Warehouse Connector。

整合 Apache Spark 和 Apache Hive 與 Hive Warehouse Connector

共用方式為