Federated Queries Across Heterogeneous Databases

Introduction

In today’s data‑driven world, organizations rarely store all of their information in a single, monolithic repository. A modern enterprise may keep transaction logs in a relational data warehouse, sensor streams in a time‑series database, and scientific observations in a cloud data lake. The value hidden in these silos is enormous, but extracting it traditionally required costly ETL pipelines, duplicate storage, and long latency.

Federated querying—executing a single SQL statement that reaches across disparate data sources—offers a pragmatic alternative. By letting the query engine act as a “virtual data fabric,” analysts can join, filter, and aggregate data wherever it lives, without moving it first. This approach reduces storage overhead, shortens time‑to‑insight, and, crucially for Apiary, enables conservation scientists to combine ecological datasets (e.g., hive health metrics) with external sources such as climate models, satellite imagery, and public health records in real time.

The technology stack for federated queries has matured dramatically. Microsoft SQL Server’s PolyBase, Google BigQuery’s Remote Functions, and the open‑source distributed SQL engine Trino (formerly PrestoSQL) each provide a distinct path to cross‑system analytics. In this pillar article we will dissect how these three solutions work, compare their performance, security, and operational characteristics, and walk through concrete, production‑grade examples that blend bee‑related data with broader environmental and AI‑agent telemetry. By the end you’ll have a roadmap for building a resilient, query‑centric data architecture that respects both the heterogeneity of modern data stores and the urgency of conservation work.

1. The Landscape of Heterogeneous Data

1.1 Why “heterogeneous” is the norm

According to a 2023 Gartner survey, 84 % of enterprises operate at least three different database technologies, and 56 % run five or more. The drivers are diverse:

Each system optimizes for a particular workload, but business questions rarely respect those boundaries. A researcher asking, “How did the 2022 drought in California affect honey‑bee colony losses compared to the previous decade?” must blend weather data from a cloud data lake, hive health logs from an on‑prem PostgreSQL instance, and pesticide usage records stored in a Google Sheet.

1.2 The cost of moving data

Traditional ETL pipelines can consume up to 30 % of an organization’s data budget (Forrester, 2022). Data duplication inflates storage costs—Amazon S3 charges $0.023 per GB‑month, while a typical relational warehouse can cost $0.15 per GB‑month for the same raw data. Moreover, latency spikes: nightly batch loads may delay insights by 12–24 hours, which is unacceptable when monitoring rapidly evolving phenomena such as bee colony collapse disorder (CCD).

1.3 What “federated query” actually means

A federated query is a single logical SQL statement that is parsed and optimized by a query engine, then dispatched to one or more underlying data sources. The engine may push down predicates (filters) to each source, retrieve only the needed columns, and perform joins either locally (in memory) or in a distributed fashion. The result set is returned to the client as if it originated from a single table.

Key technical ingredients:

• Connector / Adapter – translates the engine’s logical plan into source‑specific API calls (e.g., ODBC, REST, gRPC).
• Push‑down capabilities – the ability to delegate filtering, projection, and even aggregation to the remote system, reducing data movement.
• Security context propagation – passing authentication tokens (OAuth, Kerberos, service‑account keys) so that each source can enforce its own access controls.

The next sections explore three mature implementations of this pattern.

2. PolyBase – Bridging SQL Server and the Big Data World

2.1 Overview

PolyBase debuted with SQL Server 2016 and Azure Synapse Analytics, promising “SQL on Hadoop.” It allows a SQL Server instance to treat external data sources—HDFS, Azure Blob Storage, Oracle, MongoDB, and even other SQL Server instances—as external tables.

• Connector model: Each external data source is defined via a data source object (e.g., CREATE EXTERNAL DATA SOURCE MyHdfs) and a file format object (e.g., CREATE EXTERNAL FILE FORMAT ParquetFormat).
• Query engine: PolyBase rewrites the query plan to push down scans to the external source when the source supports the required operations. For Parquet files on Azure Blob, it can push down column pruning and predicate filters.

2.2 Real‑world performance numbers

A 2022 Microsoft internal benchmark compared a join between a 500 M‑row fact table in Azure Synapse and 10 TB of Parquet files in Azure Data Lake Storage (ADLS). Results:

The reduction in data movement directly translates into cost savings and lower carbon footprint—a point that resonates with Apiary’s sustainability goals.

2.3 Setting up a cross‑system hive health query

Suppose we keep hive weight measurements in a PostgreSQL database on‑prem, and weather forecasts in Parquet files on ADLS. The goal: compute the average weight change per hive for days with a temperature > 30 °C.

-- 1. Define the external data source (ADLS)
CREATE EXTERNAL DATA SOURCE WeatherLake
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://weather@mydatalake.dfs.core.windows.net/'
);

-- 2. Define the file format (Parquet)
CREATE EXTERNAL FILE FORMAT ParquetFmt
WITH (
    FORMAT_TYPE = PARQUET
);

-- 3. Create an external table that maps to the Parquet files
CREATE EXTERNAL TABLE dbo.WeatherForecast (
    station_id   VARCHAR(20),
    forecast_dt  DATE,
    temperature  FLOAT,
    precipitation FLOAT
)
WITH (
    LOCATION = '/daily/2023/',
    DATA_SOURCE = WeatherLake,
    FILE_FORMAT = ParquetFmt
);

-- 4. Join with the on‑prem hive table (assume a linked server called PG_HIVE)
SELECT h.hive_id,
       AVG(h.weight_kg) AS avg_weight,
       COUNT(*) AS days_above_30C
FROM dbo.HiveMeasurements h
JOIN OPENQUERY(PG_HIVE, 'SELECT hive_id, measurement_dt, weight_kg FROM hive_measurements')
      AS pg ON h.hive_id = pg.hive_id AND h.measurement_dt = pg.measurement_dt
JOIN dbo.WeatherForecast w
      ON w.station_id = h.station_id
     AND w.forecast_dt = h.measurement_dt
WHERE w.temperature > 30
GROUP BY h.hive_id;

Key take‑aways:

• The external table abstracts the Parquet files as a regular relational object.
• PolyBase pushes the WHERE w.temperature > 30 filter down to ADLS, scanning only the relevant rows.
• The OPENQUERY call reaches the on‑prem PostgreSQL instance via a linked server, allowing a single SQL statement to span cloud and on‑prem.

2.4 Security considerations

• Kerberos delegation: When PolyBase accesses Hadoop or ADLS, it can use Kerberos tickets for mutual authentication.
• Row‑level security (RLS): Defined on the SQL Server side; external tables inherit the RLS policy, ensuring that a user who can only see certain hives in the local warehouse cannot accidentally retrieve data for others from the lake.

2.5 Limitations

• Connector ecosystem: While PolyBase supports many sources, it lacks native connectors for modern NoSQL stores like DynamoDB.
• Complex joins: Multi‑way joins that involve more than two external sources may cause the engine to pull large intermediate result sets into SQL Server memory, potentially hitting the Maximum Degree of Parallelism (MAXDOP) limit.

3. BigQuery Remote Functions – Extending SQL with External Code

3.1 Conceptual model

BigQuery’s Remote Functions (GA in 2022) let you call a user‑defined function (UDF) that lives outside of BigQuery, typically as a Google Cloud Function or Cloud Run service. The function receives input rows, processes them, and returns results—all within the same SQL query.

Unlike traditional federated queries that pull data from external tables, Remote Functions push the data to an external compute environment. This is powerful for:

• Enriching rows with AI‑generated predictions (e.g., a model that assesses colony health from image metadata).
• Performing lookups against proprietary APIs (e.g., pesticide exposure data from a government service).

3.2 Example: Scoring hive health with a TensorFlow model

Assume we have a hive_events table in BigQuery that stores daily metrics: hive_id, date, temperature, humidity, weight_kg. A TensorFlow model hosted on Cloud Run predicts a “stress score” (0–1) based on these inputs.

3.2.1 Deploy the Cloud Run service

# app.py
from flask import Flask, request, jsonify
import tensorflow as tf
import numpy as np

app = Flask(__name__)
model = tf.keras.models.load_model('/models/hive_stress.h5')

@app.route('/', methods=['POST'])
def predict():
    rows = request.get_json()['rows']
    # rows is a list of lists: [[temp, hum, weight], ...]
    inputs = np.array(rows, dtype=np.float32)
    scores = model.predict(inputs).flatten().tolist()
    return jsonify({'scores': scores})

Deploy with:

gcloud run deploy hive-stress-predictor \
  --image gcr.io/my-project/hive-stress-predictor \
  --allow-unauthenticated \
  --region us-central1

3.2.2 Create the Remote Function in BigQuery

CREATE OR REPLACE REMOTE FUNCTION mydataset.hive_stress_score(
  temperature FLOAT64,
  humidity FLOAT64,
  weight_kg FLOAT64
) RETURNS FLOAT64
REMOTE WITH CONNECTION `myproject.us.central1.my-connection`
OPTIONS (
  endpoint = 'https://hive-stress-predictor-abcdef-uc.a.run.app',
  max_batching_rows = 1000
);

The Connection object stores the IAM service account that authorizes BigQuery to invoke the Cloud Run endpoint.

3.2.3 Use the function in a query

SELECT hive_id,
       DATE,
       weight_kg,
       hive_stress_score(temperature, humidity, weight_kg) AS stress_score
FROM `mydataset.hive_events`
WHERE DATE BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY stress_score DESC
LIMIT 10;

BigQuery automatically batches up to 1 000 rows per request, dramatically reducing latency. In a benchmark with 5 M rows, the remote function added ≈ 2 seconds of overhead per 100 k rows, far cheaper than exporting the data to a separate ML pipeline.

3.3 Federated query pattern with Remote Functions

Remote Functions can be combined with BigQuery’s External Table feature (e.g., referencing Cloud Storage CSVs) to achieve a true federated query:

CREATE EXTERNAL TABLE mydataset.weather_raw (
  station_id STRING,
  date DATE,
  temperature FLOAT64,
  precipitation FLOAT64
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://weather-bucket/2023/*.csv']
);

SELECT h.hive_id,
       AVG(h.weight_kg) AS avg_weight,
       AVG(myproject.mydataset.hive_stress_score(w.temperature, w.precipitation, h.weight_kg)) AS avg_stress
FROM `myproject.mydataset.hive_events` h
JOIN mydataset.weather_raw w
  ON h.station_id = w.station_id AND h.date = w.date
GROUP BY h.hive_id;

Here, the weather data lives in Cloud Storage, while the stress scoring is performed on‑the‑fly via a Remote Function, all in a single SQL statement.

3.4 Governance and cost

• Billing: Remote Function invocations are billed at $0.40 per million rows (as of 2024). For a daily run over 10 M rows, that’s $4 /day—trivial compared to the cost of moving data.
• IAM: The connection object enforces the principle of least privilege; only the service account with cloudfunctions.invoker permission can call the endpoint.
• Latency: Cloud Run cold‑starts can add ~300 ms per batch. Warm containers reduce this to < 50 ms.

3.5 When to choose Remote Functions over external tables

4. Trino – The Open‑Source Distributed SQL Engine for Anything

4.1 Architecture at a glance

Trino (formerly PrestoSQL) is a clustered query engine that separates coordinator (parses, plans, schedules) from workers (execute scans, joins, aggregations). It supports more than 80 connectors, ranging from Hive, MySQL, PostgreSQL, Cassandra, Elasticsearch, to Google Sheets.

Key architectural concepts:

• Connector‑specific split generation – each connector tells Trino how to break a table into splits (e.g., HDFS blocks, S3 objects).
• Push‑down predicates – connectors expose a Constraint object; Trino pushes down filters, projections, and even limit clauses.
• Dynamic filtering – during a join, Trino can send intermediate filter values back to the remote source to prune data early.

Because Trino is stateless (workers can be added or removed without data migration), it scales horizontally to thousands of nodes, making it suitable for large‑scale federated analytics.

4.2 Real‑world deployment metrics

A 2023 case study from a global logistics firm compared Trino vs. Snowflake for a 3‑way join across PostgreSQL, S3 Parquet, and Elasticsearch:

Trino’s ability to push down filters to Elasticsearch and dynamic filtering on the Parquet side shaved off both time and cost.

4.3 Example: Joining Hive Health, Satellite NDVI, and Pesticide API

4.3.1 Data sources

4.3.2 Catalog definitions

-- PostgreSQL catalog
CREATE CATALOG hive_pg WITH (
    connector = 'postgresql',
    connection-url = 'jdbc:postgresql://hive-db.internal:5432/hives',
    connection-user = 'trino_user',
    connection-password = '*****'
);

-- Hive catalog (reads Parquet from S3)
CREATE CATALOG satellite WITH (
    connector = 'hive',
    hive.metastore.uri = 'thrift://metastore.internal:9083',
    hive.s3.aws-access-key = '*****',
    hive.s3.aws-secret-key = '*****'
);

-- HTTP catalog for pesticide API
CREATE CATALOG pesticide WITH (
    connector = 'http',
    http.base-url = 'https://api.pesticide.gov',
    http.headers = MAP(ARRAY['Authorization'], ARRAY['Bearer ***'])
);

4.3.3 Defining a view over the pesticide API

The http connector returns JSON; we need to map it to a table schema.

CREATE SCHEMA pesticide.public;

CREATE TABLE pesticide.public.exposure (
    zip_code VARCHAR,
    date DATE,
    pesticide_ppb DOUBLE
)
WITH (
    format = 'json',
    endpoint = '/exposure?zip=${zip_code}&date=${date}'
);

The ${} placeholders are substituted per row, enabling row‑level remote lookups.

4.3.4 The federated query

SELECT h.hive_id,
       h.date,
       h.weight_kg,
       ndvi.mean_ndvi,
       p.pesticide_ppb,
       CASE
         WHEN p.pesticide_ppb > 10 THEN 'HIGH'
         ELSE 'LOW'
       END AS pesticide_risk,
       -- Simple stress metric: weight loss + high NDVI + high pesticide
       (h.prev_weight_kg - h.weight_kg) * (1 + ndvi.mean_ndvi) *
       (CASE WHEN p.pesticide_ppb > 10 THEN 1.5 ELSE 1 END) AS stress_index
FROM hive_pg.public.hive_measurements h
LEFT JOIN (
    SELECT hive_id,
           DATE_TRUNC('day', timestamp) AS date,
           AVG(ndvi_value) AS mean_ndvi
    FROM satellite.default.ndvi_tiles
    WHERE ndvi_value IS NOT NULL
    GROUP BY hive_id, DATE_TRUNC('day', timestamp)
) ndvi
  ON h.hive_id = ndvi.hive_id AND h.date = ndvi.date
LEFT JOIN pesticide.public.exposure p
  ON p.zip_code = h.zip_code AND p.date = h.date
WHERE h.date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31'
ORDER BY stress_index DESC
LIMIT 20;

What makes this powerful?

• Dynamic filtering: Trino pushes the WHERE h.date BETWEEN … clause to the PostgreSQL source, scanning only the relevant partitions.
• Push‑down aggregation: The sub‑query that computes mean_ndvi runs on the Hive side, reading only the needed Parquet columns (ndvi_value, timestamp).
• Row‑level remote API calls: For each hive row, Trino issues a tiny HTTP GET to the pesticide API, but thanks to batching (the connector groups rows by zip code), the number of external calls drops from millions to a few hundred.

4.3.5 Scaling considerations

• Connector memory: The http connector streams JSON; set http.max-response-size to avoid OOM when a single response is huge.
• Network egress: If the pesticide API charges per request, enable caching via Trino’s http.cache.enabled flag (stores recent responses in local disk).
• Security: Use IAM roles for the worker nodes to access S3; for the PostgreSQL source, enable SSL and client certificates.

4.4 Extending Trino with AI agents

Apiary’s self‑governing AI agents can be registered as Trino connectors using the SPI (Service Provider Interface). An agent that continuously learns optimal pollination routes could expose a virtual table agent.routes that returns the latest recommended path per hive. The query engine would treat this as any other source, enabling analysts to join learned routes with real‑time weather.

5. Choosing the Right Tool for Your Federation Strategy