外部表の例

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ファイルからのデータのロード。加えて: データが2つのデータ・ファイルに分割されています。各データ・ファイルにフィールドの名前を含む行が格納されています。データ・ファイルの一部のフィールドが引用符(")で囲まれています。	例18-8
最初のファイルにのみフィールド名の行が含まれることを除き、前の例と同様のデータのロード	例18-9
2つのデータ・ファイルを使用したデータのロード	例18-10
データ・ファイルのほとんどのフィールドでアクセス・パラメータのデフォルト設定が使用され、少数のフィールドでデフォルト設定が使用されない場合のデータのロード	例18-11
LOBのロード	例18-5
データ型変換によって問題が発生する仕組み	外部表の使用時のデータ型の変換

ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成
このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化された外部表を作成する方法について説明します。
ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成
この項では、サブパーティション化された外部表を作成する方法の例を示します。
ORACLE_HDFSアクセス・ドライバを使用したパーティション化された外部表の作成
この項の例はORACLE_HDFSアクセス・ドライバを使用してパーティション化された外部表を作成する方法を示しています。
ORACLE_HIVEアクセス・ドライバを使用したパーティション化された外部表の作成
ORACLE_HIVE表に対してパーティション化された外部表を作成するには、パーティション化されたHive外部表が必要です。
外部表からのLOBのロード
外部表は、単一のファイルから多数のレコードをロードし、各レコードが表の独自の行になるようにする場合に特に役立ちます。
外部表からのCSVファイルのロード
このトピックでは、様々な条件で外部表からCSVファイルをロードする方法の例を示します。

親トピック: 外部表

18.1 ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成

このトピックでは、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表の名前
CREATE DDLが実行されるかどうかを示すブール値
CREATE DDL文字列を含む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.

親トピック: 外部表の例