DuckDB載入查詢StackOverflow資料函式庫

– 9 分鐘閱讀

DuckDB 作為一款高效能的嵌入式分析型資料函式庫，非常適合處理大型資料集，例如 Stack Overflow 的完整資料函式庫。本文將逐步解析如何使用 DuckDB 載入和查詢這個龐大的資料集，並探討相關的效能考量。首先，我們需要從 Internet Archive 下載 Stack Overflow 資料函式庫的壓縮檔案，這些檔案格式為 7z，包含使用者、文章、評論、投票等資訊。由於檔案格式為 XML，我們需要使用工具如 xidel 和 jq 將其轉換為 DuckDB 可讀取的 CSV 格式，再透過 gzip 壓縮以節省儲存空間。接著，我們可以利用 DuckDB 的 CREATE TABLE 陳述式建立資料表，並使用 read_csv 函式將 CSV 檔案匯入 DuckDB。在資料匯入後，我們可以執行各種 SQL 查詢來分析資料，例如統計使用者數量、文章數量、熱門標籤等。此外，DuckDB 提供了 SUMMARIZE 函式，可以快速計算資料的統計資訊，例如最大值、最小值、平均值等，方便我們快速瞭解資料分佈。

載入與查詢完整的 Stack Overflow 資料函式庫

對於勇於挑戰的人來說，讓我們準備將這個資料集載入 DuckDB。首先，Stack Exchange 在 Internet Archive Stack Exchange dump (https://archive.org/download/stackexchange) 上公開發布所有資料，採用創用 CC 授權。我們將使用 Stack Overflow 網站本身最大的檔案集合。可以使用 curl 命令列工具完成這項任務，如下所示，該工具將檔案儲存在與伺服器上相同的名稱下：

curl -OL "https://archive.org/download/stackexchange/stackoverflow.com-{Comments,Posts,Votes,Users,Badges,PostLinks,Tags}.7z"

由於 Internet Archive 的頻寬有限，下載資料可能會很慢且令人沮喪，連線中斷的情況很常見。最終，我們將獲得七個壓縮的 XML 檔案，總大小為 27 GB：

  • 19G stackoverflow.com-Posts.7z
  • 5.2G stackoverflow.com-Comments.7z
  • 1.3G stackoverflow.com-Votes.7z
  • 684M stackoverflow.com-Users.7z
  • 343M stackoverflow.com-Badges.7z
  • 117M stackoverflow.com-PostLinks.7z
  • 903K stackoverflow.com-Tags.7z

下載完成後，您需要使用 7-Zip (https://7-zip.org/) 或 p7zip (https://p7zip.sourceforge.net/) 解壓縮檔案，如下所示。

這些檔案採用 SQL Server 匯出格式，每個 Row 元素都將所有欄位作為屬性。以下是檔案內容的範例：

<?xml version="1.0" encoding="utf-8"?>
<users>
...
<row Id="728812" Reputation="41063" CreationDate="2011-04-28T07:51:27.387"
DisplayName="Michael Hunger" LastAccessDate="2023-03-01T14:44:32.237"
WebsiteUrl="http://www.jexp.de" Location="Dresden, Germany" AboutMe=
"&lt;p&gt;&lt;a href=&quot;http://twitter.com/mesirii&quot; rel=&quot;
nofollow&quot;&gt;Michael Hunger&lt;/a&gt; has been passionate about
so?ware development for a long time. If you want him to speak at your
user group or conference, just drop him an email at michael at jexp.de"
Views="7046" UpVotes="4712" DownVotes="24" AccountId="376992" />
...

將 XML 轉換為 CSV

不幸的是，DuckDB 尚不支援解析 XML，因此我們需要使用一些外部工具將資料轉換為 DuckDB 支援的格式。雖然這個過程很耗時，但它是將 XML 轉換為 CSV 的可靠方法。

我們使用 xidel (https://www.videlibri.de/xidel.html) 這款 XML 處理命令列工具，它可以輸出 JSON。然後，我們使用 jq (https://jqlang.github.io/jq/) 命令列 JSON 處理器將 JSON 輸出轉換為 CSV。您可以從各自的網站找到 xidel 和 jq 的下載和安裝說明。最後，我們使用 gzip 壓縮 CSV 檔案以節省空間。

將評論轉換為 CSV 的範例

7z e -so stackoverflow.com-Comments.7z | \
xidel -se '//row/[(@Id|@PostId|@Score|@Text|@CreationDate|@UserId)]' - | \
(echo "Id,PostId,Score,Text,CreationDate,UserId" &&
jq -r '. | @csv') |
gzip -9 > Comments.csv.gz

內容解密：

  1. 7z e -so stackoverflow.com-Comments.7z：使用 7-Zip 解壓縮 stackoverflow.com-Comments.7z 檔案，並將輸出導向標準輸出。
  2. xidel -se '//row/[(@Id|@PostId|@Score|@Text|@CreationDate|@UserId)]' -：使用 xidel 從 XML 中提取指定的屬性，並輸出為 JSON。
  3. (echo "Id,PostId,Score,Text,CreationDate,UserId" && jq -r '. | @csv')：首先輸出 CSV 的標頭，然後使用 jq 將 JSON 輸出轉換為 CSV 格式。
  4. gzip -9 > Comments.csv.gz：將 CSV 輸出壓縮為 Comments.csv.gz 檔案。

資料模型

在開始探索之前，讓我們先看看 Stack Overflow 資料集的資料模型。圖 10.1 顯示了 Stack Overflow 網站的截圖，其中大部分資訊都是可見的。

在我們下載並轉換的檔案中，有以下實體，它們也對應於檔名：

  • 問題（Post 且 postTypeId=1）：包含標題、內文、建立日期、擁有者使用者 ID、父 ID、接受的答案 ID、答案數、標籤、讚數、倒讚數、瀏覽次數和評論。
  • 使用者：包含顯示名稱、自我介紹、聲譽值、最後登入日期等資訊。
  • 答案（Post 且 postTypeId=2）：包含擁有者使用者 ID、讚數、倒讚數和評論。其中一個答案可以被接受為正確答案。
  • 評論：包含文字、擁有者使用者 ID 和分數。
  • 徽章：包含使用者因貢獻而獲得的徽章資訊。
  • 文章連結：包含文章之間的連結，例如重複或相關問題。

這些檔案沒有任何關於索引或外部索引鍵的資訊；我們需要手動重新建立這些參考。為此，我們繪製瞭如圖 10.2 所示的資料模型，這是 Stack Overflow 資料模型的簡化版本，其中最重要的欄位以上述屬性列出，外部索引鍵以箭頭表示。

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 資料模型

rectangle "1:N" as n1
rectangle "1:N" as n2
rectangle "1:N" as n3
rectangle "1:N" as n4
rectangle "1:N" as n5
rectangle "N:1" as n6

n1 --> n2
n2 --> n3
n3 --> n4
n4 --> n5
n5 --> n6

@enduml

此圖示說明：

  1. 使用者與文章之間是一對多的關係。
  2. 使用者與評論之間是一對多的關係。
  3. 使用者與徽章之間是一對多的關係。
  4. 文章與評論之間是一對多的關係。
  5. 文章與文章連結之間是一對多的關係。
  6. 文章之間可以有關聯，例如一個問題有多個答案。

探討大型資料集的效能考量：以Stack Overflow資料函式庫為例

在處理龐大的資料集時，資料函式庫的效能是一個至關重要的議題。本篇文章將探討如何使用DuckDB來載入和查詢完整的Stack Overflow資料函式庫，並分析其效能。

資料模型與結構

首先，我們來看看Stack Overflow的資料模型。這個資料模型包含了多個實體之間的關聯，如下圖所示。

@startuml
note
  無法自動轉換的 Plantuml 圖表
  請手動檢查和調整
@enduml

此圖示描述了Stack Overflow資料函式庫中的主要實體及其關聯。使用者（Users）可以寫入多個帖子（Posts），而每個帖子都可以包含多個評論（Comments）、投票（Votes）和答案（Answers）。

探索CSV檔案資料

在將資料匯入DuckDB之前，我們先來探索一下CSV檔案的內容。首先，我們使用read_csv函式來讀取Tags.csv.gz檔案，並查詢其結構和內容。

查詢標籤數量

SELECT count(*) FROM read_csv('Tags.csv.gz');

查詢結果顯示，資料函式庫中共有約65,000個標籤。

檢視標籤資料結構

DESCRIBE(FROM read_csv('Tags.csv.gz'));

此查詢傳回了Tags.csv.gz檔案的中繼資料，包括欄位名稱和資料型別。結果如下：

┌───────────────┬─────────────┐
│ column_name │ column_type │
│ varchar │ varchar │
├───────────────┼─────────────┤
│ Id │ BIGINT │
│ TagName │ VARCHAR │
│ Count │ BIGINT │
│ ExcerptPostId │ BIGINT │
│ WikiPostId │ BIGINT │
└───────────────┴─────────────┘

查詢最受歡迎的標籤

SELECT TagName, Count
FROM read_csv(
'Tags.csv.gz',
column_names=['Id', 'TagName', 'Count'])
ORDER BY Count DESC
LIMIT 5;

查詢結果顯示了最受歡迎的前五個標籤，分別是javascript、python、java、c#和php。

將資料匯入DuckDB

將Stack Overflow資料匯入DuckDB有兩種方法：一是先建立表格，然後匯入資料；二是直接在讀取資料時建立表格。第一種方法更為明確，可以定義欄位名稱和資料型別，但需要事先了解資料的結構。

分析標籤分佈

為了分析標籤的使用分佈，我們可以將標籤的計數分成10的冪次方桶，然後計算每個桶中的標籤數量。

SELECT cast(pow(10,floor(log(Count)/log(10))) AS INT) AS bucket,
count(*)
FROM read_csv(
'Tags.csv.gz',
column_names=['Id', 'TagName', 'Count'])
WHERE Count > 0
GROUP BY bucket
ORDER BY bucket ASC;

內容解密：

  1. 計算每個計數的對數：使用log(Count)/log(10)計算每個標籤計數的對數，以10為底。
  2. 取得數量級：使用floor函式取得對數的整數部分，即數量級。
  3. 重新計算10的冪：使用pow(10, 數量級)重新計算10的冪。
  4. 轉換為整數：使用cast函式將結果轉換為整數，以便進行分組。
  5. 分組和排序：根據桶進行分組，並按桶的大小進行排序。

查詢結果顯示，標籤的使用分佈遵循冪律分佈，即少數標籤被廣泛使用，而大多數標籤的使用頻率較低。

載入與查詢完整的Stack Overflow資料函式庫

在處理大型資料集時，正確地載入和查詢資料是至關重要的。首先，我們需要調整CREATE TABLE陳述式，以適應檔案結構或欄位型別的變化。否則，載入過程可能會失敗。我們使用CREATE OR REPLACE TABLE陳述式，這樣就可以在測試過程中多次執行匯入操作，而無需在每次執行之間刪除表格。

建立使用者與文章表格

以下列舉了匯入Users和Posts資料表的陳述式，其他表格的匯入陳述式可參考本文的GitHub儲存函式庫。

CREATE OR REPLACE TABLE users AS
SELECT *
FROM read_csv(
  'Users.csv.gz',
  auto_detect=true,
  column_names=[
    'Id', 'Reputation', 'CreationDate', 'DisplayName',
    'LastAccessDate', 'AboutMe', 'Views', 'UpVotes', 'DownVotes'
  ]
);

內容解密：

  • CREATE OR REPLACE TABLE users AS：建立或替換名為users的資料表。
  • read_csv()：讀取CSV檔案的函式。
  • auto_detect=true：自動檢測CSV檔案的欄位型別。
  • column_names=[]：手動指定CSV檔案中的欄位名稱。
CREATE OR REPLACE TABLE posts AS
SELECT *
FROM read_csv(
  'Posts.csv.gz',
  auto_detect=true,
  column_names=[
    'Id', 'PostTypeId', 'AcceptedAnswerId', 'ParentId', 'CreationDate',
    'Score', 'ViewCount', 'Body', 'OwnerUserId', 'LastEditorUserId',
    'LastEditorDisplayName', 'LastEditDate', 'LastActivityDate', 'Title',
    'Tags', 'AnswerCount', 'CommentCount', 'FavoriteCount',
    'CommunityOwnedDate', 'ContentLicense'
  ]
);

內容解密：

  • 這段程式碼與前一段類別似，但用於建立posts資料表。
  • 注意Tags欄位，它包含最多六個Stack Overflow標籤，以角括號包裹（例如<sql><performance><duckdb>）。

檢查posts表格的結構：

SELECT column_name, column_type 
FROM (SHOW TABLE posts);

輸出結果如下：

┌───────────────────────┬─────────────┐
│ column_name           │ column_type │
│ varchar               │ varchar     │
├───────────────────────┼─────────────┤
│ Id                    │ BIGINT      │
│ PostTypeId            │ BIGINT      │
│ AcceptedAnswerId      │ BIGINT      │
│ CreationDate          │ TIMESTAMP   │
│ Score                 │ BIGINT      │
│ ViewCount             │ BIGINT      │
│ Body                  │ VARCHAR     │
│ OwnerUserId           │ BIGINT      │
│ LastEditorUserId      │ BIGINT      │
│ LastEditorDisplayName │ VARCHAR     │
│ LastEditDate          │ TIMESTAMP   │
│ LastActivityDate      │ TIMESTAMP   │
│ Title                 │ VARCHAR     │
│ Tags                  │ VARCHAR     │
│ AnswerCount           │ BIGINT      │
│ CommentCount          │ BIGINT      │
│ FavoriteCount        │ BIGINT      │
│ CommunityOwnedDate    │ TIMESTAMP   │
│ ContentLicense        │ VARCHAR     │
├───────────────────────┴─────────────┤
│ 19 rows 2 columns                  │
└─────────────────────────────────────┘

資料匯總與查詢

在處理大型資料集時，首先要了解資料值的分佈。我們可以使用SUMMARIZE子句來取得資料的統計資訊。

SUMMARIZE (
  SELECT Id, Reputation, CreationDate, Views, UpVotes, DownVotes
  FROM users
);

輸出結果如下：

┌──────────────┬─────────────┬────────────┬───────────────┬──────────────┐
│ column_name  │ column_type │ max        │ approx_unique │ avg          │
│ varchar      │ varchar     │ varchar    │ varchar       │ varchar      │
├──────────────┼─────────────┼────────────┼───────────────┼──────────────┤
│ Id           │ BIGINT      │ 21334825   │ 20113337      │ 11027766.241 │
│ Reputation   │ BIGINT      │ 1389256    │ 26919         │ 94.752717160 │
│ CreationDate │ TIMESTAMP   │ 2023-03-05 │ 19557978      │              │
│ Views        │ BIGINT      │ 2214048    │ 7452          │ 11.630429738 │
│ UpVotes      │ BIGINT      │ 591286     │ 6227          │ 8.7674283438 │
│ DownVotes    │ BIGINT      │ 1486341    │ 2930          │ 1.1697560125 │
└──────────────┴─────────────┴────────────┴───────────────┴──────────────┘

內容解密：

  • SUMMARIZE子句用於計算所選欄位的各種統計資訊，如最大值、近似唯一值數量和平均值等。
  • 這裡選擇了Id、Reputation、CreationDate、Views、UpVotes和DownVotes欄位進行匯總。

高效查詢大型資料表

現在，我們已經載入了資料表，可以執行一些查詢來瞭解資料的內容和查詢速度。假設我們是Stack Overflow的分析師，想要找出聲譽最高的使用者，並檢查他們是否仍然活躍。

.timer on
SELECT DisplayName, Reputation, LastAccessDate
FROM users
ORDER BY Reputation DESC
LIMIT 5;

輸出結果如下：

┌─────────────────┬────────────┬─────────────────────────┐
│ DisplayName     │ Reputation │ LastAccessDate          │
│ varchar         │ int64      │ timestamp               │
├─────────────────┼────────────┼─────────────────────────┤
│ Jon Skeet       │ 1389256    │ 2023-03-04 19:54:19.74  │
│ Gordon Linoff   │ 1228338    │ 2023-03-04 15:16:02.617 │
│ VonC            │ 1194435    │ 2023-03-05 01:48:58.937 │
│ BalusC          │ 1069162    │ 2023-03-04 12:49:24.637 │
│ Martijn Pieters │ 1016741    │ 2023-03-03 19:35:13.76  │
└─────────────────┴────────────┴─────────────────────────┘
Run Time (s): real 0.126 user 2.969485 sys 1.696962

內容解密：

  • .timer on：開啟計時功能，以測量查詢的執行時間。
  • SELECT陳述式用於檢索聲譽最高的五位使用者的顯示名稱、聲譽和最後存取日期。
  • ORDER BY Reputation DESC：按聲譽降序排序使用者。
  • LIMIT 5：限制輸出結果為前五位使用者。
玄貓 BlackCat

玄貓 BlackCat

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