資料分析指北之 KNIME 模組：Data Access 型別模組之二 Database 模組集合

資料庫資料分析 · 發表 2018-11-25 08:44:38

摘要：歡迎關注公眾號資料分析指北感覺有用？幫忙轉發？謝謝！資料分析指北 - KNIME 模組(Data Access 型別模組之二 Database 模組集合) 歷史回看： -基礎(Data Access型別模組之一IO模組集合) -泰坦尼克...

歡迎關注公眾號資料分析指北

感覺有用？幫忙轉發？謝謝！

資料分析指北 - KNIME 模組( Data Access 型別模組之二 Database 模組集合)

歷史回看：

- ofollow,noindex" target="_blank">基礎(Data Access型別模組之一IO模組集合)

- 泰坦尼克沉船資料分析之二)

- 附錄五執行 KNIME workflow 時出現問題怎麼辦?

Photo by Tobias Fischer on Unsplash

微信公眾號：資料分析指北

Data Access 型別模組(繼續…)

Connector 部分
KNIME 從資料庫獲取資料的兩種模式
Read/Write部分
Manipulation 部分
Utility 部分

上一節我們講了 KNIME 中 Data Access 型別模組中的一大類–IO模組集合，並給出了對應於各模組的學習建議。這節我們講 DATA Access 型別模組中的 Database 模組集合。

Data Access 型別模組(繼續…)

Database 模組集合簡述

注：以下 Database 模組均是 KNIME 3.6.2 中的模組，KNIME 官方正在開發新一套的資料庫相關模組，支援流相關操作，可能會在不久後釋出，名稱類似 DB xxxx，請注意區別。

Node Repository中的Database模組集合

Database型別的模組主要分為 Read/Write、Connector、Manipulation 和 Utility 四個部分。按照邏輯來講，如果我們只談純粹Data Access型別的模組，似乎講其中的 Read 部分就夠了，但實際上，因為 Database 模組的非獨立性（即它不像上面所說的檔案讀寫模組型別，單獨就可以執行），除了 Write 部分以外，其餘部分可以均成為“Data Access”這個型別的組成成分，所以也將其歸類在這裡。我們先從 Connector 部分開始講起。

Connector 部分

在基礎(基礎資料操作之二，讀取資料來源) 一篇中，我們描述了 KNIME 是如何通過 JDBC 驅動去連線各種各樣型別的資料庫。對於一些基本的關係型資料庫，SQLite、MySQL、MSSQL、PostgresSQL等，KNIME 已經內建了相應的 JDBC 驅動，我們需要做的只是選擇特定的connector，然後配置資料庫地址、埠、使用者名稱以及密碼之類的資訊就可以連線了。對於 KNIME 中沒有內建驅動的資料庫，我們需要做的是先在相應資料庫的官網或社群下載到對應的 JDBC 驅動，並在 KNIME 中配置，最後使用 Database Connector 這個通用模組連線資料庫。可參照附錄四如何配置 KNIME 訪問 MS Access 中關於 MS Access DB 的連線學習 KNIME 不預設支援的資料庫如何訪問。

Database模組集合中的Connector部分

[重點：:sparkles::sparkles::sparkles:；難度：:sparkles:]Database Connector模組。這是一個通用型的模組，在使用時，需要指定相應的 JDBC 驅動，並且配置相應資料庫的URL以及其他相關資訊。在其他 KNIME 資料庫 connector 模組不能滿足你要求的情況下，它將是你的唯一選擇。甚至在這個模組當中，你也可以配置其驅動，使其變成一個MySQL Connector。如下圖所示，在Database driver選擇了 MySQL 的驅動，然後在 URL 位置按照格式寫下MySQL的連線地址和埠就可以了。如果你的資料中有時間相關的欄位，那麼在整個配置中，TimeZone 需要特別注意一下。

經過特定配置後可以讀取 MySQL 的 Database Connector

[重點：:sparkles::sparkles:；難度：:sparkles:]MySQL Connector 模組。這裡不用像上面的模組根據格式列寫資料庫 URL，而只需要按照格式填寫資料庫相應配置內容就可以了。其實內部的組成和上面的 Database Connector 組成一致。對於學過計算機語言的人來說，這其實就是面對物件程式設計（OOP）中的概念，Database Connector 對應了基類，而 MySQL Connector 只是在基類上做了繼承的子類。這個模組的輸出和上面 Database Connector 的輸出都是 a database JDBC connnection ，可以理解為類的一個例項。對於沒有系統學過OOP的人，只需要把輸出理解為資料庫的連線就可以了。此模組輸出埠在圖形上顯示的是一個紅色的實心框框。

MySQL Connector的配置介面

而其餘的 Connector 和 MySQL Connector 一樣，都是繼承自 Database Connector，不再贅述。

KNIME 從資料庫獲取資料的兩種模式

[重點：:sparkles::sparkles::sparkles:；難度：:sparkles::sparkles:]在繼續講模組之前，先介紹一下（Big Picture）資料庫讀取的兩種模式：一種是將資料從資料庫中取出後，利用 KNIME 的模組進行分析；另外一種方式是，在資料庫中就做一些預處理，然後再把接下來的資料放在 KNIME 中處理。

比如我們有一個叫做 titanic 的 sqlite 資料庫（具體資料內容請參照泰坦尼克沉船資料分析之一) 中的介紹）。我們現在想了解一下整個資料中男性和女性的數量是多少，按照上面介紹的第一種方法，我們做出如下的 workflow:

將資料從資料庫中讀出再做處理

其中 Database reader 的配置如下：

配置 Database reader

group by 模組配置很簡單，不做贅述，最後得到如下結果：

group by 模組結果

如果按照上面所說的第二種方法，那麼我們刪除 Group by 模組，重新配置 database reader 模組：

第二種方法配置 Database reader

第一種方法是將計算的過程放在了 KNIME 中，而第二種方法是把計算的過程放在了資料庫中（這種方式稱為 in-database processing ）。這兩種方法究竟哪一種合適，需要根據具體情況具體分析，有時你的資料庫硬體配置比較強，那麼放在資料庫中計算能夠顯著加快你構建模型、除錯的速度；有時你的 SQL 語句太複雜，那麼也許將計算的過程放在 KNIME 中是比較適合。需要你根據具體情況 trade off。

另外，在KNIME中，第二種方法有另外一種寫法，就是利用 KNIME database 相關的 Manipulation 模組，比如對於前面的問題來說，可以這樣構建 workflow：

第二種方法的另外一種形式

注意和上面的區別，其中的 database table selector 模組，只是讀取了 titanic 資料（這個過程是發生在資料庫層面的），然後在資料庫層面做了 group by 的操作（ Database GroupBy 和 KNIME中的 GroupBy 大同小異），最後利用 Database Connection Table Reader 模組（注意，這不是上面的 Database Reader模組）將資料從資料庫中讀取到 KNIME 中。

Database Manipulation 模組其實只是把第二種方法複雜的 SQL 語句變成了圖形化的方案，你可以通過觀察 Database GroupBy 的輸出對這種方法的原始實現有一個認識。Database GroupBy 節點的輸出：

Database Driver:
org.sqlite.JDBC

Database URL: jdbc:sqlite:/Users/HaveF/Desktop/knime-workspace/practicalDA/07DatabaseExample1/db.sqlite

User Name:

Database Type:
sqlite

SQL Statement: SELECT Sex, COUNT(table_1189361459.Survived) AS Survived FROM (SELECT * FROM titanic) table_1189361459 GROUP BY Sex

可以通過上面的 SQL Statement 看出，這裡和第二種方法類似，構建了 SQL 語句，但這種方法最大的好處在於，方便除錯複雜的 SQL 語句，具體可以參照 database 相關節點輸出中的 table preview 來理解，在此不做細講。

如果想實驗整個過程，可以利用我已經構建好的 workflow 做實驗。

關注公眾號：資料分析指北，回覆“作業”，即可獲得下載連結。

Read/Write部分

如果已經搞明白上面兩種獲取資料庫資料的方法，那麼下面的內容就相對來說很簡單了。我們快速瀏覽一下：

Database模組集合中的Read/Write部分

[重點：:sparkles::sparkles::sparkles:；難度：:sparkles:]Database Reader 在上面已經使用過，配置項很少，只需要把sql語句填進去就可以了。

[重點：:sparkles::sparkles::sparkles:；難度：:sparkles:]Database Writer 只需要填好需要寫的表名，並配置好資料的 SQL type 。

Database Writer配置一瞥

[重點：:sparkles::sparkles:；難度：:sparkles::sparkles:]Database Table Selector 在上面也已經使用過，這是一個 in-database processing 的模組，這個模組的輸出其實是一個sql語句，後面只能接其他 in-database processing 的模組，這些型別的模組的終點只能是database connection table reader 或 database connection table writer，通過這兩個節點，要麼把在資料庫中內部處理的資料輸出到 KNIME 中，要麼把處理的結果再重新寫回到資料庫中。

[重點：:sparkles:；難度：:sparkles::sparkles:]Database Table Connector其實是 Database connector 和 Database Table Selector兩個模組組合的一個快捷方式。

[重點：:sparkles::sparkles::sparkles:；難度：:sparkles::sparkles:]Database Update、Delete 模組都是類似Writer的標準模組。需要注意的是，Update 模組不能夠插入資料庫中沒有的條目，它只是一個更新（update）模組。如果你有一些資料想更新到資料庫中，那麼你需要先把這部分資料根據條件（比如主鍵）分成資料庫中有的資料和資料庫中沒有的資料，將資料庫中有的資料通過 Update 更新，而將資料庫中沒有的資料通過 Writer 模組寫入。在將來的KNIME資料庫模組中，會有名為DB merge的模組，一個節點統一處理上述情況。

[重點：:sparkles:；難度：:sparkles::sparkles::sparkles:]Database Looping 和 Parameterized Database Query 主要就是一些引數化的查詢。比如可以通過 Looping 模組來查詢一系列類似條件的資料。而後一個模組在查詢時可以加入一些動態引數，比如下面的例子中，就是將動態資料（例子中是以 web 服務獲取到JSON資料做為臨時資料）放在查詢語句中，查詢出結果並返回。

Parameterized Database Query的一個例子

Manipulation 部分

Manipulation部分的模組，主要是配合上面說的 in-database processing 的過程，如下所示：

Database 模組集合中的 Manipulation 部分

[重點：:sparkles::sparkles:；難度：:sparkles::sparkles:]可以看到，模組和 KNIME column/ row 相關的 Manipulation 類似，只不過這些操作是在資料庫中進行的。這樣也帶來了另外一個問題 – 這些模組有一些功能其實比較依賴於資料庫本身，就拿 Group By 模組來說，KNIME 自身的 Group By 模組有很多的 Group Functions 或 Aggregate Functions 聚合函式（參照基礎( 基礎資料操作之四，從SQL的case，group by，join 到工具KNIME ) ），但這些 Functions 在 Database GroupBy 中就只有少部分能支援了。

對於資料庫操作能力比較強的小夥伴來說，也許感覺 Manipulation 部分比較雞肋，但根據個人經驗來說，如果你寫的 SQL 百行以上時，Debug 就相對來說比較困難了，這種時候，也許這些模組能夠幫你忙。

Utility 部分

Utility 部分內容比較少，Drop Table 含義一望即知；SQL executor 是 in-database processing 中的一個可以自定義sql語句的模組；而 SQL inject、Extract 是在 in-database processing 中操作中間生成 SQL 語句的模組，相對來說用的較少。

Database 模組集合中的 Utility 部分

至此，基礎的關係型資料庫相關的 Data Access 型別模組就介紹完畢了。

回頭聊