Autonomous Databaseのための高度なJSONデータ・ローディング (2021/09/30)

9月 29, 2021

Autonomous Databaseのための高度なJSONデータ・ローディング (2021/09/30)

https://medium.com/db-one/advanced-json-data-loading-for-autonomous-database-7096e2ed1560

JSONデータの読み込みは、Autonomous Databaseではいくつかの方法で実現できます。Oracle REST Data Service (ORDS)を使用するなど、他の方法についてはJeff Smith氏のブログを参照してください。

今日は、Autonomous Database上のSODAコレクションにJSONデータを素早くロードするのに非常に便利な別のシンプルな方法を紹介します。

これは、ブログ記事「How to access Open Data with Oracle Autonomous Database」のフォローアップです。

デモのために、オンラインでホストされていて一般にアクセス可能なJSONデータをロードする必要があります。ここで紹介した方法は、認証をサポートするように改善できるかもしれませんが、これは本当に重要です。

目的：JSONドキュメントをダウンロードし、SODAコレクションにロード

このために、ファイルを直接ダウンロードできるAPEX_WEB_SERVICE PL/SQLパッケージを活用します。

次のステップは、以下のPL/SQLタイプと魔法をかけるためのFunctionをインストールすることです。

CREATE OR REPLACE TYPE t_opendata_json_doc AS OBJECT ( json_document CLOB );CREATE OR REPLACE TYPE t_opendata_json_doc_collection IS TABLE OF t_opendata_json_doc;CREATE OR REPLACE FUNCTION getJSON( p_my_url IN varchar2, 
                                    p_json_path IN varchar2 default '$' ) 
    RETURN t_opendata_json_doc_collection PIPELINED AS  TYPE OpenDataCursorType IS REF CURSOR;
  l_clob   clob;
  cur OpenDataCursorType;  invalid_path_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_path_exception, -40561);
BEGIN
  l_clob := apex_web_service.make_rest_request(p_url => p_my_url, p_http_method => 'GET' );  begin
    -- uses the provided JSON Path to access JSON data
    open cur for 'select d.json_document from json_table(:l_clob, ''$'' columns (nested PATH ''' || p_json_path || ''' columns(json_document CLOB format json path ''$''))) d' using l_clob;    loop
      fetch cur into l_clob;
      exit when cur%NOTFOUND;
      PIPE ROW(t_opendata_json_doc(l_clob));
    end loop;
    
    close cur;EXCEPTION WHEN invalid_path_exception THEN
  begin
    -- try if this is an array of JSON documents to flatten
    open cur for 'select d.json_document from json_table(:l_clob, ''$'' columns (nested path ''$[*]'' columns (json_document CLOB format json path ''$''))) d' using l_clob;
    
    loop
      fetch cur into l_clob;
      exit when cur%NOTFOUND;
      PIPE ROW(t_opendata_json_doc(l_clob));
    end loop;
    
    close cur;
  
  exception WHEN invalid_path_exception THEN
    -- return the JSON document as is
    open cur for 'select d.json_document from json_table(:l_clob, ''$'' columns json_document CLOB format json path ''$'') d' using l_clob;
    
    loop
      fetch cur into l_clob;
      exit when cur%NOTFOUND;
      PIPE ROW(t_opendata_json_doc(l_clob));
    end loop;    close cur;
  end;
END;RETURN;
END;
/

Table関数の問い合わせ

この関数を呼び出すには、次のような方法があります...

select *
  from getJSON( 'https://www.ncdc.noaa.gov/cag/global/time-series/globe/land_ocean/1/8/1880-2021/data.json' );

ファイルがダウンロードされます。

{
  "description": { 
    "title": "Global Land and Ocean Temperature Anomalies, August",
    "units": "Degrees Celsius",
    "base_period": "1901-2000",
    "missing":-999
  },
  "data": { 
  "1880": "-0.07",
  "1881": "-0.06",
...
  }
}

と表示され、そのままの状態で返却されます。

1つのファイル内の1つのJSONドキュメント

select *

from getJSON( 'https://www.ncdc.noaa.gov/cag/global/time-series/globe/land_ocean/1/8/1880-2021/data.json' );

ファイルがダウンロードされます。

{
  "Name": "Test",
  "Mobile": 12345678,
  "Boolean": true,
  "Pets": ["Dog", "cat"],
  "Address": {
    "Permanent address": "USA",
    "current Address": "AU"
  }
}

と表示され、そのままの状態で返却されます。

ファイル内の1つのJSONドキュメント、JSONパスを使用したフィールドへのアクセス

select *

from getJSON( 'https://tools.learningcontainer.com/sample-json-file.json', '$.Pets' );

ファイルがダウンロードされます。

[
   {
      "NAME":"piscina_tor_di_quinto",
      "UNIVERSITY_ID":1,
      "TYPE":"Centro_sportivo",
      "COSTO":{
         "MIN":"2.20",
         "MAX":"7.20"
      },
      "CITY":"Roma",
      "ADDRESS":"Via Fornaci di Tor di Quinto, 64",
      "GEOMETRY":{
         "type":"Point",
         "coordinates":[
            12.4842835,
            41.9365692
         ]
      },
      "OPEN":"start from January 9th.",
      "SIZE":"16m x 33m",
      "PHOTO":{
         "TYPE1":"url",
         "PICTURES1":"https://web.uniroma1.it/sapienzasport/sites/default/files/IMG_20200622_164135.jpg"
      }
   },
   {
      "NAME":"mensa_lollis",
      "UNIVERSITY_ID":1,
      "TYPE":"Mensa",
...
   }
]

を作成し、関連するペットの配列フィールドを返します。

JSON配列内の複数のJSONドキュメント

select *

from getJSON( 'https://raw.githubusercontent.com/loiclefevre/jsonloader/main/src/main/resources/Uni_POI_upper.json' );

ファイルがダウンロードされます。

> soda create points_of_interest; 

Successfully created collection: points_of_interest

> desc points_of_interest;
Name          Null?    Type          
------------- -------- ------------- 
ID            NOT NULL VARCHAR2(255) 
CREATED_ON    NOT NULL TIMESTAMP(6)  
LAST_MODIFIED NOT NULL TIMESTAMP(6)  
VERSION       NOT NULL VARCHAR2(255) 
JSON_DOCUMENT          JSON

> INSERT INTO points_of_interest (id, version, json_document)
SELECT SYS_GUID(), SYS_GUID(), json_document
FROM getJSON( 'https://raw.githubusercontent.com/loiclefevre/jsonloader/main/src/main/resources/Uni_POI_upper.json' );

14 rows inserted.

Elapsed: 00:00:00.585

そして、配列の項目ごとに1つのJSONドキュメントを返します。

https://miro.medium.com/max/2000/1*Gzo6Ga_-Br4oUZ0JpI0r_A.png

SQL を使用して JSON ドキュメントを SODA コレクションに読み込む

次に、プレーンなSQLを使用して、JSONドキュメントをSODAコレクションに実際に挿入するステップが残っています。

これで完成です。

このブログを検索

Oracle Cloud Infrastructure Blog(翻訳)