18 外部表の例
次の表に、様々なアクセス・ドライバおよびアクセス方法を使用して外部表を作成する方法の例を示します。
表18-1 外部表の使用例の参照先
| 次のことを示す例... | 参照先... |
|---|---|
ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成
|
例18-1 |
ORACLE_DATAPUMPアクセス・ドライバを使用したサブパーティション化された外部表の作成および仮想化列を使用した表のパーティション化
|
例18-2 |
ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
|
例18-3 |
ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
|
例18-4 |
| アクセス・パラメータを使用しないCSVファイルからのデータのロード | 例18-6 |
アクセス・パラメータを使用しないCSVファイルからのデータのロード。加えて:
|
例18-7 |
アクセス・パラメータを使用しないCSVファイルからのデータのロード。加えて:
|
例18-8 |
| 最初のファイルにのみフィールド名の行が含まれることを除き、前の例と同様のデータのロード | 例18-9 |
| 2つのデータ・ファイルを使用したデータのロード | 例18-10 |
| データ・ファイルのほとんどのフィールドでアクセス・パラメータのデフォルト設定が使用され、少数のフィールドでデフォルト設定が使用されない場合のデータのロード | 例18-11 |
| LOBのロード | 例18-5 |
| データ型変換によって問題が発生する仕組み | 外部表の使用時のデータ型の変換 |
- ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成
- ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成
- ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
- ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
- 外部表からのLOBのロード
- 外部表からのCSVファイルのロード
親トピック: 外部表
18.1 ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成
例18-1 ORACLE_LOADERを使用したパーティション化された外部表の作成
この例では、次の内容の4つのデータ・ファイルがあると仮定しています。
p1a.dat:
1, AAAAA Plumbing,01372,
28, Sparkly Laundry,78907,
13, Andi's Doughnuts,54570,
p1b.dat:
51, DIY Supplies,61614,
87, Fast Frames,22201,
89, Friendly Pharmacy,89901,
p2.dat:
121, Pleasant Pets,33893,
130, Bailey the Bookmonger,99915,
105, Le Bistrot du Chat Noir,94114,
p3.dat:
210, The Electric Eel Diner,07101,
222, Everyt'ing General Store,80118,
231, Big Rocket Market,01754,データ・ファイルには、CUSTOMER_NUMBER、CUSTOMER_NAMEおよびPOSTAL_CODEの3つのフィールドがあります。外部表ではCUSTOMER_NUMBERに対してレンジ・パーティション化を使用して3つのパーティションを作成します。
-
パーティション1、100未満の
customer_number用 -
パーティション2、200未満の
customer_number用 -
パーティション3、300未満の
customer_number用
最初のパーティションのデータ・ファイルは2つで、他のパーティションでは1つのみであることに注意してください。次に、ファイルを作成する場合のSQLPlusからの出力を示します。
SQL> create table customer_list_xt
2 (CUSTOMER_NUMBER number, CUSTOMER_NAME VARCHAR2(50), POSTAL_CODE CHAR(5))
3 organization external
4 (type oracle_loader default directory def_dir1)
5 partition by range(CUSTOMER_NUMBER)
6 (
7 partition p1 values less than (100) location('p1a.dat', 'p1b.dat'),
8 partition p2 values less than (200) location('p2.dat'),
9 partition p3 values less than (300) location('p3.dat')
10 );
Table created.
SQL> 次に、表全体に対するSELECT *からの出力を示します。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_xt
3 order by customer_number;
CUSTOMER_NUMBER CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 54570
28 Sparkly Laundry 78907
51 DIY Supplies 61614
87 Fast Frames 22201
89 Friendly Pharmacy 89901
105 Le Bistrot du Chat Noir 94114
121 Pleasant Pets 33893
130 Bailey the Bookmonger 99915
210 The Electric Eel Diner 07101
222 Everyt'ing General Store 80118
231 Big Rocket Market 01754
12 rows selected.
SQL> 次の問合せでは、最初のパーティションのレコードのみが読み取られます。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_xt
3 where customer_number < 20
4 order by customer_number;
CUSTOMER_NUMBER CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 54570
2 rows selected.
SQL> 次の問合せでは、SELECT文の一部として読み取るパーティションを指定します。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_xt partition (p1)
3 order by customer_number;
CUSTOMER_NUMBER CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 54570
28 Sparkly Laundry 78907
51 DIY Supplies 61614
87 Fast Frames 22201
89 Friendly Pharmacy 89901
6 rows selected.
SQL> 親トピック: 外部表の例
18.2 ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成
この項の例では、サブパーティション化された外部表を作成する方法を示し、さらに仮想列を使用して表をパーティション化する方法も示します。
例18-2 ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成
この例で使用するダンプ・ファイルは、前の例でORACLE_LOADERアクセス・ドライバを使用して作成されたものと同じです。ただし、この例では、customer_numberを使用してデータをパーティション化するだけでなく、postal_codeを使用してデータをサブパーティション化します。すべてのパーティションに、postal_codeが50000未満のサブパーティションと、postal_codeが他のすべての値の別のサブパーティションがあります。それぞれ2つのサブパーティションを含む3つのパーティションでは、合計で6つのファイルが必要となります。ファイルを作成するために、SQL CREATE TABLE AS SELECTを使用してパーティションの正しい行を選択し、それらの行をORACLE_DATAPUMPドライバのファイルに書き込みます。
次の文では、パーティションp1 (customer_numberが100未満)の最初のサブパーティション(postal_codeが50000未満)のデータを含むファイルが作成されます。
SQL> create table customer_list_dp_p1_sp1_xt
2 organization external
3 (type oracle_datapump default directory def_dir1 location('p1_sp1.dmp'))
4 as
5 select customer_number, customer_name, postal_code
6 from customer_list_xt partition (p1)
7 where to_number(postal_code) < 50000;
Table created.
SQL> この文では、パーティションp1 (customer_numberが100未満)の2番目のサブパーティション(postal_codeが他のすべての値)のデータを含むファイルが作成されます。
SQL> create table customer_list_dp_p1_sp2_xt
2 organization external
3 (type oracle_datapump default directory def_dir1 location('p1_sp2.dmp'))
4 as
5 select customer_number, customer_name, postal_code
6 from customer_list_xt partition (p1)
7 where to_number(postal_code) >= 50000;
Table created. 次のように、他のパーティションのファイルは同様に作成されます。
SQL> create table customer_list_dp_p2_sp1_xt
2 organization external
3 (type oracle_datapump default directory def_dir1 location('p2_sp1.dmp'))
4 as
5 select customer_number, customer_name, postal_code
6 from customer_list_xt partition (p2)
7 where to_number(postal_code) < 50000;
Table created.
SQL>
SQL> create table customer_list_dp_p2_sp2_xt
2 organization external
3 (type oracle_datapump default directory def_dir1 location('p2_sp2.dmp'))
4 as
5 select customer_number, customer_name, postal_code
6 from customer_list_xt partition (p2)
7 where to_number(postal_code) >= 50000;
Table created.
SQL>
SQL> create table customer_list_dp_p3_sp1_xt
2 organization external
3 (type oracle_datapump default directory def_dir1 location('p3_sp1.dmp'))
4 as
5 select customer_number, customer_name, postal_code
6 from customer_list_xt partition (p3)
7 where to_number(postal_code) < 50000;
Table created.
SQL>
SQL> create table customer_list_dp_p3_sp2_xt
2 organization external
3 (type oracle_datapump default directory def_dir1 location('p3_sp2.dmp'))
4 as
5 select customer_number, customer_name, postal_code
6 from customer_list_xt partition (p3)
7 where to_number(postal_code) >= 50000;
Table created.
SQL>
これらの各外部表から選択して、書き出すデータが含まれているかどうかを確認できます。SQL CREATE TABLE AS SELECT文を実行した後、これらの外部表を削除できます。
仮想列を使用して表をパーティション化するために、パーティション化されたORACLE_DATAPUMP表を作成します。この例でも、表はcustomer_number列でパーティション化され、postal_code列でサブパーティション化されます。postal_code列は数字を含む文字列フィールドですが、この例では、文字列ではなく数値に基づいてこの列をパーティション化します。このことを行うために、値がNUMBERデータ型に変換されるpostal_codeフィールドの仮想列postal_code_numを作成します。SUBPARTITION句では、行のサブパーティションを決定するために仮想列を使用します。
SQL> create table customer_list_dp_xt
2 (customer_number number,
3 CUSTOMER_NAME VARCHAR2(50),
4 postal_code CHAR(5),
5 postal_code_NUM as (to_number(postal_code)))
6 organization external
7 (type oracle_datapump default directory def_dir1)
8 partition by range(customer_number)
9 subpartition by range(postal_code_NUM)
10 (
11 partition p1 values less than (100)
12 (subpartition p1_sp1 values less than (50000) location('p1_sp1.dmp'),
13 subpartition p1_sp2 values less than (MAXVALUE) location('p1_sp2.dmp')),
14 partition p2 values less than (200)
15 (subpartition p2_sp1 values less than (50000) location('p2_sp1.dmp'),
16 subpartition p2_sp2 values less than (MAXVALUE) location('p2_sp2.dmp')),
17 partition p3 values less than (300)
18 (subpartition p3_sp1 values less than (50000) location('p3_sp1.dmp'),
19 subpartition p3_sp2 values less than (MAXVALUE) location('p3_sp2.dmp'))
20 );
Table created.
SQL> すべての行を選択した場合、返されるデータはORACLE_LOADERアクセス・ドライバを使用した前の例で返されたデータと同じになります。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_dp_xt
3 order by customer_number;
customer_number CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 54570
28 Sparkly Laundry 78907
51 DIY Supplies 61614
87 Fast Frames 22201
89 Friendly Pharmacy 89901
105 Le Bistrot du Chat Noir 94114
121 Pleasant Pets 33893
130 Bailey the Bookmonger 99915
210 The Electric Eel Diner 07101
222 Everyt'ing General Store 80118
231 Big Rocket Market 01754
12 rows selected.
SQL> WHERE句では、読み取る行を1つのサブパーティションに制限できます。次の問合せでは、最初のパーティションの最初のサブパーティションのみが読み取られます。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_dp_xt
3 where customer_number < 20 and postal_code_NUM < 39998
4 order by customer_number;
customer_number CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
1 row selected.
SQL> 次のように、問合せで特定のパーティションを指定することもできます。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_dp_xt subpartition (p2_sp2) order by customer_number;
customer_number CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
105 Le Bistrot du Chat Noir 94114
130 Bailey the Bookmonger 99915
2 rows selected.
SQL> 親トピック: 外部表の例
18.3 ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
この項で説明するこの例では、ORACLE_HDFSアクセス・ドライバを使用してパーティション化された外部表を作成する方法を示します。
例18-3 ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
この例では、HDFSディレクトリ・パス"hdfs_pet/"に格納された4つのデータ・ファイルがあります。
p1a.dat
1, AAAAA Plumbing,01372,
28, Sparkly Laundry,07101,
13, Andi'''s Doughnuts,01372,
p1b.dat
51, DIY Supplies,07101,
87, Fast Frames,01754,
89, Friendly Pharmacy,01372,
p2.dat
121, Pleasant Pets,01754,
130, Bailey the Bookmonger,01754,
105, Le Bistrot du Chat Noir,01754,
p3.dat
210, The Electric Eel Diner,07101,
222, Everyt'ing General Store,01372,
231, Big Rocket Market,01754,
この例の目的のために、データ・ファイルは次を使用してHDFSディレクトリに書き込まれます。
hadoop dfs -mkdir hdfs_pet
hadoop dfs -put p1a.dat hdfs_pet/p1a.dat
hadoop dfs -put p1b.dat hdfs_pet/p1b.dat
hadoop dfs -put p2.dat hdfs_pet/p2.dat
hadoop dfs -put p3.dat hdfs_pet/p3.dat
パーティション化された外部表を作成するためのCREATE TABLEコマンドは次のとおりです。
create table customer_list_hdfs
(CUSTOMER_NUMBER number, CUSTOMER_NAME VARCHAR2(50), POSTAL_CODE CHAR(5))
organization external
(type oracle_hdfs
default directory def_dir1
access parameters
(com.oracle.bigdata.cluster = hadoop_cl_1
com.oracle.bigdata.rowformat = delimited fields terminated by ','))
partition by range(CUSTOMER_NUMBER)
(
partition p1 values less than (100) location('hdfs_pet/p1a.dat', 'hdfs_pet/p1b.dat'),
partition p2 values less than (200) location('hdfs_pet/p2.dat'),
partition p3 values less than (300) location('hdfs_pet/p3.dat')
); 次の問合せは、外部表からのSELECT操作を示しています。
SQL> select * from customer_list_hdfs order by customer_number;
CUSTOMER_NUMBER CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 01372
28 Sparkly Laundry 07101
51 DIY Supplies 07101
87 Fast Frames 01754
89 Friendly Pharmacy 01372
105 Le Bistrot du Chat Noir 01754
121 Pleasant Pets 01754
130 Bailey the Bookmonger 01754
210 The Electric Eel Diner 07101
222 Everyt'ing General Store 01372
231 Big Rocket Market 01754
12 rows selected.
SQL> 次のように、WHERE句の条件に一致しないパーティションを読取りの対象から除外するWHERE句を使用して問合せを実行することもできます。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_hdfs
3 where customer_number < 20
4 order by customer_number;
CUSTOMER_NUMBER CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 01372
2 rows selected.
SQL> 次に示すように、FROM句の一部として読み取るパーティションを指定することもできます。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_hdfs partition (p3)
3 order by customer_number;
CUSTOMER_NUMBER CUSTOMER_NAME POSTA
--------------- -------------------------------------------------- -----
210 The Electric Eel Diner 07101
222 Everyt'ing General Store 01372
231 Big Rocket Market 01754
3 rows selected.
SQL> 親トピック: 外部表の例
18.4 ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
ORACLE_HIVE表のパーティション化された外部表を作成するには、パーティション化されたHive外部表が必要です。その後PL/SQLプロシージャDBMS_HADOOP.CREATE_EXTDDL_FOR_HIVE()を使用する必要があります。また、この例では、前の例で使用したHDFSファイルからパーティション化されたHive表を作成します。パーティション化されたHive表を作成する前に、データを格納するHDFSディクショナリを作成する必要があります。
例18-4 ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
この例では、ORACLE_HDFSを使用してパーティション化された外部表を作成する方法を示した前の例のデータ・ファイルを使用して、Hive表を作成します。次のコマンドはすべてHive CLI内で実行されるため、Hive構文を使用します。
まず、次のように、Hiveを使用して、HDFSデータ・ファイルに基づくHive外部表を作成します。
create external table customer_list_no_part ( customer_number int,
customer_name string,
postal_code string)
row format delimited fields terminated by ','
stored as textfile
location '/user/doc/hdfs_pet'次に、パーティションを動的に作成するようにHiveに指示するために、次のコマンドを実行します。
set hive.exec.dynamic.partition=true
set hive.exec.dynamic.partition.mode=nonstrict
パーティション化されたHive表の作成:
create table customer_list( CUSTOMER_NUMBER int,
CUSTOMER_NAME string)
partitioned by (postal_code string)
row format delimited
fields terminated by '\t'
location '/user/doc/doc_pet'Hive表に表customer_list_no_partのデータを移入します。これにより、パーティションごとに、正しい行を含むファイルが作成されます。
insert overwrite table customer_list partition (postal_code) select * from customer_list_no_part次の問合せは、Hive customer_list表に移入された行を示しています。
select * from customer_list order by customer_number
1 AAAAA Plumbing 01372
13 Andi's Doughnuts 01372
28 Sparkly Laundry 07101
51 DIY Supplies 07101
87 Fast Frames 01754
89 Friendly Pharmacy 01372
105 Le Bistrot du Chat Noir 01754
121 Pleasant Pets 01754
130 Bailey the Bookmonger 01754
210 The Electric Eel Diner 07101
222 Everyt'ing General Store 01372
231 Big Rocket Market 01754
これで、SQL*Plusに戻り、Oracle Database内にパーティション化された外部表を作成できます。まず、PL/SQLファンクションDBMS_HADOOP.CREATE_EXTDDL_FOR_HIVE()を使用して外部表を作成します。引数は次のとおりです。
-
Hadoopクラスタの名前
-
表を所有するHiveユーザーの名前
-
パーティション化されたHive表の名前
-
Hive表のパーティション列を外部表の列として含めるかどうかを示すブール値
-
作成されるパーティション化された
ORACLE_HIVE表の名前 -
CREATEDDLが実行されるかどうかを示すブール値 -
CREATEDDL文字列を含むCLOB
この例では、CREATE DDL文字列のCLOBを取得し、DBMS_OUTPUT.PUT_LINE()プロシージャを使用してこれを書き込みます。SERVEROUTPUT ONを設定することで、DBMS_OUTPUT.PUT_LINE()プロシージャからデータを表示するようSQL*Plusに指示します。LINESIZEを132に設定すると、DBMS_OUTPUT.PUT_LINE()プロシージャからデータを表示するときに不適切な場所に改行が挿入されません。
SQL> SET LINESIZE 132
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 DDLtxt clob;
3 BEGIN
4 dbms_hadoop.create_extddl_for_hive
5 ('hadoop_cl_1', 'default', 'customer_list',
6 TRUE, 'CUSTOMER_LIST_HIVE', TRUE, DDLtxt);
7 dbms_output.put_line('DDL Text is : ' || DDLtxt);
8 END;
9 /
External table successfully created.
DDL Text is : CREATE TABLE "DOC"."CUSTOMER_LIST_HIVE" (customer_number NUMBER, customer_name VARCHAR2(4000), postal_code
VARCHAR2(4000)) ORGANIZATION EXTERNAL
(TYPE ORACLE_HIVE
DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS
(
com.oracle.bigdata.cluster=hadoop_cl_1
com.oracle.bigdata.tablename=default.customer_list)
) REJECT LIMIT UNLIMITED
PARTITION BY
LIST (postal_code)
(
PARTITION "P_293620257" VALUES ('01372'),
PARTITION "P_292175793" VALUES ('01754'),
PARTITION "P_717839126"
VALUES ('07101')
)
HiveではSTRING列の最大文字数は指定されないため、外部表の列定義はVARCHAR2(4000)となります。一部の列の長さをより短くする場合は、DBMS_HADOOP.CREATE_EXTDDL_FOR_HIVE()プロシージャをコールし、CREATE DDLを実行しないことを指定します。次に、CLOBで返されたCREATE文を編集して、VARCHAR2列の長さをより適切な値に設定できます。
また、パーティション名の数字は変動する場合があります。
これで、表が作成され、SELECT *文を実行すると、すべての行が返されます。前に実行したSET LINESIZEは、SQL*Plusでcustomer_nameおよびpostal_codeに対して132文字が使用されることを意味します。
SQL> select * from customer_list_hive order by customer_number;
CUSTOMER_NUMBER
---------------
CUSTOMER_NAME
------------------------------------------------------------------------------------------------------------------------------POSTAL_CODE
------------------------------------------------------------------------------------------------------------------------------ 1
AAAAA Plumbing
01372
13
Andi's Doughnuts
01372
28
Sparkly Laundry
07101
51
DIY Supplies
07101
87
Fast Frames
01754
89
Friendly Pharmacy
01372
105
Le Bistrot du Chat Noir
01754
121
Pleasant Pets
01754
130
Bailey the Bookmonger
01754
210
The Electric Eel Diner
07101
222
Everyt'ing General Store
01372
231
Big Rocket Market
01754
12 rows selected.
SQL>
SQLの実行では、パーティション情報を使用してWHERE句の条件に一致しないパーティションが除外されます。
SQL> select customer_number, customer_name, postal_code
2 from customer_list_hive
3 where postal_code = '01754'
4 order by customer_number;
CUSTOMER_NUMBER
---------------
CUSTOMER_NAME
------------------------------------------------------------------------------------------------------------------------------POSTAL_CODE
------------------------------------------------------------------------------------------------------------------------------ 87
Fast Frames
01754
105
Le Bistrot du Chat Noir
01754
121
Pleasant Pets
01754
130
Bailey the Bookmonger
01754
231
Big Rocket Market
01754
5 rows selected.
SQL> 親トピック: 外部表の例
18.5 外部表からのLOBのロード
外部表は、単一のファイルから多数のレコードをロードし、各レコードが表の独自の行になるようにする場合に特に役立ちます。次の例に、このようなロードを実行する方法を示します。
例18-5 外部表からのLOBのロード
次のように、外部表my_ext_tableを定義するとします。
CREATE TABLE my_ext_table ( id NUMBER, author VARCHAR2(30), created DATE, text CLOB )
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY MY_DIRECTORY
ACCESS PARAMETERS (
RECORDS DELIMITED BY 0x'0A'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( id CHAR(10),
author CHAR(30),
created DATE "YYYY-MM-DD",
text CHAR(131071)
)
)
LOCATION (
MY_DIRECTORY:'external.dmp'
)
);external.dmpファイルの内容は次のとおりです。
1,Roger,2015-08-08,The quick brown fox jumps over the lazy dog
2,John,2012-01-01,"The angry aligator, acting alone, ate the antelope"ダンプ・ファイルの2番目の行には、全テキスト文字列を囲む引用符が必要です。引用符がない場合、フィールドはカンマで終了します。
ノート:
この例で使用されているダンプ・ファイルの問題ではありませんが、全テキスト文字列のいずれかの部分が引用符で囲まれている場合は、次のaloneという語のように、その部分をさらにもう1組の引用符で囲みます。 2,John,2012-01-01,"The angry aligator, acting ""alone"", ate the antelope"全テキストにレコード・デリミタ文字(0x'0A'、または改行)が含まれている場合は、文書ごとに個別のファイルを指定できます。外部表ではFILLERフィールドはサポートされていないため、かわりにCOLUMN TRANSFORMS句を使用して、fnameフィールドに外部ファイルの名前が含まれることを指定する必要があります。
DROP TABLE my_ext_table2;
CREATE TABLE my_ext_table2 ( id NUMBER, author VARCHAR2(30), created DATE, text CLOB )
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY MY_DIRECTORY
ACCESS PARAMETERS (
RECORDS DELIMITED BY 0x'0A'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( id CHAR(10),
author CHAR(30),
created DATE "YYYY-MM-DD",
fname char(100)
)
COLUMN TRANSFORMS (text FROM LOBFILE(fname) FROM (MY_DIRECTORY) )
)
LOCATION (
'loader.txt'
)
);
ノート:
FROM (MY_DIRECTORY)句はデフォルト・ディクショナリとしてすでに指定されているため、実際には必要ありません。ただし、loader.txtファイルと個々のCLOBファイルが異なる場所にある場合は必要となるため、ここでは例としての目的のために示しています。
外部表にデータが移入された後は、そのまま、その外部表に対して通常の表操作(DMLおよびほとんどのDDL)を実行するか、または外部表をステージング表として使用して標準の表にデータを取得できます。新しい標準の(外部でない)表を作成するには、次のSQL文を使用します。
CREATE TABLE normaltable AS SELECT * FROM externaltable;同様に、次のSQL文を使用して新しい標準の表にデータを挿入できます。
INSERT INTO normaltable AS SELECT * FROM externaltable;親トピック: 外部表の例
18.6 外部表からのCSVファイルのロード
このトピックでは、様々な条件で外部表からCSVファイルをロードする方法の例を示します。一部の例は、前の例に基づいて作成されています。
例18-6 アクセス・パラメータを使用しないCSVファイルからのデータのロード
この例には、次の条件があります。
-
表の列の順序は、データ・ファイルのフィールドの順序に一致する必要があります。
-
データ・ファイル内のレコードは、改行で終了する必要があります。
-
データ・ファイル内のレコードのフィールドはカンマで区切る必要があります(フィールド値が引用符で囲まれている場合は、フィールドから引用符を削除しません)。
-
フィールドの途中では改行文字を使用できません。
外部表のデータは次のとおりです。
events_all.csv
Winter Games,10-JAN-2010,10,
Hockey Tournament,18-MAR-2009,3,
Baseball Expo,28-APR-2009,2,
International Football Meeting,2-MAY-2009,14,
Track and Field Finale,12-MAY-2010,3,
Mid-summer Swim Meet,5-JUL-2010,4,
Rugby Kickoff,28-SEP-2009,6,外部表の定義は次のとおりです。
SQL> CREATE TABLE EVENTS_XT_1
2 (EVENT varchar2(30),
3 START_DATE date,
4 LENGTH number)
5 ORGANIZATION EXTERNAL
6 (default directory def_dir1 location ('events_all.csv'));
Table created.
次に、外部表EVENTS_XT_1でのSELECT操作を示します。
SQL> select START_DATE, EVENT, LENGTH
2 from EVENTS_XT_1
3 order by START_DATE;
START_DAT EVENT LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament 3
28-APR-09 Baseball Expo 2
02-MAY-09 International Football Meeting 14
28-SEP-09 Rugby Kickoff 6
10-JAN-10 Winter Games 10
12-MAY-10 Track and Field Finale 3
05-JUL-10 Mid-summer Swim Meet 4
7 rows selected.
SQL> 例18-7 データ・ファイルの日付フィールドの書式と一致しないセッションのデフォルトの日付マスク
この例は、セッションのデフォルトの日付マスクがデータ・ファイルの日付フィールドの書式と一致しないことを除いて前の例と同じです。次の例では、セッションの日付書式はDD-Mon-YYYYですが、データ・ファイルの日付書式はMM/DD/YYYYです。外部表定義に日付マスクがない場合、ORACLE_LOADERアクセス・ドライバはセッションの日付マスクを使用してデータ・ファイルの文字データを日付データ型に変換しようとします。または、外部表の日付列のロードに使用されるデータ・ファイルのすべてのフィールドに使用する日付マスクのアクセス・パラメータを指定します。
次に、外部表のデータ・ファイルの内容を示します。
events_all_date_fmt.csv
Winter Games,1/10/2010,10
Hockey Tournament,3/18/2009,3
Baseball Expo,4/28/2009,2
International Football Meeting,5/2/2009,14
Track and Field Finale,5/12/2009,3
Mid-summer Swim Meet,7/5/2010,4
Rugby Kickoff,9/28/2009,6外部表の定義は次のとおりです。
SQL> CREATE TABLE EVENTS_XT_2
2 (EVENT varchar2(30),
3 START_DATE date,
4 LENGTH number)
5 ORGANIZATION EXTERNAL
6 (default directory def_dir1
7 access parameters (fields date_format date mask "mm/dd/yyyy")
8 location ('events_all_date_fmt.csv'));
Table created.
SQL> 次に、外部表EVENTS_XT_2でのSELECT操作を示します。
SQL> select START_DATE, EVENT, LENGTH
2 from EVENTS_XT_2
3 order by START_DATE;
START_DAT EVENT LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament 3
28-APR-09 Baseball Expo 2
02-MAY-09 International Football Meeting 14
12-MAY-09 Track and Field Finale 3
28-SEP-09 Rugby Kickoff 6
10-JAN-10 Winter Games 10
05-JUL-10 Mid-summer Swim Meet 4
7 rows selected.
例18-8 データが2つのデータ・ファイルに分割されている場合
この例は、次のことを除き、この項の最初の例と同じです。
-
データが2つのデータ・ファイルに分割されています。
-
各データ・ファイルにフィールドの名前を含む行が格納されています。
-
データ・ファイルの一部のフィールドが引用符で囲まれています。
FIELD NAMES ALL FILESは、アクセス・ドライバに対し、各ファイルの最初の行にファイルのフィールドの名前を含む行が含まれることを示します。アクセス・ドライバはフィールドの名前を表の列の名前と照合します。したがって、ファイルのフィールドの順序が表の列の順序と同じである必要はありません。最初の行内のフィールド名が引用符で囲まれていない場合、アクセス・ドライバでは、表の列名と照合する前に、フィールド名を大文字に変更します。フィールド名が引用符で囲まれている場合、アクセス・ドライバでは、一致する名前を検索する前に、フィールド名を大文字に変更しません。
フィールドは引用符で囲まれているため、アクセス・パラメータを使用する場合は、CSV WITHOUT EMBEDDED RECORD TERMINATORS句が必要になります。この句では、次のことを指定しています。
-
データ・ファイルのフィールドはカンマで区切られています。
-
フィールドが二重引用符で囲まれている場合、アクセス・ドライバはフィールド値からそれらを削除します。
-
フィールド値に改行は埋め込まれていません(このオプションを使用すると、アクセス・ドライバは外部表での
SELECT操作のパフォーマンスが低下する可能性がある一部のチェックをスキップできます)。
2つのデータ・ファイルは次のとおりです。
events_1.csv
"EVENT","START DATE","LENGTH",
"Winter Games", "10-JAN-2010", "10"
"Hockey Tournament", "18-MAR-2009", "3"
"Baseball Expo", "28-APR-2009", "2"
"International Football Meeting", "2-MAY-2009", "14"events_2.csv
Event,Start date,Length,
Track and Field Finale, 12-MAY-2009, 3
Mid-summer Swim Meet, 5-JUL-2010, 4
Rugby Kickoff, 28-SEP-2009, 6外部表の定義は次のとおりです。
SQL> CREATE TABLE EVENTS_XT_3
2 ("START DATE" date,
3 EVENT varchar2(30),
4 LENGTH number)
5 ORGANIZATION EXTERNAL
6 (default directory def_dir1
7 access parameters (records field names all files
8 fields csv without embedded record terminators)
9 location ('events_1.csv', 'events_2.csv'));
Table created.
次に、EVENTS_XT_3外部表でのSELECT操作の結果を示します。
SQL> select "START DATE", EVENT, LENGTH
2 from EVENTS_XT_3
3 order by "START DATE";
START DAT EVENT LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament 3
28-APR-09 Baseball Expo 2
02-MAY-09 International Football Meeting 14
12-MAY-09 Track and Field Finale 3
28-SEP-09 Rugby Kickoff 6
10-JAN-10 Winter Games 10
05-JUL-10 Mid-summer Swim Meet 4
7 rows selected.
例18-9 データが2つのファイルに分割され、最初のファイルにのみフィールド名の行がある場合
この例は、最初のファイルにのみフィールド名の行があることを除き、例3と同じです。2番目のファイルの最初の行には、実際のデータが含まれています。RECORDS句は「field names first file」に変更されます。
2つのデータ・ファイルは次のとおりです。
events_1.csv (same as for example 3)
"EVENT","START DATE","LENGTH",
"Winter Games", "10-JAN-2010", "10"
"Hockey Tournament", "18-MAR-2009", "3"
"Baseball Expo", "28-APR-2009", "2"
"International Football Meeting", "2-MAY-2009", "14"events_2_no_header_row.csv
Track and Field Finale, 12-MAY-2009, 3
Mid-summer Swim Meet, 5-JUL-2010, 4
Rugby Kickoff, 28-SEP-2009, 6
外部表の定義は次のとおりです。
SQL> CREATE TABLE EVENTS_XT_4
2 ("START DATE" date,
3 EVENT varchar2(30),
4 LENGTH number)
5 ORGANIZATION EXTERNAL
6 (default directory def_dir1
7 access parameters (records field names first file
8 fields csv without embedded record terminators)
9 location ('events_1.csv', 'events_2_no_header_row.csv'));
Table created.
次に、EVENTS_XT_4外部表でのSELECT操作を示します。
SQL> select "START DATE", EVENT, LENGTH
2 from EVENTS_XT_4
3 order by "START DATE";
START DAT EVENT LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament 3
28-APR-09 Baseball Expo 2
02-MAY-09 International Football Meeting 14
12-MAY-09 Track and Field Finale 3
28-SEP-09 Rugby Kickoff 6
10-JAN-10 Winter Games 10
05-JUL-10 Mid-summer Swim Meet 4
7 rows selected.
例18-10 ファイルのフィールドの順序が表の列の順序と一致する場合
この例には、次の条件があります。
-
ファイルのフィールドの順序は表の列の順序と一致します。
-
フィールドは改行で区切られ、オプションで二重引用符で囲まれています。
-
値に改行が埋め込まれたフィールドがあり、それらのフィールドは二重引用符で囲まれています。
データ・ファイルの内容は次のとおりです。
event_contacts_1.csv
Winter Games, 10-JAN-2010, Ana Davis,
Hockey Tournament, 18-MAR-2009, "Daniel Dube
Michel Gagnon",
Baseball Expo, 28-APR-2009, "Robert Brown"
Internation Football Meeting, 2-MAY-2009,"Pete Perez
Randall Barnes
Melissa Gray",
event_contacts_2.csv
Track and Field Finale, 12-MAY-2009, John Taylor,
Mid-summer Swim Meet, 5-JUL-2010, "Louise Stewart
Cindy Sanders"
Rugby Kickoff, 28-SEP-2009, "Don Nguyen
Ray Lavoie"表定義は次のとおりです。CSV WITH EMBEDDED RECORD TERMINATORS句では、二重引用符で囲まれ、改行も埋め込まれているフィールドの処理方法をアクセス・ドライバに指示します。
SQL> CREATE TABLE EVENTS_CONTACTS_1
2 (EVENT varchar2(30),
3 START_DATE date,
4 CONTACT varchar2(120))
5 ORGANIZATION EXTERNAL
6 (default directory def_dir1
7 access parameters (fields CSV with embedded record terminators)
8 location ('event_contacts_1.csv', 'event_contacts_2.csv'));
Table created.
次に、EVENT_CONTACTS_1外部表でのSELECT操作の結果を示します。
SQL> column contact format a30
SQL> select START_DATE, EVENT, CONTACT
2 from EVENTS_CONTACTS_1
3 order by START_DATE;
START_DAT EVENT CONTACT
--------- ------------------------------ ------------------------------
18-MAR-09 Hockey Tournament Daniel Dube
Michel Gagnon
28-APR-09 Baseball Expo Robert Brown
02-MAY-09 Internation Football Meeting Pete Perez
Randall Barnes
Melissa Gray
12-MAY-09 Track and Field Finale John Taylor
28-SEP-09 Rugby Kickoff Don Nguyen
Ray Lavoie
10-JAN-10 Winter Games Ana Davis
05-JUL-10 Mid-summer Swim Meet Louise Stewart
Cindy Sanders
7 rows selected.
例18-11 データ・ファイルの一部のフィールドでアクセス・パラメータのデフォルト設定が使用されない場合
この例では、データ・ファイルのほとんどのフィールドでアクセス・パラメータのデフォルト設定が使用されるものの、少数のフィールドではデフォルト設定が使用されない場合に行う操作を示します。この例では、すべてのフィールドの設定を一覧表示するのではなく、デフォルトとは異なるフィールドの属性のみを設定する方法を示します。 相違点は以下のとおりです。
-
2つの日付フィールドがあり、その1つではセッションの書式が使用されますが、
registration_deadlineでは異なる書式が使用されます。 -
registration_deadlineでは、NULL値を示すためにNONEの値も使用されます。
データ・ファイルの内容は次のとおりです。
events_reg.csv
Winter Games,10-JAN-2010,10,12/1/2009,
Hockey Tournament,18-MAR-2009,3,3/11/2009,
Baseball Expo,28-APR-2009,2,NONE
International Football Meeting,2-MAY-2009,14,3/1/2009
Track and Field Finale,12-MAY-2010,3,5/10/010
Mid-summer Swim Meet,5-JUL-2010,4,6/20/2010
Rugby Kickoff,28-SEP-2009,6,NONE 表定義は次のとおりです。ALL FIELDS OVERRIDE句を使用すると、そのフィールドの情報を指定しながら、残りのフィールドでデフォルトを使用できます。残りのフィールドのデータ型はCHAR(255)で、フィールド・データはカンマで終了し、切捨てオプションはLDRTRIMになります。
SQL> CREATE TABLE EVENT_REGISTRATION_1
2 (EVENT varchar2(30),
3 START_DATE date,
4 LENGTH number,
5 REGISTRATION_DEADLINE date)
6 ORGANIZATION EXTERNAL
7 (default directory def_dir1
8 access parameters
9 (fields all fields override
10 (REGISTRATION_DEADLINE CHAR (10) DATE_FORMAT DATE MASK "mm/dd/yyyy"
11 NULLIF REGISTRATION_DEADLINE = 'NONE'))
12 location ('events_reg.csv'));
Table created.
次に、EVENT_REGISTRATION_1外部表でのSELECT操作の結果を示します。
SQL> select START_DATE, EVENT, LENGTH, REGISTRATION_DEADLINE
2 from EVENT_REGISTRATION_1
3 order by START_DATE;
START_DAT EVENT LENGTH REGISTRAT
--------- ------------------------------ ---------- ---------
18-MAR-09 Hockey Tournament 3 11-MAR-09
28-APR-09 Baseball Expo 2
02-MAY-09 International Football Meeting 14 01-MAR-09
28-SEP-09 Rugby Kickoff 6
10-JAN-10 Winter Games 10 01-DEC-09
12-MAY-10 Track and Field Finale 3 10-MAY-10
05-JUL-10 Mid-summer Swim Meet 4 20-JUN-10
7 rows selected.
親トピック: 外部表の例