Skip to content

DuckDB

Connect to an embedded DuckDB database, or query remote Parquet / CSV / JSON files over HTTP/HTTPS directly through DuckDB — without downloading files into Report Designer or importing them into a database.

Goal

Create a DuckDB connection and use it as a report Data Source (local database file or remote analytical file).

Modes

DuckDB Database

Open a local .duckdb / .db file. Browse schemas, tables, and columns with DuckDB SQL.

Data Files (remote HTTP/HTTPS)

Point at an HTTP or HTTPS URL for Parquet, CSV, or JSON. Designer discovers schema via DuckDB’s native readers and generates SQL such as:

SELECT
    payment_type,
    COUNT(*) AS trip_count,
    SUM(total_amount) AS total_amount
FROM read_parquet(
    'https://example.com/nyc/yellow_tripdata_2024_01.parquet'
) AS "yellow_tripdata_2024_01"
GROUP BY payment_type
ORDER BY total_amount DESC

DuckDB reads the remote object directly (httpfs). Report Designer does not download the full file into application memory or create a permanent local copy.

Supported in this phase:

  • Protocols: http://, https://
  • Formats: Parquet, CSV, JSON (Auto Detect from URL extension)
  • Authentication: None, Basic Auth, Bearer Token, Custom HTTP Headers

Not yet supported: S3 / GCS / Azure object-storage providers (planned next), multiple files per connection, local filesystem data files.

Authentication (Data Files)

Under Format, choose Authentication:

Mode Fields DuckDB mechanism
None — Public URL or already-signed (presigned) URL
Basic Auth Username + Password Temporary CREATE SECRET … TYPE HTTP with Authorization: Basic … header
Bearer Token Token Temporary CREATE SECRET … TYPE HTTP, BEARER_TOKEN …
Custom HTTP Headers Header / Value rows Temporary CREATE SECRET … EXTRA_HTTP_HEADERS MAP {…}

Credentials are stored in the OS credential store (same as other AcuGIS connections). They are not written to JRXML, generated SQL, report parameters, connection list subtitles, metadata browser labels, or application logs.

CREATE SECRET statements are never logged.

Presigned URLs (query-string signatures) continue to work with Authentication = None. Query parameters are treated as sensitive and redacted in logs/errors/Test Connection display.

Prerequisites

  • Local DuckDB database file or an HTTP(S) Parquet/CSV/JSON URL
  • JDK 17+ (DuckDB JDBC). Designer packages the Windows DuckDB JDBC driver and the httpfs extension for offline remote-file access
  • No DuckDB server install is required

Note

Connection Test, metadata, Data Preview, and Report Preview all use DuckDB JDBC. Jasper fills DuckDB reports via REPORT_CONNECTION with language="SQL" (no Go/CGO DuckDB engine).

  1. Open Data → Connection Manager….
  2. Add a connection and choose DuckDB.
  3. Select Data Files.
  4. Enter the Remote Source URL (e.g. https://…/yellow_tripdata_2024_01.parquet).
  5. Leave Format as Auto Detect, or choose Parquet / CSV / JSON.
  6. Set Authentication (None for public/presigned URLs; otherwise Basic / Bearer / Custom Headers).
  7. Click Test — success shows source (query redacted), resolved format, and field count.
  8. Save the connection.
  9. Browse files → <logical_name> → columns, build a query, Apply, Preview, place fields, and Preview the report.

The metadata browser shows a synthetic files schema. Objects are typed as EXTERNAL_FILE and retain the real remote URL; Query Builder emits read_* table functions, not "files"."table".

Portability / Report Server

JRXML stores:

  • datasource type duckdb
  • connection name reference (acugis.datasource.connection)
  • connection method (database | files)
  • logical file aliases (e.g. yellow_tripdata_2024_01)
  • query / fields / parameters (including read_parquet('https://…') in generated SQL — URL only, never credentials)

Remote URLs stay in the Designer connection store (options.files[].source). Auth type and header names may be stored in connection options; secret values stay in the credential store.

On Report Server, resolve authentication via the existing connection-name mapping (same pattern as other AcuGIS datasources). Do not embed tokens or passwords in the published JRXML.

Notes

Note

Parquet is the primary format for large remote datasets. CSV and JSON are also supported via DuckDB readers.

Note

Designer loads the DuckDB httpfs extension from a packaged local file (tools/jasper-preview/extensions/duckdb/) for remote Data Files connections. Runtime INSTALL httpfs Internet downloads are not used.

Note

Report parameters ($P{name}) are bound as DuckDB prepared-statement parameters.

Note

Object storage (Amazon S3, S3-compatible, GCS, Azure Blob) will use dedicated DuckDB secret types in a later phase — not HTTP header workarounds.