LoginSignup
3
5

More than 3 years have passed since last update.

@shirok(Shiro Kobayashi)in日本オラクル株式会社

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

Last updated at Posted at 2020-04-11

■目的

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
SQL.png

■参考

●ORACLE

　・ホワイトペーパ:
　　　- Oracle Database 12c Release 2を使用したJSONの問合せ
　・マニュアル:
　　　- JSON Developer's Guide
　　　- データベース・ユーティリティ:外部表

●Spotify Developer

　・DOCS WEB API: Get a Playlist's Tracks

3
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5