Every Monday our growth team asks the same shape of question: "Which languages are trending in Vietnam this week, and how does watch-through there compare to Japan and Taiwan over the last 30 days?" On TopVideoHub we aggregate trending videos across a dozen Asia-Pacific regions in CJK and Southeast Asian languages, and the operational data lives in SQLite. SQLite is superb for serving pages under LiteSpeed behind Cloudflare, but it is the wrong tool for pivot-heavy, multi-region rollups over tens of millions of rows. Every time an analyst ran one of those queries against the live database, page latency spiked and I got paged.
The fix that stuck was boring and cheap: export the tables to Parquet on a schedule, and point DuckDB at the files for ad-hoc analytics. No analytics database to run, no ETL cluster, no separate warehouse to keep in sync. This post walks through exactly how that pipeline works, with runnable code, and the gotchas that bit us with CJK text and timezones.
Why SQLite is the wrong engine for this workload
Our videos table is a classic row store optimized for point lookups: fetch one video by ID, list the 40 trending items for a region, run an FTS5 match for search. SQLite handles all of that in single-digit milliseconds because those queries touch a handful of pages.
Analytics queries are the opposite. "Average watch-through per language per region per day" scans the entire table, groups on three columns, and aggregates. In a row store the engine reads every column of every row even though the query only needs four of them. On a 20-million-row table that is hundreds of megabytes of I/O for a single GROUP BY, and while it runs it competes with the request traffic for the same file locks and page cache.
You can bolt indexes on, but you cannot index your way out of a full-table aggregation that an analyst rewrites five different ways in an afternoon. What that workload wants is a columnar engine that reads only the columns it needs, in a format built for scans.
Why DuckDB instead of a real warehouse
The obvious answers are Postgres with a columnar extension, ClickHouse, or a cloud warehouse. All of them are more machine than this problem deserves. Our analytics volume is a few queries a day from three people. Standing up ClickHouse to answer that is like renting a forklift to carry groceries.
DuckDB is an in-process columnar OLAP engine — think "SQLite for analytics." There is no server, no port, no daemon. It is a single binary (or a Python/Node/Go library) that reads Parquet, CSV, and JSON directly off disk, executes vectorized SQL, and exits. For our shape of work it has three properties that matter:
- It reads Parquet natively and only touches the columns and row groups a query needs, thanks to column projection and predicate pushdown.
- It runs the same ANSI-ish SQL locally that you would write against a warehouse, so analysts do not learn a new dialect.
- It has zero operational footprint. The "cluster" is a cron job that writes files.
Parquet is the other half. It is a columnar file format with per-column compression and embedded min/max statistics per row group. When DuckDB runs WHERE region = 'VN', it reads the row-group metadata, skips every group whose stats prove no VN rows exist, and never decompresses them. That is how you scan "20 million rows" while actually touching two.
Exporting SQLite to Parquet
The export is the only moving part you own. I run it with Python because pyarrow gives precise control over types and partitioning, but you could equally use the DuckDB CLI to do the copy in one statement.
The key decisions are: pull in chunks so you never load the whole table into memory, coerce timestamps to a real timestamp type, and partition the output by the columns you filter on most — for us, region and event date.
#!/usr/bin/env python3
"""Export SQLite tables to partitioned Parquet for DuckDB analytics."""
import sqlite3
import pyarrow as pa
import pyarrow.parquet as pq
DB_PATH = "/var/lib/topvideohub/videos.sqlite"
OUT_DIR = "/var/lib/topvideohub/analytics/videos"
CHUNK = 100_000
SCHEMA = pa.schema([
("video_id", pa.string()),
("region", pa.string()),
("language", pa.string()),
("title", pa.string()),
("view_count", pa.int64()),
("watch_seconds", pa.int64()),
("duration_seconds", pa.int64()),
("event_date", pa.string()), # YYYY-MM-DD, used as partition key
("fetched_at", pa.timestamp("us", tz="UTC")),
])
def rows():
con = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
con.row_factory = sqlite3.Row
cur = con.execute(
"""SELECT video_id, region, language, title,
view_count, watch_seconds, duration_seconds,
date(fetched_at) AS event_date,
strftime('%Y-%m-%dT%H:%M:%SZ', fetched_at) AS fetched_at
FROM videos"""
)
while batch := cur.fetchmany(CHUNK):
yield batch
con.close()
def main():
writer = pq.ParquetDataset # placeholder to keep import used
for i, batch in enumerate(rows()):
table = pa.Table.from_pylist([dict(r) for r in batch], schema=SCHEMA)
pq.write_to_dataset(
table,
root_path=OUT_DIR,
partition_cols=["region", "event_date"],
compression="zstd",
existing_data_behavior="overwrite_or_ignore",
)
print(f"wrote chunk {i}: {table.num_rows} rows")
if __name__ == "__main__":
main()
The output lands in a Hive-style directory tree: videos/region=VN/event_date=2026-06-30/part-0.parquet. DuckDB understands this layout automatically and turns the directory names into queryable columns. ZSTD compression on our text-heavy rows gives roughly a 6x reduction over the raw SQLite pages, which matters because these files get read straight off local disk on the same box.
One detail worth calling out: I export event_date as a plain string partition key rather than a timestamp. Partition values become directory names, and you want those human-readable and filesystem-safe. The full fetched_at timestamp stays inside the file as a real timestamp type so time-of-day analysis still works.
Querying Parquet directly with DuckDB
With the files on disk, DuckDB needs no import step. You point it at a glob and query. This is the whole "load" phase — there isn't one.
-- Trending languages in Vietnam this week, weighted by watch-through
SELECT
language,
COUNT(*) AS videos,
SUM(view_count) AS total_views,
ROUND(AVG(watch_seconds * 1.0
/ NULLIF(duration_seconds, 0)), 3) AS avg_watch_through
FROM read_parquet('/var/lib/topvideohub/analytics/videos/**/*.parquet',
hive_partitioning = true)
WHERE region = 'VN'
AND event_date >= '2026-06-24'
GROUP BY language
HAVING videos > 50
ORDER BY avg_watch_through DESC
LIMIT 20;
Because the data is partitioned by region and event_date, the WHERE clause is pure pruning: DuckDB never opens a single file outside region=VN and never touches partitions older than the 24th. On our dataset this query returns in well under a second while scanning what would be millions of logical rows, because it physically reads only the Vietnamese partitions for the last seven days and only the four columns the query references.
Run it from the CLI with duckdb -c "...", or from any language binding. The SQL is identical everywhere, which is the point — analysts prototype in the CLI, and the exact query gets embedded into a dashboard later with no translation.
Wiring DuckDB into a PHP 8.4 stack
Our app is PHP 8.4 under LiteSpeed. There is no mature native DuckDB PHP extension I trust in production, so I take the pragmatic route: shell out to the DuckDB CLI and ask it to emit JSON. This keeps the analytics engine completely out of the request-serving PHP process — the CLI runs, produces JSON, and exits, so a heavy query can never wedge a LiteSpeed worker.
<?php
declare(strict_types=1);
final class DuckAnalytics
{
public function __construct(
private string $duckdbBin = '/usr/local/bin/duckdb',
private string $glob = '/var/lib/topvideohub/analytics/videos/**/*.parquet',
) {}
/** @return list<array<string,mixed>> */
public function languageTrends(string $region, string $sinceDate): array
{
$sql = <<<SQL
SELECT language,
COUNT(*) AS videos,
SUM(view_count) AS total_views,
ROUND(AVG(watch_seconds * 1.0
/ NULLIF(duration_seconds, 0)), 3) AS avg_watch_through
FROM read_parquet(?, hive_partitioning => true)
WHERE region = ? AND event_date >= ?
GROUP BY language
ORDER BY total_views DESC
LIMIT 30;
SQL;
return $this->query($sql, [$this->glob, $region, $sinceDate]);
}
/** @param list<string> $params */
private function query(string $sql, array $params): array
{
// DuckDB CLI supports positional parameters via -c with .parameter,
// but the robust path is to bind by escaping into a prepared file.
$bound = $sql;
foreach ($params as $p) {
$bound = preg_replace('/\?/', "'" . str_replace("'", "''", $p) . "'", $bound, 1);
}
$cmd = escapeshellarg($this->duckdbBin) . ' -json -c ' . escapeshellarg($bound);
$out = shell_exec($cmd . ' 2>/dev/null');
if ($out === null || $out === '') {
return [];
}
$rows = json_decode($out, true, flags: JSON_THROW_ON_ERROR);
return is_array($rows) ? $rows : [];
}
}
// Usage from a controller
$analytics = new DuckAnalytics();
$trends = $analytics->languageTrends('VN', '2026-06-24');
header('Content-Type: application/json; charset=utf-8');
echo json_encode($trends, JSON_UNESCAPED_UNICODE);
A few production notes. Manual string interpolation into SQL is normally a cardinal sin; here the inputs are constrained (region codes and ISO dates that I validate upstream), and I still escape single quotes defensively. If your inputs are less constrained, write the parameters to a temp .sql file using DuckDB's PREPARE/EXECUTE and pass them via .parameter set commands instead of interpolating. Also note JSON_UNESCAPED_UNICODE on the encode — without it, PHP mangles CJK titles into \uXXXX escapes that bloat the payload. I cache these results in the same data cache we use for pages, because analytics answers change only when a new export lands, not on every request.
Real rollups the growth team actually runs
The questions get more interesting than a single region. A common one is a cross-region comparison of how sticky each language is, which is a GROUP BY over two dimensions plus a computed ratio. DuckDB's window functions and PIVOT make this concise, and running it from Python lets us drop the result straight into a notebook or a Slack post.
import duckdb
con = duckdb.connect() # in-memory; no database file needed
con.execute("SET threads TO 4;")
GLOB = "/var/lib/topvideohub/analytics/videos/**/*.parquet"
sql = f"""
WITH base AS (
SELECT region, language,
watch_seconds, duration_seconds
FROM read_parquet('{GLOB}', hive_partitioning => true)
WHERE event_date >= '2026-06-01'
AND region IN ('JP', 'KR', 'TW', 'VN', 'TH')
AND duration_seconds > 0
)
SELECT region,
language,
COUNT(*) AS videos,
ROUND(AVG(watch_seconds * 1.0 / duration_seconds), 3) AS retention,
ROUND(quantile_cont(watch_seconds * 1.0 / duration_seconds, 0.9), 3) AS p90_retention
FROM base
GROUP BY region, language
HAVING videos >= 100
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY retention DESC) <= 5
ORDER BY region, retention DESC;
"""
df = con.execute(sql).df() # returns a pandas DataFrame
print(df.to_string(index=False))
That single statement uses QUALIFY — a clause most row stores do not have — to keep only the top five languages per region by retention, computes a 90th-percentile retention with quantile_cont, and hands back a DataFrame ready for plotting. There is no import step, no schema declaration, no server connection string. duckdb.connect() with no argument is a purely in-memory session that reads the files and forgets everything when the process ends. The first time I showed an analyst that the entire "data warehouse" was a glob string in a query, they did not believe the numbers until they cross-checked against the app.
When a query pattern becomes routine, I promote it from a raw glob to a persistent view so nobody has to remember the path:
CREATE OR REPLACE VIEW videos AS
SELECT * FROM read_parquet(
'/var/lib/topvideohub/analytics/videos/**/*.parquet',
hive_partitioning => true
);
-- Saved into a small .duckdb catalog file; the data still lives in Parquet.
Now analysts write SELECT ... FROM videos and the partitioning is invisible.
A Go worker to run the export on a schedule
The Python exporter runs from cron, but I also wanted a small supervised worker that triggers exports after each trending-fetch cycle and records how long they took. Go compiles to a single static binary that our ops host runs without a Python environment, which keeps the analytics box dependency-free.
package main
import (
"log"
"os"
"os/exec"
"time"
)
func runExport() error {
start := time.Now()
cmd := exec.Command("python3", "/opt/topvideohub/export_parquet.py")
cmd.Stdout = os.Stdout
cmd.Stderr = os.Stderr
if err := cmd.Run(); err != nil {
return err
}
log.Printf("export finished in %s", time.Since(start).Round(time.Millisecond))
return nil
}
func main() {
// Run once on boot, then every hour aligned to the fetch cycle.
if err := runExport(); err != nil {
log.Fatalf("initial export failed: %v", err)
}
ticker := time.NewTicker(time.Hour)
defer ticker.Stop()
for range ticker.C {
if err := runExport(); err != nil {
log.Printf("export failed, will retry next tick: %v", err)
}
}
}
It is deliberately dumb — no queue, no retries beyond "try again next hour." Because Parquet exports are idempotent overwrites of a partition, a failed run costs nothing but one stale hour, and the next tick heals it. Idempotency is what lets you keep the orchestration this simple.
The gotchas that actually cost me time
CJK and tokenization stay in SQLite. DuckDB is for numeric and categorical rollups. Full-text relevance over Japanese and Chinese titles still belongs in our SQLite FTS5 index with the CJK tokenizer — DuckDB has no equivalent, and you do not want it to. Export the title column for display and grouping, but keep search where it belongs. Mixing the two responsibilities is a trap.
Timestamps need timezones baked in. SQLite stores whatever string you put in it. If you export naive local timestamps and then group by hour, you silently blend UTC and local time across regions and every trend looks smeared. I force everything to UTC at export time and only convert to a display timezone in the final SELECT with AT TIME ZONE. For an Asia-Pacific product spanning UTC+5:30 to UTC+9, this is not optional.
Let types be explicit. DuckDB infers Parquet types from the file schema, which is why the pyarrow SCHEMA object above is worth the extra lines. Letting view_count land as a string because one export chunk had a null turns SUM(view_count) into an error at the worst possible moment — mid-demo. Declaring the schema once removes a whole category of surprise.
Small files hurt. Writing a new part file every 100k rows per partition eventually litters the tree with thousands of tiny files, and DuckDB pays an open-file cost per read. Periodically I run a compaction pass — COPY (SELECT * FROM read_parquet(...)) TO ... (FORMAT parquet) — to rewrite each partition into one file per day. Schedule it weekly and forget about it.
Conclusion
The entire analytics stack here is three moving parts: a Python script that dumps SQLite to partitioned Parquet, a DuckDB binary that queries those files in place, and a thin PHP or Python caller that runs the SQL and caches the JSON. There is no server to babysit, no second copy of the data to reconcile, and no new query language for the team to learn. When the growth team asks which languages are winning in Vietnam this week, the answer is a sub-second glob over local files — and the live database serving pages under LiteSpeed never even notices the question was asked.
If your operational store is SQLite or Postgres and analysts keep melting it with GROUP BY, try this before you provision a warehouse. Export to Parquet, point DuckDB at it, and see how far the boring path takes you. For us it has comfortably outlasted every "we should really stand up a proper warehouse" conversation, because the honest answer keeps being: we don't need one yet.
Top comments (0)