JupySQL：在 Jupyter 中輕鬆駕馭 SQL 資料函式庫

– 8 分鐘閱讀

JupySQL 是一個強大的工具，它將 SQL 的能力帶入 Jupyter Notebook，讓資料科學家能更有效率地處理資料。以往在 Python 環境中使用 SQL 需要繁瑣的設定和程式碼，JupySQL 則簡化了這個過程，讓使用者能像操作原生 SQL 工具一樣在 Jupyter 中執行 SQL 查詢。它與 DuckDB 資料函式庫的無縫整合，更進一步提升了查詢效能，讓使用者在處理大量資料時也能保持流暢的體驗。除了基本的 SQL 查詢功能，JupySQL 還支援直接讀取 CSV 檔案，省去資料匯入的步驟。它也提供資料持久化功能，讓使用者可以將資料儲存在 DuckDB 資料函式庫中，方便日後重複使用。此外，JupySQL 還內建了資料表管理工具，方便使用者檢視資料表結構、統計資訊等。對於常用的 SQL 查詢，JupySQL 提供了 Snippets 功能，讓使用者可以儲存和重複使用這些查詢，避免重複撰寫程式碼。更令人驚喜的是，JupySQL 還整合了資料視覺化功能，可以直接在 Jupyter Notebook 中繪製直方圖、盒鬚圖、圓餅圖和長條圖等，讓資料分析結果更直觀易懂。

JupySQL：資料科學家的SQL超能力解鎖

身為玄貓（BlackCat），在資料科學領域打滾多年，我深知SQL的重要性。但傳統SQL工具往往與Python生態系整合不佳，造成資料分析流程的斷裂。最近，我發現JupySQL這個神奇的工具，它完美地將SQL帶入Jupyter Notebook，讓資料科學家也能像我一樣，輕鬆駕馭SQL的力量。

DuckDB與JupySQL：天作之合

JupySQL與DuckDB的結合，簡直是天作之合。DuckDB的高效能和JupySQL的無縫整合，讓我在Jupyter Notebook中也能享受到極速的SQL體驗。

首先，我們需要安裝JupySQL：

pip install jupysql

接著，載入SQL Magic：

%load_ext sql

現在，我們可以連線到DuckDB資料函式庫。這裡我們使用一個名為MyDB.db的檔案型資料函式庫：

%sql duckdb:///MyDB.db

CSV檔案輕鬆讀取：告別繁瑣的Pandas

過去，讀取CSV檔案往往需要Pandas的協助。但有了JupySQL，一切變得簡單多了。

假設我們有一個名為airlines.csv的CSV檔案，可以直接用SQL讀取：

%sql SELECT * FROM 'airlines.csv'

JupySQL會將CSV檔案載入到DuckDB中，結果會以sql.run.ResultSet物件呈現。如果需要，你也可以將結果轉換為Pandas DataFrame：

rs = %sql SELECT * FROM 'airlines.csv'
df = rs.DataFrame() # 轉換為 pandas DataFrame

預設情況下，JupySQL只會顯示前10列結果。若要顯示所有列，可以使用以下設定：

%config SqlMagic.displaylimit = None # 或設定為 0

對於多行SQL查詢，可以使用%%sql cell magic command：

%%sql
SELECT
    count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count

資料持久化：CREATE TABLE指令

上述查詢雖然可以載入CSV檔案，但資料並不會永久儲存在DuckDB資料函式庫中。若要將資料持久化，可以使用CREATE TABLE指令：

%%sql
CREATE TABLE airlines
as
FROM 'airlines.csv'

現在，airlines.csv的內容已經儲存在名為airlines的資料表中（位於MyDB.db檔案中）。

若要檢視資料表內容，可以使用以下指令：

%sql SELECT * FROM airlines

使用持久化資料函式庫時，資料表會自動儲存。下次開啟資料函式庫時，資料表依然存在。但對於記憶體中的DuckDB資料函式庫，重新啟動Jupyter kernel後，所有資料表都會遺失。

資料表管理：%sqlcmd指令

JupySQL提供了一系列方便的指令來管理DuckDB資料函式庫。

  • 檢視資料表：
%sqlcmd tables
  • 檢視資料表結構：
%sqlcmd columns -t airlines
  • 產生資料表統計資訊：
%sqlcmd profile -t airlines

Snippets：儲存與重複使用SQL查詢

除了將查詢結果儲存為資料表，JupySQL還允許我們儲存SQL查詢（稱為snippets），以便日後重複使用。

以下範例儲存一個snippet，並命名為state_count：

%%sql --save state_count
SELECT
    count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count DESC
LIMIT 10

要再次執行這個snippet，只需使用snippet名稱：

%sql SELECT * FROM state_count

若不想在定義snippet時立即執行查詢，可以使用--no-execute選項：

%%sql --save state_count --no-execute
SELECT
    count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count

資料視覺化：%sqlplot指令

JupySQL不僅可以執行SQL查詢，還能直接在Jupyter Notebook中進行資料視覺化。%sqlplot line magic command讓我們可以輕鬆繪製各種圖表。

直方圖 (Histograms)

首先，確保Matplotlib套件已安裝：

!pip install matplotlib

接著，建立並儲存一個查詢，命名為airports_A：

%%sql --save airports_A --no-execute
SELECT
    *
FROM airports.csv
WHERE state LIKE 'A%'

這個查詢會找出所有州名以"A"開頭的機場（例如：AK, AL, AR, AS, AZ）。--no-execute選項可以避免查詢立即執行。

若要繪製一個直方圖，顯示各州機場數量，可以使用以下指令：

%sqlplot histogram --table airports_A --column STATE
  • --table: 指定用於繪圖的資料表/查詢。
  • --column: 指定用於繪圖的欄位名稱。

其他選項：

  • -s/--schema: 指定schema。若使用預設schema則無需傳遞。
  • -b/--bins: 指定bin的數量。預設為50。
  • -B/--breaks: 指定自訂的bin間隔。
  • -W/--binwidth: 指定每個bin的寬度。
  • -w/--with: 使用先前儲存的查詢作為輸入資料。

更多視覺化範例

  • Boston Housing Dataset：年齡與房價分佈
%%sql --save boston
SELECT
    *
FROM boston.csv

%sqlplot histogram --column age medv --table boston
  • Titanic Dataset：年齡分佈
%%sql --save titanic
SELECT
    *
FROM titanic_train.csv
WHERE age NOT NULL AND embarked NOT NULL

%sqlplot histogram --column age --bins 10 --table titanic

在這個範例中，我們將年齡分為10個bins，從直方圖中可以看出，大多數乘客的年齡都在30歲左右。

探索JupySQL視覺化：從直方圖到長條圖的資料洞察

JupySQL不僅能處理資料函式庫查詢，還能將查詢結果以多種視覺化圖表呈現。玄貓將帶領大家深入瞭解如何使用JupySQL繪製直方圖、盒鬚圖、圓餅圖和長條圖，並透過客製化選項，讓資料更清晰易懂。

使用JupySQL繪製直方圖

直方圖是觀察資料分佈的常用工具。以下是如何使用JupySQL在Titanic資料集中繪製年齡分佈的直方圖：

ax = %sqlplot histogram --column age --bins 10 --table titanic

這段程式碼會產生一個顯示鐵達尼號乘客年齡分佈的直方圖，其中--column age指定要繪製的欄位，--bins 10則將資料分成10個區間。

將圖表指定給變數ax，可以進一步客製化圖表的外觀。例如，可以調整標題、標籤和顏色：

import seaborn

# 設定顏色
palette_color = seaborn.color_palette('pastel')

# 迭代處理每個長條
for i, bar in enumerate(ax.patches):
    bar.set_alpha(0.8)  # 設定透明度
    bar.set_color(palette_color[i])  # 設定顏色
    bar.set_edgecolor('black')  # 設定邊框顏色

ax.set_title("鐵達尼號乘客年齡分佈")
ax.set_xlabel("年齡")

這段程式碼使用Seaborn套件設定直方圖的顏色，並調整標題和軸標籤。其中，ax.patches包含了直方圖中的所有長條，可以透過迭代的方式修改每個長條的屬性。

盒鬚圖：觀察資料分佈與異常值

盒鬚圖（Box Plot）是一種用於顯示資料分佈的圖表，它能總結資料的最小值、第一四分位數（Q1）、中位數（Q2）、第三四分位數（Q3）和最大值，並突出顯示潛在的異常值。盒鬚圖特別適合比較不同群體之間的分佈，快速識別資料的變異性和對稱性。

以下是如何使用JupySQL繪製年齡欄位的盒鬚圖：

%sqlplot boxplot --column age --table titanic

這段程式碼會產生一個顯示鐵達尼號乘客年齡分佈的盒鬚圖。圖中的圓圈代表異常值，即與其他資料點顯著不同的值。

盒鬚圖也支援水平方向的繪製，可以使用--orient h選項：

%sqlplot boxplot --column age --table titanic --orient h

圓餅圖：呈現比例關係

圓餅圖（Pie Chart）是一種用於顯示不同類別之間比例關係的圖表。以下是如何使用JupySQL繪製一個顯示各州機場數量的圓餅圖：

首先，儲存一個查詢，該查詢會計算每個州的機場數量：

%%sql --save airports_by_state
SELECT count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count
DESC LIMIT 5

然後，使用以下程式碼繪製圓餅圖：

%sqlplot pie --table airports_by_state --column STATE count --show-numbers

這段程式碼會產生一個顯示前五個州機場數量的圓餅圖。--show-numbers選項會在每個扇形上顯示百分比。

長條圖：比較不同類別的數值

長條圖（Bar Plot）是一種用於比較不同類別數值的圖表。以下是如何使用JupySQL繪製一個顯示各州機場數量的長條圖：

%sqlplot bar --table airports_by_state --column STATE Count --show-numbers

這段程式碼會產生一個顯示各州機場數量的長條圖。--show-numbers選項會在每個長條上方顯示數值。

與直方圖類別似，長條圖也可以透過ax.patches客製化外觀：

ax = %sqlplot bar --table airports_by_state --column STATE Count --show-numbers
import seaborn

palette_color = seaborn.color_palette('pastel')

# 迭代處理每個長條
for i, bar in enumerate(ax.patches):
    bar.set_alpha(0.8)  # 設定透明度
    bar.set_color(palette_color[i])  # 設定顏色
    bar.set_edgecolor('black')  # 設定邊框顏色

ax.set_title("各州機場數量")
ax.set_xlabel("州")

這段程式碼使用Seaborn套件設定長條圖的顏色，並調整標題和軸標籤。

玄貓結語

透過JupySQL，我們可以輕鬆地將資料函式庫查詢結果轉換為各種視覺化圖表，更深入地瞭解資料的分佈、比例和趨勢。無論是分析鐵達尼號乘客的年齡分佈，還是比較各州的機場數量，JupySQL都能幫助我們從資料中提取有價值的資訊。下次在分析資料時，不妨試試JupySQL的視覺化功能，相信會帶來意想不到的收穫。

玄貓

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。