Oracle Database: Spotify APIで取得したJSONデータを外部表でSELECTしてみてみた

#■目的
Oracle Databaseは、トランザクション、索引付け、宣言的問合せおよびビューを含むリレーショナル・データベースの機能を使用してJavaScript Object Notation (JSON)データをネイティブにサポートしています。
Oracle Databaseでは、SQLおよびリレーショナル・データベースのすべてのメリットがJSONデータに提供されるため、他の種類のデータベース・データと変わらない方法および確実さでデータの格納および操作を行うことが可能です。
これにより、複雑なデータ分析やレポートに対する柔軟なサポートと、強固なデータ保護およびアクセス制御が提供されます。

ということで、
前回のSpotify Developer Platform: Spotify APIアクセスしてデータ取得してみてみたより出力したJSONデータの必要要素だけを外部表でアクセスできるようにし、Inset as SelectでテーブルへINSERTできるようにSELECTしてみてみます。

#■JSONデータの用意
前回のSpotify Developer Platform: Spotify APIアクセスしてデータ取得してみてみたで「Spotify Japan 急上昇チャート」のJSONを取得し Oracle Databaseがアクセスできるディレクトリへ配置します

##① JSONデータ取得
取得したJSONデータをspotify.txtファイルへ出力

[opc@oci-inst01 ~]$ curl -H "Authorization: Bearer <Access token>" \
                    https://api.spotify.com/v1/playlists/37i9dQZF1DX9vYRBO9gjDe?fields=tracks.items \
                    > spotify.txt

[opc@oci-inst01 ~]$ cat spotify.txt
    {
        "tracks" : {
            "items" : [ {
            "added_at" : "2020-04-07T09:38:33Z",
            "added_by" : {
                "external_urls" : {
                "spotify" : "https://open.spotify.com/user/"
                },
                "href" : "https://api.spotify.com/v1/users/",
                "id" : "",
                "type" : "user",
                "uri" : "spotify:user:"
            },
            "is_local" : false,
            "primary_color" : null,
            "track" : {
                "album" : {
                "album_type" : "single",
                "artists" : [ {
                    "external_urls" : {
                    "spotify" : "https://open.spotify.com/artist/1XErHyc10VN8bfGKma1Yyc"
                    },
                    "href" : "https://api.spotify.com/v1/artists/1XErHyc10VN8bfGKma1Yyc",
                    "id" : "1XErHyc10VN8bfGKma1Yyc",
                    "name" : "Leo Ieiri",
                    "type" : "artist",
                    "uri" : "spotify:artist:1XErHyc10VN8bfGKma1Yyc"
                } ],
                "available_markets" : [ "AD", "AE", "AR", "AT", "AU", "BE", "BG", "BH", "BO", "BR", "CA", "CH", "CL", "CO", "CR", "CY", "CZ", "DE", "DK", "DO", "DZ", "EC", "EE", "EG", "ES", "FI", "FR", "GB", "GR", "GT", "HK", "HN", "HU", "ID", "IE", "IL", "IN", "IS", "IT", "JO", "JP", "KW", "LB", "LI", "LT", "LU", "LV", "MA", "MC", "MT", "MX", "MY", "NI", "NL", "NO", "NZ", "OM", "PA", "PE", "PH", "PL", "PS", "PT", "PY", "QA", "RO", "SA", "SE", "SG", "SK", "SV", "TH", "TN", "TR", "TW", "US", "UY", "VN", "ZA" ],
                "external_urls" : {
                    "spotify" : "https://open.spotify.com/album/3sYKjtp8sV39Fwvj2CYGcr"
                },
                "href" : "https://api.spotify.com/v1/albums/3sYKjtp8sV39Fwvj2CYGcr",
                "id" : "3sYKjtp8sV39Fwvj2CYGcr",
                "images" : [ {
                    "height" : 640,
                    "url" : "https://i.scdn.co/image/ab67616d0000b2735d0e62cfb92e0cc6d1d05b2c",
                    "width" : 640
                }, {
                    "height" : 300,
                    "url" : "https://i.scdn.co/image/ab67616d00001e025d0e62cfb92e0cc6d1d05b2c",
                    "width" : 300
                }, {
                    "height" : 64,
                    "url" : "https://i.scdn.co/image/ab67616d000048515d0e62cfb92e0cc6d1d05b2c",
                    "width" : 64
                } ],
                "name" : "未完成",
                "release_date" : "2020-01-29",
                "release_date_precision" : "day",
                "total_tracks" : 3,
                "type" : "album",
                "uri" : "spotify:album:3sYKjtp8sV39Fwvj2CYGcr"
                },
                "artists" : [ {
                "external_urls" : {
                    "spotify" : "https://open.spotify.com/artist/1XErHyc10VN8bfGKma1Yyc"
                },
                "href" : "https://api.spotify.com/v1/artists/1XErHyc10VN8bfGKma1Yyc",
                "id" : "1XErHyc10VN8bfGKma1Yyc",
                "name" : "Leo Ieiri",
                "type" : "artist",
                "uri" : "spotify:artist:1XErHyc10VN8bfGKma1Yyc"
                } ],
                "available_markets" : [ "AD", "AE", "AR", "AT", "AU", "BE", "BG", "BH", "BO", "BR", "CA", "CH", "CL", "CO", "CR", "CY", "CZ", "DE", "DK", "DO", "DZ", "EC", "EE", "EG", "ES", "FI", "FR", "GB", "GR", "GT", "HK", "HN", "HU", "ID", "IE", "IL", "IN", "IS", "IT", "JO", "JP", "KW", "LB", "LI", "LT", "LU", "LV", "MA", "MC", "MT", "MX", "MY", "NI", "NL", "NO", "NZ", "OM", "PA", "PE", "PH", "PL", "PS", "PT", "PY", "QA", "RO", "SA", "SE", "SG", "SK", "SV", "TH", "TN", "TR", "TW", "US", "UY", "VN", "ZA" ],
                "disc_number" : 1,
                "duration_ms" : 240546,
                "episode" : false,
                "explicit" : false,
                "external_ids" : {
                "isrc" : "JPVI01904610"
                },
                "external_urls" : {
                "spotify" : "https://open.spotify.com/track/10mmoWFRBG9mlLekSWYzWT"
                },
                "href" : "https://api.spotify.com/v1/tracks/10mmoWFRBG9mlLekSWYzWT",
                "id" : "10mmoWFRBG9mlLekSWYzWT",
                "is_local" : false,
                "name" : "未完成",
                "popularity" : 63,
                "preview_url" : "https://p.scdn.co/mp3-preview/5f9e6137ad9fa8d859cba76f4387aeb69d474a41?cid=e15bfd0dccb54db5a1757b7621535f7f",
                "track" : true,
                "track_number" : 1,
                "type" : "track",
                "uri" : "spotify:track:10mmoWFRBG9mlLekSWYzWT"
            },
            "video_thumbnail" : {
                "url" : null
            }
        }, {
            ・・・ <<省略>> ・・・
        } ],
            "limit" : 100,
            "next" : null,
            "offset" : 0,
            "previous" : null,
            "total" : 50
        },
        "type" : "playlist",
        "uri" : "spotify:playlist:37i9dQZF1DX9vYRBO9gjDe"
    }

##② Oracle Databaseがアクセスできるディレクトリへ配置
ここでは、取得したspotify.txtを Oracle Databaseがアクセスできる/home/oracle/work ディレクトリ配下へ配置します

[oracle@db-inst01 ~]$ cd /home/oracle/work
[oracle@db-inst01 work]$ ls -lh
　　　-rw-r--r--. 1 oracle oinstall 195K Apr  7 14:48 spotify.txt

#■JSONデータと外部表のマッピング
JSONデータにはPlaylist's Tracks のデータが含まれており、必要なデータをDBテーブルでSELECTできるようにするためのマップ表を作成します

DBテーブル：Column	JSONデータ: $.tracks.items[*]	データ型	内容
added_at	$.added_at	DATE	データ登録日
artists_id	$.track.artists.id	VARCHAR2	アーティストID
artists_name	$.track.artists.name	VARCHAR2	アーティスト名
track_id	$.track.id	VARCHAR2	トラックID
track_name	$.track.name	VARCHAR2	トラック名
album_id	$.track.album.id	VARCHAR2	アルバムID
album_name	$.track.album.name	VARCHAR2	アルバム名
album_release_date	$.track.album.release_date	DATE	アルバム・リリース日
album_type	$.track.album.type	VARCHAR2	トラック・タイプ
track_popularity	$.track.popularity	NUMBER	トラックの人気値

#■外部表作成
以下の手順で外部表を作成します
外部表を使用することで、外部のファイル・システムに配置されたJSONドキュメントにアクセスできます

##●DIRECTORYオブジェクト作成
JSONドキュメントが存在するサーバー・ファイル・システム上のディレクトリを示すディレクトリ・オブジェクトを作成
ディレクトリ・オブジェクトは、外部バイナリ・ファイルLOB(BFILE)および外部表データが存在するサーバー・ファイル・システム上ディレクトリの別名です

SQL> CREATE OR REPLACE DIRECTORY JSON_LOADER_OUTPUT AS '/home/oracle/work';
    ディレクトリが作成されました。

##●外部表作成
以下CREATE TABLEで、一般的なNoSQL JSONドキュメント・ストアのエクスポート形式で保存されたJSONドキュメントへのアクセスを提供する外部表の作成に必要なDDLで外部表作成します

   SQL> CREATE TABLE spotify_json_contents (json_document CLOB)
        ORGANIZATION EXTERNAL (
            TYPE ORACLE_LOADER DEFAULT DIRECTORY json_loader_output
            ACCESS PARAMETERS (
                RECORDS DELIMITED BY 0x'0A'
                BADFILE order_entry_dir:'JSON_DUMPFILE_CONTENTS.bad'
                LOGFILE order_entry_dir:'JSON_DUMPFILE_CONTENTS.log'
                DISABLE_DIRECTORY_LINK_CHECK
                FIELDS (
                    json_document CHAR(50000000)
                )
            )
            LOCATION (JSON_LOADER_OUTPUT:'spotify.txt')
        ) PARALLEL
        REJECT LIMIT UNLIMITED
    /

#■ JSONデータSQL実行

このJSONデータには、１つのJSONデータに50のtracksデータがネストされた配列ではいっています
この配列をSQLするために、JSON_TABLEにNESTED句があり、配列の項目の列ベースのコンテンツを生成する列記述子を“$.tracks.items[*]”の行パターンを指定すると、tracks.items鍵に対応付けられた50tracks配列の各メンバーと一致する50行のトラックを生成することができます

##① SQL実行
「■ JSONデータと外部表のマッピング」で作成したマッピングを元にSELECT文を実行

SQL> SELECT D.* 
     FROM spotify_json_contents p, 
        JSON_TABLE(
            p.json_document ,
            '$' 
            columns(
                NESTED PATH '$.tracks.items[*]' 
                columns(
                    added_at VARCHAR2(40 CHAR) path '$.added_at',
                    artists_id VARCHAR2(25 CHAR) path '$.track.artists.id',
                    artists_name VARCHAR2(50 CHAR) path '$.track.artists.name',
                    track_id VARCHAR2(25 CHAR) path '$.track.id',
                    track_name VARCHAR2(100 CHAR) path '$.track.name',
                    album_id VARCHAR2(25 CHAR) path '$.track.album.id',
                    album_name VARCHAR2(100 CHAR) path '$.track.album.name',
                    album_release_date DATE path '$.track.album.release_date',
                    album_type VARCHAR2(10 CHAR) path '$.track.album.type',
                    track_popularity NUMBER(3) path '$.track.popularity'
                )
            )
        ) D
     ;

##② 実行結果
以下のように５０行のTrackデータが出力されればOK

#■参考
##●ORACLE
　・ホワイトペーパ:
　　　- Oracle Database 12c Release 2を使用したJSONの問合せ
　・マニュアル:
　　　- JSON Developer's Guide
　　　- データベース・ユーティリティ:外部表
##●Spotify Developer
　・DOCS WEB API: Get a Playlist's Tracks