外部テーブルの作成

CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成します。外部テーブルを作成する際には、外部ファイルからデータを読み取るために、データファイルのパスとデータファイルの形式を指定する必要があります。

権限要件

外部テーブルを作成するには、現在のユーザーがCREATE TABLE権限を持っている必要があります。現在のユーザー権限を確認する操作については、ユーザー権限の確認を参照してください。CREATE TABLE権限がない場合は、管理者に連絡し、追加してもらってください。ユーザーに権限を付与する関連操作については、直接権限付与を参照してください。

外部テーブルの作成

外部テーブルを作成するSQL文は以下のとおりです：

CREATE EXTERNAL TABLE table_name
( col_name col_type [AS (metadata$filecol{N})]
  [ , col_name col_type [AS (metadata$filecol{N})] ]
  [ , ... ] )
LOCATION = '<string>'
FORMAT = (
  TYPE = 'CSV'
  LINE_DELIMITER = '<string>' | <expr>
  FIELD_DELIMITER = '<string>' | <expr>
  ESCAPE = '<character>' | <expr>
  FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
  ENCODING = 'charset'
  NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...)
  SKIP_HEADER = <int>
  SKIP_BLANK_LINES = { TRUE | FALSE }
  TRIM_SPACE = { TRUE | FALSE }
  EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
[ PATTERN = '<regex_pattern>' ]

関連パラメータの説明は以下のとおりです：

• col_name col_type [AS (metadata$filecol{N})]：列を定義します。ここで、AS (metadata$filecol{N}) は列マッピングを手動で定義するために使用されます。

  外部テーブルがサポートする列タイプは通常のテーブルと同じです。OceanBaseデータベースのOracleモードでサポートされるデータ型と詳細については、データ型の概要を参照してください。

  デフォルトでは、外部ファイル内のデータ列と外部テーブルで定義された列は自動的に順序付けられて対応します。つまり、外部テーブルの最初の列は外部ファイルの最初の列のデータに対応します。

  例えば、以下の例では、外部テーブル ext_t1 の C1 列は外部ファイルの1列目のデータに自動的にマッピングされます。C2 列は外部ファイルの2列目のデータに自動的にマッピングされます。

  CREATE EXTERNAL TABLE ext_t1 (
    C1 int,
    C2 int
    )
    LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
    FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    );
  

  外部ファイル内の列の順序と外部テーブルで定義された列の順序が一致しない場合、metadata$filecol{N} のような擬似列を使用して、外部テーブルの列が外部ファイルのN列目に対応するように指定できます。ここで、ファイル内の列は1から番号が付けられます。

  例えば、以下の例では、C1 int AS (metadata$filecol2) は外部テーブル ext_t2 の C1 列がファイル内の2列目に対応することを意味します。C2 int AS (metadata$filecol4) は外部テーブル ext_t2 の C2 列が外部ファイル内の4列目に対応することを意味します。

  CREATE EXTERNAL TABLE ext_t2 (
              C1 int AS (metadata$filecol2),
              C2 int AS (metadata$filecol4)
    )
    LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
    FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    );
  

  注意

  列マッピングを手動で定義する必要がある場合、自動列マッピング機能は無効になり、すべての列を手動でマッピングする必要があります。

• LOCATION = '<string>'：外部ファイルの保存パスを指定します。通常、外部テーブルのデータファイルは別のディレクトリに保存され、そのフォルダにはサブディレクトリを含むことができます。外部テーブルを作成する際、外部テーブルはそのディレクトリ内のすべてのファイルを自動的に収集します。

  以下の2種類の形式がサポートされています：

  • ローカルLocation形式：LOCATION = '[file://] local_file_path'

    • local_file_path：相対パスまたは絶対パスのいずれかです。相対パスを入力する場合、現在のディレクトリはOceanBaseデータベースのインストールディレクトリである必要があります。

      注意

      local_file_pathはファイルではなくディレクトリである必要があります。個別のファイルを指定する必要がある場合は、LOCATIONでそのファイルの上位ディレクトリを指定し、PATTERNプロパティを設定してそのファイルを指定する必要があります。

    • ローカルLocation形式を使用する場合、システム変数 secure_file_priv を使用してOceanBaseデータベースがアクセス権を持つファイルパスを設定する際、secure_file_priv は local_file_path の上位ディレクトリでなければなりません。つまり、local_file_path は secure_file_priv パスのサブパスにしかなりません。

      テナントレベルのシステム変数 secure_file_priv は、ファイルへのインポートまたはエクスポート時にOceanBaseデータベースがアクセスできるパスを制御します。secure_file_priv の詳細については、secure_file_privを参照してください。

  • リモートLocation形式：LOCATION = '{oss}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'

    $ACCESS_ID、$ACCESS_KEY、$HOST はAlibaba Cloud OSSにアクセスするために設定する必要があるアクセス情報であり、これらの機密性の高いアクセス情報は暗号化された形式でデータベースのシステムテーブルに保存されます。

    注意

    オブジェクトストレージパスを使用する場合、オブジェクトストレージパスの各パラメータは &記号で区切られます。入力するパラメータ値には、大文字と小文字、数字、/-_$+= およびワイルドカードのみを含めてください。上記以外の他の文字を入力した場合、設定が失敗する可能性があります。

• FORMAT：外部ファイルの形式を定義します。

  • TYPE：外部ファイルのタイプを指定します。現在はCSVファイルのみサポートされています。

  • LINE_DELIMITER：ファイルの行区切り文字を指定します。

  • FIELD_DELIMITER：ファイルの列区切り文字を指定します。

  • ESCAPE：ファイルのエスケープ文字を指定します。

  • FIELD_OPTIONALLY_ENCLOSED_BY：ファイル内のフィールド値を囲む記号を指定します。例えば、ESCAPE = '"' は値をダブルクォーテーションマークで囲むことを意味します。指定しない場合、デフォルト値は空です。

    注意

    外部テーブルデータファイルに NULL 値（文字列 NULL ではなく、"NULL" でもない）が含まれている場合、FIELD_OPTIONALLY_ENCLOSED_BY パラメータを明示的に設定する必要があり、そのパラメータ値は空にしてはなりません。

  • ENCODING：ファイルの文字セットエンコーディング形式を指定します。現在のOracleモードでサポートされているすべての文字セットについては、文字セットを参照してください。指定しない場合、デフォルト値はUTF8MB4です。

  • NULL_IF：どの文字列を NULL として扱うかを指定します。指定しない場合、デフォルト値は空です。

  • SKIP_HEADER：ファイルヘッダーをスキップする行数を指定します。指定しない場合、デフォルトではファイルヘッダーはスキップされません。

  • SKIP_BLANK_LINES：空白行をスキップするかどうかを指定します。指定しない場合、デフォルト値は FALSE です。

  • TRIM_SPACE：ファイル内のフィールドの先頭と末尾のスペースを削除するかどうかを指定します。指定しない場合、デフォルト値は FALSE です。

  • EMPTY_FIELD_AS_NULL：空の文字列を NULL として扱うかどうかを指定します。指定しない場合、デフォルト値は FALSE です。

• PATTERN：正規パターン文字列を指定して、LOCATION ディレクトリ内のファイルをフィルタリングします。各 LOCATION ディレクトリ内のファイルについて、このパターン文字列とマッチする場合、外部テーブルはそのファイルにアクセスできます。そうでない場合、外部テーブルはそのファイルをスキップします。このパラメータを指定しない場合、デフォルトでは LOCATION ディレクトリ内のすべてのファイルにアクセスできます。

ローカルマシンの /home/admin/oceanbase/ パスに data.csv ファイルが保存されていると仮定します。ファイルの内容は以下のとおりです。

1,"lin",98
2,"hei",90
3,"ali",95

1. OBserverノード上で、テナント管理者はローカルUnixソケットを使用してクラスタのOracleテナントに接続します。

   接続例は以下のとおりです：

   obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
   

   ローカルUnixソケットを使用してOceanBaseデータベースに接続する具体的な操作と説明については、secure_file_privを参照してください。

2. データベースがアクセスできるパス /home/admin/oceanbase/ を設定します。

   SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";
   

   コマンドの実行が成功した後、変更を反映させるにはセッションの再起動が必要です。

3. データベースに再接続した後、外部テーブル ext_t3 を作成します。

   CREATE EXTERNAL TABLE ext_t3(ID NUMBER(32), NAME VARCHAR2(30),SCORE NUMBER(32))
   LOCATION = '/home/admin/oceanbase/'
   FORMAT = (
   TYPE = 'CSV'
   FIELD_DELIMITER = ','
   FIELD_OPTIONALLY_ENCLOSED_BY ='"'
   )
   PATTERN = 'data.csv';
   

外部テーブルの作成が成功すると、通常のテーブルと同様に SHOW CREATE TABLE ステートメントを使用してテーブルの定義を確認できます。

SHOW CREATE TABLE ext_t3;

クエリ結果は次のとおりです：

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE  | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXT_T3 | CREATE EXTERNAL TABLE "EXT_T3" (
  "ID" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL1),
  "NAME" VARCHAR2(30) GENERATED ALWAYS AS (METADATA$FILECOL2),
  "SCORE" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

通常のテーブルと同様にアクセスすることもできます。外部テーブルを照会する際、システムは外部テーブルのドライバーレイヤーを介して外部ファイルを直接読み取り、ファイル形式に従って解析し、OceanBaseデータベース内部のデータ型に変換してからデータ行を返します。次に、作成したばかりの外部テーブル lineitem を照会する例を示します。

SELECT * FROM ext_t3;

クエリ結果は次のとおりです：

+----+------+-------+
| ID | NAME | SCORE |
+----+------+-------+
|  1 | lin  |    98 |
|  2 | hei  |    90 |
|  3 | ali  |    95 |
+----+------+-------+
3 rows in set

さらに、外部テーブルと通常のテーブルを組み合わせてクエリ操作を行うこともできます。現在のデータベースに通常のテーブル info があり、そのテーブルのデータは以下のとおりです：

+------+--------+------+
| NAME | SEX    | AGE  |
+------+--------+------+
| lin  | male   |    8 |
| hei  | male   |    9 |
| li   | female |    8 |
+------+--------+------+
3 rows in set

外部テーブル ext_t3 と通常のテーブル info を組み合わせてクエリを行う例を示します。

SELECT info.* FROM info, ext_t3 WHERE info.name = ext_t3.name AND ext_t3.score > 90;

クエリ結果は次のとおりです：

+------+--------+------+
| NAME | SEX    | AGE  |
+------+--------+------+
| lin  | male   |    8 |
| li   | female |    8 |
+------+--------+------+
2 rows in set

その他のクエリ操作については、データの読み取りを参照してください。

外部テーブルの使用上の注意点

• 外部テーブルはクエリ操作のみを実行でき、DML操作は実行できません。

• 外部テーブルをクエリする際、外部テーブルがアクセスする外部ファイルが削除されている場合、システムはエラーを報告せず、空の行を返します。

• 外部テーブルがアクセスするファイルは外部ストレージシステムによって管理されているため、外部ストレージが利用不可能な場合、外部テーブルのクエリはエラーを報告します。

次のステップ

外部テーブルを作成すると、システムはLOCATIONで指定されたパスにあるPATTERNに一致するファイルの一覧をOceanBaseデータベースのシステムテーブルに保存します。外部テーブルのスキャン時には、このリストに基づいて外部ファイルにアクセスします。外部ディレクトリに新しいファイルが追加された場合は、外部テーブルのファイルリストに新しいファイルを追加して外部テーブルのファイルを更新する必要があります。詳細な操作については、外部ファイルの管理を参照してください。

外部テーブルは作成後に削除することもできます。外部テーブルを削除するステートメントは通常のテーブルと同じです。DROP TABLEステートメントを使用して外部テーブルを削除します。詳細な操作については、テーブルの削除を参照してください。

関連ドキュメント

• 外部テーブルについて

• 外部ファイルの管理