テキストファイルから SQL Server に気象データをインポートする

 データベースにテキストファイルをインポートしたいというニーズは多い.今回は csv ファイルから SQL Server にデータをインポートしたので備忘録として公開する.

気象庁のサイトから過去データをダウンロードする

 今回は気象庁のサイトからダウンロードしたデータファイルを使用する.

気象庁の過去データのありか

 気象庁の過去データをダウンロードするにはここから入る.

観測地点・項目・期間を指定する

 ここでは東京都の観測地点を選択している.

気象庁の過去の気象データ・ダウンロードのページ

 観測地点をクリックして選択する.ここでは東京都を選ぶ.

東京都を選んだところ.さらに観測地点をクリックして指定する
項目をクリックして指定する.あまり細かい項目は選べない
期間の選択.「特定の期間を複数年分,表示する」からデータ量上限まで選択する.100 % を超えないように注意.「csvファイルをダウンロード」をクリックするとファイルがダウンロードされる

 右上のデータ量を示すバーが 100 % を超えないように期間を調整して選択する.当然,目的の期間に足りなければ別途指定してファイルをダウンロードすることになる.

データクレンジング

 データクレンジングはデータをデータベースにインポートする前の必須の作業である.この作業に時間と手間がかかる.

 この作業に全体の労力の九割が割かれていると言って過言ではない.官公庁はもっとこの手間を省けるように努力すべきだ.

ダウンロードしたcsvファイルはEXCELで開かれる
不要な列を削除している
同様に不要な行を削除している
項目名に機種依存文字があるのでこれを削除する
これでやっとデータベースに登録できる状態になる

フィルターを使っておかしなデータがないか探す

 EXCELのフィルターは優秀だ.以前はオートフィルターと言ったが,今は単にフィルターと言う.アドバンスドフィルターという機能もあったのだが,アプリからは見えなくなっている.

 気温は数値なのに文字列が紛れ込んでいたり,カッコがついていたりすることがある.そういうデータ型の誤りがあったりすると,変換できずにエラーが発生してインポートが止まる.これはまずいので手動で修正する.

 数値にカッコがついているだけなら取り除くだけでいいが,エラーを示す文字列がある場合はどうしようもない.その行は諦めて削除しよう.

 ここまできてやっとデータベースにインポートできるようになる.

SQL Server にデータをインポートする

 SQL Server へのデータのインポートは難しい.不可能ではないが,難しい.多くの選択肢があるが,最後まで到達できる経路は限られていて,途中で行き止まりになる分かれ道がいくつもある.

 データのインポートにはウィザードを使うのがベターだろう.T-SQL コマンドを使う手もあるが,初心者にはまず無理だ.

ウィザードを起動する

「データベース」ノードからインポート先のデータベース名を右クリックして「タスク」,「データのインポート」を選択する.直上の「フラットファイルのインポート」ではない
「SQL Server インポートおよびエクスポートウィザート」が起動する.「Next」をクリックする

データソースを選択する

「データソースの選択」画面.ポップアップメニューに「.Net Framework Data Provider for ODBC」が選択されている
ポップアップメニューをクリックして「Flat File Source」を選択する
ファイルパスを指定するダイアログ.ここでは拡張子が .txt のため何も見えていない
拡張子を .csv に変更する
目的のファイルを選択する
データソースの選択が終わった状態.「Next」をクリックする
左のパネルから「列」を選択したところ.プレビューが見えている
同様に「詳細設定」を選択したところ
同様に「プレビュー」を選択したところ.確認したら「Next」をクリックする

インポート先のデータベースを指定する

「変換先の選択」画面に遷移する.ポップアップメニューに「.Net Framework Data Provider for ODBC」が選択されているが,このままではインポートできない
ポップアップメニューをクリックして「SQL Server Native Client 11.0」を選択する.他の選択肢を選んでも行き止まりだ
「変換先」が指定されたところ.「Next」をクリックする

詳細なマッピングを指示する

「コピー元のテーブルおよびビューを選択」画面に遷移する.ここから先はインポート元の列と変換先のテーブルの列を対応付けていく.「マッピングの編集」をクリックする
「列マッピング」画面に遷移する.ここで重要なのは「型」と「NULLの許可」だ.
「年月日」のデータ型を「date」に,「最高気温」と「最低気温」のデータ型を「float」にそれぞれ変更する.「NULLの許可」のチェックを全部外して「OK」をクリックする
「データ型マッピングの確認」画面に遷移する.
「エラー時」「切り捨て時」それぞれを「無視する」に変更する.「Next」をクリックする

さあ,インポートを実行しよう

「パッケージの保存および実行」画面に遷移する.何も変更せず「Next」をクリックする
最終確認画面に遷移する.「Finish」をクリックする
正常に終了した.書き忘れたが, csv ファイル名がそのままテーブル名になる

まとめ

 ここまで図解を中心に .csv ファイルを SQL Server にインポートする方法を解説してきた.この手のスキルは必須ではないかもしれないが,官公庁や企業の公開しているデータを自力で取り込む際にはどうしても必要になる.

 以前は PDF ファイルで公開されていたデータも,最近は EXCEL のファイル形式でそのまま置いてあることが多くなってきた.データの活用という意味では良い傾向ではあるが,まだまだ不十分だ.税金を使って収集したデータを国民が活用できる形にしておくというのは,民主国家なら当然ではないだろうか?

 その意味で,気象庁のデータ提供形式は不親切だ.ネット回線はますます高速化していくのに,なぜダウンロードできるデータ量に制限を設けるのか?

 気象庁の提供する気象データは今や宝の山であることは間違いない.これは憶測だが,多くの企業にとって気象データは利益に直結する外部要因だ.リアルタイムでのデータ提供や数日後の天気予報が企業の売上を左右し,株価に影響するとなれば,より多くの企業がデータの提供を望むことは想像に難くない.

 そこに気象庁が無制限にデータの提供を開始すれば,アクセスが集中してサイトがダウンするのは目に見えている.おそらく,契約によって専用線を引いた企業へのデータ提供を優先しているのであろう.俺のような市中のユーザーからすれば歯がゆいことだが,現実的な対応なのだろう.

 しかし,である.気象データは国民の健康にとっても極めて重要だ.ちょっと想像するだけでも,真夏の最高気温と熱中症の発症率,冬の最低気温と心筋梗塞や脳血管障害の発症率が密接に関連しているだろうことは間違いないだろう.

 猪瀬直樹が指摘しているように,日本という国は省庁の縦割りのために極めて非効率な運営がなされており,省庁間の連携というものが取れていない.気象庁と他の省庁とのデータを結合できれば,もっと「データからモノを言う」ことが容易になり,政策立案に活かすことが出来るのにそれができていない.

 だが,文句ばかり言っていても仕方がない.各自が自分の持ち場で出来る範囲のことをやるしかない.俺にとっては官公庁の公開しているデータを横断的に結合して新しい発見をする,というのが一つのテーマになっている.

人は出来るだけ早く問題を作る側に軸足を移すべきである

 「愛は測定できるか?」でも書いたが,人は「問題を作る」側に移らないと,早晩人工知能に取って代わられるだろう.

何が問題なのか?

  • 健康(厚生労働省)
  • 外来生物(環境省)
  • 農産物(農林水産省)
  • 電力需給の逼迫率(経済産業省)

 気象と結合させて問いを立てられるテーマはなんだろうか?上記のリストは公的機関だが,その他の分野でも経験的に知られていた知見をデータから裏付ける作業が各分野で進行していくだろう.

データベースというインフラを整備しよう

 だが,それを裏付けるには統計が必須であり,統計の土台にはデータベースが絶対に必要だ.日本の省庁の発表しているデータはあまりにも再活用しにくい.省庁を横断するデータベースがほしい.絵に描いた餅であることは百も承知だが,今のシステムはあまりにも使いにくい.

 日本国内のデータベースはあまりに貧弱だ.米国で普及した社会保障番号のように,国民一人ひとりを識別する ID としてようやくマイナンバーが導入されたのに,規制ばかりで活用が進まない.この点では中国にも先を越されている.

 日本の IT 企業はあまりにもデータベースを軽視しすぎている.だが,それは企業だけの責任ではない.我々自身もデータベースの重要性を認識していない.「機械はよく分からない」などと言っている人間はさっさと重要なポストから降ろすべきだ.さもないと GAFA に蹴散らされることになる.個人的にはさっさと蹴散らしてほしいんだけどね.

6 Replies to “テキストファイルから SQL Server に気象データをインポートする”

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください