JupySQL 與 DuckDB 整合：資料科學效率提升

過去資料科學家常使用 Jupyter Notebook 從資料函式庫或檔案提取資料，再載入 pandas DataFrame 進行分析。這種方式雖然常見，但效率上存在瓶頸。直接查詢資料函式庫伺服器可能影響其效能，尤其當伺服器未針對分析工作負載最佳化時。此外，將資料載入 DataFrame 佔用大量記憶體和計算資源，即使僅需視覺化部分資料，也需載入整個資料集。Matplotlib 等視覺化工具也會消耗大量記憶體，因其需維護圖表、軸線、文字等圖形元素，並處理繪圖資料陣列。雖然 Power BI、Tableau 等工具簡化了資料載入和視覺化，但 pandas 的內建繪圖功能仍是 EDA 期間快速進行內嵌視覺化的利器，可直接從 DataFrame 或 Series 產生圖表，方便視覺化資料分佈、趨勢和模式。為提升效率，理想做法是將資料處理解除安裝到高效能的客戶端，再將結果傳回用於視覺化。JupySQL 正是根據此理念設計，它允許在 Notebook 中以 SQL 儲存格形式操作資料，並透過 DuckDB 引擎連線到資料函式庫或檔案，直接將查詢結果用於視覺化，避免將大量資料載入 DataFrame，從而節省資源。

DuckDB 與 JSON 的深度整合：玄貓（BlackCat）的實戰經驗分享

在資料分析的領域中，JSON (JavaScript Object Notation) 格式因其輕量級和易於閱讀的特性，成為資料交換的標準格式。DuckDB 作為一個高效能的嵌入式資料函式庫，提供了多種方式來讀取和寫入 JSON 檔案。玄貓（BlackCat）將分享在實際專案中使用 DuckDB 處理 JSON 檔案的經驗，並探討各種方法的優缺點。

駕馭 JSON 資料：DuckDB 的 `read_json_auto()` 函式

read_json_auto() 函式是 DuckDB 讀取 JSON 檔案最直接的方式。它能夠自動偵測 JSON 檔案的結構，並將其轉換為 DuckDB 的表格。

以下是一個範例：

import duckdb

conn = duckdb.connect()

query = """
SELECT * FROM read_json_auto('data.json')
"""

df = conn.execute(query).df()
print(df)

內容解密

引入 DuckDB 函式庫：首先，我們使用 import duckdb 引入 DuckDB 函式庫，以便在 Python 中使用 DuckDB 的功能。
建立連線：conn = duckdb.connect() 建立與 DuckDB 資料函式庫的連線。如果資料函式庫不存在，DuckDB 會自動建立一個新的資料函式庫。
建構 SQL 查詢：
- 使用 SELECT * FROM read_json_auto('data.json') 從名為 data.json 的 JSON 檔案中讀取所有資料。
- read_json_auto() 函式會自動偵測 JSON 檔案的結構，並將其轉換為 DuckDB 的表格。
執行查詢並取得結果：
- conn.execute(query) 執行 SQL 查詢。
- .df() 將查詢結果轉換為 Pandas DataFrame，方便後續處理和分析。
顯示結果：print(df) 顯示從 JSON 檔案讀取並轉換為 Pandas DataFrame 的資料。

read_json_auto() 函式的優點是使用簡單，能夠快速讀取 JSON 檔案。然而，它也有一些限制。例如，當 JSON 檔案結構複雜或不一致時，read_json_auto() 可能無法正確解析。

精準控制：使用 `read_json()` 函式讀取 JSON

當 read_json_auto() 無法滿足需求時，read_json() 函式提供了更精確的控制。read_json() 函式允許您指定 JSON 檔案的結構，並定義如何將其轉換為 DuckDB 的表格。

以下是一個範例：

import duckdb

conn = duckdb.connect()

query = """
SELECT * FROM read_json('data.json', format = 'json', columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'value': 'DOUBLE'})
"""

df = conn.execute(query).df()
print(df)

內容解密

引入 DuckDB 函式庫：首先，我們使用 import duckdb 引入 DuckDB 函式庫，以便在 Python 中使用 DuckDB 的功能。
建立連線：conn = duckdb.connect() 建立與 DuckDB 資料函式庫的連線。如果資料函式庫不存在，DuckDB 會自動建立一個新的資料函式庫。
建構 SQL 查詢：
- 使用 SELECT * FROM read_json('data.json', format = 'json', columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'value': 'DOUBLE'}) 從名為 data.json 的 JSON 檔案中讀取所有資料。
- format = 'json' 指設定檔案格式為 JSON。
- columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'value': 'DOUBLE'} 定義表格的欄位名稱和資料型別。
執行查詢並取得結果：
- conn.execute(query) 執行 SQL 查詢。
- .df() 將查詢結果轉換為 Pandas DataFrame，方便後續處理和分析。
顯示結果：print(df) 顯示從 JSON 檔案讀取並轉換為 Pandas DataFrame 的資料。

在這個範例中，我們使用 columns 引數來定義表格的欄位名稱和資料型別。這樣可以確保 DuckDB 正確解析 JSON 檔案，並將其轉換為具有正確結構的表格。

大量資料的救星：使用 `COPY-FROM` 語法匯入 JSON

當處理大型 JSON 檔案時，read_json_auto() 和 read_json() 函式可能會遇到效能瓶頸。這時，COPY-FROM 語法就派上用場了。COPY-FROM 語法允許您直接將 JSON 檔案匯入到 DuckDB 的表格中，而無需將整個檔案讀取到記憶體中。

以下是一個範例：

import duckdb

conn = duckdb.connect()

conn.execute("""
CREATE TABLE my_table (
    id INTEGER,
    name VARCHAR,
    value DOUBLE
);
""")

conn.execute("""
COPY my_table FROM 'data.json' (FORMAT JSON);
""")

query = """
SELECT * FROM my_table
"""

df = conn.execute(query).df()
print(df)

內容解密

引入 DuckDB 函式庫：首先，我們使用 import duckdb 引入 DuckDB 函式庫，以便在 Python 中使用 DuckDB 的功能。
建立連線：conn = duckdb.connect() 建立與 DuckDB 資料函式庫的連線。如果資料函式庫不存在，DuckDB 會自動建立一個新的資料函式庫。
建立表格：
- 使用 CREATE TABLE my_table (id INTEGER, name VARCHAR, value DOUBLE); 建立一個名為 my_table 的表格。
- 表格包含三個欄位：id (INTEGER 型別), name (VARCHAR 型別), 和 value (DOUBLE 型別)。
從 JSON 檔案匯入資料：
- 使用 COPY my_table FROM 'data.json' (FORMAT JSON); 從名為 data.json 的 JSON 檔案中匯入資料到 my_table 表格。
- (FORMAT JSON) 指設定檔案格式為 JSON。
建構 SQL 查詢：使用 SELECT * FROM my_table 從 my_table 表格中選取所有資料。
執行查詢並取得結果：
- conn.execute(query) 執行 SQL 查詢。
- .df() 將查詢結果轉換為 Pandas DataFrame，方便後續處理和分析。
顯示結果：print(df) 顯示從 JSON 檔案匯入並轉換為 Pandas DataFrame 的資料。

COPY-FROM 語法的優點是效能高，能夠處理大型 JSON 檔案。然而，它需要您事先建立表格，並定義表格的結構。

將 DuckDB 表格匯出為 JSON：`COPY-TO` 語法

除了讀取 JSON 檔案，DuckDB 也支援將表格匯出為 JSON 檔案。這可以使用 COPY-TO 語法來完成。

以下是一個範例：

import duckdb

conn = duckdb.connect()

conn.execute("""
CREATE TABLE my_table AS SELECT 1 AS id, 'test' AS name, 1.0 AS value;
""")

conn.execute("""
COPY my_table TO 'output.json' (FORMAT JSON);
""")

內容解密

引入 DuckDB 函式庫：首先，我們使用 import duckdb 引入 DuckDB 函式庫，以便在 Python 中使用 DuckDB 的功能。
建立連線：conn = duckdb.connect() 建立與 DuckDB 資料函式庫的連線。如果資料函式庫不存在，DuckDB 會自動建立一個新的資料函式庫。
建立表格並插入資料：
- 使用 CREATE TABLE my_table AS SELECT 1 AS id, 'test' AS name, 1.0 AS value; 建立一個名為 my_table 的表格，並插入一筆資料。
- 表格包含三個欄位：id (INTEGER 型別), name (VARCHAR 型別), 和 value (DOUBLE 型別)。
將表格資料匯出到 JSON 檔案：
- 使用 COPY my_table TO 'output.json' (FORMAT JSON); 將 my_table 表格的資料匯出到名為 output.json 的 JSON 檔案。
- (FORMAT JSON) 指設定檔案格式為 JSON。

在這個範例中，我們使用 COPY-TO 語法將 my_table 表格匯出為 output.json 檔案。FORMAT JSON 引數指定輸出檔案的格式為 JSON。

玄貓（BlackCat）提醒，DuckDB 預設會將表格匯出為 newline-delimited JSON 格式，其中每一行都是一個 JSON 物件。如果您需要將表格匯出為標準的 JSON 格式，可以使用 ARRAY TRUE 引數。

JupySQL 與 DuckDB：資料科學家的效率利器

身為一個資料科學家，過去我習慣使用 Jupyter Notebook 從資料函式庫或外部檔案（例如 CSV、JSON）中提取資料，然後將其儲存到 pandas DataFrame 中。這種方式雖然直觀，但存在一些效率上的瓶頸。

傳統資料分析的挑戰

傳統的資料分析流程，如圖 7-1 所示，有幾個明顯的缺點：

資料函式庫伺服器效能：直接查詢資料函式庫伺服器可能會降低其效能，特別是當資料函式庫伺服器沒有針對分析工作負載進行最佳化時。
記憶體資源消耗：將資料載入到 DataFrame 會佔用大量的記憶體和計算資源。舉例來說，如果我們只想視覺化資料集中的特定部分，仍然需要將整個資料集載入記憶體。
視覺化工具的限制：使用 Matplotlib 繪製視覺化圖表也會消耗大量記憶體。Matplotlib 在後端維護了各種物件，例如圖表、軸線、文字和其他圖形元素，這些元素都會消耗資源。此外，Matplotlib 還會處理用於繪圖的資料陣列，並將其暫時儲存在記憶體中。

雖然 Power BI、Domo、Tableau 和 Excel 等工具簡化了資料載入和視覺化的過程，但 pandas 仍然是 EDA 期間快速進行內嵌視覺化的絕佳選擇，這歸功於其內建的繪圖功能。使用者只需付出極小的努力，就可以直接從 pandas DataFrame 或 Series 產生基本的視覺化圖表，例如折線圖、長條圖、直方圖和散佈圖。這使得在進行資料操作時，能夠方便地視覺化資料分佈、趨勢和模式，而無需切換到其他函式庫或執行額外的步驟。

JupySQL：提升資料處理效率的新選擇

為了提高效能，理想的做法是將資料處理（所有資料整理和篩選）解除安裝到能夠有效執行資料分析的客戶端，然後將結果傳回以用於視覺化。這就是本篇文章要探討的主題 —— JupySQL。

雖然 DuckDB 也允許你使用 SQL 直接存取資料集，但使用 JupySQL 可以在 Notebook 中以 SQL 儲存格的形式完成操作。

什麼是 JupySQL？

JupySQL 是一個 Jupyter Notebook 的 SQL 客戶端，使你能夠使用 SQL 直接在 Jupyter Notebook 中存取資料集。JupySQL 的主要思想是在 Jupyter Notebook 中執行 SQL，因此得名。JupySQL 是 ipython-sql 的一個分支，它將 SQL 儲存格新增到 Jupyter。它目前由 Ploomber 團隊積極維護和增強。

JupySQL 使你能夠使用 SQL 查詢資料集，而無需維護 DataFrame 來儲存資料集。例如，你可以使用 JupySQL 透過 DuckDB 引擎連線到資料函式庫伺服器（例如 MySQL 或 PostgreSQL）或 CSV 檔案。然後，可以直接將查詢結果用於視覺化。圖 7-2 顯示了 JupySQL 的工作方式。

JupySQL 的運作方式

JupySQL 的核心概念是將資料處理流程解除安裝到 DuckDB 引擎，然後直接在 Jupyter Notebook 中使用 SQL 語法進行查詢和分析。這樣可以避免將大量資料載入到 pandas DataFrame 中，從而節省記憶體和計算資源。

圖 7-2 說明瞭 JupySQL 如何與 DuckDB 協同工作，以查詢各種資料來源並將其用於資料視覺化。

安裝 JupySQL

要在 Jupyter Notebook 中使用 JupySQL，你需要安裝 jupysql 套件。此外，為了在 JupySQL 中使用 DuckDB，你還需要安裝 duckdb-engine 套件。以下陳述式會同時安裝這兩個套件：

!pip install jupysql duckdb-engine

duckdb-engine 套件是 DuckDB 的 SQLAlchemy 驅動程式。SQLAlchemy 是一個開放原始碼 Python 函式庫，用於處理關係資料函式庫。它提供了一組高階和低階工具，用於以彈性和有效的方式與資料函式庫互動。你還應該安裝 SQLAlchemy 函式庫：

!pip install SQLAlchemy

你可以在 Jupyter Notebook 中使用以下魔法指令（以 % 符號表示）來使用 JupySQL 與資料來源互動。

| 選項 | 作用 | |

載入 sql 擴充功能

安裝了必要的套件後，讓我們看看如何使用 JupySQL 直接在 Jupyter Notebook 中使用 SQL。

首先，建立一個新的 Jupyter Notebook。接下來，建立一個新的儲存格，並使用 %load_ext 行魔法指令載入 sql 擴充功能（見圖 7-3）：

%load_ext sql

這裡要注意一個重要事項：不要將註解放在與 %load_ext 魔法指令相同的行上。這會在你的 SQL 陳述式上造成錯誤。例如，假設你想在前述陳述式上加上註解，例如：

%load_ext sql # 載入 sql 擴充功能

這會嘗試載入名為 sql # 載入 sql 擴充功能 的模組，從而導致錯誤。若要新增註解，請將其放在單獨的行上：

# 載入 sql 擴充功能
%load_ext sql

與 DuckDB 整合

載入 sql 擴充功能後，你需要載入一個資料函式庫引擎，你可以在其中使用它來處理你的資料。對於本文，你將使用 DuckDB。以下陳述式會啟動一個 DuckDB 記憶體資料函式庫：

%sql duckdb://

此陳述式使用 SQLAlchemy 樣式的連線字串來連線到資料函式庫引擎 — 在本例中為 DuckDB 引擎。如果你想使用永續性 DuckDB 資料函式庫，請指設定檔案名稱，如下所示：

%sql duckdb:///MyDB.db

JupySQL 通常會為你管理連線。當你使用 %sql 執行 SQL 命令時，它會開啟連線、執行命令，並在執行後自動關閉連線。

如果你需要 JupySQL 方面的協助，你可以隨時使用 %sql? 行魔法指令來顯示檔案字串（見圖 7-4）：

%sql?

執行查詢

讓我們先使用 Jupyter Notebook 中的 %sql 魔法指令來對 CSV 檔案 airlines.csv 執行查詢：

%sql SELECT * FROM airlines.csv

玄貓認為 JupySQL 是一個強大的工具，可以簡化資料科學工作流程，並提高資料處理效率。透過將資料處理流程解除安裝到 DuckDB 引擎，並直接在 Jupyter Notebook 中使用 SQL 語法進行查詢和分析，我們可以節省記憶體和計算資源，並更有效地進行資料分析。

玄貓 BlackCat

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

JupySQL 與 DuckDB 整合：資料科學效率提升

DuckDB 與 JSON 的深度整合：玄貓（BlackCat）的實戰經驗分享

駕馭 JSON 資料：DuckDB 的 read_json_auto() 函式

精準控制：使用 read_json() 函式讀取 JSON

大量資料的救星：使用 COPY-FROM 語法匯入 JSON

將 DuckDB 表格匯出為 JSON：COPY-TO 語法

JupySQL 與 DuckDB：資料科學家的效率利器

傳統資料分析的挑戰

JupySQL：提升資料處理效率的新選擇

什麼是 JupySQL？

JupySQL 的運作方式

安裝 JupySQL

| 選項 | 作用 | |

載入 sql 擴充功能

與 DuckDB 整合

執行查詢

玄貓 BlackCat

駕馭 JSON 資料：DuckDB 的 `read_json_auto()` 函式

精準控制：使用 `read_json()` 函式讀取 JSON

大量資料的救星：使用 `COPY-FROM` 語法匯入 JSON

將 DuckDB 表格匯出為 JSON：`COPY-TO` 語法