はじめに
Autonomous Databaseのアップデートで、クラウド・ストレージ上のJSONファイルに対してJSONサーチ・インデックスを作成し、クラウド・ストレージ上のJSONファイルに対しての全文検索、非定型検索を実行可能にする機能が追加されたので、早速検証してみました。
JSONサーチ・インデックスに関しての詳細は、こちらのマニュアルをご参照ください。
今回は、Amazon S3上にあるJSONファイルに対してJSONサーチ・インデックスを作成して全文検索、非定型検索を実行してみました。
1.事前準備
今回は、コース（講義）の情報のJSONドキュメントを含むファイルcourse_data.jsonを使用しました。
各コース情報のJSONドキュメントには、コース番号、コース名、受講する学生の情報、担当する講師の情報などが含まれています。
{"courseId":"MATH101","name":"Algebra","creditHours":3,"students":[{"studentId":1,"name":"Donald P."},{"studentId":5,"name":"Hye E."}],"teacher":{"teacherId":101,"name":"Abdul J."},"Notes":"Prerequisite for Advanced Algebra"}
{"courseId":"MATH102","name":"Calculus","creditHours":4,"students":[{"studentId":2,"name":"Elena H."},{"studentId":10,"name":"Ming L."},{"studentId":9,"name":"Luis F."},{"studentId":4,"name":"Georgia D."}],"teacher":{"teacherId":101,"name":"Abdul J."}}
{"courseId":"CS101","name":"Algorithms","creditHours":5,"students":[{"studentId":1,"name":"Donald P."},{"studentId":2,"name":"Elena H."},{"studentId":4,"name":"Georgia D."},{"studentId":9,"name":"Luis F."},{"studentId":7,"name":"Jatin S."}],"teacher":{"teacherId":102,"name":"Betty Z."}}
{"courseId":"CS102","name":"Data Structures","creditHours":3,"students":[{"studentId":1,"name":"Donald P."},{"studentId":2,"name":"Elena H."},{"studentId":5,"name":"Hye E."},{"studentId":7,"name":"Jatin S."},{"studentId":8,"name":"Katie H."}],"teacher":{"teacherId":102,"name":"Betty Z."}}
{"courseId":"MATH103","name":"Advanced Algebra","creditHours":3,"students":[{"studentId":3,"name":"Francis K."},{"studentId":4,"name":"Georgia D."},{"studentId":8,"name":"Katie H."},{"studentId":9,"name":"Luis F."},{"studentId":6,"name":"Ileana D."}],"teacher":{"teacherId":103,"name":"Colin J."}}
course_data.jsonをS3バケットにアップロードします。
ここでは、東京リージョンにあるs3bucket-courseという名前のバケットにアップロードしました。
次に、S3にアクセスするためのクレデンシャルを作成します。
ここではARNを使用したクレデンシャルAWS_S3_CREDを作成します。
ARNを使用したクレデンシャルの作成方法の詳細はこちらの記事を参照ください。
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'AWS_S3_CRED',
4 params =>
5 JSON_OBJECT('aws_role_arn' value 'arn:aws:iam::nnnnnnnnnnnn:role/Role_for_ADW23ai',
6 'external_id_type' value 'database_ocid')
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
作成したクレデンシャルAWS_S3_CREDを使用してS3にアクセスできるかを確認します。
SQL> SELECT object_name, bytes
2 FROM DBMS_CLOUD.LIST_OBJECTS('AWS_S3_CRED', 'https://s3bucket-course.s3.ap-northeast-1.amazonaws.com');
OBJECT_NAME BYTES
-------------------- ----------
course_data.json 1360
SQL>
クレデンシャルAWS_S3_CREDを使用して、問題なくS3にアクセスすることができることが確認できました。
3. S3上にあるJSONファイルに対するJSONサーチ・インデックスの作成
S3上にあるJSONファイルに対して、DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXプロシージャを使用してJSONサーチ・インデックスを作成します。
指定するパラメータは以下のとおりです。
credential_name：S3にアクセスするためのクレデンシャルを指定
location_uri：S3のバケットまたはオブジェクトのURIを指定
index_name：作成するJSONサーチ・インデックスの名前
format：JSON形式で各種オプションを指定、JSONサーチ・インデックスを作成する場合は、'json_index'にtrueをセットする
formatのオプションの詳細は、こちらのマニュアルをご参照ください。
ここでは、バケットs3-bucket-course内のJSONファイルに対して、S3_COURSE_DATA_INDEXという名前のJSONサーチ・インデックスを作成しています。
また、S3バケットにアクセスする際に使用するクレデンシャルとして、AWS_S3_CREDを指定しています。
SQL> BEGIN
2 DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
3 credential_name => 'AWS_S3_CRED',
4 location_uri => 'https://s3bucket-course.s3.ap-northeast-1.amazonaws.com',
5 index_name => 'S3_COURSE_DATA_INDEX',
6 format => JSON_OBJECT ('json_index' value 'true')
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
JSONサーチ・インデックスの作成はスケジューラ・ジョブとして実行されます。
JSONサーチ・インデックスの作成には、バケット内のJSONファイルの大きさ、数に応じて時間がかかります。
all_scheduler_job_run_detailsビューで、スケジューラ・ジョブの状況を確認できます。
SQL> col job_name for a25
SQL> col run_duration for a20
SQL> col status for a10
SQL> SELECT job_name, run_duration, status
2 FROM all_scheduler_job_run_details
3 WHERE LOWER(job_name) = LOWER('S3_COURSE_DATA_INDEX$JOB');
JOB_NAME RUN_DURATION STATUS
------------------------- -------------------- ----------
S3_COURSE_DATA_INDEX$JOB +000 00:00:09 SUCCEEDED
SQL>
'インデックス名$TXTIDX'という名前のJSONサーチ・インデックス参照表が作成されます。
SQL> SELECT table_name FROM user_tables
2 WHERE table_name LIKE 'S3_COURSE_DATA_INDEX%'
3 ORDER BY table_name;
TABLE_NAME
-----------------------------------------------
S3_COURSE_DATA_INDEX$TXTIDX
S3_COURSE_DATA_INDEX$TXTIDX_ERR
S3_COURSE_DATA_INDEX$TXTIDX_LINE
S3_COURSE_DATA_INDEX$TXTIDX_STATUS
SQL>
また、インデックス名と同じ名前のJSONサーチ・インデックス参照ビューが作成されます。
SQL> SELECT view_name FROM user_views
2 WHERE view_name = 'S3_COURSE_DATA_INDEX';
VIEW_NAME
------------------------------------------------
S3_COURSE_DATA_INDEX
SQL>
JSONサーチ・インデックス参照ビューはこちらのような構造になっています。
SQL> desc s3_course_data_index
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(4000)
OBJECT_PATH VARCHAR2(4000)
FILE_LINE_JSON CLOB
MTIME TIMESTAMP(6)
DATA CLOB
SQL>
各列の詳細はこちらの表のとおりです。
| 列名 | 詳細 |
|---|---|
| OBJECT_NAME | 検索されたテキスト文字列を含む Object Storage 上のファイル名 |
| OBJECT_PATH | OBJECT_NAMEのオフジェクトを含むクラウド・ストレージのバケットまたはフォルダの URI |
| LENGTH | JSONドキュメントの長さ |
| OFFSET | ファイルの先頭のバイトオフセット |
| DATA | クラウド・ストレージ上のファイルの内容 |
| FILE_LINE_JSON | JSON_TEXTCONTAINSの実行対象となるカラム |
| MTIME | クラウド・ストレージ上のファイルの最終更新タイムスタンプ |
SQL条件JSON_TEXTCONTAINSを使用してJSONサーチ・インデックスと同名の参照ビューにクエリを実行すると、data列にデータが取り込まれます。
JSONサーチ・インデックスの利用
作成したJSONサーチ・インデックスを使用して、S3バケット内にあるJSONファイルに対して、クエリを実行してみます。
こちらのSQLでは、S3上のJSONファイルから講師ID（$.teacher.teacharId）が101のJSONドキュメントを抽出しています。
SQL> SELECT JSON_QUERY (data, '$' returning CLOB pretty) AS course_data
2 FROM s3_course_data_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.teacher.teacherId', '101');
COURSE_DATA
--------------------------------------------------------------------------------
{
"courseId" : "MATH101",
"name" : "Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 5,
"name" : "Hye E."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
},
"Notes" : "Prerequisite for Advanced Algebra"
}
{
"courseId" : "MATH102",
"name" : "Calculus",
"creditHours" : 4,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 10,
"name" : "Ming L."
},
{
"studentId" : 9,
"name" : "Luis F."
},
{
"studentId" : 4,
"name" : "Georgia D."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
}
}
SQL>
こちらのSQLでは、名前（$.students.name）にDonaldが含まれる学生が受講しているコースのコース名、その情報を含むJSONドキュメントが格納されているファイル名（オブジェクト名）およびそのパスを抽出しています。
SQL> set linesize 150
SQL> col course_name for a20
SQL> col object_name for a20
SQL> col object_path for a80
SQL> SQL> SELECT JSON_VALUE (data, '$.name' returning VARCHAR2) AS course_name, object_name, object_path
2 FROM s3_course_data_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.students.name', 'Donald');
COURSE_NAME OBJECT_NAME OBJECT_PATH
-------------------- -------------------- --------------------------------------------------------------------------------
Algebra course_data.json https://s3bucket-course.s3.ap-northeast-1.amazonaws.com/
Algorithms course_data.json https://s3bucket-course.s3.ap-northeast-1.amazonaws.com/
Data Structures course_data.json https://s3bucket-course.s3.ap-northeast-1.amazonaws.com/
SQL>
まとめ
Amazon S3上にあるJSONファイルに対してJSONサーチ・インデックスを作成して、JSONデータに対するテキスト検索が簡単に行えることが確認できました。
参考情報
・Use JSON Search on Documents in Object Storage
・Amazonリソース名(ARN)を使用したAWSリソースへのアクセス
・DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXプロシージャ
・[OCI]Autonomous DatabaseからAmazon S3にARNを用いたロールベースの認証でアクセスしてデータ連携してみた
・JSON検索索引: 非定型の問合せおよび全文検索