External query engines¶

Use an external engine to query Siglake's Apache Iceberg tables directly. The warehouse stores Parquet files, so a compatible Iceberg reader does not need a running Siglake process.

These queries read Siglake's primary storage. They do not use an export copy.

Choose when to use an external engine¶

• If you stop running Siglake, your data remains as Parquet in your bucket, described by an Iceberg catalog.
• Use Siglake for interactive log search and freshness; use Trino for a monthly join against your warehouse.
• Direct reads need no extract, transform, and load (ETL) copy or export job.

Gather the connection details¶

Grant external readers read-only access. Siglake owns the write path; concurrent writers from another engine would fight with compaction over the same manifests.

Siglake tables use Iceberg format version 2. The events event time has two required columns so that v2 readers remain compatible without losing the source precision. Their declared Iceberg types are in the generated table schema reference; this is how they land in Parquet:

This contract replaced an earlier one, in which timestamp was a v3-only timestamptz_ns and the tables were format version 3. Warehouses written before the change are still format version 3 and must be recreated, not migrated. The measurements that motivated the change are kept in Superseded results.

Reconstruct nanosecond timestamps from timestamp_ns¶

Reconstruct an exact nanosecond timestamp by converting timestamp_ns with the engine's own function. timestamp is the portable time column, floored to a microsecond; timestamp_ns preserves all nine fractional digits and distinguishes the rows that share one microsecond. In Siglake's DataFusion SQL, to_timestamp_nanos(timestamp_ns) does the conversion; external engines use their corresponding one (from_unixtime_nanos in Trino and make_timestamp_ns in DuckDB), or retain the bigint where the engine's timestamp type is only microsecond precision.

Query from DuckDB, Trino, or Spark¶

Each engine reads the committed Iceberg snapshot. Configure it with the same catalog, warehouse, and namespace from the table above. Then query events through the engine's Iceberg connector:

-- DuckDB, using the current Iceberg metadata file
SELECT count(*)
FROM iceberg_scan('s3://<bucket>/warehouse/siglake/events/metadata/<current>.metadata.json');

-- Trino
SELECT count(*) FROM siglake.siglake.events;

After configuring spark.sql.catalog.siglake as an Iceberg catalog, run:

spark.sql("SELECT count(*) FROM siglake.siglake.events").show()

DuckDB needs an explicit metadata path unless you put an Iceberg REST catalog in front of Siglake's JDBC catalog. The Trino and Spark sections below give their catalog configuration. External readers lose the WAL buffer, Siglake metadata fast paths, raw-text indexes, ordered early-stop, and Siglake SQL user-defined functions (UDFs). They retain Iceberg snapshot isolation and standard partition and statistics pruning.

Reader compatibility¶

Measured on 2026-09-06 against a fresh fixture written by Siglake merge commit f0bacb5: a format-version-2 events table, 2,000 rows one nanosecond apart in two data files, one day(timestamp) partition, a SQLite Iceberg JDBC catalog and a local-filesystem warehouse. Method and full output are in the evidence section below.

Combinations not in this table are unverified. We have not run them, and this page does not claim they work. In particular, Spark 4.x and DuckDB extension builds other than the core hash above are untested.

Every result on this page is labelled with the timestamp contract and the Siglake revision it was measured against. Results for the superseded format-version-3 contract, where three of these four engines failed, are kept as dated history in Superseded results.

DuckDB¶

DuckDB's iceberg_scan reads the current events table from its explicit metadata path. Checked on 2026-09-06 against the fresh format-version-2 fixture written by Siglake f0bacb5:

DuckDB reports timestamp as TIMESTAMP WITH TIME ZONE and timestamp_ns as BIGINT. make_timestamp_ns(timestamp_ns) reconstructs all nine fractional digits when a timestamp value is needed.

That same extension build could not read Siglake tables at all under the superseded v3 contract; see Superseded results.

Read events from DuckDB¶

1. Install and load the Iceberg extension:

   INSTALL iceberg;
   LOAD iceberg;
   

2. For a warehouse on S3, add httpfs and a read-only secret for the bucket:

   INSTALL httpfs;
   LOAD httpfs;
   CREATE SECRET warehouse_s3 (
     TYPE s3,
     KEY_ID '<access-id>',
     SECRET '<secret>',
     REGION '<region>'
   );
   

3. Read the table's current metadata path from the catalog database, not from DuckDB:

   SELECT metadata_location
   FROM iceberg_tables
   WHERE table_namespace = '<namespace>' AND table_name = 'events';
   

4. Scan that path:

   SELECT count(*) FROM iceberg_scan('<metadata_location>');
   

The count matches what siglake sql "SELECT count(*) FROM events" reports for the same snapshot. Repeat step 3 after Siglake commits again: the path changes on every commit, and a stale path reads the older snapshot.

Two catalog caveats explain why step 3 exists. DuckDB's ATTACH … (TYPE ICEBERG) speaks the Iceberg REST catalog only (including its Glue, S3 Tables and Polaris flavours), so attaching Siglake's SQL catalog directly is not an option: a DuckDB reader needs either a REST catalog in front of it or the explicit metadata path from step 3. The bare directory form of iceberg_scan also requires a version-hint.text file, which Siglake does not write. Scanning the Parquet files directly works but loses snapshot isolation.

Trino¶

Verified on 2026-09-06 with Trino 483 against the format-version-2 contract as written by Siglake f0bacb5. Trino reports the portable timestamp column as timestamp(6) with time zone and timestamp_ns as bigint. For nanosecond-exact event time, use the sibling:

SELECT from_unixtime_nanos(timestamp_ns) AS exact_timestamp
FROM siglake.siglake.events;

The catalog file below shows the production shape: a Postgres catalog and S3 warehouse. What we actually ran was the same connector against a Postgres-shaped SQLite catalog on local disk; the Postgres and S3 legs are untested here.

# etc/catalog/siglake.properties
connector.name=iceberg
iceberg.catalog.type=jdbc
iceberg.jdbc-catalog.driver-class=org.postgresql.Driver
iceberg.jdbc-catalog.connection-url=jdbc:postgresql://catalog.internal:5432/siglake
iceberg.jdbc-catalog.connection-user=reader
iceberg.jdbc-catalog.connection-password=…
iceberg.jdbc-catalog.catalog-name=siglake
iceberg.jdbc-catalog.default-warehouse-dir=s3://my-siglake-warehouse/warehouse
fs.s3.enabled=true
s3.region=us-east-1

Three properties are easy to get wrong:

• iceberg.jdbc-catalog.driver-class is required. Trino refuses to start the catalog without it, and the class must be on the Iceberg plugin's classpath.
• iceberg.jdbc-catalog.catalog-name must equal the catalog_name column Siglake wrote into the catalog's iceberg_tables table. Use siglake unless you changed it.
• fs.s3.enabled is the current name; fs.native-s3.enabled still works as a deprecated alias and logs a warning.

SELECT sourcetype, count(*)
FROM siglake.siglake.events
WHERE timestamp >= current_timestamp - INTERVAL '1' DAY
GROUP BY sourcetype;

Spark¶

Spark 3.5.9 with Iceberg 1.11.0 reads both current event-time columns. Measured on 2026-09-06 against the format-version-2 contract as written by Siglake f0bacb5: DESCRIBE reports timestamp and bigint, and the fixture's count and exact timestamp_ns bounds agree with Siglake. Spark timestamps are microsecond precision, so keep timestamp_ns as a bigint in comparisons that must retain all nine digits.

Under the superseded v3 contract this same Spark and Iceberg pair could not read the table at all; see Superseded results.

This is the configuration shape we tested:

spark = (SparkSession.builder
    .config("spark.sql.extensions",
            "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions")
    .config("spark.sql.catalog.siglake", "org.apache.iceberg.spark.SparkCatalog")
    .config("spark.sql.catalog.siglake.catalog-impl",
            "org.apache.iceberg.jdbc.JdbcCatalog")
    .config("spark.sql.catalog.siglake.uri",
            "jdbc:postgresql://catalog.internal:5432/siglake")
    .config("spark.sql.catalog.siglake.warehouse",
            "s3://my-siglake-warehouse/warehouse")
    .config("spark.sql.catalog.siglake.io-impl",
            "org.apache.iceberg.aws.s3.S3FileIO")
    .getOrCreate())

spark.sql("SELECT host, count(*) FROM siglake.siglake.events GROUP BY host").show()

The local evidence used the same JdbcCatalog against SQLite and local disk; the production Postgres and S3 legs remain unverified.

Accelerations external readers lose¶

External readers get the full committed dataset and correct partition and statistics pruning. Iceberg's snapshot isolation means a reader sees a consistent snapshot even while Siglake commits.

Parquet-native per-column blooms are an opt-in, write-only knob: SIGLAKE_PARQUET_NATIVE_BLOOMS=on. They have been off by default since 2026-08-06 because time-sorted files spread every tag value across every row group, preventing useful pruning.

External readers do not get these Siglake features:

A GROUP BY host that Siglake answers in 3 ms with rows_scanned: 0 requires a scan in Trino or DuckDB. Use those engines for analytical work instead.

The time-ordered physical layout still helps every reader because time-bounded predicates prune at the manifest and row-group level in any engine.

Attributes from other engines¶

attributes is a JSON string. Use your engine's JSON functions:

-- DuckDB (using the current metadata path, per the DuckDB section above)
SELECT json_extract_string(attributes, '$."k8s.namespace"') AS ns, count(*)
FROM iceberg_scan('s3://bucket/warehouse/siglake/events/metadata/00012-<uuid>.metadata.json')
GROUP BY ns;

-- Trino
SELECT json_extract_scalar(attributes, '$["k8s.namespace"]') AS ns,
       count(*) FROM siglake.siglake.events GROUP BY ns;

-- Spark SQL
SELECT get_json_object(attributes, '$["k8s.namespace"]') AS ns,
       count(*) FROM siglake.siglake.events GROUP BY ns;

Each engine takes the JSON path in its own syntax, and a dotted attribute name has to be quoted inside the path, as above, so the dot is not read as another level.

If an attribute is promoted to a typed column, query that column directly. It works in every reader and avoids JSON extraction.

Operational cautions¶

• Snapshot expiry is on by default and retains the last 100 snapshots. A very long-running external query could have its snapshot expired underneath it. Widen snapshotExpire.retainLast if you run long analytical jobs.
• Orphan garbage collection deletes files. Its safety age (--min-age-secs) guards in-flight writes, but a reader holding a very old snapshot reference is not part of that calculation.
• Do not write from an external engine. Compaction rewrites files and swaps manifests atomically, and another writer committing to the same table will produce conflicts at best.

Verifying interoperability¶

A quick sanity check that both engines agree:

siglake sql "SELECT count(*) FROM events"

-- Trino, same warehouse
SELECT count(*) FROM siglake.siglake.events;

The counts should match, modulo rows still in the WAL buffer that Siglake can see and the external reader cannot. Query a closed time window in the past to compare exactly.

To reproduce the compatibility evidence below rather than trust it, run the Siglake repository's own check:

scripts/check-external-timestamp-contract.sh

It writes a fixture with siglake iceberg-demo, asserts the Siglake-side contract, then has each engine read the same warehouse and agree on the row count, the nanosecond bounds and the decoded microsecond timestamp. It finds duckdb and spark-sql on the path and PyIceberg through python3, and skips an engine it cannot find. The script's header documents the option that turns a skip into a failure, and where Spark looks for the SQLite JDBC driver the fixture's catalog needs.

Compatibility evidence¶

This is how the table in Reader compatibility was produced, so you can reproduce it or repeat it against a newer engine. Every result below was measured on 2026-09-06 against the current contract: timestamp as a microsecond timestamptz, timestamp_ns as a required long, with tables at Iceberg format version 2, as written by Siglake f0bacb5.

The fixture used a fresh debug build of Siglake merge commit f0bacb5 on Linux x86-64:

siglake --data-dir ./fixture iceberg-demo --n 2000 --reset

That writes a SQLite Iceberg JDBC catalog (iceberg_tables / iceberg_namespace_properties, the standard V1 catalog schema) and a file:// warehouse. The events table came out at format version 2, partitioned by day(timestamp): 2,000 deterministic rows one nanosecond apart from 2026-01-01T00:00:00Z, two data files and one partition. PyArrow inspected the written Parquet schema as required INT64 TIMESTAMP(MICROS, isAdjustedToUTC=true) for timestamp and required plain INT64 for timestamp_ns. Siglake's contract check and reference answers were:

The fixture writer also asserted format version 2, Iceberg timestamptz / long, the nanosecond round-trip, and the (timestamp, timestamp_ns) total order before an external engine ran.

Trino 483 used the release server with trino-iceberg-483 (Iceberg 1.11.0) on Temurin JDK 25.0.4.1+1, single node, catalog as in the Trino section but pointed at the fixture's SQLite catalog with iceberg.jdbc-catalog.driver-class=org.sqlite.JDBC. DESCRIBE reported timestamp(6) with time zone and bigint; the count and timestamp_ns bounds matched. from_unixtime_nanos(min(timestamp_ns)) returned 2026-01-01 00:00:00.000000000 UTC, and the maximum returned 2026-01-01 00:00:00.000001999 UTC.

Spark 3.5.9 (spark-3.5.9-bin-hadoop3) ran on Temurin JDK 21.0.12.1+1 with iceberg-spark-runtime-3.5_2.12:1.11.0 and sqlite-jdbc:3.53.4.0, the same catalog via org.apache.iceberg.jdbc.JdbcCatalog. DESCRIBE reported timestamp and bigint; the count and timestamp_ns bounds matched. The microsecond timestamp bounds were 2026-01-01 00:00:00 through 2026-01-01 00:00:00.000001.

DuckDB 1.5.5 with core iceberg extension 45163a28 used iceberg_scan on the explicit current metadata file. It returned the same count and exact long bounds and reported timestamp as TIMESTAMP WITH TIME ZONE. make_timestamp_ns(max(timestamp_ns)) returned 2026-01-01 00:00:00.000001999.

PyIceberg 0.12.0 with PyArrow 25.0.1 loaded the same explicit metadata file as a static table. Its schema reported required timestamptz and long; an Arrow scan of timestamp_ns returned the same count and bounds.

The fixture, DuckDB and PyIceberg checks were driven by Siglake's scripts/check-external-timestamp-contract.sh with required-engine mode enabled. Its bundled Spark command currently selects a Hadoop catalog that cannot resolve the SQLite SQL-catalog fixture; for this measurement a launcher wrapper redirected that command to the fixture's actual JdbcCatalog. The unwrapped-driver defect is tracked as Siglake task #1539; the reader result above is from the same files and SQL the driver generated, not a source-code inference.

Not covered: object storage (the fixture is on local disk, so S3, GCS and Azure paths are exercised by no engine here), Postgres as the catalog backend, concurrent reads during compaction, Spark 4.x, DuckDB REST-catalog attachment, and any Trino version other than 483.

Superseded results (2026-09-06, format version 3)¶

These results do not describe Siglake today. They are measurements against the superseded timestamp contract, kept because they are the reason the contract changed. Nothing here should be used to judge a current warehouse; for that, see Reader compatibility above.

The superseded contract was measured on 2026-09-06 against a fixture written by Siglake at commit d356796, a release build on Linux x86-64:

siglake --data-dir ./fixture/data iceberg-demo --n 500 --reset

The events table used Iceberg format version 3, with a single event-time column timestamp as a required timestamptz_ns. There was no timestamp_ns sibling. Siglake stamped each table with the minimum format version its schema required, so events, query_audit and the user-index timestamp schemas were all v3; schemas without a nanosecond timestamp stayed at v2. An external reader therefore needed both v3 metadata support and the v3-only timestamptz_ns type.

That second requirement failed hard: an engine that could not map timestamptz_ns could not read the table at all, even when projecting other columns. It failed while converting the schema, before planning a scan.

The fixture was a SQLite Iceberg JDBC catalog and a local-filesystem warehouse: 500 rows, two data files, one day(timestamp) partition. Siglake's own answers, which the external readers had to match:

Reader compatibility under the superseded contract was measured 2026-09-06 against Siglake d356796:

Trino was the only external reader demonstrated end to end under that contract. Spark 4.0 and 4.1 were untested, but Iceberg 1.11.0 carried the same missing type mapping in its spark/v4.0 and spark/v4.1 modules, so the same failure was expected.

Trino 483 used server-core plus the trino-iceberg-483 plugin (Iceberg 1.11.0) on Temurin JDK 25.0.4.1+1, single node, the catalog shape in the Trino section pointed at the fixture's SQLite catalog with iceberg.jdbc-catalog.driver-class=org.sqlite.JDBC. All four queries matched exactly, including both timestamps to the nanosecond; DESCRIBE events reported timestamp(9) with time zone; SELECT … WHERE timestamp >= current_timestamp - INTERVAL '1' DAY GROUP BY sourcetype returned 500; "events$partitions" showed the single {day_ts=2026-09-06} partition with 500 records; query_audit read as an empty table rather than erroring.

Spark 3.5.9 (spark-3.5.9-bin-hadoop3) ran on Temurin JDK 21.0.12.1+1 with iceberg-spark-runtime-3.5_2.12:1.11.0 and sqlite-jdbc:3.53.4.0, the same catalog via org.apache.iceberg.jdbc.JdbcCatalog. SHOW NAMESPACES and SHOW TABLES succeeded. SELECT count(*), DESCRIBE TABLE, and the same statements against query_audit all failed with UnsupportedOperationException: Cannot convert unsupported type to Spark: timestamptz_ns. Reading the data files as plain Parquet failed with AnalysisException: Illegal Parquet type: INT64 (TIMESTAMP(NANOS,true)).

DuckDB v1.5.5 with iceberg extension 45163a28 (core) and 6561bfca (core nightly). Every route into the table failed while parsing the table schema. These routes included a catalog attachment, iceberg_scan on the table directory, and iceberg_scan on an explicit metadata path:

Invalid Configuration Error: Unrecognized primitive type: timestamptz_ns

That was a type failure, not a format-version one: the extension understood timestamp_ns but not the v3 timestamptz_ns. timestamptz_ns support landed on duckdb-iceberg main on 2026-07-08; neither of those extension builds contained it.

PyIceberg was not measured against this contract.