CREATE FOREIGN TABLE (SQL)

ファイルから外部テーブルを作成

外部ファイルのデータを InterSystems IRIS のインスタンスに投影する外部テーブルを作成できます。このような場合、テーブルの作成先とする外部サーバでは、その外部データ・ラッパとして CSV を使用している必要があります。指定したファイルが存在するかどうかによって、動作にわずかな違いがあります。

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name [ USING json-options ] は、指定した名前のファイルに格納されているデータを投影した外部テーブルを作成します。

  • ファイルにヘッダがない場合、新しい外部テーブルの列には、ファイルの先頭 n 個の列にあるデータが置かれます。n は、プライマリ列リストの長さです。InterSystems SQL では、プライマリ列リストの列名を参照することで、この外部テーブルを問い合わせることができます。

    CREATE FOREIGN TABLE (
       firstName VARCHAR(15), 
       lastName VARCHAR(15), 
       DOB DATE
    ) Sample.Person SERVER Sample.HospitalDir FILE 'person.csv'
    

    

  • ファイルにヘッダがある場合、プライマリ列リストの各列名は、ファイルにある各列のヘッダ名に対応している必要があります。ファイルの中でプライマリ列リストの列名に対応している列名のみが、投影したテーブルに置かれます。

    CREATE FOREIGN TABLE (
       firstName VARCHAR(15), 
       lastName VARCHAR(15), 
       DOB DATE
    ) Sample.Person SERVER Sample.HospitalDir FILE 'person.csv' USING { "from": { "file": { "header": true } } }
    

    

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name COLUMNS ( col-name type, col-name2 type2, ... ) [ USING json-options ] は、指定のファイルに格納されているデータを投影した外部テーブルを作成します。このファイルでは、COLUMNS 節で指定した順序で列が並んでいる必要があります。プライマリ列リストにある名前は、外部テーブルの列名を指定し、それぞれはファイルの各列と相互に位置的な関連性があります。COLUMNS 節に記述した名前は、プライマリ列リストにある名前と同一で、それぞれが節とリストの両方に存在する必要があります。COLUMNS 節を使用して、ファイルから取得する列を並べ替えることができます。COLUMNS 節に記述する列の順序が、プライマリ列リストでの列の順序と一致している必要はありません。外部テーブルでの列の順序は、COLUMNS 節に記述した列名の位置で決まります。

  ファイルにヘッダがあっても無視されるので、このコマンドの動作は変わりません。その場合は、USING 節で from.file.header JSON オプションに true を指定する必要があります。

  CREATE FOREIGN TABLE Sample.Person (
     FileColumnOne VARCHAR(10),
     FileColumnTwo VARCHAR(20)
  ) SERVER Sample.HospitalDir FILE person.csv COLUMNS (FileColumnTwo VARCHAR(20), FileColumnOne VARCHAR(10))
  

  

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name COLUMNS ( col-name type, col-name2 type2, ... ) VALUES ( header, header2, ...) [ USING json-options ] は、指定のファイルに格納されたデータを投影した外部テーブルを作成します。このファイルでは、VALUES 節で指定した順序で列が並んでいる必要があります。外部テーブルでは、.csv ファイルの特定の列が欠落することがあります。プライマリ列リストでは、外部テーブルに置かれる列の名前とタイプを定義します。COLUMNS 節には、ファイルにある列の名前とタイプを記述します。ここに記述した列の数が、プライマリ列リストの列数より多くなってもかまいませんが、列名は同一にする必要があります。VALUES 節では、COLUMNS 節に記述した列名を並べ替えますが、列数はプライマリ列リストの列数と同じになります。

  VALUES 節を使用して、COLUMNS 節でファイルから指定した列のうち、特定の列を外部テーブルから除外できます。VALUES 節に名前を記述した順序は、プライマリ列リストでの列名の順序にマッピングされます。InterSystems SQL では、プライマリ列リストの列名を参照することで、この外部テーブルを問い合わせることができます。

  ファイルにヘッダがあっても無視されるので、このコマンドの動作は変わりません。その場合は、USING 節で from.file.header JSON オプションに true を指定する必要があります。

  以下の例では、FieldOne 列が COLUMNS 節の 2 番目の要素を投影し、FieldTwo 列が COLUMNS 節の 1 番目の要素を投影します。また、FieldThree 列が COLUMNS 節の 4 番目の要素を投影します。

  CREATE FOREIGN TABLE Sample.Person (
     FieldOne VARCHAR(10),
     FieldTwo VARCHAR(20),
     FieldThree INTEGER
  ) SERVER Sample.HospitalDB FILE person.csv COLUMNS (FirstName VARCHAR(10), LastName(20), DOB DATE, Age INTEGER) VALUES (LastName, FirstName, Age)
  

  

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name VALUES ( header, header2, ...) [ USING json-options ] は、指定のファイルに格納されたデータの一部を投影する外部テーブルを作成します。VALUES 節に記述する列名は、.csv ファイルでの列名に対応している必要があります。この列名が、プライマリ列リストでの名前と異なっていてもかまいません。外部テーブルでの列の順序は、プライマリ列リストでの列の順序で決まります。これらの列には、VALUES 節でその列と位置的に関連する要素のデータが置かれます。

  ファイルにヘッダがない場合、VALUES 節は無視され、意味がないものになります。

  CREATE FOREIGN TABLE Sample.Person (
     FirstName VARCHAR(10),
     LastName VARCHAR(20)
  ) SERVER Sample.HospitalDB FILE person.csv VALUES (FirstNameInFile, LastNameInFile) USING { "from": { "file": { "header": 1 } } }
  

  

データベースから外部テーブルを作成

外部データベースのデータを InterSystems IRIS のインスタンスに投影する外部テーブルを作成できます。このような場合、テーブルの作成先とする外部サーバでは、その外部データ・ラッパとして JDBC を使用している必要があります。

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ... ) SERVER server-name [ TABLE external-table ] は、指定のデータベースに存在するテーブルのデータを投影する外部テーブルを作成します。作成されたテーブルには、外部データベースにあるテーブルと同じ列が置かれます。TABLE 節を省略すると、InterSystems IRIS は、名前が table-name の外部サーバ上にあるテーブルにアクセスしようとします。

  CREATE FOREIGN TABLE Sample.Person (
     FirstName VARCHAR(10),
     LastName VARCHAR(20)
  ) SERVER Sample.ExternalDB TABLE 'hospital.people'
  

  

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name SERVER server-name QUERY query は、外部データベースに存在するテーブルに対して実行したクエリ query で返されたデータを投影する外部テーブルを作成します。外部データベースに対して実行する前のクエリが InterSystems SQL で検証されることはありません。

  CREATE FOREIGN TABLE Sample.Team (
     FirstName VARCHAR(10),
     LastName VARCHAR(20)
  ) SERVER Sample.ExternalDB QUERY 'SELECT FirstName,LastName FROM Hospital.Patients'
  

  

• CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ... ) SERVER server-name TABLE external-table VALUES ( header, header2, ... ) は、指定したテーブルに格納されているデータを投影した外部テーブルを、外部データ・ソースでの列名とは異なる列名で作成します。VALUES 節のヘッダ名で外部データ・ソースの列名が特定されますが、これは列リストで指定した名前と異なっていることがあります。したがって、VALUES 節には、列リストの列と同じ数の列を指定する必要があります。

  CREATE FOREIGN TABLE Sample.Team (
     TeamID BIGINT,
     Name VARCHAR(100)
  ) SERVER Sample.ExternalDB TABLE 'hospital.teams' VALUES (team_id, name)
  

  

table-name

CREATE FOREIGN TABLE コマンドでは、作成する外部テーブルの名前を、有効な識別子でこの引数に指定します。テーブル名は修飾、未修飾のどちらでもかまいません。

• 未修飾の外部テーブル名には、構文 tablename を使用します。スキーマ名とピリオド (.) 文字は省略します。未修飾のテーブルには既定のスキーマ名が使用されます。システム規模で既定の初期のスキーマ名は、既定のクラス・パッケージ名である User に対応する SQLUser です。スキーマ検索パスの値は無視されます。

  JDBC 接続を使用して外部テーブルを作成していて、TABLE 節を省略している場合は、その未修飾テーブル名を外部データ・ソースに対して利用して投影が作成されます。ただし、このテーブルには、既定のスキーマで修飾した名前で InterSystems SQL からアクセスできます。

  システム全体の既定のスキーマ名は構成可能です。

  現在のシステム全体の既定のスキーマ名を確認するには、$SYSTEM.SQL.Schema.Default()Opens in a new tab メソッドを使用します。

• 修飾した外部テーブル名には構文 schema.tablename を使用します。既存のスキーマ名を指定することも、新規スキーマ名を指定することもできます。既存のスキーマ名を指定すると、そのスキーマに外部テーブルが配置されます。新規スキーマ名を指定すると、その名前のスキーマとそれに関連するクラス・パッケージが作成され、外部テーブルがそのスキーマに配置されます。

column

CREATE FOREIGN TABLE コマンドでは、作成するテーブルの列を定義する 1 つの列名または列名のコンマ区切りのリストをプライマリ列リストに指定します。列名は任意の順序で指定でき、スペースを使用して、列名に関連付けたデータ型から分離します。規則によれば、通常は各列の定義を別々の行にインデントして記述します。この規則は必須ではありませんが、従うことをお勧めします。

プライマリ列リストは括弧で囲んで記述します。

type

column で指定した列名の InterSystems SQL データ型クラス。データ型を指定すると、列で使用できるデータ値がそのデータ型に適した値に制限されます。InterSystems SQL は、大半の標準的な SQL データ型をサポートしています。

外部データ・ソースのデータには、プロジェクトの過程で指定の型が強制されます。日付形式が無効な場合など、フィールドに型を強制できない場合は実行時エラーが発生します。

server-name

CREATE FOREIGN TABLE コマンドでは、外部データ・ソースへのアクセスで使用する外部サーバ構成を、この引数で指定します。

修飾サーバ名または未修飾サーバ名のどちらで指定してもかまいません。未修飾の外部サーバ名を指定すると、既定のスキーマである SQLUser の中でその外部サーバが検索されます。修飾した外部サーバ名を指定すると、指定したスキーマの中でその外部サーバが検索されます。

指定されたスキーマに目的の外部サーバが見つからないと SQLCODE -360 エラーが発生します。

file-name

CREATE FOREIGN TABLE コマンドでは、InterSystems IRIS に投影するデータを収めた .csv ファイルの場所をこの引数で指定します。この場所は、完全なファイル・パスを引用符で囲んで定義します。このコマンドで指定した外部サーバで、その外部データ・ラッパに CSV オプションが使用されている場合にのみ、この引数を使用します。

• 外部テーブルに投影する行を、このファイルの行ごとに 1 行ずつ指定します。既定の行区切り文字は新規行文字 ("\n") です。空白行は無視されます。

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

• 既定では、エスケープ文字は定義されていません。データ値にリテラルとして列区切り文字を記述するには、そのデータ値を引用符で囲みます。引用符で囲んだデータ値に引用符を記述するには引用符文字を二重にします。エスケープ文字を定義するには、USING json-options 節で escapechar オプションを指定します。

• 既定では、外部テーブルに記述されたフィールドの順序でデータ値を指定します。COLUMNS 節と VALUES 節を使用すると、別の順序でデータを指定できます。

• .csv ファイルのすべてのデータは、テーブルのデータ条件と一致するか検証されます。この条件として、レコードにあるデータ・フィールドの数や、各フィールドのデータ型とデータ長などがあります。検証できないレコードがファイルにあるとエラー・メッセージが表示されます。.csv ファイルにある日時/時刻文は ODBC 形式とする必要があります。他の形式にすると、エラーが発生することや正しくないクエリ結果が得られることがあります。

col-name

CREATE FOREIGN TABLE コマンドでは、この引数を COLUMNS 節に使用します。ファイルにヘッダがない場合は、COLUMNS 節でファイルの各列名を指定します。ファイルにヘッダがあれば、多くの場合、COLUMNS 節を無視してかまいません。

header

CREATE FOREIGN TABLE コマンドでは、この引数を VALUES 節に使用します。さまざまなシナリオで VALUES 節を使用できます。

external-table

JDBC 接続で外部データ・ソースに接続する CREATE FOREIGN TABLE コマンドでは、InterSystems IRIS に投影する外部テーブルの名前をこの引数で指定します。列リストを省略すると、この方法で作成する外部テーブルにはデータ・ソースの列定義がコピーされます。この定義として、列名やサポートされているデータ型などがあります。

query

JDBC 接続で外部データ・ソースに接続する CREATE FOREIGN TABLE コマンドでは、外部テーブルの列定義と列データを取得するために外部データ・ソースのテーブルに対して実行するクエリを、この引数で指定します。これは、外部データ・ソースに対して実行する SELECT クエリです。

この方法で作成する外部テーブルには、外部データ・ソースから列定義がコピーされます。この定義として、列名やサポートされているデータ型などがあります。外部データ・ソースの複数のテーブルをこのクエリで結合して指定すると、それら複数のテーブルの列定義を外部テーブルにコピーできます。

json-options

JSON オブジェクトとして読み込むためのオプションや JSON オブジェクトを記述した文字列を、USING 節にこの引数で指定します。この使用方法は、LOAD DATA コマンドでこの引数に相当する引数の使用方法と同じです。その構文とオプションの詳しい概要は、LOAD DATA のドキュメントを参照してください。CREATE FOREIGN TABLE コマンドでは from.file ツリーでのオプションのみをサポートしている点に注意が必要です。