(#010) 2001.11.07

復刻「ツボによく利くJAVA & DB」

復刻シリーズ 第６０弾！！この頃からJavaでも結局Rubyの話してるな^^

━━━━━━━━━━━━━━━━━━━━━━ 2001.11.07 Vol.437 ━━━━━
　□■□
　■□■　　日刊「WEBのツボ」 〜次世代WEBマーケティングを読む〜
　□■□ 　　　　　　　　　　　　　        http://www.soho-union.com/
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
　　　　　　　　　　　　　　　　　　　　　　　　　　配信部数　2422部

【○】本日のお題 「ツボによく利くJAVA & DB」(#010) ━━━━━━━━━━
                                                           福井修@Fsys

▽ SQL の 使いこなし 実例

今回も引き続き SQL の使いこなしについて話を進めます。

ＤＢの土俵は、データ件数が多くて、ファイルのハンドリングでは、効率が悪い所で
す。SQL文の使いこなしで自在にデータが取り扱えるようになるともう手放せなくな
ります。

では、大量のデータから自由自在にデータを取り出すSQLの実例をやってみましょう。
まず、取り出すには、大量のデータを作ってDBに格納しておく必要があります。
大量のデータを入手する簡単な例として、総務省郵政事業庁(旧郵政省)の郵便番号簿
があります。
・ 郵便番号簿ダウンロードサイト
http://www.post.yusei.go.jp/newnumber/down_2.htm

全国一括で約 1,7MB １２万件程あります。この ken_all.lzh をダウンロードして
解凍します。すると11.4MB の KEN_ALL.CSV が出来上がります。
・ 新郵便番号データの説明 http://www.post.yusei.go.jp/newnumber/readme.htm

このデータを格納するテーブルを SQLで作成し create table しておきます。
もちろん PostgreSQL を使用します。

    

  • 

      

    • ───────────────────────────────────
      • 

    • 

create table postlist           -- ■ 郵便番号簿

    

  • 

      

    • ───────────────────────────────────
      • 

    • 

(
    jiscode         text,
    old_id          varchar(5),
    post_id         char(7),
    ken_k           text,
    si_k            text,
    tyou_k          text,
    ken             text,
    si              text,
    tyou            text,
    somezips        int4,
    aza             int4,
    choume          int4,
    sometowns       int4,
    changed         int4,
    changed_why     int4
);
create index newcodeindex on postlist using btree(post_id);
grant select on postlist    to nobody;

新郵便番号は、７桁固定なので、この項目は固定長にしてindexを張ります。

解凍したファイルは、SHIFT_JISコードで、Linux上のPostgreSQLは、通常 EUCコード
を用いるので、コード変換が必要となります。

また インターネット上で扱うことも考慮すると半角カナも全角カナに変換します。
（ imodeでは、SHIFT_JISで半角カナもどんどん使うのですが...(^_~;; )
さらに PostgreSQL でテーブルに流しこんでやるのに便利な copyコマンドが使える
のですが、その場合 Defaultでは、区切り文字はダブルクォーテーションとカンマで
なくタブなのです。

そこで、これらの変換を一気に掛けてやります。
もちろん Ruby で。

#! /usr/local/bin/ruby -Ks
require "nkf"
ARGF.each do | line |
  line.gsub(/"/,//)
  line.gsub(/,/,\t)
  print NKF.nkf('-XSe', line)
end

ARGFは、コマンドラインに並んだ文字列をファイル名と見なして仮想IOオブジェクト
として扱える組み込み定数です。

nkfは、ネットワーク用漢字コード変換フィルタで、Rubyに標準添付されています。
詳細は ftp://ftp.ie.u-ryukyu.ac.jp/pub/software/kono/nkf171.shar

例えば上記スクリプトを s2e.rb として
 $ ./s2e.rb KEN_ALL.CSV > ken_all.euc
で実行して 変換後のファイルを作成します。

これを PostgreSQLの postlist テーブルに流し込むには、DB名が、postとして

$ psql post

これでSQLインタプリタを立ち上げて

post=# \copy postlist from ken_all.euc

と copyコマンドでテーブル名とテキストファイルを指定します。
うまくゆけば、

\.

と表示されます。

簡単な select検索 から いろいろ組み合わせを見てみようとしたのですが、前準備
のデータの格納で今回は終わります。

変換フィルタに Ruby を使用しましたが、これを Javaで書くとなると結構大変です。
そうなんです。Javaってテキスト処理や正規表現には、スクリプト言語に比較して
そう有利ではないのです。DBアクセスの JDBC は、優れていると思いますが。

今週のツボ 『 何事も、適材適所で 』

■ Ruby FAQ  
http://www.ruby-lang.org/ja/man-1.6/?cmd=view;name=Ruby+FAQ

【プロフィール】
福井 修 ( FUKUI Osamu )o-fukui@po.iijnet.or.jp fukui@fsys.net
福井システムリサーチ http://fsys.net/  主幹。システム構築歴２５年。
Linux+JSP+PostgreSQLでの郵便番号簿 検索 http://fsys.net/post/
日本リヌクス協会、神戸商工会議所、情報処理学会 会員
関西ソーホ・デジタルコンテンツ事業協同組合員
流通科学大学 委託SE http://www.umds.ac.jp/