Working with Federated Queries

Use this page when you need to run SQL across multiple sources in one request.

Federated queries use DuckDB as a read-only query layer, so you can join and compare multiple sources without creating a stream first.

Federated queries extend the file querying capabilities by allowing multiple sources to be combined in a single SQL statement.

Each request dynamically attaches the listed connections as queryable sources inside a single DuckDB execution context.

Example

Join a PostgreSQL table with a Parquet dataset in one query:

SELECT c.name, s.revenue
FROM pg1.public.customers c
JOIN read_parquet('s31://analytics/sales/*.parquet') s
  ON c.id = s.customer_id
LIMIT 10

This page covers how to:

• attach database and S3 connections to one request
• reference each source with the correct alias format
• join databases with S3 data or local files
• verify that a federated query returned the expected rows
• troubleshoot auth, alias, and query execution failures

Common workflows

Compare two databases

1. Add both database connections to connections[].
2. Assign short aliases such as pg1 and my1.
3. Join or UNION the sources in one query.

Join a database table to S3 data

1. Add the database connection and the S3 connection to connections[].
2. Use the database alias in table references and the S3 alias as the URL scheme in read_parquet(...) or another DuckDB file reader.
3. Confirm the join returns the expected row count before using the SQL in automation.

Join a database table to a local file

1. Add only the database connection to connections[].
2. Read the local file directly with read_csv_auto(...), read_json_auto(...), or read_parquet(...).
3. Use an absolute file path that exists where the API server is running.

Prerequisites

• A running DBConvert Streams API server
• Your API base URL
• Access to the authentication headers described in API Reference
• Stored connection IDs for each database or S3 provider you want to attach

If your query reads a local file, use an absolute path from the environment where the API is running:

• Windows desktop app: C:\Users\alice\Downloads\ratings.csv
• macOS or Linux desktop app: /Users/alice/Downloads/ratings.csv
• Docker or server deployment: /data/imports/ratings.csv

Local files are always resolved on the API host, not the client machine sending the request.

Use a common request pattern

API_URL="http://127.0.0.1:8020/api/v1"
API_KEY="YOUR_API_KEY"
INSTALL_ID="YOUR_INSTALL_ID"

All requests on this page use the same headers:

-H "X-API-Key: $API_KEY" \
-H "X-Install-ID: $INSTALL_ID"

Understand the request shape

POST /query/federated

{
  "query": "SELECT * FROM pg1.public.customers ORDER BY id LIMIT 100 OFFSET 0",
  "maxRows": 1000,
  "connections": [
    {
      "alias": "pg1",
      "connectionId": "conn_postgres_source_id",
      "database": "analytics"
    },
    {
      "alias": "s31",
      "connectionId": "conn_s3_aws_id"
    }
  ]
}

Request rules:

• query is the SQL statement to execute
• connections is an array of attached sources for this request
• each alias must start with a letter and may contain only letters, numbers, and underscores
• database is optional; omit it when the connection's default database is the one you want
• to page results, put LIMIT / OFFSET in the SQL query itself — there are no separate limit/offset body fields, and no withTotalCount field
• maxRows is an optional hard safety cap on rows materialised into the response; omit it to use the server default (1,000 rows), or pass maxRows: 0 for an explicit unbounded response
• executionMode is optional: run (default) executes the SQL exactly as written; plan returns a DuckDB EXPLAIN instead of results
• local file readers such as read_csv_auto('/absolute/path/file.csv') do not require a connections[] entry

Alias and naming rules

Use the current API contract naming conventions in your SQL:

Supported file reader examples in the current contract:

• read_parquet(...)
• read_csv_auto(...)
• read_json_auto(...)

You can also use aggregations, filters, and subqueries across sources just like in standard SQL.

Query two databases in one request

Use this pattern when you need one SELECT to read across PostgreSQL and MySQL.

curl -X POST "$API_URL/query/federated" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT a.first_name || '\'' '\'' || a.last_name AS actor, f.title AS film FROM pg1.public.actor a JOIN pg1.public.film_actor fa ON a.actor_id = fa.actor_id JOIN my1.film f ON fa.film_id = f.film_id LIMIT 20",
    "connections": [
      {
        "alias": "pg1",
        "connectionId": "CONN_POSTGRES_ID",
        "database": "sakila"
      },
      {
        "alias": "my1",
        "connectionId": "CONN_MYSQL_ID",
        "database": "sakila"
      }
    ]
  }'

Verify by checking:

• the response contains status: "success"
• columns include the aliases from your projection, such as actor and film
• count matches the number of returned rows

Join a database table to S3 data

Use this pattern when the SQL needs database tables and objects from an attached S3-compatible provider in the same query.

curl -X POST "$API_URL/query/federated" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT c.name, s.revenue FROM pg1.public.customers c JOIN read_parquet('\''s31://analytics/sales/*.parquet'\'') s ON c.id = s.customer_id LIMIT 100",
    "connections": [
      {
        "alias": "pg1",
        "connectionId": "CONN_POSTGRES_ID",
        "database": "analytics"
      },
      {
        "alias": "s31",
        "connectionId": "CONN_S3_ID"
      }
    ]
  }'

Verify by checking:

• columns contain fields from both the database table and the Parquet file
• rows show joined values rather than two independent datasets
• count stays within the route's default 1,000-row response cap (unless maxRows raises it)

Join a database table to a local file

Use this pattern when the database is attached through connections[], but the second source is a local file accessible to the API host.

curl -X POST "$API_URL/query/federated" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT db.title, csv.rating FROM pg1.public.film db JOIN read_csv_auto('\''ABSOLUTE_FILE_PATH'\'') csv ON db.film_id = csv.film_id LIMIT 100",
    "connections": [
      {
        "alias": "pg1",
        "connectionId": "CONN_POSTGRES_ID",
        "database": "sakila"
      }
    ]
  }'

Replace ABSOLUTE_FILE_PATH with the real path for the environment where DBConvert Streams is running.

Verify by checking:

• the file path resolves on the API host, not just on your local shell
• columns include fields from both the database table and the file reader
• sample joined rows match known records from both sources

Behavior and limits

The current backend enforces these constraints for POST /query/federated:

• attached database sources are read-only
• responses are capped at 1,000 rows by default unless maxRows changes the cap (maxRows: 0 for an explicit unbounded response)
• paging is done with LIMIT / OFFSET in the SQL query itself; the request body has no limit, offset, or withTotalCount fields
• connectionId values are resolved in the authenticated user's scope before the query runs

Federated queries are for analysis and validation, not data movement. They do not persist results or create streams.

These limits keep federated queries fast and predictable across mixed sources.

To fetch more than one page, change the LIMIT / OFFSET in the SQL query between requests. Include a stable ORDER BY so page order is deterministic.

For cross-source joins, every page is a fresh federated execution and may be close to the cost of running the query again. For single-source federated queries, DuckDB can usually push LIMIT and OFFSET down to the attached source.

End-to-end sanity check

After a successful federated query:

• status should be success
• columns should match the projection in your SQL
• rows should contain only the joined or filtered records your query asks for
• count should equal the number of rows returned in the response

Troubleshooting

401 Unauthorized

Confirm the X-API-Key and X-Install-ID headers from API Reference are present and match the current installation.

400 Bad Request

Check for an empty query, an invalid alias format, or a malformed JSON body.

404 Connection not found

At least one connectionId does not exist or is not available to the authenticated account.

SQL error or attach failure

Recheck the source naming format:

• PostgreSQL: alias.schema.table
• MySQL: alias.table
• S3: read_*('alias://bucket/path/...')
• local files: read_*('/absolute/path/...')

Unexpectedly truncated results or slow queries

Narrow the dataset with WHERE, reduce the projected columns, or aggregate before returning rows. The backend caps each response at 1,000 rows by default (when the SQL has no LIMIT), so larger result sets should be fetched as ordered pages using LIMIT / OFFSET in the SQL. Large cross-source queries may still fail or take longer depending on the attached systems and file sizes.

Related docs

• API Reference
• Working with Files and S3
• Federated Queries
• SQL Console