Postgresql 上面的「尿布、啤酒、星期五」，不用寫程式下行 SQL 就搞定

MADlib + postgresql 9.4 in Linux CentOS 7
MADlib®: Big Data Machine Learning in SQL for Data Scientists

啤酒、尿布、星期五

MADlib 在 postgresql 上面以是以 postgres 函式的型態來實現對於數據資料的操作，你可以透過這個以 plpython 語言寫的函式，直接操作原始數據轉存到新的資料庫當中，這個函式庫支援很多機器學習的演算法，可以很方便的分析數據. 目前提供的有底下這些，看起來應該有陸續發展的可能，因為已經被放到 Apache 基金會的子項目計畫內了...XDXD

功能：
Classification 分類
Regression 回歸
Clustering 集群
Topic Modeling 主題模型
Association Rule Mining 關聯性規則
Descriptive Statistics 描述性統計
Validation 驗證

more... http://madlib.net/product/


官方網站 http://madlib.net/

我使用來安裝的版本是 CentOS Linux 7 ，但是安裝這個 LIB 前請先選擇一套支援的資料庫系統，這裡我用的是 Postgresql 9.4 的版本，安裝請參考這一篇：
http://blog.jangmt.com/2016/03/postgresql-94-in-centos-linux-7.html

下載 http://madlib.net/download/

安裝
[root@c7 ~]#  yum install madlib-1.8-Linux.rpm  -y

檢查是否裝了，安裝好後他的路徑放在 /usr/local/madlib/
[root@c7 ~]# rpm -qa | grep madlib
madlib-1.8-1.x86_64


我使用 Single Node 的模式，系統需求:( HAWQ >= 1.2, Greenplum >= 4.2 or PostgreSQL >= 9.2 )

在安裝後，這特 MADlib 必須搭配資料庫才可以使用，所以必須做一個匯入函式到資料庫的動作。

madlib 是 PostgreSQL 的自訂函數  ，它會透過 plpython2 在 postgresql 內產生自訂的函式。

centos7 預設安裝沒裝 plpython2 ，補安裝
[root@c7 ~]# yum install postgresql94-plpython -y
# 安裝好後，透過 madpack 將建立好的個人資料庫，匯入成為 postgresql 的函式。
# 由於此 lib 支援兩種的資料庫(postgres or Pivotal Greenplum) ，但這裡以 postgres 為主
# 安裝語法: madpack install -s madlib -p 資料庫種類 -c 帳號@主機:5432/資料庫
# 建議使用者，一人一個帳號，一個資料庫彼此的獨立，這樣才不會相互干擾。底下這個範例，我已經建立了一個個人的帳號及資料庫了。
# 帳號:  mtchang
# 主機:  127.0.0.1
# 資料庫:  mtchang

[mtchang@c7 ~]$ /usr/local/madlib/bin/madpack install -s madlib -p postgres -c mtchang@127.0.0.1:5432/mtchang
Password for user mtchang:  ooxx
madpack.py : INFO : Detected PostgreSQL version 9.4.
madpack.py : INFO : *** Installing MADlib ***
madpack.py : INFO : MADlib tools version    = 1.8 (/usr/local/madlib/Versions/1.8/bin/../madpack/madpack.py)
madpack.py : INFO : MADlib database version = None (host=127.0.0.1:5432, db=mtchang, schema=madlib)
madpack.py : INFO : Testing PL/Python environment...
madpack.py : INFO : > Creating language PL/Python...
madpack.py : INFO : > PL/Python environment OK (version: 2.7.5)
madpack.py : INFO : Installing MADlib into MADLIB schema...
madpack.py : INFO : > Creating MADLIB schema
madpack.py : INFO : > Creating MADLIB.MigrationHistory table
madpack.py : INFO : > Writing version info in MigrationHistory table
madpack.py : INFO : > Creating objects for modules:
madpack.py : INFO : > - array_ops
madpack.py : INFO : > - bayes
madpack.py : INFO : > - crf
madpack.py : INFO : > - elastic_net
madpack.py : INFO : > - linalg
madpack.py : INFO : > - pmml
madpack.py : INFO : > - prob
madpack.py : INFO : > - quantile
madpack.py : INFO : > - sketch
madpack.py : INFO : > - svd_mf
madpack.py : INFO : > - svec
madpack.py : INFO : > - tsa
madpack.py : INFO : > - conjugate_gradient
madpack.py : INFO : > - data_profile
madpack.py : INFO : > - lda
madpack.py : INFO : > - stats
madpack.py : INFO : > - svec_util
madpack.py : INFO : > - utilities
madpack.py : INFO : > - assoc_rules
madpack.py : INFO : > - cart
madpack.py : INFO : > - convex
madpack.py : INFO : > - glm
madpack.py : INFO : > - kernel_machines
madpack.py : INFO : > - linear_systems
madpack.py : INFO : > - recursive_partitioning
madpack.py : INFO : > - regress
madpack.py : INFO : > - sample
madpack.py : INFO : > - summary
madpack.py : INFO : > - kmeans
madpack.py : INFO : > - pca
madpack.py : INFO : > - validation
madpack.py : INFO : MADlib 1.8 installed successfully in MADLIB schema.

# 這樣就完成了!!!!

# 來個範例試試看 ，我找了一下看到了 Association rules ，這個比較熟悉所以使用這個當練習。
參考：http://doc.madlib.net/latest/group__grp__assoc__rules.html#examples

(1) 先由 SQL 匯入一些資料看看

DROP TABLE IF EXISTS test_data;
CREATE TABLE test_data (
    trans_id INT,
    product TEXT
);
INSERT INTO test_data VALUES (1, 'beer');
INSERT INTO test_data VALUES (1, 'diapers');
INSERT INTO test_data VALUES (1, 'chips');
INSERT INTO test_data VALUES (2, 'beer');
INSERT INTO test_data VALUES (2, 'diapers');
INSERT INTO test_data VALUES (3, 'beer');
INSERT INTO test_data VALUES (3, 'diapers');
INSERT INTO test_data VALUES (4, 'beer');
INSERT INTO test_data VALUES (4, 'chips');
INSERT INTO test_data VALUES (5, 'beer');
INSERT INTO test_data VALUES (6, 'beer');
INSERT INTO test_data VALUES (6, 'diapers');
INSERT INTO test_data VALUES (6, 'chips');
INSERT INTO test_data VALUES (7, 'beer');
INSERT INTO test_data VALUES (7, 'diapers');


(2) 先建立一個 myschema 的 schemas(模式)，再來執行下面的指令。

(3) 使用 Association rules 函式庫
# 關聯性規格則請參考這一篇： http://blog.jangmt.com/2015/10/fpgrowth-algorithm.html
# 函式用法：
assoc_rules_results assoc_rules (
float8 support, (支持度)
float8 confidence, (信心)
text tid_col, 欄位 tid
text item_col, 欄位 item
text input_table, (來源資料表格)
text output_schema, (運算後輸出的 schema)
boolean verbose 有沒有 comments
)
# 範例：
SELECT * FROM madlib.assoc_rules( .25,.5,'trans_id','product','test_data','myschema',TRUE);

(4) Association rules 針對 test_data 產生的結果
# 產生的結果，會放在 myschema 內，也可以用這個 sql 來觀看
# sql 觀看結果
SELECT * FROM myschema.assoc_rules ORDER BY support DESC;

更多閱讀：
官方的安裝手冊 https://github.com/madlib/madlib/wiki/Installation-Guide
Association rules 函式庫 http://doc.madlib.net/latest/assoc__rules_8sql__in.html