Autonomous JSON Database を使ってみる

Last updated at 2023-04-04Posted at 2023-04-04

はじめに

Webサービスから JSON データを取得し、データベースに挿入してみます。

本記事の前提条件

AJDインスタンスが構成済みであること
Geonameにアカウント登録をしてあること

作業の大まかな流れ

LiveLabs, Full Python App Development with Autonomous JSON for MongoDB Developers, Labs1, Task3を参考にAJDインスタンスを作成する
Geonames に登録する
AJDに接続
JSON データの生成
JSONドキュメントを保存する
サンプルデータの取得

AJDを使ってみる

まずはGeonameに登録

Geonameに移動し、メールアドレスとパスワードを入力する。
メールに送られてきたURLをクリックし、アカウント作成を完了させる。
アカウントページで、Web サービスのアカウントを有効にします。(click here to enableを押す。)

AJDに接続

ADMINユーザーでDatabase Actionsに接続し、開発>SQLを開く。

JSONデータの生成

データベースが Web サービスに接続し、HTTPS 経由で情報を取得する必要があるため、ネットワークアクセスコントロールリストを作成します。これには、アクセスコントロールリスト(ACL)が必要です。このACLは、SYSDBA権限を持つユーザー(今回はADMIN)で次の手順を実行します。
```
    begin
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'secure.geonames.org',
    ace        => xs$ace_type(privilege_list => xs$name_list('http','connect','resolve'),
                              principal_name => 'ADMIN',
                              principal_type => xs_acl.ptype_db));
end;
/
```
Geonameへのアクセスをテストします。今回は、GeoNamesのcountryInfo Web サービスです。次のスクリプトをSQLワークシートで実行します。Autonomous DatabaseのURL_HTTPパッケージは、セキュリティを強化するためにSSLウォレットを使用するHTTPSリクエストのみをサポートしています。

注: [YourGeoNameUsername]を GeoNames のアカウントのユーザー名に書き換えてください。

```
    set serveroutput on

declare
    t_http_req  utl_http.req;
    t_http_resp  utl_http.resp;
    t_response_text clob;
begin  
     UTL_HTTP.SET_WALLET('');

t_http_req:= utl_http.begin_request('https://secure.geonames.org/countryInfoJSON?formatted=true&' || 'lang=en&' || 'country=ES&' || 'username=YourGeoNameUsername&' || 'style=full','GET','HTTP/1.1');
UTL_HTTP.SET_HEADER(t_http_req, 'User-Agent', 'Mozilla/4.0');
t_http_resp:= utl_http.get_response(t_http_req);
UTL_HTTP.read_text(t_http_resp, t_response_text);
UTL_HTTP.end_response(t_http_resp);
DBMS_OUTPUT.put_line(t_response_text);
end;
/
```

JSON ドキュメントをOracle データベースに格納する

PDB内にすべての JSON ドキュメントを格納する新しいテーブルを作成します。

CREATE TABLE MYJSON (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 5) PRIMARY KEY,
  doc CLOB CONSTRAINT valid_json CHECK (doc IS JSON));

Oracle データベース内でのJSONの使用には、定義済みのデータ構造や特定のスキーマは必要ありません。作成したばかりのようなリレーショナルテーブルに任意の JSON ドキュメントを任意の内部ドキュメント構造で格納できます。以下のコマンドは、GeoNamesから受け取ったものとはまったく異なる構造を持つ別の JSONドキュメントの例であり、同じテーブルに格納できます。
```
INSERT INTO MYJSON (doc) VALUES (
'{
  "workshopName": "Database 19c New Features for Developers",
  "audienceType": "Partners Technical Staff",
  "location": {
    "company": "Oracle",
    "office": "Customer Visiting Center",
    "region": "EMEA"
  }
}');

commit;
```
Commitしたら、このドキュメントに対してクエリして、JSON値を従来のリレーショナルデータとして取得できます。
```
SELECT j.doc FROM MYJSON j;
```

サンプルデータを取得する

以下のスクリプトを実行して、GeoNames Web サービスから国情報を取得する関数を作成します。
注: [YourGeoNameUsername]を GeoNames のアカウントのユーザー名に書き換えてください。

create or replace function get_country_info (countryCode in VARCHAR2) return clob
  is
    t_http_req  utl_http.req;
    t_http_resp  utl_http.resp;
    t_response_text clob;
  begin   
    UTL_HTTP.SET_WALLET('');

    t_http_req:= utl_http.begin_request('https://secure.geonames.org/countryInfoJSON?formatted=true&' || 'lang=en&' || 'country=ES&' || 'username=YourGeoUsername&' || 'style=full','GET','HTTP/1.1');
    UTL_HTTP.SET_HEADER(t_http_req, 'User-Agent', 'Mozilla/4.0');
    t_http_resp:= utl_http.get_response(t_http_req);
    UTL_HTTP.read_text(t_http_resp, t_response_text);
    UTL_HTTP.end_response(t_http_resp);
    return t_response_text;
  end;
/

作成したばかりの関数の入力は、国の ISO コードです。たとえば、スペインに関する情報を取得するには、次のクエリを実行します。テーブルの行にカーソルを合わせると、JSONが全て表示されます。
```
select get_country_info('ES') country_info from dual;
```
Web サービスから取得したJSONドキュメントを同じテーブルのJSON列に挿入してコミットします。
```
insert into MYJSON (doc) values (get_country_info('ES'));

commit;
```
テーブルの内容を選択し、中身を確認します。
```
select * from MYJSON;
```