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

はじめに

この記事では、USの祝日の情報が格納されているJSONデータ(https://date.nager.at/api/v3/PublicHolidays/2024/US) をAutonomous Database内に格納する手順をご紹介します。

下記のようにデータが格納されています。

      [
    {
      "date": "2024-01-01",
      "localName": "New Year's Day",
      "name": "New Year's Day",
      "countryCode": "US",
      "fixed": false,
      "global": true,
      "counties": null,
      "launchYear": null,
      "types": [
        "Public"
      ]
    },
    {
      "date": "2024-01-15",
      "localName": "Martin Luther King, Jr. Day",
      "name": "Martin Luther King, Jr. Day",
      "countryCode": "US",
      "fixed": false,
      "global": true,
      "counties": null,
      "launchYear": null,
      "types": [
        "Public"
      ]
    },
    {
      "date": "2024-02-12",
      "localName": "Lincoln's Birthday",
      "name": "Lincoln's Birthday",
      "countryCode": "US",....

オープンデータをDBに格納する

オープンデータに対するアクセスを許可

• Autonomous DatabaseにADMINユーザーとして接続します。
• 接続をしたら、オープンデータに対するアクセスを許可します。

  BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
          host => 'date.nager.at',
          ace  => xs$ace_type(
              privilege_list => xs$name_list('http'),
              principal_name => 'ADMIN',  
              principal_type => xs_acl.ptype_db
          )
      );
  END;
  /
  

表の作成

• JSONデータを格納する表を作成します。

  CREATE TABLE us_holidays (
      id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      json_data CLOB CHECK (json_data IS JSON)  
  );
  

オープンデータからJSONデータを取得をして表に格納

• JSONデータを取得して、表に格納します。

  SET SERVEROUTPUT ON
  DECLARE
      v_req     UTL_HTTP.req;
      v_resp    UTL_HTTP.resp;
      v_clob    CLOB;
      v_buffer  VARCHAR2(32767);
  BEGIN
      v_req := UTL_HTTP.begin_request('https://date.nager.at/api/v3/PublicHolidays/2024/US', 'GET');
      v_resp := UTL_HTTP.get_response(v_req);
  
      -- CLOB を初期化
      v_clob := '';
  
      -- レスポンスを読み取る
      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);
  
      -- 取得したデータを表示
      DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_clob, 4000, 1));
  END;
  /
  

• これでオープンデータを取得し、DBに格納することができました。

表形式で見やすく表示

JSON_TABLE関数を使って、一部のデータを表形式で見てみます。

  SET LINESIZE 200
  SET PAGESIZE 50
  SET WRAP OFF
  SET TRIMSPOOL ON

  COLUMN Holiday_Date        FORMAT A12   HEADING 'Date'
  COLUMN Local_Name          FORMAT A25   HEADING 'Local Name'
  COLUMN English_Name        FORMAT A25   HEADING 'English Name'
  COLUMN Country_Code        FORMAT A5    HEADING 'Code'
  COLUMN Is_Fixed            FORMAT A5    HEADING 'Fixed'
  COLUMN Is_Global           FORMAT A5    HEADING 'Global'
  
  -- データ表示
  SELECT 
    jt."date"          AS Holiday_Date,
    jt."localName"     AS Local_Name,
    jt."name"          AS English_Name,
    jt."countryCode"   AS Country_Code,
    jt."fixed"         AS Is_Fixed,
    jt."global"        AS Is_Global
  FROM us_holidays u,
    JSON_TABLE(
      u.json_data FORMAT JSON, '$[*]'
      COLUMNS (
        "date"        VARCHAR2(20)  PATH '$.date',
        "localName"   VARCHAR2(100) PATH '$.localName',
        "name"        VARCHAR2(100) PATH '$.name',
        "countryCode" VARCHAR2(10)  PATH '$.countryCode',
        "fixed"       VARCHAR2(10)  PATH '$.fixed',
        "global"      VARCHAR2(10)  PATH '$.global'
      )
    ) jt
  ORDER BY Holiday_Date;

*　以下のように出力されます。

```
    Date         Local Name                English Name          Code  Fixed Globa
------------ ------------------------- ------------------------- ----- ----- -----
2024-01-01   New Year's Day            New Year's Day            US    false true
2024-01-15   Martin Luther King, Jr. D Martin Luther King, Jr. D US    false true
2024-02-12   Lincoln's Birthday        Lincoln's Birthday        US    false false
2024-02-19   Washington's Birthday     Presidents Day            US    false true
2024-03-29   Good Friday               Good Friday               US    false false
2024-03-29   Good Friday               Good Friday               US    false false
2024-05-08   Truman Day                Truman Day                US    false false
2024-05-27   Memorial Day              Memorial Day              US    false true
2024-06-19   Juneteenth National Indep Juneteenth National Indep US    false true
2024-07-04   Independence Day          Independence Day          US    false true
2024-09-02   Labour Day                Labor Day                 US    false true
2024-10-14   Columbus Day              Columbus Day              US    false false
2024-10-14   Indigenous Peoples' Day   Indigenous Peoples' Day   US    false false
2024-11-11   Veterans Day              Veterans Day              US    false true
2024-11-28   Thanksgiving Day          Thanksgiving Day          US    false true
2024-12-25   Christmas Day             Christmas Day             US    false true

16 rows selected.

SQL>

```