本記事では、Oracleモードでファイル外部テーブルを作成し、クエリする手順について説明します。完全な構文やLOCATION、FORMATなどのパラメータの説明については、CREATE EXTERNAL TABLEを参照してください。
外部ファイルを一時的に読み取るだけの場合は、FILESテーブル関数を使用してください。
前提条件
- クラスタがデプロイ済みでOracleモードのテナントが作成されており、データベースに接続されていること。
- 現在のユーザーに
CREATE権限が付与されていること。現在のユーザー権限を確認する操作については、ユーザー権限の確認を参照してください。CREATE TABLE権限がない場合は、管理者に連絡し権限の追加を依頼してください。ユーザー権限の付与に関する操作については、直接権限付与を参照してください。 - ローカルファイルのパスがsecure_file_privで許可されている範囲内であること。
- HDFSへのアクセスには、OceanBaseデータベースJAVA SDK環境のデプロイの設定が完了している必要があります。
例
ローカルCSVファイル外部テーブルの作成
ローカルマシンの/home/admin/oceanbase/ディレクトリにdata.csvファイルが存在すると仮定します。ファイルの内容は以下の通りです。
1,"lin",98
2,"hei",90
3,"ali",95
OBServerノード上で、テナント管理者がローカルUnixソケットを介してクラスタのOracleテナントに接続します。
接続例：
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********ローカルUnixソケットを介してOceanBaseデータベースに接続する具体的な操作と説明については、secure_file_privを参照してください。
データベースがアクセス可能なパス
/home/admin/oceanbase/を設定します。SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";コマンドの実行が成功した後、変更を有効にするにはセッションを再起動する必要があります。
データベースに再接続した後、外部テーブル
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データベースの内部データ型に変換してからデータ行を返します。先ほど作成した外部テーブル ext_t3 のクエリ例を以下に示します。
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
詳細なクエリ操作については、データの読み取りを参照してください。
次のステップ
- ファイルリストの更新：
ALTER EXTERNAL TABLE table_name REFRESH;。詳細については、外部ファイルの管理およびALTER EXTERNAL TABLEを参照してください。