Debugging why query is not pushed down

1

For some reason my dremio query for bigquery (custom ARP connector) is not pushed down. Even simple query with sum() is not pushed down, and the aggregation always happens in dremio.

What are the best ways how to find out why Dremio did not push some query down? I checked the ARP file several times and cannot figure out why it won’t push the query down. Some logs with info what prevents pushdown would be helpful!

2

Hello @froxCZ

Can you share the query profile with us?

Thanks,
@Rakesh_Malugu

3

Sure,
Query:

SELECT sum(t.latitude) AS col
FROM lukas_bigquery_westeros_knights."solar-melody-287313".my_own_data.bikeshare_stations AS t
limit 1000;

Pushed down:

SELECT `bikeshare_stations`.`latitude`
FROM `solar-melody-287313`.`my_own_data`.`bikeshare_stations`

profile.zip (8.6 KB)

Thanks @Rakesh_Malugu !

4

@Rakesh_Malugu Did u have a chance to look at it please?

5

@froxCZ

Couple of things

  1. Your query is only returning one row as you are doing a SUM() with no group by, so not sure why you need a LIMIT
  2. We generally push down the entire SQL but I see your source is BigQuery, is this a customer built connector. What are the push down rules on this, for example a similar pushdown in Oracle would be

Jdbc(sql=[SELECT *
FROM (SELECT SUM(CAST(“EMPLOYEES”.“SALARY” AS NUMBER(38, 2))) “EXPR$0”
FROM “DREMIO”.“EMPLOYEES”) “EMPLOYEES”
WHERE ROWNUM <= 5])

In Postgres would be

Jdbc(sql=[SELECT CAST(SUM(“total_sales”) AS NUMERIC(38, 6)) AS “EXPR$0”
FROM (SELECT CAST(“sales_by_store”.“total_sales” AS DECIMAL(38, 6)) AS “total_sales”
FROM “public”.“sales_by_store”) AS “sales_by_store” FETCH NEXT 1 ROWS ONLY])

6

Hi @balaji.ramaswamy
This is the connector https://github.com/panoramichq/dremio-bigquery-connector - it is mostly copy & paste from Snowflake connector, and modified where it was needed.

I could not figure out why this query is not pushed down. The sum seems to be correctly defined in the APR. What I am looking for is some information in Dremio Query plan, why certain query could not be pushed down. How can I debug this?

7

@froxCZ

I am wondering if you should first submit your connector and then take it forward