はじめに

APサーバ等を使用せずに、Autonomous Databaseから直接外部APIを一定間隔でコールしてJSONデータを取得し、取得したJSONデータをテーブルに保存する方法を検証してみました。

今回は東京駅付近の気象データを5分毎に取得してみました。

今回はこちらのサイトで公開されている気象データを取得するAPIを使用しました。

以下のようにAPIを呼び出します。

api.openweathermap.org/data/2.5/weather?lat={lat}&lon={lon}&appid={API key}

こちらのAPIを緯度、経度を指定して呼び出すと、その時点での該当する場所の気象データを以下のようなJSON形式で返します。

{
  "coord": {
    "lon": -122.08,
    "lat": 37.39
  },
  "weather": [
    {
      "id": 800,
      "main": "Clear",
      "description": "clear sky",
      "icon": "01d"
    }
  ],
  "base": "stations",
  "main": {
    "temp": 282.55,
    "feels_like": 281.86,
    "temp_min": 280.37,
    "temp_max": 284.26,
    "pressure": 1023,
    "humidity": 100
  },
  "visibility": 16093,
  "wind": {
    "speed": 1.5,
    "deg": 350
  },
  "clouds": {
    "all": 1
  },
  "dt": 1560350645,
  "sys": {
    "type": 1,
    "id": 5122,
    "message": 0.0139,
    "country": "US",
    "sunrise": 1560343627,
    "sunset": 1560396563
  },
  "timezone": -25200,
  "id": 420006353,
  "name": "Mountain View",
  "cod": 200
  }

1.気象データを保存するテーブルの準備

データの取得日時とAPI経由で取得したJSONデータを保存するテーブル"weather_info"を作成します。

CREATE TABLE weather_info
(
time_loaded DATE,
weather_data VARCHAR2(1000)
CONSTRAINT ensure_json CHECK (weather_data IS JSON)
);

2.DBのネットワークACLの変更

DBのネットワークACL（アクセス制御リスト）に、APIのホストのACE（アクセス制御エントリ）を追加します。

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACEでホストapi.openweathermap.orgに対するconnect権限とresolve権限をADMINに付与します。

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.openweathermap.org',
    ace => xs$ace_type(
        privilege_list => xs$name_list('connect','resolve'),
        principal_name => 'ADMIN',
        principal_type => xs_acl.ptype_db));
END;
/

3.PL/SQLプロシージャの作成

UTL_HTTPプロシージャでAPIにアクセスしてJSON形式の気象データを取得し、現在日時と共にweather_infoテーブルに格納するプロシージャ"get_weather_info"を作成します。

CREATE OR REPLACE PROCEDURE get_weather_info
IS
	api_url VARCHAR2(1024); 
	req   UTL_HTTP.REQ;
	resp  UTL_HTTP.RESP;
	value VARCHAR2(1024);
BEGIN
/* APIリクエストのURLの設定 */
	api_url := 'https://api.openweathermap.org/data/2.5/weather?lat=35.6809591&lon=139.7673068&units=metric&appid=XXXXXXXXXXXXXXXXXXXX';

/* SYSDATE_AT_DBTIMEZONEの有効化 */
	EXECUTE IMMEDIATE 'ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE';

/* ウォレット のセット */
	UTL_HTTP.SET_WALLET('');

/* APIへのHTTPリクエストの開始 */
	req := UTL_HTTP.BEGIN_REQUEST(api_url);
/* リクエストヘッダーの設定 */
	UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

/* APIからのHTTPレスポンスの読み取り */
	resp := UTL_HTTP.GET_RESPONSE(req);
	UTL_HTTP.READ_LINE(resp, value, TRUE);

/* APIへのHTTPリクエストの完了 */
	UTL_HTTP.END_RESPONSE(resp);

/* 取得したJSON形式の気象データを現在日時と共にweather_infoテーブル保存 */
	INSERT INTO weather_info VALUES (sysdate,value);

	EXCEPTION
		WHEN UTL_HTTP.END_OF_BODY THEN
		UTL_HTTP.END_RESPONSE(resp);
END;
/

SQL> EXECUTE get_weather_info;

PL/SQLプロシージャが正常に完了しました。

SQL>

プロシージャの実行が正常に完了したので、weather_infoテーブルの中身を確認してみます。

SQL> SELECT * FROM weather_info;

TIME_LOADED
-------------------
WEATHER_DATA
--------------------------------------------------------------------------------
2022-02-12 17:37:42
{"coord":{"lon":139.77,"lat":35.68},"weather":[{"id":801,"main":"Clouds","descri
ption":"few clouds","icon":"02n"}],"base":"stations","main":{"temp":8.01,"feels_
like":7.13,"temp_min":6.01,"temp_max":7.93,"pressure":1024,"humidity":46},"visib
ility":10000,"wind":{"speed":1.03,"deg":70},"clouds":{"all":20},"dt":1644658644,
"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615103,"sunset":1644653
926},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

SQL>

APIから取得したJSON形式の気象データがweather_infoテーブルに格納されていることが確認できました。

APIから気象データを取得するプロシージャが作成できたので、このプロジージャを定期に実行するためにDBMS_SCHEDULERにジョブを作成します。
こちらのジョブ"GET_WEATHER_INFO_JOB"は、先ほど作成したプロシージャ"get_weather_info"を5分おきに実行します。

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'GET_WEATHER_INFO_JOB',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'ADMIN.GET_WEATHER_INFO',
   start_date         =>  TO_DATE('2022/02/12 17:45:00','yyyy/mm/dd hh24:mi:ss'),
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=5', /* every 5 minutes */
   auto_drop          =>  FALSE
   );
END;
/

しばらく待ってから、weather_infoテーブルの内容を確認してみます。

SQL> SELECT * FROM weather_info
  2  ORDER BY time_loaded;

TIME_LOADED
-------------------
WEATHER_DATA
--------------------------------------------------------------------------------
2022-02-12 17:45:05
{"coord":{"lon":139.774,"lat":35.681},"weather":[{"id":802,"main":"Clouds","desc
ription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.92
,"feels_like":7.92,"temp_min":6.13,"temp_max":9.62,"pressure":1023,"humidity":42
},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":164
4655490,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615102,"sunset"
:1644653925},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 17:50:00
{"coord":{"lon":139.774,"lat":35.681},"weather":[{"id":802,"main":"Clouds","desc
ription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.92
,"feels_like":7.92,"temp_min":6.13,"temp_max":9.62,"pressure":1023,"humidity":42
},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":164
4655490,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615102,"sunset"
:1644653925},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 17:55:00
{"coord":{"lon":139.7673,"lat":35.681},"weather":[{"id":802,"main":"Clouds","des
cription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.8
1,"feels_like":7.81,"temp_min":6.16,"temp_max":8.4,"pressure":1023,"humidity":43
},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":164
4656100,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615104,"sunset"
:1644653927},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:00:00
{"coord":{"lon":139.7673,"lat":35.681},"weather":[{"id":802,"main":"Clouds","des
cription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.8
1,"feels_like":7.81,"temp_min":6.16,"temp_max":8.4,"pressure":1023,"humidity":43
},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":164
4656100,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615104,"sunset"
:1644653927},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:05:00
{"coord":{"lon":139.7673,"lat":35.681},"weather":[{"id":802,"main":"Clouds","des
cription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.8
1,"feels_like":7.81,"temp_min":6.16,"temp_max":8.4,"pressure":1023,"humidity":43
},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":164
4656100,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615104,"sunset"
:1644653927},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:10:00
{"coord":{"lon":139.7731,"lat":35.678},"weather":[{"id":802,"main":"Clouds","des
cription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.5
4,"feels_like":7.54,"temp_min":6.13,"temp_max":8.37,"pressure":1023,"humidity":4
4},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":16
44656954,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615102,"sunset
":1644653926},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:15:00
{"coord":{"lon":139.7731,"lat":35.678},"weather":[{"id":802,"main":"Clouds","des
cription":"scattered clouds","icon":"03n"}],"base":"stations","main":{"temp":7.5
4,"feels_like":7.54,"temp_min":6.13,"temp_max":8.37,"pressure":1023,"humidity":4
4},"visibility":10000,"wind":{"speed":1.03,"deg":50},"clouds":{"all":40},"dt":16
44656954,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615102,"sunset
":1644653926},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:20:00
{"coord":{"lon":139.7673,"lat":35.681},"weather":[{"id":801,"main":"Clouds","des
cription":"few clouds","icon":"02n"}],"base":"stations","main":{"temp":7.41,"fee
ls_like":7.41,"temp_min":5.16,"temp_max":8.4,"pressure":1023,"humidity":45},"vis
ibility":10000,"wind":{"speed":1.03,"deg":70},"clouds":{"all":20},"dt":164465760
0,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615104,"sunset":16446
53927},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:25:00
{"coord":{"lon":139.7673,"lat":35.681},"weather":[{"id":801,"main":"Clouds","des
cription":"few clouds","icon":"02n"}],"base":"stations","main":{"temp":7.41,"fee
ls_like":7.41,"temp_min":5.16,"temp_max":8.4,"pressure":1023,"humidity":45},"vis
ibility":10000,"wind":{"speed":1.03,"deg":70},"clouds":{"all":20},"dt":164465760
0,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615104,"sunset":16446
53927},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

2022-02-12 18:30:00
{"coord":{"lon":139.7673,"lat":35.681},"weather":[{"id":801,"main":"Clouds","des
cription":"few clouds","icon":"02n"}],"base":"stations","main":{"temp":7.41,"fee
ls_like":7.41,"temp_min":5.16,"temp_max":8.4,"pressure":1023,"humidity":45},"vis
ibility":10000,"wind":{"speed":1.03,"deg":70},"clouds":{"all":20},"dt":164465760
0,"sys":{"type":2,"id":268395,"country":"JP","sunrise":1644615104,"sunset":16446
53927},"timezone":32400,"id":1857654,"name":"Marunouchi","cod":200}

10行が選択されました。

SQL>

プロシージャget_weather_infoが自動実行され、5分おきに外部APIから気象データを取得してテーブルに保存できていることが確認できました。

JSON_VALUEファンクションを使用して、気温（temp）を出力してみます。

SQL> col temperature for a15
SQL> SELECT time_loaded,JSON_VALUE(weather_data, '$.main.temp') temperature
  2  FROM weather_info
  3  ORDER BY time_loaded;

TIME_LOADED	        TEMPERATURE
------------------- ---------------
2022-02-12 17:45:05 7.92
2022-02-12 17:50:00 7.92
2022-02-12 17:55:00 7.81
2022-02-12 18:00:00 7.81
2022-02-12 18:05:00 7.81
2022-02-12 18:10:00 7.54
2022-02-12 18:15:00 7.54
2022-02-12 18:20:00 7.41
2022-02-12 18:25:00 7.41
2022-02-12 18:30:00 7.41
2022-02-12 18:35:00 7.27

11行が選択されました。

SQL>

JSON形式の気象データから気温データを取り出すことができました。

参考情報

DBMS_NETWORK_ACL_ADMIN
UTL_HTTP
Restrictions and Notes for Database PL/SQL Packages
Oracle Schedulerを使用したジョブのスケジューリング
 SQL/JSONファンクションJSON_VALUE