Leo Yeh's Blog

SAS 資料管理 (19)

教學目標

初步了解如何透過 FedSQL 搭配 SAS Data Studio 網站應用程式透過傳遞 SQL 查詢的方式準備資料的基本概念。

重點概念

首先在 SAS Cloud Analytic Services (CAS) 中 FedSQL 主要是以 ANSI SQL:1999 核心標準進行實作,其主要提供產業標準資料類型,同時提供了一種可擴展且高效能方式來查詢資料,並且從現有資料表中建立新的 CAS 資料館中的資料表,以及讓我們能夠使用產業標準的 SQL 查詢表達式來連接資料, FedSQL 可用於在單個請求中合併來自多個資料來源的關聯資料。此外對於應用程式 FedSQL 能夠在所有資料來源之間提供通用的 SQL 語法,將能夠存取各種資料來源中的資料,而不必在特定於資料來源中提交查詢,另外單個 FedSQL 查詢可以將多個資料來源中的資料作為目標,並且返回單個結果資料集,我們能夠透過以下幾種方式將 FedSQL 敘述提交至 CAS 伺服器執行,主要有兩種方式,分別為:

  1. 使用 PROC FEDSQL 程序:PROC FEDSQL 程序能夠在 SAS 函式庫和 CAS 伺服器上執行 FedSQL 敘述。
  2. 使用 fedSql.execDirect 動作:fedSql.execDirect 動作能夠從 CASL、Python 或 R 程序中使用 fedSql.execDirect 動作。

其中當使用 fedSql.execDirect 動作時 FedSQL 敘述需要帶引號的方式提交給 CAS 伺服器執行,但是如果使用 PROC FEDSQL 程序時 FedSQL 敘述不需要帶引號的方式提交給 CAS 伺服器執行,至於 CAS 伺服器目前支援的 FedSQL 敘述主要有 CREATE TABLE、DROP TABLE 和 SELECT 查詢敘述,預設情況下,資料存取為序列 (Serial),當安裝SAS Data Connect Accelerator 軟體之後,就能夠針對 Teradata 和 Hadoop 的資料存取為平行 (Parallel),而當在 FedSQL 敘述中使用 CAS 資料館參考資料表名稱時,資料連接器將會提供對 CAS 資料館中指定資料表的單次按需需載入存取,同時在使用 fedSql.execDirect 動作處理之前,也能夠將資料載入至 CAS 工作階段中。

1
2
3
proc fedsql sessref=mysess;
[FedSQL 敘述];
quit;
1
2
3
4
proc cas;
fedsql.execdirect
query="[FedSQL 敘述]";
quit;

接著 FedSQL 的隱含傳遞 (Implicit pass-through) 功能主要是將 SQL 查詢程式碼轉換為等效用於資料來源的 SQL 程式碼的過程,將其直接傳遞給資料來來源進行處理,以利縮短了查詢回應時間,並且增強了安全性。隱含傳遞主要提供了兩大效能優勢,分別為:

  1. 減少查詢 SQL 敘述的資料傳輸量,以利減少了整體查詢處理時間。
  2. 使用特定於資料來源的功能產生結果,以利使用資料來源查詢進階功能。

此外隱含傳遞之安全優勢在於能夠在資料來源中處理查詢的每個部分,這樣就無需將其可能包含敏感資訊的關聯資料表傳輸到 FedSQL 端進行查詢處理,此外 CAS 中的FedSQL 提供了單個資料來源完整查詢的隱式傳遞,當請求正在存取單個資料來源時,將會嘗試將整個查詢隱式傳遞給資料來源,如果無法將完整查詢傳遞給資料來源,則會在 CAS 伺服器上本地處理該請求,目前支援隱式傳遞的資料來源主要有 Google BigQuery、Amazon Redshift、Apache Spark、Impala、Hadoop (Hive)、JDBC、ODBC、Oracle、DB2、PostgreSQL、SAP Hana、Teradata、… 等,請注意 FedSQL 請求中指定的所有資料表必須存在於相同 CAS 資料館中,而存在於不同 CAS 資料館中的卸載資料表的合併和聯接皆會自動載入至 CAS 資料館中進行處理。

再來 FedSQL 的顯式傳遞 (Explicit Pass-Through) 功能主要是連接到資料來源,並且將 SQL 語句直接發送到該資料來源執行,此功能能夠讓我們直接使用資料來源的 SQL 查詢語法,而不管其是否符合 SQL 的 ANSI 標準。主要透過「FROM CONNECTION TO caslib (native-syntax) [[AS] alias]」語法在 SELECT 敘述的 FROM 子句中使用 CONNECTION TO 能夠提交產生成結果資料集的本機 SQL 查詢敘述。此外當我們使用 SAS Data Studio 建立資料計劃時,則必須將資料先載入至 CAS 伺服器的記憶體中,才能夠進行處理,這時若是資料來源有幾百 GB 的資料將會無法在短時間內建立資料計劃,所以為了解決此問題,則我們能夠先以任何一個已經載入至 CAS 伺服器的資料表來建立新的資料計劃,接著增加「自訂轉換」,點選「CASL」,輸入以下 CASL 程式碼,並且修改「CAS 資料館名稱」和「SQL 查詢敘述」,按下「執行」鈕,就能夠以顯式傳遞的方式提交產生成結果資料集的本機 SQL 查詢敘述至資料來源中進行 SQL 查詢操作,特別是針對多個大數據資料表進行聯結的操作,可以先在資料來源處理完成之後,再將結果回傳為資料表,並且載入至 CAS 伺服器的記憶體中, 以利後續透過 SAS Data Studio 網站操作界面建立多個步驟的 ETL 排程工作。

1
2
3
4
5
6
loadActionSet 'fedSql';
queryCode='create table "'||_dp_outputCaslib||'"."'||_dp_outputTable||'" {options replace=true}
AS SELECT TBL.*FROM CONNECTION TO [CAS 資料館名稱]
([SQL 查詢敘述]) AS TBL';
print queryCode;
ExecDirect / query=queryCode;

最後 FedSQL 敘述提交至 CAS 伺服器時,將會建立 FedSQL 查詢計劃主要使用 FedSQL 查詢優化器和 FedSQL 傳遞功能來計劃和執行查詢,其使用 CAS 伺服器針對資料進行分區和排序,FedSQL 查詢計劃分為多個階段,像是 SeqScan、HashJoin、MergeJoin、NestLoop、Sort、Group、Aggregate、Unique、Limit 和 Result,每個階段都需要一個獨立的 SQL 查詢敘述。此外若要查看給定 FedSQL 請求的查詢計劃,請設定「method」選項,以利針對給定請求生成查詢計劃中節點和階段的文本描述,並將輸出寫入 SAS 日誌中,更多資訊請參考官方文件

1
2
3
proc fedsql sessref=mysess _method;
[FedSQL 敘述];
quit;
1
2
3
4
5
proc cas;
fedsql.execdirect
method=true
query="[FedSQL 敘述]";
quit;

總結我們將能夠透過 FedSQL 搭配 SAS Data Studio 網站應用程式透過傳遞 SQL 查詢 (SQL Pass-Through) 的方式準備來自於資料來源中的大數據,並且直接透過 SAS Data Studio 網站應用程式操作界面建立多個步驟的 ETL 排程工作。

相關資源

⬅️ Go back