Integration Servicesを利用してHTMLからデータを抽出する [SSRS with Bing Maps #2]

少し間が空いてしまいました。
佐久間です。

「Reporting ServicesとBing MapsでWEB上のHTMLデータを可視化するレポートを作成する」

第2回目は前回作成したWorkテーブルにHTMLから抽出したデータを取り込む処理を行います。
(目次はこちら)
 

2. データを取得・変換する:Integration Services

今日は、SQL Server 2008 R2に付属のIntegration Services(以下SSIS)を使用してデータの抽出・変換・ロード(ETL)を行います。
ETL処理の全体像は下記の通りです。
2-1. 抽出(Extract) ←今回
 2-1-1 HTMLからデータを抽出してTSVを作成する
 2-1-2 ワークテーブルを空にする
 2-1-3 TSVからSQL Serverへデータをインポートする
 
2-2. 変換(Transform)
 2-2-1 都道府県IDを付与する
 2-2-2 最大震度IDを付与する
 2-2-3 座標データをGeography型に変換する
 
2-3. ロード(Load)
 2-3-1 インデックスを無効化する
 2-3-2 ワークテーブルのデータでレポート用テーブルのデータを洗い替える
 2-3-3 インデックスを再構築する

 

– 前準備 –

まずは前準備としてSSISの新しいプロジェクトを作成します。
開発環境はBusiness Intelligence Development Studioです。
これはSQL Serverのインストーラーに同梱されているVisual Studioのシェルを利用したツールです。
新しいプロジェクトの作成は左上のメニューから行います。

1. 「新しいプロジェクト」を選択
新しいプロジェクト

2. 「Integration Services プロジェクト」を選択

プロジェクトの新規作成

3. SSISのデザイナが開く

SSISデザイナ

SSISのデザイナが開いたら前準備はOK。
これから本題に入ります。

[抽出(Extract)]
 
2-1-1 HTMLからデータを抽出してTSVを作成する

データは気象庁が公開していた地震情報を使用します。
※2011/09/16時点で気象庁のページから直接のリンクが削除されていることを確認しました。
件数が減った状態でページが残ってはいますがいつ削除されるかわからないため、変換後のファイルを添付します。

地震情報を発表した地震の震源および震度(3月11日)

HTMLのままではWorkテーブルに取り込めません。そこでまず必要なデータのみを抽出します。
気象庁の地震データには以下の項目が含まれています。

> 時刻・北緯・東経・震源の深さ・マグニチュード・震源・最大震度

これらは1つのTable(1TRに1TD)で構成されていて、各項目の区切りは基本全角の空白、
震源と最大深度の間の区切りのみがBRタグになっているため、
今回は正規表現を利用したスクリプトを記述してHTMLを必要なデータのみのTSVに変換します。

HTMLサンプル
(※クリックして拡大)

SSISでは「スクリプトタスク」を使用することでC#やVBで記述した処理を実行させることができます。
スクリプトタスクを使用するにはまずツールボックスからデザイナにドラッグ&ドロップします。

スクリプトタスク

するとデザイナ上にスクリプトタスクが配置されるので、右クリックして編集を選択します。

スクリプトタスクとメニュー

そしてスクリプトタスクエディターが開いたら右下の「スクリプトの編集」ボタンをクリックします。
※VBで記述したい場合はScriptLanguageを「Microsoft Visual Basic 2008」に変更してください。

スクリプトタスクの編集

こうして開くssisscriptに処理を記述します。

SSISSCRIPT

処理はこちら↓↓↓。
ちょっと気持ち悪いコードですが説明を兼ねた変数名・メソッド名にしています。
「スクリプトタスク」では.Net Frameworkを自由に使用できるためWebRequestとWebResponseクラスを使用して直接HTMLを取得することもできます。
しかし、気象庁のサイトの利用上の注意に定期的・自動的な気象データの収集は遠慮してくださいと記載されているため、ブラウザでアクセスして保存したHTMLファイルをデータソースとして使用する処理にしました。
また、シンプルな作りにするためにTSVファイルのパスを固定にしていますが、
変数として定義してSSISのパッケージを実行する時に引数として渡すことも可能です。
これはまたの機会に。


// データ抽出用正規表現とRegexオプション
private const string tdTagRegEx = "<td>(.*?)</td>";
private const string aTagRegEx = "<a(.*?)</a>";
private const RegexOptions options =
   RegexOptions.IgnoreCase | RegexOptions.Singleline;

// HTMLファイルのパスとエンコード
private const string dataFile = @"C:\Work\SampleData.html";
private Encoding dataEncode = Encoding.GetEncoding("EUC-JP");

// TSVファイルのパスとエンコード及び出力テンプレート
private const string tsvFile = @"C:\Work\SampleData.tsv";
private Encoding tsvEncode = Encoding.Unicode;
private const string tsvFormat =
   "{0:yyyy/MM/dd HH:mm:ss}\t{1}\t{2}\t{3}\t{4:#.0}\t{5}\t{6}";

public void Main()
{
   string html = String.Empty;
   StringBuilder sb = new StringBuilder();
   Regex tdTagRegex = new Regex(tdTagRegEx, options);
   Regex aTagRegex = new Regex(aTagRegEx, options);

   // HTMLファイルを読み込む
   using (StreamReader sr = new StreamReader(dataFile, dataEncode))
   {
     html = sr.ReadToEnd();
   }

   // HTMLからTD要素を抽出
   MatchCollection mc = tdTagRegex.Matches(html);
   foreach (Match m in mc)
   {
     // Aタグと改行を取り除く
     string rowData = aTagRegex
       .Replace(m.Groups[1].Value, string.Empty)
       .Replace(Environment.NewLine, string.Empty);

     // 全角スペースの区切りで各項目を分割する
     string[] splitData = rowData
       .Split(new string[] { " " }, StringSplitOptions.None);

     // 各項目を整形(時刻・北緯・東経・震源の深さ・マグニチュード・震源・震度)
     DateTime 時刻 = DateTime.Parse(splitData[0]);
     string 北緯 = ConvertTo10進数表記From60進数表記(splitData[1]);
     string 東経 = ConvertTo10進数表記From60進数表記(splitData[2]);
     int 深さ = int.Parse(splitData[3].Replace("km", string.Empty));
     double マグニチュード =
        double.Parse(splitData[4].Replace("M:", string.Empty));

     // 震源と震度はさらにBRタグで分割する
     string[] 震源と震度 =
        splitData[5].Split(new string[] { "<br>" }, StringSplitOptions.None);
     string 震源 = 震源と震度[0].Trim();
     string 震度 = 震源と震度[1].Replace("最大震度:", string.Empty).Trim();

     // TSV形式の行にする
     sb.AppendLine(
        string.Format(tsvFormat,
            時刻, 北緯, 東経, 深さ, マグニチュード, 震源, 震度)
     );
   }

   // ファイルに書き込む
   using (StreamWriter sw = new StreamWriter(tsvFile, false, tsvEncode))
   {
     sw.Write(sb.ToString());
   }

   Dts.TaskResult = (int)ScriptResults.Success;
}

/// <summary>
/// 60進数表記の北緯または東経を10進数表記に変換します。
/// </summary>
/// <param name="text">60進数表記の文字列</param>
/// <returns>10進数表記の文字列</returns>
private string ConvertTo10進数表記From60進数表記(string text)
{
   // 「゜(度)」で分割
   string[] splitData = text
    .Split(new string[] { "゜" }, StringSplitOptions.None);
   // 分部分を調整
   decimal minute = decimal.Parse(splitData[1].Remove(splitData[1].Length - 2));
   decimal mm = minute / 60.0m;
   return splitData[0] + mm.ToString(".#");
}

コードを記述したらビルドを行ってからウィンドウを閉じてください。
以上で、スクリプトタスクの設定は完了です。

2-1-2 ワークテーブルを空にする

HTMLからTSVへの変換が完了したら次はすぐにでもWorkテーブルへ取り込みたいところですが、
先にWorkテーブルを空にする処理を行います。
これは一連の処理を何度でも実行できるようにするためです。

SSISでは「SQL実行タスク」を使用することで任意のSQLを実行できます。

SQL実行タスク

SQL実行タスクの配置

スクリプトタスクと同じようにツールボックスからデザイナにドラッグ&ドロップすると
赤い×印がついた状態で配置されます。
これは接続先情報が設定されていないためです。
右クリックから編集を選択し、接続先情報と実行させるSQLを設定します。

SQL実行タスクエディター

まずはConnectionTypeの項目で接続マネージャーの種類を指定します。
ExcelやOLE DB、ODBC、ADO.NETなどが指定可能です。
ここではADO.NETを指定しました。
続いて接続先情報の設定を行います。
Connectionのところで「新しい接続…」をクリック。

Connectionの設定

ADO.NETの接続マネージャーの構成

そして開いた「ADO.NETの接続マネージャーの構成」の画面右下の新規作成ボタンをクリックし、
サーバーと認証情報、接続先データベースを指定します。

接続マネージャー

接続先の設定が済んだら次はSQLStatementのところにSQLを書きます。

SQL Statement

SQL実行タスクのSQLStatementには任意のSQLを記述することが可能です。
しかし、処理はSQL Server側で一元管理した方がメンテナンスしやすくなるため、
ここではストアドを呼び出すことにします。

まずは下記のストアドを作成。

CREATE PROCEDURE dbo.TruncateWork
AS
BEGIN
   TRUNCATE TABLE dbo.Work
END

※今回はトランザクション管理を行わないため潔くTruncateしています。
  SSISにおけるトランザクション管理についてはまた後日。

そしてこのストアドの名前をSQLStatementに入力し、すぐ下のIsQueryStoredProcedureをTrueにします。

ストアドを指定

これでSQL実行タスクの設定は完了です。

– デバッグ –

ここまできたら一旦動作の確認を行いましょう。
スクリプトタスクの実行後に続けてSQL実行タスクが動くようにするために
2つのタスクを一連のタスクとして設定します。
設定方法はとっても簡単で、タスクを選択した時に表示される緑の矢印を次のタスクにつなぐだけです。

タスクの連結

タスクを連結したら画面上部の緑色の右向き三角ボタンからデバッグを開始できます。

デバッグの実行

デバッグ実行結果

上の画像のようにすべてのタスクが緑色になれば正常終了です。
後のタスクの設定に必要になるのでTSVファイルが生成されているかも確認してください。
エラーが発生した場合は赤い表示に変わります。

2-1-3 TSVからSQL Serverへデータをインポートする

ついにTSVをワークテーブルへ取り込みます。
ファイルからのデータ取り込みにはSSISの「データフロータスク」を使用します。
データフロータスク

データフロータスクを配置

データフロータスクの編集はこれまでのタスクとは異なりデータフローのタブで行います。

データフロータスクの編集

まず配置するのは「フラットファイルソース」です。

フラットファイルソース

デザイナに配置したら右クリックから編集を選択します。

フラットファイルソースの編集

新規作成ボタンをクリックするとファイル指定のダイアログが開くので、
ここでスクリプトタスクで作成したTSVファイルのパスを指定します。
接続マネージャー名には任意の名称を入力し、文字コードをUnicodeに設定します。

フラットファイルの設定

次に詳細設定の項目で取り込むデータの列毎の設定をします。

– 設定前の画面 –

フラットファイルの列定義

– 設定内容 –

Name DataType OutputColumnWidth
日時 データベース タイムスタンプ [DT_DBTIMESTAMP]
北緯 浮動小数点数 [DT_R4]
東経 浮動小数点数 [DT_R4]
深さ 4 バイト符号付き整数 [DT_I4]
マグニチュード 浮動小数点数 [DT_R4]
震源 Unicode 文字列 [DT_WSTR] 50
最大震度 Unicode 文字列 [DT_WSTR] 5

– 設定後の画面 –

列設定後

次にデータ取り込み先の設定を行います。
今回はSQLServerにデータを格納するので「SQL Server 変換先」を使用します。
SQL Server 変換先の設定にはデータソースの列情報が必要なため、
まずフラットファイルソースの緑矢印をSQL Server 変換先につなぎ、
それから右クリックの編集を選択します。

SQL Server変換先

変換元ソースを設定

接続先とデータインポート先テーブル(Work)を指定

変換先設定

フラットファイルの列とWorkテーブルの列のマッピングを確認する

列のマッピング

これで個々のタスクの設定が完了しました。
あとはSQL実行タスクとデータフロータスクを緑の矢印でつないで動作を確認します。

タスクの連結

デバッグ
※管理者権限で実行しています。

デバッグ実行を行ってすべてのタスクが緑になったらWorkテーブルの中を見てみましょう。
データがインポートされているはずです。

Workテーブルのデータ

 
以上でWeb上のデータがWorkテーブルに格納されました。
次回はこのデータに対してIDの付与と型変換を行います。

ちなみに、各タスクに名前をつけておくと何の処理タスクなのかが一目瞭然になるのでおすすめです。

タスク名の設定

ネクストスケープ企業サイトへ

NEXTSCAPE

検索する

タグ

メタデータ

投稿のRSS

著者

データベースの中のデータはその企業の財産のはず。
しかし現実にはユーザー自身が自由に見れないことが多い。

データを未来のために活かせる状態にすることを求めるエンジニアです。
「sakuma」さんの全ポストを読む