Autonomous JSON Database を使ってみる

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


# 本記事の前提条件 

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

# 作業の大まかな流れ
* [LiveLabs, Full Python App Development with Autonomous JSON for MongoDB Developers, Labs1, Task3](https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/run-workshop?p210_wid=3272&p210_wec=&session=112889452697234)を参考にAJDインスタンスを作成する
* [Geonames](http://www.geonames.org/) に登録する
* AJDに接続
* JSON データの生成
* JSONドキュメントを保存する
* サンプル データの取得

# AJDを使ってみる

### まずはGeonameに登録
1. [Geoname](http://www.geonames.org/)に移動し、メールアドレスとパスワードを入力する。
    ![Autonomous JSON Databaseを使ってみる-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/4410cdf6-8824-280a-43ac-e695c093976c.png)

1. メールに送られてきたURLをクリックし、アカウント作成を完了させる。
1. アカウント ページで、Web サービスのアカウントを有効にします。(click here to enableを押す。)
    ![Autonomous JSON Databaseを使ってみる-3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/30fca83c-7fe0-f863-20ae-fd55a3d9a3d2.png)

### AJDに接続

1. ADMINユーザーでDatabase Actionsに接続し、開発>SQLを開く。
    ![Autonomous JSON Databaseを使ってみる-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/76cc60a8-a8ee-c7a5-76b9-6a822cca4445.png)

    ![Autonomous JSON Databaseを使ってみる-1.5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/c2af3f28-7a9f-3f70-2263-fc579bade554.png)

### JSONデータの生成

1. データベースが 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;
    /
    ```
    ![Autonomous JSON Databaseを使ってみる-4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/1354feb6-ef9a-8d85-cc7c-d80f646a4ed8.png)

1. 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;
    /
    ```


   ![Autonomous JSON Databaseを使ってみる-5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/78d56132-dd3c-0a51-3196-16538d26af54.png)


### JSON ドキュメントをOracle データベースに格納する
1. 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));
    ```
    ![Autonomous JSON Databaseを使ってみる-6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/6f9fab37-3b86-b024-a273-e10cdcca627a.png)

1. 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;
    ```
    ![Autonomous JSON Databaseを使ってみる-7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/00f564d2-4c58-c006-3c01-dc4e276b36e4.png)

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

    ![Autonomous JSON Databaseを使ってみる-8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/75231a24-bf03-9b82-ed09-bd398573281d.png)


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

1. 以下のスクリプトを実行して、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;
    /
    ```
    ![Autonomous JSON Databaseを使ってみる-9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/11d729c3-e7f6-fbc0-4b51-bf864b9b3808.png)

1. 作成したばかりの関数の入力は、国の ISO コードです。たとえば、スペインに関する情報を取得するには、次のクエリを実行します。テーブルの行にカーソルを合わせると、JSONが全て表示されます。

    ```
    select get_country_info('ES') country_info from dual;
    ```

    ![Autonomous JSON Databaseを使ってみる-10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/d1ec6d11-49d0-daa2-bb8c-65391c19d147.png)

1. Web サービスから取得したJSONドキュメントを同じテーブルのJSON列に挿入してコミットします。
    ```
    insert into MYJSON (doc) values (get_country_info('ES'));

    commit;
    ```
    ![Autonomous JSON Databaseを使ってみる-11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/e9489c93-5aa7-1291-c542-e1ffed8130f0.png)

1. テーブルの内容を選択し、中身を確認します。
    ```
    select * from MYJSON;
    ```
    ![Autonomous JSON Databaseを使ってみる-12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/00794174-8c57-4d15-d645-16982422c6ed.png)


    


# まとめ
本記事では外部WebサービスからJSONデータをAJDに格納し、テーブルを作成する流れを行いました。

<br>

# 参考資料
* [Autonomous JSON Database, SODA and JSON - Tutorials, JSON in Autonomous Database](https://oracle.github.io/learning-library/developer-library/oracle-db-features-for-developers/workshops/adb-json-freetier/?lab=json-adb)