mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
hanzoskills.com/llms.txt now opens with the install line, the API reference index and the company index, so an agent that lands on any one of the three can find the rest. robots.txt claimed 537 documents while llms.txt counts 920. The number is gone from robots.txt; llms.txt derives it. public/ is regenerated from scripts/site.py, which it had fallen behind: the copy-prompt menu and agent bar were in the generator and not in the committed pages. tests/site.mjs passes against the result. Co-authored-by: Hanzo Dev <[email protected]>
922 lines
36 KiB
HTML
922 lines
36 KiB
HTML
<!doctype html>
|
|
<html lang="en"><head>
|
|
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
|
|
<title>duckdb-analytics</title>
|
|
<meta name="color-scheme" content="dark">
|
|
<style>
|
|
/* Committed to one visual world: a dark instrument panel. No light variant —
|
|
the dot field and the hairline rules only read on black, and a naive
|
|
inversion would wash both out. Every colour is painted explicitly so the page
|
|
never borrows a host background. */
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
|
|
|
|
:root{
|
|
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
|
|
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
|
|
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
|
|
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
|
|
--font-zen-pixel-square:'Zen Pixel Square',monospace;
|
|
--mono:var(--font-zen-mono);
|
|
--sans:var(--font-zen-sans);
|
|
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
|
|
--r:999px; --r-soft:14px;
|
|
}
|
|
*{box-sizing:border-box}
|
|
html{-webkit-text-size-adjust:100%}
|
|
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
|
|
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
|
|
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
|
|
|
|
/* The dot field. A halftone made of one repeating radial-gradient, faded out
|
|
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
|
|
/* No overflow clip here: the dot field is inset to the box already, and a clip
|
|
would cut off the copy-prompt menu. */
|
|
.hero{position:relative;border-bottom:1px solid var(--line)}
|
|
.hero::before{content:"";position:absolute;inset:0;
|
|
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
|
|
background-size:7px 7px;
|
|
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
pointer-events:none}
|
|
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
|
|
|
|
nav{border-bottom:1px solid var(--line)}
|
|
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
|
|
justify-content:space-between;align-items:center;gap:1rem}
|
|
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--ink);text-decoration:none;
|
|
display:inline-flex;align-items:center;gap:.6rem}
|
|
.brand i{font-style:normal;color:var(--faint)}
|
|
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
|
|
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--soft);text-decoration:none}
|
|
nav a.up:hover{color:var(--ink)}
|
|
|
|
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
|
|
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
|
|
h1 em{font-style:normal;color:var(--faint)}
|
|
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
|
|
|
|
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
|
|
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
|
|
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
|
|
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
|
|
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
|
|
line-height:1.2;font-weight:400}
|
|
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
|
|
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
|
|
.btn.solid:hover{background:#d8d8d4}
|
|
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
|
|
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
|
|
|
|
/* Copy-prompt selector. Each row copies the prompt for its own target. */
|
|
.copy{position:relative;display:inline-flex}
|
|
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
|
|
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
|
|
padding:.4rem;display:flex;flex-direction:column;
|
|
box-shadow:0 12px 32px rgba(0,0,0,.75)}
|
|
.menu[hidden]{display:none}
|
|
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
|
|
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
|
|
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
|
|
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
|
|
white-space:nowrap}
|
|
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
|
|
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
|
|
.mi:hover .mark{color:var(--ink)}
|
|
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
|
|
letter-spacing:.1em;text-transform:uppercase}
|
|
|
|
/* Works-with bar: one scrolling row, name and mark per agent. */
|
|
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
|
|
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
|
|
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
|
|
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
|
|
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
|
|
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
|
|
display:flex;align-items:center}
|
|
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
|
|
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
|
|
-webkit-overflow-scrolling:touch;
|
|
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
|
|
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
|
|
.bar::-webkit-scrollbar{display:none}
|
|
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
|
|
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
|
|
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
|
|
text-decoration:none;background:var(--ground)}
|
|
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
|
|
.agent .mark{width:.95rem;height:.95rem}
|
|
|
|
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
|
|
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
|
|
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
|
|
p{margin:0 0 1rem;max-width:70ch}
|
|
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
|
|
li{margin:.22rem 0}
|
|
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
|
|
text-decoration-color:var(--line-hi)}
|
|
a:hover{text-decoration-color:var(--ink)}
|
|
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
|
|
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
|
|
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
|
|
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
|
|
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
|
|
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
|
|
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
|
|
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
|
|
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
|
|
display:block;overflow-x:auto}
|
|
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
|
|
|
|
.curl{display:block;background:var(--panel);border:1px solid var(--line);
|
|
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
|
|
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
|
|
white-space:pre;line-height:1.9}
|
|
.curl b{color:var(--ink);font-weight:400}
|
|
.curl i{font-style:normal;color:var(--faint)}
|
|
|
|
/* index */
|
|
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
|
|
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
|
|
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
|
|
#q:focus{outline:none;border-color:var(--soft)}
|
|
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
|
|
text-transform:uppercase;margin:0 0 2rem}
|
|
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
|
|
padding-bottom:.45rem}
|
|
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
|
|
border-radius:var(--r-soft);text-decoration:none}
|
|
.row:hover{background:#0a0a0a}
|
|
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
|
|
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
|
|
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
|
|
color:var(--faint);font-size:.85rem}
|
|
footer a{color:var(--soft)}
|
|
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
|
|
margin:0 0 2rem;font-size:.85rem}
|
|
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
|
|
border-bottom:1px solid var(--line);padding-bottom:.4rem}
|
|
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
|
|
.fmap a:hover{text-decoration:underline}
|
|
.fmap div{color:var(--faint);line-height:1.5}
|
|
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
|
|
|
|
@media (max-width:640px){
|
|
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
|
|
.btns{gap:.5rem}
|
|
.btn{padding:.75rem 1.15rem;font-size:.68rem}
|
|
.copy,.copy .btn{width:100%;justify-content:center}
|
|
.menu{left:0;right:0;min-width:0}
|
|
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
|
|
.strip b{border-right:0;padding-right:0}
|
|
.bar{width:100%}
|
|
}
|
|
</style>
|
|
<link rel="alternate" type="text/markdown" href="/duckdb-analytics.md">
|
|
|
|
<link rel="icon" href="/favicon.svg">
|
|
<link rel="canonical" href="https://hanzoskills.com/duckdb-analytics">
|
|
</head>
|
|
<!-- Reading this with a machine? Every page here is also plain markdown.
|
|
This one: https://hanzoskills.com/duckdb-analytics.md
|
|
Index: https://hanzoskills.com/llms.txt
|
|
Corpus: https://hanzoskills.com/all.md
|
|
Append .md to any path for the markdown twin. -->
|
|
<body>
|
|
<nav><div class="in">
|
|
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
|
|
<a class="up" href="/">All skills</a>
|
|
</div></nav>
|
|
|
|
<div class="wrap">
|
|
<h1>duckdb-analytics</h1><p class="lede">Skill: duckdb-analytics Category: Data/Analytics Related: Python, SQL, Parquet, data pipelines</p><h1>DuckDB Analytics</h1>
|
|
<p><strong>Skill</strong>: duckdb-analytics <strong>Category</strong>: Data/Analytics <strong>Related</strong>: Python, SQL, Parquet, data pipelines</p>
|
|
<h2>When to Use This Skill</h2>
|
|
<h3>Triggers</h3>
|
|
<ul><li>"analyze this dataset"</li><li>"query Parquet files"</li><li>"embedded analytics database"</li><li>"fast SQL on CSV/JSON"</li><li>"convert Pandas to SQL"</li><li>"OLAP queries"</li><li>"aggregate large files"</li><li>"analytical workload"</li><li>"columnar database"</li><li>"serverless analytics"</li></ul>
|
|
<h3>Use Cases</h3>
|
|
<ul><li><strong>Ad-hoc analysis</strong>: Query CSV/Parquet files without loading into database</li><li><strong>ETL pipelines</strong>: Transform data with SQL, export to various formats</li><li><strong>Data science</strong>: Bridge between Pandas/Polars and SQL analytics</li><li><strong>Embedded analytics</strong>: Add SQL engine to Python/Node.js applications</li><li><strong>Data exploration</strong>: Fast aggregations and window functions on large datasets</li><li><strong>Prototyping</strong>: Replace complex data pipelines with SQL queries</li><li><strong>Local development</strong>: Test analytics queries before deploying to warehouse</li></ul>
|
|
<h3>When NOT to Use</h3>
|
|
<ul><li>Need distributed/clustered database (use ClickHouse, Spark)</li><li>Multi-user concurrent writes (use PostgreSQL, MySQL)</li><li>Real-time transactional workload (use PostgreSQL, OLTP database)</li><li>Gigantic datasets (>1TB single-node) (use BigQuery, Snowflake)</li></ul>
|
|
<hr>
|
|
<h2>Core Concepts</h2>
|
|
<h3>Architecture</h3>
|
|
<p><strong>In-Process Embedded Database</strong>:</p>
|
|
<pre><code>Application Process
|
|
├─ DuckDB Engine (embedded library)
|
|
├─ Columnar Storage
|
|
├─ Vectorized Execution
|
|
└─ Direct File Access (Parquet, CSV, JSON)
|
|
|
|
NO separate server process required</code></pre>
|
|
<p><strong>Key Characteristics</strong>:</p>
|
|
<ul><li><strong>Embedded</strong>: Runs inside application process (like SQLite)</li><li><strong>Columnar</strong>: Stores data in columns (OLAP-optimized)</li><li><strong>Vectorized</strong>: Processes data in batches (SIMD-accelerated)</li><li><strong>Zero-copy</strong>: Query files directly without import</li><li><strong>ACID</strong>: Full transactional support</li></ul>
|
|
<h3>Columnar Storage</h3>
|
|
<pre><code>Row-Oriented (OLTP): Columnar (OLAP):
|
|
ID | Name | Age | City ID: [1, 2, 3, 4]
|
|
1 | Alice | 30 | NYC Name: ['Alice', 'Bob', ...]
|
|
2 | Bob | 25 | LA Age: [30, 25, 28, 35]
|
|
3 | Carol | 28 | SF City: ['NYC', 'LA', 'SF', ...]
|
|
|
|
Access pattern: Full rows Access pattern: Specific columns
|
|
Use case: CRUD operations Use case: Aggregations, analytics</code></pre>
|
|
<p><strong>Benefits</strong>:</p>
|
|
<ul><li>Better compression (similar values grouped)</li><li>Faster aggregations (scan only needed columns)</li><li>Efficient analytics (skip irrelevant columns)</li></ul>
|
|
<h3>SQL Dialect</h3>
|
|
<p><strong>PostgreSQL-Compatible</strong>:</p>
|
|
<ul><li>Standard SQL-92/99/2003 features</li><li>Window functions, CTEs, LATERAL joins</li><li>JSON operators, full-text search</li><li>Array/struct types, nested data</li><li>Extensions via SQL (like PostgreSQL)</li></ul>
|
|
<hr>
|
|
<h2>Patterns</h2>
|
|
<h3>Installation & Setup</h3>
|
|
<pre><code class="lang-bash"># Python
|
|
pip install duckdb
|
|
# or with uv
|
|
uv add duckdb
|
|
|
|
# CLI
|
|
brew install duckdb
|
|
# or
|
|
curl -OL https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
|
|
unzip duckdb_cli-linux-amd64.zip</code></pre>
|
|
<h3>Pattern 1: Direct File Querying</h3>
|
|
<p><strong>Query CSV Without Import</strong>:</p>
|
|
<pre><code class="lang-sql">-- No CREATE TABLE needed!
|
|
SELECT city, COUNT(*), AVG(age)
|
|
FROM 'data/users.csv'
|
|
GROUP BY city
|
|
ORDER BY COUNT(*) DESC;</code></pre>
|
|
<p><strong>Query Parquet Files</strong>:</p>
|
|
<pre><code class="lang-sql">-- Single file
|
|
SELECT * FROM 'sales.parquet' WHERE year = 2024;
|
|
|
|
-- Multiple files (glob pattern)
|
|
SELECT product_id, SUM(revenue)
|
|
FROM 'sales/*.parquet'
|
|
GROUP BY product_id;
|
|
|
|
-- S3/HTTP files (requires httpfs extension)
|
|
INSTALL httpfs;
|
|
LOAD httpfs;
|
|
|
|
SELECT * FROM 's3://mybucket/data/*.parquet'
|
|
WHERE date >= '2024-01-01';</code></pre>
|
|
<p><strong>Query JSON/JSONL</strong>:</p>
|
|
<pre><code class="lang-sql">-- JSON array
|
|
SELECT * FROM 'data.json';
|
|
|
|
-- JSON Lines (newline-delimited)
|
|
SELECT event_type, COUNT(*)
|
|
FROM read_json_auto('events.jsonl')
|
|
GROUP BY event_type;</code></pre>
|
|
<h3>Pattern 2: Python Integration</h3>
|
|
<p><strong>Basic Usage</strong>:</p>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
# In-memory database
|
|
con = duckdb.connect()
|
|
|
|
# Persistent database
|
|
con = duckdb.connect('analytics.db')
|
|
|
|
# Execute query
|
|
result = con.execute("SELECT 42 AS answer").fetchall()
|
|
print(result) # [(42,)]
|
|
|
|
# Fetch as DataFrame
|
|
df = con.execute("SELECT * FROM 'data.csv'").df()</code></pre>
|
|
<p><strong>Query Pandas DataFrames Directly</strong>:</p>
|
|
<pre><code class="lang-python">import pandas as pd
|
|
import duckdb
|
|
|
|
# Create DataFrame
|
|
df = pd.DataFrame({
|
|
'name': ['Alice', 'Bob', 'Carol'],
|
|
'age': [30, 25, 28],
|
|
'city': ['NYC', 'LA', 'SF']
|
|
})
|
|
|
|
# Query DataFrame as if it's a table
|
|
result = duckdb.query("""
|
|
SELECT city, AVG(age) as avg_age
|
|
FROM df
|
|
GROUP BY city
|
|
""").df()
|
|
|
|
print(result)</code></pre>
|
|
<p><strong>Pandas/Polars/Arrow Integration</strong>:</p>
|
|
<pre><code class="lang-python">import duckdb
|
|
import pandas as pd
|
|
import polars as pl
|
|
|
|
# Pandas
|
|
df_pandas = pd.read_csv('data.csv')
|
|
result = duckdb.query("SELECT * FROM df_pandas WHERE age > 25").df()
|
|
|
|
# Polars
|
|
df_polars = pl.read_csv('data.csv')
|
|
result = duckdb.query("SELECT * FROM df_polars WHERE age > 25").pl()
|
|
|
|
# Arrow
|
|
result_arrow = duckdb.query("SELECT * FROM 'data.parquet'").arrow()</code></pre>
|
|
<h3>Pattern 3: ETL and Transformations</h3>
|
|
<p><strong>Load Data</strong>:</p>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
con = duckdb.connect('warehouse.db')
|
|
|
|
# Create table from CSV
|
|
con.execute("""
|
|
CREATE TABLE users AS
|
|
SELECT * FROM read_csv_auto('users.csv')
|
|
""")
|
|
|
|
# Create table from Parquet
|
|
con.execute("""
|
|
CREATE TABLE sales AS
|
|
SELECT * FROM 'sales.parquet'
|
|
""")
|
|
|
|
# Create table from DataFrame
|
|
import pandas as pd
|
|
df = pd.read_json('events.json')
|
|
con.execute("CREATE TABLE events AS SELECT * FROM df")</code></pre>
|
|
<p><strong>Export Data</strong>:</p>
|
|
<pre><code class="lang-python"># Export to Parquet
|
|
con.execute("""
|
|
COPY (SELECT * FROM users WHERE active = true)
|
|
TO 'active_users.parquet' (FORMAT PARQUET)
|
|
""")
|
|
|
|
# Export to CSV
|
|
con.execute("""
|
|
COPY users TO 'users.csv' (HEADER, DELIMITER ',')
|
|
""")
|
|
|
|
# Export to JSON
|
|
con.execute("""
|
|
COPY (SELECT * FROM events WHERE date >= '2024-01-01')
|
|
TO 'recent_events.json' (ARRAY true)
|
|
""")</code></pre>
|
|
<p><strong>Complex Transformations</strong>:</p>
|
|
<pre><code class="lang-python"># Multi-stage ETL
|
|
con.execute("""
|
|
-- Stage 1: Clean data
|
|
CREATE TABLE clean_users AS
|
|
SELECT
|
|
LOWER(TRIM(email)) AS email,
|
|
name,
|
|
age,
|
|
created_at::DATE AS signup_date
|
|
FROM 'raw_users.csv'
|
|
WHERE email IS NOT NULL;
|
|
|
|
-- Stage 2: Aggregate
|
|
CREATE TABLE user_stats AS
|
|
SELECT
|
|
DATE_TRUNC('month', signup_date) AS month,
|
|
COUNT(*) AS signups,
|
|
AVG(age) AS avg_age
|
|
FROM clean_users
|
|
GROUP BY month
|
|
ORDER BY month;
|
|
""")
|
|
|
|
# Export final result
|
|
result = con.execute("SELECT * FROM user_stats").df()</code></pre>
|
|
<h3>Pattern 4: Advanced Analytics</h3>
|
|
<p><strong>Window Functions</strong>:</p>
|
|
<pre><code class="lang-sql">SELECT
|
|
user_id,
|
|
order_date,
|
|
amount,
|
|
-- Running total
|
|
SUM(amount) OVER (
|
|
PARTITION BY user_id
|
|
ORDER BY order_date
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
|
) AS running_total,
|
|
-- Moving average
|
|
AVG(amount) OVER (
|
|
PARTITION BY user_id
|
|
ORDER BY order_date
|
|
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
|
|
) AS moving_avg_7d,
|
|
-- Rank within partition
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY user_id
|
|
ORDER BY amount DESC
|
|
) AS amount_rank
|
|
FROM orders;</code></pre>
|
|
<p><strong>CTEs and Complex Queries</strong>:</p>
|
|
<pre><code class="lang-sql">WITH monthly_sales AS (
|
|
SELECT
|
|
DATE_TRUNC('month', order_date) AS month,
|
|
product_id,
|
|
SUM(quantity) AS units_sold,
|
|
SUM(revenue) AS total_revenue
|
|
FROM sales
|
|
GROUP BY month, product_id
|
|
),
|
|
ranked_products AS (
|
|
SELECT
|
|
month,
|
|
product_id,
|
|
total_revenue,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY month
|
|
ORDER BY total_revenue DESC
|
|
) AS revenue_rank
|
|
FROM monthly_sales
|
|
)
|
|
SELECT
|
|
month,
|
|
product_id,
|
|
total_revenue,
|
|
revenue_rank
|
|
FROM ranked_products
|
|
WHERE revenue_rank <= 10
|
|
ORDER BY month DESC, revenue_rank;</code></pre>
|
|
<p><strong>Pivot and Unpivot</strong>:</p>
|
|
<pre><code class="lang-sql">-- Pivot
|
|
PIVOT (
|
|
SELECT category, year, revenue
|
|
FROM sales
|
|
) ON year
|
|
USING SUM(revenue);
|
|
|
|
-- Unpivot
|
|
UNPIVOT (
|
|
SELECT * FROM quarterly_sales
|
|
) ON q1, q2, q3, q4
|
|
INTO NAME quarter VALUE revenue;</code></pre>
|
|
<h3>Pattern 5: Extensions</h3>
|
|
<p><strong>Installing Extensions</strong>:</p>
|
|
<pre><code class="lang-sql">-- HTTP/S3 file access
|
|
INSTALL httpfs;
|
|
LOAD httpfs;
|
|
|
|
-- PostgreSQL scanner
|
|
INSTALL postgres_scanner;
|
|
LOAD postgres_scanner;
|
|
|
|
-- Spatial/GIS
|
|
INSTALL spatial;
|
|
LOAD spatial;
|
|
|
|
-- Full-text search
|
|
INSTALL fts;
|
|
LOAD fts;
|
|
|
|
-- Excel files
|
|
INSTALL spatial; -- Includes Excel support
|
|
LOAD spatial;</code></pre>
|
|
<p><strong>Using Extensions</strong>:</p>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
con = duckdb.connect()
|
|
|
|
# Read from PostgreSQL
|
|
con.execute("INSTALL postgres_scanner")
|
|
con.execute("LOAD postgres_scanner")
|
|
|
|
result = con.execute("""
|
|
SELECT * FROM postgres_scan(
|
|
'host=localhost port=5432 dbname=mydb user=user password=pass',
|
|
'public',
|
|
'users'
|
|
)
|
|
""").df()
|
|
|
|
# Read from S3
|
|
con.execute("INSTALL httpfs")
|
|
con.execute("LOAD httpfs")
|
|
con.execute("""
|
|
SET s3_access_key_id='YOUR_KEY';
|
|
SET s3_secret_access_key='YOUR_SECRET';
|
|
""")
|
|
|
|
df = con.execute("SELECT * FROM 's3://bucket/data.parquet'").df()
|
|
|
|
# Spatial queries
|
|
con.execute("INSTALL spatial")
|
|
con.execute("LOAD spatial")
|
|
|
|
con.execute("""
|
|
SELECT
|
|
city,
|
|
ST_Distance(
|
|
ST_Point(lon, lat),
|
|
ST_Point(-74.006, 40.7128) -- NYC coordinates
|
|
) AS distance_from_nyc
|
|
FROM cities
|
|
ORDER BY distance_from_nyc
|
|
LIMIT 10;
|
|
""")</code></pre>
|
|
<h3>Pattern 6: Performance Optimization</h3>
|
|
<p><strong>Persistent Database with Indexes</strong>:</p>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
con = duckdb.connect('analytics.db')
|
|
|
|
# Create table
|
|
con.execute("""
|
|
CREATE TABLE events (
|
|
event_id INTEGER,
|
|
user_id INTEGER,
|
|
event_type VARCHAR,
|
|
timestamp TIMESTAMP,
|
|
data JSON
|
|
)
|
|
""")
|
|
|
|
# Load data
|
|
con.execute("INSERT INTO events SELECT * FROM 'events.parquet'")
|
|
|
|
# Create indexes (ART - Adaptive Radix Tree)
|
|
con.execute("CREATE INDEX idx_user ON events(user_id)")
|
|
con.execute("CREATE INDEX idx_timestamp ON events(timestamp)")</code></pre>
|
|
<p><strong>Query Optimization</strong>:</p>
|
|
<pre><code class="lang-python"># Use EXPLAIN to analyze query plan
|
|
plan = con.execute("EXPLAIN SELECT * FROM events WHERE user_id = 123").fetchall()
|
|
print(plan)
|
|
|
|
# Use PRAGMA for optimization settings
|
|
con.execute("PRAGMA threads=8") # Use 8 threads
|
|
con.execute("PRAGMA memory_limit='4GB'") # Set memory limit</code></pre>
|
|
<p><strong>Partitioned Data</strong>:</p>
|
|
<pre><code class="lang-sql">-- Export partitioned by date
|
|
COPY (SELECT * FROM events)
|
|
TO 'events_partitioned'
|
|
(FORMAT PARQUET, PARTITION_BY (DATE_TRUNC('day', timestamp)));
|
|
|
|
-- Query specific partitions
|
|
SELECT * FROM 'events_partitioned/timestamp=2024-01-*/*.parquet';</code></pre>
|
|
<h3>Pattern 7: WASM/Browser Deployment</h3>
|
|
<p><strong>DuckDB in Browser</strong>:</p>
|
|
<pre><code class="lang-html"><!DOCTYPE html>
|
|
<html>
|
|
<head>
|
|
<script src="https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@latest/dist/duckdb-mvp.wasm.js"></script>
|
|
</head>
|
|
<body>
|
|
<script type="module">
|
|
import * as duckdb from 'https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@latest/dist/duckdb-mvp.wasm.js';
|
|
|
|
const WASM_URL = 'https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@latest/dist/duckdb-mvp.wasm';
|
|
const worker = new Worker('duckdb-browser.worker.js');
|
|
const logger = new duckdb.ConsoleLogger();
|
|
const db = new duckdb.AsyncDuckDB(logger, worker);
|
|
await db.instantiate(WASM_URL);
|
|
|
|
const conn = await db.connect();
|
|
|
|
// Query CSV from URL
|
|
await conn.query(`
|
|
SELECT * FROM 'https://example.com/data.csv'
|
|
LIMIT 10
|
|
`);
|
|
|
|
const result = await conn.query(`
|
|
SELECT COUNT(*) as count FROM 'https://example.com/data.csv'
|
|
`);
|
|
console.log(result.toArray());
|
|
</script>
|
|
</body>
|
|
</html></code></pre>
|
|
<h3>Pattern 8: Full-Text Search</h3>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
con = duckdb.connect()
|
|
con.execute("INSTALL fts")
|
|
con.execute("LOAD fts")
|
|
|
|
# Create FTS index
|
|
con.execute("""
|
|
CREATE TABLE documents (
|
|
id INTEGER,
|
|
title VARCHAR,
|
|
content VARCHAR
|
|
)
|
|
""")
|
|
|
|
con.execute("INSERT INTO documents VALUES (1, 'DuckDB Guide', 'DuckDB is an analytics database')")
|
|
con.execute("INSERT INTO documents VALUES (2, 'SQL Tutorial', 'Learn SQL with examples')")
|
|
|
|
# Create full-text search index
|
|
con.execute("""
|
|
PRAGMA create_fts_index('documents', 'id', 'title', 'content')
|
|
""")
|
|
|
|
# Search
|
|
results = con.execute("""
|
|
SELECT * FROM (
|
|
SELECT *, fts_main_documents.match_bm25(id, 'analytics') AS score
|
|
FROM documents
|
|
) sq
|
|
WHERE score IS NOT NULL
|
|
ORDER BY score DESC
|
|
""").fetchall()</code></pre>
|
|
<hr>
|
|
<h2>Quick Reference</h2>
|
|
<h3>CLI Commands</h3>
|
|
<pre><code class="lang-bash"># Start interactive shell
|
|
duckdb
|
|
|
|
# Open database file
|
|
duckdb analytics.db
|
|
|
|
# Execute query and exit
|
|
duckdb -c "SELECT * FROM 'data.csv'" analytics.db
|
|
|
|
# Run SQL file
|
|
duckdb analytics.db < script.sql
|
|
|
|
# Export to CSV
|
|
duckdb -c "COPY (SELECT * FROM table) TO 'output.csv' (HEADER)" analytics.db</code></pre>
|
|
<h3>Python API</h3>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
# Connect
|
|
con = duckdb.connect() # In-memory
|
|
con = duckdb.connect('file.db') # Persistent
|
|
|
|
# Execute
|
|
con.execute("SELECT 42")
|
|
result = con.execute("SELECT * FROM table").fetchall()
|
|
df = con.execute("SELECT * FROM table").df() # Pandas
|
|
pl_df = con.execute("SELECT * FROM table").pl() # Polars
|
|
arrow = con.execute("SELECT * FROM table").arrow() # Arrow
|
|
|
|
# Query shorthand
|
|
duckdb.query("SELECT * FROM df").df() # df is Pandas DataFrame
|
|
|
|
# Close
|
|
con.close()</code></pre>
|
|
<h3>Common Functions</h3>
|
|
<pre><code class="lang-sql">-- Date/Time
|
|
DATE_TRUNC('month', timestamp_col)
|
|
EXTRACT(year FROM date_col)
|
|
AGE(end_date, start_date)
|
|
|
|
-- String
|
|
CONCAT(str1, str2)
|
|
LOWER(str), UPPER(str)
|
|
REGEXP_MATCHES(str, pattern)
|
|
|
|
-- Aggregate
|
|
COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)
|
|
STDDEV(col), VARIANCE(col)
|
|
STRING_AGG(col, ',') -- Concatenate strings
|
|
ARRAY_AGG(col) -- Collect into array
|
|
|
|
-- Window
|
|
ROW_NUMBER(), RANK(), DENSE_RANK()
|
|
LAG(col, 1), LEAD(col, 1)
|
|
FIRST_VALUE(col), LAST_VALUE(col)
|
|
|
|
-- JSON
|
|
json_col->'key' -- Extract JSON field
|
|
json_col->>'key' -- Extract as text
|
|
JSON_EXTRACT(json_col, '$.path')</code></pre>
|
|
<h3>Data Import/Export</h3>
|
|
<pre><code class="lang-sql">-- CSV
|
|
CREATE TABLE t AS SELECT * FROM read_csv_auto('file.csv');
|
|
COPY table TO 'output.csv' (HEADER, DELIMITER ',');
|
|
|
|
-- Parquet
|
|
CREATE TABLE t AS SELECT * FROM 'file.parquet';
|
|
COPY table TO 'output.parquet' (FORMAT PARQUET);
|
|
|
|
-- JSON
|
|
CREATE TABLE t AS SELECT * FROM read_json_auto('file.json');
|
|
COPY table TO 'output.json' (ARRAY true);
|
|
|
|
-- Excel
|
|
INSTALL spatial;
|
|
LOAD spatial;
|
|
SELECT * FROM st_read('file.xlsx');</code></pre>
|
|
<h3>Configuration</h3>
|
|
<pre><code class="lang-sql">-- Threads
|
|
PRAGMA threads=8;
|
|
|
|
-- Memory limit
|
|
PRAGMA memory_limit='4GB';
|
|
|
|
-- Progress bar
|
|
PRAGMA enable_progress_bar;
|
|
|
|
-- Show settings
|
|
PRAGMA database_size;
|
|
PRAGMA table_info('table_name');</code></pre>
|
|
<hr>
|
|
<h2>Anti-Patterns</h2>
|
|
<h3>Don't Use DuckDB for OLTP</h3>
|
|
<pre><code class="lang-python"># WRONG: High-frequency transactional updates
|
|
for user_id in range(10000):
|
|
con.execute(f"UPDATE users SET last_active = NOW() WHERE id = {user_id}")
|
|
|
|
# RIGHT: Use PostgreSQL/MySQL for OLTP, or batch updates
|
|
con.execute("""
|
|
UPDATE users
|
|
SET last_active = NOW()
|
|
WHERE id IN (SELECT id FROM active_users)
|
|
""")</code></pre>
|
|
<h3>Don't Import Data Unnecessarily</h3>
|
|
<pre><code class="lang-sql">-- WRONG: Import before querying
|
|
CREATE TABLE data AS SELECT * FROM 'large_file.parquet';
|
|
SELECT AVG(value) FROM data;
|
|
|
|
-- RIGHT: Query directly
|
|
SELECT AVG(value) FROM 'large_file.parquet';</code></pre>
|
|
<h3>Don't Ignore Query Plans</h3>
|
|
<pre><code class="lang-python"># WRONG: Run slow query without investigation
|
|
result = con.execute("SELECT * FROM huge_table WHERE col = 'value'").fetchall()
|
|
|
|
# RIGHT: Analyze and optimize
|
|
plan = con.execute("EXPLAIN SELECT * FROM huge_table WHERE col = 'value'").fetchall()
|
|
# Create index if needed
|
|
con.execute("CREATE INDEX idx_col ON huge_table(col)")</code></pre>
|
|
<h3>Don't Mix Multiple Connections Unsafely</h3>
|
|
<pre><code class="lang-python"># WRONG: Multiple connections to same file without coordination
|
|
con1 = duckdb.connect('db.db')
|
|
con2 = duckdb.connect('db.db')
|
|
con1.execute("INSERT INTO table VALUES (1)")
|
|
con2.execute("INSERT INTO table VALUES (2)") # Potential conflict
|
|
|
|
# RIGHT: Use single connection or read-only mode
|
|
con = duckdb.connect('db.db')
|
|
# Or
|
|
con_readonly = duckdb.connect('db.db', read_only=True)</code></pre>
|
|
<h3>Don't Forget to Close Connections</h3>
|
|
<pre><code class="lang-python"># WRONG: Leave connections open
|
|
con = duckdb.connect('file.db')
|
|
con.execute("SELECT * FROM table")
|
|
# ... never close
|
|
|
|
# RIGHT: Use context manager
|
|
with duckdb.connect('file.db') as con:
|
|
result = con.execute("SELECT * FROM table").df()</code></pre>
|
|
<h3>Don't Use SELECT * in Production</h3>
|
|
<pre><code class="lang-sql">-- WRONG: Fetch unnecessary columns
|
|
SELECT * FROM 'huge_file.parquet' WHERE id = 123;
|
|
|
|
-- RIGHT: Select only needed columns (columnar optimization)
|
|
SELECT id, name, email FROM 'huge_file.parquet' WHERE id = 123;</code></pre>
|
|
<hr>
|
|
<h2>Related Skills</h2>
|
|
<ul><li><strong>python</strong> - DuckDB Python API integration</li><li><strong>sql</strong> - SQL fundamentals, query optimization</li><li><strong>pandas-data-analysis</strong> - DataFrame integration patterns</li><li><strong>parquet-columnar</strong> - Parquet file format, columnar storage</li><li><strong>modal-functions-basics</strong> - Deploy DuckDB analytics on Modal</li><li><strong>data-pipeline-design</strong> - ETL patterns, data workflows</li><li><strong>postgresql</strong> - PostgreSQL scanner, SQL compatibility</li><li><strong>arrow-interop</strong> - Apache Arrow integration</li></ul>
|
|
<hr>
|
|
<h2>Examples</h2>
|
|
<h3>Example 1: Sales Analytics Pipeline</h3>
|
|
<pre><code class="lang-python">import duckdb
|
|
import pandas as pd
|
|
|
|
# Connect to persistent database
|
|
con = duckdb.connect('sales_analytics.db')
|
|
|
|
# Load raw data from multiple sources
|
|
con.execute("""
|
|
CREATE OR REPLACE TABLE raw_sales AS
|
|
SELECT * FROM 's3://mybucket/sales/*.parquet'
|
|
""")
|
|
|
|
con.execute("""
|
|
CREATE OR REPLACE TABLE raw_customers AS
|
|
SELECT * FROM postgres_scan(
|
|
'host=db.example.com dbname=prod',
|
|
'public',
|
|
'customers'
|
|
)
|
|
""")
|
|
|
|
# Transform and aggregate
|
|
con.execute("""
|
|
CREATE OR REPLACE TABLE sales_summary AS
|
|
WITH daily_sales AS (
|
|
SELECT
|
|
s.customer_id,
|
|
c.customer_name,
|
|
c.segment,
|
|
DATE_TRUNC('day', s.order_date) AS sale_date,
|
|
SUM(s.amount) AS daily_revenue,
|
|
COUNT(*) AS num_orders
|
|
FROM raw_sales s
|
|
JOIN raw_customers c ON s.customer_id = c.id
|
|
GROUP BY s.customer_id, c.customer_name, c.segment, sale_date
|
|
)
|
|
SELECT
|
|
customer_id,
|
|
customer_name,
|
|
segment,
|
|
sale_date,
|
|
daily_revenue,
|
|
num_orders,
|
|
SUM(daily_revenue) OVER (
|
|
PARTITION BY customer_id
|
|
ORDER BY sale_date
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
|
) AS cumulative_revenue
|
|
FROM daily_sales
|
|
ORDER BY customer_id, sale_date
|
|
""")
|
|
|
|
# Export results
|
|
con.execute("""
|
|
COPY sales_summary TO 'sales_summary.parquet' (FORMAT PARQUET)
|
|
""")
|
|
|
|
# Get insights as DataFrame for visualization
|
|
insights = con.execute("""
|
|
SELECT
|
|
segment,
|
|
COUNT(DISTINCT customer_id) AS customers,
|
|
SUM(daily_revenue) AS total_revenue,
|
|
AVG(daily_revenue) AS avg_daily_revenue
|
|
FROM sales_summary
|
|
GROUP BY segment
|
|
ORDER BY total_revenue DESC
|
|
""").df()
|
|
|
|
print(insights)</code></pre>
|
|
<h3>Example 2: Log Analysis</h3>
|
|
<pre><code class="lang-python">import duckdb
|
|
|
|
con = duckdb.connect()
|
|
|
|
# Query logs directly (JSON Lines format)
|
|
con.execute("""
|
|
CREATE VIEW parsed_logs AS
|
|
SELECT
|
|
timestamp::TIMESTAMP AS ts,
|
|
level,
|
|
message,
|
|
user_id,
|
|
request_path
|
|
FROM read_json_auto('logs/*.jsonl')
|
|
WHERE timestamp IS NOT NULL
|
|
""")
|
|
|
|
# Find error patterns
|
|
errors = con.execute("""
|
|
SELECT
|
|
DATE_TRUNC('hour', ts) AS hour,
|
|
level,
|
|
COUNT(*) AS count,
|
|
STRING_AGG(DISTINCT request_path, ', ') AS paths
|
|
FROM parsed_logs
|
|
WHERE level IN ('ERROR', 'CRITICAL')
|
|
GROUP BY hour, level
|
|
ORDER BY hour DESC, count DESC
|
|
LIMIT 20
|
|
""").df()
|
|
|
|
print("Error Summary:")
|
|
print(errors)
|
|
|
|
# User activity analysis
|
|
activity = con.execute("""
|
|
SELECT
|
|
user_id,
|
|
COUNT(*) AS requests,
|
|
COUNT(DISTINCT request_path) AS unique_paths,
|
|
MIN(ts) AS first_seen,
|
|
MAX(ts) AS last_seen
|
|
FROM parsed_logs
|
|
WHERE user_id IS NOT NULL
|
|
GROUP BY user_id
|
|
HAVING requests > 100
|
|
ORDER BY requests DESC
|
|
LIMIT 10
|
|
""").df()
|
|
|
|
print("\nTop Active Users:")
|
|
print(activity)</code></pre>
|
|
<hr>
|
|
<p><strong>End of Skill: duckdb-analytics</strong></p>
|
|
<footer>
|
|
<div class="fmap">
|
|
<div><b>Build</b>
|
|
<a href="https://hanzo.app">hanzo.app</a> app builder
|
|
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
|
|
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
|
|
<div><b>Run</b>
|
|
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
|
|
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
|
|
<div><b>Account</b>
|
|
<a href="https://hanzo.id">hanzo.id</a> sign in
|
|
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
|
|
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
|
|
<div><b>Company</b>
|
|
<a href="https://hanzo.ai">hanzo.ai</a> canonical
|
|
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
|
|
</div>
|
|
<div class="fend"><a href="/duckdb-analytics.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
|
|
</footer>
|
|
</div></body></html> |