oracle

Oracle 外部表を使ってCSVファイルを参照する

今回の要件

Linux環境にて、国土交通省のサイトからダウンロードしたCSVファイル(Shift_JIS)を外部表として直接SQLから参照できるようにする。

今回は以下のサイトから大字・町丁目レベルの住所データCSVをダウンロードして利用します

位置参照情報 ダウンロードサービス

手順概要

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ファイルには1行目がヘッダー情報になっているので、実データは2行目以降となるので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
スポンサーリンク
コピペで使う