Are we limited to some SQL subset supported by Dremio while working with Postgres or can we push down any query supported by Postgres while doing aggregations for reflections? My use case would require using recursive CTEs for example.
The answer is yes, Dremio supports CTE, but maybe not in the same way as PG, and there may be other ways to accomplish what you’re doing that are easier or more flexible.
You can nest SQL expressions many layers deep, and I find this is easier to write than CTE, personally. If you start to build a new VDS and perform many different transformations, you’ll see an example of how the nesting works. Here’s one, working with data in PG:
SELECT first_name, last_name, CONCAT(“email”,’@dremio.com’) AS email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
FROM (
SELECT first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
FROM marketing.nested_PG
) nested_0
WHERE ‘1997-10-01’ > hire_date
In Dremio users do not explicitly control the expressions thst are pushed down into the underlying source. You can experiment to see what is generated, probably the easiest is to look at your PG logs. You can also look at the job in Dremio but the expression is embedded in other information and isn’t terribly easy to find. Here’s that same query above and what was pushed down:
SELECT *
FROM (SELECT “first_name”, “last_name”, “email”, “phone_number”, “hire_date”, “job_id”, “salary”, “commission_pct”, “manager_id”, “department_id”
FROM “public”.“employees”) AS “t”
WHERE DATE ‘1997-10-01’ > “hire_date”
If you have specific questions related to your use of CTE, please ask and we will see what the best option would be.
As far as I have experimented, Recursive CTEs are not supported. Non-recursive CTEs are supported.