Mongodb 60s Timeout

1

When i start long query in mongodb a receive this message:
Query was cancelled because planning time exceeded 60 seconds

This is my query:
SELECT msg_id, hours, nested_4.hours.data_hour AS dataTime, dt_end, dt_start, dt_upd, etso, event_status, last, tipo, unaviable_capacity, zona
FROM (
SELECT msg_id, hours, dt_end, dt_start, dt_upd, etso, event_status, last, tipo, unaviable_capacity, zona
FROM (
SELECT msg_id, days, hours, nested_2.hours.last AS hours_last, dt_end, dt_start, dt_upd, etso, event_status, last, tipo, unaviable_capacity, zona
FROM (
SELECT msg_id, days, flatten(nested_1.days.hours) AS hours, dt_end, dt_start, dt_upd, etso, event_status, last, tipo, unaviable_capacity, zona
FROM (
SELECT nested_0.* FROM (
SELECT msg_id, flatten(days) AS days, CAST(“remit_centrali_test_dremio”.“dt_end” AS TIMESTAMP) AS dt_end, CAST(“remit_centrali_test_dremio”.“dt_start” AS TIMESTAMP) AS dt_start, CAST(“remit_centrali_test_dremio”.“dt_upd” AS TIMESTAMP) AS dt_upd, etso, event_status, last, tipo, unaviable_capacity, zona
FROM mongo_test.transmission.remit_centrali_test_dremio AS remit_centrali_test_dremio
WHERE last = 1
) nested_0 WHERE nested_0.days.last = 1
) nested_1
) nested_2
) nested_3
WHERE hours_last = 1
) nested_4

The problem is when i try to extract data_hour from nested_4.hours.

How can i increase the timeout??

2

Can you share a query profile?

3

This is my query profile
58a94b6a-cfb0-435e-8be4-e78519fdba60.zip (3,7 KB)

Thanks

4

Same problem with other query
When i use this query

SELECT msg_id, etso, dataTimeUTC, FROM_UTC_TIMESTAMP(dataTimeUTC, ‘Europe/Rome’) AS dataTime, FROM_UTC_TIMESTAMP(CAST(dt_start as TIMESTAMP), ‘Europe/Rome’) AS dt_start, FROM_UTC_TIMESTAMP(CAST(dt_end as TIMESTAMP), ‘Europe/Rome’) AS dt_end, FROM_UTC_TIMESTAMP(CAST(dt_upd AS TIMESTAMP), ‘Europe/Rome’) AS dt_upd, remit, tipo, zona, unavailability_type
FROM (
SELECT msg_id, nested_2.day_hour.last AS day_hour_last, CAST(nested_2.day_hour.data_hour AS TIMESTAMP) AS dataTimeUTC, nested_2.day_hour.remit AS remit, dt_end, dt_start, dt_upd, etso, tipo, unavailability_type, zona
FROM (
SELECT msg_id, days, flatten(nested_1.days.hours) AS day_hour, dt_end, dt_start, dt_upd, etso, tipo, unavailability_type, zona
FROM (
SELECT nested_0.* FROM (
SELECT msg_id, flatten(days) AS days, dt_end, dt_start, dt_upd, etso, last, tipo, unavailability_type, zona
FROM mongo_remit_dev.transmission.remit_centrali
WHERE last = 1 AND dt_end >= ‘2018-03-07’
) nested_0 WHERE nested_0.days.last = 1
) nested_1
) nested_2
) nested_3 WHERE day_hour_last = 1

This is ok, and this query use Raw Reflection Acceleratiom, and take 10 s

But if i try to filter dataTime field this query take long time and give me this error
Query was cancelled because planning time exceeded 60 seconds

SELECT nested_4.* FROM (
.
.
.
) nested_4 WHERE dataTime > ‘2018-01-01 00:00:00.000’

I attach the query profile.

Thanks361ebc1c-87f3-4152-9107-e6df09554e57.zip (89,0 KB)

5

There are limitations connecting to recent versions of MongoDB. The planning issue you are running into is not a time out that should be increased - this is time spent generating the query plan, which is typically a few milliseconds.

There may be variations of the SQL that you can get to work.

6

Thanks for your answer, the workaround that i use now is create parquet file and create virtual dataset from this
DROP TABLE IF EXISTS “$SCRATCH”.test
CREATE TABLE $SCRATCH.test AS SELECT * FROM “mongo”.“test”
CREATE VDS “Report”.test AS SELECT * FROM “$SCRATCH”.test

And in this way is very fast.

7

Did you try adding a raw data reflection on the source collection then running your query? Might be easier to manage than your CTAS approach if it works.

8

Thanks your suggestion work fine, I am very happy.
But i preferer take all roow from mongodb and make on VDS and make specific parquet file for report, i think this way is very fast

9

The raw reflection is essentially doing the same thing … the difference is that Dremio can manage the updates for you according to a schedule you define.

You can also create aggregation reflections to speed up GROUP BY queries.