DuckDB MotherDuck 最佳實踐

– 10 分鐘閱讀

DuckDB 和 MotherDuck 的結合為資料分析提供了強大的雲端解決方案。本文除了說明如何在 MotherDuck 上建立及管理 DuckDB 資料函式庫，也涵蓋了資料同步、分享及最佳化技巧。尤其在資料上傳方面，特別提醒本地與雲端資料函式庫命名需區分，避免衝突。此外，善用 MotherDuck 的雲端特性，直接從 S3 等雲端儲存服務匯入資料，能大幅提升效率。對於成本控管，建議依需求載入資料，並設定合理的熱儲存上限。最後，文章也介紹了 MotherDuck 的 AI 查詢功能，讓使用者能以自然語言操作資料函式庫，降低使用門檻。

在MotherDuck上使用DuckDB的最佳實踐

7.3.1 上傳本地資料函式庫至MotherDuck

將本地資料函式庫上傳至MotherDuck時，有一項非常重要的規則需要謹記：本地與遠端的資料函式庫名稱必須不同。如果忘記這項規則並給予相同的名稱，將會收到以下錯誤訊息：

CREATE DATABASE "countries" FROM 'countries';
Error: Catalog Error: Database 'countries' has already been created and attached

若嘗試上傳目前的資料函式庫，將會出現以下錯誤：

Error: Binder Error: Database "countries.duckdb" is already attached with alias "countries"

在範例中，我們透過執行 .open md: 避免了這些錯誤，這個指令做了兩件事：載入MotherDuck擴充套件功能並連線到預設的MotherDuck資料函式庫。另一種選擇是使用 .open，這將切換到一個記憶體內的資料函式庫。

內容解密：

  1. .open md: 的作用是載入MotherDuck擴充套件並連線到預設資料函式庫。
  2. 使用 .open 可切換至記憶體內資料函式庫，避免與本地資料函式庫名稱衝突。

7.3.2 在MotherDuck中建立資料函式庫

除了上傳現有的資料函式庫外，也可以直接在雲端從零開始建立資料函式庫結構。如果schema的內容主要依賴於儲存在其他公共雲上的檔案，將它們先下載到本地的DuckDB例項再上傳至MotherDuck將浪費時間和資源。在大多數情況下，MotherDuck相較於本地系統更接近S3儲存桶，因此直接從雲端匯入資料至MotherDuck可以節省時間和成本。

使用 CREATE DATABASE 命令可以在MotherDuck中建立新的資料函式庫。資料函式庫名稱不能包含特殊字元，只能使用字母、數字和底線。

CREATE DATABASE "my-test";

可透過 SHOW DATABASES 命令確認資料函式庫是否已成功建立：

SHOW DATABASES;
┌───────────────┐
│ database_name │
│ varchar │
├───────────────┤
│ my-test       │
└───────────────┘

或者，可以使用 .databases CLI命令。這些命令可以在本地的DuckDB CLI上執行，也可以在MotherDuck UI（https://app.motherduck.com/）上執行。

內容解密：

  1. CREATE DATABASE 命令用於在MotherDuck中建立新的資料函式庫。
  2. 資料函式庫名稱限制為字母、數字和底線。
  3. SHOW DATABASES 用於確認資料函式庫是否建立成功。

切換到新建立的資料函式庫

執行 CREATE DATABASE 陳述式後，需要使用 USE 'my-test'; 切換到該資料函式庫。然後，可以使用 current_database() 函式檢查當前連線的資料函式庫：

SELECT current_database();
┌────────────────────┐
│ current_database() │
│ varchar            │
├────────────────────┤
│ my-test            │
└────────────────────┘

內容解密：

  1. 使用 USE 陳述式切換到新建立的資料函式庫。
  2. current_database() 函式傳回當前連線的資料函式庫名稱。

在MotherDuck中建立表並插入資料

可以在MotherDuck資料函式庫中建立表並插入資料：

CREATE TABLE people (name varchar, born date);
INSERT INTO people VALUES ('Mark', '1989-01-01'), ('Hannes', '1990-01-01');

然後，可以查詢該表的記錄數：

SELECT count(*) FROM people;
┌──────────────┐
│ count_star() │
│ int64        │
├──────────────┤
│ 2            │
└──────────────┘

或者，使用帶有資料函式庫名稱的字首查詢：

SELECT count(*) FROM "my-test".people;

內容解密：

  1. 在MotherDuck中建立表和插入資料的操作與本地DuckDB相同。
  2. 可以使用完全限定的表名（包括資料函式庫名稱）進行查詢。

刪除測試資料函式庫

當完成測試後，可以刪除測試資料函式庫。首先，需要切換回預設的 my_db 資料函式庫，然後執行 DROP DATABASE 命令：

USE my_db;
DROP DATABASE "my-test";

內容解密：

  1. 刪除資料函式庫前需要先切換到其他資料函式庫。
  2. 使用 DROP DATABASE 命令刪除不再需要的資料函式庫。

7.3.3 分享資料函式庫

MotherDuck允許分享資料函式庫的唯讀快照。這不僅便於資料分享，也有利於協同分析和功能分享。快照中不僅包含資料，還包含所有檢視以及在建立過程中付出的努力。可以將其視為「強化版的試算表」。

要分享資料，可以使用 CREATE SHARE 陳述式。這將生成一個可分享的連結，其他人可以使用 ATTACH 命令連線到該連結。

CREATE SHARE shared_countries FROM countries;

執行後，將獲得類別似以下的分享連結：

┌──────────────────────────────────────────────────────────┐
│ share_url                                                │
│ varchar                                                  │
├──────────────────────────────────────────────────────────┤
│ md:_share/countries/1acb80cf-d872-4fab-8077-64975cce0452 │
└──────────────────────────────────────────────────────────┘

內容解密：

  1. CREATE SHARE 用於生成可分享的唯讀快照。
  2. 分享連結可以被其他擁有MotherDuck帳戶的人使用 ATTACH 命令連線。

要連線分享的資料函式庫，對方需要在他們的DuckDB例項中執行：

ATTACH 'md:_share/countries/1acb80cf-d872-4fab-8077-64975cce0452' AS shared_countries;

可以使用 DESCRIBE SHARE 命令描述分享資料函式庫的內容：

DESCRIBE SHARE shared_countries;

這將輸出分享資料函式庫的詳細資訊，包括原始名稱、連結、資料函式庫ID和快照ID。

內容解密：

  1. 對方使用 ATTACH 命令連線分享的資料函式庫。
  2. DESCRIBE SHARE 用於查詢分享資料函式庫的詳細資訊。

目前，分享的資料函式庫不會在源資料函式庫變更時自動更新。變更需要透過 UPDATE SHARE 陳述式明確地傳播到分享的資料函式庫中。

內容解密：

  1. 目前分享資料函式庫不支援自動更新。
  2. 需要使用 UPDATE SHARE 手動更新分享資料函式庫。

充分利用MotherDuck的最佳實踐

在前面的章節中，我們已經介紹瞭如何使用DuckDB和MotherDuck進行資料處理和分析。在本文中，我們將探討如何充分利用MotherDuck的功能，包括管理S3金鑰、載入S3儲存桶中的資料、最佳化資料擷取和查詢資料。

管理S3金鑰和載入S3儲存桶中的資料

在使用MotherDuck時，您經常需要從Amazon S3儲存桶中載入資料。為了實作這一點，您需要提供S3的存取金鑰。MotherDuck允許您使用CREATE OR REPLACE SECRET陳述式來儲存和管理您的S3金鑰。

CREATE OR REPLACE SECRET (
  TYPE S3,
  KEY_ID 'access-key',
  SECRET 'secret-key',
  REGION 'us-east-1'
);

一旦您儲存了S3金鑰，您就可以像查詢本地檔案一樣查詢S3儲存桶中的資料。

CREATE OR REPLACE TABLE mytable AS
FROM 's3://...';

完成資料查詢後，請記得刪除S3金鑰以確保安全。

DROP SECRET (TYPE s3);

內容解密：

  1. CREATE OR REPLACE SECRET陳述式用於在MotherDuck中建立或更新S3存取金鑰，包含TYPE S3、KEY_ID、SECRET和REGION等必要資訊。
  2. KEY_ID和SECRET分別代表您的AWS存取金鑰ID和秘密存取金鑰。
  3. REGION指定了您的S3儲存桶所在的AWS區域。
  4. 使用DROP SECRET陳述式可以刪除不再需要的S3金鑰，以避免安全風險。

最佳化資料擷取和MotherDuck使用

在使用MotherDuck時，瞭解如何最佳化資料擷取和查詢對於控制成本和提高效率至關重要。MotherDuck提供了靈活的執行選項，允許您在本地或雲端執行查詢。

使用MD_RUN引數控制查詢執行位置

MotherDuck的擴充功能增強了所有以read_為字首的函式，例如read_json和read_csv_auto，支援MD_RUN引數。該引數允許您控制查詢的執行位置。

SELECT count(*)
FROM read_csv_auto(
  'https://github.com/duckdb/duckdb/raw/main/data/csv/ips.csv.gz',
  MD_RUN=REMOTE
);

內容解密：

  1. MD_RUN=LOCAL表示在本地DuckDB環境中執行查詢。
  2. MD_RUN=REMOTE表示在MotherDuck託管的雲端DuckDB執行時執行查詢。
  3. MD_RUN=AUTO是預設選項，自動決定查詢的執行位置，通常將遠端請求（如s3://、http://和https://）在雲端執行。

成本控制和效能最佳化

MotherDuck提供了免費和付費服務層級。付費層級（Standard tier）每月基本價格為25美元，包含100 GB儲存空間和100小時計算時間。額外的冷儲存和計算時間可以按需購買。

為了控制成本，應避免在MotherDuck中儲存不必要的資料，而是根據需要載入或處理資料。同時，設定合理的熱儲存上限對於控制成本和提高查詢效能至關重要。

結合AI進行資料查詢

雖然SQL與自然語言相似，但對於新手來說仍然具有挑戰性。未來，我們可以期待更多的工具和技術來簡化SQL查詢的過程，例如結合AI技術來輔助查詢。

7.3 充分利用 MotherDuck 的強大功能

在雲端環境中使用 DuckDB 的一大亮點是 MotherDuck 提供的生成式 AI 功能（https://mng.bz/lMjB），允許使用者以自然語言查詢資料。這項功能可以描述資料函式庫結構，並自動生成 SQL 陳述式，甚至修正現有的 SQL 陳述式。該功能的工作原理是將資料函式庫結構與詳細提示和使用者問題一併傳送給大語言模型（LLM），由 LLM 生成所需的 SQL 陳述式，並可選執行該陳述式。

使用自然語言進行查詢

根據我們的經驗，這項功能相當實用，您可以在任何測試資料集上試用。以下範例使用 StackOverflow 資料集，並透過以下陳述式進行連線：

ATTACH 'md:_share/stackoverflow/6c318917-6888-425a-bea1-5860c29947e5' AS stackoverflow_analysis;
USE stackoverflow_analysis;

您可以透過呼叫 prompt_schema 程式來取得資料函式庫結構的描述：

.mode line
CALL prompt_schema();

內容解密：

  • .mode line 用於設定輸出的顯示模式為行模式，讓結果以更易讀的方式呈現。
  • CALL prompt_schema(); 呼叫 prompt_schema 程式，取得當前資料函式庫的結構描述。

執行此程式幾次後，您可能會得到不同的結果，例如：

summary = The database contains tables for storing data related to votes, tags, posts, post links, badges, users, and comments.
Run Time (s): real 3.672 user 0.007355 sys 0.002674

以及

summary = The data in the database is about votes, tags, posts, post links, badges, users, and comments.
Run Time (s): real 3.054 user 0.007354 sys 0.003175

這看似令人驚訝，直到我們意識到 LLM 是一個機率模型，不保證每次傳回相同的結果。

接下來，我們不使用 SQL 陳述式，而是使用自然語言來查詢最流行的標籤：What are the most popular tags?。由於這不是有效的 SQL 陳述式，我們需要透過一個特殊的 pragma 稱為 prompt_query 來指示這是一個提示：

.mode duckbox
PRAGMA prompt_query('What are the most popular tags?');

內容解密：

  • .mode duckbox 將輸出模式設定為 duckbox，讓結果以表格形式呈現。
  • PRAGMA prompt_query('What are the most popular tags?'); 使用自然語言查詢最流行的標籤，並透過 prompt_query pragma 指示這是一個自然語言查詢。

結果顯示了前 10 個最流行的標籤：

┌────────────┬─────────┐
│ TagName    │ Count   │
│ varchar    │ int64   │
├────────────┼─────────┤
│ javascript │ 2479947 │
│ python     │ 2113196 │
│ java       │ 1889767 │
│ c#         │ 1583879 │
│ php        │ 1456271 │
│ android    │ 1400026 │
│ html       │ 1167742 │
│ jquery     │ 1033113 │
│ c++        │ 789699  │
│ css        │ 787138  │
├────────────┴─────────┤
│ 10 rows 2 columns     │
└──────────────────────┘
-- Run Time (s): real 3.763 user 0.124567 sys 0.001716

如果您想知道用於計算結果的 SQL 陳述式，可以使用 prompt_sql 程式：

.mode line
CALL prompt_sql('What are the most popular tags?');

內容解密：

  • CALL prompt_sql('What are the most popular tags?'); 取得用於回答自然語言查詢的 SQL 陳述式。

結果顯示了生成的 SQL 陳述式：

query = SELECT TagName, Count FROM tags ORDER BY Count DESC;
Run Time (s): real 5.425 user 0.010331 sys 0.005074

處理更複雜的查詢

讓我們看看它如何處理更複雜的問題：What are the titles and comment counts of the five posts with the most comments?

.mode duckbox
PRAGMA prompt_query("Which 5 questions have the most comments, what is the post title and comment count");

內容解密：

  • 此查詢使用自然語言詢問具有最多評論的前五個問題的標題和評論數量。

結果顯示了相關問題的標題和評論數量：

┌──────────────────────────────────────────────────────────────┬───────────┐
│ Title                                                        │ comments  │
│ varchar                                                      │ int64     │
├──────────────────────────────────────────────────────────────┼───────────┤
│ UIImageView Frame Doesnt Reflect Constraints                 │ 108       │
│ Is it possible to use adb commands to click on a view by find│ 102       │
│ How to create a new web character symbol recognizable by html│ 100       │
│ Why isnt my CSS3 animation smooth in Google Chrome (but very │ 89        │
│ Heap Gives Page Fault                                        │ 89        │
└──────────────────────────────────────────────────────────────┴───────────┘
Run Time (s): real 19.695 user 2.406446 sys 0.018353

同樣地，您可以透過 prompt_sql 程式檢視生成的 SQL 陳述式：

.mode line
CALL prompt_sql("Which 5 questions have the most comments, what is the post title and comment count");

內容解密：

  • 此呼叫傳回了用於回答複雜自然語言查詢的 SQL 陳述式。

結果如下：

query = SELECT p.Title, COUNT(c.Id) AS comment_count FROM posts p JOIN comments c ON p.Id = c.PostId AND p.PostTypeId = 1 GROUP BY p.Title ORDER BY comment_count DESC LIMIT 5;
Run Time (s): real 4.795 user 0.002301 sys 0.001346

MotherDuck UI 與 AI 查詢

圖表如下所示（此圖示為 MotherDuck UI 的範例）：

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

title MotherDuck UI 與 AI 查詢

rectangle "輸入自然語言查詢" as node1
rectangle "生成 SQL 陳述式" as node2
rectangle "傳回查詢結果" as node3

node1 --> node2
node2 --> node3

@enduml

此圖示說明瞭 MotherDuck UI 如何與其生成式 AI 功能互動，生成並執行 SQL 陳述式，最終傳回並顯示查詢結果。

玄貓 BlackCat

玄貓 BlackCat

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