Query Millions of VoIP CDRs with DuckDB on Budget VPS

Q: 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/cdranalysis.duckdb') result = conn.execute("SELECT COUNT() FROM vicidiallog WHERE calldate = '2024-01-21'").fetchall() print(result[0][0]) conn.close() This avoids spawning a subprocess and is faster for multiple queries.

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:

Export daily call records from ViciDial's vicidial_log table to CSV format via MySQL.
Load the CSV into a DuckDB database file using read_csv_auto() and INSERT statements.
Run analytical queries on millions of records in milliseconds, grouping by agent, campaign, status, and time.
Export results to JSON or CSV for reporting.
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.