バッチインサートを用いると、表データを格納したファイル(CSVファイルなど)から一括して表への挿入ができます。 一般に通常のINSERT文を用いた挿入よりも高速ですが、以下の制限があります。
バッチインサートには特別なINSERT文を用います。 構文は以下のようなものです。
insert [ into ] <表名> [ (<列名>, ...) ] input from path '<データファイルパス>' [ with path '<スペックファイルパス>' ] [ hint '<ヒント文字列'> ]
以下の節で具体的な例とともに各構文要素の使用方法を説明します。 データファイルの仕様についてはA. データファイルフォーマットを参照してください。
最も単純なのはCSVファイルからの読み込みです。 以下では特に断りのないかぎり、下に示したスキーマで定義された表に挿入することを想定します。
create table TBL ( t_id int, t_name nvarchar(128), t_date datetime, primary key(t_id) );
以下のようなファイルを用いて、TBL表へバッチインサートを実行します。
[namelist.csv] 10,織田信長,2009-4-29 10:00:00 11,豊臣秀吉,2009-5-15 12:30:00 12,徳川家康,2009-6-5 7:45:30
構文は以下のようになります。
insert into TBL input from path '/work/data/namelist.csv';
データファイルパスに相対パスを書くと、DoqueDBのサーバープロセスが動作しているディレクトリからの相対パスになります。 サーバープロセスが動作しているディレクトリが不明な場合は絶対パスを指定するようにしてください。
上記のINSERT文を実行すると、TBLには以下のようなレコードが追加されます。
select * from TBL; => 10,織田信長,2009-04-29 10:00:00 11,豊臣秀吉,2009-05-15 12:30:00 12,徳川家康,2009-06-05 07:45:30
CSVファイルにTBL表の一部の列しか記述されていない場合はどうしたらいいでしょうか。
たとえば以下のように、IDとNAMEに相当するデータを持つCSVファイルからTBL表へ登録するとします。
[nameonly.csv] 13,石田三成 14,伊達政宗
この場合、通常のINSERT文と同様に、値を設定する列を指定します。
insert into TBL(id, name) input from path '/work/data/nameonly.csv';
このINSERT文を実行すると、TBLの内容は以下のようになります。
select * from TBL; => 10,織田信長,2009-04-29 10:00:00 11,豊臣秀吉,2009-05-15 12:30:00 12,徳川家康,2009-06-05 07:45:30 13,石田三成,(null) 14,伊達政宗,(null)
次はCSVファイルに余分なフィールドが含まれている場合です。
たとえば以下のように、ID、NAME、DATEのほかに関係のない値が含まれているCSVファイルからTBL表へ登録するとします。
[nameextra.csv] 15,上杉謙信,2009-8-15 9:15:45,越後の戦国大名 16,武田信玄,2009-10-26 8:30:00,甲斐の戦国大名
この場合、最後のフィールド(4番目)が余分です。
これを除いてバッチインサートの入力として使用するには、ヒント文字列に1番目から3番目だけを使う旨記述します。
insert into TBL input from path '/work/data/nameextra.csv' hint 'InputField=(1-3)';
TBLの内容は以下のようになります。
select * from TBL; => 10,織田信長,2009-04-29 10:00:00 11,豊臣秀吉,2009-05-15 12:30:00 12,徳川家康,2009-06-05 07:45:30 13,石田三成,(null) 14,伊達政宗,(null) 15,上杉謙信,2009-08-15 09:15:45 16,武田信玄,2009-10-26 08:30:00
InputFieldには以下のような書き方が可能です。
つまり、上の例ではInputField=(-3)と書いてもいいのでした。
次に、CSVファイルに見出しなどの余分な行が含まれている場合です。
たとえば、以下のように先頭行に見出しがあるCSVファイルからTBL表に登録するとします。
[nametitle.csv] 番号,名前,登録日 17,浅井長政,2009-5-7 14:50:25 18,毛利元就,2009-6-15 9:54:14
この場合、最初の行をスキップするには、ヒント文字列に2行目以降を使用する旨指定します。
insert into TBL input from path '/work/data/nametitle.csv' hint 'InputRecord=(2-)';
TBLの内容は以下のようになります。
select * from TBL; => 10,織田信長,2009-04-29 10:00:00 11,豊臣秀吉,2009-05-15 12:30:00 12,徳川家康,2009-06-05 07:45:30 13,石田三成,(null) 14,伊達政宗,(null) 15,上杉謙信,2009-08-15 09:15:45 16,武田信玄,2009-10-26 08:30:00 17,浅井長政,2009-05-07 14:50:25 18,毛利元就,2009-06-15 09:54:14
InputRecordの指定方法はInputFieldの場合と同様です。
DoqueDBの表には配列型の列が定義できます。 配列型の列にバッチインサートで値を入れるには、専用の区切り文字を使用します。 デフォルトではセミコロン(;)が使用されます。
たとえば以下のような表が定義されているとします。
create table TBL_with_ARRAY ( t_id int, t_name nvarchar(128), t_prefecture nvarchar(3) ARRAY[NO LIMIT], primary key(t_id) );
この表に値を入れるデータは以下のように作ります。
[namearray.csv] 1,北海道,北海道 2,東北,青森;岩手;秋田;宮城;福島 3,関東,東京;神奈川;千葉;埼玉;茨城;群馬;栃木
バッチインサートに使用するファイルに非ASCII文字が含まれている場合、codeヒントにより文字コードを指定することができます。
たとえば、ファイルの文字コードがUTF-8だった場合、以下のようにしてUTF-8であることを指定します。
insert into TBL input from path '/work/data/utf8file.txt' hint 'code="utf-8"';
codeヒントには以下のものが指定できます。
codeヒントで指定した文字コードは、データファイルのほか後述するスペックファイルの文字コードの指定にも使われます。
したがって、データファイルとスペックファイルの文字コードは同一である必要があります。
区切り文字列を指定することにより、CSV以外のファイルをバッチインサートに用いることもできます。
区切り文字列は、ヒントか後述するスペックファイルで指定します。 指定するキーには以下のものがあります。
各区切り文字列には1文字以上20文字以下の文字列を指定します。 区切り文字列には、ASCII英数字記号(ただしダブルクオートは除く)のみが使用できます。 それぞれに包含関係があってはいけません。
FieldSeparator="|\t|" RecordSeparator="\n::\n" ElementSeparator="@"
FieldSeparator="ここまで\tここから" … ASCII以外の文字が使用されているため。
RecordSeparator="\n::\n" ElementSeparator=":" … ElementSeparatorがRecordSeparatorに包含されているため。
なお、上の例で使用したように、区切り文字列の指定には\tと\nが使用できます。 それぞれTABと改行を表します。 LF、CR/LFのどちらでも改行と認識されます。
区切り文字列を使用したデータファイルの例を示します。
[namekugiri.txt] 4|<TAB>|東海|<TAB>|静岡@愛知@三重@岐阜 :: 5|<TAB>|北陸|<TAB>|新潟@富山@石川@福井 :: 6|<TAB>|甲信|<TAB>|山梨@長野
このデータを上述のTBL_with_ARRAYにバッチインサートするには以下のように指定します。
insert into TBL_with_ARRAY input from path '/work/data/namekugiri.txt'
hint 'FieldSeparator="|\t|" RecordSeparator="\n::\n" ElementSeparator="@"'
区切り文字列の指定は、スペックファイルと呼ぶテキストファイルに記述することができます。 スペックファイルの使用には、HINT句に記述する手間や誤記の可能性を減らす利点があります。
スペックファイルには区切り文字列の指定を改行で区切って並べます。順序は任意です。 先頭がシャープ記号(#)である行はコメントとして無視されます。
以下にスペックファイルの例を示します。
[oosystem.spec] # ○○システム用スペック FieldSeparator="::" RecordSeparator="\n==\n" ElementSeparator="@@"
スペックファイルを使用したバッチインサートは以下のように実行します。
insert into TBL input from path '/work/data/oosystem.txt'
with path '/work/spec/oosystem.spec';
バッチインサートで処理中にエラーが発生したときは、すべてのインサート結果を破棄し、処理を取り消します。 エラーには、データファイルがフォーマットと一致しない場合(たとえば数値であるべき個所に数字以外があるなど)も含まれます。
しかし、たとえばデータファイルの一部にだけ不正な箇所があり、その 場所が特定できない場合などには、データファイルを修正してはバッチインサートを試みるという作業を何度もする羽目になりかねません。 このような場合は、以下のようにErrorDataヒントでエラーデータ出力ファイルを指定します。
insert into TBL input from path '/work/data/namewitherror.txt'
hint 'ErrorData="/work/data/error.out"';
ErrorDataの指定があると、データファイルにフォーマットと合わない箇所があったとき、その部分を含むレコード(RecordSeparatorで挟まれた部分)が指定されたパスのファイルに書き出され、処理は継続されます。
たとえば、TBL_with_ARRAYに以下のデータファイルを適用するとします。 区切り文字はデフォルトのものを使用します。
[namewitherror.txt] 7,近畿,大阪;京都;滋賀;兵庫;奈良;和歌山 XXX,中国,岡山;広島;山口;鳥取;島根 9,四国,香川;徳島;高知;愛媛
このとき、2行目のデータは先頭のフィールドが数値でないためエラーになります。
これを上記のINSERT文でバッチインサートすると、正しい行だけが挿入されます。
select * from TBL_with_ARRAY;
=>
7,近畿,{大阪,京都,滋賀,兵庫,奈良,和歌山}
9,四国,{香川,徳島,高知,愛媛}
さらに、/work/data/error.outの内容は以下のようになります。
[error.out] XXX,中国,岡山;広島;山口;鳥取;島根
ErrorDataで指定したファイルに出力があった場合、適宜修正してバッチインサートのデータファイルとして使用することで、最終的にすべてのデータを挿入することができるようになります。
データファイルにはいくつかの特殊な書き方ができます。
たとえば、FieldSeparatorがカンマであるときにカンマをデータとして含んだ文字列を入れたいならば、その部分をダブルクオートで囲むことで実現できます。
[doublequote.txt] 10,"カンマ(,)を含んだデータ","改行を 含んだデータ" 11,普通のデータ,改行を 含むとエラーになる
最初の行のデータは、FieldSeparatorであるカンマやRecordSeparatorである改行をデータの一部として使用しています。 3行目のデータでは、3つ目の列値は「改行を」のみとなり、「含むとエラーになる」の行はエラーになります。
また、ダブルクオートをデータに入れたい場合は、その部分全体をダブルクオートで囲んだ上で、データとして扱いたいダブルクオートを2重に書きます。
[doublequotedata.txt]
12,"ダブルクオート("")を含んだデータ",普通のデータ
挿入結果は以下のようになります。
select * from TBL
=>
12,ダブルクオート(")を含んだデータ,普通のデータ
なお、ヒントにNoDoubleQuoteと書くとダブルクオートに特別な意味がなくなります。 すでにデータとしてダブルクオートを含んだデータファイルがあるが修正したくない、といった場合に使用できます。
以下のスキーマを持つ表に対して、
create table TBL_with_DEFAULT ( key int, col1 int default 123, col2 nvarchar(10) default 'x' )
以下のデータファイルでバッチインサートすると、
[nulldefault.txt] 13,,DEFAULT 14,NULL,"DEFAULT"
以下のような結果になります。
select * from TBL_with_DEFAULT => 13,123,x 14,(null),DEFAULT
これらの機能を無効にすることはできません。DEFAULTやNULLという文字列をデータにしたい場合は、ダブルクオートで囲んでください。
長い文字列やバイナリデータを登録する場合、データファイルに書くかわりに外部ファイルを用いることができます。 外部ファイル中のデータはそのまま使用されるため、区切り文字列やNULLなどの特殊な文字列を含んでいてもエスケープする必要がありません。
外部ファイルは「FILE ファイル名」のように指定します。 ファイル名には相対パスも使用でき、データファイルのあるディレクトリからの相対位置になります。
insert into TBL_extern input from path '/work/data/extern.txt'; [extern.txt] 15,FILE ../image/data1.jpg,画像データ 16,NULL,FILE /work/text/content.txt
この例では、ID=15の2つ目の列値に/work/image/data1.jpgの内容、ID=16の3つ目の列値に/work/text/content.txtの内容が挿入されます。
FILEという文字列をデータに含めたい場合は、データをダブルクオートで囲みます。 FILEという文字列が含まれたデータファイルを修正せずに使用したい場合、ヒントにNoExternと書くとこの機能を無効にできます。
これまではデータファイルを用いてバッチインサートする方法について説明しました。 逆に、表データからバッチインサート用ファイルを作ることができます。 この機能により、既存の表データを別のデータベースにコピーしたり、表単位でバックアップしたりすることができます。
表データからバッチインサート用ファイルを作るには以下のようにします。
select * output to path '/work/data/tabledata.csv' from TBL;
区切り文字列をヒントやスペックファイルで指定することもできます。
select * output to path '/work/data/tablewithhint.txt'
hint 'FieldSeparator="|\t|" RecordSeparator="::\n::"' from TBL;
区切り文字列をスペックファイルで記述しておいてデータファイルとともに保管しておくと、バッチインサートにも使用できるため便利です。
NoExtern、NoDoubleQuoteの指定は無視されます。 すべての文字列データはダブルクオートでエスケープされ、バイナリファイルや4Kバイト以上の文字列データは外部ファイルに出力されます。
外部ファイルのパス名は「レコードの通し番号/フィールドの通し番号.dat」になります。 対象のデータが配列だった場合はさらに「レコードの通し番号/フィールドの通し番号/配列の要素番号.dat」になります。 たとえば、2番目と3番目の列がバイナリであるような表からバッチインサート用ファイルを作ると、以下のようになります。
[tabledata.txt] 1,FILE "00000000/00000001.dat",NULL 2,FILE "00000001/00000001.dat",FILE "00000001/00000002.dat"
00000000や00000001といったディレクトリはデータファイルと同じ場所に作られます。
外部ファイルのパスはデータファイルからの相対パスで評価されるので、データファイルとこれらのディレクトリをまとめて管理することで、任意の場所に展開して使用することができます。
バッチインサートで使用するデータファイルのフォーマット仕様について説明します。
<ファイル> ::= <レコード> <RecordSeparator> <レコード> …
<レコード> ::= <フィールド> <FieldSeparator> <フィールド> …
<フィールド> ::= <データ>
| <データ> <ElementSeparator> <データ> …
<データ> ::= <文字列>
| <数値>
| <NULL指定>
| <FILE指定>
<文字列> ::= <ダブルクオートを含まない文字の並び>
| " <ダブルクオートを含まない文字の並び> "
| " <ダブルクオートを含まない文字の並び> "" <ダブルクオートを含まない文字の並び> …"
※ 先頭と末尾の"は無視されます。
※ ""は"として扱われます。
※ 先頭の"をデータとして扱わせるには"""とダブルクオートを3つ書きます。
※ "と"に囲まれた範囲では<XXXSeparator>は抽出されません。
※ 2MB(100万文字)を超える文字列は入れられません。
この制限値はパラメーターPlan_BulkMaxSizeで変更できます。
<数値>::= <SQL文で数値リテラルとして認識される文字列>
<NULL指定> ::= <空文字列>
| NULL
| DEFAULT
※ 空文字列の場合、列定義のDEFAULT指定が使用されます。
※ NULLの場合、NULL値が使用されます。(NOT NULL制約は検査されます。)
※ 「NULL」という文字列をデータにしたいときはダブルクオートで囲む必要があります。
※ DEFAULT指定を明示したい場合もあるため、DEFAULTと書いたときもDEFAULT指定を
意味することとします。
※ 「DEFAULT」という文字列をデータにしたいときはダブルクオートで囲む必要があります。
※ 配列の列に対してNULLを指定すると、NULLを要素として1つ持つ配列の意味になります。
<FILE指定> ::= FILE <文字列>
※ 「FILE」という文字列で始まるデータにしたいときはダブルクオートで囲む必要があります。
※ FILEと<文字列>の間に空白(0x20)を置かないときは、<文字列>は
ダブルクオートで挟まれている必要があります。
※ 対応する列がLOB型(BLOBまたはNCLOB)の場合のファイルサイズは2GBまでです。
それ以外の場合は文字列の直接指定と同じ制限になります。
※ FILEで指定されたファイル名が相対パスのとき、データファイルからの相対位置になります。
<RecordSeparator> ::= <セパレータ>
<FieldSeparator> ::= <セパレータ>
<ElementSeparator> ::= <セパレータ>
<セパレータ> ::= <ダブルクオートを含まない文字の並び>
※ スペックで指定された文字列とマッチする部分がセパレータになります。
※ スペック中の「\n」は、改行コード(0x0aまたは0x0d 0x0a)にマッチします。
※ スペック中の「\t」は、タブ(0x09)にマッチします。
バッチインサートのHINT句に使用することのできるヒント文字列の仕様を説明します。
FieldSeparator="文字列" … 無指定時は","です。
RecordSeparator="文字列" … 無指定時は"\n"です。
ElementSeparator="文字列" … 無指定時は";"です。
Code="文字列" … 文字コードを表す文字列("utf-8"または"")です。
""はOSデフォルト(LinuxならEUC)で、無指定時は""です。
InputField=(<カンマかハイフンで区切った数値列>) … 無指定時はすべてです。
InputRecord=(<カンマかハイフンで区切った数値列>) … 無指定時はすべてです。
ErrorData="文字列" … 文字列はFALSEまたはファイル名です。
FALSEならエラーが起きたらそのトランザクションが失敗します。
FALSE以外ならエラー行をそのファイルに出力し、続行します。
無指定時はFALSEです。
(ここで検出するエラーはフィールド数の不一致やデータの
異常など形式的なエラーのみで、Unique制約などファイルに
挿入して初めてわかるエラーは続行できません。)
NoExtern … 指定するとFILEがキーワードではなくなります。
(すなわち外部ファイルを使用しません。)
NoElementNull … 指定すると配列型にnullと書いたとき全体がnullになります。
NoDoubleQuote … 指定するとダブルクオートは特殊な意味を持ちません。
Copyright (c) 2023 Ricoh Company, Ltd. All rights reserved.