2.9 ファイルのロード
SQLclでLOADコマンドを使用して、カンマ区切り値ファイルをローカル・ディレクトリまたはクラウド・ストレージの場所から表にロードします。
2.9.1 LOADコマンド
カンマ区切り値(csv)ファイルをローカル・ディレクトリまたはクラウド・ストレージの場所から表にロードします。
構文
LOAD [TABLE] [schema.]table_name { <file-specification> | <cloud-storage-specification> }
[NEW | SHOW | SHOW_DDL | CREATE |CREATE_DDL]ここで
[schema.]table_nameは、ロードする表を示します。schemaが省略されている場合、接続されているユーザー・スキーマの表がロードされます。
file-specificationの構文は次のとおりです。
{ <fully-qualified-file-name> | <file-name> }-
fully-qualified-file-name: ロードするファイルのフルパスを示します。
-
file-name: ロードするファイルを示します。ファイルはデフォルトのパスにある必要があります。
cloud-storage-specificationの構文は次のとおりです。
{ CLOUDSTORAGE | CS | CLOUD_STORAGE } [ <url> | <qualified-name> ] -
url: クラウド・ストレージ・コマンドを使用してデフォルトのクラウド・ストレージURLが設定されていない場合、クラウド・ストレージ・ファイルの完全なURL。
-
qualified-name: オブジェクトの名前。オプションでネームスペースおよびバケットで修飾されます。修飾された名前とクラウド・ストレージ・コマンドにより指定されたデフォルトURLの組合せによって、オブジェクトURLが完全に識別される必要があります。urlおよびqualified-nameを省略する場合は、デフォルトのクラウド・ストレージURLをオブジェクトに設定する必要があります。
NEWは表を作成し、データをロードします。
[SHOW | SHOW_DDL]はDDL生成フェーズを実行してDDLを表示します。
[CREATE | CREATE_DDL]はDDL生成フェーズを実行して表を作成します。
SET LOADおよびSET LOADFORMATを使用して、DDL分析および生成のためのプロパティを指定します。
CREATE TABLE DDLの生成により、データ・ファイルが事前スキャンされ、列のプロパティが特定されます。SET LOAD SCAN <nを使用して、DDLでスキャンする行数を指定します。デフォルトは100です。スキャンをオフにするには、0に設定します。
SET LOAD COL_SIZEを使用して、生成される列サイズを変更します。SET LOAD MAP_NAMESを使用して、ファイルの列名を表の列名にマップします。
Cloud Storageコマンドの詳細は、クラウド・ストレージの使用を参照してください。
ファイル形式のデフォルトは次のとおりです。
-
列はカンマで区切られ、オプションで二重引用符で囲まれている場合があります。
-
行はWindows、UNIXまたはMacの標準の行の終了文字で終了されます。
-
ファイルはエンコードされたUTF8です。
デフォルト・ロード:
-
バッチ当たり50行を処理します。
-
AUTOCOMMITがSQLcLで設定されている場合、10バッチごとにコミットが実行されます。
-
50を超えるエラーが見つかった場合、ロードが終了します。
SET LOADFORMATオプションを使用して、ファイルを読み取ります(デリミタ、囲み)。
SET LOADオプションを使用して、データ(バッチ当たりの行、日付書式)をロードします。
例
次の例は、ローカル・ストレージから表にファイルをロードする方法を示しています。
--Create Table "countries"
create table countries(countries_id NUMBER(5),countries_name VARCHAR2(40));
Table COUNTRIES created
--Load file COUNTRIES_DATA_TABLE.csv in local storage to "countries" table
load countries C:\Users\JDOE\SQLcl\COUNTRIES_DATA_TABLE.csv
format csv
column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
--Number of rows processed: 30
--Number of rows in error:
0 - SUCCESS: Load processed without errors
--Check the number of rows in countries table
select count(*) from countries;
COUNT(*)
–--------
30
次の例は、新しい表EMPにデータをロードする方法を示しています。
load emp empfile.csv new
--Create new table and load data into table HR.EMP
csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
#INFO DATE format detected: RRRR-MM-DD
CREATE TABLE HR.EMP
(
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(26),
LAST_NAME VARCHAR2(26),
EMAIL VARCHAR2(26),
PHONE_NUMBER VARCHAR2(26),
HIRE_DATE DATE,
JOB_ID VARCHAR2(26),
SALARY NUMBER(9, 2),
COMMISSION_PCT VARCHAR2(26),
MANAGER_ID NUMBER(5),
DEPARTMENT_ID NUMBER(5)
)
;
#INFO Table created
#INFO Number of rows processed: 21
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 21
SUCCESS: Processed without errors
次の例は、ローカル・ファイルから新しいテーブルを作成する方法を示しています。
load emp1 empfile.csv create_ddl
--Create new table HR.EMP1
csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
batch_rows 50
batches_per_commit 10
clean_names transform
column_size rounded
commit on
date_format
errors 50
map_column_names off
method insert
timestamp_format
timestamptz_format
locale English United States
scan_rows 100
truncate off
unknown_columns_fail on
--Pre-scans the date format
#INFO DATE format detected: RRRR-MM-DD
CREATE TABLE SYSTEM.EMP1
(
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(26),
LAST_NAME VARCHAR2(26),
EMAIL VARCHAR2(26),
PHONE_NUMBER VARCHAR2(26),
HIRE_DATE DATE,
JOB_ID VARCHAR2(26),
SALARY NUMBER(9, 2),
COMMISSION_PCT VARCHAR2(26),
MANAGER_ID NUMBER(5),
DEPARTMENT_ID NUMBER(5)
)
;
#INFO Table created
SUCCESS: Processed without errors
2.9.1.1 SET LOADコマンド
SET LOADでは、LOADコマンドの使用時にデータをロードするためのオプションを設定できます。
構文
SET LOAD default | [options...] ここで
defaultは、ロード方法のプロパティがデフォルト値に戻ることを意味します。
optionsは、次のものを表します。
-
BATCH_ROWS|BATCHROWS <number_of_rows>データ・ロードはバッチで実行されます。各バッチに含める行数を指定します。
-
BATCHES_PER_COMMIT|BATCHESPERCOMMIT <batches_per_commit>number_of_batchesの処理後にコミットします。数値が0の場合、コミットはロードの最後に行われます。数値が0以上の場合は、
COMMIT ONが設定されます。 -
CLEAN_NAMES [ TRANSFORM | TRANSFORM128 | QUOTE | QUOTE128 | UNIQUE ]表および列名をデータベース識別子に準拠させるルールを識別します。以前のリリースとの一貫性を保つために、名前はマッピングされる前に消去されます。
CLEAN_NAMESおよびMAP_COLUMN_NAMESの両方が使用される場合、削除する名前を指定する必要があります。標準の識別子は次のとおりです。
- 30文字または128文字を超えることはできません。
- 予約語以外です。
- 文字で始まり、文字、数字、または_$#の1つのみを含みます。
- 大文字
- 準拠していない名前は引用符で囲む必要があります。長さルールは常に適用されます。
ノート:
引用符に囲まれたデータは、名前が削除される前にヘッダー行の引用符が削除されます。TRANSFORM (default)名前が次のように変換されることを示します。
- 名前は大文字です。
- 名前が引用符で開始および終了している場合、引用符は削除されます。
- 名前が予約語である場合、ドル記号($)が追加されます。
- 数字または特殊文字で始まる名前には、接頭辞としてXが付けられます。
- スペースとハイフンはアンダースコア(_)に置き換えられます。$および#文字は保持されます。
- $と#以外の特殊文字は、シャープ記号(#)で置き換えられます。
- 名前は、データベースMAX_STRING_SIZEに応じて30文字または128文字に切り捨てられます。
- 名前が削除された後、列セット内の一意ではない名前には一意の連番が追加されます。切捨てが必要な場合でも、連番は維持されます。
TRANSFORM (default)すべての変換ルールを適用します。名前は128文字です。
QUOTE準拠していない名前は引用符で囲まれ、データベースMAX_STRING_SIZEに応じて30文字または128文字に短縮されます。
QUOTE128準拠していない名前は引用符で囲まれます。名前は128文字です。
UNIQUEロード・サービスの以前のリリースとの互換性オプション。列セット内の一意ではない名前には一意の連番が追加されます。切捨ては指定されません。
-
COLUMN_SIZE|COLUMNSIZE|COLSIZE {ACTUAL|ROUND|ROUNDED|MAX|MAXIMUM}表の列サイズ戦略を作成します。
ACTUALは、スキャン中に見つかった最大サイズを使用します。ROUND|ROUNDEDは、スキャン中に見つかった最大サイズより少し大きいサイズを使用します。MAX|MAXIMUMは、検出されたデータ型のデータベースの最大サイズを使用します。 -
COMMIT {ON|OFF}データ・コミットを有効化または無効化します。
-
DATE|DATE_FORMAT|DATEFORMAT format_maskロードされたすべてのDATEデータ型列のフォーマット。format_maskまたはDEFAULTを指定しないと、データベースのデフォルトが使用されます。
DATE列では、書式が設定されていない、および
SCAN_ROWS = 0の場合、データは有効なマスクでスキャンされません。ERRORS {number_of_rows | UNLIMITED}|-1: 許可されるエラー行数を示します。この数を超えると、ロードが終了します。
-1とUNLIMITEDはエラー制限がないことを示します。
いずれかの行が失敗した場合、バッチ内のすべての行がエラーになります。
-
LOCALE { <language country> | DEFAULT | "" }ロケール言語、およびオプションで国を指定します。
DEFAULT|"": デフォルト・ロケールに設定されます。 -
MAP_COLUMN_NAMES|MAPCOLUMNNAMES|MAPNAMES { OFF| (<file-col-name>=<table-col-name>,...) }ファイルに指定された列名から表の列名へのマッピングを提供します。
-
METHOD INSERTデータ・ロードに使用する方法。
-
SCAN_ROWS|SCANROWS|SCAN <1-5000>CREATE TABLEの生成のためにスキャンする行数を識別します。デフォルトは100行です。
-
TIMESTAMP|TIMESTAMP_FORMAT|TIMESTAMPFORMATロードされるすべてのTIMESTAMPデータ型列の形式。format_maskまたはDEFAULTを指定しないと、データベースのデフォルトが使用されます。TIMESTAMP列では、書式が設定されていない、およびSCAN_ROWSが0でない場合、データは有効なマスクでスキャンされます。
-
TIMESTAMPTZ|TIMESTAMPTZ_FORMAT|TIMESTAMPTZFORMATロードされるすべてのTIMESTAMPTZデータ型列の形式。format_maskまたはDEFAULTを指定しないと、データベースのデフォルトが使用されます。TIMESTAMPTZ列では、書式が設定されていない、およびSCAN_ROWSが0でない場合、データは有効なマスクでスキャンされます。
-
TRUNCATE {OFF|ON}「切捨て」をオンにすると、ロード前に表が切り捨てられます
-
UNKNOWN_COLUMNS_FAIL|UNKNOWNCOLUMNSFAIL|UNKNOWNFAIL {ON|OFF}ON: ファイルの列を表の列にマップできない場合、ロードを終了します。OFF: ファイルの列を表の列にマップできない場合でも、ロードの続行を許可します。
2.9.1.2 SET LOADFORMATコマンド
SET LOADFORMATでは、LOADコマンドの使用時にデータをロードするためのフォーマット・プロパティを設定できます。
構文
SET LOADFORMAT [ default|csv|delimited|html|insert|json|json-formatted|loader|t2|xml] [options...]ここで
default: ロード・フォーマット・プロパティがデフォルト値に戻ります。csv: カンマ区切り値。delimited: (csvシノニム)区切り形式、デフォルトではカンマ区切り値。-
html: Hypertext Markup Language。UNLOADコマンドの場合のみ。 insert: SQL挿入文。UNLOADコマンドの場合のみ。-
json: JavaScript Object Notation。UNLOADコマンドの場合のみ。 json-formatted: 整形されたJSON。UNLOADコマンドの場合のみ。loader: Oracle SQL Loader形式。UNLOADコマンドの場合のみ。t2: T2メトリック。UNLOADコマンドの場合のみ。xml: Extensible Markup Language。UNLOADコマンドの場合のみ。
optionsは、次の句を表します:
COLUMN_NAMES|COLUMNNAMES|NAMES {ON|OFF}: 列名付きのヘッダー行。DELIMITER{separator}: レコード内のフィールドを区切るデリミタ。DOUBLE [OFF]: (インポートのみ)埋め込まれている右囲みが二重になります。OFFは、埋め込まれている右囲みが二重にならず、埋め込まれている右囲みが予期しない結果を引き起こす可能性があることを示します。ENCLOSURES {enclosures|OFF}: オプションの左と右の囲み。- OFFは囲みがないことを示します
- 1文字が指定されている場合は、左と右の囲みをこの値に設定します。
- 2文字以上が指定されている場合は、左を最初の文字に設定し、右を2番目の文字に設定し、残りの文字は無視します。
- 複数の文字囲みを設定するには、ENCLOSURE_LEFTおよびENCLOSURE_RIGHTのセットを使用します。
ENCODING {encoding|OFF|""}: ロード・ファイルのエンコーディング。OFFおよび""は環境のデフォルト・エンコーディングにリセットします。LEFT|ENCLOSURE_LEFT|ENCLOSURELEFT {enclosure|OFF}: 1文字以上の左囲みを設定します。ENCLOSURE_RIGHTが指定されていない場合は、左と右の両方に使用されます。OFFは囲みがないことを示します。RIGHT|ENCLOSURE_RIGHT|ENCLOSURERIGHT {enclosure|OFF}: 1文字以上の右囲みを設定します。OFFは右囲みがないことを示します。ROW_LIMIT|ROWLIMIT|LIMIT} {number_of_rows|OFF|""}: 読み取る最大行数(ヘッダーを含む)。OFFおよび""の場合、制限なしに設定されます。SKIP|SKIP_ROWS|SKIPROWS {number_of_rows|OFF|""}: スキップする行数。[[SKIP_AFTER_NAMES|SKIPAFTERNAMES|AFTER]|[SKIP_BEFORE_NAMES|SKIPBEFORENAMES|BEFORE]]:(ヘッダー)列名行の前または後の行をスキップします。TERM|ROW_TERMINATOR {terminator|""|DEFAULT|CR|CRLF|LF}: 行の終わりを示す文字。ファイルに標準の行末文字が含まれている場合、line_endを指定する必要はありません。- ""またはDEFAULTは、
LOADコマンドのデフォルト(任意の標準の終了文字)を指定します。 - ""またはDEFAULTは、
UNLOADコマンドの環境のデフォルトを指定します。 - CRLFはWINDOWSの終了文字(一般に
UNLOADコマンド用)を指定します。 - LFはUNIXの終了文字(一般に
UNLOADコマンド用)を指定します。 - CRはMACの終了文字(一般に
UNLOADコマンド用)を指定します。
- ""またはDEFAULTは、
例
SQL> set loadformat delimited
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20,5555555555554444
SQL> set loadformat delimited enclosures <> line_end {eol}
7369,<SMITH>,<CLERK>,7902,17-DEC-80,800,,20,5555555555554444{eol}
SQL> set loadformat default (restore default settings)
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20,5555555555554444