チュートリアル:Azure HDInsight で対話型クエリを使用してデータの抽出、変換、読み込みを行うTutorial: Extract, transform, and load data using Interactive Query in Azure HDInsight

このチュートリアルでは、一般公開されているフライト データの生の CSV データ ファイルを取得して HDInsight クラスター ストレージにインポートした後、Azure HDInsight で対話型クエリを使用してデータを変換します。In this tutorial, you take a raw CSV data file of publicly available flight data, import it into HDInsight cluster storage, and then transform the data using Interactive Query in Azure HDInsight. データを変換したら、Apache Sqoop を使用して Azure SQL データベースにデータを読み込みます。Once the data is transformed, you load that data into an Azure SQL database using Apache Sqoop.

このチュートリアルに含まれるタスクは次のとおりです。This tutorial covers the following tasks:

  • サンプルのフライト データをダウンロードするDownload the sample flight data
  • HDInsight クラスターにデータをアップロードするUpload data to an HDInsight cluster
  • 対話型クエリを使用してデータを変換するTransform the data using Interactive Query
  • Azure SQL データベースでテーブルを作成するCreate a table in an Azure SQL database
  • Sqoop を使用して Azure SQL データベースにデータをエクスポートするUse Sqoop to export data to an Azure SQL database

前提条件Prerequisites

フライト データのダウンロードDownload the flight data

  1. 米国運輸省研究・革新技術庁/運輸統計局のページに移動します。Browse to Research and Innovative Technology Administration, Bureau of Transportation Statistics.

  2. ページで、すべてのフィールドをクリアしてから、次の値を選択します。On the page, clear all fields, and then select the following values:

    名前Name Value
    Filter YearFilter Year 20192019
    Filter PeriodFilter Period JanuaryJanuary
    フィールドFields 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。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. [Download] を選択します。Select Download. 選択したデータ フィールドを含む .zip ファイルがダウンロードされます。You get a .zip file with the data fields you selected.

HDInsight クラスターにデータをアップロードするUpload data to an HDInsight cluster

HDInsight クラスターに関連付けられたストレージにデータをアップロードする方法はたくさんあります。There are many ways to upload data to the storage associated with an HDInsight cluster. このセクションでは、scp を使用してデータをアップロードします。In this section, you use scp to upload data. データをアップロードする他の方法については、HDInsight へのデータのアップロードに関する記事をご覧ください。To learn about other ways to upload data, see Upload data to HDInsight.

  1. .zip ファイルを HDInsight クラスターのヘッド ノードにアップロードします。Upload the .zip file to the HDInsight cluster head node. FILENAME を .zip ファイルの名前に、CLUSTERNAME を HDInsight クラスターの名前に置き換えて、以下のコマンドを編集します。Edit the command below by replacing FILENAME with the name of the .zip file, and CLUSTERNAME with the name of the HDInsight cluster. その後、コマンド プロンプトを開き、ファイルの場所に作業ディレクトリを設定してから、コマンドを入力します。Then open a command prompt, set your working directory to the file location, and then enter the command.

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

    「yes」または「no」を入力して続行するよう求められた場合は、コマンド プロンプトから「yes」と入力して Enter キーを押してください。If you are asked to enter yes or no to proceed, type yes at the command prompt and press enter. 入力するとき、ウィンドウにテキストは表示されません。The text is not visible in the window as you type.

  2. アップロードが完了したら、SSH を使用してクラスターに接続します。After the upload has finished, connect to the cluster by using SSH. CLUSTERNAME を HDInsight クラスターの名前に置き換えて、以下のコマンドを編集します。Edit the command below by replacing CLUSTERNAME with the name of the HDInsight cluster. 次のコマンドを入力します。Then enter the following command:

    ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
    
  3. SSH 接続が確立されたら、環境変数を設定します。Set up environment variable once an SSH connection has been established. FILE_NAMESQL_SERVERNAMESQL_DATABASESQL_USERSQL_PASWORD を適切な値に置き換えます。Replace FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USER, and SQL_PASWORD with the appropriate values. その後、コマンドを入力します。Then enter the command:

    export FILENAME=FILE_NAME
    export SQLSERVERNAME=SQL_SERVERNAME
    export DATABASE=SQL_DATABASE
    export SQLUSER=SQL_USER
    export SQLPASWORD='SQL_PASWORD'
    
  4. 以下のコマンドを入力して、.zip ファイルを解凍します。Unzip the .zip file by entering the command below:

    unzip $FILENAME.zip
    
  5. 次のコマンドを入力し、HDInsight ストレージにディレクトリを作成してから、そのディレクトリに .csv ファイルをコピーします。Create a directory on HDInsight storage, and then copy the .csv file to the directory by entering the command below:

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

Hive クエリを使用したデータの変換Transform data using a Hive query

HDInsight クラスター上で Hive ジョブを実行する方法はたくさんあります。There are many ways to run a Hive job on an HDInsight cluster. このセクションでは、Beeline を使用して Hive ジョブを実行します。In this section, you use Beeline to run a Hive job. Hive ジョブを実行するその他の方法については、HDInsight での Apache Hive の使用に関するページを参照してください。For information on other methods of running a Hive job, see Use Apache Hive on HDInsight.

Hive ジョブの一環として、.csv ファイルから Delays という名前の Hive テーブルにデータをインポートします。As part of the Hive job, you import the data from the .csv file into a Hive table named Delays.

  1. HDInsight クラスター用に既に開いている SSH プロンプトから、次のコマンドを使用して flightdelays.hql という名前の新しいファイルを作成して編集します。From the SSH prompt that you already have for the HDInsight cluster, use the following command to create, and edit a new file named flightdelays.hql:

    nano flightdelays.hql
    
  2. このファイルの内容として、次のテキストを使用します。Use the following text as the contents of this file:

    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 + X キー、Y キー、Enter キーの順に押します。To save the file, press Ctrl + X, then y, then enter.

  4. Hive を起動し、flightdelays.hql ファイルを実行するには、次のコマンドを使用します。To start Hive and run the flightdelays.hql file, use the following command:

    beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
    
  5. flightdelays.hql スクリプトの実行が完了したら、次のコマンドを使用して対話型 Beeline セッションを開きます。After the flightdelays.hql script finishes running, use the following command to open an interactive Beeline session:

    beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
    
  6. jdbc:hive2://localhost:10001/> プロンプトが表示されたら、次のクエリを使用してインポートされたフライト遅延データからデータを取得します。When you receive the jdbc:hive2://localhost:10001/> prompt, use the following query to retrieve data from the imported flight delay data:

    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 に保存されます。This query retrieves a list of cities that experienced weather delays, along with the average delay time, and saves it to /tutorials/flightdelays/output. その後、Sqoop がこの場所からデータを読み取り、Azure SQL Database にエクスポートします。Later, Sqoop reads the data from this location and exports it to Azure SQL Database.

  7. Beeline を終了するには、プロンプトで「 !quit 」と入力します。To exit Beeline, enter !quit at the prompt.

SQL データベース テーブルの作成Create a SQL database table

SQL Database に接続してテーブルを作成するには、多くの方法があります。There are many ways to connect to SQL Database and create a table. 次の手順では、HDInsight クラスターから FreeTDS を使用します。The following steps use FreeTDS from the HDInsight cluster.

  1. FreeTDS をインストールするには、クラスターへの開いている SSH 接続から、次のコマンドを使用します。To install FreeTDS, use the following command from the open SSH connection to the cluster:

    sudo apt-get --assume-yes install freetds-dev freetds-bin
    
  2. インストールが完了したら、次のコマンドを使用して SQL Database サーバーに接続します。After the installation finishes, use the following command to connect to the SQL Database server.

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

    次のテキストのような出力が返されます。You receive output similar to the following text:

    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> プロンプトで、以下の行を入力します。At the 1> prompt, enter the following lines:

    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 ステートメントを入力すると、前のステートメントが評価されます。When the GO statement is entered, the previous statements are evaluated. このステートメントにより、クラスター化インデックス付きの、delays という名前のテーブルが作成されます。This statement creates a table named delays, with a clustered index.

    次のクエリを使用して、テーブルが作成されたことを確認します。Use the following query to verify that the table has been created:

    SELECT * FROM information_schema.tables
    GO
    

    出力は次のテキストのようになります。The output is similar to the following text:

    TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
    databaseName       dbo             delays        BASE TABLE
    
  4. Enter exit at the 1>」と入力して、tsql ユーティリティを終了します。Enter exit at the 1> prompt to exit the tsql utility.

Apache Sqoop を使用して SQL データベースにデータをエクスポートするExport data to SQL database using Apache Sqoop

前のセクションで、変換済みデータを /tutorials/flightdelays/output にコピーしました。In the previous sections, you copied the transformed data at /tutorials/flightdelays/output. このセクションでは、Sqoop を使用して、/tutorials/flightdelays/output のデータを、Azure SQL データベースに作成したテーブルにエクスポートします。In this section, you use Sqoop to export the data from /tutorials/flightdelays/output to the table you created in Azure SQL database.

  1. 以下のコマンドを入力して、Sqoop で SQL データベースを認識できることを確認します。Verify that Sqoop can see your SQL database by entering the command below:

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

    このコマンドにより、先ほど delays テーブルを作成したデータベースを含む、データベースのリストが返されます。This command returns a list of databases, including the database in which you created the delays table earlier.

  2. 以下のコマンドを入力して、/tutorials/flightdelays/output から delays テーブルにデータをエクスポートします。Export data from /tutorials/flightdelays/output to the delays table by entering the command below:

    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 connects to the database that contains the delays table, and exports data from the /tutorials/flightdelays/output directory to the delays table.

  3. sqoop コマンドが完了した後、以下のコマンドを入力し、tsql ユーティリティを使ってデータベースに接続します。After the sqoop command finishes, use the tsql utility to connect to the database by entering the command below:

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

    次のステートメントを使って、データが delays テーブルにエクスポートされたことを確認します。Use the following statements to verify that the data was exported to the delays table:

    SELECT * FROM delays
    GO
    

    テーブル内のデータの一覧が表示されます。You should see a listing of data in the table. テーブルには、都市の名前と、その都市のフライトの平均遅延時間が含まれます。The table includes the city name and the average flight delay time for that city.

    exit 」と入力して、tsql ユーティリティを終了します。Type exit to exit the tsql utility.

リソースのクリーンアップClean up resources

チュートリアルを完了したら、必要に応じてクラスターを削除できます。After you complete the tutorial, you may want to delete the cluster. HDInsight を使用すると、データは Azure Storage に格納されるため、クラスターは、使用されていない場合に安全に削除できます。With HDInsight, your data is stored in Azure Storage, so you can safely delete a cluster when it is not in use. また、HDInsight クラスターは、使用していない場合でも課金されます。You are also charged for an HDInsight cluster, even when it is not in use. クラスターの料金は Storage の料金の何倍にもなるため、クラスターを使用しない場合は削除するのが経済的にも合理的です。Since the charges for the cluster are many times more than the charges for storage, it makes economic sense to delete clusters when they are not in use.

クラスターを削除するには、「ブラウザー、PowerShell、または Azure CLI を使用して HDInsight クラスターを削除する」を参照してください。To delete a cluster, see Delete an HDInsight cluster using your browser, PowerShell, or the Azure CLI.

次の手順Next steps

このチュートリアルでは、生の CSV データ ファイルを取得し、それを HDInsight クラスター ストレージにインポートしてから、Azure HDInsight で対話型クエリを使用してデータを変換しました。In this tutorial, you took a raw CSV data file, imported it into an HDInsight cluster storage, and then transformed the data using Interactive Query in Azure HDInsight. 次のチュートリアルに進んで、Apache Hive Warehouse Connector について確認してください。Advance to the next tutorial to learn about the Apache Hive Warehouse Connector.