チュートリアル:Azure HDInsight の Apache Hive を使用したデータの抽出、変換、および読み込みTutorial: Extract, transform, and load data by using Apache Hive on Azure HDInsight

このチュートリアルでは、ETL (データの抽出、変換、読み込み) 操作を実行します。In this tutorial, you perform an ETL operation: extract, transform, and load data. 生の CSV データ ファイルを取得して Azure HDInsight クラスターにインポートした後、Apache Hive を使用して変換し、Apache Sqoop を使用して Azure SQL データベースに読み込みます。You take a raw CSV data file, import it into an Azure HDInsight cluster, transform it with Apache Hive, and load it into an Azure SQL database with Apache Sqoop.

このチュートリアルでは、以下の内容を学習します。In this tutorial, you learn how to:

  • データを抽出し、HDInsight クラスターにアップロードする。Extract and upload the data to an HDInsight cluster.
  • Apache Hive を使用してデータを変換する。Transform the data by using Apache Hive.
  • Sqoop を使用して Azure SQL データベースにデータを読み込む。Load the data to an Azure SQL database by using Sqoop.

Azure サブスクリプションをお持ちでない場合は、開始する前に無料アカウントを作成してください。If you don't have an Azure subscription, create a free account before you begin.

前提条件Prerequisites

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

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

  2. このページで、次の値を選択します。On the page, select the following values:

    NameName Value
    Filter YearFilter Year 20132013
    Filter PeriodFilter Period JanuaryJanuary
    フィールドFields 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。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.

    その他のフィールドはすべてクリアします。Clear all other fields.

  3. [Download] を選択します。Select Download. 選択したデータ フィールドを含む .zip ファイルがダウンロードされます。You get a .zip file with the data fields you selected.

データの抽出とアップロードExtract and upload the data

このセクションでは、HDInsight クラスターにデータをアップロードしてから、そのデータを自分の Data Lake Storage Gen2 アカウントにコピーします。In this section, you'll upload data to your HDInsight cluster and then copy that data to your Data Lake Storage Gen2 account.

  1. コマンド プロンプトを開き、次の Secure Copy (Scp) コマンドを使用して HDInsight クラスターのヘッド ノードに .ZIP ファイルをアップロードします。Open a command prompt and use the following Secure Copy (Scp) command to upload the .zip file to the HDInsight cluster head node:

    scp <file-name>.zip <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net:<file-name.zip>
    
    • <file-name> プレースホルダーを .ZIP ファイルの名前に置き換えます。Replace the <file-name> placeholder with the name of the .zip file.
    • <ssh-user-name> プレースホルダーを HDInsight クラスターの SSH ログインに置き換えます。Replace the <ssh-user-name> placeholder with the SSH login for the HDInsight cluster.
    • <cluster-name> プレースホルダーを HDInsight クラスターの名前に置き換えます。Replace the <cluster-name> placeholder with the name of the HDInsight cluster.

    パスワードを使用して SSH ログインを認証する場合は、パスワードを入力するよう求められます。If you use a password to authenticate your SSH login, you're prompted for the password.

    公開キーを使用している場合は、-i パラメーターを使用して、対応する秘密キーへのパスを指定することが必要な場合があります。If you use a public key, you might need to use the -i parameter and specify the path to the matching private key. たとえば、「 scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net: 」のように入力します。For example, scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

  2. アップロードが完了したら、SSH を使用してクラスターに接続します。After the upload has finished, connect to the cluster by using SSH. コマンド プロンプトで次のコマンドを入力します。On the command prompt, enter the following command:

    ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
    
  3. 次のコマンドを使用して .zip ファイルを解凍します。Use the following command to unzip the .zip file:

    unzip <file-name>.zip
    

    このコマンドにより .csv ファイルが抽出されます。The command extracts a .csv file.

  4. 次のコマンドを使用して、Data Lake Storage Gen2 ファイル システムを作成します。Use the following command to create the Data Lake Storage Gen2 file system.

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

    <file-system-name> プレースホルダーを、ファイル システムに付ける名前に置き換えます。Replace the <file-system-name> placeholder with the name that you want to give your file system.

    <storage-account-name> プレースホルダーは、実際のストレージ アカウントの名前に置き換えます。Replace the <storage-account-name> placeholder with the name of your storage account.

  5. 次のコマンドを使用して、ディレクトリを作成します。Use the following command to create a directory.

    hdfs dfs -mkdir -p abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data
    
  6. 次のコマンドを使用して .csv ファイルをディレクトリにコピーします。Use the following command to copy the .csv file to the directory:

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

    ファイル名にスペースや特殊文字が含まれる場合は、ファイル名を引用符で囲んでください。Use quotes around the file name if the file name contains spaces or special characters.

データの変換Transform the data

このセクションでは、Beeline を使用して Apache Hive ジョブを実行します。In this section, you use Beeline to run an Apache Hive job.

Apache Hive ジョブの一環として、.csv ファイルから delays という名前の Apache Hive テーブルにデータをインポートします。As part of the Apache Hive job, you import the data from the .csv file into an Apache 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. 次のテキストに変更を加えます。<file-system-name><storage-account-name> のプレースホルダーを実際のファイル システムとストレージ アカウントの名前に置き換えてください。Modify the following text by replace the <file-system-name> and <storage-account-name> placeholders with your file system and storage account name. マウスの右ボタンをクリックしたまま Shift キーを押して、そのテキストを nano コンソールにコピーして貼り付けます。Then copy and paste the text into the nano console by using pressing the SHIFT key along with the right-mouse click button.

    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://<file-system-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://<file-system-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. Ctrl キーを押しながら X キーを押し、確認を求められたら「Y」と入力してファイルを保存します。Save the file by using use CTRL+X and then type Y when prompted.

  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;
    

    このクエリにより、悪天候による遅延が発生した都市の一覧と平均遅延時間が取得され、abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output に保存されます。This query retrieves a list of cities that experienced weather delays, along with the average delay time, and saves it to abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/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 データベースのサーバー名が必要になります。You need the server name from your SQL database for this operation. サーバー名を確認するには、次の手順に従います。Complete these steps to find your server name.

  1. Azure ポータルにアクセスします。Go to the Azure portal.

  2. [SQL データベース] を選択します。Select SQL Databases.

  3. 使用するデータベースの名前でフィルター処理します。Filter on the name of the database that you choose to use. サーバー名は [サーバー名] 列に表示されます。The server name is listed in the Server name column.

  4. 使用するデータベースの名前でフィルター処理します。Filter on the name of the database that you want to use. サーバー名は [サーバー名] 列に表示されます。The server name is listed in the Server name column.

    Azure SQL サーバーの詳細を取得Get Azure SQL server details

    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.

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

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

    TDSVER=8.0 tsql -H '<server-name>.database.windows.net' -U '<admin-login>' -p 1433 -D '<database-name>'
    
    • <server-name> プレースホルダーを SQL Database のサーバー名に置き換えます。Replace the <server-name> placeholder with the SQL Database server name.

    • <admin-login> プレースホルダーを SQL Database の管理者ログインに置き換えます。Replace the <admin-login> placeholder with the admin login for SQL Database.

    • <database-name> プレースホルダーをデータベース名に置き換えます。Replace the <database-name> placeholder with the database name

    メッセージが表示されたら、SQL Database 管理者ログインのパスワードを入力します。When you're prompted, enter the password for the SQL Database admin login.

    次のテキストのような出力が返されます。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 sqooptest
    1>
    
  7. 1> プロンプトで、次のステートメントを入力します。At the 1> prompt, enter the following statements:

    CREATE TABLE [dbo].[delays](
    [OriginCityName] [nvarchar](50) NOT NULL,
    [WeatherDelay] float,
    CONSTRAINT [PK_delays] PRIMARY KEY CLUSTERED
    ([OriginCityName] ASC))
    GO
    
  8. GO ステートメントを入力すると、前のステートメントが評価されます。When the GO statement is entered, the previous statements are evaluated.

    このクエリにより、クラスター化インデックス付きの、delays という名前のテーブルが作成されます。The query creates a table named delays, which has a clustered index.

  9. 次のクエリを使用して、テーブルが作成されたことを確認します。Use the following query to verify that the table is 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
    
  10. Enter exit at the 1>」と入力して、tsql ユーティリティを終了します。Enter exit at the 1> prompt to exit the tsql utility.

データのエクスポートと読み込みExport and load the data

これまでのセクションで、変換済みデータを abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output という場所にコピーしました。In the previous sections, you copied the transformed data at the location abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. このセクションでは、Sqoop を使用して、abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output のデータを、Azure SQL データベースに作成したテーブルにエクスポートします。In this section, you use Sqoop to export the data from abfs://<file-system-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output to the table you created in the Azure SQL database.

  1. 次のコマンドを使用して、Sqoop が SQL データベースを認識できることを確認します。Use the following command to verify that Sqoop can see your SQL database:

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

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

  2. 次のコマンドを使って、hivesampletable テーブルから delays テーブルにデータをエクスポートします。Use the following command to export data from the hivesampletable table to the delays table:

    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://<file-system-name>@.dfs.core.windows.net/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:

    TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>
    
  4. 次のステートメントを使用して、データが 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.

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

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

このチュートリアルで使用されるすべてのリソースは、既存のものです。All resources used in this tutorial are preexisting. クリーンアップは必要ありません。No cleanup is necessary.

次の手順Next steps

HDInsight でのデータ操作の詳細については、次の記事を参照してください。To learn more ways to work with data in HDInsight, see the following article: