今回の要件
Linux環境にて、国土交通省のサイトからダウンロードしたCSVファイル（Shift_JIS）を外部表として直接SQLから参照できるようにする。
今回は以下のサイトから大字・町丁目レベルの住所データCSVをダウンロードして利用します
位置参照情報 ダウンロードサービス
nlftp.mlit.go.jp
手順概要
CSVファイルを配備するフォルダを作成
今回はCSVファイルを配備する場所として以下を作成しました。
mkdir /u01/app/oracle/external_tabl
CSVファイルを配備します
上記作成したフォルダへ国土交通省からダウンロードしたCSVファイルを配備します
$ wc 01_2021-sjis.csv
25981 25981 2538925 01_2021-sjis.csv
$Oracleにてディレクトリ作成
上記作成したフォルダをOracleからアクセスできるようにsystemユーザにて定義する
CREATE OR REPLACE DIRECTORY extables AS '/u01/app/oracle/external_table'
;
作成ディレクトリの確認
SELECT * FROM DBA_DIRECTORIES t where t.DIRECTORY_NAME ='EXTABLES' ;
SQL> SELECT * FROM DBA_DIRECTORIES t where t.DIRECTORY_NAME ='EXTABLES'
2 ;
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ---------------- ---------------------------------------- -------------
SYS EXTABLES /u01/app/oracle/external_table 0
SQL>アクセス権限の付与
今回はsooniスキーマからのCSVファイルアクセスを実現するので以下の通りsystemユーザから権限付与しています。
GRANT READ ON DIRECTORY EXTABLES TO sooni ;
外部表の作成
sooniスキーマにて以下外部表をcreateします。ORGANIZATION EXTERNAL部より下でCSVファイル側の情報を記載しています。（SQL*Loaderのコントロールファイルを記載する要領です）
今回のCSVファイル名は01_2021-sjis.csvとします。
CSVファイルはShift_JISで作成されているのでCHARACTERSET JA16SJISTILDEとしています。国土交通省からダウンロードするCSVファイルには１行目がヘッダー情報になっているので、実データは２行目以降となるのでSKIP 1 としています。
CREATE TABLE EXT_ADDRESS_CHOME
(
PREFECTURES_CODE VARCHAR2(2 char),
PREFECTURES_NAME VARCHAR2(32 char),
CITY_CODE VARCHAR2(5 char),
CITY_NAME VARCHAR2(64 char),
ADDRESS_CODE VARCHAR2(12 char),
OAZA_TOWN_CHOME_NAME VARCHAR2(32 char),
LATITUDE NUMBER(11,8),
LONGITUDE NUMBER(11,8),
DOCUMENT_CODE VARCHAR2(2),
CHOME_KUBUN_CODE VARCHAR2(2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTABLES
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET JA16SJISTILDE
SKIP 1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
PREFECTURES_CODE CHAR(2),
PREFECTURES_NAME CHAR(32),
CITY_CODE CHAR(5),
CITY_NAME CHAR(64),
ADDRESS_CODE CHAR(12),
OAZA_TOWN_CHOME_NAME CHAR(64),
LATITUDE DECIMAL EXTERNAL,
LONGITUDE DECIMAL EXTERNAL,
DOCUMENT_CODE CHAR(2),
CHOME_KUBUN_CODE CHAR(2)
)
)
LOCATION ('01_2021-sjis.csv')
);
外部表の確認
select t.OWNER,t.TABLE_NAME,t.TYPE_NAME,t.DEFAULT_DIRECTORY_NAME,t.ACCESS_TYPE from DBA_EXTERNAL_TABLES t ;
select ACCESS_PARAMETERS from DBA_EXTERNAL_TABLES t where TABLE_NAME ='EXT_ADDRESS_CHOME' ;
確認例
SQL> select t.OWNER,t.TABLE_NAME,t.TYPE_NAME,t.DEFAULT_DIRECTORY_NAME,t.ACCESS_TYPE
2 --,t.ACCESS_PARAMETERS
3 from DBA_EXTERNAL_TABLES t
4 ;
OWNER TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_ ACCESS_TYPE
---------- -------------------- ------------------ ------------------ ----------------
SYS OPATCH_XML_INV ORACLE_LOADER OPATCH_SCRIPT_DIR CLOB
SOONI EXT_ADDRESS_CHOME ORACLE_LOADER EXTABLES CLOB
SQL> select ACCESS_PARAMETERS from DBA_EXTERNAL_TABLES t
2 where TABLE_NAME ='EXT_ADDRESS_CHOME'
3 ;
ACCESS_PARAMETERS
----------------------------------------------------------------------
RECORDS DELIMITED BY NEWLINE
CHARACTERSET JA16SJISTILDE
SKIP 1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
PREFECTURES_CODE CHAR(2),
PREFECTURES_NAME CHAR(32),
CITY_CODE CHAR(5),
CITY_NAME CHAR(64),
ADDRESS_CODE CHAR(12),
OAZA_TOWN_CHOME_NAME CHAR(64),
LATITUDE DECIMAL EXTERNAL,
LONGITUDE DECIMAL EXTERNAL,
DOCUMENT_CODE CHAR(2),
CHOME_KUBUN_CODE CHAR(2)
)
SQL>外部表を通してCSVへアクセス
以下の通りselect文でアクセスできる事確認できました。（今回はTYPE ORACLE_LOADERを利用しているので参照しかできません）
SQL> select * from EXT_ADDRESS_CHOME t order by t.ADDRESS_CODE
2 fetch first 5 rows only
3 ;
PREF PREFECTURE CITY_CODE CITY_NAME ADDRESS_CODE OAZA_TOWN_CHOME_NAME LATITUDE LONGITUDE DOCU CHOM
---- ---------- ---------- ---------------- -------------- -------------------- ---------- ---------- ---- ----
01 北海道 01101 札幌市中央区 011010001001 旭ケ丘一丁目 43.04223 141.319722 0 3
01 北海道 01101 札幌市中央区 011010001002 旭ケ丘二丁目 43.039768 141.321733 0 3
01 北海道 01101 札幌市中央区 011010001003 旭ケ丘三丁目 43.039569 141.319617 0 3
01 北海道 01101 札幌市中央区 011010001004 旭ケ丘四丁目 43.038819 141.32304 0 3
01 北海道 01101 札幌市中央区 011010001005 旭ケ丘五丁目 43.036547 141.322217 0 3
SQL>アクセスするセッション毎にログファイルが作成されます
内部的には、外部表をselectしたタイミングで、CSVファイルが都度ロードされているようです。このタイミングでセッション毎（プロセス毎）にログファイルができるので、定期的なお掃除処理を入れる必要がありそうです。
[oracle@vm102 external_table]$ pwd
/u01/app/oracle/external_table
[oracle@vm102 external_table]$ ls -lt
合計 2508
-rw-r--r--. 1 oracle oinstall 2529 3月 4 16:32 EXT_ADDRESS_CHOME_20697.log
-rw-r--r--. 1 oracle oinstall 17703 3月 4 16:32 EXT_ADDRESS_CHOME_18827.log
-rw-r--r--. 1 oracle oinstall 2527 3月 4 15:55 EXT_ADDRESS_CHOME_9084.log
-rw-r--r--. 1 oracle oinstall 2538925 3月 3 2022 01_2021-sjis.csv
[oracle@vm102 external_table]$SQL*Plus用
set lin 200 COLUMN owner FORMAT A10 COLUMN directory_name FORMAT A16 COLUMN directory_path FORMAT A40 COLUMN OWNER FORMAT A10 COLUMN TABLE_NAME FORMAT A20 COLUMN TYPE_NAME FORMAT A18 COLUMN DEFAULT_DIRECTORY_NAME FORMAT A18 COLUMN ACCESS_TYPE FORMAT A8 COLUMN ACCESS_TYPE FORMAT A16 COLUMN PREFECTURES_NAME FORMAT A10 COLUMN CITY_NAME FORMAT A16 COLUMN OAZA_TOWN_CHOME_NAME FORMAT A20 COLUMN ADDRESS_CODE FORMAT A14
