Autonomous Databaseだけで、Autonomous Databaseに天気予報のデータをロードしてみた

気象庁HPから入手できる天気予報のデータ（JSON形式）を例に、外部データをAutonomous Database にロードしてみたいと思います。

メリット

通常はローカルPCにデータを一旦ダウンロードし、加工し、データベースにロードする必要があるが、Autonomous Databaseはデータベース内からAPIを実行することができるので、そういった一連の作業を簡素化できます。
また、単一のDBの中でJSONデータや地理空間データなど様々なフォーマットのデータを扱うことができるので、分析開始までのリードタイムを短縮でき、分析の幅も広がります。
（例えば、RDB形式で保存されている東京都内にある店舗の売上データとJSON形式で保存されている天気予報の履歴データをJOINして天気予報に含まれるキーワードと店舗売上げとの相関を見たり。）

尚、今回サンプルとして気象庁のデータを例に記載しますが、このAPIについては気象庁の公式のものではなく、仕様の継続性や運用状況のお知らせを約束しているものではないとのことですので、その点はご留意ください。参考サイト

作業の流れ

1. データベース・ユーザーを作成する
2. クレデンシャルを登録する
3. コレクションを作成する
4. 天気予報データをコレクションに格納する
5. 格納したデータを確認する
6. (応用)DBMS_SCHEDULERを利用した定期登録
7. 参考資料

1. データベース・ユーザーを作成する

CREATE USER dbuser identified by XXXXXX;
GRANT soda_app TO dbuser;
GRANT dwrole TO dbuser; -- 手順簡素化のためにDWROLEを付与。本来は要件に応じて最適なロールを設定してください。
ALTER USER dbuser QUOTA 100G ON DATA;

ここから先の手順は特に指定のない限り、dbuserで実施します。

2. クレデンシャルを登録する

今回は認証が不要のサイトからGETするため認証情報は不要ですが、DBMS_CLOUDパッケージはクレデンシャルの利用が前提となりますので、ダミーのAutoTokenを登録しておきます。

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'APPUSER_CRED',  -- クレデンシャルの名前(任意の文字列)
    username => 'appuser', -- OCIユーザー名
    password => 'XXXXXXXXXXXXXXXXXX'    -- ダミーのAuth Token
  );
END;
/

3. JSONコレクションの作成

DB内にJSONドキュメントを格納するコレクション（RDMBSでいうところの表）を作成します。

DECLARE
    collection  SODA_Collection_T;
BEGIN
    collection := DBMS_SODA.create_collection('weather_data');
END;
/

4. 天気予報データをコレクションに格納する

気象庁がJSON形式で公開している天気予報データをAPI経由で取得し、DBに格納します。

まずはAutonomous Database からAPIを利用して気象庁のサイトにアクセスできるか確認します。無事アクセスできれば天気予報のデータが出力されます。

set serveroutput on

DECLARE
    resp dbms_cloud_types.resp;
BEGIN
  
    resp := dbms_cloud.send_request(
        credential_name => 'APPUSER_CRED',
        uri => 'https://www.jma.go.jp/bosai/forecast/data/overview_forecast/130000.json', --東京都のデータ。その他都道府県については本ページの最後に記載したリンクを参照のこと
        method => dbms_cloud.method_get
    );
  
    dbms_output.put_line(
      dbms_cloud.get_response_text(resp)
    );

END ;
/

補足）上記クライアントツールはAutonomous Databaseに付属するDatabase Actionsです。このGUIツールを利用すると簡単にSQLを実行できます。ちなみにGUIベースでJSONデータをロード、参照することも可能です。

次に作成済みのJSONコレクション（weather_data）に、JSONデータを格納します。RDBMSで言えば表に相当するコレクションに対して、行に相当するJSONデータを1行挿入します。

DECLARE
    collection  SODA_COLLECTION_T;
    resp        DBMS_CLOUD_TYPES.RESP;
    document    SODA_DOCUMENT_T;
    status      NUMBER;

BEGIN
    -- get the open data about forecast of weather
    resp := dbms_cloud.send_request(
        credential_name => 'APPUSER_CRED',
        uri => 'https://www.jma.go.jp/bosai/forecast/data/overview_forecast/130000.json',
        method => dbms_cloud.method_get
    );

    -- Open the collection, insert
    collection := DBMS_SODA.open_collection('weather_data');
    document := SODA_DOCUMENT_T(
        b_content => DBMS_CLOUD.GET_RESPONSE_RAW(resp));
    status := collection.insert_one(document);

END;
/

5. 格納したデータをSQLで確認する

まずはJSON形式で出力してみます。

SELECT
    JSON_SERIALIZE(JSON_DOCUMENT)
FROM
    WEATHER_DATA;

次にRDB形式で出力してみます。（JSONデータのそれぞれのキーが、以下のそれぞれの列に相当します。）
（jsonフォーマットのキーと列名は大文字・小文字を合わせる必要があるので注意）

SELECT
    D.JSON_DOCUMENT.publishingOffice,
    D.JSON_DOCUMENT.reportDatetime,
    D.JSON_DOCUMENT.targetArea,
    D.JSON_DOCUMENT.text
FROM
    WEATHER_DATA D;

Autonomous Databaseは、単一のDBの中でJSONデータや地理空間データなど様々なフォーマットのデータを扱うことができるため、例えば、Autonomous DatabaseにRDB形式で保存されている東京都内にある店舗の売上データと、JSON形式で保存されている天気予報の履歴データをJOINして、天気予報に含まれるキーワードと店舗売上げとの相関を分析する、といったことが可能です。

6. (応用) DBMS_SCHEDULERを利用した定期取得

天気予報のデータは日々更新されますが、それらのデータの取得を定期的に自動的に行うにはどうしたら良いでしょうか？
Computeインスタンスを作成しつつ上記スクリプトをcrontabに登録して定期実行させることももちろん可能ですが、Autonomous Databaseはスケジューラー機能を搭載しているので、Autonomous Databaseだけで自動化させることが可能です。

まずは必要に応じてADMINユーザーで権限を付与してください。

GRANT CREATE JOB TO dbuser;
GRANT MANAGE SCHEDULER TO dbuser;

次にプロシージャを作成します。

CREATE OR REPLACE PROCEDURE
  proc_get_weather_info_tokyo
IS
    collection  SODA_COLLECTION_T;
    resp        DBMS_CLOUD_TYPES.RESP;
    document    SODA_DOCUMENT_T;
    status      NUMBER;
BEGIN
    -- get the open data about forecast of weather
    resp := dbms_cloud.send_request(
        credential_name => 'APPUSER_CRED',
        uri => 'https://www.jma.go.jp/bosai/forecast/data/overview_forecast/130000.json' ,
        method => dbms_cloud.method_get
    );

    -- Open the collection, insert
    collection := DBMS_SODA.open_collection('weather_data');
    document := SODA_DOCUMENT_T(
        b_content => DBMS_CLOUD.GET_RESPONSE_RAW(resp));
    status := collection.insert_one(document);

    COMMIT;

END
;
/

--動作確認用
exec proc_get_weather_info_tokyo

次に、作成したプロシージャ（JOB_GET_WEATHER_INFO_TOKYO）をスケジューラに登録します。

BEGIN
DBMS_SCHEDULER.DROP_JOB('JOB_GET_WEATHER_INFO_TOKYO');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
  job_name           =>  'JOB_GET_WEATHER_INFO_TOKYO',
  job_type           =>  'STORED_PROCEDURE',
  job_action         =>  'DBUSER1.PROC_GET_WEATHER_INFO_TOKYO',
  start_date         =>  TO_DATE('2021/06/01 03:00:00','yyyy/mm/dd hh24:mi:ss'),
  repeat_interval    =>  'FREQ=DAILY',
  enabled            =>  TRUE
);
END;
/

-- 設定確認用
SELECT
    JOB_NAME,
    ENABLED,
    STATE,
    RUN_COUNT,
    MAX_RUNS,
    FAILURE_COUNT,
    LAST_RUN_DURATION,
    NEXT_RUN_DATE
FROM
    USER_SCHEDULER_JOBS;

参考情報

気象庁の天気予報APIについて

https://www.jma.go.jp/jma/index.html
https://mindtech.jp/?p=1754
https://forest.watch.impress.co.jp/docs/serial/yajiuma/1309318.html

その他、天気予報のAPIについて

https://api.rakuten.net/marketplace
https://weather.tsukumijima.net

ADB、およびSODA関連

https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/document-database-json.html
https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/plsql/19/adsdp/using-soda-pl-sql.html