Data Flow Timeout Error After 1 Hour When Loading Data from Hive Database

Hi Power BI Community,

I’m encountering a timeout error in a Gen1 Dataflow while loading data from a Hive database into a Power BI semantic model. The data is large, particularly due to three columns (`CustomerID`, `ProductID`, and `RegionID`) with long text values (200 characters each, containing a mix of alphabets and numbers).

Here’s the context:
- Hive Database Connection: Using a DSN (`dsn=ds-hive_database-presto`).
- SalesDate Values: Stored in the format `YYYYMMDD` (e.g., `20240801`, `20240802`, etc.).
- Dataflow SQL Query:
sql
SELECT CustomerID, ProductID, RegionID, Price, Qty, SalesDate
FROM Sales
WHERE SalesDate >= 20241001 AND SalesDate <= 20241031

- Error Message:

PipelineException: ERROR [HY000] [Simba][Presto] (1060) Presto Query Error:
Query exceeded the maximum execution time limit of 60.00m (131075).
RootActivityId = 11a51daa-628e-4763-8c64-9368954be744.

M Query Details:
- Parameter `DateKey`:

(x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

- Parameter `RangeStart`:

let RangeStart = #datetime(0001,01,01,00,00,00)
meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]
in RangeStart

- Parameter `RangeEnd`:

let RangeEnd = #datetime(9999,12,31,23,59,59)
meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]
in RangeEnd

- M Query:
let
startDate = DateKey(RangeStart),
endDate = DateKey(RangeEnd),
query = "
SELECT CustomerID, ProductID, RegionID, Price, Qty, SalesDate
FROM Sales
WHERE SalesDate > '" & Text.From(startDate) & "'
AND SalesDate <= '" & Text.From(endDate) & "'
AND Flag = 'Y'",
Source = Odbc.Query("dsn=ds-hive_database-presto", query, [CommandTimeout=#duration(0,3,0,0)]),
Change_dt_date = Table.TransformColumnTypes(Source, {{"dt", type date}}),
Change_dt_datetime = Table.TransformColumnTypes(Change_dt_date, {{"dt", type datetime}}),
#"Query-6474-autogenerated_for_incremental_refresh" =
Table.SelectRows(Change_dt_datetime, each DateTime.From([SalesDate]) >= RangeStart
and DateTime.From([SalesDate]) < RangeEnd)
in
#"Query-6474-autogenerated_for_incremental_refresh"

Issue:
The query is timing out after 1 hour due to the large dataset and the complexity of the query. I’ve set a `CommandTimeout` of 3 hours (`#duration(0,3,0,0)`), but the error persists.

Questions:
1. Are there any best practices to optimize this query or reduce the load time?
2. Is there a way to increase the timeout limit beyond 1 hour for Presto queries in Power BI?
3. Would partitioning the data or filtering it further help resolve this issue?

Any guidance or suggestions would be greatly appreciated!

Thanks in advance