構建AI前的數據準備，SQL要比Python強

選自towardsdatascience

作者：Marc Laforet

機器之心編譯

參與：高璇、路

Python 可以完成某項任務，並不意味著這個任務就應該使用 Python 來做。

作為一名 Web 開發人員，我第一次與資料庫和 SQL 產生交集是使用對象關係映射（ORM）。我使用的是 Django 查詢集 API，這個界面用戶體驗很好。之後，我轉向數據工程方向，更多地利用數據集來構建 AI。我的職責是從用戶應用程序中獲取數據，並將其轉換為數據科學家可利用的內容，這一過程通常稱為ETL(extract, transform and load)。

隨著產業發展，生產系統中的數據非常混亂，需要進行大量轉換才能用於構建 AI。有些 JSON 列每行模式都不相同，有些列包含混合數據類型，有些行有錯誤值。此外，還需要計算「用戶成為訪問者的時間」以及「他們在兩次訪問間的等待時間」等特徵。當我著手清理、聚合和管理數據特徵時，我想確定哪種語言最適合該任務。在之前的工作中我每天都使用Python，我知道它可以完成工作。但是，這次經歷使我了解到，Python 可以完成一項任務並不意味著這個任務就應該使用 Python 來做。

我對 SQL 的第一個誤解是：SQL 無法進行複雜的轉換

我們正在處理一個時間序列數據集，我們希望能夠跟蹤特定用戶。隱私法規不允許獲取用戶訪問的具體日期，因此我們決定將記錄日期歸一化為用戶首次訪問的日期（如首次訪問後 5 天等）。對於我們的分析，重要的是要知道離上次訪問過去了多久以及離首次訪問過去了多久。A 有兩個樣本數據集，一個有大約 750 萬行，大小為 6.5 GB，另一個有 55 萬行，大小為 900MB。

我使用下面的 Python 和 SQL 代碼先在較小的數據集上測試轉換。Python 和 SQL 分別花費 591 秒和 40.9 秒完成了任務。這意味著 SQL 的速度是 Python 的大約 14.5 倍！

SQL 轉換不僅速度更快，而且代碼也更易讀，更易於維護。在這裡，我使用 lag 和 first_value 函數來查找用戶歷史記錄中的特定記錄（即分區）。然後使用 age 函數來確定兩次訪問間的時間差。

更有趣的是，當這些轉換腳本應用於 6.5 GB 的數據集時，Python 完全失敗。在 3 次嘗試中，Python 崩潰了 2 次，第三次我的計算機完全崩潰...... 而 SQL 只耗時 226 秒。

更多信息參見：

https://www.postgresql.org/docs/9.5/functions-window.html

http://www.postgresqltutorial.com/postgresql-window-function/

我對 SQL 的第一個誤解是：SQL 無法扁平化不規則的 json

對我來說，另一個改變是我意識到 Postgres 可以很好地處理 json。我最初認為用 Postgres 扁平化或解析 json 是不可能的...... 我不敢相信自己竟然如此愚蠢。如果你想關聯 json 並且它的模式在行間是一致的，那麼最好的選擇可能就是使用 Postgres 內置功能來解析 json。

另一方面，我的樣本數據集中一半 json 不是有效的，因此存儲為文本。在這種情況下，我要麼重新編碼數據使其有效，或者刪除無效的行。為此，我創建了一個名為 is_json 的新 SQL 函數，然後使用該函數來驗證 WHERE 子句中的 json 是否有效。

不幸的是，我發現 user_json 具有不同的模式，具體取決於用戶所使用的 app 版本。雖然從應用程序開發的角度來看這是有道理的，但是有條件地解析每行的每種可能性代價是很高昂的。難道我的最終歸宿還是 Python？不不不！我在 Stack Overflow 上找到了一個由 Postgres 大神編寫的 klin 函數（https://stackoverflow.com/users/1995738/klin）。

這個函數能夠成功地扁平化 json，輕鬆解決我的噩夢。

結語

有一種說法叫「Python 是做任何事情的第二好語言」。我相信這是真的，並且在某些情況下 Python 和「最好」語言之間的性能差異可以忽略不計。但是在本文介紹的情況下，Python 無法與 SQL 比肩。這些發現完全改變了我做 ETL 的方法。我現在的工作模式是「不要將數據移動到代碼中，而是將代碼移動到數據中」。Python 將數據移動到代碼中，而 SQL 執行後者。更重要的是，我知道我只是觸及了 SQL 和 postgres 的皮毛。我期待能發掘出更多出色的功能，使用分析庫實現加速。

本文為機器之心編譯，轉載請聯繫本公眾號獲得授權。

------------------------------------------------

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！