LOAD DATA (SQL)

ファイルからのロード

JDBC ソースからのロード

• LOAD DATA FROM JDBC CONNECTION connection TABLE jdbcTable INTO table 外部 JDBC データ・ソースのデータをターゲット・テーブルにロードします。データ・ソース jdbcTable は、定義した SQL ゲートウェイ接続 connection を使用して接続する JDBC 準拠の SQL テーブルです。詳細は、"JDBC 経由での SQL ゲートウェイへの接続" を参照してください。

  この文は、JDBC ソース・テーブル countries のすべての列を Sample.Countries テーブルの対応する列にロードします。

  LOAD DATA FROM JDBC CONNECTION MyJDBCConnection
  TABLE countries
  INTO Sample.Countries
  

  

• LOAD DATA FROM JDBC CONNECTION connection TABLE jdbcTable (column, column2, ...) は、JDBC ソース・データを、位置的に指定されたターゲットのテーブル列にのみロードします。JDBC ソースの列が入力テーブルより少ない場合、挿入行でこれらの列が空白になります。

  この文は、JDBC の countries テーブルの最初の 3 つの列を、Countries テーブルの Name、Continent、および Region の列にロードします。テーブルでこれらの列が異なる順序で格納されている場合でも、あるいは、ここで示した 3 つの列の間に列が存在する場合でも、LOAD DATA は Name、Continent、Region にのみデータをロードします。

  LOAD DATA FROM JDBC CONNECTION MyConnection
  TABLE countries
  INTO Sample.Countries (Name,Continent,Region)
  

  

• LOAD DATA FROM JDBC CONNECTION connection TABLE jdbcTable ... INTO table ... VALUES (header,header2 ...) は、JDBC ソースのデータを VALUES 節で指定されたヘッダ名を持つ列のみにロードします。この構文を使用すると、JDBC ソース・テーブル内の任意の場所にある列データを、ターゲット・テーブルの任意の場所にある列に配置できます。

  INTO table 節内の列数は、VALUES 節内のヘッダ数と一致する必要があります。INTO table 節が列を指定しない場合、VALUES 節内のヘッダ数はテーブルのヘッダ数と一致する必要があります。この場合、ソース・データは位置に基づいてテーブルにロードされます。

  この文は JDBC countries テーブルの name、surface_area、および region 列のデータを Sample.Countries テーブルの対応する列にロードします。

  LOAD DATA FROM JDBC CONNECTION MyConnection
  TABLE countries
  INTO Sample.Countries (Name,SurfaceArea,Region)
  VALUES (name,surface_area,region)
  

  

  VALUES 節が SQL の列名を位置でマッチングする方法は、INSERT コマンドの構文と似ています。

• LOAD DATA FROM JDBC URL path TABLE jdbcTable INTO table 外部 JDBC データ・ソースのデータをターゲット・テーブルにロードします。path で定義されるデータ・ソースは、データ・ソースの接続 URL にあります。詳細は、"JDBC 経由での SQL ゲートウェイへの接続" を参照してください。

  この文は、JDBC ソース・テーブル countries のすべての列を Sample.Countries テーブルの対応する列にロードします。

  LOAD DATA FROM JDBC URL jdbc:oracle:thin:@//oraserver:1521/SID
  TABLE countries
  INTO Sample.Countries
  

  

一括ロード・オプション

これらのオプションは、データがテーブルに挿入される際に実行される一般的なチェックや操作を無効にします。これらのオプションを無効にすることにより、大量の行を含むデータのロードを大幅に高速化できます。

• LOAD BULK DATA FROM ... は、以下の INSERT %keyword オプションを指定してデータをロードします。

  • %NOCHECK — 一意の値のチェック、外部キーの参照整合性チェック、NOT NULL 制約 (フィールド・チェックが必要)、および列のデータ型、最大列長、列のデータ制約の検証を無効にします。

  • %NOINDEX — INSERT 処理の際にインデックス・マップの設定を無効にします。LOAD BULK DATA 操作時に、ターゲット・テーブルに対する SQL 文の実行が不完全となる、または正しくない結果を返す可能性があります。

  • %NOLOCK — INSERT の実行時に行のロックを無効にします。

  BULK キーワードを使用するには、%NOCHECK、%NOINDEX、および %NOLOCK 管理特権 (GRANT コマンドを使用して設定可能) が必要です。

  この文は、ファイルからバルク・データをロードします。

  LOAD BULK DATA FROM FILE 'C://mydata/countries.csv'
  INTO Sample.Countries
  

  

• LOAD %NOJOURN DATA FROM ... は、%NOJOURN INSERT %keyword オプションを指定してデータをロードします。このオプションは、挿入操作の間、ジャーナリングを抑制し、トランザクションを無効化します。%NOJOURN オプションを使用するには、%NOJOURN SQL 管理特権 (GRANT コマンドを使用して設定可能) が必要です。

  この形式の LOAD DATA コマンドは、ターゲット・テーブルに対するテーブルレベルのロックを取得しますが、各行は %NOLOCK により挿入されます。テーブル・レベル・ロックは、コマンドが完了すると解放されます。

  この文は、JDBC 接続を介してテーブルからデータをロードし、ジャーナリングを無効にします。

  LOAD %NOJOURN DATA FROM JDBC CONNECTION MyJDBCConnection
  TABLE countries
  INTO Sample.Countries
  

  

• LOAD %NOJOURN BULK DATA FROM ... は、前に指定した構文から INSERT %keyword オプションを指定してデータをロードします。%NOJOURN と BULK は、任意の順序で指定できます。

filePath

ロードするデータを含むテキスト・ファイルのサーバ側の位置。引用符で囲まれた完全なファイル・パスで指定します。

• ファイル内の各行は、テーブルにロードされる個別の行を指定します。新規行 ("\n") が既定の行区切り文字です。空白行は無視されます。

• 行内のデータ値は列区切り文字で区切られます。コンマが既定の列区切り文字です。プレースホルダ列区切り文字で示される未指定のデータを含め、すべてのデータ・フィールドが列区切り文字で示される必要があります。別の列区切り文字を定義するには、USING jsonOptions 節で columnseparator オプションを指定します。

• 既定では、エスケープ文字は定義されていません。データ値内にリテラルとして列区切り文字を含めるには、データ値を疑問符で囲みます。引用符付きのデータ値内に引用符を含めるには、引用符文字を二重にします ("")。エスケープ文字を定義するには、USING jsonOptions 節で escapechar オプションを指定します。

• 既定では、データ値はテーブル (またはビュー) 内のフィールドの順に指定されます。COLUMNS 節を使用すると、データを別の順序で指定できます。ビューを使用してデータ・レコードをテーブルにロードするには、ビュー内で定義されたフィールドの値のみを指定します。

• データ・ファイル・レコード内のすべてのデータは、テーブルのデータ条件に対して検証されます。この条件には、レコード内のデータ・フィールドの数、各フィールドのデータ型とデータ長などがあります。検証に失敗したデータ・ファイル・レコードは無視されます (ロードされません)。エラー・メッセージは発行されません。引き続き次のレコードのデータがロードされます。

table

データのロード先のテーブル。テーブル名は修飾 (schema.tablename)、未修飾 (tablename) のどちらでもかまいません。テーブル名が未修飾の場合は、既定のスキーマ名が使用されます。ビューを指定すると、そのビューからアクセスされるテーブルにデータをロードできます。

column

ファイル・データのロード先のテーブル列。ファイル内の列の順序で指定します。この列名のリストにより、選択するテーブルの列を指定し、データ・ファイル項目の順序を table 内の列に合わせることができます。テーブル内で定義されている未指定の列は、既定値を取ります。この節が省略された場合、table 内のすべてのユーザ定義フィールドが、データ・ファイルに表示されます。

header

データ・ソースからロードする列の特定に使用されるヘッダ値のコンマ区切りリスト。

• ヘッダ行を含まないファイル・ソースのデータをロードする際は、COLUMNS (header type, header2 type2, ...) 節内にヘッダを指定することにより列を指定します。

  • VALUES 節を含める場合、これらのヘッダ名を VALUES (header, header2) に指定して、テーブルにロードする列を選択します。

  • VALUES 節を含めない場合は、これらのヘッダ名はターゲット・テーブルの列名と一致させる必要があります。

• ヘッダ行を含むファイル・ソースのデータをロードする場合は、VALUES (header, header2) 節にヘッダを指定し、データのロード元のソース・ファイルのヘッダを特定します。これらのヘッダ名は、ソース・ファイルに存在している必要があります。

• JDBC ソースのデータをロードする場合は、VALUES (header, header2) 節にヘッダを指定し、データのロード元の JDBC ソース・テーブルの列を特定します。これらのヘッダ名は、JDBC ソース・テーブルに存在している必要があります。

type

COLUMNS (header type, header2 type2, ...) 節に指定されたヘッダのデータ型。各列のデータ型は、テーブルのデータ型と互換性がある必要があります。テーブルのデータ長 (COLUMNS のデータ長ではない) を使用してデータを検証します。

jsonOptions

USING 節で JSON (JavaScript Object Notation) オブジェクト、または JSON オブジェクトを含む文字列として指定されるロード・オプション。以下の各構文は同等です。

USING {"from":{"file":{"header":true}}}

USING '{"from":{"file":{"header":true}}}'

これらの JSON オブジェクトを使用して、SQL キーワードでは設定できないロード・オプションを設定します。"JSON 値" の説明のように、入れ子になった key:value ペア構文を使用してこれらのオブジェクトを指定します。

このオブジェクトの主な用途は、FROM FILE 構文を補足する、ロードされるデータのオプションの設定ですが、LOAD DATA の実行中に、並列処理し、エラーを許可するオプションもあります。この例は、複数のオプションが指定されたサンプルの JSON オブジェクトを示しています。ここに示されている空白はオプションで、読みやすくするためだけに使用されています。

USING
{
  "from": {
    "file": {
       "header": true,
       "skip": 2
       "charset": "UTF-8"
       "escapechar": "\\"
       "columnseparator": "\t"
    }
  }
}

以下のテーブルは、指定できるオプションを示しています。未指定のオプションは既定値を使用します。

jdbcConnection

JDBC ソースからのデータのロードに使用される、定義された SQL ゲートウェイ接続の名前。JDBC 接続の確立の詳細は、"JDBC 経由での SQL ゲートウェイへの接続" を参照してください。

jdbcTable

JDBC 接続を介してアクセスされる外部 SQL データ・ソース・テーブル。JDBC 接続の確立の詳細は、"JDBC 経由での SQL ゲートウェイへの接続" を参照してください。

jdbcTable

JDBC ソースからのデータのロードに使用される、SQL ゲートウェイ接続のURL。JDBC 接続の確立の詳細は、"JDBC 経由での SQL ゲートウェイへの接続" を参照してください。

INSERT 特権

テーブルで LOAD DATA を実行するには、ユーザがそのテーブルに対してテーブルレベルまたは列レベルの特権を持っている必要があります。特に、ユーザは、テーブルに対する INSERT 特権を持っている必要があります。テーブルの所有者 (作成者) にはそのテーブルに対するすべての特権が自動的に付与されます。所有者でない場合は、そのテーブルに対する特権が付与される必要があります。適切な特権がない場合、InterSystems IRIS では SQLCODE -99 エラーが発生します。

テーブルレベルの特権は、テーブルの全列に対して列レベルの特権を持っていることと同等ですが、まったく同じではありません。テーブルの列のサブセットに対する列レベルの特権のみがある場合、これらの列にデータをロードすることのみが可能です。許可のない列にデータをロードしようとすると、InterSystems IRIS では SQLCODE -99 エラーが発生します。

適切な特権があるかどうかを確認するには、%CHECKPRIV を使用します。ユーザにテーブルの特権を割り当てるには、GRANT を使用します。詳細は、"特権" を参照してください。

ゲートウェイ特権

LOAD DATA を実行するには、ユーザがサーバ上の JVM へのアクセス権を持っている必要があります。InterSystems IRIS の外部言語サーバへのアクセスと同様、そのような接続には特権が必要です。JVM に適切にアクセスするには、ユーザは %Gateway_Object:USE 特権を持っている必要があります。

アトミック性

LOAD DATA はアトミック処理です。他のアトミック処理と同様、LOAD DATA コマンドは、既定でトランザクションを使用することにより、成功しなかった場合に完全にロールバックされます。このコマンドが完了できなかった場合、データは挿入されず、データベースは LOAD DATA を発行する前の状態に戻ります。他のアトミック処理とは異なり、このルールには、注目すべき例外があります。これらの例外は以下のとおりです。

• LOAD BULK DATA と LOAD %NOJOURN DATA は、トランザクションを開始しません。

• LOAD DATA は、USING 節内で jsonoption を使用できるようにするため、アトミック処理の中でも独特です。maxerrors JSON を使用して、LOAD DATA コマンド中の挿入エラーに対する上限を指定できます。この上限に達すると、トランザクションは失敗し、データベースは LOAD DATA を発行する前の状態に戻ります。この制限に達しない場合は、トランザクションが成功し、正常にロードされたデータがデータベースに表示されます。ただし、ロードに失敗したデータはデータベースに表示されません。

現在のプロセスに対するこの既定は、SET TRANSACTION %COMMITMODE を呼び出すことによって SQL 内で変更できます。現在のプロセスに対するこの既定は、次の構文を使用して SetOption()Opens in a new tab メソッドを呼び出すことによって ObjectScript 内で変更できます。

SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval)

以下の intval 整数オプションを使用できます。

• 1 または IMPLICIT (自動コミットがオン — 既定) — LOAD DATA の呼び出しで独自のトランザクションを開始および終了します。

• 2 または EXPLICIT (自動コミットがオフ) — 進行中のトランザクションがない場合は、LOAD DATA コマンドによってトランザクションは自動的に開始されます。ただし、COMMIT または ROLLBACK で明示的にトランザクションを終了する必要があります。EXPLICIT モードでは、トランザクションあたりのデータベース操作の数は、ユーザ定義です。

• 0 または NONE (自動トランザクションなし) — LOAD DATA を呼び出してもトランザクションは開始されません。LOAD DATA 操作の失敗により、行の一部が挿入されたり挿入されなかったりすることで、データベースが整合性のない状態になる可能性があります。このモードでトランザクションのサポートを提供するには、START TRANSACTION を使用してトランザクションを開始し、COMMIT または ROLLBACK を使用してトランザクションを終了する必要があります。

  シャード・テーブルは常に、自動トランザクションなしのモードに設定されます。つまり、シャード・テーブルに対する挿入、更新、および削除はすべて、トランザクションの範囲外で実行されます。

現在のプロセスのアトミック性設定を確認するには、以下の ObjectScript の例のように、GetOption("AutoCommit")Opens in a new tab メソッドを使用します。

  SET stat=$SYSTEM.SQL.Util.SetOption("AutoCommit",$RANDOM(3),.oldval)
  IF stat'=1 {WRITE "SetOption failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET x=$SYSTEM.SQL.Util.GetOption("AutoCommit")
    IF x=1 {
      WRITE "Default atomicity behavior",!
      WRITE "automatic commit or rollback" }
    ELSEIF x=0 {
      WRITE "No transaction initiated, no atomicity:",!
      WRITE "failed DELETE can leave database inconsistent",!
      WRITE "rollback is not supported" }
    ELSE { WRITE "Explicit commit or rollback required" }

CSV ファイルから SQL テーブルおよびビューへのデータのロード

この例は、CSV (comma-separated value) ファイルに格納されているデータを既存のテーブルおよびビューにロードする方法を示しています。

データのロード先のテーブルを作成します。このテーブルには、メンバシップ・データを指定する 3 つのフィールド、メンバ ID、月単位でのメンバシップ期間の長さ、およびメンバが住んでいる米国の州 (2 文字の州の省略形を使用) が含まれます。

CREATE TABLE Sample.Members (
    MemberId INT PRIMARY KEY,
    MemberTerm INT DEFAULT 12,
    MemberState CHAR(2))

これらのデータ・レコードをテキスト・ファイルにコピーします。このファイルをローカル・マシンに保存し、members.csv という名前を付けます。このファイルは、メンバシップ ID とメンバの州の値を指定します。2 番目の行は、値が配置される位置の前に挿入されたプレースホルダのコンマによって示されているように、値が欠落しています。

6138830,MA
1720936,
4293608,NH

LOAD DATA を使用して Sample.Members テーブルにデータをロードします。ここに示されているパスを、ファイルを保存したパスに置き換えます。

LOAD DATA FROM FILE 'C://temp/members.csv' INTO Sample.Members (MemberId,MemberState)

データを確認します。MemberId 列と MemberState 列が生成されています。ソース・ファイルには MemberTerm 列のデータは含まれていなかったため、これらの列の値は既定の 12 となっています。欠落している行の値は、NULL 値としてロードされます。

SELECT * FROM Sample.Members

LOAD DATA の全体としての SQLCODE 結果は 0 (成功) であるため、LOAD DATA はデータが欠落していても SQLCODE エラーを報告しません。以下の場合、LOAD DATA 操作は成功と見なされます。

• LOAD DATA がソースにアクセスできる。

• ターゲット・テーブルが存在する。

• LOAD DATA 操作が有効である。例えば、操作により正しい数の列が指定され、それらの列名がターゲット・テーブルに存在する場合などです。

個々の行の SQLCODE エラーを、LOAD DATA 操作に関するその他の情報と共に表示するには、%SQL_Diag.Result テーブルと %SQL_Diag.Message テーブルを使用します。詳細は、"ロードされたデータの診断ログの表示" を参照してください。

最新の LOAD DATA 操作のメッセージを表示します。州の省略形が欠落している行は、SQLCODE エラー -104 を報告しています。表示されている結果は読みやすいように切り詰められています。

SELECT actor,message,severity,sqlcode
FROM %SQL_Diag.Message
WHERE diagResult = 
  (SELECT TOP 1 resultId
  FROM %SQL_Diag.Result
  ORDER BY resultId DESC)

テーブルからビューを作成する場合は、ビューを使用してデータをテーブルにロードすることもできます。Sample.Members テーブルのメンバシップ ID と状態の列のみを表示するビューを作成します。

CREATE VIEW Sample.VMem (MId,State) AS SELECT MemberId,MemberState FROM Sample.Members

これらの追加データ・レコードをテキスト・ファイルにコピーします。このファイルをローカル・マシンに保存し、members2.csv という名前を付けます。

6785674,VT
4564563,RI
4346756,ME

LOAD DATA を使用して、作成したビューを使用することにより、この新しい CSV データをテーブルにロードします。

LOAD DATA FROM FILE 'C://temp/members2.csv' INTO Sample.VMem(MId,State)

ビューによって返されたデータを確認します。ここには、ロードされた両方の CSV ファイルのデータが含まれています。

SELECT * FROM Sample.VMem

ベース・テーブルのデータを確認します。ここには、両方の CSV ファイルの結合された列データが含まれています。2 度目にロードされた CSV ファイルでも、MemberTerm 列の値には既定値の 12 が適用されています。

SELECT * FROM Sample.Members

ビューとテーブルを削除します。

DROP VIEW Sample.VMem

DROP TABLE Sample.Members

ロードされたデータの診断ログの表示

LOAD DATA の呼び出しごとに、%SQL_Diag.Result テーブルに新しい行が生成されます。このテーブルには、操作に関する診断情報が含まれます。これらの行は、SELECT クエリを使用して表示できます。例えば、以下のクエリは、最近の 5 つの LOAD DATA 呼び出しを返します。

SELECT TOP 5 * FROM %SQL_Diag.Result ORDER BY createTime DESC

返されるテーブルには、以下の列が含まれます。

• ID — ログ・エントリの整数の ID。この値は、テーブルの主キーです。

• resultId — ID と同じ。

• createTime — LOAD DATA 操作が行われ、ログ・エントリ行が作成された時点のタイムスタンプ。タイムスタンプは、ローカル時刻ではなく、UTC (協定世界時) です。

• namespace — LOAD DATA 操作が行われたネームスペース。

• processId — LOAD DATA 操作を実行したプロセスの整数の ID。

• user — LOAD DATA 操作を実行したユーザ。

• sqlCode — LOAD DATA 操作全体の SQLCODE。

• inputRecordCount — 正常にロードされたレコード数。

• errorCount — 発生したエラーの数。LOAD DATA コマンドの原因となるエラーや、個々のデータ行のロードまたは書き込みの失敗が含まれます。

• maxErrorCount — LOAD DATA が許容できる行挿入エラーの最大数。これを超えると操作は失敗となります。

• status — LOAD DATA 操作のステータス。LOAD DATA の実行中、ステータスは "In Progress" に設定されます。LOAD DATA 操作が完了すると、ステータスは "Complete" に更新されます。LOAD DATA の実行でエラーが発生すると、ステータスは "Failed" に更新されます。

• statement — この %SQL_Diag.Result レコードを生成するために実行された SQL 文のテキスト。

%SQL_Diag.Message は、%SQL_Diag.Result テーブルにログ記録された各 LOAD DATA 操作の詳細なメッセージ・データを提供します。%SQL_Diag.Message の diagResult 列は、%SQL_Diag.Result テーブルの resultId 列への外部キー参照であり、個々の LOAD DATA 操作のメッセージへのアクセスを可能にします。

例えば、このクエリは、resultId 29 を持つ LOAD DATA 操作に関連するすべてのエラー・メッセージを返します。このデータを使用して、テーブルのどの行がロードに失敗したのかを診断できます。

SELECT *
FROM %SQL_Diag.Message
WHERE severity = 'error'
AND diagResult = 29

返されるテーブルには、以下の列が含まれます。

• ID — メッセージの整数の ID。この値は、テーブルの主キーです。

• actor — サーバ、FileReader、JdbcWriter など、メッセージを報告したエンティティ。

• diagResult — %SQL_Diag.Result テーブルに記録された LOAD DATA ログ・エントリの行 ID。

• message — メッセージ・データ。エラーの場合、この列には SQLCODE 値と %msg テキストが含まれます。

• messageTime — ローカル時刻ではなく、UTC (協定世界時) でのメッセージのタイムスタンプ。

• severity — メッセージの重大度レベル。重大度は、対応する表示を持つ論理整数です。有効な値は、"completed"、"info"、"warning"、"error"、"abort」 です。

• sqlcode — メッセージの SQLCODE。"completed" または "info" の重大度のメッセージは、SQLCODE 0 を報告します。"warning" または "error" の重大度のメッセージは、そのメッセージに関連する SQLCODE を報告します。"abort" の重大度のメッセージは、SQLCODE -400 を報告します。