MTULabo
  • 製品紹介
  • 導入事例
  • FAQ
  • MTULabo
  • ダウンロード
評価版ライセンス申込 有償版利用申込(見積依頼) MTU Advance

法人番号の一括ダウンロードと表へのインポート (3/3)

公開日 2018-10-17
更新日 2018-10-17

デモの前提条件

法人番号のデータが作成できましたので、器となるテーブルやロードに必要な資源を準備します。本編の事例では次の条件を想定しています。実際に試される場合は、読者の環境に会うよう読み替えて、入手したスクリプト等を編集してご利用下さい。

ユーザ名 HOUJIN
パスワード HOUJIN
Oracle Netサービス名 ORCL
作成する表の名前 HOUJIN_BANGOU
パーティション化 ハッシュ・パーティショニング

表の作成

テーブルを作成するスクリプトです。提供しているサンプルは HOUJIN_BANGOU.sql で確認できます。項目の定義については国税庁のサイトにあるダウンロードファイルのデータ定義をご確認下さい。

create table HOUJIN_BANGOU
( SEQUENCE_NUMBER            number(9)
, CORPORATE_NUMBER           char(13)
, PROCESS                    char(2)
, CORRECT                    char(1)
, UPDATE_DATE                date
, CHANGE_DATE                date
, NAME                       varchar(600)
, NAME_IMAGE_ID              char(8)
, KIND                       char(3)
, PREFECTURE_NAME            varchar2(40)
, CITY_NAME                  varchar2(80)
, STREET_NUMBER              varchar2(1200)
, ADDRESS_IMAGE_ID           char(8)
, PREFECTURE_CODE            char(2)
, CITY_CODE                  char(3)
, POST_CODE                  char(7)
, ADDRESS_OUTSIDE            varchar2(1200)
, ADDRESS_OUTSIDE_IMAGE_ID   char(8)
, CLOSE_DATE                 date
, CLOSE_CAUSE                char(2)
, SUCCESSOR_CORPORATE_NUMBER char(13)
, CHANGE_CAUSE               varchar2(2000)
, ASSIGNMENT_DATE            date
, LATEST                     char(1)
, EN_NAME                    varchar2(300)
, EN_PREFECTURE_NAME         varchar2(9)
, EN_CITY_NAME               varchar2(600)
, EN_ADDRESS_OUTSIDE         varchar2(600)
, FURIGANA                   varchar2(2000)
, CONSTRAINT HOUJIN_BANGOU_PK PRIMARY KEY (CORPORATE_NUMBER)
)
partition by hash (PREFECTURE_CODE)
( partition p01
, partition p02
, partition p03
, partition p04
, partition p05
, partition p06
, partition p07
, partition p08
);

都道府県を跨ぐ移動への対応

差分データをインポートすると、法人が別な都道府県へ移動するケースが時々有ります。テーブルが都道府県コードでパーティション化されている為、上記の定義のままだと下記のエラーで差分データをが拒否されてしまいます。

ORA-14402. It makes rows moveable across each partition.

これを回避する為、次の変更文を実行します。

ALTER TABLE HOUJIN_BANGOU ENABLE ROW MOVEMENT;

差分データのマージへの対応

差分データを SQL*Loader を使って適用する場合にはひと工夫必要です。SQL*Loader は内部的には INSERT を実行していますので、そのまま HOUJIN_BANGOU 表をターゲットとしてしまうと、HOUJIN_BANGOU_PK について主キー制約違反となってしまいます。実際には、マージを行いたいので、HOUJIN_BANGOU に法人番号が存在すれば UPDATE を、存在しなければ INSERT を実行するようにしなければなりません。

この操作を実現する為、更新可能なビュー(HOUJIN_BANGOU_VIEW)を作成し、INSTEAD OF ビュートリガーを追加します。

更新可能なビュー:

create or replace view HOUJIN_BANGOU_VIEW as
select * from HOUJIN_BANGOU
with check option constraint HOUJIN_BANGOU_V
;

INSTEAD OF ビュートリガー:

create or replace trigger TRG_HOUJIN_BANGOU_INS
instead of insert on HOUJIN_BANGOU_VIEW
begin
	update HOUJIN_BANGOU
	--  SEQUENCE_NUMBER = :new.SEQUENCE_NUMBER    更新対象外
	--  CORPORATE_NUMBER = :new.CORPORATE_NUMBER  更新対象外
	set PROCESS = :new.PROCESS
	  , CORRECT = :new.CORRECT
	--  UPDATE_DATE = :new.UPDATE_DATE            更新対象外
	  , CHANGE_DATE = :new.CHANGE_DATE
	  , NAME = :new.NAME
	  , NAME_IMAGE_ID = :new.NAME_IMAGE_ID
	  , KIND = :new.KIND
	  , PREFECTURE_NAME = :new.PREFECTURE_NAME
	  , CITY_NAME = :new.CITY_NAME
	  , STREET_NUMBER = :new.STREET_NUMBER
	  , ADDRESS_IMAGE_ID = :new.ADDRESS_IMAGE_ID
	  , PREFECTURE_CODE = :new.PREFECTURE_CODE
	  , CITY_CODE = :new.CITY_CODE
	  , POST_CODE = :new.POST_CODE
	  , ADDRESS_OUTSIDE = :new.ADDRESS_OUTSIDE
	  , ADDRESS_OUTSIDE_IMAGE_ID = :new.ADDRESS_OUTSIDE_IMAGE_ID
	  , CLOSE_DATE = :new.CLOSE_DATE
	  , CLOSE_CAUSE = :new.CLOSE_CAUSE
	  , SUCCESSOR_CORPORATE_NUMBER = :new.SUCCESSOR_CORPORATE_NUMBER
	  , CHANGE_CAUSE = :new.CHANGE_CAUSE
	  , ASSIGNMENT_DATE = :new.ASSIGNMENT_DATE
	  , LATEST = :new.LATEST
	  , EN_NAME = :new.EN_NAME
	  , EN_PREFECTURE_NAME = :new.EN_PREFECTURE_NAME
	  , EN_CITY_NAME = :new.EN_CITY_NAME
	  , EN_ADDRESS_OUTSIDE = :new.EN_ADDRESS_OUTSIDE
	  , FURIGANA = :new.FURIGANA
	where CORPORATE_NUMBER = :new.CORPORATE_NUMBER
	and CHANGE_DATE < :new.CHANGE_DATE-- 直近の更新以降に生じた差分
	and :new.PROCESS in
		( '11' -- 商号又は名称の変更
		, '12' -- 国内所在地の変更
		, '13' -- 国外所在地の変更
		, '21' -- 登記記録の閉鎖等
		, '22' -- 登記記録の復活等
		, '71' -- 吸収合併
		, '72' -- 吸収合併無効
		, '81' -- 商号の登記の抹消
		)
	;
	if sql%notfound then
		insert into HOUJIN_BANGOU
		( SEQUENCE_NUMBER
		, CORPORATE_NUMBER
		, PROCESS
		, CORRECT
		, UPDATE_DATE
		, CHANGE_DATE
		, NAME
		, NAME_IMAGE_ID
		, KIND
		, PREFECTURE_NAME
		, CITY_NAME
		, STREET_NUMBER
		, ADDRESS_IMAGE_ID
		, PREFECTURE_CODE
		, CITY_CODE
		, POST_CODE
		, ADDRESS_OUTSIDE
		, ADDRESS_OUTSIDE_IMAGE_ID
		, CLOSE_DATE
		, CLOSE_CAUSE
		, SUCCESSOR_CORPORATE_NUMBER
		, CHANGE_CAUSE
		, ASSIGNMENT_DATE
		, LATEST
		, EN_NAME
		, EN_PREFECTURE_NAME
		, EN_CITY_NAME
		, EN_ADDRESS_OUTSIDE
		, FURIGANA
		) select
		  :new.SEQUENCE_NUMBER
		, :new.CORPORATE_NUMBER
		, :new.PROCESS
		, :new.CORRECT
		, :new.UPDATE_DATE
		, :new.CHANGE_DATE
		, :new.NAME
		, :new.NAME_IMAGE_ID
		, :new.KIND
		, :new.PREFECTURE_NAME
		, :new.CITY_NAME
		, :new.STREET_NUMBER
		, :new.ADDRESS_IMAGE_ID
		, :new.PREFECTURE_CODE
		, :new.CITY_CODE
		, :new.POST_CODE
		, :new.ADDRESS_OUTSIDE
		, :new.ADDRESS_OUTSIDE_IMAGE_ID
		, :new.CLOSE_DATE
		, :new.CLOSE_CAUSE
		, :new.SUCCESSOR_CORPORATE_NUMBER
		, :new.CHANGE_CAUSE
		, :new.ASSIGNMENT_DATE
		, :new.LATEST
		, :new.EN_NAME
		, :new.EN_PREFECTURE_NAME
		, :new.EN_CITY_NAME
		, :new.EN_ADDRESS_OUTSIDE
		, :new.FURIGANA
		from DUAL
		where not exists ( -- 既存の法人番号がない
			select null
			from HOUJIN_BANGOU
			where CORPORATE_NUMBER = :new.CORPORATE_NUMBER
		);
	end if;
end;
/

加えて SQL*Loader のターゲットにHOUJIN_BANGOU_VIEWを指定します。
HOUJIN_BANGOU_VIEW をターゲットにする制御ファイル HOUJIN_BANGOU_VIEW.ctl

LOAD CHARACTERSET AL32UTF8 BYTEORDERMARK NOCHECK
INFILE "00_houjin_bangou_sabun.csv" "VAR 10"
INTO TABLE "HOUJIN_BANGOU_VIEW"
APPEND REENABLE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(  "SEQUENCE_NUMBER"                    DECIMAL EXTERNAL(10)
,  "CORPORATE_NUMBER"                   CHAR(13) ENCLOSED BY '"'
,  "PROCESS"                            CHAR(2) ENCLOSED BY '"'
,  "CORRECT"                            CHAR(1) ENCLOSED BY '"'
,  "UPDATE_DATE"                        DATE(14) "YYYY-MM-DD" ENCLOSED BY '"'
,  "CHANGE_DATE"                        DATE(14) "YYYY-MM-DD" ENCLOSED BY '"'
,  "NAME"                               CHAR(600) ENCLOSED BY '"'
,  "NAME_IMAGE_ID"                      CHAR(8) ENCLOSED BY '"'
,  "KIND"                               CHAR(3) ENCLOSED BY '"'
,  "PREFECTURE_NAME"                    CHAR(40) ENCLOSED BY '"'
,  "CITY_NAME"                          CHAR(80) ENCLOSED BY '"'
,  "STREET_NUMBER"                      CHAR(1200) ENCLOSED BY '"'
,  "ADDRESS_IMAGE_ID"                   CHAR(8) ENCLOSED BY '"'
,  "PREFECTURE_CODE"                    CHAR(2) ENCLOSED BY '"'
,  "CITY_CODE"                          CHAR(3) ENCLOSED BY '"'
,  "POST_CODE"                          CHAR(7) ENCLOSED BY '"'
,  "ADDRESS_OUTSIDE"                    CHAR(1200) ENCLOSED BY '"'
,  "ADDRESS_OUTSIDE_IMAGE_ID"           CHAR(8) ENCLOSED BY '"'
,  "CLOSE_DATE"                         DATE(14) "YYYY-MM-DD" ENCLOSED BY '"'
,  "CLOSE_CAUSE"                        CHAR(2) ENCLOSED BY '"'
,  "SUCCESSOR_CORPORATE_NUMBER"         CHAR(13) ENCLOSED BY '"'
,  "CHANGE_CAUSE"                       CHAR(2000) ENCLOSED BY '"'
,  "ASSIGNMENT_DATE"                    DATE(14) "YYYY-MM-DD" ENCLOSED BY '"'
,  "LATEST"                             CHAR(1) ENCLOSED BY '"'
,  "EN_NAME"                            CHAR(300) ENCLOSED BY '"'
,  "EN_PREFECTURE_NAME"                 CHAR(9) ENCLOSED BY '"'
,  "EN_CITY_NAME"                       CHAR(600) ENCLOSED BY '"'
,  "EN_ADDRESS_OUTSIDE"                 CHAR(600) ENCLOSED BY '"'
,  "FURIGANA"                           CHAR(2000) ENCLOSED BY '"'
)

データのロード

テーブルの作成/変更、更新可能なビューとビュートリガー作成

SQL*Plus を使ってスクリプトを実行します。

sqlplus HOUJIN/HOUJIN@ORCL @HOUJIN_BANGOU

スクリプトが正しく実行されると次のようなメッセージが表示されます。

SQL*Plus: Release 11.2.0.2.0 Production on 月 10月 15 19:35:54 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.



Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
drop table HOUJIN_BANGOU
           *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。



表が作成されました。


表が変更されました。

drop view HOUJIN_BANGOU_VIEW
*
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。



ビューが作成されました。


トリガーが作成されました。

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsとの接続が切断されました。

全件データのロード

引数を与えて SQL*Loader を実行します。ロード時のパフォーマンスを考慮し、ダイレクト・パス・ロードを有効化し、一度に読取るデータ量を 50 万件としています。

sqlldr USERID=HOUJIN/HOUJIN@ORCL control=HOUJIN_BANGOU DIRECT=Y ROWS=500000

ロードが正しく実行されると次のようなメッセージが表示されます。

SQL*Loader: Release 11.2.0.2.0 - Production on 月 10月 15 19:47:51 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

セーブ・データ・ポイントに達しました。 - 論理レコード件数500000
セーブ・データ・ポイントに達しました。 - 論理レコード件数1000000
セーブ・データ・ポイントに達しました。 - 論理レコード件数1500000
セーブ・データ・ポイントに達しました。 - 論理レコード件数2000000
セーブ・データ・ポイントに達しました。 - 論理レコード件数2500000
セーブ・データ・ポイントに達しました。 - 論理レコード件数3000000
セーブ・データ・ポイントに達しました。 - 論理レコード件数3500000
セーブ・データ・ポイントに達しました。 - 論理レコード件数4000000
セーブ・データ・ポイントに達しました。 - 論理レコード件数4500000

ロードは完了しました。 - 論理レコード件数4664375

差分データのロード

引数を与えて SQL*Loader を実行します。INSTEAD OF ビュートリガーが作動するよう、従来パス・ロードを使用し、バインド変数用の領域と読み取りバッファを多めに与えて最適化します。

sqlldr USERID=HOUJIN/HOUJIN@ORCL control=HOUJIN_BANGOU_VIEW DIRECT=N ROWS=1000 READSIZE=10000000 BINDSIZE=10000000

ロードが正しく実行されると次のようなメッセージが表示されます。

SQL*Loader: Release 11.2.0.2.0 - Production on 月 10月 15 19:59:54 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

コミット・ポイントに達しました。 - 論理レコード件数1000
コミット・ポイントに達しました。 - 論理レコード件数2000
コミット・ポイントに達しました。 - 論理レコード件数3000
コミット・ポイントに達しました。 - 論理レコード件数4000
コミット・ポイントに達しました。 - 論理レコード件数5000
コミット・ポイントに達しました。 - 論理レコード件数6000
コミット・ポイントに達しました。 - 論理レコード件数7000
コミット・ポイントに達しました。 - 論理レコード件数8000
コミット・ポイントに達しました。 - 論理レコード件数9000
コミット・ポイントに達しました。 - 論理レコード件数10000
コミット・ポイントに達しました。 - 論理レコード件数11000
コミット・ポイントに達しました。 - 論理レコード件数12000
コミット・ポイントに達しました。 - 論理レコード件数13000
コミット・ポイントに達しました。 - 論理レコード件数14000
コミット・ポイントに達しました。 - 論理レコード件数15000
コミット・ポイントに達しました。 - 論理レコード件数16000
コミット・ポイントに達しました。 - 論理レコード件数17000
コミット・ポイントに達しました。 - 論理レコード件数18000
コミット・ポイントに達しました。 - 論理レコード件数19000
コミット・ポイントに達しました。 - 論理レコード件数20000
コミット・ポイントに達しました。 - 論理レコード件数21000
コミット・ポイントに達しました。 - 論理レコード件数22000
コミット・ポイントに達しました。 - 論理レコード件数23000
コミット・ポイントに達しました。 - 論理レコード件数24000
コミット・ポイントに達しました。 - 論理レコード件数25000
コミット・ポイントに達しました。 - 論理レコード件数25316

まとめ

本編を通じて次の事を確認する事が出来ました。

  • 今回のケースでは法人番号表は主キー索引と併せておよそ1100MBのセグメントサイズが有ります。データベース・バッファ・キャッシュを最大限有効活用し、プログラム性能の最適化を図るには、一緒に参照される可能性が高い行同士をなるべく同じブロックに格納することが有効です。これを一般にはクラスタリングと呼びます。Oracle データベースにはクラスタリングを実現する方法として、表クラスタ、索引構成表、パーティショニングの3つのテクノロジーが利用可能です。デモで使用した表は都道府県コードをキーとしてパーティショニングを行い、同じ都道府県の行データ同士が同じパーティションへ配属されるされることが強制されるよう設計しました。
  • 更新可能なビューとINSTEAD OF ビュートリガーを使うと、Oracle DBサーバ以外の場所からSQL*Loader を使って差分データのマージが出来ます。この場合、差分用のデータは必ずしもDBサーバのローカルファイルシステム上に置く必要はありません。util_fileパッケージを使ってマージプログラムを書くのと比べて、この方法は実行場所の制約に支配され難く柔軟性が有ります。

by 開発1号

法人番号の一括ダウンロードと表へのインポート (1/3)へ戻る
法人番号の一括ダウンロードと表へのインポート (2/3)へ戻る