🐷
DuckDBを使ってgit管理のzenn記事に対してDBクエリを実行する
zenn記事をgit管理している前提。
DuckDBを利用して、ローカルのデータ分析基盤を作成してみる。
Install
DuckDBをインストールする。ワンライナーでインストール可能。
curl https://install.duckdb.org | sh
Zennの記事でduck.dbファイルを作成する
Zennの記事ファイル articles/*.md を読み込み、duck.dbファイルを作成する。
python実行環境を作成する。また、python duckdb apiをインストールする。
python3 -m venv venv
source venv/bin/activate
venv ❯ pip install duckdb python-frontmatter
venv ❯ python duck.py
# duck.py
import duckdb
import glob
import frontmatter
# DuckDBに接続
conn = duckdb.connect('articles.db')
# articlesテーブルの作成
conn.execute("""
DROP TABLE IF EXISTS articles;
CREATE TABLE articles(
title VARCHAR,
published BOOLEAN,
published_at DATE,
date DATE,
topics VARCHAR[],
content TEXT,
filename VARCHAR
);
""")
# Markdownファイルを読み込む
markdown_files = glob.glob('articles/*.md')
# 一括挿入用のデータリストを準備
articles = []
for file_path in markdown_files:
try:
# frontmatterとcontentを分離
article = frontmatter.load(file_path)
# データの準備
data = [
article.metadata.get('title'),
article.metadata.get('published'),
article.metadata.get('published_at'),
article.metadata.get('date'),
article.metadata.get('topics', []),
article.content,
file_path
]
articles.append(data)
print(f"Processed: {file_path}")
except Exception as e:
print(f"Error processing {file_path}: {str(e)}")
continue
# データの一括挿入
if articles:
conn.executemany("""
INSERT INTO articles (title, published, published_at, date, topics, content, filename)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", articles)
# 確認のためのクエリ
result = conn.execute("SELECT count(*) FROM articles").fetchall()
print(result)
conn.close()
duckdbへのINSERTは一括挿入とする。
クエリ実行する
作成したデータベースを指定して、duckdbを起動する。
duckdb articles.db
SQLスニペット
zenn記事総数の取得
select count(*) from articles;
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 44 │
└──────────────┘
2025年2月の公開記事数の取得
select count(*) from articles WHERE date >= '2025-02-01' and date < '2025-03-01';
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 3 │
└──────────────┘
rails記事カテゴリ数の取得
select count(*) from articles WHERE list_contains(topics, 'rails');
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 13 │
└──────────────┘
記事カテゴリごとの記事数を取得
WITH expanded AS (
SELECT unnest(topics) as topic
FROM articles
WHERE topics != []
)
SELECT
topic,
COUNT(*) as count,
bar(count(*), 0, 100)
FROM expanded
GROUP BY topic
ORDER BY count DESC;
┌──────────────┬───────┬──────────────────────────────────────────────────────────────────────────────────┐
│ topic │ count │ bar(count_star(), 0, 100) │
│ varchar │ int64 │ varchar │
├──────────────┼───────┼──────────────────────────────────────────────────────────────────────────────────┤
│ rails │ 13 │ ██████████▍ │
│ aws │ 7 │ █████▌ │
│ ruby │ 7 │ █████▌ │
│ tailwindcss │ 7 │ █████▌ │
│ books │ 4 │ ███▏ │
│ git │ 2 │ █▌ │
│ ecs │ 2 │ █▌ │
│ kendra │ 1 │ ▊ │
│ elb │ 1 │ ▊ │
│ administrate │ 1 │ ▊ │
│ gitlab │ 1 │ ▊ │
│ ssm │ 1 │ ▊ │
│ cloudfront │ 1 │ ▊ │
│ book │ 1 │ ▊ │
│ test │ 1 │ ▊ │
│ ec2 │ 1 │ ▊ │
│ rubocop │ 1 │ ▊ │
│ docker │ 1 │ ▊ │
│ csv │ 1 │ ▊ │
│ CloudShell │ 1 │ ▊ │
├──────────────┴───────┴──────────────────────────────────────────────────────────────────────────────────┤
│ 20 rows 3 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────┘
参考
Discussion