Aggregate pushdown from Foundry SQL API to Databricks: malformed schema probe

Ask the Community
,
1

Context: We have a couple upcoming needs where querying Databricks/Snowflake through Foundry’s SQL API and virtual tables vs. going direct would be advantageous. After seeing some inconsistent performance for certain types of queries I re-created a simplified example.

TLDR: I think there is a defect producing malformed query during the aggregate schema probe (missing ‘()’ for sub queries) triggering a fallback path that is much less efficient.

image
image1568×144 23.9 KB

Expected behavior: Databricks performs the filtering and aggregation, Foundry passes the aggregate result back to the app. In the API test below… the query took 26.879 seconds through Foundry, compared with 3.353 seconds directly against Databricks

We reduced the test to a Databricks view containing IDs 1–10, registered as a JDBC-backed Foundry virtual table. The result is correct, but aggregate pushdown fails. What was strange was filter and projection pushdown DO get correctly executed in databricks hence why i think it’s a defect.

APIs used: Foundry REST SQL v2

POST /api/v2/sqlQueries/execute
GET  /api/v2/sqlQueries/{queryId}/getStatus
GET  /api/v2/sqlQueries/{queryId}/getResults

{
  "query": "SELECT COUNT(*), SUM(id) FROM `master`.`<virtual-table-rid>` WHERE id >= 8",
  "fallbackBranchIds": ["master"],
  "serializationFormat": "CSV"
}

Foundry returned (3, 27) which is correct, but didn’t explain the slowness.

Using the Databricks CLI, matching the connection’s service principal, source view, warehouse and test window:

databricks --profile <profile> --output json api get \
  '/api/2.0/sql/history/queries?include_metrics=true&max_results=100'

Databricks received these statements (identifiers are redacted):

-- Valid schema probe: FINISHED, zero rows (tbh i don't get why it even does this, but w/e)
SELECT *
FROM `catalog`.`schema`.`jdbc_view_probe`
WHERE 1=0;

-- Aggregate schema probe: PARSE_SYNTAX_ERROR, SQLSTATE 42601
SELECT * FROM SELECT COUNT(*),SUM(`id`)
FROM `catalog`.`schema`.`jdbc_view_probe`
WHERE 1=0 WHERE 1=0;

-- Fallback: FINISHED, three rows containing IDs 8, 9, 10
SELECT `id`
FROM `catalog`.`schema`.`jdbc_view_probe`
WHERE (`id` IS NOT NULL) AND (`id` >= 8);

Here is a visual of what’s hapening..

image
image1464×1098 108 KB

Running the aggregate directly through Databricks POST /api/2.0/sql/statements succeeds and returns (3, 27) as one row:

SELECT COUNT(*), SUM(id)
FROM `catalog`.`schema`.`jdbc_view_probe`
WHERE id >= 8;

In this example the inefficiency is trival, there are three matching rows, but fetching millions of rows into Foundry to calculate a small aggregate result would significantly constrain what kinds of queries this approach could be used for.

Note: I did a very brief sanity test of the other query entrypoints to see if anything changed:

  1. public REST API (described above ^^)
  2. Arrow Flight SQL
  3. directly calling the api SQL Studio uses

Am I missing something?