Last.fm 百萬歌曲資料集 • PostgreSQL 的藝術

接下來兩個案例研究使用 Last.fm 資料集——**百萬歌曲資料集（Million Song Dataset, MSD）**官方的歌曲標籤與歌曲相似度資料。MSD 團隊與 Last.fm 合作，提供最大的歌曲層級標籤與預先計算的歌曲相似度研究資料集，且所有資料都關聯到 MSD track，方便連結其他 MSD 資源（音訊特徵、藝人資料、歌詞等）。

匯入 SQLite 資料庫#

資料集同時以 SQLite 資料庫與 JSON 檔提供。載入 SQLite 資料庫用 pgloader 很容易：

$ curl -L -o /tmp/lastfm_tags.db \
       http://labrosa.ee.columbia.edu/millionsong/sites/default/files/lastfm/lastfm_tags.db

$ pgloader /tmp/lastfm_tags.db pgsql://appdev@localhost/appdev

pgloader 透過 sqlite_master 目錄與 PRAGMA table_info() 取出資料表與索引定義，再以 COPY 協定用串流方式把資料搬進 PostgreSQL。

延伸輸出：pgloader 匯入報表

             table name      errors        read    imported       bytes       total time
-----------------------   ---------   ---------   ---------   ---------   --------------
                  fetch           0           0           0                       0.000s
        fetch meta data           0           8           8                       0.028s
          Create tables           0           6           6                       0.031s
                   tids           0      505216      505216      9.2 MB           1.893s
                   tags           0      522366      522366      8.6 MB           1.781s
                tid_tag           0     8598630     8598630    135.7 MB          32.614s
COPY Threads Completion           0           4           4                      34.366s
         Create Indexes           0           5           5                    2m14.346s
 Index Build Completion           0           5           5                      36.976s
-----------------------   ---------   ---------   ---------   ---------   --------------
      Total import time           ✓     9626212     9626212    153.4 MB        3m25.743s

看過專案的 demo_tags.py 腳本後會發現，它使用 SQLite 的 64 位元帶號整數 ROWID 系統欄位來關聯資料。我們需要對等的欄位才能讓資料對得起來：

begin;

alter table tags add column rowid serial;
alter table tids add column rowid serial;

commit;

有了新欄位就能初探資料。先在使用者自訂標籤中搜尋 Brian Setzer：

  select tags.tag, count(tid_tag.tid)
    from tid_tag, tags
   where tid_tag.tag=tags.rowid and tags.tag ~* 'setzer'
group by tags.tag;

             tag             │ count
═════════════════════════════╪═══════
 Brian Setzer                │     1
 Setzer                      │    13
 brian setzer is GOD         │     1
 brian setzer orchestra      │     3
 rockabilly Setzer style     │     4
 setzer is a true guitarhero │     9
 ...
(8 rows)

Time: 394.927 ms

這個查詢在 tids（track id）與 tid_tag（track 與 tag 的關聯）之間做 join，並以不分大小寫的正規表達式 'setzer' 過濾。從執行時間就能猜到——這個過濾條件目前沒有索引可用。

從 JSON 檔補上歌名與藝人#

MSD 專案也以一組 JSON 編碼的文字檔發布資料，其中有 track id 之外的額外資訊，例如歌名與藝人。track id 長得像 TRVBGMW12903CBB920，對人類來說不是好的歌曲指稱方式，所以下載 JSON 資源並寫個小解析腳本處理：

curl -L -o /tmp/lastfm_subset.zip \
     http://labrosa.ee.columbia.edu/millionsong/sites/default/files/lastfm/lastfm_subset.zip

新內容載入新的資料表：

begin;

create table lastfm.track
  (
     tid    text,
     artist text,
     title  text
  );

commit;

作者以他偏好的 Common Lisp 撰寫解析腳本：直接讀取 zip 檔、在記憶體中解壓並解析其中的 JSON 檔，不在客戶端把 JSON 落地寫檔，一邊解析一邊透過 COPY 串流注入 PostgreSQL，全部內容在單一 PostgreSQL 命令中完成。

延伸範例：Common Lisp 的 zip → COPY 載入腳本

(defpackage #:lastfm
   (:use #:cl #:zip)
  (:import-from #:cl-postgres
                #:open-db-writer
                #:close-db-writer
                #:db-write-row))

(in-package #:lastfm)

(defvar *db* '("appdev" "appdev" nil "localhost" :port 5432))
(defvar *tablename* "lastfm.track")
(defvar *colnames* '("tid" "artist" "title"))

(defun process-zipfile (filename)
  "Process a zipfile by sending its content down to a PostgreSQL table."

  (pomo:with-connection *db*

    (let ((count 0)
          (copier (open-db-writer pomo:*database* *tablename* *colnames*)))

      (unwind-protect
           (with-zipfile (zip filename)
             (do-zipfile-entries (name entry zip)
               (let ((pathname (uiop:parse-native-namestring name)))
                 (when (string= (pathname-type pathname) "json")
                   (let* ((bytes    (zipfile-entry-contents entry))
                          (content
                           (babel:octets-to-string bytes :encoding :utf-8)))
                     (db-write-row copier (parse-json-entry content))
                     (incf count))))))
        (close-db-writer copier))

      ;; Return how many rows we did COPY in PostgreSQL
      count)))

(defun parse-json-entry (json-data)
  (let ((json (yason:parse json-data :object-as :alist)))
    (list (cdr (assoc "track_id" json :test #'string=))
          (cdr (assoc "artist"   json :test #'string=))
          (cdr (assoc "title"    json :test #'string=)))))

同樣的技巧可以用任何程式語言實作——前提是你選用的 PostgreSQL 驅動程式有暴露 COPY 協定。務必確認這點，並學會用它正確載入資料。以作者使用的 Postmodern（Common Lisp）驅動為例，COPY API 就三個函式：open-db-writer 開啟 COPY 串流、db-write-row 推送一列、close-db-writer 收尾關閉串流；腳本每解析完一個 JSON 檔就推送一列。

客戶端雖然不落地，PostgreSQL 伺服器端收到 COPY 協定的資料時，當然還是要把資料序列化到磁碟。

初探資料模型#

用幾個互動式查詢滿足好奇心。先看資料集裡最多歌曲的藝人：

  select artist, count(*)
    from lastfm.track
group by artist
order by count desc
   limit 10;

           artist            │ count
═════════════════════════════╪═══════
 Mario Rosenstock            │    13
 Aerosmith                   │    12
 Snow Patrol                 │    12
 Phil Collins                │    12
 Shakira                     │    11
 Radiohead                   │    11
 Nick Cave and the Bad Seeds │    11
 ...
(10 rows)

再看 Last.fm 使用者幫 Aerosmith 貼過哪些標籤。這個簡單而經典的查詢展示了整個資料模型如何組合：tags、tid_tag、tids 來自專案的 SQLite 資料庫，track 則是我們從 JSON zip 檔 COPY 進來的新表：

  select track.artist, tags.tag, count(*)
    from tags
         join tid_tag tt on tags.rowid = tt.tag
         join tids on tids.rowid = tt.tid
         join lastfm.track on track.tid = tids.tid
   where track.artist = 'Aerosmith'
group by artist, tags.tag
order by count desc
   limit 10;

  artist   │      tag       │ count
═══════════╪════════════════╪═══════
 Aerosmith │ Radio4You      │    12
 Aerosmith │ hard rock      │    12
 Aerosmith │ rock           │    11
 Aerosmith │ classic rock   │    11
 Aerosmith │ 70s            │    10
 Aerosmith │ 80s            │     9
 ...
(10 rows)

這裡只列出十列——光是 Aerosmith 一個樂團，資料集就有 464 個不重複標籤。其中一個拼作 favorites；那麼不論哪種拼法，哪些歌曲被使用者標為最愛？

  select track.tid, track.title, tags.tag
    from tags
         join tid_tag tt on tags.rowid = tt.tag
         join tids on tids.rowid = tt.tid
         join lastfm.track on track.tid = tids.tid
   where track.artist = 'Aerosmith'
     and tags.tag ~* 'favourite'
order by tid, tag;

        tid         │          title          │              tag
════════════════════╪═════════════════════════╪════════════════════════════════
 TRAQPKV128E078EE32 │ Livin' On The Edge      │ Favourites
 TRAVUAJ128E078EDA2 │ What It Takes           │ favourite
 TRAYKOC128F930D2B8 │ Cryin'                  │ Favourites
 TRAZDPO128E078ECE6 │ Crazy                   │ all- time favourite
 TRAZISI128E078EE2F │ Same Old Song and Dance │ first favourite metalcore song
 TRBARHH128E078EDE9 │ Janie's Got A Gun       │ favourite
 ...
(12 rows)

對資料集有了概念之後，就可以拿它來解決更有趣的使用情境了。