外部テーブルの作成

本記事では、SQLステートメントを使用して外部テーブルを作成する方法について紹介します。また、外部テーブルを作成するための前提条件、外部テーブルの概要、注意事項などについても紹介し、いくつかの例も提供します。

外部テーブルの概要

外部テーブルは、論理的なテーブルオブジェクトを指し、その実際のデータストレージの位置はデータベース内部ではなく、外部ストレージサービスに保存されています。

外部テーブルの情報については、外部テーブルについてを参照してください。

前提条件

外部テーブルを作成する前に、以下の事項を確認する必要があります：

• OceanBaseクラスタをデプロイし、MySQLモードのテナントを作成していること。OceanBaseクラスタのデプロイに関する情報については、デプロイの概要を参照してください。

• OceanBaseデータベースのMySQLテナントに接続していること。データベースへの接続に関する詳細については、接続方法の概要を参照してください。

• データベースを作成していること。データベースの作成に関する詳細については、データベースの作成を参照してください。

• 既に CREATE 権限を保有していること。現在のユーザー権限を確認する操作の詳細については、ユーザー権限の確認を参照してください。この権限を持っていない場合は、管理者に連絡し権限の付与を依頼してください。ユーザー権限に関する操作については、直接権限付与を参照してください。

注意事項

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

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

• 外部テーブルがアクセスするファイルは外部ストレージシステムによって管理されているため、外部ストレージが利用できない場合、外部テーブルのクエリ実行時にエラーが発生します。

• 外部テーブルのデータは外部データソースに保存されているため、クエリを実行する際にはネットワーク超えやファイルシステムなどの要素が関与し、クエリのパフォーマンスに影響を与える可能性があります。そのため、外部テーブルを作成する際には、適切なデータソースと最適化戦略を選択し、クエリの効率を向上させる必要があります。

コマンドラインで外部テーブルを作成する

CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成してください。

外部テーブル名の定義

外部テーブルを作成する際には、まず外部テーブルに名前を付ける必要があります。混乱や誤解を避けるために、外部テーブルに名前を付ける際には、通常のテーブルと外部テーブルを区別するための特定の命名ルールやプレフィックスを使用することを推奨します。たとえば、外部テーブルの名前の末尾に _csv というサフィックスを付けることができます。

例：

学生情報に関する外部テーブルを作成する際には、students_csv という名前を付けることができます。

CREATE EXTERNAL TABLE students_csv external_options

列の定義

外部テーブルの列には、DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY などの制約を定義することはできません。

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

LOCATIONを定義する

LOCATION オプションは、外部テーブルファイルの保存先パスを指定するために使用されます。通常、外部テーブルのデータファイルは単独のディレクトリに保存され、そのフォルダ内にはサブディレクトリを含むことができます。テーブルを作成する際、外部テーブルはそのディレクトリ内のすべてのファイルを自動的に収集します。

OceanBaseデータベースは、以下の2種類のパス形式をサポートしています：

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

  注意

  ローカルLocation形式を使用するシナリオでは、システム変数 secure_file_priv を設定してアクセス可能なパスを設定する必要があります。詳細については、secure_file_privを参照してください。

• リモートLocationの形式は以下のとおりです：

  注意

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

  • LOCATION = '{oss|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path' の中で、$ACCESS_ID、$ACCESS_KEY、および $HOST は、Alibaba Cloud OSS、AWS S3、およびS3プロトコルに互換性のあるオブジェクトストレージにアクセスするために必要な設定のアクセス情報です。s3_region はS3を使用する際に選択したリージョン情報であり、これらの機密性の高いアクセス情報は暗号化された形式でデータベースのシステムテーブルに保存されます。

  • ファイルがHDFS上にある場合、以下の形式があります：

    • 単一のNameNode（NN）アドレスを使用してクラスタにアクセスする場合の形式は：LOCATION = hdfs://localhost:port/PATH です。ここで、localhost はHDFSのアドレスを指し、port はHDFSのポート番号を指し、PATH はHDFS内のファイルパスを指します。

      • Kerberos認証を使用する場合の形式は：LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx' です。ここで：

        • principal：ログイン認証ユーザーを指します。
        • keytab：ユーザー認証の秘密鍵ファイルのパスを指定します。
        • krb5conf：ユーザーがKerberos環境で使用する記述ファイルのパスを指定します。
        • configs：追加のHDFS構成パラメータを指定します。デフォルトは空ですが、Kerberos環境の場合、通常このパラメータには値があり、設定が必要です。例：dfs.data.transfer.protection=authentication,privacy は、データ転送の保護レベルを authentication と privacy に指定します。

    • Hadoop HA（高可用性）構成の論理ネームサービスを使用してクラスタにアクセスする場合の形式は：LOCATION = hdfs://nameserviceID/PATH です。ここで、nameserviceID はHadoop HAの論理ネームサービスIDを指し、PATH はファイルパスを指します。

      説明

      クライアント側のOBServerの設定にHAクラスタの nameservice 定義とフェールオーバー戦略が含まれていることを確認してください。

      • Kerberos認証を使用する場合の形式は：LOCATION = 'hdfs://nameserviceID/PATH?principal=xxx&keytab=xxx&krb5conf=xxx&configs=dfs.data.transfer.protection=${string}#dfs.nameservices=${nameservice id}#dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}#dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}#dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}#dfs.ha.automatic-failover.enabled.${nameservice id}=true#dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider' です。ここで：

        • principal：ログイン認証ユーザーを指し、非プライマリノードのNameNode (NN)のprincipalを設定します。

        • keytab と krb5conf：単一NameNodeの場合と同様に設定します。

        • configs：追加のHDFS構成パラメータを指定します。複数の構成パラメータを設定する必要があり、HA構成パラメータとセキュリティ構成パラメータに関連します：

          • dfs.data.transfer.protection=${string}：クラスタの dfs.data.transfer.protection の設定と一致します。
          • dfs.nameservices=${nameservice id}：現在のHAクラスタの namesevice（エイリアス）を指定します。
          • dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}：HAクラスタのnamenodeのバックアップIDリストを指定します。
          • dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}：namenode1 の具体的なnamenodeアドレスを設定し、クライアントのルーティングを容易にします。
          • dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}：namenode2 の具体的なnamenodeアドレスを設定し、クライアントのルーティングを容易にします。
          • dfs.ha.automatic-failover.enabled.${nameservice id}=true：HAクラスタが要求を受け取った際、自動的に利用可能なNameNodeにフェイルオーバーできるようにします。
          • dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider：HAクラスタでプライマリ／スタンバイの切り替えを行うロジックツールクラスを指定します。必要に応じてカスタムクラスを作成し、HAクラスタにアップロードすることも可能です。

      注意

      HA関連の一部構成パラメータは namespace と紐づいています。たとえば、以下の例3の mycluster のように、関連する設定を正しく対応付けて設定してください。

FORMATを定義する

• FORMAT = ( TYPE = 'CSV'... ) は、外部ファイルのフォーマットをCSVタイプとして指定するために使用されます。以下のパラメータがあります：

  • TYPE：外部ファイルのタイプを指定します。

  • LINE_DELIMITER：CSVファイルの行区切り文字を指定します。デフォルト値は LINE_DELIMITER='\n' です

  • FIELD_DELIMITER：CSVファイルの列区切り文字を指定します。デフォルト値は FIELD_DELIMITER='\t' です。

  • ESCAPE：CSVファイルのエスケープ文字を指定します。1バイトの文字のみ使用できます。デフォルト値は ESCAPE ='\' です。

  • FIELD_OPTIONALLY_ENCLOSED_BY：CSVファイル内のフィールド値を囲むための文字を指定します。デフォルト値は空です。

    注意

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

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

  • NULL_IF：NULL として扱う文字列を指定します。デフォルト値は空です。

  • SKIP_HEADER：ファイルのヘッダーをスキップし、スキップする行数を指定します。

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

  • TRIM_SPACE：ファイル内のフィールドの先頭および末尾の空白を削除するかどうか指定します。デフォルト値は FALSE で、ファイル内のフィールドの前後の空白を削除しないことを示します。

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

• FORMAT = ( TYPE = 'PARQUET'... ) は、外部ファイルのフォーマットをPARQUETタイプとして指定するために使用されます。
• FORMAT = ( TYPE = 'ORC'... ) は、外部ファイルのフォーマットをORCタイプとして指定するために使用されます。

（オプション）PATTERNを定義する

PATTERN オプションは、LOCATION ディレクトリ内のファイルをフィルタリングするための正規表現パターン文字列を指定するために使用されます。各 LOCATION ディレクトリ内のファイルパスについて、そのパターン文字列にマッチする場合、外部テーブルはこのファイルにアクセスします。マッチしない場合、外部テーブルはこのファイルをスキップします。このパラメータを指定しない場合、デフォルトで LOCATION ディレクトリ内のすべてのファイルにアクセスできます。外部テーブルは、LOCATION で指定されたパスにある PATTERN に一致するファイルのリストをデータベースシステムのテーブルに保存します。外部テーブルをスキャンする際は、このリストに基づいて外部ファイルにアクセスします。

（オプション）外部テーブルのパーティションを定義する

外部テーブルのパーティションを自動的に定義する

外部テーブルは、パーティションキーで定義された式に基づいて、パーティションを計算して追加します。クエリを実行する際に、パーティションキーの値または範囲を指定できます。この時、パーティションプルーニングが行われ、外部テーブルはそのパーティション内のファイルのみを読み取ります。

外部テーブルのパーティションを手動で定義する

手動でパーティションを追加および削除する必要がある場合、外部テーブルにパーティションの自動管理を任せるのではなく、PARTITION_TYPE = USER_SPECIFIED フィールドを指定する必要があります。

例

以下は、外部ファイルがローカルおよびOceanBaseデータベースのMySQLモードに存在する場合の外部テーブルを作成する例です。手順は次のとおりです：

1. 外部ファイルを準備します。

   以下のコマンドを実行して、OBServerノードにログインするマシンの /home/admin ディレクトリ内に test_tbl1.csv ファイルを作成します。

   [admin@xxx /home/admin]# vi test_tbl1.csv
   

   ファイルの内容は以下のとおりです：

   1,'Emma','2021-09-01'
   2,'William','2021-09-02'
   3,'Olivia','2021-09-03'
   

2. インポートファイルのパスを設定します。

   注意

   セキュリティ上の理由により、システム変数 secure_file_priv を設定する際は、ローカルSocket接続を通じてデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。詳細については、secure_file_privを参照してください。

   1. 以下のコマンドを実行して、OBServerノードが存在するマシンにログインします。

      ssh admin@10.10.10.1
      

   2. 以下のコマンドを実行し、ローカルUnix Socket接続を介してテナント mysql001 に接続します。

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

   3. 以下のSQLコマンドを実行し、インポートパスを /home/admin に設定します。

      SET GLOBAL secure_file_priv = "/home/admin";
      

3. テナント mysql001 に再接続します。

   例：

   obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Dtest
   

4. 以下のSQLコマンドを実行して、外部テーブル test_tbl1_csv を作成します。

   CREATE EXTERNAL TABLE test_tbl1_csv ( 
       id INT, 
       name VARCHAR(50), 
       c_date    DATE
       )
       LOCATION = '/home/admin'
       FORMAT = (
         TYPE = 'CSV'
         FIELD_DELIMITER = ','
         FIELD_OPTIONALLY_ENCLOSED_BY ='\''
         )
       PATTERN = 'test_tbl1.csv';
   

5. 以下のSQLコマンドを実行して、外部テーブル test_tbl1_csv のデータを確認します。

   SELECT * FROM test_tbl1_csv;
   

   実行結果は次のとおりです：

   +------+---------+------------+
   | id   | name    | c_date     |
   +------+---------+------------+
   |    1 | Emma    | 2021-09-01 |
   |    2 | William | 2021-09-02 |
   |    3 | Olivia  | 2021-09-03 |
   +------+---------+------------+
   3 rows in set
   

例2

HDFSの外部テーブルを作成するには、OceanBaseデータベースをJava SDKがインストールされた環境で使用する必要があります。Java SDK環境のデプロイに関する詳細については、OceanBaseデータベースJava SDK環境のデプロイをご参照ください。

HDFS外部テーブルの作成例

Kerberosを使用する：

CREATE EXTERNAL TABLE ext_data ( 
  id INT, 
  name VARCHAR(50), 
  c_date    DATE
)
LOCATION = 'hdfs://localhost:8020/user?principal=principal_str&keytab=/path/to/keytab&krb5conf=/path/to/krb5conf_file&configs=xxx=xxx#xxx=xxx'
FORMAT = (
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
PATTERN = 'data.csv';

その中で：

• principal：ログイン認証ユーザーを指定します。
• keytab:ユーザー認証の秘密鍵ファイルを指定します。
• krb5conf:ユーザーがKerberos環境で使用する記述ファイルを指定します。
• configs: 追加のHDFS構成パラメータを指定します。デフォルトは空ですが、Kerberos環境の場合、通常このパラメータには値があり、設定が必要です。例：dfs.data.transfer.protection=authentication,privacy は、データ転送の保護レベルを authentication と privacy に指定します。

Kerberosを使用しない：

CREATE EXTERNAL TABLE ext_data ( 
  id INT, 
  name VARCHAR(50), 
  c_date    DATE
)
LOCATION = 'hdfs://localhost:8020/user'
FORMAT = (
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  FIELD_OPTIONALLY_ENCLOSED_BY ='\''
  )
PATTERN = 'test_tbl1.csv';

関連ドキュメント

• 外部テーブルを削除する必要がある場合は、通常のテーブルを削除するのと同じ方法で操作できます。テーブルの削除の詳細については、テーブルの削除を参照してください。
• 外部テーブルのアクセス可能なファイルの確認と更新に関する情報については、外部ファイルの管理 をご覧ください。