Running SQL Concurrently Across Three Remote DuckDB Servers
Simultaneous querying across remote databases with the Quack protocol.
1Introduction to Quack Protocol
The DuckDB team developed a new database communication protocol called Quack. This protocol allows DuckDB databases on different servers to communicate over HTTP in a client/server arrangement. Quack enables databases to read and write each other's data. However, it is emphasized that Quack does not facilitate distributed query processing.
2Experiment with Concurrent SQL Queries
To test the potential of the Quack protocol, three AWS EC2 servers were set up. Each server hosts a DuckDB database, with one server acting as the coordinator. This setup was designed to investigate how concurrent SQL queries can be executed on remote databases. The servers were quickly configured using a CloudFormation stack.
3Coordination with Python
Python code running on the coordinator server sends concurrent SQL queries to three different DuckDB servers. Each query is labeled as a QueryFragment and executed on a separate thread. A barrier mechanism ensures all queries start simultaneously. This guarantees that the queries are executed concurrently.
4Gathering Results
When each query completes, the results are collected on the coordinator server. The Quack protocol facilitates access to remote databases and retrieves query results. The Python code records the start times and durations of the queries. This information is used to analyze query performance.
5Database Structure
Each server hosts a different DuckDB database. Worker 1 contains sales data, Worker 2 contains customer data, and Worker 3 contains product data. Each database includes a synthetic dataset of ten million records. This structure is used to test how concurrent queries perform on different data types.
6CloudFormation Template
The entire setup was implemented using a CloudFormation template. This template automatically configures three AWS EC2 servers and installs the necessary software. Python source code is embedded in the template as a Base64-encoded archive. This approach speeds up the setup process and makes it repeatable.
7Technical Details of Quack
The Quack protocol listens on port 9494 by default. Authentication is handled using an encrypted SSM Parameter Store parameter. Python source code for Quack is present on all servers, but additional commands are added to the coordinator server. These commands are used to initiate concurrent queries.
8Initiating Concurrent Queries
The cluster-duck-sql command run on the coordinator server starts the Python code. This code executes each query on a separate thread and collects the results. Each query is sent to a remote DuckDB server, and the results are retrieved. This process is managed by the Quack protocol.
9Creating Query Fragments
Each query is labeled as a QueryFragment and processed in sequence. The coordinator creates a thread for each query fragment and uses a barrier mechanism. This barrier ensures all queries start simultaneously. Due to operating system scheduling, queries may not start exactly at the same time.
10Presenting Results
When all queries complete, the results are merged on the coordinator server. The start time and duration of each query are recorded. This information is used to analyze query performance. The Quack protocol facilitates access to remote databases and retrieves query results.
11Synthetic Datasets
The DuckDB databases on each server contain synthetic datasets of ten million records. Worker 1 contains sales data, Worker 2 contains customer data, and Worker 3 contains product data. These datasets are used to test how concurrent queries perform on different data types.
12Potential of Quack
The Quack protocol enables concurrent querying across remote DuckDB databases. This simplifies data access and improves query performance. However, it should be noted that Quack does not facilitate distributed query processing. Quack opens up new possibilities in database management and analysis.
Original article: Towards Data Science — Üç Uzak DuckDB Sunucusunda Eşzamanlı SQL Çalıştırma