14 外部表の概要
外部表機能は、既存のSQL*Loader機能を補足する機能です。この機能により、データベース内の表にあるデータと同様に、外部ソースのデータにアクセスできるようになります。
- 外部表の作成方法
外部表は、SQLのCREATE TABLE...ORGANIZATION EXTERNAL文を使用して作成されます。 - CREATE_EXTERNAL_PART_TABLEプロシージャ
このプロシージャは、クラウドのファイルに外部パーティション表を作成します。このプロシージャでは、ORACLE_BIGDATAドライバを使用して、Oracle Autonomous Databaseの外部データに対して問合せを実行できます。 - データ・ファイルおよび出力ファイルの位置
データ・ファイルおよび出力ファイルはサーバーに配置する必要があります。ファイルの読取りおよび書込みを行う位置を指定するディレクトリ・オブジェクトが必要です。 - 外部表のアクセス・パラメータ
外部表のアクセス・ドライバのデフォルトの動作を変更するには、アクセス・パラメータを指定します。 - 外部表使用中のデータ型変換
ソースとターゲットのデータ型が一致しない場合、Oracle Databaseが外部表から読み取るとき、および外部表に書き込むときに変換エラーが発生する可能性があります。
親トピック: 外部表
14.1 外部表の作成方法
外部表は、SQLのCREATE TABLE...ORGANIZATION EXTERNAL文を使用して作成されます。
データ・ロード時にステージング表の追加の索引付けが必要な場合、SQL*Loaderを使用する方が有効です。SQL*Loaderと外部表との処理内容の違いについては、「SQL*Loaderと外部表との処理内容の違い」を参照してください。
Oracle Database 23c以降では、外部表とSQL*Loaderのどちらについても、ソース・ファイル名をデータ・ファイル内のフィールドとしてロードできます。
Oracle Database 12c リリース2 (12.2.0.1)からは、外部表に含まれるデータをパーティション化でき、これにより、データベースに格納された表をパーティション化した場合(パーティション・プルーニングなど)と同様にパフォーマンスを高めることができます。
ノート:
外部表はSQL文のインライン外部表として使用できるため、データ・ディクショナリ内に永続データベース・オブジェクトとして外部表を作成する必要がなくります。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
外部表の作成時に、次の属性を指定します。
-
TYPE—外部表の型を指定します。外部表の各型は、固有のアクセス・ドライバによってサポートされます。-
ORACLE_LOADER—これがデフォルトのアクセス・ドライバです。これによって、外部表から内部表にデータがロードされます。データはテキスト・データ・ファイルからのものである必要があります。(ORACLE_LOADERアクセス・ドライバは、アンロードを実行できないため、内部表から外部表にデータを移動することはできません。) -
ORACLE_DATAPUMP—このアクセス・ドライバは、ロードとアンロードの両方を実行できます。データは、バイナリのダンプ・ファイルからロードする必要があります。内部表から外部表へのロードは、バイナリのダンプ・ファイルからフェッチすることによって実行されます。内部表から外部表へのアンロードは、外部表のバイナリのダンプ・ファイルを移入することによって実行されます。ORACLE_DATAPUMPアクセス・ドライバは、SQLのCREATE TABLE AS SELECT文を使用した外部表の作成の一環としてのみダンプ・ファイルにデータを書き込むことができます。作成されたダンプ・ファイルは、何回でも読み取ることができますが、変更はできません(つまり、DML操作は実行できません)。 -
ORACLE_HDFS—Hadoop Distributed File System (HDFS)に格納されたデータを抽出します。 -
ORACLE_HIVE—Apache HIVEに格納されたデータを抽出します。
-
-
DEFAULT DIRECTORY—明示的にディレクトリ・オブジェクトに名前を付けないすべての入出力ファイルに対して、使用するデフォルトのディレクトリを指定します。位置はディレクトリ・パスではなく、ディレクトリ・オブジェクトで指定されます。外部表を作成する前にディレクトリ・オブジェクトを作成しておく必要があります(これを行わない場合はエラーが生成されます)。詳細は、「データ・ファイルおよび出力ファイルの位置」を参照してください。 -
ACCESS PARAMETERS—外部データ・ソースを記述し、指定された外部表の型を実装します。各外部表の型には、その外部表の型に固有のアクセス・パラメータを提供する専用のアクセス・ドライバがあります。アクセス・パラメータはオプションです。詳細は、「アクセス・パラメータ」を参照してください。 -
LOCATION—外部表のデータ・ファイルを指定します。-
ORACLE_LOADERおよびORACLE_DATAPUMPの場合、ファイルの名前はdirectory:fileという形式になります。directoryの部分はオプションです。この部分を指定しないと、デフォルトのディレクトリがファイルのディレクトリとして使用されます。ORACLE_LOADERアクセス・ドライバを使用している場合には、ファイル名にはワイルドカードを使用でき、アスタリスク(*)は複数文字を、疑問符(?)は1文字を表します。 -
ORACLE_HDFSの場合、LOCATION句は、ディレクトリまたはファイルのUniform Resource Identifier (URI)のリストです。URIに関連付けられたディレクトリ・オブジェクトはありません。 -
ORACLE_HIVEの場合、LOCATION句は使用されません。かわりに、Hadoop HCatalog表を読み取ってデータ・ソースの場所(ファイルや別のデータベース)に関する情報を取得します。
-
次の各例に、各アクセス・ドライバの属性の簡単な使用方法を示します。
例14-1 ORACLE_LOADERアクセス・ドライバの属性の指定
次の例では、ORACLE_LOADERアクセス・ドライバを使用して、これらの各属性の使用方法を示しています(デフォルト・ディレクトリdef_dir1はすでに存在するものとします)。
SQL> CREATE TABLE emp_load
2 (employee_number CHAR(5),
3 employee_dob CHAR(20),
4 employee_last_name CHAR(20),
5 employee_first_name CHAR(15),
6 employee_middle_name CHAR(15),
7 employee_hire_date DATE)
8 ORGANIZATION EXTERNAL
9 (TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY def_dir1
11 ACCESS PARAMETERS
12 (RECORDS DELIMITED BY NEWLINE
13 FIELDS (employee_number CHAR(2),
14 employee_dob CHAR(20),
15 employee_last_name CHAR(18),
16 employee_first_name CHAR(11),
17 employee_middle_name CHAR(11),
18 employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"
19 )
20 )
21 LOCATION ('info.dat')
22 );
Table created.
アクセス・ドライバで指定する情報により、データ・ソースのデータが、外部表の定義と一致するように処理されます。CREATE TABLE emp_loadの後にリストされるフィールドが、実際にinfo.datソース・ファイル内のデータのメタデータを定義します。
例14-2 ORACLE_DATAPUMPアクセス・ドライバの属性の指定
この例では、inventories_xtという名前の外部表を作成し、外部表のダンプ・ファイルにoeサンプル・スキーマ内のinventories表のデータを移入します。
SQL> CREATE TABLE inventories_xt
2 ORGANIZATION EXTERNAL
3 (
4 TYPE ORACLE_DATAPUMP
5 DEFAULT DIRECTORY def_dir1
6 LOCATION ('inv_xt.dmp')
7 )
8 AS SELECT * FROM inventories;
Table created.例14-3 ORACLE_HDFSアクセス・ドライバの属性の指定
CREATE TABLE sales_external
( time_id DATE NOT NULL, …
amount_sold NUMBER(10,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_HDFS
ACCESS PARAMETERS (com.oracle.bigdata.cluster=hadoop1)
LOCATION (“hdfs:/usr/sales_1.csv”, “hdfs:/usr/my_sales_*.csv”)
)例14-4 ORACLE_HIVEアクセス・ドライバの属性の指定
CREATE TABLE sales_external
( time_id DATE NOT NULL, …
amount_sold NUMBER(10,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_HIVE
ACCESS PARAMETERS (com.oracle.bigdata.cluster=hadoop1
com.oracle.bigdata.tablename=default.ratings_hive_table)
);親トピック: 外部表の概要
14.2 CREATE_EXTERNAL_PART_TABLEプロシージャ
このプロシージャでは、クラウド内のファイルにパーティション化された外部表を作成します。このプロシージャでは、ORACLE_BIGDATAドライバを使用して、Oracle Autonomous Databaseの外部データに対して問合せを実行できます。
ユースケース
Oracle Database 23c以降、オブジェクト・ストアでORACLE_BIGDATAドライバを使用している場合、外部表のパスから列値を選択できるようになりました。この機能を使用すると、表のパーティション列を表す、パーティション化されたオブジェクト・ストレージ内のファイルを問い合せてロードできます。
構文
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
table_name IN VARCHAR2,
credential_name IN VARCHAR2,
partitioning_clause IN CLOB,
column_list IN CLOB,
field_list IN CLOB DEFAULT,
format IN CLOB DEFAULT);
パラメータ
| パラメータ | 説明 |
|---|---|
|
|
外部表の名前。例: |
|
|
Cloud Object Storageにアクセスするための資格証明の名前。リソース・プリンシパルが有効な場合は、 |
|
|
個別のパーティションの場所情報を含む、完全なパーティション化句を指定します。
|
|
|
パラメータ
この場合、sales表のルート・フォルダは |
|
|
外部表の列名とデータ型のカンマ区切りリスト。このパラメータには、
例:
|
|
|
ソース・ファイル内のフィールドとそのデータ型を識別します。デフォルト値は Apache Parquetファイルなどの構造化ファイルには、field_listは必要ありません。 |
|
|
フォーマット・オプション
データ・ファイルが構造化されておらず、 Hive形式に基づかないオブジェクト名の場合、 |
使用上のノート
-
partitioning_clauseパラメータとfile_url_listパラメータの両方を使用してこのプロシージャをコールすることはできません。 -
Avro、ParquetまたはORCデータ・ファイルを含む構造化データ・ファイルでは、
column_listパラメータの指定はオプションです。column_listを指定しない場合、formatパラメータのpartition_columnsオプションには、nameとtypeの両方を含める必要があります。 -
CSVテキスト・ファイルなどの非構造化データ・ファイルでは、
column_listパラメータは必須です。 -
プロシージャ
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEは、次のようなサポートされているクラウド・オブジェクト・ストレージ・サービスの外部パーティション・ファイルをサポートします。- Oracle Cloud Infrastructureオブジェクト・ストレージ
-
Azure Blob Storage
- Amazon S3互換: Oracle Cloud Infrastructure Object Storage、Google Cloud Storage、Wasabi Hot Cloud Storageなど。
- GitHubリポジトリ
-
file_url_listパラメータを指定してDBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEをコールする場合、クラウド・オブジェクト・ストアのファイル名で指定される列の型は、次のいずれかの型である必要があります。VARCHAR2(n) NUMBER(n) NUMBER(p,s) NUMBER DATE TIMESTAMP(9) -
デフォルトのレコード・デリミタは
detected newlineです。detected newlineを指定すると、DBMS_CLOUDは、レコード・デリミタとして使用する適切な改行文字を自動的に見つけようとします。DBMS_CLOUDは、最初にWindowsの改行文字\r\nを検索します。Windowsの改行文字が見つかると、それがプロシージャのすべてのファイルのレコード・デリミタとして使用されます。Windowsの改行文字が見つからない場合、DBMS_CLOUDは、UNIX/Linuxの改行文字\nを検索し、見つかった場合は\nがプロシージャのすべてのファイルのレコード・デリミタとして使用されます。ソース・ファイルで異なるレコード・デリミタの組合せが使用されている場合は、KUP-04020: found record longer than buffer size supportedなどのエラーが発生する可能性があります。この場合、同じレコード・デリミタを使用するようにソース・ファイルを変更するか、同じレコード・デリミタを使用するソース・ファイルのみを指定する必要があります。 -
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEを使用して作成する外部パーティション表には、2つの非表示列file$pathおよびfile$nameが含まれます。これらの列は、レコードの取得元ファイルの識別に役立ちます。-
file$path: オブジェクト名の先頭までのファイル・パス・テキストを指定します。 -
file$name: バケット名に続くすべてのテキストを含め、オブジェクト名を指定します。
-
例
partitioning_clauseパラメータを使用した例:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
table_name =>'PET1',
credential_name =>'OBJ_STORE_CRED',
format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'characterset' value 'us7ascii'),
column_list => 'col1 number, col2 number, col3 number',
partitioning_clause => 'partition by range (col1)
(partition p1 values less than (1000) location
( ''&base_URL//file_11.txt'')
,
partition p2 values less than (2000) location
( ''&base_URL/file_21.txt'')
,
partition p3 values less than (3000) location
( ''&base_URL/file_31.txt'')
)'
);
END;
/
非構造化データ・ファイルでfile_uri_listおよびcolumn_listパラメータを使用した例:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
table_name => 'MYSALES',
credential_name => 'DEF_CRED_NAME',
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/*.csv',
column_list => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)',
field_list => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
format => '{"type":"csv", "partition_columns":["country","year","month"]}');
END;
/
構造化データ・ファイルでcolumn_listパラメータを指定せずにfile_uri_listを使用した例:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
table_name => 'MYSALES',
credential_name => 'DEF_CRED_NAME',
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
table_name => 'MYSALES',
credential_name => 'DEF_CRED_NAME',
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/*.parquet',
format =>
json_object('type' value 'parquet', 'schema' value 'first',
'partition_columns' value
json_array(
json_object('name' value 'country', 'type' value 'varchar2(100)'),
json_object('name' value 'year', 'type' value 'number'),
json_object('name' value 'month', 'type' value 'varchar2(2)')
)
)
);
END;
/パーティション化されたApache Parquetソースを使用した例。この例は、データがパブリックであるため実行できます。
この場合、データは月ごとに編成されます。次に示すように、リソース・プリンシパルが有効になりました。ただし、これはパブリック・データ・ソースであるため、必須ではありません。
ノート:
列のリストはParquetソースから導出されるため、必要ありません。列のリストがないため、月のデータ型を指定する必要があります。BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
table_name => 'sales',
file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/*.parquet',
format => '{"type":"parquet","partition_columns":[{name:"month","type":"varchar2(20)"}]}'
);
END;
/
mgubar: Finally, here is the generated ddl:
CREATE TABLE sales
( "DAY_ID" TIMESTAMP (6),
"GENRE_ID" NUMBER(19,0),
"MOVIE_ID" NUMBER(19,0),
"CUST_ID" NUMBER(19,0),
"APP" VARCHAR2(4000 BYTE),
"DEVICE" VARCHAR2(4000 BYTE),
"OS" VARCHAR2(4000 BYTE),
"PAYMENT_METHOD" VARCHAR2(4000 BYTE),
"LIST_PRICE" BINARY_DOUBLE,
"DISCOUNT_TYPE" VARCHAR2(4000 BYTE),
"DISCOUNT_PERCENT" BINARY_DOUBLE,
"ACTUAL_PRICE" BINARY_DOUBLE,
"MONTH" VARCHAR2(20 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_BIGDATA
DEFAULT DIRECTORY "DATA_PUMP_DIR"
ACCESS PARAMETERS
( com.oracle.bigdata.fileformat=parquet
com.oracle.bigdata.filename.columns=["month"]
com.oracle.bigdata.file_uri_list="https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/*.parquet"
com.oracle.bigdata.credential.schema="ADMIN"
com.oracle.bigdata.credential.name="OCI$RESOURCE_PRINCIPAL"
com.oracle.bigdata.trimspaces=notrim
)
)
REJECT LIMIT 0
PARTITION BY LIST ("MONTH")
(PARTITION "P1" VALUES (('2019-01'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/month=2019-01/*.parquet'
),
PARTITION "P2" VALUES (('2019-02'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/month=2019-02/*.parquet'
),
PARTITION "P3" VALUES (('2019-03'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/month=2019-03/*.parquet'
),
PARTITION "P4" VALUES (('2019-04'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/month=2019-04/*.parquet'
),
...
PARTITION "P24" VALUES (('2020-12'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/month=2020-12/*.parquet'
))
PARALLEL ;フィールド・リストが不要な例。Parquetは構造化ファイルです。このファイルはParquetであるため、フィールド・リストは構造化ファイルから導出されます。
CREATE TABLE ADMIN.EXT_CUSTSALES
( DAY_ID TIMESTAMP (6),
GENRE_ID NUMBER(19,0),
MOVIE_ID NUMBER(19,0),
CUST_ID NUMBER(19,0),
APP VARCHAR2(4000 BYTE),
DEVICE VARCHAR2(4000 BYTE),
OS VARCHAR2(4000 BYTE),
PAYMENT_METHOD VARCHAR2(4000 BYTE),
LIST_PRICE BINARY_DOUBLE,
DISCOUNT_TYPE VARCHAR2(4000 BYTE),
DISCOUNT_PERCENT BINARY_DOUBLE,
ACTUAL_PRICE BINARY_DOUBLE
) DEFAULT COLLATION USING_NLS_COMP
ORGANIZATION EXTERNAL
( TYPE ORACLE_BIGDATA
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS
( com.oracle.bigdata.fileformat=parquet
com.oracle.bigdata.trimspaces=notrim
)
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_landing/o/sales_sample/*.parquet'
)
)
REJECT LIMIT UNLIMITED
PARALLEL ;
親トピック: 外部表の概要
14.3 データ・ファイルおよび出力ファイルの位置
データ・ファイルおよび出力ファイルはサーバーに配置する必要があります。ファイルの読取りおよび書込みを行う位置を指定するディレクトリ・オブジェクトが必要です。
ノート:
この項に示すディレクトリ・オブジェクト関連の情報は、ORACLE_HDFSアクセス・ドライバまたはORACLE_HIVEアクセス・ドライバには適用されません。ORACLE_HDFSドライバを使用する場合、データの場所はディレクトリまたはファイルのURIのリストで指定します。URIに関連付けられたディレクトリ・オブジェクトはありません。ORACLE_HIVEドライバではデータ・ソースの場所を指定しません。その情報はHiveメタストア表を読み取って取得するため、ディレクトリ・オブジェクトは必要ありません。
アクセス・ドライバは、データベース・サーバー内で実行されます。この動作は、SQL*Loaderが、ロードするデータをサーバーに送信するクライアント・プログラムであるという点で、SQL*Loaderとは異なります。この違いは、次のことを意味しています。
-
アクセス・ドライバがロード可能なファイルにサーバーがアクセスできる必要があります。
-
サーバーでは、アクセス・ドライバによって作成された出力ファイル(ログ・ファイル、不良ファイルおよび廃棄ファイル)、および
ORACLE_DATAPUMPアクセス・ドライバによって作成されたダンプ・ファイルの作成と書込みを行う必要があります。
ファイルの読取りおよび書込みを実行する場所を指定するには、アクセス・ドライバでディレクトリ・オブジェクトを使用する必要があります。ディレクトリ・オブジェクトは、ファイル・システムのディレクトリ名に名前をマップします。たとえば、次の文は/usr/apps/datafilesにあるディレクトリにマップされる、ext_tab_dirという名前のディレクトリ・オブジェクトを作成します。
CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';
DBAまたはユーザーがCREATE ANY DIRECTORY権限でディレクトリ・オブジェクトを作成できます。
ノート:
Oracle Real Applications Cluster(Oracle RAC)構成で外部表を使用するには、ディレクトリ・オブジェクトのパスがクラスタ・ファイル・システム上に存在するようにする必要があります。
ディレクトリの作成後、ディレクトリ・オブジェクトを作成するユーザーは、そのディレクトリのREAD権限およびWRITE権限を他のユーザーに付与する必要があります。これらの権限は、ロールを使用して割り当てるのではなく、明示的に付与する必要があります。たとえば、ext_tab_dirで指定されたディレクトリのユーザーscottのかわりに、サーバーがファイルを読み込むことができるようにするには、ディレクトリ・オブジェクトを作成したユーザーが、次のコマンドを実行する必要があります。
GRANT READ ON DIRECTORY ext_tab_dir TO scott;
Oracle DatabaseのSYSユーザーが、ディレクトリ・オブジェクトを所有できる唯一のユーザーです。ただし、SYSユーザーは、ディレクトリ・オブジェクトを作成する権限を他のOracle Databaseユーザーに付与できます。ディレクトリ・オブジェクトに対するREADまたはWRITE権限は、Oracle Databaseのみが、そのファイルへの読取りまたは書込みをユーザーのかわりに行えることを意味します。適切なオペレーティング・システム権限がないかぎり、Oracle Databaseの外部にあるファイルには直接アクセスできません。同様に、Oracle Databaseには、そのディレクトリ内のファイルに対する読取りおよび書込みのオペレーティング・システム権限が必要になります。
親トピック: 外部表の概要
14.4 外部表のアクセス・パラメータ
外部表のアクセス・ドライバのデフォルトの動作を変更するには、アクセス・パラメータを指定します。
特定の型の外部表を作成する場合、アクセス・パラメータを指定して、アクセス・ドライバのデフォルトの動作を変更できます。各アクセス・ドライバには、アクセス・パラメータ用の固有の構文があります。外部表に使用するためのアクセス・ドライバORACLE_LOADER、ORACLE_DATAPUMP、ORACLE_HDFSおよびORACLE_HIVEが提供されています。
ノート:
これらのアクセス・パラメータは、SQL文のCREATE TABLE...ORGANIZATION EXTERNALのopaque_format_specとしてまとめて参照されます。ACCESSパラメータ句を使用すると、SQLコメントを使用できます。
関連項目:
-
SQL
CREATE TABLE文を使用するときにopaque_format_specを指定する方法の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: 外部表の概要
14.5 外部表の使用時のデータ型の変換
ソースとターゲットのデータ型が一致しない場合、Oracle Databaseが外部表から読み取るとき、および外部表に書き込むときに、変換エラーが発生する可能性があります。
外部表の読取り時の変換エラー
外部表から行を選択すると、アクセス・ドライバによって、データ・ソースのデータを外部表の対応する列のデータ型と一致させるために必要な変換が実行されます。データおよび必要な変更タイプによっては、変換でエラーが発生する可能性があります。
外部表からの読取り時に発生する可能性のあるデータ変換の問題のタイプを示すために、2つの列(データ型がVARCHAR2(4)のKEYと、データ型がNUMBERのVAL)を使用して次の外部表KV_TAB_XTを作成するとします。
SQL> CREATE TABLE KV_TAB_XT (KEY VARCHAR2(4), VAL NUMBER)
2 ORGANIZATION EXTERNAL
3 (DEFAULT DIRECTORY DEF_DIR1 LOCATION (‘key_val.csv’)); 外部表KV_TAB_XTは、アクセス・パラメータのデフォルト値を使用します。したがって、次のようになります。
- レコードは改行で区切られます。
- データ・ファイルとデータの文字セットは同じです。
- データ・ファイル内のフィールドの名前と順序は、外部表の列と同じです。
- フィールドのデータ型は
CHAR(255)です。 - 各フィールドのデータはカンマで終了します。
KV_TAB_XT外部表のデータ・ファイル内のレコードの要件は、次のとおりです。
- 最大4バイトの文字列。文字列が空の場合、フィールドの値は
NULLです。 - 終了のカンマ。
- 数字の文字列。文字列が空の場合、このフィールドの値は
NULLです。 - オプションの終了カンマ。
アクセス・ドライバでは、データ・ファイルからレコードを読み取るとき、データ・ファイル内のKEYフィールドの値の長さが4以下であることを確認し、データ・ファイル内のVALフィールドの値をOracle Databaseの数値に変換しようとします。
KEYフィールドの値の長さが4より大きい場合、またはVALの値に数値以外の文字がある場合、ORACLE_LOADERアクセス・ドライバはその行を拒否します。その結果、行のコピーが不良ファイルに書き込まれ、エラー・メッセージがログ・ファイルに書き込まれます。
すべてのアクセス・ドライバは、外部表のソースにあるフィールドのデータ型からの変換、および外部表の列のデータ型からの変換を処理する必要があります。アクセス・ドライバが実行する変換およびチェックのタイプについて、いくつかの例を次に示します。
- 文字データを、ソース・データで使用されている文字セットからデータベースで使用される文字セットに変換します。
- 文字データから数値データに変換します。
- 数値データから文字データに変換します。
- 文字データから日付またはタイムスタンプに変換します。
- 日付またはタイムスタンプから文字データに変換します。
- 文字データから内部データ型に変換します。
- 内部データ型から文字データに変換します。
- キャラクタ列のデータ値の長さがその列の長さ制限を超えていないことを確認します。
必要な変換または検証の実行中にエラーが発生した場合、アクセス・ドライバではエラーの処理方法を決定できます。ORACLE_LOADERおよびORACLE_DATAPUMPアクセス・ドライバでは、エラーを検出すると、レコードを拒否し、エラー・メッセージをログ・ファイルに書き込みます。その場合、そのレコードがデータ・ソースになかったかのようになります。ORACLE_HDFSおよびORACLE_HIVEアクセス・ドライバがエラーを検出すると、エラーが見つかったフィールドの値がNULLに設定されます。この動作は、HiveがHadoopでエラーを処理する際の動作と一致しています。
外部表列のデータ型と一致させるために、データ・ソースからのデータがアクセス・ドライバによって変換された後も、その外部表にアクセスするSQL文で、追加のデータ型変換が必要になる場合があります。この追加変換のいずれかでエラーが発生した場合は、文全体が失敗します。(ただし、SQL文でDMLエラー・ロギング機能を使用してこれらのエラーを処理する場合は例外です。)これらの変換は、通常、SQL文の実行時に必要になる可能性がある変換と同じです。たとえば、KV_TAB_XT外部表の定義を文字データ型の列のみが含まれるように変更した後、INSERT文を実行して、外部表のデータを列VALのデータ型がNUMBERの別の表にロードするとします。
SQL> CREATE TABLE KV_TAB_XT (KEY VARCHAR2(20), VAL VARCHAR2(20))
2 ORGANIZATION EXTERNAL
3 (DEFAULT DIRECTORY DEF_DIR1 LOCATION (‘key_val.csv’));
4 CREATE TABLE KV_TAB (KEY VARCHAR2(4), VAL NUMBER);
5 INSERT INTO KV_TAB SELECT * FROM KV_TAB_XT;この例において、VALのデータに数字以外の文字が含まれている場合、アクセス・ドライバはレコードを拒否しません。これは、外部表のVALが(NUMBERではなく) VARCHAR2になったためです。ただし、SQLの処理でKV_TAB_XTの文字データ型からKV_TABの数値データ型への変換処理が必要になります。外部表のVALの値に数字以外の文字がある場合、SQLで変換エラーが発生し、挿入された行はすべてロールバックされます。SQLでの変換エラーを回避するため、外部表の列のデータ型が、これらの列の値を使用する他の表または関数で要求されるデータ型と一致させることをお薦めします。
外部表への書込み時の変換エラー
ORACLE_DATAPUMPアクセス・ドライバを使用すると、CREATE TABLE AS SELECT文を使用してデータを外部表にアンロードできます。SELECT式における列のデータ型が外部表の列のデータ型と一致しない場合、データ変換が発生します。SQLでデータ型変換のエラーが検出されると、SQLは文を停止し、データ・ファイルは読み取ることができなくなります。
変換エラーによる操作の失敗に関連する問題を回避するには、外部表の列のデータ型を、外部表への書込みに使用されるソース表または式の列のデータ型と一致させる必要があります。ただし、外部表はすべてのデータ型をサポートするわけではないため、必ず成功するとはかぎりません。そのような場合、ソース表でサポートされていないデータ型を、外部表でサポートするデータ型に変換する必要があります。次のCREATE TABLE文は、この変換の例を示しています。
CREATE TABLE LONG_TAB_XT (LONG_COL CLOB)
ORGANIZATION EXTERNAL...SELECT TO_LOB(LONG_COL) FROM LONG_TAB;LONG_TABという名前のソース表には、LONG列があります。そのため、作成する外部表の対応する列LONG_TAB_XTはCLOBである必要があり、外部表の移入に使用されるSELECT副問合せは、TO_LOB演算子を使用して列をロードする必要があります。
ノート:
LONGデータ型(LONG、LONG RAW、LONG VARCHAR、LONG VARRAW)のすべての形式は、Oracle8iリリース8.1.6で非推奨になりました。以降のリリースでは、LONGデータ型は既存のアプリケーションとの下位互換性のために提供されていました。以降のリリースで開発された新しいアプリケーションでは、大量の文字データにCLOBおよびNCLOBデータ型を使用することをお薦めします。
親トピック: 外部表の概要