共用方式為


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

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

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

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

必要條件

下載航班資料

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

  2. 在此頁面上清除所有欄位,然後選取下列值:

    名稱
    篩選年份 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.
  3. 選取 [下載]。 下載的 .zip 檔案會包含您選取的資料欄位。

將資料上傳至 HDInsight 叢集

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

  1. 將 .zip 檔案上傳到 HDInsight 叢集前端節點。 將 FILENAME 取代為 .zip 檔案的名稱,並將 CLUSTERNAME 取代為 HDInsight 叢集的名稱,以編輯下列命令。 然後,開啟命令提示字元,並將工作目錄設為檔案位置,然後輸入命令:

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

    輸入是或否,以在出現提示時繼續。 在輸入文字時,視窗中不會顯示出來。

  2. 完成上傳之後,使用 SSH 連線至叢集。 將 CLUSTERNAME 取代為 HDInsight 叢集的名稱,以編輯下列命令。 輸入下列命令:

    ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
    
  3. 在 SSH 連線建立後,請設定環境變數。 請將 FILE_NAMESQL_SERVERNAMESQL_DATABASESQL_USERSQL_PASWORD 取代為適當的值。 然後輸入命令:

    export FILENAME=FILE_NAME
    export SQLSERVERNAME=SQL_SERVERNAME
    export DATABASE=SQL_DATABASE
    export SQLUSER=SQL_USER
    export SQLPASWORD='SQL_PASWORD'
    
  4. 輸入以下命令將 .zip 檔案解壓縮:

    unzip $FILENAME.zip
    
  5. 輸入下列命令以在 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 資料表。

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

    nano flightdelays.hql
    
  2. 使用下列文字做為此檔案的內容:

    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. 若要儲存檔案,請依序按 Ctrl + Xy 和 Enter 鍵。

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

    beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
    
  5. flightdelays.hql 指令碼執行完畢之後,請使用下列命令來開啟互動式 Beeline 工作階段:

    beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
    
  6. 當您收到 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。

  7. 若要結束 Beeline,請在提示字元中輸入 !quit

建立 SQL 資料庫資料表

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

  1. 若要安裝 FreeTDS,請從開啟的叢集 SSH 連線使用下列命令:

    sudo apt-get --assume-yes install freetds-dev freetds-bin
    
  2. 安裝完成後,請使用下列命令連線至 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>
    
  3. 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
    
  4. Enter exit at the 1> 以結束 tsql 公用程式。

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

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

  1. 輸入下列命令,以確認 Sqoop 看得見您的 SQL 資料庫:

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

    此命令會傳回一份資料庫清單,包含您稍早在其中建立 delays 資料表的資料庫。

  2. 輸入下列命令,將資料從 /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 資料表。

  3. 在 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。