duckdb

My agent managed to reach up to 99.7% storage savings on @ApacheParquet edits in @duckdb !! Using my fav technique, still unreleased duckdb#27911 t.co

The last month is always a lie: lessons from querying 1 billion rows of Brazilian crime data We run Crime Brasil, a site that turns official Brazilian public-safety data into maps and rankings for every neighbourhood and municipality in the country. Under the hood that is about 1 billion rows: police reports from state security departments, health-ministry mortality records, court and prison data, traffic and more. Most of the hard problems are not about scale. They are about data that looks right and isn't. Here are two rules we now apply before any number leaves the database, plus what they say about São Paulo in 2026. Rule 1: the last month in the file is always incomplete State police data arrives in batches. A report filed today may describe a crime from last week, so the newest month keeps growing for weeks after it "ends". Here is the São Paulo police-report feed by day, at the end of our current extract: | Day | Reports | |---|---| | 2026-08-26 | 2,459 | | 2026-08-27 | 2,413 | | 2026-08-28 | 2,275 | | 2026-08-29 | 2,061 | | 2026-08-30 | 1,665 | | 2026-08-31 | 994 | A normal day in São Paulo is 2,400 to 2,900 reports. If you compare "August 2026" with "August 2025" you get a drop in crime that is really a drop in paperwork. The check is one query. Count by day over the last two weeks and look for the tail: SELECT data_fato::date AS day, count(*) FROM crimes WHERE state = 'SP' AND data_fato::date >= (SELECT max(data_fato::date) - 14 FROM crimes WHERE state = 'SP') GROUP BY 1 ORDER BY 1; If the last days have half the volume of the days before, close your window one month earlier. We do this for every state, every time. Rule 2: keep huge microdata out of your main tables Some official datasets are far bigger than what a website needs. Brazil's hospital-admission records alone are over 120 million rows. Loading that into normal Postgres tables would eat disk and make backups crawl, for data the site touches a few times a day. So every dataset gets one of two homes: - Site-facing data lives in regular Postgres tables, partitioned by state, indexed for the queries the pages actually run. - Big research microdata stays as Parquet files on disk, queried from inside Postgres with pg_duckdb. Same SQL, same connection, no copy. The rule we enforce: never bulk-load a Parquet dataset into a heap table. When we need a slice for the site, we aggregate it first and store only the result. What São Paulo looks like in 2026 With both rules applied (January to July only, because August is still filling in), here is São Paulo state compared with the same months of 2025. These are counts of police reports from the SSP-SP open-data portal: | Crime | Jan–Jul 2025 | Jan–Jul 2026 | Change | |---|---|---|---| | Robbery (excluding vehicles, cargo and banks) | 96,706 | 80,180 | −17.1% | | Intentional homicide | 1,415 | 1,278 | −9.7% | | Robbery followed by death (latrocínio) | 84 | 47 | −44.0% | The robbery decline is not a one-month blip: the monthly count went from about 15,000 in mid-2024 to about 10,500 in July 2026, falling a little almost every month. You can explore the same data by city and neighbourhood on the São Paulo state page, or compare places in the rankings. Takeaways 1. Before you compute a change, prove the newest month is complete. 2. Decide where each dataset lives before you load it, not after the disk fills up. 3. Show your window. "January to July" is less exciting than "2026", and much harder to get wrong. If you work with Brazilian open data, I'd like to hear which datasets gave you the most trouble.

DuckDB GitHub Explained: Vectorized Analytics Without a Database Server duckdb by duckdb: duckdb/duckdb DuckDB is an in-process analytical SQL database for data work that outgrows memory-bound local tools but does not need a standalone database server. This overview explains how the engine embeds inside Python, R, or a command-line application, reads Parquet data with predicate pushdown, and skips irrelevant file blocks using metadata. The architecture story continues with columnar vectorized execution, 2,048-value morsels, adaptive parallelism, and Apache Arrow integration for a zero-copy handoff into Python workflows. The tradeoffs matter too: persistent files support multiple readers but enforce a single writer, while default memory and thread behavior can cause trouble in Docker or Kubernetes unless resource limits are configured explicitly. The overview also covers the security boundary around untrusted SQL and the need for operating-system isolation in external-facing applications. TimeStamps: 0:00 Large-File Analysis Hits a Memory Wall 0:25 Why Traditional Database Servers Add Overhead 0:54 DuckDB's In-Process Architecture 1:40 Predicate Pushdown for Parquet Files 2:21 Vectorized Execution and Morsel-Driven Parallelism 2:58 Apache Arrow and Zero-Copy Results 3:51 Concurrency and the Single-Writer Trade-off 4:18 Memory Limits in Containers 5:14 Security Risks of Untrusted SQL 5:40 Duck Lake and the Road Beyond Local Scripts 🧩 In-process design for local analytical SQL ⚡ Predicate pushdown, vectorized morsels, and Arrow zero-copy handoffs ⚠️ Single-writer, memory, container, and SQL security trade-offs 🌊 Duck Lake and cloud-storage architecture DuckDB fits analysts and data engineers who want local SQL over large files without standing up a database server. Its architecture rewards explicit resource limits in shared containers and careful isolation for untrusted SQL. The practical takeaway is to use vectorized execution and Arrow for efficient data paths while evaluating concurrency and deployment boundaries before scaling outward. #DuckDB #DataEngineering #ApacheArrow [de-DE] YNAS ynp_571f86f0ae270842 Cinematic Privater automatisierter Staging-Upload. Paketidentität: ynp_571f86f0ae270842; Ausgabe: cinematic. [es-US] Explicación de DuckDB en GitHub: Análisis vectorizado sin un servidor de base de datos DuckDB (de duckdb): duckdb/duckdb DuckDB es una base de datos SQL analítica integrada en el proceso, diseñada para el análisis de datos que supera las capacidades de las herramientas locales con limitaciones de memoria, pero que no requiere un servidor de base de datos independiente. Esta descripción general explica cómo el motor se integra en Python, R o una aplicación de línea de comandos, lee datos Parquet con optimización de predicados y omite bloques de archivos irrelevantes mediante metadatos. La arquitectura se complementa con ejecución vectorizada en columnas, lectura de fragmentos de 2048 valores, paralelismo adaptativo e integración con Apache Arrow para una transferencia sin copias a flujos de trabajo de Python. Las ventajas y desventajas también son importantes: los archivos persistentes admiten múltiples lectores, pero imponen un único escritor, mientras que el comportamiento predeterminado de la memoria y los subprocesos puede causar problemas en Docker o Kubernetes a menos que se configuren explícitamente los límites de recursos. Esta descripción general también aborda la seguridad del SQL no confiable y la necesidad de aislamiento del sistema operativo en aplicaciones externas. Marcas de tiempo: 0:00 El análisis de archivos grandes se topa con la limitación de memoria 0:25 Por qué los servidores de bases de datos tradicionales generan sobrecarga 0:54 Arquitectura en proceso de DuckDB 1:40 Optimización de predicados para archivos Parquet 2:21 Ejecución vectorizada y paralelismo basado en fragmentos 2:58 Apache Arrow y resultados sin copia 3:51 Concurrencia y la disyuntiva del escritor único 4:18 Límites de memoria en contenedores 5:14 Riesgos de seguridad del SQL no confiable 5:40 Duck Lake y el camino más allá de los scripts locales 🧩 Diseño en proceso para SQL analítico local ⚡ Optimización de predicados, fragmentos vectorizados y transferencias sin copia de Arrow ⚠️ Disyuntivas entre escritor único, memoria, contenedor y seguridad SQL 🌊 Arquitectura de Duck Lake y almacenamiento en la nube DuckDB es ideal para analistas e ingenieros de datos que prefieren SQL local Archivos grandes sin necesidad de configurar un servidor de base de datos. Su arquitectura prioriza los límites explícitos de recursos en contenedores compartidos y un aislamiento riguroso para SQL no confiable. La conclusión práctica es utilizar la ejecución vectorizada y Arrow para rutas de datos eficientes, evaluando la concurrencia y los límites de implementación antes de escalar. #DuckDB #IngenieríaDeDatos #ApacheArrow [fr-FR] YNAS ynp_571f86f0ae270842 cinématique Téléchargement de l'environnement de test d'automatisation privé. Identifiant du package : ynp_571f86f0ae270842 ; sortie : cinématique. [hi] YNAS ynp_571f86f0ae270842 सिनेमैटिक निजी स्वचालन स्टेजिंग अपलोड। पैकेज पहचान: ynp_571f86f0ae270842; आउटपुट: सिनेमाई। [id] YNAS ynp_571f86f0ae270842 sinematik Unggahan staging otomatisasi pribadi. Identitas paket: ynp_571f86f0ae270842; output: sinematik. [it] YNAS ynp_571f86f0ae270842 cinematografico Caricamento di automazione privata. Identità del pacchetto: ynp_571f86f0ae270842; output: cinematografico. [pt-BR] YNAS ynp_571f86f0ae270842 cinematográfico Upload de teste de automação privada. Identificação do pacote: ynp_571f86f0ae270842; saída: cinemática.

Shipped the Go client for @duckdb v2.0.0! 🎉 github.com/duckdb/duckdb-go/releases/tag/v2.20000…

DuckDB 2.0's agent mode is a footer, and -json throws it away --- title: DuckDB 2.0's agent mode is a footer, and -json throws it away published: true published_at: 2026-10-21 14:48 -0700 tags: ai, programming, opensource, productivity cover_image: beehiiv-images-production.s3.amazonaws.com --- The first time I ran DuckDB 2.0 today, it printed one line before my query: duckdb agent mode on: AI_AGENT is set and stdout is not a terminal; -no-agent turns it off, .help agent explains the output I hadn't asked for anything. Claude Code was driving my shell, and the CLI noticed. An hour later I had the result that matters more. The same 5,000-row query came back as 1,385 bytes in that mode, and as 246,729 bytes once I added -json. What shipped today DuckDB 2.0.0 "Cinnamon Teal" is out, and it is a long-term support release, supported until at least October 2027. The feature list is long: a client-server protocol called Quack, async I/O for Parquet and CSV, a first-class VARIANT type, triggers, a new PEG parser. It sat on the Hacker News front page this afternoon with close to 300 points, and the thread is mostly about speed on big machines, the server mode and triggers. I picked the feature nobody in that thread mentioned: agent mode in the CLI. Coding agents run duckdb -c "..." through a pipe all day, and 2.0 changes what they get back. It turns on when an agent's environment variable is set (my Claude Code shell has both AI_AGENT and CLAUDECODE), stdout is not a terminal, and no output format flag was passed. Then the CLI prints compact Markdown tables, errors as JSON, a cost estimate before big queries, and a footer. My setup: the official duckdb-cli-osx-arm64 binary from the v2.0.0 release (checksum matched, signed by the DuckDB Foundation), run from a scratch folder on an Apple M1 Max. The data was a seeded table of 2 million orders across 5,000 customers that I generated myself. The machine had other jobs running, with load averages between 30 and 98, so treat my timings as ranges. The bytes, counted honestly Seven queries an agent might run while exploring, each in three modes. Agent mode counts stderr too, since an agent reads both streams. query box agent mode -json status totals 864 396 308 orders by country 687 453 307 top 10 customers 1,876 1,033 1,179 per customer, 5k rows 2,561 1,385 246,729 SUMMARIZE orders 3,200 982 1,130 EXPLAIN a join 2,932 734 2,932 a typo'd column 206 384 206 total 12,326 5,367 252,791 In total, agent mode is 56% smaller than the box, close to the 59% token cut the DuckDB team measured on TPC-H questions. Two rows surprised me. On small results, plain JSON is as small or smaller, because agent mode pays a 124-byte startup line on every call. And errors get bigger: the JSON error is more precise (it carries the position) but nearly twice the size of the plain one. The team is candid that bytes are not the prize. In their Claude Code experiment, the query output the model read fell from 123.6k to 50.8k tokens, and that was about 0.5% of the total input, because the system prompt is reread every turn. So if agent mode matters, it has to matter for another reason. The footer is the feature Here is the bottom of that 5,000-row result in the old box format, which cuts to 40 rows: │ · │ · │ · │ │ 4980 │ 367 │ 95714.21 │ ... 5000 rows (40 shown) 3 columns And in agent mode: | 19 | 428 | 111142.39 | | … 4960 rows omitted … | | 4980 | 367 | 95714.21 | ... first 20 and last 20 of 5000 rows (.maxbytes 0 for all), hash e1cd177a85aae05e Both cut the middle. Only the second says so in a row a model can't skim past, tells it how to get the rest, and fingerprints the whole result, including the 4,960 rows it never printed. I poked at that hash. Three orderings of the same query gave the same hash, so it doesn't care about row order. A one-cent change to a single order out of 2 million flipped it. Undoing the cent brought the old hash back. NULL and the string 'NULL' hash differently, as documented. For an agent, "did my fix change the result?" becomes a comparison of two short strings instead of two printed tables. The other footer trick is the early stop. FROM range(1_000_000_000) in agent mode returned in 0.05 to 0.15 seconds over three runs, with 20 rows and "first 20 of at least 102400 rows (query stopped early)". The box took 2.3 to 3.4 seconds to tell me "1000000000 rows (40 shown)". One flag and it's gone Now the part I'd want to know before trusting any of this. Agents like JSON. It parses, and it's the flag I'd reach for myself when a script reads the output. Pass -json and agent mode stays off, by design: the docs treat a format flag as a deliberate choice. That per-customer query printed all 5,000 rows, 246,729 bytes, 178 times what agent mode sent. Forcing the mode back on doesn't do what I expected. The pull request that added it says -agent -csv keeps the mode and only changes the format. That is exactly what happened, and it's the catch. With -agent -json, the startup line and the estimate line came back on stderr, and then FROM range(1_000_000) printed every row: 17,888,891 bytes. -agent -csv printed 6,888,896. Plain -agent printed 229. Adding .maxrows 40 to a -json run changed nothing: still 17.9 MB. So the cap, the omitted-rows marker, the hash and the early stop all live in the Markdown renderer, not in "agent mode" as a whole. The flag that looks most agent-friendly is the one that throws the guard away. What I'd tell an agent If an agent of mine drives DuckDB, its instructions get three lines: - Call duckdb with no format flag while exploring, and let the default do its job. - When a program needs JSON, put a LIMIT in the SQL. Nothing else caps it. - To check whether an edit changed a result, compare footer hashes, and put SET max_execution_time in front of anything heavy. One LTS note, since this is the release teams will pin for a year. A database file created by 2.0 can't be read by older versions (existing files stay readable both ways). If an agent with a 2.0 CLI creates a new file, your 1.x services can't open it. Limits of my check I didn't run an agent benchmark. Every number above is the CLI's output on my data, not a model's behavior with it. The estimate line counts rows read, not rows produced: for a self-join of orders on customer, it said about 4 million rows read, while the join produced 802,046,608 pairs. It still finished in about a second here, but the line wouldn't have warned me. And this is a day-one CLI: issue #27635, filed today, reports a deprecation warning landing on stdout with color codes even under -csv. I reproduced it on my Mac. When your agent queries a database, does it ask for JSON, and would it notice if the answer were 17 MB?

note that if you want range queries in duckdb-wasm to work, you currently need to make sure that you disable `builtin_httpfs`, otherwise you can end up pulling a ton of data: duckdb-wasm#2228

DuckDB 2.0 LTS adds server mode, triggers and VARIANT DuckDB released version 2.0.0 on October 21, 2026, as a long-term support (LTS) release that the project will support until at least October 2027. It adds a built-in server mode, SQL triggers and a new parser. It also makes a one-way change: database files written by 2.0 cannot be opened by any earlier version, so upgrades need a plan. DuckDB is an open-source SQL database for analytics. Like SQLite, it runs inside your program rather than as a separate server. Unlike SQLite, it is built for big analytical queries over large tables and files such as Parquet and CSV. What DuckDB 2.0 adds The DuckDB announcement calls 2.0 the team's largest feature release so far, built from close to 20,000 commits since version 1.5. "DuckDB 2.0 is above all a feature release," the DuckDB team writes. The release is codenamed "Cinnamon Teal," after the duck species Spatula cyanoptera. The GitHub release uses the name "Cyanoptera." | Feature | What it does | | --- | --- | | Quack server mode | Runs DuckDB as a server that other clients connect to | | Triggers | CREATE TRIGGER runs SQL automatically, before or after a change to a table | | VARIANT type | Stores semi-structured data, such as JSON, and splits out its common structure for better compression and speed | | Async I/O | Reads Parquet and CSV files while queries run, with several requests in flight, mainly to speed up cloud storage like Amazon S3 | | New SQL parser | A PEG-based parser is now the default; extensions can add their own SQL syntax, and errors show where the problem is | | Native time zones | Time zones, calendars and collations no longer need the ICU library | A trigger is a rule stored in the database: when a row changes, run this SQL too. DuckDB 2.0 supports BEFORE and AFTER triggers, at row or statement level. Some combinations are not supported yet, such as a BEFORE ... FOR EACH ROW trigger on UPDATE. Quack turns DuckDB into a server Until now, DuckDB served one program at a time. With Quack, CALL quack_serve(...) starts a server, and a client connects with ATTACH 'quack:...'. A new CONNECT statement switches a whole session to a remote database. The command-line tool gains matching -serve and -connect flags. The server listens only on the local machine by default. For remote access, the DuckDB team recommends putting it behind a reverse proxy that handles TLS encryption. InfoWorld reported in August that Quack also makes it easier to query PostgreSQL and MySQL databases remotely. Breaking changes to check before upgrading According to the release post and the GitHub release notes, these changes can break existing code or builds: - Files are one-way. DuckDB 2.0 reads and writes existing database files. "DuckDB versions before 2.0 cannot read database files created by DuckDB 2.0," the team warns. - New parser by default. SET enable_legacy_parser = true; brings back the old one. - Single-arrow lambdas are off. Syntax like x -> x + 1 is disabled. SET lambda_syntax = 'enable_single_arrow'; turns it back on until version 2.1 removes it. - Integer division by zero now raises an error instead of returning NULL. - Builds need C++17, up from C++11, and release builds now use the Clang compiler. Amalgamation builds are deprecated. - Intel Macs are being phased out. Community extensions are no longer built for macOS on x86_64, and DuckDB 2.1 drops support. The sources disagree on one detail. InfoWorld's preview described a stable C API for extensions. The release post says the revamped C API v2 is "not yet marked as stable," because the team wants to test it in the field first. Support windows | Version | Supported until | | --- | --- | | DuckDB 2.0 (LTS) | At least October 2027 | | DuckDB 1.5 | March 2027 (extended) | | DuckDB 1.4 | Late November 2026 | What this means for developers Treat this as a planned migration, not a routine bump. - Copy your database files before the first open. Once DuckDB 2.0 writes to a file, an older tool or teammate on 1.5 can no longer read it. Upgrade every reader of a shared file together. - Test on a copy of production. The DuckDB team recommends a shadow system first. For mission-critical work, it suggests waiting for the first patch release, due in November. - Check your client. The command-line tool and Python client are ready now. Go, R and Java clients, and some extensions, "may take a few days," the team says. - Search your SQL for two patterns. Look for single-arrow lambdas and any integer division that could hit zero. Both behave differently now. - Do not expose a Quack server directly. Keep it on localhost, or put a TLS reverse proxy in front, as the team advises. - Teams on 1.4 should move first. Its support ends in late November 2026, so upgrade to 2.0, or to 1.5 for a smaller step. --- This article was first published on Tech AI Wire. Also available in Deutsch · 日本語 · Français · Español · Português Sources - Announcing DuckDB 2.0.0 - DuckDB - DuckDB v2.0.0 release notes - GitHub - DuckDB 2.0 coming this fall with client-server mode - InfoWorld

DuckDB 2.0 as a Mobile Analytics Engine: Embedding In-Process OLAP in Android and iOS Apps --- title: "DuckDB 2.0 LTS: Embedding In-Process OLAP in Android and iOS Apps" published: true description: "DuckDB 2.0 LTS brings production-grade embedded OLAP to Android and iOS — query local Parquet files without a network hop, backed by a stable release window." tags: android, ios, kotlin, architecture canonical_url: https://mvpfactory.co/blog/duckdb-2-mobile-analytics --- ## What we will build Let me show you a pattern I use in every project that does serious on-device analytics. By the end of this tutorial you will have DuckDB 2.0 LTS running inside an Android app via Kotlin and an iOS app via Swift Package Manager — querying local Parquet files with columnar performance that leaves SQLite in the dust on analytical workloads. --- ## Prerequisites - Android: Kotlin, Gradle, familiarity with JDBC - iOS: Swift 5.9+, Xcode 15+, SPM basics - Basic SQL knowledge - Some local Parquet files to query (or willingness to generate them) --- ## Why bother with embedded OLAP at all Every aggregation you route to the backend costs you latency, breaks offline scenarios, and generates backend load that scales with user engagement. The real question is *where does the data already live*. If it lives on device, move the query there. DuckDB's columnar execution model, vectorized query engine, and zero-dependency footprint make it the first credible candidate for this layer. The 2.0 LTS release gives you a stable dependency to ship against — that was the missing piece. --- ## Step 1 — Android: JNI bridging with Kotlin DuckDB ships a JVM artifact. Add it to your Gradle deps: dependencies { implementation("org.duckdb:duckdb_jdbc:2.0.0") } Here is the minimal setup to get this working: Class.forName("org.duckdb.DuckDBDriver") val conn = DriverManager.getConnection("jdbc:duckdb:/data/local/analytics.db") conn.createStatement().use { stmt -> val rs = stmt.executeQuery( "SELECT date_trunc('day', event_ts) AS day, COUNT(*) AS events " + "FROM read_parquet('/data/local/events/*.parquet') " + "GROUP BY 1 ORDER BY 1 DESC LIMIT 30" ) while (rs.next()) { Log.d("Analytics", "${rs.getString(1)}: ${rs.getLong(2)}") } } The JNI bridge adds negligible overhead for analytical queries — the bottleneck is I/O or the query itself, never the JVM boundary. Package `.so` for `arm64-v8a` and `x86_64` only; shipping `armeabi-v7a` for OLAP workloads is not worth the APK weight. --- ## Step 2 — iOS: Swift Package Manager Add the Swift package: .package(url: "duckdb/duckdb-swift", from: "2.0.0") import DuckDB let db = try Database(store: .file(atPath: analyticsPath)) let conn = try db.connect() let stmt = try conn.prepare(""" SELECT strftime('%Y-%m-%d', event_ts) AS day, COUNT(*) AS events FROM read_parquet(?) GROUP BY 1 ORDER BY 1 DESC LIMIT 30 """) let result = try stmt.execute(parquetGlob) Background thread execution via `async/await` or a `DispatchQueue` is mandatory here. You do not want columnar scans blocking the main run loop. --- ## Step 3 — Set your memory budget Mobile is not a server. Your process budget is typically 300–512 MB before the OOM killer gets interested. The docs do not always emphasize this, but setting these two lines is non-negotiable in production: SET memory_limit = '256MB'; SET temp_directory = '/data/local/tmp/duckdb_spill'; The spill directory lets DuckDB page intermediate results to disk under memory pressure. Reserve in-memory execution for foreground queries; allow disk spill for background aggregations. --- ## Performance baseline On a Pixel 7 (Android 14), against 1M rows: | Workload | SQLite | DuckDB 2.0 | |---|---|---| | COUNT + GROUP BY | ~1,800ms | ~90ms | | Filtered aggregation (10% selectivity) | ~900ms | ~35ms | | Multi-column window function | ~4,200ms | ~210ms | | Cold read, 50MB Parquet | ~600ms | ~180ms | The gains come from column pruning, SIMD-accelerated vector operations, and Parquet's native columnar layout aligning with DuckDB's execution model. --- ## Gotchas **Never interpolate file paths into query strings on iOS.** Always use prepared statement parameters — `parquetGlob` in the example above is derived from the app sandbox at init time. If it ever accepts external input, the prepared-parameter pattern is mandatory. **Cold-start latency.** DuckDB's first-query cold-start on a freshly opened connection adds perceptible latency. Pre-warm connections on app foreground if your latency budget is tight. **Do not use DuckDB for mutation-heavy workloads.** Frequent INSERT/UPDATE/DELETE patterns work against the engine's grain. Keep SQLite or Room for mutable app state; DuckDB owns the immutable analytics path. **Sub-10K rows?** SQLite is fast enough. The JNI/SPM overhead, binary size, and operational complexity do not pay off at low cardinality. --- ## Conclusion Three things to take away: 1. Adopt DuckDB 2.0 LTS for any mobile analytics feature with >100K rows. The columnar execution advantage over SQLite compounds significantly at this scale. 2. Set `memory_limit` explicitly and provide a spill directory. Do not let DuckDB discover your memory ceiling at runtime. 3. Structure data as append-only Parquet partitioned by time. This unlocks partition pruning and aligns perfectly with DuckDB's read path. The architecture is straightforward: SQLite/Room handles mutable app state, DuckDB + Parquet owns the analytics layer, and a background worker writes append-only Parquet files at the sync boundary. Clean separation, no lock contention, and the analytics layer becomes trivially replaceable.

CSV 太大，电子表格打开就卡？🦆 DuckDB 可直接用 SQL 查询 CSV、Parquet 文件，也能从 Python 调用。适合本地数据分析。💾 duckdb/duckdb #SQL #浇朋友 #蓝朋友 #互关 #fo

Query Millions of VoIP CDRs with DuckDB on Budget VPS Query Millions of VoIP CDRs with DuckDB on Budget VPS Extract and analyze call records from Asterisk and ViciDial in seconds on 1GB RAM by switching from SQL to column-oriented format. DuckDB queries raw CDR data 10 to 100 times faster than MySQL because it operates on compressed columnar storage instead of row-by-row lookups. On a budget VPS with 1GB RAM, you can load millions of call records from ViciDial's vicidial_log table, run complex aggregations on agent performance, call duration, and carrier routes, then export results in seconds. The setup requires only a single binary, basic file format conversion, and a cron job. This tutorial walks through importing real Asterisk CDRs into DuckDB, writing practical queries for call center operations, and troubleshooting the gaps between what ViciDial logs and what you actually need to see. Tested on ViciDial 2.14 (SVN 3555+), Asterisk 16/18, MariaDB 10.5+, DuckDB 0.9.1+, on Debian 11/12 with 1GB RAM. Prerequisites - Running ViciDial instance with MariaDB or MySQL backend - SSH access to the VPS where ViciDial runs (or a separate analytics host) - Asterisk 16+ with CDR logging enabled - 500 MB free disk space for sample data - wget or curl to download DuckDB binary - Basic knowledge of SQL SELECT statements No GUI tools needed. All work happens in the shell. Why switch from MySQL to DuckDB for call records? When you have 10 million call records in vicidial_log, a MySQL query like "SELECT COUNT(*), AVG(LENGTH_IN_SEC) FROM vicidial_log WHERE date BETWEEN..." can take 30 seconds or more. The database must scan every row, decompress fields, and filter. DuckDB pre-compresses data by column: all the LENGTH_IN_SEC values live together, indexed and packed, so the same query finishes in under a second on a single-core VPS. The tradeoff: DuckDB is read-only for analytics. You do not replace ViciDial's live MariaDB. Instead, you export CDR snapshots (daily or hourly) into Parquet or CSV format, load them into DuckDB, and run reports offline. Write operations stay in MySQL. This pattern works because call records rarely change after the call ends. Step 1: Download and install DuckDB SSH into your VPS and install the DuckDB CLI binary. cd /opt wget https://github.com/duckdb/duckdb/releases/download/v0.9.1/duckdb_cli-linux-x86_64.zip unzip duckdb_cli-linux-x86_64.zip chmod +x duckdb ln -s /opt/duckdb /usr/local/bin/duckdb duckdb --version Verify installation: duckdb :memory: "SELECT 1 as test;" Output should be: ┌────────┐ │ test │ │ int64 │ ├────────┤ │ 1 │ └────────┘ If you are on a system with restricted /opt permissions, extract to /home/vicidial/ or /tmp/ instead. Step 2: Export ViciDial CDRs to CSV format ViciDial stores call logs in the vicidial_log table. You will export a date range to a CSV file that DuckDB can ingest. Use the MariaDB client directly on the VPS. mysql -u root -p asterisk -e "SELECT call_date, call_time, caller_id_number, called_party_number, length_in_sec, agent_id, campaign_id, call_status, lead_id, phone_number, user_id FROM vicidial_log WHERE call_date >= '2024-01-15' AND call_date <= '2024-01-21' INTO OUTFILE '/tmp/vicidial_cdr.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';" If you are on a managed host and do not have OUTFILE permission, use an alternative: mysql -u root -p asterisk --batch --skip-column-names -e \ "SELECT call_date, call_time, caller_id_number, called_party_number, \ length_in_sec, agent_id, campaign_id, call_status, lead_id, phone_number, user_id \ FROM vicidial_log WHERE call_date >= '2024-01-15'" | \ sed 's/\t/,/g' > /tmp/vicidial_cdr.csv Verify the file was created and contains data: wc -l /tmp/vicidial_cdr.csv head -3 /tmp/vicidial_cdr.csv Sample output: 1247563 /tmp/vicidial_cdr.csv 2024-01-15,08:23:45,5551234567,5559876543,87,1023,SALES,LIVE,55412,5559876543,1023 2024-01-15,08:45:12,5551234560,5559876544,156,1024,SALES,LIVE,55413,5559876544,1024 Step 3: Create a DuckDB database and import the CSV Initialize a DuckDB file and create a table from the CSV. duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' CREATE TABLE vicidial_log AS SELECT CAST(call_date AS DATE) as call_date, call_time, caller_id_number, called_party_number, CAST(length_in_sec AS INTEGER) as length_in_sec, CAST(agent_id AS VARCHAR) as agent_id, campaign_id, call_status, CAST(lead_id AS BIGINT) as lead_id, phone_number, CAST(user_id AS INTEGER) as user_id FROM read_csv_auto('/tmp/vicidial_cdr.csv', sample_size=-1); SELECT COUNT(*) as record_count, MIN(call_date) as earliest_date, MAX(call_date) as latest_date FROM vicidial_log; EOF DuckDB will auto-detect column types. Verify the import by querying row and date counts. Expected output (adjust numbers to your data): ┌──────────────┬───────────────┬─────────────┐ │ record_count │ earliest_date │ latest_date │ │ 1247562 │ 2024-01-15 │ 2024-01-21 │ └──────────────┴───────────────┴─────────────┘ Step 4: Automate daily CDR exports and DuckDB ingestion Create a shell script that runs via cron each night to pull the previous day's records and append them to the DuckDB database. cat > /home/vicidial/sync_cdr_to_duckdb.sh << 'SCRIPT' #!/bin/bash VICIDIAL_HOME="/home/vicidial" DB_FILE="$VICIDIAL_HOME/cdr_analysis.duckdb" CSV_TMP="/tmp/vicidial_cdr_daily.csv" YESTERDAY=$(date -d "yesterday" +%Y-%m-%d) YESTERDAY_END=$(date -d "yesterday" +%Y-%m-%d) LOG_FILE="/var/log/duckdb_sync.log" echo "[$(date '+%Y-%m-%d %H:%M:%S')] Starting CDR sync for $YESTERDAY" >> $LOG_FILE # Export yesterday's records from MariaDB mysql -u root -p'password_here' asterisk --batch --skip-column-names -e \ "SELECT call_date, call_time, caller_id_number, called_party_number, \ length_in_sec, agent_id, campaign_id, call_status, lead_id, phone_number, user_id \ FROM vicidial_log WHERE call_date = '$YESTERDAY'" | \ sed 's/\t/,/g' > "$CSV_TMP" if [ ! -s "$CSV_TMP" ]; then echo "[$(date '+%Y-%m-%d %H:%M:%S')] No data found for $YESTERDAY, exiting" >> $LOG_FILE exit 0 fi # Append to DuckDB duckdb "$DB_FILE" << EOF INSERT INTO vicidial_log SELECT CAST(call_date AS DATE) as call_date, call_time, caller_id_number, called_party_number, CAST(length_in_sec AS INTEGER) as length_in_sec, CAST(agent_id AS VARCHAR) as agent_id, campaign_id, call_status, CAST(lead_id AS BIGINT) as lead_id, phone_number, CAST(user_id AS INTEGER) as user_id FROM read_csv_auto('$CSV_TMP', sample_size=-1); SELECT COUNT(*) as rows_inserted FROM ( SELECT * FROM vicidial_log WHERE call_date = CAST('$YESTERDAY' AS DATE) ); EOF RESULT=$? if [ $RESULT -eq 0 ]; then echo "[$(date '+%Y-%m-%d %H:%M:%S')] Sync completed successfully" >> $LOG_FILE rm -f "$CSV_TMP" else echo "[$(date '+%Y-%m-%d %H:%M:%S')] Sync failed with code $RESULT" >> $LOG_FILE fi SCRIPT chmod +x /home/vicidial/sync_cdr_to_duckdb.sh Edit the script to use your actual MariaDB password, then add to crontab: crontab -e Add the line: 0 1 * * * /home/vicidial/sync_cdr_to_duckdb.sh This runs the sync script every day at 01:00 UTC (adjust the hour as needed). Step 5: Query call data by agent performance Now that CDRs are in DuckDB, write queries for common reporting needs. All examples assume your DuckDB file is at /home/vicidial/cdr_analysis.duckdb. Daily call volume and average handling time by agent duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SELECT call_date, agent_id, COUNT(*) as calls_handled, ROUND(AVG(length_in_sec), 2) as avg_duration_sec, SUM(length_in_sec) as total_handle_time_sec, COUNT(CASE WHEN call_status = 'LIVE' THEN 1 END) as connected_calls, ROUND(100.0 * COUNT(CASE WHEN call_status = 'LIVE' THEN 1 END) / COUNT(*), 2) as connect_rate_pct FROM vicidial_log WHERE call_date >= '2024-01-15' AND call_date < '2024-01-22' AND agent_id IS NOT NULL GROUP BY call_date, agent_id ORDER BY call_date DESC, calls_handled DESC; EOF Output sample: ┌────────────┬──────────┬────────────────┬─────────────────────┬──────────────────────┬─────────────────┬──────────────────┐ │ call_date │ agent_id │ calls_handled │ avg_duration_sec │ total_handle_time_sec│ connected_calls │ connect_rate_pct │ │ 2024-01-21 │ 1023 │ 145 │ 187.43 │ 27176 │ 121 │ 83.45 │ │ 2024-01-21 │ 1024 │ 138 │ 201.12 │ 27754 │ 118 │ 85.51 │ │ 2024-01-20 │ 1023 │ 142 │ 183.22 │ 25996 │ 119 │ 83.80 │ └────────────┴──────────┴────────────────┴─────────────────────┴──────────────────────┴─────────────────┴──────────────────┘ Campaign performance: total cost per lead by carrier duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SELECT campaign_id, call_status, COUNT(*) as attempt_count, COUNT(DISTINCT lead_id) as unique_leads, ROUND(AVG(length_in_sec), 1) as avg_duration_sec, SUM(CASE WHEN length_in_sec >= 10 THEN 1 ELSE 0 END) as calls_over_10sec FROM vicidial_log WHERE call_date >= '2024-01-15' GROUP BY campaign_id, call_status ORDER BY campaign_id, attempt_count DESC; EOF Find long calls that may indicate transfer or hold duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SELECT call_date, call_time, agent_id, caller_id_number, called_party_number, length_in_sec, call_status FROM vicidial_log WHERE length_in_sec > 600 AND call_date = '2024-01-21' ORDER BY length_in_sec DESC LIMIT 20; EOF Hourly call volume distribution duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SELECT call_date, SUBSTR(call_time, 1, 2) as hour, COUNT(*) as call_count, ROUND(AVG(length_in_sec), 1) as avg_duration_sec FROM vicidial_log WHERE call_date >= '2024-01-15' GROUP BY call_date, SUBSTR(call_time, 1, 2) ORDER BY call_date, hour; EOF Step 6: Export query results for reporting DuckDB can output results in JSON, CSV, or Parquet formats. Use this to feed dashboards or spreadsheets. duckdb /home/vicidial/cdr_analysis.duckdb -json << 'EOF' > /tmp/agent_report.json SELECT call_date, agent_id, COUNT(*) as calls_handled, ROUND(AVG(length_in_sec), 2) as avg_duration_sec FROM vicidial_log WHERE call_date >= '2024-01-15' GROUP BY call_date, agent_id; EOF Or export to CSV: duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' COPY ( SELECT call_date, agent_id, COUNT(*) as calls_handled, ROUND(AVG(length_in_sec), 2) as avg_duration_sec FROM vicidial_log WHERE call_date >= '2024-01-15' GROUP BY call_date, agent_id ) TO '/tmp/agent_report.csv' (FORMAT CSV, HEADER TRUE); EOF Retrieve the file for import into your BI tool or Excel. Step 7: Handle missing or malformed CDR data Not all ViciDial configurations log every field consistently. You may encounter NULLs, empty strings, or misaligned columns. When importing, check for data quality first: duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SELECT COUNT(*) as total_rows, COUNT(agent_id) as rows_with_agent, COUNT(call_date) as rows_with_date, MIN(length_in_sec) as min_duration, MAX(length_in_sec) as max_duration FROM vicidial_log; EOF If you see a large number of NULL agent_ids, your ViciDial instance may not be logging agent interactions. Check that vicidial_log is configured to capture agent_id. In the ViciDial admin web interface, navigate to System Settings, then verify "Log Agent" is enabled. If you see negative or extremely large values in length_in_sec, add a filter to the import query: duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' CREATE TABLE vicidial_log_clean AS SELECT * FROM vicidial_log WHERE length_in_sec >= 0 AND length_in_sec <= 36000; EOF This removes calls with duration over 10 hours (likely corrupted records). Step 8: Set up a read-only DuckDB endpoint for other team members If you have multiple analysts on the VPS, you can create a simple shell wrapper that allows running queries without exposing the database file directly. cat > /usr/local/bin/query_cdr << 'SCRIPT' #!/bin/bash # Simple read-only CDR query wrapper if [ -z "$1" ]; then echo "Usage: query_cdr 'SELECT ...' [format]" echo "Format: table, json, csv (default: table)" exit 1 fi FORMAT=${2:-table} case $FORMAT in json) duckdb /home/vicidial/cdr_analysis.duckdb -json "$1" ;; csv) duckdb /home/vicidial/cdr_analysis.duckdb << EOF COPY ($1) TO '/dev/stdout' (FORMAT CSV, HEADER TRUE); EOF ;; *) duckdb /home/vicidial/cdr_analysis.duckdb "$1" ;; esac SCRIPT chmod +x /usr/local/bin/query_cdr Team members can now run: query_cdr "SELECT COUNT(*) FROM vicidial_log WHERE call_date = '2024-01-21'" csv Note: This does not implement row-level access control. For stricter security, use a web API wrapper such as DuckDB's official Python HTTP server, which can enforce authentication. Step 9: Optimize DuckDB for very large datasets When your DuckDB file grows beyond 5GB, consider these optimizations. Use Parquet format instead of CSV for imports Parquet is compressed and column-oriented, much faster to load than CSV: # Export from MariaDB to Parquet via Python python3 << 'PYTHON' import duckdb import pandas as pd import pymysql conn = pymysql.connect( host="localhost", user="root", password="password", database="asterisk" ) df = pd.read_sql( "SELECT * FROM vicidial_log WHERE call_date = '2024-01-21'", conn ) duckdb_conn = duckdb.connect('/home/vicidial/cdr_analysis.duckdb') duckdb_conn.register('df', df) duckdb_conn.execute(""" INSERT INTO vicidial_log SELECT * FROM df """) duckdb_conn.close() conn.close() PYTHON Partition the database by date Instead of one large table, create separate tables or Parquet files by month: for month in 2024-01 2024-02 2024-03; do mysql -u root -p asterisk -e "SELECT * FROM vicidial_log WHERE call_date LIKE '$month%' INTO OUTFILE '/tmp/cdr_$month.csv' ..." & done Then load each month into a separate DuckDB instance or use DuckDB's partitioned table support. Troubleshooting DuckDB returns "No such table" error Verify the table exists by connecting to the database file and listing tables: duckdb /home/vicidial/cdr_analysis.duckdb ".tables" If the table is missing, reimport the CSV. Confirm that the CSV file path in the read_csv_auto() function is absolute or relative to the working directory. Cron job for sync_cdr_to_duckdb.sh did not run Check the cron log: grep "sync_cdr_to_duckdb" /var/log/syslog # or on systemd-based systems: journalctl -u cron Verify the script has execute permissions: ls -la /home/vicidial/sync_cdr_to_duckdb.sh Test the script manually: /home/vicidial/sync_cdr_to_duckdb.sh cat /var/log/duckdb_sync.log CSV import is very slow on large files For files over 100MB, increase DuckDB's thread count: duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SET threads = 4; CREATE TABLE vicidial_log AS SELECT * FROM read_csv_auto('/tmp/vicidial_cdr.csv'); EOF Or pre-compress the CSV using gzip and import directly: gzip /tmp/vicidial_cdr.csv duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' CREATE TABLE vicidial_log AS SELECT * FROM read_csv_auto('/tmp/vicidial_cdr.csv.gz'); EOF DuckDB process runs out of memory on 1GB VPS DuckDB is designed for in-memory performance, but on constrained hardware, you may hit swap. Reduce the number of concurrent queries or partition your data: # Limit DuckDB to 512MB memory duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' SET memory_limit = '512MB'; SELECT * FROM vicidial_log WHERE call_date = '2024-01-21' LIMIT 100; EOF Or reduce the date range per query: # Query by single day instead of full week for day in {15..21}; do duckdb /home/vicidial/cdr_analysis.duckdb << EOF SELECT * FROM vicidial_log WHERE call_date = '2024-01-$day' ... EOF done Query results include rows from calls without an agent_id By design, DuckDB reads all rows. If your reporting requirement is "only calls handled by agents," filter explicitly: SELECT * FROM vicidial_log WHERE call_date = '2024-01-21' AND agent_id IS NOT NULL AND agent_id <> ''; Or create a materialized view: duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' CREATE VIEW agent_calls AS SELECT * FROM vicidial_log WHERE agent_id IS NOT NULL AND agent_id <> ''; EOF Then query the view: SELECT COUNT(*) FROM agent_calls WHERE call_date = '2024-01-21'; Frequently asked questions Why does DuckDB require me to export CDRs first instead of querying MariaDB directly? DuckDB is a columnar analytical database optimized for aggregations on static snapshots. MariaDB is a transactional row store optimized for updates. If you query MariaDB directly via a tool, it will still scan full tables row-by-row. DuckDB's advantage comes from copying the data into its compressed columnar format, which trades write speed for read speed. For live agent dashboards, keep querying MariaDB. For historical reports and bulk analysis, use DuckDB. Can I delete old records from DuckDB to save disk space? Yes. DuckDB tables support DELETE statements. To remove records older than 90 days: duckdb /home/vicidial/cdr_analysis.duckdb << 'EOF' DELETE FROM vicidial_log WHERE call_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY); VACUUM; EOF The VACUUM command reclaims disk space. Alternatively, do not import data older than your retention window in the first place. How do I query DuckDB from a Python script without shelling out to the CLI? Install the DuckDB Python package and use it as a library: pip install duckdb Then in your Python code: import duckdb conn = duckdb.connect('/home/vicidial/cdr_analysis.duckdb') result = conn.execute("SELECT COUNT(*) FROM vicidial_log WHERE call_date = '2024-01-21'").fetchall() print(result[0][0]) conn.close() This avoids spawning a subprocess and is faster for multiple queries. What if my ViciDial instance uses a different table schema, with custom columns? Export only the columns that exist and are relevant to your analysis. Check your schema first: mysql asterisk -e "DESC vicidial_log;" | head -20 Adapt the SELECT statement in the export script to include only those columns. DuckDB will infer types automatically. If a column is missing, add it to the export and then add it to the CREATE TABLE statement. Can DuckDB work with live Asterisk CEL (Channel Event Log) data instead of ViciDial's vicidial_log? Yes. Asterisk's CEL table in the asterisk database contains finer-grained events. Export it the same way: mysql asterisk -e "SELECT * FROM cel WHERE eventtime > DATE_SUB(NOW(), INTERVAL 1 DAY) INTO OUTFILE '/tmp/asterisk_cel.csv' ..." Then import to DuckDB. CEL data is more voluminous than CDR summaries, so expect larger files and longer query times. Filter by eventtype if you only need call setup, connect, and hangup events. Summary You have set up a production CDR analytics pipeline using DuckDB on a budget VPS. The workflow is: 1. Export daily call records from ViciDial's vicidial_log table to CSV format via MySQL. 2. Load the CSV into a DuckDB database file using read_csv_auto() and INSERT statements. 3. Run analytical queries on millions of records in milliseconds, grouping by agent, campaign, status, and time. 4. Export results to JSON or CSV for reporting. 5. Automate the daily import via a cron job. Next steps: adjust the sync script to match your MariaDB credentials and backup schedule, add Parquet file exports for archival beyond 90 days, and consider setting up a scheduled report that emails query results to your management team each morning. Monitor the DuckDB file size and cron logs weekly to catch any import failures early. If queries slow down as data accumulates, partition by month or switch to quarterly analysis windows instead of appending to a single table indefinitely.