外部テーブルの作成

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

外部テーブルの概要

外部テーブルとは、論理的なテーブルオブジェクトを指します。このテーブルが対応する実際のデータはデータベース内部には保存されず、外部ストレージサービスに保存されます。

外部テーブルの詳細については、外部テーブルにに関するドキュメントをご参照ください。

前提条件

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

• OceanBaseクラスタをデプロイし、MySQLモードのテナントを作成していること。詳細な操作については、クラスタインスタンスの作成およびテナントの作成をご参照ください。

• 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|cos|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'

  $ACCESS_ID、$ACCESS_KEY、$HOST は、Alibaba Cloud OSS、Tencent Cloud COS、およびS3へのアクセスに必要なアクセス情報であり、これらの機密アクセス情報は暗号化されてデータベースのシステムテーブルに保存されます。

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ファイルでフィールド値を囲む記号を指定します。デフォルト値は空です。
  • 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タイプとして指定するために使用されます。

（オプション）PATTERNの定義

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

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

自動定義の外部テーブルパーティション

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

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

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

例

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

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 を設定する際は、ローカルのソケット接続を介してデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。詳細については、secure_file_priv に関するドキュメントをご参照ください。

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

      ssh admin@10.10.10.1
      

   2. 以下のコマンドを実行し、ローカルUnixソケット接続を介してテナント 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
   

関連ドキュメント

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