Federated Queries Across Databases with DolphinDB

In real-world systems, data rarely lives in a single place. Trading platforms keep orders and positions in Oracle or MySQL, tick data lands in a time-series store, reference data sits in CSV or Parquet on S3, and some analytics live in a separate warehouse entirely.

The result? Every "simple" analysis turns into a multi-step data-wrangling project. You export from one system, import into another, align schemas, write glue scripts, and maintain fragile pipelines that break whenever anything changes.

Starting from DolphinDB V3.00.4, the cross-database federated query feature simplifies all of this into one idea: don't move the data—query it wherever it is, using a single SQL statement.

What is Cross-Database Federated Query?

The concept is straightforward: federated query virtualizes multiple heterogeneous data sources as tables inside DolphinDB. Your data might be in Oracle, MySQL, SQL Server, or Parquet files on S3, but to you as a user, they all appear as DolphinDB tables. You can select from them, join them together, filter with where clauses, and aggregate with group by—just like any native table.

In DolphinDB, this virtualization starts with createExternalTable. These external tables have one key characteristic: they don't actually store data. Instead, they store metadata that tells DolphinDB how to connect to the external data source, which table or file to read, and how to map column types between systems.

When you execute a query, DolphinDB uses this metadata to access the external system on-demand and treats the returned data as part of a local table. This works seamlessly across relational databases like Oracle, MySQL, and SQL Server, object storage systems like S3 and HDFS, and file formats including CSV and Parquet.

Turning External Data into “Local Tables” in DolphinDB

Let’s start with a concrete example: mounting an Oracle table in DolphinDB.

loadPlugin("odbc")
oracle_cfg = dict(["connectionString"], ["Dsn=MyOracleDB"])
t = createExternalTable("aka_name", "oracle", oracle_cfg)
select t.name from t where t.id > 200 limit 50

The resulting t behaves just like a DolphinDB table syntactically and can be used in any SQL query.
Under the hood, DolphinDB translates operations on t into calls to Oracle.

In the same way, you can mount MySQL or SQL Server tables, or virtualize Parquet files on S3 as external tables. As a developer, you just write SQL against tables—regardless of where they actually live.

A Unified Query Layer

This is where federated queries become powerful. After you've defined external tables from different systems, DolphinDB effectively becomes a single entry point for querying across your entire data landscape.

Here’s an example showing how to join trades from Oracle, users from MySQL, and quotes from Parquet for analysis:

t_trade = createExternalTable("trade",  "oracle",  oracle_cfg)
t_user  = createExternalTable("users",  "mysql",   mysql_cfg)
t_quote = createExternalTable("quote",  "parquet", parquet_cfg)

select
    t_user.userName,
    t_trade.orderId,
    t_quote.price
from t_trade
join t_user  on t_trade.userId = t_user.id
join t_quote on t_quote.sym = t_trade.sym and t_quote.time = t_trade.time
where t_trade.tradeDate >= 2024.01.01

From your perspective as a SQL user, this looks like an ordinary multi-table join. Behind the scenes, though, DolphinDB is doing significant translation work. It connects to Oracle using one protocol, pulls data from MySQL using another, reads Parquet files from S3, and then merges all the results together before returning them to you.

For business developers and analysts, this unified view eliminates entire categories of ETL and data synchronization work. Instead of building pipelines to move data around, many requests can now start with a simpler question: "Can we just write a single SQL for this?"

Predicate Pushdown & Column Pruning

Once cross-system queries are involved, performance and network overhead become critical.
A natural worry is:

“Is DolphinDB pulling entire tables across the network and filtering them locally?”

The answer is no. Federated queries in DolphinDB are designed to be smarter than that. Whenever possible, DolphinDB pushes filtering and column selection down to the external source itself, so that heavy lifting happens where the data lives.

Consider a query with filters like where t.id > 200 and t.sym = 'AAPL'. Rather than retrieving all rows and filtering them locally, DolphinDB translates these conditions into the external system's native query language—whether that's Oracle SQL, a MySQL query, or a Parquet file scan. The external system applies the filter first, and only the matching rows are sent back over the network. This dramatically reduces data transfer and speeds up query execution.

The same principle applies to column selection. If your query only needs a few columns from a table, DolphinDB will attempt to read only those columns from the external source, not the entire row. This is particularly effective with columnar formats like Parquet, where reading individual columns is efficient by design. The result is lower I/O, less network traffic, and faster queries overall.

From Cross-Database to Cross-Cluster: Federated Queries in Master of Master

Federated querying isn't limited to external databases and files. It extends naturally to multi-cluster DolphinDB deployments as well.

In large-scale environments, organizations often split DolphinDB into multiple clusters based on business domains or operational boundaries. You might have one cluster dedicated to market data, another for trade execution data, and a third for risk analytics. Each cluster operates independently, but analysts and applications frequently need to correlate data across them.

To enable seamless cross-cluster access, DolphinDB provides a Master of Master architecture that manages global metadata across all clusters. With this setup, you don't need to manually create external tables for every remote dataset. Instead, you can directly reference tables in other clusters using the Database.Table@ClusterName syntax.

Here's what that looks like in practice:

select * from trade, trading.stock2.quote@cluster1 as b
         where b.sym = trade.sym and trade.time = b.time

In this query, trade is a local table in your current cluster, while trading.stock2.quote@cluster1 accesses the trading.stock2.quote table located in a remote cluster named cluster1. From a SQL perspective, it's just an ordinary join. Behind the scenes, though, the Master of Master architecture handles cluster discovery, query routing, data transmission, and result aggregation—all transparently, so you can focus on the analysis rather than the infrastructure.

Real-World Applications

In practice, federated queries enable several powerful use cases that address common challenges in data infrastructure.

Gradual Migration

Many organizations want to move their core analytics and real-time processing to DolphinDB, but they have years of historical data sitting in Oracle or MySQL. Migrating everything at once is risky and expensive. Federated queries offer a smoother path: write new data directly to DolphinDB while leaving historical data in place temporarily. Through federated queries, you can access both old and new data seamlessly from DolphinDB, presenting a unified query interface to downstream applications. Then migrate the historical data gradually—by time period or business unit—without disrupting existing workflows.

Breaking Down Data Silos

Trading records, account information, market data, system logs, and risk metrics often live in completely separate systems. In the past, creating a comprehensive analysis report meant orchestrating multiple data pipelines and maintaining intermediate staging tables. Now you can mount all these sources as external tables and write SQL directly in DolphinDB to join and analyze them. Many statistical reports and monitoring dashboards that once required complex ETL can now be built with straightforward queries.

Multi-Cluster Collaboration

In distributed deployments, different clusters often specialize in different workloads. One cluster might focus on high-frequency market data ingestion and queries, while another handles compute-intensive trading or risk calculations. Sometimes you need to reference data from another cluster temporarily. The xxx@clusterName syntax enables cross-cluster queries while maintaining clear boundaries between clusters—each can optimize for its own workload while still sharing data at the logical layer.

Summary

The cross-database federated query feature in DolphinDB can be summed up in one sentence: it pushes the complexity of "where data lives and in what format" down into the system, so you can focus on "what you want to compute."

Try It Now

DolphinDB's cross-database federated query feature is now available in the latest release. Upgrade to DolphinDB V3.00.4 to start using it today.

📖 Documentation: https://docs.dolphindb.com/en/Programming/SQLStatements/createExternalTable.html
💬 Technical Support: support@dolphindb.com
🚀 Download: https://dolphindb.com/product#downloads-top

Learn more: https://dolphindb.com