ITコーディネータのシュウです。
写真は、前回も載せましたが、富山の五箇山という世界遺産合掌造り集落に行ったときの写真です。
さて、とうとうブラジル・リオデジャネイロで行われていたパラリンピックも終わりました。オリンピック、パラリンピックとも、多くの感動を私たちに与えて頂いたと、選手の方々に本当に感謝します。
パラリンピックでは金メダルはなかったということですが、過去最高のメダル数を獲得。また、ちょっと話題は古くなりますが、先月のオリンピックでは、体操団体での悲願の金メダル、個人総合2連覇の内村選手の最後の大逆転は圧巻でしたね。伊調馨選手のレスリング4連覇は、女子個人競技で史上初めてのこと。卓球でも男子団体で銀メダル、女子団体も銅メダル、女子バドミントンダブルスの日本初の金メダル、本当に手に汗握って応援しました。柔道も男子は全階級でメダル、男子の陸上4*100メートルリレー銀メダル、競泳陣の活躍、数え上げればきりがないです。
次はいよいよ東京オリンピック、パラリンピック！ 4年間という長い努力と訓練の期間、想像を絶する戦いの末に、栄光があるのでしょう。私たちも、道は違いますが、選手の方々に負けないよう、与えられた環境の中で精いっぱい頑張って行かなければと背中を押される気がします。
選手の皆さん、本当にお疲れ様でした。そして、感動をありがとう！
＜本日の題材＞
Oracleの外部表（２）
前回、ORACLEの外部表についてORACLE_LOADER型を取り上げましたが、今回は、ORACLE_DATAPUMP型というものを試してみたいと思います。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルにデータを書き込んだり、読込んだりすることができます。
例）
商品売上という、商品コード毎、年度毎の各月の売上金額を集計したテーブルがあり、そこに、2014年、2015年、2016年のデータが登録されているとします。
テーブルの定義は以下のようになっています。
DESC 商品売上
年度毎の件数は、
SELECT 年度, COUNT(*) FROM 商品売上
GROUP BY 年度
ORDER BY 年度;
サンプルデータなので件数は少ないです。
それで、今年度（2016）のデータだけ残して、過去の年度のデータは、テーブルとしては持たないでダンプファイルに保存し、必要なときには外部表としてアクセスするようにしたいと思います。
まず、2014年のデータの外部表を作成し、外部表のダンプファイルにデータを登録します。
CREATE TABLE SYO_URIAGE_2014
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY temp_dir
LOCATION ('uriage_2014.dmp')
)
AS SELECT * FROM 商品売上
WHERE 年度 = '2014';
次に、同様にして、2015年のデータの外部表を作成します。
CREATE TABLE SYO_URIAGE_2015
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY temp_dir
LOCATION ('uriage_2015.dmp')
)
AS SELECT * FROM 商品売上
WHERE 年度 = '2015';
実際に、ダンプファイルが指定のフォルダに作成されていることを確認してみます。今回ディレクトリに指定した「temp_dir」は、前回のブログで作成したように、「C:\temp」フォルダになりますので、エクスプローラで確認すると、
確かに、「URIAGE_2014.DMP」「URIAGE_2015.DMP」ファイルが作成されていました。
それぞれ作成した外部表のデータ件数を確認してみます。
SELECT COUNT(*) FROM SYO_URIAGE_2014;
SELECT COUNT(*) FROM SYO_URIAGE_2015;
元の商品売上テーブルからは、過去の年度のデータは削除し、2016年度のデータのみを残すことにします。
DELETE FROM 商品売上
WHERE 年度 < '2016';
処理の上で、2016年度のデータを見ればよい場合は「商品売上」テーブルに対してSELECTを実施しますが、過去のデータも見たい場合には、外部表を使うことで検索できます。
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
FROM 商品売上
WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
FROM SYO_URIAGE_2015
WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
FROM SYO_URIAGE_2014
WHERE 商品CD = 'A0001'
ORDER BY 年度;
また、上記でユーザ「BLOG」によって作成された外部ファイル（ダンプファイル）を、別のユーザで使用するようなこともできます。
今回、「BLOG2」というユーザを作成し、「temp_dir」をBLOG2ユーザからも読み書きできるようにします。
CREATE USER BLOG2
IDENTIFIED BY< password> -- パスワードを設定します
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
GRANT READ, WRITE ON DIRECTORY temp_dir TO BLOG2;
作成したユーザー「BLOG2」でログインし、上記で作成した2015年度の商品売上データのダンプファイル（uriage_2015.dmp）を元にBLOG2ユーザ用の外部表「SYO_URIAGE2_2015」を作成してみます。
CREATE TABLE SYO_URIAGE2_2015
("商品CD" VARCHAR2(20 BYTE),
"年度" VARCHAR2(4 BYTE),
"売上4月" NUMBER(12,0),
"売上5月" NUMBER(12,0),
"売上6月" NUMBER(12,0),
"売上7月" NUMBER(12,0),
"売上8月" NUMBER(12,0),
"売上9月" NUMBER(12,0),
"売上10月" NUMBER(12,0),
"売上11月" NUMBER(12,0),
"売上12月" NUMBER(12,0),
"売上1月" NUMBER(12,0),
"売上2月" NUMBER(12,0),
"売上3月" NUMBER(12,0)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY TEMP_DIR
LOCATION
( 'uriage_2015.dmp'
)
);
作成した外部表を検索してみます。
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
FROM SYO_URIAGE2_2015
ORDER BY 商品CD;
データの検索ができました。
上記のように、一度作成した外部ファイル（ダンプファイル）は、別のユーザや別のデータベース環境に移動してそこで使用することも可能なんですね。
さらに、PREPROCESSOR句を使用すると、外部ファイルを圧縮したファイルに対して解凍しながら使用するということも可能なようです。
（※）注意点として、DATAPUMP(expdp)で作成したdmpファイルは外部表のダンプファイルとしては使用できないようです。
今日は以上まで
にほんブログ村