日本語のオープンデータをAutonomous Databaseに取り込んでみる

はじめに

この記事では、東京都が公開している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;
  

• 下記のようなイメージで結果が出力されます。