LoginSignup
0
1

More than 1 year has passed since last update.

@500InternalServerErrorin日本オラクル株式会社

[OCI]Autonomous Databaseに一定間隔で外部APIに自動的にアクセスさせて、取得したJSONデータをテーブルに保存してみた

Last updated at Posted at 2022-02-12

はじめに

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

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1