外部テーブルの作成

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

外部テーブルの概要

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

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

前提条件

外部テーブルを作成する前に、以下の事項を確認してください：

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

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

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

注意点

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

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

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

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

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

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

外部テーブル名の定義

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

例：

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

CREATE EXTERNAL TABLE students_csv external_options

列の定義

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

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

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 はHDFSの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：ログイン認証ユーザーを指します。非プライマリノードNNのprincipalに設定します。

        • keytab と krb5conf：シングルノードNNの設定と同じです。

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

  • NULL_IF：NULLとして処理される文字列を指定します。デフォルト値は空です。

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

  • SKIP_BLANK_LINES：空白行をスキップするかどうかを指定します。デフォルト値はFALSEで、空白行はスキップされません。

  • TRIM_SPACE：ファイル内のフィールドの先頭および末尾のスペースを削除するかどうかを指定します。デフォルト値はFALSEで、ファイル内のフィールドの先頭および末尾のスペースは削除されません。

  • EMPTY_FIELD_AS_NULL：空文字列をNULLとして処理するかどうかを指定します。デフォルト値はFALSEで、空文字列はNULLとして処理されません。

  • COMPRESSION：オプションです。ファイルの圧縮形式を指定します。取り得る値は以下の通りです：

    • GZIP/DEFLATE：GZIP圧縮ファイル。

    • ZSTD：ZSTD圧縮ファイル。

    • NONE：ファイルが圧縮されていないことを示します（デフォルト値）。

    • SNAPPY_BLOCK：HADOOP-SNAPPY圧縮ファイル。

      説明

      V4.4.2バージョンでは、V4.4.2 BP1バージョンから圧縮形式SNAPPY_BLOCKがサポートされています。

  • PARALLEL_PARSE_ON_SINGLE_FILE：単一のCSVファイルの並列解析を有効にするかどうかを指定します。指定しない場合、デフォルト値はTRUEです。

  • PARALLEL_PARSE_FILE_SIZE_THRESHOLD：このしきい値を超えるサイズのファイルが並列解析される可能性があるかどうかを指定します。単位はバイトです。デフォルト値は256MBです。

  • MAX_ROW_LENGTH：1行データの最大長をバイト単位で指定します。デフォルト値は2MBです。単一のCSVファイルを並列解析する際の定義段階で使用され、データ読み取りの正確性には影響しません。

  説明

  V4.4.2バージョンでは、FORMATオプションはV4.4.2 BP1バージョンから、PARALLEL_PARSE_ON_SINGLE_FILE、PARALLEL_PARSE_FILE_SIZE_THRESHOLD、MAX_ROW_LENGTHの各パラメータをサポートしています。

• FORMAT = ( TYPE = 'PARQUET'... )は、外部ファイルの形式をPARQUETタイプとして指定するために使用します。

• FORMAT = ( TYPE = 'ORC'... )は、外部ファイルの形式をORCタイプとして指定するために使用します。

（オプション）PATTERNの定義

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

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

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

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

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

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

例1

以下では、外部ファイルがローカルにある場合とOceanBaseデータベースのOracleモードにある場合の外部テーブル作成手順を例に説明します。

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

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

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

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

   1,'Emma'
   2,'William'
   3,'Olivia'
   

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

   注意

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

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

      ssh admin@10.10.10.1
      

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

      obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
      

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

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

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

   例：

   obclient -h10.10.10.1 -P2881 -usys@oracle001 -p****** -A
   

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

   CREATE EXTERNAL TABLE test_tbl1_csv (
       id INT,
       name VARCHAR(50)
       )
       LOCATION = '/home/admin/external_csv'
       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    |
   +------+---------+
   |    1 | Emma    |
   |    2 | William |
   |    3 | Olivia  |
   +------+---------+
   3 rows in set
   

例2

HDFS外部テーブルの作成例

Kerberosを使用する場合：

CREATE EXTERNAL TABLE ext_data(ID NUMBER(32), NAME VARCHAR2(30),SCORE NUMBER(32))
FORMAT = (
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
LOCATION = 'hdfs://localhost:8020/user?principal=principal_str&keytab=/path/to/keytab&krb5conf=/path/to/krb5conf_file&configs=xxx=xxx#xxx=xxx'
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 NUMBER(32), NAME VARCHAR2(30),SCORE NUMBER(32))
FORMAT = (
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
LOCATION = 'hdfs://localhost:8020/user'
PATTERN = 'test_tbl1.csv';

関連ドキュメント

外部テーブルの参照と更新に関するファイル情報については、外部ファイルの管理を参照してください。