Turning DuckDB into a Mini Serverless Data Warehouse on Cloud VPS with Cronjob
Transform DuckDB into a mini serverless data warehouse on a cloud VPS by automating data ingestion, processing, and reporting using cronjob. This guide demonstrates how to set up a...
Transform DuckDB into a mini serverless data warehouse on a cloud VPS by automating data ingestion, processing, and reporting using cronjob. This guide demonstrates how to set up a lightweight, cost-effective, and fully automated data analytics system ideal for personal projects or small businesses. DuckDB, with its high performance and ability to analyze data directly from files, is perfect for building a centralized data warehouse without requiring a full database server. Combined with cronjob, data processing tasks can run on a scheduled basis without manual intervention.
Overview of DuckDB and Cronjob Mechanism
DuckDB is a relational database management system (RDBMS) designed for analytical processing (OLAP) directly from files such as CSV, Parquet, or JSON. It operates as an embedded database, requiring no separate server, and can run on local machines or cloud VPS instances. With built-in support, DuckDB enables fast querying of large datasets without complex setup. Meanwhile, cronjob is a Unix/Linux scheduling tool that executes commands or scripts at specified intervals. By combining DuckDB with cronjob, you can create a fully automated mini data warehouse that runs periodic tasks to process new data and update reports.
To implement this, you need a Linux-based cloud VPS (Ubuntu or CentOS), install DuckDB, and configure cronjob to run tasks like data loading, querying, and result export. Cloud VPS provides stable resources, reliable network connectivity, and scalability, making it suitable for running scheduled analytics workloads.
Setting Up the Environment on VPS
First, choose a suitable cloud VPS service, such as NVMe Cloud VPS for Docker, Nginx & Databases. Install Ubuntu 22.04 LTS. Then, update the system:
sudo apt update && sudo apt upgrade -y
Proceed to install DuckDB. DuckDB can be installed via pip or by downloading the binary from the official site. Here's how to install it using pip:
sudo apt install python3-pip -y
pip3 install duckdb
To verify the installation, run:
duckdb --version
Ensure the correct version is installed. DuckDB supports multiple file formats, including CSV, Parquet, and JSON, enabling integration from various data sources.
Writing Data Processing Script and Setting Up Cronjob
Create a directory to store data files and scripts. For example:
mkdir -p /home/user/duckdb-warehouse
cd /home/user/duckdb-warehouse
Create a Python or shell script to process data. Below is a simple Python script to load CSV data, query it, and export results:
#!/usr/bin/env python3
import duckdb
import pandas as pd
# Connect to the database file
conn = duckdb.connect('/home/user/duckdb-warehouse/data.db')
# Load data from CSV
conn.execute("""
CREATE TABLE IF NOT EXISTS sales AS
SELECT * FROM read_csv('/home/user/duckdb-warehouse/sales.csv')
""")
# Query data
result = conn.execute("""
SELECT product, SUM(quantity) as total_quantity
FROM sales
GROUP BY product
ORDER BY total_quantity DESC
""").fetchall()
# Export results to file
with open('/home/user/duckdb-warehouse/report.txt', 'w') as f:
f.write("Top products by quantity:\n")
for row in result:
f.write(f"{row[0]}: {row[1]}\n")
conn.close()
Make the script executable:
chmod +x /home/user/duckdb-warehouse/process_data.py
Set up cronjob to run the script daily. Run:
crontab -e
Add the following line to run the script every day at 2:00 AM:
0 2 * * * /home/user/duckdb-warehouse/process_data.py
Save and exit. Cron will automatically execute the script according to the schedule.
Verification and Troubleshooting
To verify, check the report.txt file after the cronjob runs. If the file is not updated, check the cron logs using:
grep CRON /var/log/syslog
Ensure the script has execution permissions and the correct file paths. If needed, run the script manually to test for errors:
/home/user/duckdb-warehouse/process_data.py
For large datasets, DuckDB can handle them without additional configuration. However, if memory issues occur, optimize by reducing batch size or switching to Parquet format instead of CSV.
References
Related infrastructure & engineering guides: Detailed Guide to Building a Personal CDN Optimized with Brotli 11 by Manually Compiling Nginx on Linux VPS
Recommended NVMe Cloud VPS Plans to Deploy This Setup
Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.
VPS-3E
10% VAT included
- CPU: 2 vCPU
- RAM: 4 GB
- SSD: 50 GB NVMe
