はじめに
この記事では、東京都が公開しているJSONデータ(https://api.data.metro.tokyo.lg.jp/v1/SportsFacility) をAutonomous Database内に格納する手順をご紹介します。
このデータには東京都のスポーツ施設一覧が格納されています。
オープンデータをDBに格納する
オープンデータに対するアクセスを許可
- Autonomous DatabaseにADMINユーザーとして接続します。
- 今回は日本語のデータを扱うので、接続前にNLS_LANGの変更を行います。
export NLS_LANG=Japanese_Japan.AL32UTF8
- 接続をしたら、オープンデータに対するアクセスを許可します。
BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => 'api.data.metro.tokyo.lg.jp', ace => xs$ace_type( privilege_list => xs$name_list('http'), principal_name => 'ADMIN', principal_type => xs_acl.ptype_db ) ); END; /
表の作成
- JSONデータを格納する表を作成します。
CREATE TABLE sports_facilities ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, json_data CLOB CHECK (json_data IS JSON) -- JSON データを格納 );
オープンデータからJSONデータを取得をして表に格納
- JSONデータを取得して、表に格納します。
SET SERVEROUTPUT ON DECLARE v_req UTL_HTTP.req; v_resp UTL_HTTP.resp; v_buffer VARCHAR2(32767); v_clob CLOB := ''; BEGIN -- リクエスト作成 v_req := UTL_HTTP.begin_request('https://api.data.metro.tokyo.lg.jp/v1/SportsFacility', 'GET'); -- charsetをUTF-8として明示 UTL_HTTP.set_body_charset(v_req, 'UTF-8'); -- ヘッダー指定 UTL_HTTP.set_header(v_req, 'User-Agent', 'Mozilla/5.0'); -- レスポンス取得 v_resp := UTL_HTTP.get_response(v_req); -- UTF-8として読み込み BEGIN LOOP UTL_HTTP.read_text(v_resp, v_buffer, 32767); v_clob := v_clob || v_buffer; END LOOP; EXCEPTION WHEN UTL_HTTP.end_of_body THEN NULL; END; -- レスポンス終了 UTL_HTTP.end_response(v_resp); -- DBへ保存 INSERT INTO sports_facilities (json_data) VALUES (v_clob); COMMIT; -- デバッグ表示 DBMS_OUTPUT.PUT_LINE(SUBSTR(v_clob, 1, 4000)); END; /
- 本データは日本語で格納されているので、データを取得する段階でcharsetをUTF-8として明示すると、文字化けを防ぐことができます。
- これでオープンデータを取得し、DBに格納することができました。
表形式で分かりやすく表示
-
JSON形式で格納されているので、JSON_TABLE関数を使って表形式で表示してみます。
-
このJSONデータはのトップレベル構造が二重配列になっています。このような二重配列構造の場合、JSON_TABLE関数を使用する際には '$[0][*]' と指定して二重配列の中の各施設データを1件ずつ展開する必要があります。
SELECT jt.施設名, jt.住所, jt.電話番号, jt.緯度, jt.経度, jt.管理者名, jt.アクセス備考, jt.ウェブサイト FROM sports_facilities, JSON_TABLE( json_data FORMAT JSON, '$[0][*]' COLUMNS ( 施設名 VARCHAR2(100) PATH '$."名称"."表記"', 住所 VARCHAR2(200) PATH '$."住所"."表記"', 電話番号 VARCHAR2(30) PATH '$."連絡先"."電話番号"', 緯度 VARCHAR2(30) PATH '$."地理座標"."緯度"', 経度 VARCHAR2(30) PATH '$."地理座標"."経度"', 管理者名 VARCHAR2(100) PATH '$."管理者"."表記"', アクセス備考 VARCHAR2(200) PATH '$."アクセス"."備考"', ウェブサイト VARCHAR2(200) PATH '$."連絡先"."Webサイト"' ) ) jt;