Back to articles
AI & Cloud

Configure DuckDB on Cloud Server: Gigabyte-Scale OLAP Solution Optimized from S3 Parquet

Detailed guide to configure DuckDB on a Cloud Server for processing Gigabyte-scale data from S3 Parquet, optimizing cost and performance for OLAP analysis.

DPTCloud Engineering4 min read
Configure DuckDB on Cloud Server: Gigabyte-Scale OLAP Solution Optimized from S3 Parquet

Introduction to DuckDB and Gigabyte-Scale OLAP Solution

DuckDB is an open-source relational database optimized for analytical processing (OLAP) on personal computers or small servers. With its ability to process large datasets from Parquet files stored in S3, DuckDB enables small and medium-sized businesses to build cost-effective, high-performance data analytics solutions. This guide walks through configuring DuckDB on a Cloud Server to process Gigabyte-scale data from S3 Parquet, optimizing both performance and cost.

Prerequisites and Environment Setup

To deploy DuckDB on a Cloud Server, you need:

  • A Cloud server (VPS or Dedicated Server) with at least 8GB RAM and 2 CPU cores.
  • Linux operating system (Ubuntu 20.04/22.04 or CentOS 8/9).
  • Python 3.8 or higher, pip, and supporting tools like git, curl, and wget.
  • An AWS S3 account with access to the bucket containing Parquet data.
  • Prepare a credentials file or use an IAM role if deployed on EC2.

Configure DuckDB on Cloud Server

Follow these steps to install and configure DuckDB:

1. Install DuckDB

Run the following commands on your Cloud Server:

sudo apt update
sudo apt install -y python3 python3-pip
pip3 install duckdb

2. Connect to S3 Parquet via Boto3

Use the Boto3 library to access Parquet data from S3. Install Boto3:

pip3 install boto3

Create a s3_config.py file to store connection details:

import boto3
from duckdb import connect

# S3 Configuration
s3_client = boto3.client(
    's3',
    aws_access_key_id='YOUR_ACCESS_KEY',
    aws_secret_access_key='YOUR_SECRET_KEY',
    region_name='us-east-1'
)

# Connect to DuckDB
conn = connect(':memory:')

# Query Parquet data from S3
query = """
SELECT * FROM read_parquet('s3://your-bucket/path/to/data.parquet')
WHERE date > '2023-01-01'
"""
result = conn.execute(query).fetchall()
print(result)

3. Optimize Performance with Data Partitioning

To efficiently handle Gigabyte-scale data, consider:

  • Partitioning data by date or region.
  • Using Parquet format with Snappy or Zstd compression.
  • Selecting only required columns to reduce memory usage.

Perform OLAP Analysis with DuckDB

DuckDB supports powerful OLAP queries. For example:

SELECT 
    country,
    COUNT(*) as count,
    AVG(sales) as avg_sales
FROM read_parquet('s3://your-bucket/data.parquet')
WHERE year = 2023
GROUP BY country
ORDER BY avg_sales DESC;

Verify and Test Performance

Run the following command to test connectivity and performance:

python3 s3_config.py

To measure query time:

import time
start_time = time.time()
# Run query
end_time = time.time()
print(f"Query time: {end_time - start_time} seconds")

Troubleshooting

  • Cannot access S3: Verify IAM permissions, access keys, and region.
  • Memory issues: Increase Cloud Server RAM or split data into smaller files.
  • Slow queries: Check Parquet format, compression, and only query necessary columns.

References

DPTCloud Tier III Infrastructure

High-RAM Cloud VPS Plans Optimized for AI, Databases & Cache

Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.

View 22 VPS Plans
ECO · vn-02Recommended

VPS-4E

297.000 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe
ECO · vn-02

VPS-5E

614.900 ₫ / mo

10% VAT included

  • CPU: 8 vCPU
  • RAM: 16 GB
  • SSD: 100 GB NVMe
ADVANCED · vn-03Recommended

VPS-4T

548.900 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478