少し間が空いてしまいました。
佐久間です。
「Reporting ServicesとBing MapsでWEB上のHTMLデータを可視化するレポートを作成する」
第2回目は前回作成したWorkテーブルにHTMLから抽出したデータを取り込む処理を行います。
（目次はこちら）
2. データを取得・変換する：Integration Services
ETL処理の全体像は下記の通りです。
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のシェルを利用したツールです。
新しいプロジェクトの作成は左上のメニューから行います。
2. 「Integration Services プロジェクト」を選択
3. SSISのデザイナが開く
SSISのデザイナが開いたら前準備はOK。
これから本題に入ります。
[抽出(Extract)]
2-1-1 HTMLからデータを抽出してTSVを作成する
HTMLのままではWorkテーブルに取り込めません。そこでまず必要なデータのみを抽出します。
気象庁の地震データには以下の項目が含まれています。
これらは１つのTable（1TRに1TD）で構成されていて、各項目の区切りは基本全角の空白、
震源と最大深度の間の区切りのみがBRタグになっているため、
今回は正規表現を利用したスクリプトを記述してHTMLを必要なデータのみのTSVに変換します。
（※クリックして拡大）
SSISでは「スクリプトタスク」を使用することでC#やVBで記述した処理を実行させることができます。
スクリプトタスクを使用するにはまずツールボックスからデザイナにドラッグ＆ドロップします。
するとデザイナ上にスクリプトタスクが配置されるので、右クリックして編集を選択します。
そしてスクリプトタスクエディターが開いたら右下の「スクリプトの編集」ボタンをクリックします。
※VBで記述したい場合はScriptLanguageを「Microsoft Visual Basic 2008」に変更してください。
こうして開く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 ワークテーブルを空にする
先にWorkテーブルを空にする処理を行います。
これは一連の処理を何度でも実行できるようにするためです。
SSISでは「SQL実行タスク」を使用することで任意のSQLを実行できます。
スクリプトタスクと同じようにツールボックスからデザイナにドラッグ＆ドロップすると
赤い×印がついた状態で配置されます。
これは接続先情報が設定されていないためです。
右クリックから編集を選択し、接続先情報と実行させるSQLを設定します。
まずはConnectionTypeの項目で接続マネージャーの種類を指定します。
ExcelやOLE DB、ODBC、ADO.NETなどが指定可能です。
ここではADO.NETを指定しました。
続いて接続先情報の設定を行います。
Connectionのところで「新しい接続…」をクリック。
そして開いた「ADO.NETの接続マネージャーの構成」の画面右下の新規作成ボタンをクリックし、サーバーと認証情報、接続先データベースを指定します。
接続先の設定が済んだら次はSQLStatementのところにSQLを書きます。
SQL実行タスクのSQLStatementには任意のSQLを記述することが可能です。
しかし、処理はSQL Server側で一元管理した方がメンテナンスしやすくなるため、
ここではストアドを呼び出すことにします。
まずは下記のストアドを作成。
CREATE PROCEDURE dbo.TruncateWork
AS
BEGIN
TRUNCATE TABLE dbo.Work
END
※今回はトランザクション管理を行わないため潔くTruncateしています。
SSISにおけるトランザクション管理についてはまた後日。
そしてこのストアドの名前をSQLStatementに入力し、すぐ下のIsQueryStoredProcedureをTrueにします。
これでSQL実行タスクの設定は完了です。
– デバッグ –
ここまできたら一旦動作の確認を行いましょう。
スクリプトタスクの実行後に続けてSQL実行タスクが動くようにするために
２つのタスクを一連のタスクとして設定します。
設定方法はとっても簡単で、タスクを選択した時に表示される緑の矢印を次のタスクにつなぐだけです。
タスクを連結したら画面上部の緑色の右向き三角ボタンからデバッグを開始できます。
上の画像のようにすべてのタスクが緑色になれば正常終了です。
後のタスクの設定に必要になるのでTSVファイルが生成されているかも確認してください。
エラーが発生した場合は赤い表示に変わります。
2-1-3 TSVからSQL Serverへデータをインポートする
ファイルからのデータ取り込みには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 変換先につなぎ、
それから右クリックの編集を選択します。
接続先とデータインポート先テーブル（Work）を指定
フラットファイルの列とWorkテーブルの列のマッピングを確認する
これで個々のタスクの設定が完了しました。
あとはSQL実行タスクとデータフロータスクを緑の矢印でつないで動作を確認します。
※管理者権限で実行しています。
デバッグ実行を行ってすべてのタスクが緑になったらWorkテーブルの中を見てみましょう。
データがインポートされているはずです。
以上でWeb上のデータがWorkテーブルに格納されました。
次回はこのデータに対してIDの付与と型変換を行います。
ちなみに、各タスクに名前をつけておくと何の処理タスクなのかが一目瞭然になるのでおすすめです。
