19 外部表の例
これらの例から、Oracle DatabaseおよびBig Dataのデータの問合せにアクセス・ドライバのORACLE_LOADER、ORACLE_DATAPUMP、ORACLE_HDFSおよびORACLE_HIVEを使用する方法を学習します。
- ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成
このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化された外部表を作成する方法について説明します。 - ORACLE_LOADERアクセス・ドライバを使用したパーティション化されたハイブリッド表の作成
このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化されたハイブリッド表を作成する方法について説明します。 - ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成
サブパーティション化された外部表、および仮想列を含むパーティション表をORACLE_DATAPUMPアクセス・ドライバを使用して作成する方法について説明します。 - ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
この項の例はORACLE_HDFSアクセス・ドライバを使用してパーティション化された外部表を作成する方法を示しています。 - ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
ORACLE_HIVE表に対してパーティション化された外部表を作成するには、パーティション化されたHive外部表が必要です。 - ORA_PARTITION_VALIDATIONファンクションを使用したパーティション化された外部表の検証
パーティション化された外部表の行が正しいパーティションかどうかを確認するには、ORA_PARTITION_VALIDATIONファンクションを使用します。 - ファイル・パスにパーティション値を含めることによる外部表に対するSQL*Loaderの使用
オブジェクト・ストア内の多数のデータ・ファイルの管理を促進するには、ファイル・パスの一部としてフォルダ名を含めて外部表パーティション化を使用します。外部表の列で、行ごとにソース・ファイルのファイル名を返すこともできます。 - 外部表でのLOBのロード
外部表は、単一のファイルから多数のレコードをロードし、各レコードが表の独自の行になるようにする場合に特に役立ちます。 - 外部表からのCSVファイルのロード
このトピックでは、様々な条件で外部表からCSVファイルをロードする方法の例を示します。
親トピック: 外部表
19.1 ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成
このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化された外部表を作成する方法について説明します。
例19-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> 親トピック: 外部表の例
19.2 ORACLE_LOADERアクセス・ドライバを使用したパーティション化されたハイブリッド表の作成
このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化されたハイブリッド表を作成する方法について説明します。
ハイブリッド・パーティション表は、データベース・セグメント内および外部のファイルまたはソース内に一部のパーティションが存在できるようにすることで、Oracle Partitioningを拡張する機能です。これにより、表の大部分が外部パーティションに存在できるようになり、Big Data SQLのパーティション化の機能が大幅に向上します。
例19-2 例
次に、パーティション化されたハイブリッド表を作成する文の例を示します。
CREATE TABLE hybrid_pt (time_id date, customer number)
TABLESPACE TS1
EXTERNAL PARTITION ATTRIBUTES (TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir0
ACCESS PARAMETERS(FIELDS TERMINATED BY ',')
REJECT LIMIT UNLIMITED)
PARTITION by range (time_id)
(
PARTITION century_18 VALUES LESS THAN ('01-01-1800')
EXTERNAL, <-- empty external partition
PARTITION century_19 VALUES LESS THAN ('01-01-1900')
EXTERNAL DEFAULT DIRECTORY data_dir1 LOCATION (‘century19_data.txt'),
PARTITION century_20 VALUES LESS THAN ('01-01-2000')
EXTERNAL LOCATION (‘century20_data.txt'),
PARTITION year_2000 VALUES LESS THAN ('01-01-2001') TABLESPACE TS2,
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
この例では、表に内部パーティションと外部パーティションの両方が含まれています。表の内部パーティションのデフォルト表領域は、TS1です。表内の外部パーティションに表レベルで適用されるパラメータを指定するために、EXTERNAL PARTITION ATTRIBUTES句が追加されます。ハイブリッド・パーティション表の場合、この句は必須です。この場合、外部パーティションはORACLE_LOADERアクセス・ドライバを介してアクセスされ、アクセス・ドライバで必要なパラメータは句で指定されます。パーティション・レベルで、EXTERNAL句は、パーティションに適用されるすべての外部パラメータとともに、外部パーティションごとに指定されます。
この例では、century_18、century_19およびcentury_20は外部パーティションです。century_18は、場所が含まれていないため空のパーティションです。パーティションcentury_19のデフォルト・ディレクトリはdata_dir1であり、表レベルのデフォルト・ディレクトリよりも優先されます。パーティションの場所はdata_dir1:century19_data.txtになります。パーティションにデフォルト・ディレクトリが指定されていない場合、表レベルのデフォルト・ディレクトリが場所に適用されるため、パーティションcentury_20の場所はdata_dir0:century20_data.txtになります。パーティションyear_2000およびpmaxは、内部パーティションです。パーティションyear_2000にはtablespace TS2が含まれます。パーティションにEXTERNAL句または外部パラメータが指定されていない場合、デフォルトで内部パーティションとみなされます。
親トピック: 外部表の例
19.3 ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成
サブパーティション化された外部表、および仮想列を含むパーティション表をORACLE_DATAPUMPアクセス・ドライバを使用して作成する方法について説明します。
ノート:
Oracle Database 23c以降、ORACLE_DATAPUMPアクセス・ドライバでは、ハイブリッド・パーティション表(HyPT)のサポートのために、間隔、自動リストおよびコンポジット・パーティション化のオプションが提供されます。詳細は、Oracle Database VLDBおよびパーティショニング・ガイドを参照してください。
例19-3 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列は数字を含む文字列フィールドですが、この例では、文字列ではなく数値に基づいてこの列をパーティション化します。これを行うために、仮想列postal_code_numを作成します。その値は、NUMBERデータ型に変換されたpostal_codeフィールドです。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> 関連トピック
親トピック: 外部表の例
19.4 ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
この項で説明する例では、ORACLE_HDFSアクセス・ドライバを使用してパーティション化された外部表を作成する方法を示します。
例19-4 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> 親トピック: 外部表の例
19.5 ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
ORACLE_HIVE表のパーティション化された外部表を作成するには、パーティション化されたHive外部表が必要です。
パーティション化されたHive外部表を設定した後は、PL/SQLプロシージャDBMS_HADOOP.CREATE_EXTDDL_FOR_HIVE()を使用する必要があります。この例では、ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成で使用したHDFSファイルから、パーティション化されたHive表を作成します。パーティション化されたHive表を作成する前に、データを格納するHDFSディクショナリを作成する必要があります。
例19-5 ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
この例では、ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成で示した例で作成したデータ・ファイルを使用して、Hive表を作成します。この例では、パーティション化された外部表をORACLE_HDFSを使用して作成する方法を確認できます。次のコマンドはすべてHiveコマンドライン・インタフェース内で実行されるため、それらでは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次の問合せで示されるように、
customer_listHive表に行が移入されています。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
次の例のSQL構文では、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 *文を実行すると、表内のすべての行が返されます。なお、Hive外部表に対するSQL内で使用したSET LINESIZE 132文は、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> 親トピック: 外部表の例
19.6 ORA_PARTITION_VALIDATIONファンクションを使用したパーティション化された外部表の検証
パーティション化された外部表の行が正しいパーティションかどうかを確認するには、ORA_PARTITION_VALIDATIONファンクションを使用します。
ORA_PARTITION_VALIDATIONを使用すると、データ配置エラーを修正できます。
例19-6 ORA_PARTITION_VALIDATIONのパーティションのテストへの使用
ORA_PARTITION_VALIDATIONファンクションを使用すると、誤ったパーティションに配置されている外部表パーティション行のリストを取得できます。この機能を示すために、この例では、誤った部門セットで作成されたパーティションを示し、次にORA_PARTITION_VALIDATIONファンクションを使用して、誤ったパーティションのデータを識別する例を示します。
create or replace directory def_dir1 as '/tmp';
REM create the exact same data in files locally
REM
set feedback 1
spool /tmp/xp1_15.txt
select '12#dept_12#xp1_15#' from dual;
spool off
spool /tmp/xp2_30.txt
select '29#dept_29#xp2_30#' from dual;
spool off
spool /tmp/xp2_wrong.txt
select '99#dept_99#xp2_wrong#' from dual;
spool off
drop table ept purge;
create table ept(deptno number,dname char(14),loc char(13))
organization external
( type oracle_loader
default directory def_dir1
access parameters(
records delimited by newline
fields terminated by '#')
)
reject limit unlimited
partition by range (deptno)
(
partition ep1 values less than (10),
partition ep2 values less than (20) location ('xp1_15.txt'),
partition epwrong values less than (30) location ('xp2_wrong.txt')
)
;
select pt.*, ora_partition_validation(rowid) from pt;親トピック: 外部表の例
19.7 ファイル・パスにパーティション値を含めることによる外部表に対するSQL*Loaderの使用
オブジェクト・ストア内の多数のデータ・ファイルの管理を促進するには、ファイル・パスの一部としてフォルダ名を含めて外部表パーティション化を使用します。外部表の列で、行ごとにソース・ファイルのファイル名を返すこともできます。
Oracle Database 23c以降、パーティション・キーとパーティション値の組合せ(たとえば、/state=CA)、またはパーティション値のみ(たとえば、/state/CA/)を使用する外部表パーティション化では、ファイル・パスにフォルダ名が含まれます。また、外部表の列で、行ごとにソース・ファイルのファイル名を返すことができます。
オブジェクト・ストア内のデータを指す外部表は、多数のファイルからなる可能性があります。これらのファイルは、複数のディレクトリにまたがって編成でき、複数のディレクトリ・ツリーにまたがってもかまいません。パーティションの値は、ディレクトリ名またはファイル名で指定できます。たとえば、様々な月や様々な状態のファイルを別々のディレクトリに配置できます。これを、オブジェクト・ストアでの、Hiveで生成された表の要件にできます。
親トピック: 外部表の例
19.8 外部表でのLOBのロード
外部表は、単一のファイルから多数のレコードをロードし、各レコードが表の独自の行になるようにする場合に特に役立ちます。
- LOBおよび外部表の概要
データベースで外部表を使用してデータの読取りおよび書込みを行う利点と、その作成方法について説明します。 - ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード
プライマリ・データ・ファイル、LOBfileまたはLOB場所指定子(LLS)からLOB列をロードできます。 - ORACLE_DATAPUMPアクセス・ドライバを使用したLOBのロード
この例を使用して、LOBORACLE_LOADERアクセス・ドライバをロードする方法を確認します。
親トピック: 外部表の例
19.8.1 LOBおよび外部表の概要
データベースで外部表を使用してデータの読取りおよび書込みを行う利点と、その作成方法を学習します。
外部表を使用すると、外部ファイルの内容をOracle Databaseの表の行として扱うことができます。外部表を作成したら、SQL文を使用して外部表から行を読み取り、別の表に挿入できます。
これらの操作を実行するために、Oracle Databaseは次のいずれかのアクセス・ドライバを使用します。
ORACLE_LOADERアクセス・ドライバは、SQL Loaderと同様に、テキスト・ファイルおよびその他のファイル形式を読み取ります。ORACLE_DATAPUMPアクセス・ドライバは、問合せによって返されたデータを格納するバイナリ・ファイルを作成します。また、ファイルから行をバイナリ形式で返します。
外部表を作成するときは、外部表の列およびデータ型を指定します。アクセス・ドライバは、データ・ファイルに列のリストを持ち、データ・ファイルのフィールドの内容を外部表の同じ名前の列にマップします。アクセス・ドライバは、データ・ソース内のフィールドを検索し、これらのフィールドを外部表内の対応する列の適切なデータ型に変換します。外部表を作成した後、INSERT AS SELECT文を使用してターゲット表をロードできます。
外部表を使用してデータをSQL Loaderと比較してロードする利点の1つは、外部表がデータをパラレルにロードできることです。これを行う最も簡単な方法は、PARALLEL句を外部表とターゲット表の両方のCREATE TABLEの一部として指定することです。
例19-7
この例では、外部表でロードできる表CANDIDATEを作成します。ロードされると、外部表CANDIDATE_XTが作成されます。次に、INSERT文を実行して表をロードします。INSERT文には、ダイレクト・ロードを使用して行を表CANDIDATESに挿入する+APPENDヒントが含まれています。PARALLELパラメータは、表にパラレルでアクセスできることをSQLに通知します。
PARALLELパラメータ設定では、CANDIDATE_XTから読み取るパラレル問合せプロセスを4つ(4)指定し、CANDIDATEに挿入するパラレル・プロセスを4つ指定できます。BASICFILEとして格納されるLOBは、パラレルでロードできないことに注意してください。SECUREFILE LOBSはパラレルでのみロードできます。変数additional-external-table-infoは、追加の外部表情報を挿入できる場所を示します。
CREATE TABLE CANDIDATES
(candidate_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(20),
resume CLOB,
picture BLOB
) PARALLEL 4;
CREATE TABLE CANDIDATE_XT
(candidate_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(20),
resume CLOB,
picture BLOB
) PARALLEL 4;
ORGANIZATION EXTERNAL additional-external-table-info PARALLEL 4;
INSERT /*+APPEND*/ INTO CANDIDATE SELECT * FROM CANDIDATE_XT;アクセス・ドライバによって作成された外部表のファイルの場所
ORACLE_LOADERおよびORACLE_DATAPUMPによって作成または読み取られたすべてのファイルは、ディレクトリ・オブジェクトが指すディレクトリにあります。DBAまたはCREATE DIRECTORY権限を持つユーザーは、新しいオブジェクトをファイル・システム上のパスにマップするディレクトリ・オブジェクトを作成できます。これらのユーザーは、作成されたディレクトリ・オブジェクトに対するREAD、WRITEまたはEXECUTE権限を他のユーザーに付与できます。ディレクトリ・オブジェクトに対するREAD権限を付与されたユーザーは、外部表を使用して、ディレクトリ・オブジェクトのディレクトリからファイルを読み取ることができます。同様に、ディレクトリ・オブジェクトに対するWRITE権限を持つユーザーは、外部表を使用して、ディレクトリ・オブジェクトのディレクトリにファイルを書き込むことができます。
例19-8 ディレクトリ・オブジェクトの作成
次の例では、ディレクトリ・オブジェクトを作成し、READおよびWRITEアクセス権をユーザーHRに付与する方法を示します。
create directory HR_DIR as /usr/hr/files/exttab;
grant read, write on directory HR_DIR to HR;ノート:
Oracle Real Application Clusters(Oracle RAC)環境で外部表を使用する場合は、ディレクトリ・オブジェクトが指すディレクトリが、すべてのノードからアクセス可能なディレクトリにマップされていることを確認する必要があります。
親トピック: 外部表でのLOBのロード
19.8.2 ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード
プライマリ・データ・ファイル、LOBfileまたはLOB場所指定子(LLS)からLOB列をロードできます。
- プライマリ・データ・ファイルからのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してプライマリ・データ・データタイプ・ファイルからLOB列をロードする方法を確認します。 - LOBFILEファイルからのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOBFILEデータ型ファイルからLOB列をロードする方法を確認します。 - LOB場所指定子からのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOB場所指定子からLOBをロードする方法を確認します。
親トピック: 外部表でのLOBのロード
19.8.2.1 プライマリ・データ・ファイルからのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してプライマリ・データ・データタイプのファイルからLOB列をロードする方法を確認します。
LOBデータがプライマリ・データ・ファイルにある場合、それはデータ・ファイルのレコード形式に定義された別のフィールドです。アクセス・ドライバでのフィールドの定義方法は関係ありません。絶対的位置を使用してフィールドを定義するか、CHAR、VARCHARまたはVARCHARCを使用できます。ORACLE_LOADERのデータ型は、SQLのデータ型とは異なることに注意してください。
ノート:
Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_LOADERアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。
例19-9 プライマリ・データ・ファイルからのLOBのロード
この例では、各レコードのCOMMENTSフィールドは最大10000バイトです。SELECTを使用して表INTERVIEW_XTからCOMMENT列を選択すると、COMMENTSフィールドのデータはキャラクタ・ラージ・オブジェクト(CLOB)に変換され、Oracle SQLエンジンに表示されます。
CREATE TABLE INTERVIEW_XT
(candidate_id NUMBER,
interviewer_id NUMBER,
comments CLOB
)
ORGANIZATION EXTERNAL
(type ORACLE_LOADER
default directory hr_dir
access parameters
(records delimited by newline
fields terminated by ‘|’
(candidate_id CHAR(10),
employee_id CHAR(10),
comments CHAR(10000))
)
location ('interviews.dat')
);親トピック: ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード
19.8.2.2 LOBFILEファイルからのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOBFILEデータ型ファイルからLOB列をロードする方法を確認します。
プライマリ・データ・ファイルに次のいずれかの特性がある場合は、プライマリ・データ・ファイルからLOBを読み取るよりも、LOBファイルを使用することをお薦めします。
-
レコード・デリミタ。
LOBフィールドのデータには、データにレコード・デリミタを含めることはできません。プライマリ・データ・ファイルでは、
NEWLINEなどのレコード・デリミタをデータに含められます。ただし、ORACLE_LOADERアクセス・ドライバは、次のレコードにアクセスするときに、次のレコード・デリミタを検索します。レコード・デリミタもデータの一部である場合、LOB列の正しいデータは読み取られません。 -
フィールド終了記号。
LOB列のデータにフィールド終了記号を含めることはできません。プライマリ・データ・ファイルでは、データに
|などのフィールド終了記号を含められます。ただし、レコード・デリミタと同様に、フィールド終了記号がデータの一部である場合、ORACLE_LOADERはLOB列の正しいデータを読み取りません。 -
サイズ制限を超えるレコード・サイズ。
LOB列のデータは、レコードのサイズ制限内に収まる必要があります。
ORACLE_LOADERアクセス・ドライバでは、レコードが読取りバッファのサイズを超えないようにする必要があります。デフォルト値は1MBですが、READSIZEパラメータで変更できます。 -
バイナリ・データ
プライマリ・ファイルからバイナリ・データを読み取るには、ファイルの作成に特別な注意が必要です。レコード・デリミタまたはフィールド・デリミタが
BLOBのデータ内に出現しないことを保証できる場合を除き、VARレコード形式を使用し、バイナリ・フィールドにVARRAWまたはVARRAWCデータ型を使用する必要があります。このようなファイルは通常、プログラムで生成する必要があります。
プライマリ・データ・ファイルにこれらの特性がある場合は、LOBFILEデータ型を使用してLOB列をロードすることをお薦めします。
ノート:
Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_LOADERアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。
例19-10 プライマリ・データ・ファイルからのLOBのロード
各レコードのLOB列ごとに、ORACLE_LOADERアクセス・ドライバにディレクトリ・オブジェクトと、LOBの内容を含むファイルのファイル名が必要です。通常、LOB列のすべてのファイルは1つのディレクトリにあり、データファイル内の各レコードのファイル名はディレクトリ内にあります。たとえば、ユーザーHRとしてLOBファイル用に作成された次のオブジェクトがあるとします。
create directory HR_LOB_DIR as /usr/hr/files/exttab/lobfile;
grant read, write on directory HR_LOB_DIR to HR;データが次のレコードで構成されているとします。
cristina_resume.pdf
cristina.jpg
arvind_resume.pdf
arvind.jpgデータ・ファイルは次のようになり、フィールド終了記号、カンマ・デリミタ、文字列およびバイナリ・データが使用されます。
4378,Cristina,Garcia,cristina_resume.pdf,cristina.jpg
673289,Arvind,Gupta,arvind_resume.pdf,arvind.jpgこのシナリオでは、外部表のLOBファイルは次のようになります。
CREATE TABLE CANDIDATE_XT
(candidate_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(20),
resume CLOB,
picture BLOB
)
ORGANIZATION EXTERNAL
(type oracle_loader
default directory hr_dir
access parameters
(fields terminated by ‘,’
(candidate_id char(10),
first_name char(15),
last_name char(20),
resume_file char(40),
picture_file char(40)
)
column transforms
(
resume from lobfile (constant 'HR_LOB_DIR': resume_file,
picture from lobfile (constant 'HR_LOB_DIR': picture_file
)親トピック: ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード
19.8.2.3 LOB場所指定子からのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOB場所指定子からLOBをロードする方法を確認します。
LOB場所指定子(LLS)は、1つのファイルに複数のLOBのデータがある場合に使用されます。LLSを使用してLOB列をロードする場合、プライマリ・データ・ファイルのデータには、LOBデータを含むファイルの名前、LOBの開始位置のオフセットおよびLOBのバイト数が含まれます。
ノート:
Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_LOADERアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。
例19-11 LOB場所指定子を使用したデータのロード
次の例では、履歴書と画像を含むディレクトリHR_LOB_DIRがあるとします。このディレクトリでは、履歴書を1つのファイルに連結し、画像を別のファイルに連結しました。
resumes.dat
pictures.datデータ・ファイルは次のようになります。
4378,Cristina,Garcia,resumes.dat.1.10928/,picture.dat.1.38679/
673289,Arvind,Gupta,resumes.dat.10929.8439,picture.dat.38680,45772/このシナリオでは、外部表のLOBファイルは次のようになります。
CREATE TABLE CANDIDATE_XT
(candidate_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(20),
resume CLOB,
picture BLOB
)
ORGANIZATION EXTERNAL
(type oracle_loader
default directory hr_dir
access parameters
(fields terminated by ‘,’
(candidate_id char(10),
first_name char(15),
last_name char(20),
resume_file lls directory ‘HR_LOB_DIR’,
picture_file lls directory ‘HR_LOB_DIR’
)
)
location ('candidates.dat')
);
関連トピック
親トピック: ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード
19.8.3 ORACLE_DATAPUMPアクセス・ドライバを使用したLOBのロード
この例を使用して、LOB ORACLE_LOADERアクセス・ドライバのロード方法を確認します。
ORACLE_DATAPUMPアクセス・ドライバでは、コマンドCREATE TABLE AS SELECTを使用してSELECT文からデータをアンロードできます。このコマンドは、SELECT文によって返されるすべての行のデータを含むバイナリ・ファイルを作成します。このファイルを作成したら、ターゲット・データベースにORACLE_DATAPUMP外部表を作成し、文INSERT INTO target_table SELECT * FROM external_tableを使用して表をロードできます。
ノート:
Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_DATAPUMPアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。
例19-12 CREATE TABLE AS SELECTを使用した外部表の作成
この例では、CREATE TABLE AS SELECTを使用して、データベース内の表からデータをアンロードします。hr_dirのディレクトリにcandidate.dmpという名前のファイルが作成されます。次に、外部表を作成し(別のデータベースまたは同じデータベース内の別のスキーマに存在できます)、INSERTを使用してターゲット表をロードします。ターゲット表が別のデータベースにある場合、ファイルcandidates.dmpをそのデータベースのHR_DIRのディレクトリにコピーする必要があります。
CREATE TABLE CANDIDATE_XT
(candidate_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(20),
resume CLOB,
picture BLOB
)
ORGANIZATION EXTERNAL
(type oracle_datapump
default directory hr_dir
location ('candidates.dmp')
)
as select * from candidates;次に、別のスキーマまたは別のデータベースで、前述の手順で作成したファイルを使用して外部表を作成します。このコマンドを別のデータベースで実行する場合は、そのデータベースのHR_DIRのディレクトリにファイルをコピーする必要があります。
CREATE TABLE CANDIDATE_XT
(candidate_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(20),
resume CLOB,
picture BLOB
)
ORGANIZATION EXTERNAL
(type oracle_datapump
default directory hr_dir
location ('candidates.dmp')
);
INSERT INTO CANDIDATES SELECT * FROM CANDIDATE_XT;親トピック: 外部表でのLOBのロード
19.9 外部表からのCSVファイルのロード
このトピックでは、様々な条件で外部表からCSVファイルをロードする方法の例を示します。
一部の例は、前の例に基づいて作成されています。
例19-13 アクセス・パラメータを使用しない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> 例19-14 データ・ファイルの日付フィールドの書式と一致しないセッションのデフォルトの日付マスク
この例は、セッションのデフォルトの日付マスクがデータ・ファイルの日付フィールドの書式と一致しないことを除いて前の例と同じです。次の例では、セッションの日付書式は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.
例19-15 データが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.
例19-16 データが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.
例19-17 ファイルのフィールドの順序が表の列の順序と一致する場合
この例には、次の条件があります。
-
ファイルのフィールドの順序は表の列の順序と一致します。
-
フィールドは改行で区切られ、オプションで二重引用符で囲まれています。
-
値に改行が埋め込まれたフィールドがあり、それらのフィールドは二重引用符で囲まれています。
データ・ファイルの内容は次のとおりです。
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.
例19-18 データ・ファイルの一部のフィールドでアクセス・パラメータのデフォルト設定が使用されない場合
この例では、データ・ファイルのほとんどのフィールドでアクセス・パラメータのデフォルト設定が使用されるものの、少数のフィールドではデフォルト設定が使用されない場合に行う操作を示します。この例では、すべてのフィールドの設定を一覧表示するのではなく、デフォルトとは異なるフィールドの属性のみを設定する方法を示します。 相違点は以下のとおりです。
-
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.
親トピック: 外部表の例