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

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

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


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

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

```shell
[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 ディレクトリ配下へ配置します

```shell-session

[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 ](https://developer.spotify.com/documentation/web-api/reference/playlists/get-playlists-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
SQL> CREATE OR REPLACE DIRECTORY JSON_LOADER_OUTPUT AS '/home/oracle/work';
    ディレクトリが作成されました。
```

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


```sql
   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
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
![SQL.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/dbc4f958-d029-e957-96df-b76c373d0298.png)





#■参考
##●ORACLE
　・ホワイトペーパ:
　　　- [Oracle Database 12c Release 2を使用したJSONの問合せ](https://www.oracle.com/technetwork/jp/database/sql-json-wp-2604702-ja.pdf)
　・マニュアル: 
　　　- [JSON Developer's Guide](https://docs.oracle.com/cd/E82638_01/adjsn/json-in-oracle-database.html#GUID-A8A58B49-13A5-4F42-8EA0-508951DAE0BB)
　　　- [データベース・ユーティリティ:外部表](https://docs.oracle.com/cd/E82638_01/sutil/oracle-external-tables.html#GUID-038ED956-A6EE-4C6D-B7C9-0D406B8088B6)
##●Spotify Developer
　・DOCS WEB API: [ Get a Playlist's Tracks](https://developer.spotify.com/documentation/web-api/reference/playlists/get-playlists-tracks/)