渡鴉之丘: 一個 PL/R 的使用範例

其實，在 PostgreSQL 產生樞紐表已經有一個模組 tablefunc，之前也有紀錄一篇筆記。不過聽說 tablefunc 的 crosstab() 在資料量「太大」的時候，會很耗記憶體用量. . . 有多麼嚴重，我也沒東西可以試驗，就留給有實際應用的人，使用後製作使用心得了。
在這裡是藉著這個功能，來了解 R 語言嵌入 PostgreSQL 作為 Stored Procedure 的用法的筆記～
對了，這個 R 語言的 Procedural Language 外掛叫做 PL/R～

先提一下 R 環境如何 PGSQL 互動。一般來說，在 R 語言要與 PGSQL 溝通，有兩種主要的客戶端連線模組（還有其他的，例如 RPostgres 或 RJDBC 或 RODBC 等，類似下列前者）

RpostgreSQL 跟一般程式語言提供的功能比較像（例如 JDBC、ODBC 或 PsychoPG 等等），都是提供資料庫連線、執行 SQL / Cursor / Prepared Statement 的函數，都需要自己寫 SQL 指令。並且資料查回 R 環境，就是待在記憶體上，因此使用上需要考量資料量。

而 PivotalR 則比較貼近 R 的使用經驗，可以直接把資料庫表格「變成」Dataframe 而不用寫 SQL！而 PivotalR 對資料的處理，不像 RPostgreSQL 一樣會立刻把資料查詢回來，而是先轉譯指令變成 SQL，等到最後才發送查詢，讓一些運算留在資料庫主機上進行。此外，PivotalR 直接支援呼叫 PostgresSQL/Greenplum 等資料庫安裝的 MADLib 運算模組，是一個屬於大量資料分析的工具。

PL/R 是將 R 語言的 Script 嵌入 PGSQL 作為 Proceudral Language 使用的外掛。PL/R 提供的內部函數比較貼近 RPostgreSQL（好像是因為是同一批人開發的？），提供的函數大多可以不用改名字，直接套進來用。但 PL/R 比較不貼近 PivotalR，沒辦法在 PL/R 直接把表格變成 Dataframe，使用上就沒有這麼親切～

實際使用 PL/R 後，發現PL/R 與一般 R 的使用比較，可以整理出以下的幾點要注意：

PL/R 不是互動式操作；並且從 R Script 移轉到 PL/R 需要作調整！

這表示調整過程很不便利，可能需要一段段把結果匯出（匯出文字檔吧，目前我是這樣作）以確認 Script 移轉的運作正常

可以使用所有 R 的模組，但是請先在普通的 R Shell 底下先裝好

PL/R 沒辦法互動操作，當然在 PL/R 安裝 R 模組也不會是好選項～

PL/R 比較貼近 RPostgreSQL，而不是 PivotalR～

PL/R 和 RPostgreSQL 使用上有共通的函數，可以套過來；但是從 PivotalR 移過來就要稍微注意了，需要把資料處理轉化為 PL/R 可以用的方式才行。

PGSQL 的 Procedural Language 沒有 Table-Values Variable

透過 Postgres 引入 PL/R 的參數型態，雖然支援所有欄位型態，或是 Table Record（宣告時直接用表格當參數），但是實際在執行的時候，資料是一筆筆進入函數執行，不會一次就一整批進去函數。

下面是一個小範例，試著把表格當參數（PivotalR 的使用習慣～），以便表格資料都塞到 Dataframe 內，但是失敗

CREATE OR REPLACE FUNCTION tbl2dataframe(emp %ROWTYPE) -- 拿掉 %rowtype 也行
RETURNS SETOF emp AS $$
write.table(arg1, file = "/tmp/tbl_dataframe.csv", row.names=FALSE, na="", col.names=TRUE, sep=",")
return(arg1)  # 直接回傳
$$ LANGUAGE 'plr';

edb=# SELECT tbl2dataframe(emp) FROM emp;
                           tbl2dataframe                
            
---------------------------------------------------------------------
 (7369,SMITH,CLERK,7902,"17-DEC-80 00:00:00",800.00,,20)
 (7499,ALLEN,SALESMAN,7698,"20-FEB-81 00:00:00",1600.00,300.00,30)
 (7521,WARD,SALESMAN,7698,"22-FEB-81 00:00:00",1250.00,500.00,30)
(中間略)
(7934,MILLER,CLERK,7782,"23-JAN-82 00:00:00",1300.00,,10)
(14 rows)
edb=# \! cat /tmp/tbl_dataframe.csv
"empno","ename","job","mgr","hiredate","sal","comm","deptno"
7934,"MILLER","CLERK",7782,"23-JAN-82 00:00:00",1300,,10

上面範例中，每次都只對一筆資料執行函數，因此資料並不會成功的累加到 R 的 Dataframe，這在匯出的檔案只有最後一筆就可以看到。

不容易在 SQL 語句內把執行中產生的 return set 直接代入 PL/R 函數＆直接轉成 R 的 dataframe 進行分析演算

當我們想要在 SQL 語句中，把篩出的查詢資料，直接在 SQL Query 呼叫 PL/R 撰寫的函數進行處理（例如，很多 Join 或是搭配 Group by 的複雜 Query ～），會發現想要把這些資料直接透過呼叫 PL/R 撰寫的函數，送到 R 裡面直接轉變成 dataframe 進行演算不太容易。

這個困擾的原因是 PGSQL 沒有支援 table-valued variable 的緣故，使的要直接把多筆資料一次代入 PL/R 函數轉換成 dataframe 使用有一些困難。

通常在一般的 PL/pgSQL 要達成這種功能，會使用 Aggregation Function 處理，或是在查詢時把多筆資料轉成 Array 送入函數內處理。才能夠呼叫 SQL WHERE / GROUP BY / ORDER BY 等條件篩選過的欄位。

但是 Aggregation 的功能一般來說是代入多筆資料，聚合成單一結果回傳，這與 PL/R 匯入 dataframe 的目的不完全一樣：PL/R 作分析需要利用 dataframe，但不是每個 PL/R 都要把結果縮減成單一結果，很多狀況都希望可以在一句 SQL 用條件過濾出資料再代入函數。

一個 PGSQL Session 期間，都是同一個 R 的程序在服務

利用這個特性，可以彌補上述的缺點：透過 R 的全域變數（Global Data 或稱作 Global Variable）的功能，紀錄需要累計的狀態。

PL/R 從 Postgres 吃的參數不支援 SQL Cursor

要使用 Cursor 的話，只能在裡面用函數處理 Query 的 Cursor

函數回傳值使用 RETURNS SETOF record 不夠方便

這是一般 PGSQL 的 Stored Procedure 的共通問題，呼叫這種回傳值的函數，需要使用 SELECT . . . AS (col TYPE, [. . .]); 的方式，明確指定出欄位及其型態。
由於這個限制，使用上就沒辦法彈性的把任意 Dataframe 回傳出來。欄位需要預先知道才行。

上述這些點，對於習慣使用 R 的人，可能有一點違反 R 的操作直覺（改 R Script 沒有辦法用互動式輸出確認）。不過整體來說，將原先使用 RPostgreSQL 模組的 Script 不加修改地移植到 PL/R 上面還是容易的（保留所有 SQL 執行，不去管 PL/R 的引入參數或是回傳值就好～）

就目前學習的感想，PL/R 的使用場合，除了建造正規的 Aggregation Function 可以從呼叫的 Query 取得資料之外，最方便的方式還是使用內建 API 執行 Dynamic Query（組合出 Query 「字串」再執行），但這樣多少降低了一點應用上的靈活性。

接著就是正文：使用 PL/R 產生樞紐表。

基本上，與之前的 tablefunc 模組的 crosstab() 操作筆記一樣，先用 SQL 取得製作樞紐表的總結資訊（三個欄位，分別是 x 軸品項、y 軸品項、以及累積項目），然後用 R 的 reshape 模組，將資料重新排列成樞紐表的格式。

此處使用 EDB 企業版內提供的範例資料。另外，簡單起見，這裡只進行依照品項（部門、職稱）的加總資料（薪水）（薪水總和好像沒意義～）。有需要的話，只要把個別的加總換成其他的 PGSQL Aggregation 就好。

在 PL/R 裡面使用 Query：可以的話，查詢的 SQL 指令盡量用條件縮小回傳的資料量，方便處理

CREATE OR REPLACE FUNCTION pivotsum(tablename varchar, rowc varchar, colc varchar , cellc varchar)  
returns SETOF record AS $$ 

#Creating query statement and performing query
qstr=paste("SELECT", rowc, ",", colc, ",", "sum(", cellc, ") FROM", tablename, 
           "GROUP BY 1,2", "ORDER BY 1,2", sep=" ")
pivot_summary=pg.spi.exec(qstr)

# Ready for pivoting
library(reshape2) 

names(pivot_summary) <- c("v1", "v2", "v3")   # Rename elements

wide_table <- dcast(pivot_summary, pivot_summary$v1~pivot_summary$v2, value.var = "v3") 

write.table(wide_table, file = "/tmp/pivot.csv",row.names=FALSE, na="",col.names=TRUE, sep=",")

return(wide_table) 

$$ LANGUAGE 'plr';

edb=# select * from pivotsum('emp','deptno','job','sal') AS ( deptno int,"ANALYST" numeric,"CLERK" numeric,"MANAGER" numeric,"PRESIDENT" numeric,"SALESMAN" numeric);
 deptno | ANALYST | CLERK | MANAGER | PRESIDENT | SALESMAN 
--------+---------+-------+---------+-----------+----------
     10 |         |  1300 |    2450 |      5000 |         
     20 |    6000 |  1900 |    2975 |           |         
     30 |         |   950 |    2850 |           |     5600
(3 rows)

可惜的是，這裡直接在函數裡面用 Dynamic Query，因此沒辦法從外面送資料進去，因此沒辦法有效的進行 SQL 條件過濾（WHERE）或分組（GROUP BY）。但是若打算直接把上面的樞紐表函數轉成 aggregation ，會因為 PGSQL 的 Aggregtion 回傳結果不可以為多筆資料（setof record）的關係而不容易達成。

以下利用 array_agg() 這個 Aggregation Function 把多筆資料轉換成 PGSQL Array 送入 PL/R 函數，避開 Procedural Language 一次只能送一筆資料的限制。（這裡換個口味，數數數量～）

CREATE OR REPLACE FUNCTION pivot_cnt(col_for_pivot_row varchar[], 
                                     col_for_pivot_col varchar[], 
                                     cnt_col varchar[]) 
returns void AS $$ 

library(reshape2) 

pivot_table <- data.frame(col_for_pivot_row,col_for_pivot_col,cnt_col)
names(pivot_table) <- c("v1", "v2", "v3")    # Rename elements 
 
wide_table <- dcast(pivot_table, pivot_table$v1~pivot_table$v2, 
                    value.var = "v3", fun.aggregate = length) 
write.table(wide_table, file = "/tmp/pivotcnt.csv",row.names=FALSE, na="",col.names=TRUE, sep=",")

$$ LANGUAGE 'plr';

edb=# select pivot_cnt( array_agg(deptno::varchar), 
                  array_agg(job), 
                  array_agg(job) ) 
from emp limit 10;
 pivot_cnt 
-----------
 
(1 row)

edb=# \! cat /tmp/pivotcnt.csv 
"pivot_table$v1","ANALYST","CLERK","MANAGER","PRESIDENT","SALESMAN"
"10",0,1,1,1,0
"20",2,2,1,0,0
"30",0,1,1,0,4

以上方式的缺點是，大量資料會卡在 array_agg() 處理；而且在 PGSQL 中，單個欄位的資料大小限制為 1GB，這限制了 array 的資料上限。因此只能是差強人意。

最後感想，或許在資料庫主機的 R 環境上，安裝 PivotalR，然後在 PL/R 上呼叫 PivotalR 使用才是正解？？

參考資料

好像是少數幾篇 PL/R 的參考資料